作为一名接触Excel有7年历史的运营94年大叔，建议日常的大多数人只需快速系统了解Excel，以后遇到问题再针对性搜索或学习即可。

E00.Excel系统知识大纲

本文内容较长，干货不断，建议小伙伴们点赞收藏后，供日后学习使用~

根据了Excel2016专家认证的考试大纲，结合自己的工作使用，就给大家从管理工作簿、自定义数据格式与验证、Excel函数、Excel图表、数据透视表等5大部分来一一论述。

E01.管理工作簿

这部分的内容主要是接触整体的菜单栏、功能区的熟悉、让我们对Excel整体框架有大概的了解以及如何保护工作簿不被别人修改等等。

E01-1.新建功能区藏着不少模板

【文件】→【新建】就有许多同学不知道的Excel模板，这些模板美观+实用，绝对是初学者最好用的模板。

数据透视表中实用的图文教程。

详细清晰的图文教程

2.日常中考勤用到的模板

计划甘特图

家庭贷款计算器

家庭预算表

商品库存表

E01-3.日常处理工作簿的小技巧

1.设置快速访问工具栏

设置保护密码的表格

取消保护的工作表

还有更多工作簿处理小技巧就不一一论述，详细可以看教程的视频操作，更容易上手啦。

E02.自定义数据格式和验证

单元格是工作表最基本的单位，单元格的格式设置尤为重要，我们主要了解到文本、数值、日期等格式的区别，了解通配符、*号等应用就OK了。

E02-1.单元格式设置

通过选中单元格右击，或者利用快捷键ctrl+1就能调出【单元格格式设置】，日常用的较多单元格格式设置主要为，日期、文本、数值、货币等几种形式。

利用自定义格式还能设置出别的形式，例如在数值带单位、重复文本等，这方面内容只需了解即可。作为基础无需深入研究。

E02-2.多行多列的转换

我们经常要遇到一些乱糟糟的数据要整理，多行多列转换经常要用到滴。

杂乱无序的多行多列转换

↓↓↓

E02-3.常用Excel快捷键

能熟练应用一些常用快捷键能极大帮助我们提高工作效率，不少小伙伴由于记快捷键太难而放弃使用快捷键，其实整理好规律记快捷键就能极大提高效率。

我整理日常自己用得比价多的快捷键，分别是从A到Z，0到9的运用，帮助大家能记住这些快键键。

E02-4.条件格式

通过常规的条件格式设置，我们可标记出重点的数据，突出显示某部分的数据。

01.突出显示单元格规则

点击【开始】→【条件格式】即可选择相关的规则，突出显示相关的单元格。

1.查找重复值，查找班里不及格的人等等

02.最前最后的规则

利用前N项或者后N项，通过突出显示快速找到对应的数据。

例如我们可以显示数学成绩高于平均分的同学、显示各位同学最低的一门科目成绩、班里成绩最好的3位同学

03.图标集

许多小伙伴不知道这些小图标是来自条件格式，导致了误以为是图表上的。

优点：其实这些精致的小图标设置很简单，通过默认的设置就能得出来。缺点：就是精度不高，只能表示大概。图标集分为方向、形状、标记、等级四个归类，根据归类我们日常主要分别应用在

方向：表示涨↑跌↓，一般用于数据对比

形状：一般用于分类划分的信号

标记：用于标记状态，某件事做了与否，达成目标与否

等级：表示占比划分

E02-5.批量重命名文件和新建文件夹

日常我们经常遇到要批量把某文件重新按照一定规律，通过bat批处理就能快速解决该问题。

1.提取当前文件夹文件名。

我们只需要把下面的代码复制到一个新建的记事本中，然后把记事本的文件类型.txt改为.bat即可用提取当前文件夹的所有文件名。

@echo offfor /f "delims=" %%a in (dir /b/a-d/oN *.*) do echo %%a >>当前文件夹内文件名.txt

2.批量新建文件夹。

新建文件夹的代码如下，把他们复制到bat文件里面，双击即可运行。

MD 北京MD 天津MD 河北MD 山西MD 内蒙古MD 辽宁

3.批量改名字。

批量改名往往结合1中的提取文件名使用，如果涉及处理数据我们可以使用Excel出来好数据然后把数据复制到txt文档，最后修改文件类型为.bat，双击bat文件运行。

ren 1月.png 1月.jpgren 2月.png 2月.jpg

注意：以上批处理均是在.bat文件中执行的，有些安全防护软件会阻止bat批处理的执行，直接退出或在弹窗时允许本次执行即可。

E02-6.设置主题颜色

日常我们可以看到许多大神能做出许多不同配色的方案，其实这种通过主题色设置即可，主题是颜色、字体和形状三种的组合。

00配色好比穿搭

今天跟大家讲一下配色，把PPT配色比喻女生穿穿搭，那么Excel就是男生的穿搭，所以Excel的风格更适合黑白灰三种保险色，其他突出的就用同一种色系点缀突出。

