《21天Excel新手到高手》带你玩转Excel

本思维导图是在本人学习慕课网上秋叶老师的《21天Excel新手到高手》精品课后，花费大量时间进行整理和部分内容补充后得到的。如果你的excel不太熟练，用excel是感到无从下手，那么此知识导图一定对你有很大的帮助。

21天Excel新手到高手

一、技巧篇：快速数据选择、输入数据的秘诀

1.掌握4种方法，选择数据快的不是一点点

1）鼠标拖拽法

连续单元格

方法一：鼠标放在第一个单元格，然后按住左键不放，将鼠标拖拽到最后一个单元格

方法二：鼠标单击起始单元格，然后按住Shift键，将鼠标放到最后单元格

非连续单元格

加上Ctrl键

2）快捷操作法

连续单元格

方法一：Ctrl加方向键，即可跳转到相应边缘。

方法二：鼠标移动到单元格边框，出现十字光标时，双击即可跳转到边缘

同时按住shift即可选择连续单元格区域

在单元格任何位置按住Ctrl+Home（End），即可返回到A1单元格（该区域的最后单元格）

在单元格任何位置按住ctrl+A即可全选整个单元区域

职场细节

需要外发给别人查看的Excel文档，在完成内容后，随手按下Ctrl+Home键，回到A1单元格，方便阅读者从头查看

3）定义名称法

非连续单元格

选择单元格区域，在名称框中输入定义名称，按回车确定。之后只需在名称栏中找到或输入对应名称即可选中命名区域。

注意

在单元格命名中，不能出现A100,B35这种名称 (原因是因为在Excel中每一个单元格区域，都是由字母和数字两个位置共同完成的)

4）条件定位法

Ctrl+G打开定位面板，选择定位条件。

2.学会3种操作，高效查看数据

1）冻结拆分窗格

冻结首行（首列）：点击视图，冻结窗格—冻结首行（首列）

冻结多行（多列）：在需要同时显示的单元格下方任意单元格位置，点击然后冻结窗格

冻结行和列：选中行和列方向交叉位置单元格，点击冻结窗格

技巧：在excel中按ALT，此时excel菜单栏出现快捷键字母按钮，按下对应的快捷键即可快速进入相应的功能（如冻结窗口：ALT+W+F+F\R\C（即冻结内容））

2）数据内容查找

在开始菜单栏中找到查找和选择，点击查找，调出查找对话框（或ctrl+F）

如果想对所查找的内容进行标注的话，点击其中一个，按ctrl+A全选，关闭对话框，进行加粗或颜色调整

3）数据内容筛选

筛选条件调出：选中表格需要筛选的标题单元格，点击排序和筛选中的筛选（或ctrl+shift+L）

大于小于等筛选：点击筛选小三角，找到数据筛选，即可实现一定范围内的数字筛选

快速将表格回复原状：菜单栏找到排序和筛选下面的，点击清除选项

3.图片PDF中的表格，如何快速录入Excel

微信小程序：微软AI识图

识别后的内容复制后发送到文件助手，电脑端复制到Word，选择数据部分，点击插入表格，文本转换成表格，设置行列，点击确定，然后复制内容，回到excel中，简单调整一下，对照数据，美化一下即可。（标题加粗，绿色底纹，字体反白处理，正文字体微软雅黑）（亦可以给列数据添加条件格式，让数据看起来增长分明）

其他程序推荐：扫描全能王，白描APP，Acrobat，泰比(ABBYY)

4.如何多人协同完成Excel表格数据的快速收集

1）识别两种类型表格的特征

信息收集型数据

特点

侧重于标准化字段的数据收集

例如

个人信息统计，调查问卷等

内容创作型数据

特点

侧重于团队之间内容的协作，共享，修改等

例如

多部门多地区的计划提交，报告汇总

2）信息收集型数据的获取

工具：问卷系统（问卷星，金数据，麦克CRM，腾讯问卷(wj.qq.com)等）

3内容创作型数据的获取

工具：问卷系统（腾讯文档（docs.qq.com））

5.外部数据文件，如何批量录入Excel

1）出现的问题

（1）复制粘贴之后堆成一团，处理起来太麻烦

（2）用excel打开格式不对，每月都要更新

（3）每次都要打开网站，还要处理各种格式

2）操作分类

（1）TXT数据导入excel及精加工

找到数据选项卡，单击获取外部数据中的自文本选项卡，找到文件并导入，选择分割符号分割，选择其他（/），点击下一步，点击完成。出现导入数据面板，数据的放置位置选择一个单元格，点击确定。出现短横线，按CTRL+H查找替换，短横线替换为（不填），点击全部替换。在开始选项卡中点击套用表样式，选择合适的即可。

注意：有时候我们在导入文本文件会出现中文乱码，一般是由于文本文件的编码格式不是ANSI所导致，这个时候只需要使用TXT文档，记事本文件打开后，点击文件另存为，在另存为处将编码改成ANSI格式即可

（2）CSV数据导入excel及精加工

找到数据-新建查询-从文件-从csv，找到已经下载好的csv文件，点击导入，弹出窗口，点击数据转换选项，进入到Power Query中，对数据进行编辑和查看，对照数据，如果日期是数字格式，选中列，修改为日期格式等

（3）网页数据导入excel及自动更新

查询往日天气，使用天气后报网站，复制网址信息，回到excel中，点击数据-新建查询-其他来源-自网站，在弹出的对话框中粘贴网址信息，点击确定。在导航器中出现导航器选项，选择需要信息，点击加载（设置为实时更新，即可获得最新天气信息。首先点击天气表格信息，会发现在菜单栏中多出表设计和查询两个下选项，在表设计中有一个刷新选项，点击刷新下方小箭头，点击链接属性，选择允许后台刷新，打开文件时刷新数据）

6.善用填充工具善用填充工具，录入数据从此告别手工敲击

1）数据序号三种填充方式

