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白银级配色
02.例如这就是选中第一个的样式的效果
02黄金级配色
01.不同色系表示不同数据系列懂得选择同一饱和度数的颜色，在填充颜色的【主题颜色】，同一行的颜色代表颜色饱和度是相同的。所以我们选择区分不同系列选择颜色，饱和度相同的不同色系。
02.饱和度不同表示程度深浅
我们在配色的相同的列，就能选择不同的饱和度的颜色，代表同一事物的深浅。
03白金级配色
01.懂得利用颜色主题的系统搭配颜色
点开【页面布局】【主题颜色】就能变换多种主题颜色配置。系统原来就为我们搭配了许多配色方案，相对默认的配色。这些配色色系上还是不错的。
注意：配色的核心是使用“主题色”，而不是采用标准色，标准色是你换任何配色方案都不会有变化的，当然自定义的颜色也跟标准色一样，不会随主题的改变而改变。
04钻石级配色
01.自定义主题颜色设置
点开【页面布局】【主题颜色】拉到最后的【自定义颜色】就能自己设置颜色了。
许多同学不会自己配色的，那么最快捷的方法就是参考别人优秀的配色方案
05寻找配色方案
01.参考专业的图表网站：如echart
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，构建新的查找区域↑
延伸阅读
如果多重条件查找返回的结果是数值，可以通过sumifs函数来匹配出结果值。
如数学成绩则表达为：
=SUMIFS(F:F,B:B,M14,C:C,N14)
更多的教程内容请看详细教程《零基础180分钟快速入门Excel2016》
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个字段】分别命名后，确定即可。
以上的作用是个人日常使用数据透视表用得比较多的地方，需要掌握更透彻，需要继续学习更多知识。自己也可在日常工作中总结，改进解决问题流程或办法，提供效率。