01.总的来说，PPT的配色可以丰富多变，但再漂亮的妹纸也要遵循一定的穿搭审美滴。

02.Excel较多为数据，最好用黑白灰三色，在图表上的配色可以丰富点。

数据表格采用黑白灰三色

图表可以丰富一点

或者再丰富一点

03.掌握原理之后更重要是如何执行，接下来通过三招，带你成为图表穿搭高手。

01白银级配色

01.选中表格数据源，点击【开始】【套用表格样式】，选择你喜欢的一个表格样式就能直接套用。

02.例如这就是选中第一个的样式的效果

02黄金级配色

01.不同色系表示不同数据系列懂得选择同一饱和度数的颜色，在填充颜色的【主题颜色】，同一行的颜色代表颜色饱和度是相同的。所以我们选择区分不同系列选择颜色，饱和度相同的不同色系。

02.饱和度不同表示程度深浅

我们在配色的相同的列，就能选择不同的饱和度的颜色，代表同一事物的深浅。

03白金级配色

01.懂得利用颜色主题的系统搭配颜色

点开【页面布局】【主题颜色】就能变换多种主题颜色配置。系统原来就为我们搭配了许多配色方案，相对默认的配色。这些配色色系上还是不错的。

注意：配色的核心是使用主题色，而不是采用标准色，标准色是你换任何配色方案都不会有变化的，当然自定义的颜色也跟标准色一样，不会随主题的改变而改变。

04钻石级配色

01.自定义主题颜色设置

点开【页面布局】【主题颜色】拉到最后的【自定义颜色】就能自己设置颜色了。

许多同学不会自己配色的，那么最快捷的方法就是参考别人优秀的配色方案

05寻找配色方案

01.参考专业的图表网站：如echart

网站地址：https://www.echartsjs.com/zh/index.html

02.设计配色网站：安利这个PPT导航的网站给大家。网址：http://www.hippter.com/

c.可视化平台等：如网易数读

04.商业杂志配色

06配色展示

01.参考《经济学人》杂志的配色效果

02.参考《中国色》配色网站效果

03.参考数据大屏的配色效果

E03.常用Excel函数

Excel函数是最考验逻辑的功能，也是提极大提高我们工作效率一环。Excel函数多达472个，其实我们只要掌握核心的4个函数就OK了。他们分别是vlookup、if，sumifs，countifs四个函数。

E03-1.IF函数

=IF(logical_test,value_if_true,value_if_false)

=IF(判断条件,条件成立返回这里,条件不成立反馈这里)

①单个if函数使用

=IF(C2>6,"帅","一般")

式子里填写中文需要用引住。

②多重if嵌套应用↓

=IF(K2>=85,"优",IF(K2>=70,"良",IF(K2>=60,"中","差")))

其实某部分多层IF嵌套，用vlookup可以更完美的解决，下面会跟大家降到vlookup的具体用法。

=VLOOKUP(K2,{0,"差";60,"中";70,"良";80,"优"},2,TRUE)

③IF/and/OR函数嵌套使用

E03-2.vlookup函数

表达式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=VLOOKUP(查找值,查找区域,返回列数,匹配类型)

①正向查找

例子：要找班上一名叫喜洋洋的数学成绩

=VLOOKUP(喜洋洋,成绩表区域,返回列数,0)

正向查找，我们可以通过改变引用的方式，匹配出多个相对应的结果值。

延伸类型

①逆向查找