方法一：单元格右下十字光标出现，下拉至所需位置，此时填充的均为相同的，之后在十字光标处选择序列填充即可改变

方法二：先填前两个数据，之后选中两个单元格，十字光标，下拉，excel自动按等差数列进行处理（或直接双击十字光标（需此列至少一边边均有数据，否则系统无法识别，无法填充））

方法三：在单元格填入序号后，点击单元格，单击开始-填充-序列填充，在弹出的序列面板中填写所需即可（此方法可以在两边无数据进行）

2）指定条件日期列表生成

在日期序列输入日期信息，打开填充-序列面板-列方向，选中日期后，点到工作日，终止值设置为月末（数据要采用日期的形式）

3）自定义序列的数据生成

先人肉输入一遍，找到文件-选项-高级-编辑自定义列表（有些没有，可跳过）-自定义序列，选择刚输好的数据单元格，单击导入，点击添加即可

4）自带光环的智能填充使用

提取混排的文本和汉字：复制第一个文本，填充到单元格，选则下一个单元格，按智能填充（CTRL+E）即可

实例：从身份中提取出生年月：1.在出生日期位置根据身份证信息，输入第一个人员的出生日期信息。2.鼠标移动到下一个单元格区域，按ctrl+E键。3.抽取些进行验证正确性

excel的智能填充不仅可以提取身份证中的出生日期外，还可以提取混排的文本和汉字

7.活用快捷键与公式，数据输入效率倍增

1）快捷键实现数据的批量输入

按CTRL+G调出定位面板，定位需填写内容的区域（如：定位条件为空值，即可选出未填写区域），在编辑栏中输入内容，按CTRL+ENTER实现批量输入填充

快速搞定合并单元格的拆分操作：首先拆分合并的单元格，接着使用定位功能，选择空值，然后在第一个高亮单元格区域输入“等号”等于上一单元格，接着按CTRL+enter实现批量填充，即可实现将每个合并的数据分别粘贴到相应的拆分的区域

2）自定义格式让录入省时省力

方法一：如要填入01，02等数据，将单元格格式改为文本，然后用填充操作即可

方法二：选择序号列，右键设置单元格格式，点击自定义，输入00（每个0代表一位），点击确定，这样前面的1，2，3就会变成01，02，03（如果人员增加到三位数，只需要输入000即可）（针对前缀也是一样，输入“SCP-“000即可（注意中文需要带双引号））

优点：除此之外还可快速改变数据的大小写，同一数据格式

单元格格式快捷键（CTRL+1）

3）活用公式快速生成序号值

方法

应用if函数，判断姓名单元格是否为空

ROW（行）函数的作用是返回一个引用的行号，参数是求取于行号的单元格区域，例如ROW（A1）返回值为1

实例：=if(b2<≥"",row(a1),"")

在一个单元格输入后，可进行填充操作

8.用好复制粘贴用好复制粘贴，轻松搞定数据调整

1）调出面板：选择需要复制的区域后，点击开始-粘贴下拉-选择性粘贴（或快捷键CTRL+ALT+V）

2）功能

（1）选择性粘贴实现公式转化为数据

在粘贴方式中选择数值，即可将公式产生的数据固定下来

原因：用公式进行数据运算所得到的结果，不会受筛选等操作的影响，即公式是固定在各个单元格中，只有将其变化为数据，才能脱离单元格公式的束缚，才可进行筛选等操作

同样前面由于粘贴导致的格式混乱等问题，也可用选择性粘贴将所有改为数值解决

（2）选择性粘贴实现单元格行列转置

转置

选择转置，即可将列数据复制到行

跳过空白单元格

选择跳过空单元格，即可实现多行数据数据合并

此功能可以当复制的源数据区域中包含有空单元格时粘贴时空单元格不会替换粘贴区域对应的单元格

（3）选择性粘贴实现批量修改数值及数据核对

在空白单元格输入要增减乘的数目，进行复制，选择需要运算的单元格，在选择性粘贴对话框选择运算篮中加（减，乘）按钮，即可实现数据的批量改变

扩展：如果想知道两列数据有那些不同，将两列数据相减，查看不为0的数据即可

9.用好条件格式，为Excel找一个智能助理

1）概述

什么是条件格式？：条件+格式=规则

让符合条件的单元格，显示为预设的格式，根据条件使用数据条，色阶，图标集，以突出显示相关单元格，强调异常数值，以及实现数据可视化的操作。

excel的条件格式分类

突出显示单元格

最前/最后

数据预警可视化范畴

数据条

色阶

图标集

自定义

使用：选中需要设置条件格式的区域，点击开始-条件格式-选择对应的条件模块即可

2）功能

（1）条件格式标记重复值及特定条件值

选中姓名列后，突出显示单元格规则-重复数据，在弹出的对话框中设置重复值显示方式即可

突出显示单元格-内容输入（或文本包含）-输入需标记的内容，即可实现批量的标记（如果选取整个表格，则不仅目前，以后输入的也一样会显示）

（2）条件格式高亮显示数据最高最低值

突出前和后几位：条件格式-项目选取规则-最前/最后-前十项-将十改为应填数据借口（后几位同样操作）

用高于平均值衡量：最前/最后-高于（低于）平均值

将前（后）10%表示：最前/最后-高于（低于）10%

（3）借助公式实现临期数据自动提醒

选中整个单元格，开始-条件格式-新建规则-使用公式确定要设置格式的单元格，在公式对话框中输入=today()-日期单元格（记得去除列锁定，这样才能使用列填充）=0，设置格式，完成 （亦可以在产品临期提醒，员工生日提醒等中使用）

调出具体年月日日期的方法

today()是当日完整日期，year（today()）是当日年日期，month(today())是当日月日期，day(today())是当日日日期，以此类推，可得当日或表中日期单元格的年月日单项

例如：用出生日期设置临期提醒的公式为=year(today())-year(出生日期单元格)=年龄所在单元格，或=if(month(today())-month(出生年月单元格)=0,if(day())-day(日期单元格)=0,0,1),1)=0