表达式：=vlookup(查找值，IF（{1,0}，查找值所在列，结果值所在列）,2,0）

逆向查找其实是构造出新的查找区域↑

例子：你有一个儿子叫哆啦A梦，你去到学校找儿子的，门卫要查找以为名叫哆啦A梦的学号，才能知道儿子在哪个班。

=VLOOKUP($M8,IF({1,0},B:B,A:A),2,0)

因为有数组输入后，需要同时按下，ctrl+shift+enter

得：={VLOOKUP($M8,IF({1,0},B:B,A:A),2,0)}

延伸阅读：

逆向查找在更多工作的时候，直接构造出辅助列，这样可以沿用原来的正向查找的方法，所以我们要学会灵活多变。

②多重条件查找

查找值合并成1，构建新的查找区域↑

E04.可视化表达

许多小伙伴经常来问一些图表是怎么做的，他们是分不清数据可视化的形式，有些小伙伴不知道条件格式、迷你图、更不用说字体和函数表达滴。本人总结出数据可视化表达的常见几种形式~

E04-2.常用的基础图表含义

我们日常工作中90%都是由基础构成的，掌握这几种基础图表的表达和理解，就能满足使用图表的90%的需求。

Excel常用的基础图表都在里面了

E04-3.动态图表的原理及表达

动态图表是Excel最吸引人的地方，不少人以为动态图表只有数据透视表+切片器才能做，其实不是滴。本小章节带领大家认清楚动态图表的原理，以及各种动态图表的基础构成。

00动态图表原理

因为Excel没有时间轴，无法描述运动轨迹，只能表达图表的最终形态。简单来说动态图表就是作图数据源发生了变化。

在Excel中常见的动态图表元素组合有：选择器+抽数引擎+图表=动态图表

①选择器常见有Excel控件，下拉菜单，单选框、复选框、调节数值器、切片器、日程表等；

②常见的抽数引擎有：透视表、常见的链接、引用函数如：vlookup，offset，index与match等

以下，我们根据选择器的不同，盘点各类动态图表：

01randbetween函数

由randbetween构造的数据源，我们刷新时，作图数据源就能随机变化，从而形成动态图表。

大家看到我大多数的动态图表，都是由randbetween函数构造的。

02下拉序列

我们通过【数据验证】→【序列】就能构建出下拉序列，如图：

通过下拉序列的单元格值，利用vlookup求出1-3月对应产品的销量，利用图表展示数据，通过拉下序列切换形成动态图表。

03透视表+切片器

透视表和切片器是最好的分析组合，我大多数【数据看吧】作品都是利用透视表+切片器的。

作品示例：

04透视表+日程表

日程表对于日期分析非常好用，因为能自动根据日期、月、季度、年等多个日期组合。

Excel控件动态图表

接下来的都是设计Excel控件的动态图表，Excel控件是在【开发工具】→【插入】。

如果没有【开发工具】菜单，记得在【文件】→【选项】→【自定义菜单】，把【开发工具】勾上即可。

因为Excel控件都是漂浮在单元格之上的控件，我们需要把控件的数值链接到单元格，所以这些控件都要设置链接单元格。05下拉菜单

控制关系链：下拉菜单→链接单元格→联动作图数据源=动态图表

06复选框

控制关系链：复选框→链接单元格→联动作图数据源=动态图表

复选框：多个选框控制多个单元格。

07单选框

控制关系链：下拉菜单→链接单元格→联动作图数据源=动态图表

单选框：多个选框控制一个单元格。

08调节数值器

E05.数据透视表

终于要说到最后一个了，个人认为数据透视表在目前来说称得上为Excel中最强大的功能，首先看看图片放松一下吧。

数据透视表的作用实在太多了，个人感觉再多的作用和功能都是围绕着【数据分析】这个点，其中【快速汇总、统计】这个作用最强大与核心，之后的所有作用均围绕着不同条件下统计汇总数据。例如按照【动态排名】、【分组】、【拆分】等都是统计的条件。

下面我就简单过一些数据透视表有哪些功能吧↓

E01-.制作数据看板

制作数据看板是对数据透视表掌握的综合考核，主要涉及到【切片器】【透视图】【图表应用】【统计汇总】等功能的。为什么最难放在前面，因为是为了想勾起你们的兴趣，O(∩_∩)O哈哈~

E05-1.快速汇总数据

选中数据源，建立数据透视表后。分别拖动【城市】【月份】字段到【行】【列】区间，最后拖动【销售价】到【值】区间，即可快速汇总统计数据。

E05-2.拆分工作表

日常你还在一个个滴拆分每个工作表分发给各子公司么，学会这个即可一键拆分工作表。把【城市】拖到【筛选区】，选择数【据透视表工具】→【分析】→【选项】→【显示筛选页】选择即可根据对应的每个城市拆分工作表。

E05-3.二维表转一维表

依次按下Alt D P键盘（不能一起按），调出数据透视表向导。【多重合并计算】，【单页字符】，选择数据源，接下来就按确定即可。

建立数据透视表后，双击最后汇总项，从而得到一维的数据源。

E05-4.动态排序

在行标签的数据项，【排序】【其他排序】，选择根据【销售额】降序从大到小排序，当数据源发生变化后，排序依旧根据销售额大小排序。

E05-5.图形图表交互

这个就是一种小的交互图表，多图表组合就成为数据看板。

E05-6.数值分组统计

如果是数值或日期在行标签列，可以根据组合汇总进行自动的分组。【分析】【创建组合】根据步长和起点值、终点值设置。

E05-7.返回特定数据源

只需要在透视表统计的数值双击，就能返回对应的数值。

E05-8.跨多表汇总统计

也是按住依次按下Alt D P键盘（不能一起按），调出数据透视表向导。【多重合并计算】，【自定义字节】，依次选择【1月-6月】工作表的数据源分别添加，最后根据实际，选择【3个字段】分别命名后，确定即可。

以上的作用是个人日常使用数据透视表用得比较多的地方，需要掌握更透彻，需要继续学习更多知识。自己也可在日常工作中总结，改进解决问题流程或办法，提供效率。

能看到这里的小伙伴实在太太太厉害了，2020定会爱情事业双丰收滴，别忘了点赞哦~

作者：运营菌链接：https://www.zhihu.com/question/23991984/answer/902705116来源：知乎