10.Power Query处理多工作表数据汇总

概述

何为Power Query？

正在连接和转换excel中的数据：1.连接，2.转换，3.组合，4.共享

认识Power Query组件及使用入口

选择数据-获取数据-选择对应的文件数据

Power Query实现多工作表数据汇总

导入数据后弹出导航器对话框，其左侧显示了所有工作表的表明，既可以单击选择，也可以多项选择，单击转换数据，将数据加载到power query编辑器，右侧应用步骤只保留源区域，按CTRL选择name和data，然后单击删除列选项下的删除其他列，然后点击data下的按钮，在弹出的对话框中选择使用原始名作为前缀勾线掉，选择将第一行作为标题，删除右侧更改的类型，同时吧这个月份的一月修改为月份，删除不需要的表格区域，对编号做一个降序排列，将名称等删除，之后做升序排列，使其回复原装，将数据加载到excel中

当原表数据发生改变时，只需刷新一下即可获取最新数据

11.Power Query动态处理多文件汇总

使用Power Query 加载多文件数据

在空白工作簿，在数据选项卡中选择获取数据，来自文件夹，浏览选择目标文件夹（或）

在Power Query 数据编辑器中进行数据整理

处理沉余数据信息后上载数据至excel

12.活用页面设置做好Excel文档输出打印

常见问题

1.打印时表格列不能显示在同一页怎么办？

2.怎么设置才能只打印需要进行打印的区域

3.打印表格前，需要对页面进行设置吗？

4.在打印时如何保证每一页都显示表头内容？

5.如何快速实现报表的自动分页打印效果？

1）打印页面的基本属性设置

打印预览界面（打印快捷键Ctrl+P）

2）选择设置打印区域打印

选中打印区域，单击文件-打印-找到打印区域-选定打印选定区域

3）打印每页带表头的数据表格

当工作表过长时，把打印的工作表设置为将所有的列调整为一页，这样这张工作表就可以分布在多张纸上了，

在打印的每页都带上标题

页面布局-页面设置(有些没有，直接点打印标题即可)-打印标题-顶端标题位置选择我们需要打印的标题内容-单击确定

4）借助分类汇总实现自动分页打印

实例：将每个销售员的销售产品，金额进行汇总，并分页打印个人销售员的情况

步骤一：选中需要进行拆分的销售员这一列数据，单击排序选项（目的，在分类汇总之前，将汇总的数据进行排列，防止汇总数据不准确）

步骤二：选中需要汇总数据区域任意单元格，依次单击数据分类汇总，在弹出的对话框中，分类字段选择销售员，汇总方式选择求和，选定汇总项选择销售金额，同时因为要求分页打印，所以要勾选下方的每组数据分页，单击确定

步骤三（可选）：页面布局-页面设置-也秒打印-设置为顶端标题

二、效率篇：事半功倍的小技巧

1.输入身份证总是乱码怎么办？

1）身份证号码准确录入

原因：excel只能识别15位数据，因此输入后再改变文本格式无法达到目的，同时15位之后的数据均变为0.（注意：这种错误是不可逆的，因此输入数据时要注意先调整单元格格式，再输入数据）

解决方法：选中需要输入身份证号码的区域，设置成文本格式，然后再去输入身份证号码（完成后数据左上角出现绿色小三角，点击出现感叹号，选择忽略错误即可去除小三角）

2）数字格式的样式设置

设置单元格格式（Ctrl+1）

选中需要调整的区域-设置单元格格式，根据需要进行选择小数位数，数字或中文等格式（在特殊中可以修改中文大小写）

3）日期格式的样式设置

选中数据，设置为短日期或长日期

从设置单元格格式中调整

4）自定义格式样式设置

实现不同位数数据对齐

：打开设置单元格格式-自定义-输入几个0就代表几位数

电话号码分段显示

自定义-输入例如“0000-0000-000”

通过代码输入性别

自定义-例如输入[=1]"男";[=0]"女"（相当于if判断，[]为判断条件，之后为判断输入值）

2.收集信息总是出错怎么办？

标准数据录入表要求

特定输入下输入符合要求的数据

一级下拉菜单的制作

先制作每个标题下能填写的数据，制作选择区域

1.选中需要填写数据的区域，找到数据-数据验证（有效性）-序列-来源区域-选择制作的区域，即可再每个单元格通过下拉列表选择输入信息（无论是数字，还是文本，均可选择序列完成选择输入）

2.数据验证-输入信息（或提醒和警告）-勾选选定单元格时显示输入信息-可以输入当选择数据时出现文字（或出错时的提醒警告）

功能

特定大小位数的录入

选中需要填写的区域，找到数据-数据验证（有效性）-设置-整数-选择可输入数据范围

也可设置输入信息以及出错警告等

特定文本长度的录入

选中需要填写的区域，找到数据-数据验证（有效性）-设置-文本长度-设置文本的长度（可用于防止身份证或电话等输入位数错误）

3.批量插入图片如何操作最快？

步骤

（1）设置excel表格属性

对姓名这个区域进行排序，这样excel和文件夹中照片形成一一对照的形式，调整行高和列宽，使其能匹配照片

（2）批量导入图片素材

选中需要插入图片的第一个单元格区域-插入图片-全选图片-插入

（3）姓名与图片区域匹配

-再图片格式中调整图片大小-将第一个图片放置到位-将最后一张也放置到位-同时选中第一张与最后一张进行水平居中对齐-调出定位条件-对象-选中所有图片-图片格式-对齐和分布-左对齐-纵向分布

（4）设置图片大小位置属性

定位条件-定位对象-右键设置图片大小和属性-随单元格改变位置但不改变大小（或选择嵌入单元格也可达到同样效果）

4.重复信息如何快速标记删除筛选及统计

快速标记重复信息

选中需要查看重复的内容后，将鼠标移到单元格区域，右下角出现小图标，单击之后，在格式化中选择 重复的值，即可快速标记出带有重复值的内容（注意：重复项，只有在非数据内之中才会出现，如果选中的位置有数字，日期等数据，则该位置不会出现重复值标记选项）

可以在条件设置-设置突出选项-重复值，实现同样的效果

快速删除重复信息

数据-删除重复值-选择需要作为删除信息标记的列

快速筛选重复值信息

选择数据后，单击数据选项卡排序筛选中的高级选项，勾选在原有区域中显示，列表区域和条件区域均选择数据区域，勾选选择不重复的记录，点击确定（仅是筛选出，并没有删除）

快速统计重复值信息

选择表格任意区域，单击插入下的数据透视表，在弹出的对话框中，直接点击确定，弹出新表后，将学历拖拽到行和值，即可达成统计相同学历人数目的。

5.动动手指轻松搞定数据拆分合并

使用分列功能完成规律数据拆分

按分割符号

选择需要拆分的区域，在数据-分列选项-文本分列向导对话框-选择使用分隔符号-选择分割标志-确认预览无误后点击完成

按固定宽度

选择需要拆分的区域，在数据-分列选项-文本分列向导对话框-选择使用固定宽度分割-在数据预览中用鼠标直接选出分割区域-选择需要导入的列和数据格式-确定导入（如从身份证中提取生日）

使用分列功能完成文本的数据化 (实际上使用的是分列中的数据格式功能)

文本金额转数值

选择不规范数据，使用分列功能直接确定即可

不规范日期转规范

选择不规范数据，使用分列功能-在列数据格式位置设置为日期即可

数据合并

使用&连接符实现数据合并操作

使用shift+7（&键实现），选择需要输入合并数据的单元格，输入等号，点选各个需要的单元格区域，用&连接，即可实现

使用TEXTJOIN函数实现多文本合并

选择需要输入合并数据的单元格，输入等号，输入TEXTJOIN，第一个用英文状态下的双引号输入需要插入的符号，第二个选项选择忽略空单元格，第三个选则需要合并的区域（=TEXTJOIN（"、",TRUE,C3:C11））（注意：此方法只能OFFICE2019以上版本才能使用）

区别：&无法使用分隔符，是直接连成一句，而text join可以加入分隔符

6.用好智能填充从此告别文本拆分函数？

提取数字和字符串

在目标单元格内输入需要提取的部分，在下方单元格按下CTRL+E，即可快速提取整列的信息

提取合并一步实现

在目标单元格内输入需要提取合并的部分，在下方单元格按下CTRL+E，即可快速提取合并整列的信息（例如：张三丰，真人可合并成张真人）

调整字符串的顺序

在目标单元格内输入调整正确顺序的部分，在下方单元格按下CTRL+E，即可快速改变整列中的信息顺序

从身份证提取出生日期

在目标单元格内输入需要提取的部分，在下方单元格按下CTRL+E，即可快速提取整列的信息（即提取数字，故应先提取数字，然后使用分列功能将其转化为日期）

7.Power Query瞬间搞定数据逆透视

一维表和二维表的区别：一维表是单方向即可确定数据，二维表是横纵两个方向共同确定数据

第一步，将数据导入PQ编辑器

选中数据源任意单元格，点击数据-获取和转换数据-自表格区域-点击确定进入PQ编辑器

第二步，逆透视数据列

选择需要作为分类项的列-单击转换-逆透视列

第三步，修改标题及数据类型

修改各项标题名称，把日期，数字等数据从文本改为相应的日期，数字等格式

第四步，将数据上载回Excel

点击主页-关闭并上载

8.Power Query实现数字文本复杂数据处理

第一步，将数据导入PQ编辑器

第二步，拆分信息字段

选择需要拆分数据的列-单击拆分列-按分隔符，清除默认的分隔符，高级选项修改为行，使用特殊字符进行拆分，选择特殊字符，点击确定

第三步，透视列完成数据转换

点击仍需要拆分的字段，进行拆分。

选择应为标题的列，点击菜单栏转换-透视列，在值列选择标题下的数据列，点击高级选项，在聚合函数中选择不要聚合，点击确定，即可实现数据整理

修改各个字段名称和类型

第四步，将数据上载回Excel

优势：数据有增加或减少时，刷新数据即可

9.Word和Excel联合三分钟制作1000张奖状

邮件合并功能

第一步，准备模板文件

将奖状模板（word）和人员信息（excel）放在同一个文件夹中，方便合并邮件时调用

第二步，开始邮件合并

打开“奖状”模板，单击工具栏的邮件（或引用-邮箱），开始文件合并-普通word文档（目的：确保最后制作出来的文档格式）

第三步，导入数据到word

单击选择收件人-使用现有列表-选择已经保存在本机的数据源-选择名单表，预览时看看内容是否正确，然后单击确定即可

第四步，插入合并域

把需要变动的内容删除并插入对应的合并域数据

在需要变动的地方选择插入合并域

第五步，完成并合并文档

单击完成并合并-编辑单个文档-合并全部

预览无误后保存

三、函数篇：函数公式入门到进阶

1.掌握函数基本语法，学会和Excel高效对话

了解公式和函数概念

公式：以=为引导，通过将运算符，函数，参数等按照一定的顺序组合，进行数据运算的处理方式

函数：excel内部预先定义，并按照特定顺序和结构执行运算任务的功能模板，是一个个分装好，用来简化操作的公式

初次体验函数公式

选择需要求和的区域，点击公式-函数库-自动求和（也可在单元格输入=SUM（）进行求和）

函数公式注意事项

1.无论是手动输入，还是通过菜单栏的函数库插入的函数，均是以等号开始，以括号结束

2.函数括号内的内容称为函数的参数，参数之间用英文逗号分隔（同时用方括号显示的参数为非必须参数，可以不填）

3.函数的输入字母不分大小写，输入完成后excel会自动的将小写的字母转化为大写的字母

4.函数中的所有符号都是英文状态下的（即英文半角状态）

单元格引用类型

相对引用

从属单元格和引用单元格的相对位置，也就是行列的位置，保持绝对不变

即：相对引用的单元格地址会变

例如：E4*F1

绝对引用

不管公式引用到哪，所引用的单元格地址绝对不会发生改变

例如：E4*$F$1（所用F1即不会发生改变）（输入方式，选择对应数据，按F4（或FN+F4））

2.使用SUM函数完成各种数据求和

一键快速求和

选择表格区域和需要放置求和值的区域，然后使用快捷键ALT+=即可

注意：每个单元格选取一次，不能重合选择，否则无法识别并求和（因为一件求和就是智能填写公式）

多数据批量求和

选择表格区域和需要放置求和值的区域，按下Ctrl+G键，调出定位面板，定位空值，接着按求和快捷键，即可实现

多表快速求和

在需要填写的单元格输入公式，用shift键选中需要汇总的表格，接着点击需要求和的单元格，完成公式即可（此操作是相对引用，可进行填充）

数据累加求和

选择需要填写的单元格，输入公式（注意：累计求和相当于固定初始位置值然后进行依次累加，如$B$2：B2），其余位置进行填充即可

3.利用函数公式，轻松搞定比赛计分和排名

RANK(NUMBER（需要排名的数值）,REF（排名的数值区域）,[ORDER]（排名的方式）)

公式的结果为当前需要排名的数值在数值区域中的排名，排名的方式可按升序或倒序排名，当第三参数省略或为零时，表示数值按降序排名，数值越大排名越靠前，当第三参数不省略或数值不为零时，表示数值按升序排名

例子：RANK（O3,$O$3:$O$25,0）（故可进行填充操作）

优点：当出现数据变动时，不需要进行操作

4.核对数据必备：VLOOKUP函数

函数作用：查找与引用，在数组第一列中查找，然后在行之内移动返回单元格的值

VLOOKUP基本语法规则

VLOOKUP(lookup_value（要查什么）,table_array（在哪查）,col_index_num（查到了要哪部分）,range_lookup（精确查还是模糊查）)

要保证查找值在查找区域的首列

VLOOKUP的精确查找

例如：VLOOKUP(C3,$J$2:$k$13,2,FALSE)（因为固定了查找范围，所以可进行填充）

应用范围：统计产品销售量时按单价表填入对应单价

VLOOKUP的近似查找

例如：VLOOKUP(F3,$M$2:$N$8,2,TURE)

应用例子：通过各级数据范围进行评级操作

5.VLOOKUP数据验证制作交互式图表

做一个辅助数据，复制原始表头，然后粘贴到下方空白位置，接着利用数据验证功能，制作产品的下拉选项（点击单元格-数据-数据验证-选择序列-来源选择复制的表头单元格区域）

接下来输入VLOOKUP公式，根据产品名称批量查询所需数据， 例如：VLLOKUP($B$15,$B$2:$N$12,COLUMN(B1),false)

COLUMN函数作用：获取单元格的列号(即返回单元格所在的列标对应的数字A-1,B-2等）（即COLUMN(B1)中实际有用只有B，返回值为2）

选择做好的辅助数据，点击菜单栏-插入-选择需要的图形，调整图形的位置，使其符合要求即可

6.随机抽奖这样玩：Rand函数

将人员名单放置在A列，然后在B列使用RAND函数生成随机数，作为后面数据排序的依据。

具体操作：

1.在单元格输入RAND（），按下回车键生成一个随机数，选中该单元格进行填充（这个函数不需要任何参数，作用是返回大于等于0及小于1的均匀分布随机实数，并且RAND在每次计算时都将返回一个新的随机实数，因此可以按F9对数据进行刷新操作）

2.接着在人员列插入一列数据，利用RAND函数进行排名操作，作为辅助序列（RAND的作用是求某个数值在某一区域内的排名，在此我们使用的是刚刚生成的随机数的排名信息。）输入RANK函数，第一个参数为排名的数字，第二个参数为参与排名的区域，第三个区域为排列方式（例如RANK(C2,C;C,0)）

3.选择单元格制定好中奖人员的标题后，设置中奖人数序号，使用VLOOKUP函数将姓名提取出来，第一个参数为填充序列信息，第二个参数为左侧的排名和姓名列数据，因为需要返回姓名，第三个参数为2，第四个参数为精确匹配。最后进行填充即可

4.此时已制作完成，抽奖时一直按F9，停止的一刻即是中奖的人员

7.让Excel自动判断：IF函数

IF函数基本概念

IF函数是逻辑函数，判断是否满足某个条件，如果满足，返回一个值，如果不满足，返回另外一个值

IF(logical_test（条件判断）,value_if_true（结果为真返回值）,value_if_false（结果为假返回值）)

IF函数单条件判断

例如：IF(A2>=60,"及格","不及格")

两个IF函数的嵌套

IF函数最多可以嵌套64层

例如：IF(A2>=60,IF(A2>=85,"优秀","及格"),"不及格")

贴士：在应用多个IF函数时，尽量使用相同的比较方式，结果也按升序或降序排列。是一个好习惯

if函数不能出现200<B2<500这样的形式，否则无法进行判断并填入数据

IF函数综合使用

防止填充时空单元格也进行的操作：增加IF函数判断其是否为空 （例如IF(B2<>"",IF(B2="本地",300,800),"")），此操作的优点是可在后续进行数据添加时自动判断并加入数据

8.自动统计重复次数：Countif函数

有时要求输入的数据不能重复，例如员工工号，电话号码，身份证号码等，为避免输入出错，采用数据验证中自定义的方式让excel自动对重复数据进行核对检查。

COUNTIF函数概念

COUNTIF是一个统计函数，用于统计满足某个条件的单元格数量

COUNTIF(range（需要在哪些区域）,criteria（查找哪些内容）)

参数一range要计算其中符合条件单元格格数目的区域；参数二：criteria以数字表达式或文本形式定义的条件

例如：COUNTIF(B:B,E2)

如何同时设置多个限定条件？

使用判断函数

AND函数是满足所有条件返回为true，否则返回为false

OR函数是至少满足其中一个条件即返回true，否则false

LEN函数是返回字符串的位数，可以计算文本的长度，数字的位数

例如：请在限定录入的基础上，新增加一个条件电话号码必须是11位。=AND(COUNTIF(B:B,B2)<=1,LEN(B2)=11)

使用

选择需要重复验证的所有单元格，单击数据-数据工具-数据验证-数据验证....（或数据有效性）-在设置中允许下拉列表选择“自定义”选项，同时选择忽略空值-在公式框中输入COUNTIF(C:C,C2)=1（C:C表示统计单元格的区域，可根据需要进行调整）

可以为了让提示更加人性化，修改刚刚做好的数据验证切换到出错警告选项卡中进行修改

四、图标篇：数据汇报好看又直观

1.图示化呈现，让数据一目了然

体验条件格式的使用

在excel中数据条有渐变填充和实心填充两种类型，各有六种颜色

选中数据区域，找到开始-条件格式-数据条-实心填充

条件格式的修改

单击开始-条件格式-管理规则-新建规则，在弹出的条件格式规则管理面板中找到数据条双击即可进行修改

也可在条件格式中的数据条中选择其他规则进行修改

色阶的使用

使用和修改方式和数据条一致

色阶作为一种直观的指示，可以帮助读者了解数据分布和数据变化，双色刻度是用两种颜色的渐变表示单元格区域的值，用颜色的深浅表示颜色的高低

内置的色阶条件格式，只能选择默认的色阶样式，如果需要设置个性的双色或三色，可在格式样式下来列表中进行调整

图标集的使用

使用图标集可以对数据进行注释，并可以按阈值将数据分为三到五个类别，每个图标代表一个值的范围。（例如，在三项箭头图标集中，绿色代表较高值，黄色代表中间值，红色代表较低值）

如果需要设置个性的图标形状和颜色，可以在格式样式下拉列表中选择图标集选项进行调整

2.突出显示重点，让异常数据无处可藏

区间数据标记

选中数据区域-开始-条件格式-突出显示单元格规则，找到对应的匹配规则

文本包含标记

选中文本区域-开始-条件格式-突出显示单元格规则-文本包含，然后输入文本，调整显示格式即可

前N项数据标记

选中数据区域-开始-条件格式-突出显示单元格规则-最前/最后规则选项-选择前十项（或后十项），在弹出的对话框中可对标记位数和样式进行调整

选中数据区域-开始-条件格式-突出显示单元格规则-最前/最后规则选项-选择前10%项，在弹出的对话框中可对标记百分比和样式进行调整

高于/低于平均值标记

选中数据区域-开始-条件格式-突出显示单元格规则-最前/最后规则选项-选择高于（低于）平均值，在弹出的对话框中可对样式进行调整

3.自定义规则，让数据更直观好看

甘特图：甘特图又称为横道图，条状图。是当前项目管理中，被广泛应用评估工作进度的科学管理方法、目前在IT，建筑，制造等行业应用颇为广泛

制作过程

1.构建基础数据结构

原始数据表中要包含任务，起始和结束日期，每日的日期（为避免甘特图过长，对日期在单元格格式中进行调整）

2.设置甘特图部分

选择需要制作甘特图的区域，开始-样式-条件格式-新建规则-在弹出的对话框中选择是用公式确定，在编辑规则中输入起始和结束日期（例如：AND(F$2>=$C3,F$2<=$D3)），点击格式，选择填充，调整格式，确定即可完成操作

3.设置当前日期显示

选择需要制作甘特图的区域，开始-样式-条件格式-新建规则-在弹出的对话框中选择是用公式确定，在编辑规则中输入日期（例如：F$2=today()），点击格式，选择填充，调整格式，确定即可完成操作

4.表格美化修饰操作

4.掌握六种基本图表，让数据汇报更有说服力

条件格式可视化效果仅仅局限于单元格中

图表选择

插入-图表-所有图表

常见的图表

柱形图

堆积柱形图

整体的各个部分之间的比较（如国内外销量和单国内销量的比较）

簇状柱形图

若干部分之间的比较

百分比堆积柱形图

比较各个值占总计的百分比。柱子整体高度代表100%，每部分高度代表了该部分的占比值

三维柱形图表达效果与二维相似，只是增加了三维效果

折线图

用多个线段将多个数据点连接起来形成的图形，以折线的方式显示数据的变化趋势。可以清楚的反映出数据是递增还是递减，增减的速率，规律，峰值等特征。因此，折线图通常用来反应数据随时间的变化趋势，与柱形图相比，更能突出起伏变化，也更适合数据点较多的情况

堆积柱形图

簇状柱形图

饼环图

以列或行的形式排列的数据可以绘制为饼图，显示出各项的大小与总和的比例，通常以一组数据为数据源，显示数据占该数据系列总和的比例值。通常用来反应数据在整体中的构成和占比情况

饼图类别一般别超过七个，否则会造成角度难以估计，不利于信息传递的准确性

字母饼图

显示整体的比例，从第一个饼图中提取一些值，将其合并在第二个饼图中，使较小百分比更具可读性或突出强调第二个饼图中的值

如果需要将饼图换成圆环图，只需要选中图表，右键在图标设计中选择更改图表类型

条形图

在工作表中以列或行的形式排列的数据可以绘制为条形图。显示各个项目的比较情况

其实是柱形图旋转得到，主要用于数值大小的比较，更适合展现排名，另外由于条形图的分类标签是纵向排列的，由此可以容纳更多的标签文字。

先对数据做一个降序排列，然后再进行制作图表

堆积柱形图

簇状柱形图

百分比堆积柱形图

三维柱形图表达效果与二维相似，只是增加了三维效果

散点图

在工作表中以列或行的形式排列的数据可以绘制为XY散点图，将X值放在行或列，然后再相邻的行或列中输入对应的Y值

也称为相关图，是一种将两个变量分布再纵轴和横轴上，再他们的交叉位置绘制出点的图表，主要用于表示两个变量的相关关系。

通常用于显示和比较数值

散点图：比较两组值或两组数据，但是不连线。带平滑线和数据标记的散点图或带平滑线的散点图，这种图表显示用于连接数据点的平滑线可以带标记，也可以不带，如果有多个数据点，使用不带标记的平滑线，带直线和数据标记点的散点图或带直线的散点图，此图显示了数据点之间的直线连接线，显示的直线可以带标记，也可以不带，只不过是把数据点之间的曲线换成了直线，散点图除了用于相关统计分析领域之外，往往还会作为高阶图表的一种重要的辅助元素，

雷达图

在工作表中以列或行的形式排列的数据可以绘制为雷达图，比较若干个数据系列的聚合值，一般用于并列的多个元素的对比，比如常见的个人能力素质项的评分，

雷达图

带数据标记的雷达图

显示数据相对于中心点的变化

填充雷达图

数据内有颜色

选择数据区域，单击插入-柱形图图表-在下拉列表选择使用的样式。确定后会发现菜单栏多出图标设计和格式选项卡，图标设计中有添加图表元素，快速布局，更改颜色等选项。如果对图表颜色仍不满意，可以选中图标后在格式选项卡，在形状填充中直接修改颜色

单击插入下的图表，查看所有图表，会贪图所有图表面板，单击柱形图，会发现柱形图包含很多类型

图表制作

图表调整

图表美化

5.玩转组合图表，轻松应对复杂的呈现需求

重要性

实际工作中，如果把不同类型的数据放在一张图表上展示，就需要使用不同类型图表构成的组合图表，同时由于数据的不同对比参照系，无法在一个坐标轴上体现，为更加清晰直观的显示，引入次坐标轴，采取双坐标组合图就显得尤为重要

步骤

实例

销售收入侧重数据大小对比，放在主坐标轴，采取柱状图；增长率同时注重增长率大小及变化趋势，放在次坐标轴，采取折线图

方法一

1.插入柱形图

选中所有数据，插入-柱形图，此时会发现表格中出现一个两类数据的柱形图，但是增长率数据非常小，呈现出来不是很明显。

2.修改次坐标轴

因增长率图线太小，很难直接选中，可以先选中图表，在单击主菜单栏格式下的当前所选内容下拉选项，点击系列增长率，即可选中。（或右键图表，选择数据，选择增长率，这部相当于筛选，完成设置次坐标后再恢复即可）

然后在选中的增长率图表上右键选择设置数据系列格式，设置在次坐标轴上显示，增长率按次坐标刻度显示柱状图，此时原来图表右边会多出一根百分比显示的坐标轴，同时原来显示高度很小的增长率也变的跟清晰，并且覆盖在销售收入系列上面

3.更改图表类型

由于增长率用折线表示更加直观，故选择用折线图表示增长率

在图表右键，更改图表类型，在组合图对话框中选择更改

4.表格美化修饰操作

方法二：选中数据，打卡插入图表，之间选择组合图，调整图表类型，主次坐标轴后插入即可

6.玩转创意图表，让汇报更形象与高效

1.构建辅助数据

辅助数据是由100%减去原数据得到一列与原数据互补的数据

2.插入圆环图

选中一对完成列和辅助列后，插入圆环图

3.调整图标格式

选中图表，右键单击设置数据系列格式（或双击图表），将圆环大小设置为60%（此时大小适中），删除图例和图表标题等多余元素，修改图表和绘图区大小，让圆环图充满整个图表区域，最后设置图表区和绘图区都为无轮廓无填充状态

4.美化图表

双击选中销售占比系列，进行颜色填充，同样可以调整其余颜色

单击插入选项卡下的文本框，在圆环图的中央插入文本框，然后选中文本框，在格式中将文本框设置为无填充无轮廓，在文本框中输入=B2，即可在修改原始数据时文本框内容随之变化（WPS的excel文本框无法输入公式）

制作完后可整体复制到PPT中进行使用

7.可爱又炫酷的mini图表，查看产品走势的秒杀技

要求：excel2010以上

类型

折线

柱形

盈亏

步骤

选中需要放置迷你图的单元格，插入-迷你图进行选择，在弹出的对话框中选择数据范围

点击迷你图位置，出现迷你图选项卡，可对迷你图进行调整（可改变图表类型和样式，标记出特殊点等）

可进行填充操作

选中迷你图，在迷你图菜单栏可进行清除迷你图操作

其实是插图到工作表单元格中的微型图表，可提供数据的直观显示，使用迷你图可以显示一系列数据的趋势。也可突出特殊值

与图表相比，它不是对象，实际上是一个嵌入在单元格中的微型图表

8.借助Power Map,Excel中也能玩转高大上的数据地图

概述

要求：excel2013需官网下载插件，excel2016以上内置功能模板

Power Map是excel内置的一个地理信息系统可视化工具，也是微软Power BI系列的四大工具之一

步骤

选中所有需要进行可视化的数据区域，插入-演示栏-三维地图，打开三维地图，进入三维地图编辑页面，自动识别位置信息，将产品信息拖入右侧高度菜单栏中，系统默认类型为堆积柱形图，可单击右上方将可视化更改为簇状柱形图选项，切换图标的显示类型

如果有时间数据，可完成动态时间模拟过程，可生成动态演示场景视频嵌入其他演示文稿中，实现动态演示

在主题菜单栏中可对样式进行修改

注意

本质是在线地图，使用时必须联网。对应的微软的必应地图

带有三维地图数据文件在2010及以下版本中打开的是无法显示三维效果的

9.开挂神器Power BI，简单拖拽就能让数据颜值爆表

概述

是一款商业智能软件，针对商务人士设计的，相对于Eexcel而言，它在数据导入，数据清洗，数据转换，交互图表制作方面更有优势。同时也是微软推出的一套商业分析工具。

简单理解：从各个数据源中获取数据，并对数据进行整理，然后绘制成图表，最后可以拿着做好的图表与别人在网页端或者移动端共享信息的工具

注册和安装

目前它分为在线版，移动版，桌面版，如果需要下载最新软件，可以登录微软Power Bi主页。由于是面对企业的，在填写注册邮箱时要用企业邮箱的方式进行注册，如果填的是个人，系统不会通过。注册完后在官网进行下载（免费的）

步骤

打开后获取数据-选择需要的excel，找到需要加载的数据表并勾选，单击加载即可

在可视化区域选择合适的图表类型，通过拖拽将数据到轴区域即可导入图表中

完成后，单击其中任何一个图表的内容，发现其他图表也会跟随着选择数据维度进行动态变化，在Power BI中称为数据钻取

推荐下载Power BI Desktop版本，该版本对个人用户免费开放，能满足日常需求

五、数据透视表篇：Excel高手必会的分析器

概述

数据透视表是一项能对大量数据快速汇总，并建立交叉列表的交互式动态表格，是excel自带的一款数据分析工具，综合了排序，筛选，组合和分类汇总等数据分析常用功能。能够获取不同数据源的数据，以多种不同的方式展示数据的特征，但其操作又简单，鼠标拖拽即可快速完成不同类型的数据报表，又称之为上帝的视角看数据

1.点击拖拽，轻松搞定汇总统计

1）数据透视表的数据源要求及不规范数据处理

要求

数据表必须是一维表

不能有合并单元格，否则无法计算或计算不准确

建议先选中需要进行透视分析的数据中任意单元格，然后套用表格样式，或在插入中选择插入表格，这样在后期更新数据的过程中，也能实现数据透视表的自动更新拓展

2）快速生成透视表并拖拽字段到对应区域完成统计工作

选中需要进行分析的任一单元格，插入-数据透视表，在弹出的对话框中excel会自动选择当前表格区域，单击确定即可

勾选需要的数据，勾选文本的数据系统会自动将其添加到行区域内，勾选数值的数据系统会自动添加到值区域，

如果想了解每个区域的销售额占比情况，只需要在值字段设置-值显示方式，选择为总计的百分比即可

3）调整透视表的布局显示及表格美化

透视表默认显示行/列的汇总或总计的，如果不需要，可以在设计-总计进行修改

在设计选项卡中可对样式进行改变

2.分组（段）统计，透视表一键快速搞定

掌握数据分组操作入口

将日期拉到行方向，会发现自动出席那年份信息，点击加号，会发现每个加号里面会有季度信息，再点击加号，会出现月度信息，同时再列表中会出现年和季度的字段

再表的行或列中，鼠标右键，找到组合功能进行手动组合并赋予新的名称

日期数据分组的常见形式及应用

日期在excel中可以按秒，分，小时，日，月，季度，年等多种单位进行组合

组合字段和原有字段一样，可按不同的布局进行移动

文本型字段也可进行分组，默认为地区分组。自动形成双层字段

可进行选择，点击鼠标右键进行组合，重命名形成新的组合

频次分布的常见形式及应用

先使用VLOOK UP的模糊查询，按梯段进行等级划分，然后再在透视表中进行统计

3.拆分表格，用透视表原来这么简单

按照指定条件快速拆分表格

在透视表中将需要的分类拖拽到筛选中，单击数据选项表分析-选项-显示报表筛选页，确定后发现excel已经按照分类分成几张表格

快速按照指定名称生成100张工作表

将需要新建表格的人员名单整理好放到excel中，利用透视表的筛选功能按姓名操作，即可实现每个姓名一张表格。删除姓名标题栏，在第一张中选中，按住shift键，选择最后一张表，即可选中所有，开始-清除选项-全部清除即可

按住CTRL+单击三角箭头，即可快速滚动到末端工作表，然后按住shift键点击工作表即可选择连续的工作表

汇总分析过程中快速定位到数据源

完成汇总分析后，想要看某个数据的详细构成，可双击该数值，即可快速生成该数据下所有数据的明细信息

没必要删除原始表格，因为对方只需要在总计处双击鼠标，即可看到详细的明细信息

外发的数据，利用选择性粘贴，将结果粘贴成数值，然后在发送，实现保密

4.用好切片器，轻松做出交互式动态图表

汇总数据快速生成图表

点击透视表任意位置-数据透视表分析-插入切片器，选择合适的字段，即可生成

注意：只有新excel格式，即.xlsx格式文件可使用切片器功能，旧格式的.xls不能使用

如果切片器呈现灰色不可用，请检查文档格式是否为.xlsx，若不是，可用excel2010版本以上打开，另存为.xlsx格式

插入切片器并关联图表

先根据数据，插入图表，然后选中图表，插入切片器

切片器支持多选，可按CTRL键多选，也可单击打开切片器多选按钮后多选

切片器右上角清除按钮可清除已筛选项，快捷键ALT+C

图表格式样式调整美化

选中切片器，在菜单栏切片器中进行修改，可更改切片器的展示方向，样式等

图标上大量的字段按钮，影响观看，右键选择隐藏图标上的所有字段按钮即可进行隐藏

5.综合练习 制作销售分析数据仪表板

分析数据指标，规划呈现内容

划分表格区域，摆放图表内容

关联切片效果，综合调整细节

调整图表尺寸可按住ALT键，图表就会以网格线为边界，方便对齐操作

右键切片器，找到报表连接项，实现同时关联不同的图表

相同项的切片器与图表不进行关联（如地区切片器不和地区销售占比关联），因为100%无观察意义

切片器中月份的显示顺序时错误的，并不是按从小到大顺序，可右键切片器，设置，排序时使用自定义列表，将一月到十二月的数据序列添加到自定义的数据序列中。

在页面布局改变仪表板颜色等样式