第一讲：认识Excel

1.简介

excel能做什么？

• 数据存储 → 数据处理 → 数据分析 → 数据呈现

excel界面

补充：Excel数据分析步骤
①提出问题：明确自己需要通过数据分析解决什么问题
②理解数据：理解数据各个字段的含义
③数据清洗 

这时候就需要用到Excel了
首先要对数据进行一次大清洗，将多余重复的数据筛选清除，将缺失数据补充完整，将错误数据纠正或删除
这时要用到一系列函数

• 数据重复：countif
• 缺失数据：if，and，or，嵌套函数等
• 数据抽样：left，right，concatenate，vlookup
• 数据计算：average，sum，max，min，date，if
• 数据分组：vlookup，近似匹配，search
• 数据抽样：rand

④构建模型：运用函数，数据透视表，分析工具库（描述统计分析） 

⑤数据可视化

根据数据关系选择图表

• 成分：饼图，柱形图，条形图，瀑布图
• 排序：柱形图，条形图，气泡图，帕累托图
• 时间序列：折线图，柱形图
• 相关性：散点图，柱形图，对称条形图（旋风图），散点图，气泡图
• 多重数据比较：雷达图

图表注意事项

• 信息完整：图表标题，单位，图例，脚注，来源等
• 避免无意义的图表
• 一表反映一个观点
• 只选对的不选复杂的图表
• 标题一句话阐述清楚反映观点

2.一些重要概念

几种常用文件类型

• XLS工作簿文件：2003之前的格式
• XLSX 工作簿文件：2007到2010之后新版本的文件格式
• XLW工作区文件：类似快捷方式，数据随着原文件变动，可进入【视图 - 新建窗口】熟悉该选项卡

工作簿，工作表，单元格

• 新建工作表：点击右侧小加号，双击重命名
• 更改工作表标签颜色：右键
• 交换两列数据的位置：选中目标列 → 按住shift键 → 拖动目标列至目标位置
• 插入/删除多个工作表：左键选中第一个工作表，按住shift，选中最后一个工作表，右键插入/删除
• 插入多行/列：选中n行/n列，右键插入
• 交换两列数据位置：选中目标列，鼠标放置右边线处，按住shift键拖动目标列至目标位置
• 调整行高列宽：选中多列 → 鼠标放置右边线双击 → 自动调整多列宽度；选择多列 → 手动调整列宽度（也可以同时调整多行宽度，并且都是被一致调整的）
• 框选多行：名称框中输入（起始行：目标行）

3.使用小工具，冻结窗格，填充柄，查找和替换

冻结窗格

• 定义：依据当前被选中单元格锁定表格行与列，滚屏时，被冻结的标题行总显示在上面，标题列显示在左面
• 位置：选项卡 → 视图 → 冻结窗格
• 冻结首行：视图 - 冻结窗格 - 冻结首行（即可实现滚动表格，而首行不动）
• 冻结多行：选择下一行单元格（以冻结前3行为例，选中第4行首个单元格，选择冻结拆分窗格命令）
• 同时冻结行与列：选择交界处单元格，冻结窗格。选中某一单元格，选择冻结拆分窗格命令，被选中的单元格符合以下规律：总是冻结被选中单元格上方和左侧的单元格，下方和右侧单元格是可变动范围

填充柄

• 定义：鼠标放置在单元格右下方变成黑色实心加号拖拽自动填充单元格
• 用法：选中n个单元格 → 加号拖拽。
• 拖拽规律为序列填充，按住Ctrl再拖拽可实现复制填充
• 自定义填充顺序：文件 - 选项 - 高级 - 编辑自定义列表 - 输入序列添加
• 其它格式拖拽：选中单元格，移动至右下角按住右键拖拽会弹出填充快捷菜单，可以选择想要的填充格式

小技巧

• 快速到达表格最前&最后：选中单元格，在表格的上边线双击到达表格最前，表格下边线双击到达表格最后
• 显示今天日期快捷键：Ctrl + : ，直接向下拖拽默认序列拖拽

第二讲：Excel单元格格式设置

1.使用单元格格式工具美化表格

“设置单元格格式”对话框在哪里？

• 选中单元格 - 右键 - 设置单元格格式
• 开始选项卡 - 单元格功能区

合并单元格

• 多个小单元格合并成一个大单元格，常用于标题
• 选中多个单元格 - 开始选项卡 - 合并后居中
• 合并多行：选中多个单元格 - 开始选项卡 - 跨行合并（避免用上述操作合并3次）

边框线

• 若不设置，打印不会有边框
• 选中整个表格区域的单元格 → 开始选项卡 → 边框 → 选择所有框线
• 如需设置边框格式，选中单元格区域后，右击选择设置单元格格式

斜线

• 设置斜线：在单元格内右键 - 设置单元格格式 - 边框 - 选择斜线
• 斜线双表头（通过双行文字实现）：表头1单元格设置斜线 - 双击单元格 - 写表头2 - alt + enter - 左右对齐，空格设置格式 - 回车
• 为什么应用空格调整而不直接设置右对齐和左对齐？因为对齐是针对整个单元格内容，不能同时设置一行左对齐，另一行右对齐
• 如果需要三个斜线：插入 - 形状 - 直线 - 鼠标绘制斜线
• 填充文字：通过双航文字实现，回车（alt + enter）分行，文字左对齐，第一行输入空格将文字右对齐

格式刷

• 先在已经完成格式设置的表格里应用格式刷，可以刷到另一个新表格，就会应用一样的格式
• 双击格式刷按钮，可以保持格式刷状态，刷新多个单元格的格式，直到按下esc退出

2.单元格数据格式

定义：在Excel当中，会去掉无意义的数字格式。比如，输入2400.00，会直接省略成2400，输入007，会变成7。所以需要设置单元格数字格式

路径：选中单元格 - 右键设置单元格格式 - 可以设置不同的数字类别（但数值永远是数值，只是显示格式改变了）

如果一组数字是文本格式，那么这组数字不能运算，但仍然可以把它改成数字：选中文本格式的数字，然后这些文本格式数字的左上角会出现黄色棱形感叹号，点击这个感叹号，选择“转换成数字”，就可以转换成数字了

自定义数字格式：可以实现在所有数值前面或后面加内容，如在数值后加“元”字，并且这些数值还可以做相加等运算（因为设置格式不改变值）

3.使用“分列”工具

将一组txt内容复制黏贴到Excel表格，数据会全部在A列，此时找到数据选项卡 - 分列，根据实际情况选择通过【分隔符号】或者【固定宽度】分列

一般是通过【分隔符号】来分列，找到数据里可以用来分隔的符号

如果一次分列没有完成，可以再次找到能够分隔的符号，进行二次分列

日期是以文本格式输入的，无法通过“设置单元格格式”改为其他格式，可以通过分列的第3步修改列数据格式，修改成常规或者日期，修改成功后就可以修改格式了

第三讲：查找，替换与定位

1.查找与替换

快捷键：查找Ctrl + F ，替换Ctrl + H

补充：替换选项卡里有 “选项” 功能，可选择 “单元格匹配”，避免一些不必要的替换

按值查找

• 开始选项卡 - 查找和选择
• 替换选项卡里有“选项”功能，选择“单元格匹配”，避免一些不必要的替换

按格式查找

• 如按颜色查找替换：格式按钮 - 填充 - 选择颜色

模糊查找

• 认识通配符：* 和 ?都是通配符
• * 指的是任意一个或多个值
• ?（英文的半角问号）指的是一个模糊字
• 注意：像 “张?” 替换成 “经理的亲戚” ，这样 “张三” 也会被替换成 “经理的亲戚三” 。此时需要开启单元格匹配，张?表示一个模糊字，这样单元格内 “张三三” 就不会被替换了。用 ? 时，常常需要勾选单元格匹配，为了限制字符
• 规避通配符的作用，用波浪线 ~

2.定位工具

通过名称框定位单元格及区域位置

• 如在名称框输入A900：B1000即可选中该单元格区域
• 在名称框输入100:200即可选中100到200整行

定义名称

• 为某个单元格区域自定义名称，以后可以直接使用这个名称查找，通过输入名称，或运用 “查找和替换” - “转到”
• 选择某个区域，然后在名称框中输入一个名字，然后回车，这个名字就被保留了下来，下一次就可以比较容易找到

定位条件

• 查找和替换 - 转到 - 定位条件
• 查找和替换 - 定位条件

使用定位条件解决以下问题

• 为有批注的单元格设置红色填充色：查找和替换 - 定位条件里选择批注 - 选中 - 可以自行修改颜色
• 为有公式的单元格设置红色填充色：查找和替换 - 定位条件里选择公式 - 选中 - 可以自行修改颜色
• 填充解除单元格合并后遗留的空白单元格：查找和替换 - 定位条件里面选择空值 - 选中 - 输入 - 并按下上方向键 - 全部填充快捷键（Ctrl + enter）
• 批量删除图片：查找和替换 - 定位条件里选择对象 - 选中 - 按删除键删除，如果没有图片，执行上述操作后Excel会警告 “找不到对象”，除此之外，在查找和替换里找到 “选择对象”，也可以只框选图片

批注

• 标志：右上角红色小三角
• 插入批注：选中单元格 - 右键 - 插入批注 - 编辑文字
• 显示/隐藏单个批注：选中单元格 - 右键 - 显示隐藏批注
• 显示所有批注：审阅选项卡 - 显示所有批注
• 删除所有批注：选中区域，右键删除批注
• 更改批注框形状：插入 - 形状 - 选择目标形状
• 在批注中插入图片：设置批注格式 - 颜色与线条 - 填充 - 颜色 - 填充效果 - 图片

取消合并单元格后空白内容处理

• 选中区域 - 定位条件：空值 → 在一个空单元格内输入 “=” 
• 若填充与上/下方相同的内容，则按下上方向键，然后软回车（Ctrl + enter）
• 若填充其它内容，则手动输入再软回车
• 选中一个区域，在一个单元格输入数据，按下软回车，则该区域所有单元格都被填上相同的数据

第四讲：排序与筛选

1.排序

注意：执行排序操作的时候避免选中某列，如果以当前选中的区域排序会导致数据错乱，可以选择全部数据或者这一列中的某个单元格

简单排序

• 开始 - 排序和筛选 - 升序/降序

多条件排序

• 如将语数外成绩排序，当数学成绩一样时按照语文成绩排，语文成绩一样时按英语成绩排：排序和筛选 - 自定义排序 - 添加排序依据
• 也可以分别进行单列排序：先依次排次要关键字，再排主要关键字（先排英文，再排语文）

按颜色排序

• 排序和筛选 - 自定义排序 - 添加排序依据 - 单元格颜色

自定义排序次序

• 如果要排序的值是文字，默认按照拼音首字母排序
• 如果要改变，那么选择次序为自定义序列

利用排序插入行制作工资条（格式：表头 + 个人信息）

• 假设第一行是工资表表头，下方11行是员工个人信息
• 再复制10行工资表表头
• 在第一行表头旁的空白列输入0，在员工个人信息输入1-11
• 其它10行表头输入1.5-10.5
• 最后将数字按照升序排序即可

打印要求每页都有表头

• 页面布局 - 页面设置 - 工作表 - 设置顶端标题行 - 选中第一行 - 打印
• 设置后，第一行名称栏会显示print titles

2.筛选

使用筛选

• 点击筛选区域的任意单元格 - 排序和筛选 - 筛选 - 第一行出现下拉箭头
• 筛选完了，让原本所有数据都出现，在下拉箭头勾选 “全选”
• 想要把筛选出来的数据复制到另一个表中去，却发现粘贴了原来的整个表，解决方法：筛选完成后 - 查找和选择 - 定位条件 - 可见单元格 - 复制

在筛选中使用多个条件

• 数字筛选：根据大于/小于/等于等条件来筛选
• 同时筛选多列：分别进行筛选，在上一列的筛选结果中再筛选
• 文本筛选：输入开头/结尾能匹配的关键词

高级筛选

• 筛选不重复值：数据选项卡 - 高级筛选 - 可选择在原区域/其它位置显示筛选结果 - 选中列表区域 - 选中条件区域（没有就不动）- 选中复制到哪个单元格 - 勾选不重复的记录。应用场景为找出不重复的数量
• 使用常量条件区域：条件区域指的是在数据中找到符合条件的需要另外输出条件，且（与）的条件写在同一行，或的条件错开位置，条件也可以输入大于小于

第五讲：分类汇总与数据有效性

1.分类汇总工具

认识分类汇总

• 数据选项卡 - 分类汇总
• 按什么分类，汇总什么，怎么汇总

使用分类汇总前先排序

• 选中要分类所属列的某个单元格 - 点击【开始 - 排序和筛选】或【数据】里的【升序】或【降序】- 完成排序操作
• 点击【数据】里的【分类汇总】，选择分类字段（即根据哪一项进行分类），汇总项（即对哪一项进行汇总）汇总方式（可对汇总项进行求和，计数，求平均值等操作）

分类汇总的嵌套

• 根据多个字段进行分类汇总，即依次根据不同的字段进行重复操作
• 选择主要关键字，次要关键字进行排序
• 依次对主要关键字为分类字段先进行分类汇总，第二次起注意取消 “替换当前分类” 的勾选
• 可以通过点击工作表左上角的【1,2,3,4】来查看结果

复制分类汇总的结果区域

• 选中结果区域 - 选择【开始 - 查找和选择 - 定位条件】- 选择【可见单元格】（可用快捷键alt+;）- 复制

使用分类汇总批量合并内容相同的单元格

• 将需要批量合并的列先排序
• 数据 - 分类汇总 - 将该列表头设置为【分类字段和汇总项】- 将汇总方式改为计数（不改也问题不大）- 确定
• 完成后会自动新生成 - 列计数 - 选中新列并选择【开始 - 查找和选择 - 定位条件】- 定位到【空值】- 合并单元格
• 数据 - 分类汇总 - 全部删除，即可删除分类汇总的数据
• 选中新出现的列 - 开始 - 格式刷 - 选中目标列，即可粘贴格式，合并内容相同的单元格（或者先复制再【选择性粘贴 - 格式】）

2.设置数据有效性

设置整数数据有效性

• 仅能输入500~1000之间的整数
• 选中目标列 - 数据选项卡 - 数据有效性 - 选择允许【整数】，数据【介于】，最小值【500】，最大值【1000】- 确定

设置文本长度数据有效性

• 仅能输入字符长度为8位的产品编码
• 选中目标列 - 数据选项卡 - 数据有效性 - 允许【文本长度】，数据【等于】，长度【8】- 确定

数据有效性的其他设置

• 输入法切换：可以选择只能以中文或英文输入，功能能否实现和个人电脑输入法设置有关
• 单元格信息：可以将【输入无效数据时现实的出错警告】改为【警告】并手动输入警告信息引起别人注意，相当于一种半保护状态
• 单元格信息保护：选中目标区域 - 数据选项卡 - 数据有效性 - 允许【自定义】- 输入一个逻辑值为 “false” 的公式（可以直接输入0），这样公式无法成立表格也无法被编辑

第六讲：认识数据透视表

常见数据透视表

• 字段名称：原始表格中列的字段
• 报表筛选：如果把某个字段拖拽到这个里面，它就会变为筛选项，相当于根据该字段对透视表做筛选
• 列和行：如果想在透视表的行上显示什么就把对应字段拖拽到行上，想在列上显示什么就把什么拖拽到列标签
• 值：就是透视表中的数据，有求和，计数，求最大值等多种汇总依据
• 创建步骤：选中任意一个单元格，点击【插入 - 数据透视表 - 确定】→ 点中生成的数据透视表 → 选择【数据透视表选项 - 显示】→ 勾选【经典透视表布局】（可做可不做，看个人习惯）→ 根据需要将字段拉入数据透视表中的行/列/值，即完成一个简单的数据透视表

更改数据透视表汇总方式

• 熟知的默认汇总方式是求和，可以双击表头值字段，可以更改汇总方式（如更改为计数，平均值等）
• 双击表中汇总后的数值可以得到一张新表，能够查看详细记录
• 双击表头第一个单元格，除了可以更改汇总方式，也可以选择【无】，则去掉分类汇总（不同版本/电脑不一定实现，也可以通过点击数据透视表中的任一单元格，此时表格顶端会出现“设计”功能选项卡，点击“设计”中的子菜单 “分类汇总”，选择 “不显示分类汇总”）

数据透视表中的组合

• 对日期进行组合：选中日期和其它字段拉入数据透视表中的 “行字段”中，注意放的位置，一级字段放前面，如果日期字段是以天为统计维度，可以选中任一日期右击组合，能够修改统计维度为月/季度/年
• 对数据进行划分区间的统计：选中数据字段拉入“行字段”中，再从右侧选中数据字段拉入 “值字段”，选中 “行字段” 中任意一个数据 - 右击 - 组合 - 确定起止和数值间隔

汇总多列数据

• 将姓名字段拉入 “行字段” - 再将工号字段拉入 “行字段”，此时姓名和公号字段呈现上下分布
• 但是针对姓名和工号默认出现了汇总，可通过设计 - 分类汇总 - 不显示分类汇总隐藏
• 目的是统计生产数量的求和项，平均产量，最大产量和最小产量，所以重复4次将生产数量拉入 “值字段”，此时如果4行生产数量呈现上下分布，新生成了一行数据和汇总，可以把数据字段拉到汇总字段上
• 双击 “求和项”，更改【值字段汇总方式】也可以单击右键，选择【值字段设置】进行更改
• 更改值字段名称：选中后双击 - 在字段名称内更改，也可以在编辑栏中更改，不可与原有字段重复
• 更改表格样式：点钟透视表中任意单元格，选择窗口最上面出现的【数据透视表工具 - 设计】，选择喜欢的样式

利用筛选字段自动创建工作表

• 插入数据透视表，将某字段拖入“报表筛选字段”中，则可以筛选数据
• 但是如果要同时展示多个字段的数据透视表，也可以利用筛选字段自动创建
• 准备要新建表格的一列数据 - 插入数据透视表 - 将字段拖入“报表筛选字段”和“值字段”中 - 打开数据透视表分析选项卡 - 找到【选项】- 选择【显示报表筛选页】就自动生成了新的工作表
• 但是每一张工作表上都有透视表，需要删除这些透视表：按住shift键选中所有生成的工作表 - 复制空白区域覆盖生成的透视表，就去掉了透视表区域，新的工作表也批量生成了

第七讲：认识函数与公式

1.公式

运算符

• 算术运算符：用来完成基本的数学运算
• 比较运算符：结果一定是逻辑值“TRUE”（运算中当做“1”）或者“FALSE”（运算中当做“0”）

公式中的比较判断

• 比较运算符的结果：TRUE，FALSE
• 公式里的文本要用""引用

运算符的优先级

单元格引用

• 相对引用：A1（引用的是相对位置的单元格，公式所在单元格的位置改变，引用也随之改变）
• 绝对引用：$A$11（引用的是固定单元格，公式所在单元格的位置改变，绝对引用的单元格始终保持不变）
• 混合引用：$A1绝对引用列，A$1绝对引用行（只有行或列被锁定）（既要横向又要纵向拖拽，结合“九九乘法表”理解）

2.函数

如何使用函数

• 等号开头
• 函数名在中间
• 括号结尾
• 括号中间写参数

学习以下函数

• sum：求和
• average：求平均
• min：求最小
• max：求最大
• count和counta：计数
• rank：排名

利用定位工具选择输入公式的位置

• 选中目标区域，点击【查找和选择 - 定位条件 - 空值】，再点击公式选项卡里面自动求和工具，实现跳跃式的求和
• 选中目标区域，点击【查找和选择 - 定位条件 - 空值】，输入公式函数，用 “Ctrl + Enter” 进行填充

第八讲：IF函数逻辑判断

1.使用IF函数

基本用法

• = if（条件，如果符合则A，如果不符合则B）
• 参数1：进行一次判断，是一个逻辑值
• 参数2：如果逻辑值为TRUE，则在单元格中返回A
• 参数3：如果逻辑值为FALSE，则返回B

嵌套

• = if（条件1，如果符合则A，if（条件2，如果符合则B，如果不符合则C））
• 参数1：进行一次判断，是一个逻辑值
• 参数2：如果TRUE，则返回A
• 参数3：如果不为TRUE，需要再根据剩下的情况进行区分，则再使用if函数，以此类推（不要遗漏小括号）

如何尽量回避IF函数的嵌套

• 当IF函数嵌套超过了四五层，应考虑是不是用错了函数或者改用VLOOKUP等其他函数
• 可以并列使用多个IF函数，若返回的值为数值，则可以=IF1(..)+IF2(...)+...(若为FALSE返回0)；若返回的值为文本，则可以=IF1(...)&IF2(...)&（若为FALSE则不返回）

用IF函数处理运算错误

• 判断公式运行是否出错
• = IF(ISERROR(A),0,A)：如果运算A出错，返回0，如果是数字，返回数字本身

2.AND函数与OR函数

AND函数：表示 “且” 的关系

• 表示需要满足多个条件：参数必须同时为真，返回的结果才为真
• AND(条件1，条件2，条件3，...）

OR函数：表示 “或” 的关系

• 表示需要满足至少一个条件：只需满足其中一个为真，结果为真
• OR(条件1，条件2，条件3，...)

第九讲：COUNTIF函数

1.使用countif函数

count函数

• 用于对给定数据集合或单元格区域中数据的个数进行计数，只能对数字数据进行统计，对于空单元格，逻辑值或者文本数据将被忽略
• 可以利用该函数来判断给定的单元格区域中是否包含空单元格
• = COUNT(value1,value2,...)
• 参数1：必须，用于计算选中的第一个区域内数字的个数
• 参数2：可选，用于计算选中的第一/三个区域内数字的个数，最多可包含255个区域

countif函数

• 计算某个单元格区域内，满足单个指定条件的单元格数量
• = COUNT(range,criteria)
• 参数1：数据集合，数值区域
• 参数2：数值，文本或条件等形式定义的条件

2.常见应用示例

在数据区域中寻找重复数据

在数据有效性中使用

3.2007以上版本中的countifs函数

表示对满足多个条件的单元格计数

= COUTNIFS(range1,criteria1,range2,criteria2,...)

只有一个条件则和COUNTIF函数一样

第十讲：SUMIF函数

语法

• 根据指定条件对若干单元格，区域或引用求和
• = SUMIF(range，criteria，sum_range)
• 参数1：条件区域，用于条件判断的单元格区域
• 参数2：求和条件，由数字，逻辑表达式等组成的判定条件
• 参数3：实际求和区域，需要求和的单元格，区域或引用

超过15位字符时的错误

• 查找数据时默认最多只能计算数据的前15位，若需计算超过15位，应该在后面加上&“*”（和countif一样）

关于第三参数简写时的注意事项

• 参数内range与sum_range的范围必须一样大
• sumif有很大的自由度和容错率，当输入的sum_range范围较小时，会默认补充至range一样大，因此可以简写第三参数
• 简写第三参数应保证第三参数的第一行要与range的第一行相对应

在多列中使用sumif函数

• 选中整个表作为参数中的range，仅选择一个或几个数值作为sum_range

使用辅助列处理多条件的sumif

• 当需要满足两个或以上条件时，可新增一列作为辅助列，输入公式 “= 条件1 & 条件2 & ...”，向下填充，以此列作为range条件区域
• 以“条件1所在的单元格 & 条件2所在的单元格&...”作为criteria求和条件

sumifs函数

• 快速对多条件单元格求和
• = SUMIFS(sum_range,criteria_range1,criteria1,【,criteria_range2,criteria2...】)
• 参数1：要求和的单元格区域
• 参数2：条件区域1
• 参数3：条件1
• 【】为附加的区域及其关联条件，最多可以输入127个区域/条件

第十一讲：Vlookup函数

语法

• = VLOOKUP(lookup_value,table_array,col_index_num,【range_lookup】）
• 参数1：要查找（匹配）的值
• 参数2：要查找的区域
• 参数3：返回的数据在查找区域的第几列
• 参数4：精确匹配/近似匹配，FALSE(0,空格或不填（但是要有 ',' 占位))，TRUE(1或者不填(无逗号占位))，尽量都使用精确匹配

跨表引用

• 在需要接收返回值的单元格内输入公式，当需要跨表引用时，直接点击切换到工作簿内的其他工作表进行选择查找的区域，在输入完逗号后可切换回来，也可以直接继续输入完公式更方便

使用通配符

• 要查找的数据是列表区域中的部分关键字（参数1与参数2中的值不能完全匹配）可以使用通配符 “*”（代表任意数量的任意字符）

模糊查找

• 找小于等于自己的最大值，适用于找数值区间的划分
• 模糊匹配用的是 “二分法”，在使用时需要查找区域的值从小到大排序

使用isna函数处理数字格式引起的错误

• vlookup函数不能匹配储存格式不一样的数值，比如一个是文本数值，一个是数字数值无法匹配

Hlookup函数

• 用法与vlookup函数基本一样，vlookup函数适用于以一行为一条记录的表格，而hlookup用于以一列为一条记录的表格，注意绝对引用

第十二讲：Match + Index

语法

①MATCH函数

• 返回查找值在查找区域中的位置
• = MATCH(lookup_value,lookup_array,【match_type】)
• 参数1：需要查找的值
• 参数2：在哪列或哪行查找（可以不是一整列或一整行）
• 参数3：精确查找/模糊查找

②INDEX函数

• 返回表或区域中的值或值的引用
• = INDEX(array,row_num,【column_num】）函数返回的是对应单元格的值
• 参数1：引用哪一区域
• 参数2：引用第几行的值
• 参数3：引用第几列的值

Match + Index 与 Vlookup函数比较

• vlookup函数只会查找选中区域的最左列，而且引用列在查找列的右边，不能做从右向左的查找引用。match和index函数的查找和引用是分开进行的，不存在列序的矛盾
• vlookup只能查询返回一个值，不能引用照片，index可以

使用Match与Vlookup函数嵌套返回多列结果

• = VLOOKUP(lookup_value,table_array,COLUMN(),0)
• = VLOOKUP(lookup_value,table_array,MATCH(),0)

column函数

• = COLUMN()
• 查询单元格在第几列，若无参数则返回当前单元格在第几列

index函数引用图片

• 在工作表中添加图片
• 点击【公式 - 定义名称】如定义名称为 “图片”，在【引用位置】中输入引用图片的函数
• 在【文件 - 选项 - 自定义功能区】中添加【照相机】功能到【新建选项卡】选中用于接收图片的单元格，点击【新建选项卡 - 照相机】画一个框，在编辑栏中输入 “=图片”，回车
• 复制图片，粘贴到用于接收图片的单元格，点中图片，在编辑栏中输入 “=图片”，回车

第十三讲：邮件合并

1.简单的邮件合并

先建立两个文档：一个word包括所有文件共有内容的主文档（比如未填写的信封等）和一个包括变化信息的数据源Excel（填写的收件人，发件人，邮编等）然后使用邮件合并功能在主文档中插入变化的信息，合成后的文件用户可以保存为word文档，可以打印出来，也可以以邮件形式发出去

可以应用在批量打印工资条，批量打印个人简历，批量打印请柬，批量打印准考生等

批量生成多个文档，利用word发送邮件（不同版本操作大同小异）

• 在【邮件】选项卡选择【开始邮件合并】- 【电子邮件】
• 在excel中创建数据源（准备数据源）
• 在【邮件】选项卡选择【选择收件人】选择【使用现有列表】，打开数据源表格，根据提示点击确定
• 在【邮件】选项卡选择【插入合并域】，然后选择要添加的域，输入邮件内容设置格式
• 在【邮件】选项卡上，选择【预览结果】查看电子邮件外观
• 如果对邮件外观已经满意，请在【邮件】选项卡上选择【完成并合并】-【合并到电子邮件】，完成邮件合并并发送
• 如果电子邮件外观已经满意，请在【邮件】选项卡上选择上【完成并合并】-【合并到电子邮件】完成邮件合并并发送

每页显示多条记录

• 在【邮件】选项卡选择【开始邮件合并】-【目录】-选择【选择收件人】选择【使用现有列表】，打开数据源表格，根据提示点击确定
• 选择【插入合并域】，然后选择要添加的域，输入邮件内容设置格式，选择【完成并合并】-【编辑单个文档】

邮件合并后的数字格式处理

• 数字格式#"#,##0.00（切换为千分位并保留两位小数显示）
• 日期格式@"M/d/yyy"（代表月份的M一定要大写）
• 使用“alt + f9组合键”（或者【选中数字合并域 - 单击右键 - 切换域代码（编辑域 - 域代码）】），在字段名称后输入代码，再切换回来，按f9键（或右键 - 更新域）来更新域代码
• 每一种显示格式在excel中有特定对应的代码，可【右键单击excel单元格 - 设置单元格格式 - 数字 - 自定义】来查看

2.复杂的邮件合并

利用邮件合并批量生成单个的文档

为不同的邮件插入不同的附件

（视频不完整，没有后续内容）

第十四讲：日期函数

1.认识时间和日期

回顾日期格式

• Excel日期采用 “1900纪年方式”，日期可转换为整数，即从1900年1月1日开始的第几天

时间格式

• 时刻可以转换为小数，即到了该时刻，该天已经过去了多少

基本的时间与日期运算

• 时间运算：注意单位换算，天/小时/分钟/秒钟 = 1/24/60/60
• 日期运算：日期可与整数相加减，可更改单元格数字格式得到日期或数值

2.日期函数

Year，Month，Day函数

• 分别求参数（一个日期）的年，月，日

Date函数

• = DATE(YEAR(),(MONTH(),(DAY())
• 将三个参数相组合，生成一个日期，三个参数依次是生成日期的年，月，日
• 若代表月，日的数字过大，会向前进位；若小于1，则会退位

Dateif函数

• = DATEIF(start_date,end_date,unit)，用于比较两个日期的不同，参数3决定返回值是年，月还是日
• 参数1：表示给定期间的第一个或开始日期的日期
• 参数2：表示时间段的最后一个（即结束）日期的日期
• 参数3：表示要返回的信息类型

Weeknum

• 返回参数1（日期）是该年中的第几周，参数2可以设置以星期几为一周的开始

Weekday

• 返回参数1（日期）是该周的第几天（1-7，也可以选择返回0-6），参数2可以设置以周几为一周的开始

第十五讲：条件格式与公式

1.使用简单的条件格式

位置：开始选项卡 - 条件格式

为特定范围的数值标记特殊颜色

• 在数据内标记大于1500000的值，选中数据范围 - 开始 - 条件格式 - 突出显示单元格规则 - 大于，在格式规则内输入条件和设置格式

• 在数据内将车间标记为红色背景，选中数据范围 - 开始 - 条件格式 - 突出显示单元格规则 - 文本包含，在格式规则内输入条件和设置格式

清除规则

• 开始 - 条件格式 - 清除规则

查找重复值

• 找到凭证号的重复值，选中C列 - 开始 - 条件格式 - 突出显示单元格规则 - 重复值

为数据透视表中的数据制作数据条

• 新建数据透视表 - 选中数据区域 - 开始 - 数据格式 - 数据条，选择想要的填充方案

在已设置条件格式的数据透视表中设置“切片器”

• 点击数据透视表任意单元格 - 数据透视表分析 - 插入切片器，根据想要的字段进行选择，即可完成【点击字段进行筛选】如果不要“切片器”直接选中删除

2.定义多重条件的条件格式

如何管理条件

• 设置多重条件不会覆盖替换前一个条件格式。但是后一个条件包含前一个条件就会覆盖前一个条件
• 想要兼容的做法是先设置大的范围标记，再设置小的

条件格式 - 新建规则

• 可通过新建规则为【错误值，空值，无空值】等设置格式
• 新版本也可以直接通过突出显示单元格规则 - 文本包含，选择具体的值进行设置

3.使用公式定义条件格式

条件格式中公式的书写规则

• 将数量大于100的项目日期标记为红色背景

使用weekday函数标记周末

• 将日期为周六日设置红色背景，说明在【使用数字1（星期一）到7（星期日）表示的一周中的第几天】该类型下，只要返回的结果为6或7就可以标红，在条件格式规则内输入公式 = weekday(A2,2)=6，再进行1次条件设置即可
• 如果会使用or函数，可直接输入 = or(weekday(A2,2)=6,weekday(A2,2)=7)即可
• 如果要将周末整行设置为红色背景，选择整个数据区域，输入公式 = or(weekday($A2,2)=6，weekday($A2,2)=7)，将A列绝对引用可以避免公式在B列的时候变成B2

标记未来15天的日期

• 将未来15天过生日的员工姓名设置为红色背景，说明在忽略年份的情况下只要员工生日距离今天开始的未来15天相差小于等于15就可以设置为红色背景，在条件格式规则内输入公式 = datedif(C2,TODAY()+15,"yd")<=15
• today函数的语法：该函数没有参数，只用一对括号即可，=today()

第十六讲：简单文本函数

1.使用文本截取字符串

left函数

• 从一个文本字符串左边的第一个字符开始返回指定个数的字符
• = LEFT(text,【num_chars】)
• 参数1：一个文本（要取数的单元格）
• 参数2：指定返回字符的个数（若无规定，默认返回1个）
• 若参数2大于文本长度，则返回整个文本

right函数

• 从一个文本字符串的右边（即最后一个字符）开始返回指定个数的字符，与LEFT函数用法完全相同（返回的字符依然是从左到右截取）
• = RIGHT(text,【num_chars】
• 参数1：一个文本（要取数的单元格）
• 参数2：指定返回字符的个数（若无规定，默认返回1个）

mid函数

• 返回文本字符串中从指定位置开始的指定数目的字符
• = MID(text,start_num,num_chars)
• 参数1：一个文本（要取数的单元格）
• 参数2：截取字符的开始位置，若小于1，则返回错误；若大于文本字符个数，则返回空
• 参数3：截取字符的长度，若大于文本字符个数，则返回从参数2位置开始到文本结尾的所有字符
• left函数和right函数嵌套使用可以替代mid函数

2.获取文本中的信息

find函数

• 查找指定字符在另一个字符中的位置
• 除此之外，search是另一个常用的查找函数，区别在于FIND函数能区分大小写字母，SEARCH函数可以使用通配符，FIND不可以
• = FIND(find_text,within_text【start_num】)
• 参数1：要查找的文本（可以是单个字符，数字）若是字符，要用""引用；若为空，则返回参数3的值
• 参数2：在此文本内查找；若不是参数1的子串，则返回错误
• 参数3：规定开始查找的位置，若无，则默认为1，即从文本第一个字符开始查找；若大于文本长度或小于1，则返回错误

len函数

• 返回文本字符数的长度
• = LEN(text)，某单元格有多少个字符，空格也会作为字符计数

lenb()函数

• 返回文本字节数的长度
• = LENB(text)，某单元格有多少个字节，空格也会作为字符计数

上述LEFT，RIGHT，MID，LEN，FIND有与其对应的-B函数，前者返回的是字符个数，后者返回的是字节个数

3.关于身份证

通过身份证前六位判断地区

• 首先可以通过LEFT()函数截取前6位地区编码，接着使用VLOOKUP()函数匹配地区码，即可得出身份证对应的地区
• 需要注意，身份证是文本格式，对文本处理得到的6位地区编码数字还是文本，但是匹配表内的地区码是数字格式，所以要将地区编码*1变成数字格式，否则无法查找

通过身份证计算出生年月日

第十七讲：数学函数

1.认识函数

round函数

• ROUND(number,num_digits)，按指定的位数对数值进行四舍五入
• 参数1：要进行四舍五入的数字
• 参数2：要进行四舍五入的位数
• 若参数2 > 0，则四舍五入到指定的小数位数；若 = 0，则四舍五入到最接近的整数；若 < 0，则四舍五入到小数点左边的相应位数

roundup函数

• 按指定的位数对数值进行向上舍入（往上无条件进位，如果是负数是往大的值进位），公式参数同上

rounddown函数

• 按指定的位数，对数值进行向下舍入（无条件舍去），公式参数同上

int函数

• 取整，将数值向下取整为最接近的整数，只有一个参数，不能规定位数（不是四舍五入，而是舍尾）
• 处理正值，跟ROUNDDOWN类似，处理负值，跟ROUNDUP相似

mod函数

• 求余数
• 返回两数相除后的余数，余数可以包含小数部分
• = MOD(number,divisor)，参数1为除数，参数2为被除数

row函数和column函数

• ROW(【reference】)：求单元格的行号
• COLUMN(【reference】)：求单元格的列号
• 若不带参数，则判定当前单元格的位置

2.函数应用实例

通过身份证号码判断性别

• 取身份证的性别判断位，用MOD函数来判断其奇偶性，再用IF函数来判断男女
• = IF(MOD(RIGHT(LEST(B2,I7),1),2)=1,"男","女")
• 遇到多个函数嵌套，可以先从最里层函数梳理

特殊的舍入方式：员工假期计算

• = IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2))
• = INT(A2*2)/2

第十八讲：Vlookup与数组

1.统计函数

SUMIF函数 = (条件区域，条件单元格，求和区域)

SUMIFS函数 = (求和区域，条件区域1，条件单元格1，条件区域1，条件单元格1)

2.认识数组

数组生成原理

• 用SUM函数替代SUMIF/SUMIFS
• =SUM(条件判断1 * 条件判断2 * ... *条件判断n * 求和项)

sumproduct函数

• 作用和SUM相同，但不用三键组合使用，直接回车即可

3.lookup函数基本应用

认识lookup函数

• 从单行或单列或从数组中进行查找并返回一个值
• = LOOKUP(lookup_value,lookup_vector[,result_vector])
• 第一参数：需要查找的值
• 第二参数：查找的区域，只包含一行或者一列，数值必须按升序排序
• 第三参数：返回的值所在的区域，只包含一行或一列，必须和第二参数区域大小一致

lookup函数模糊匹配

• lookup函数第二参数只有一列，而vlookup函数必须包含查询列和返回值所在的列。相较之下，lookup函数更加灵活
• lookup函数只有3个参数，不能设置精确匹配
• 使用lookup函数时，若查找列按升序排列，运算结果与vlookup函数精确查找一致

lookup函数多条件精确匹配

• lookup函数在查找时虽然不能设置精确匹配，但是会自动回避错误值，故可将所有不匹配的值转化为错误来进行精确匹配
• = LOOKUP(1,0/(条件区域1=条件1)，结果区域)
• = LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),结果区域)

第十九讲：indirect函数

1.认识indirect单元格引用

了解indirect函数的基本运用

• 如在A1单元格输入小佩，在当前单元格输入=A1可返回小佩，这种属于直接引用
• 如继续在C10单元格输入A1，在当前单元格输入=INDIRECT(C10)也可返回小佩，因为C10单元格的内容是A1，表示单元格地址，所以会返回A1单元格的内容，这种属于间接引用
• 但是如果在当前单元格输入=INDIRECT("C10")，加上双引号后C10就是一个文本，不会返回A1单元格的内容；同理在当前单元格输入=INDIRECT("A10")，也是返回小佩

indirect函数的意义及语法

• = indirect(ref_text,[a1])，即=indirect(引用的单元格，引用方式)
• 参数1：需要引用的单元格，如果不是合法的单元格的引用，函数indirect返回错误值
• 参数2：引用的单元格类型，引用方式为A1或R1C1两种方法，在A1样式中，列和行将分别按字母和数字顺序添加标签；在R1C1引用样式中，列和行均按数字顺序添加标签
• 平时使用的工作表行都使用阿拉伯数字表示，列都是用英文大写字母表示，默认A1-样式，所以这里的第二个参数往往都会省略，仅需考虑第一个参数即可

indirect函数与index函数引用方式对比

• 通过index函数直接引用：取每周第二个数值也就是数据内第5,10,15,20,25列，所以通过index函数返回E列第5行数据，输入公式 = index(E:E,ROW()*5-25)
• 通过indirect函数间接引用：取每周第二个数值也就是数据内E5,E10,E15,E20,E25单元格，如果直接输入单元格公式无法自动拖拽，所以通过输入 = "e"&row()*5-25得到单元格，再加上indirect函数引用单元格即可，=indirect("e"&row()*5-25)

处理跨表

• 在12张表内统计了销售人员的业绩，每张表排版一致，汇总张三每月的销售业绩：在每张表格内张三的单元格位置不变，只有月份变化，可以直接通过公式实现 = indirect((A4&"!G2"))
• 在12张表内统计了销售人员的业绩，每张表排版不一致，汇总张三每个月的销售业绩：在每张表格内张三的单元格位置不固定，可以与vlookup结合找到张三的销售业绩，输入公式 = vlookup("张三"，indirect(A4&"!A:G"),7,false)

跨表引用时的单引号问题

• 如果有时候使用indirect函数错误，注意表格命名问题，可以加上单引号规避

2.indirect名称引用

为区域定义名称

• 选择单元格区域 - 公式选项卡 - 名称管理器 - 对选定区域定义名称

制作二级下拉列表

• 新建名称：选中单元格区域 - 公式选项卡 - 名称管理器 - 新建名称
• 在实操中，如果要新建3个名称，就需要操作3次，为了省略重复步骤，也可以选中单元格区域 - 根据所选内容创建 - 创建名称的位置根据需要选中首行 - 名称管理器里面就批量新建好了3个名称
• 域 - 根据所选内容创建 - 创建名称的位置根据需要选中首行 - 名称管理器里面就批量新建好了3个名称
• 一级下拉框：选中制作下拉框的列 - 数据选项卡 - 数据验证 - 验证条件选中序列 - 来源选中所需单元格的范围 - 生成一级下拉框
• 二级下拉框：选中制作下拉框的列 - 数据选项卡 - 数据验证 - 验证条件选中序列 - 来源输入 =indirect(定义名称指定单元格) - 生成二级下拉框

第二十讲：图表基础

1.认识图表中的元素

在组合内隐藏详细信息时如何将图片一起隐藏：默认设置下隐藏行/列之后，图片不会随之隐藏，而是移到下一行/列了。选择图片 - 右击设置图片格式 - 属性选中随单元格改变位置和大小，此时隐藏组合（设置：数据 - 组合）时图片也会一起隐藏

插入图表

• 选中需要制表的所有数据 - 插入选项卡 - 找到想要的图表款式
• 它包含两个分类，一个是推荐的图表，另一个则是所有图表

布局

• 图表标题：跟文章标题一样，用一句简明扼要的归纳出图表内容；在图表设计 - 添加图表元素 - 图表标题，可以设置图表标题位置；在图表内右击图表标题，可以设置图表标题格式；选中图表标题文本框，在编辑栏输入=B1，标题会自动随B1切换
• 坐标轴标题：对坐标轴的命名；在图表设计 - 添加图表元素 - 坐标轴标题，可以设置坐标轴标题
• 图例：跟地图上的图例一样，方便查看数据系列反映的是哪些内容
• 数据标签：数据系列的数据化显示，可以通过设置数据标签格式来调整显示内容/显示位置
• 模拟运算表：原始制表表格
• 坐标轴：一般是主要横坐标轴（分类轴）和主要纵坐标轴（数据轴）一些复杂的图表可能还有两个次坐标轴；在图表元素 - 坐标轴或者图表内右击坐标轴，可以设置坐标轴格式：坐标轴选项，刻度线，标签，数值等；选中坐标轴文字 - 设置坐标轴格式 - 选项 - 标签，可以选择周旁，高低等；纵坐标轴是数值的话，可以设置比例尺大小，即边界的最大值最小值，间隔单位值；设置横坐标重排顺序，纵坐标移到右侧（镜像）：坐标轴选项 - 逆序类别
• 网格线：网格线垂直各水平网格线，便于使用图表的人进行比较
• 如果隐藏了某个元素，可以在格式选项卡里找到

2.创建并美化柱形图

了解主次坐标轴的概念

• 当涉及不成比例的两组数据时，需要用主，次坐标轴区分

制作折线与柱形复合图表

• 各个地区有营业额，指标完成率，这两个数据比例不匹配，插入柱形图表后，指标完成率无法直观显示
• 点击指标完成率的柱形图（或格式选项卡 - 左上角下拉框选择指标完成率 - 设置所选内容格式）选择系列绘制在次坐标轴上
• 再点击指标完成率的柱形图，右键更改系列图表类型 - 折线图，在折线上右键设置数据系列格式 - 标记 - 勾选自动，也可内置选择大小或形状
• 此时图表基础已经完成，但是要把柱形图和折线图分开。选中主要纵坐标轴 - 右击选择设置坐标轴格式 - 在坐标轴选项内将边界最大值设为110，最小值设为70，单位设置为10，在标签内将标签位置改为无；选择次要坐标中 - 右击选择设置坐标轴格式 - 将边界最大值设为1，最小值设置为0.6，单位设置为0.1，在标签内将标签位置改为无
• 选中网格线 - 设置网格线颜色和类型；移动图例位置；选中整个图表可直接修改字体；修改图表标题名称；分别点击柱状图/折线图 - 设置数据系列格式 - 线条颜色，标记填充色，标记边框颜色设置合适的颜色
• 分别点击柱形图和折线图 - 右击添加数据标签 - 在设置数据标签格式根据需要更改字体颜色，大小，图标就按照需求完成了

制作计划与实际对比图

• 各个销售人员有计划完成销售额，实际完成销售额，插入柱形图表 - 点击计划完成的柱形图 - 选择系列绘制在次坐标轴上
• 将主要坐标轴和次要坐标轴的边界设置为一致，最小值为0，最大值为900万，单位是100万
• 点击计划完成柱形图 - 将格式修改为无填充 - 红色边框，调整间隙宽度为160；点击实际完成柱形图 - 将格式修改为绿色填充，调整间隙宽度为160
• 选中主要坐标轴 - 设置坐标轴格式内的数字类别为自定义 - 类型为【#，“百万”，0】选中次要坐标轴，将标签位置改成无；选择网格线，将线条设置成无线条；选择图例 - 删除；修改图表标题名称，修改字体和大小，图标就按照需求完成了

制作双向柱形图

• 设置主次坐标轴，选中整个数据区域 - 插入选项卡 - 建议的图表 - 簇状条形图；点击出口条形图 - 设置数据系列格式 - 次坐标
• 目的是两个条形从中间分别向左右延伸，也就是说主坐标轴正中间的值应该是0，坐标轴的数值分别从中间的0向两边延伸，所以点击横向坐标轴 - 点击选择设置坐标轴格式 - 在坐标轴选项里边界最小值为-1，最大值为1，单位为0.25，勾选逆序刻度值让两个条形方向相反，在数字里面选择数字类别为自定义 - 类型为【0%；0%】这样就不会存在负数
• 调整下次坐标轴边界和单位大小，保持跟主坐标轴一致，可以忽略负数，在标签内将标签位置改成无，此时得到旋风图的基本形态
• 把纵坐标轴标签移出图外，点击纵坐标轴右键 - 设置坐标轴格式 - 标签内将标签位置改成低；点中网格线，在填充内将线条改成无线条（或者按delete键删除）
• 美化条形图，分别点中红色条形和蓝色条形 - 右键 - 设置数据系列格式 - 间隙宽度为100%，填充颜色改成适合的颜色，阴影预设内调整适当的阴影模式
• 在条形图上加相应数值，分别点中两侧条形图 - 点击添加数据标签，点击数据标签设置标签位置为居中，字体为白色；修改图表标题；移动图例位置
• 图片背景：选中图片 - 格式 - 设置对象格式 - 艺术效果 - 虚化 - 再选中图片 - 复制 - 选中图表区（表外框内的空白部分）- 右键 - 设置图表区格式 - 填充：图片或纹理填充 - 插入图片来自：剪贴板 - 选中图表数据区域 - 右键 - 设置绘图区格式 - 无填充

3.图表通用设置

利用复制粘贴更改数据系列显示样式

• 将柱状图改成三角形状图：插入 - 生成1个三角形 - 复制 - 选中柱状图 - 粘贴
• 将柱状图改成心形图：插入 - 生成1个心形 - 复制 - 选中柱状图 -粘贴后出现变形的心形 - 点击心形右键设置数据系列格式 - 填充点击层叠完成

巧用图表模板

• 选中图表 - 右击 - 保存为模板
• 选中数据 - 插入 - 图表模板
• 特别注意：数据表要和原图标的格式一致，否则无法应用图表模板

第二十一讲：动态图表

1.动态图表实现原理

理解图表中的数据系列

• 选择订购日期，彩盒，宠物用品3列数据插入一个折线图，在图表上右击选择数据则可以看到图表工具自动分的图例项
• 也可以直接制作该类图表：点击空单元格 - 插入 - 折线图 - 得出空的图表 - 右键图表 - 选择数据 - 图例项（系列）- 添加 - 系列名称：彩盒；系列值：选择B2:B13（不能选两列）同里添加宠物用品；水平（分类）轴标签 - 选择A2:A13

手工修改系列中的数值与坐标轴数据

• 建立空白图表，右键，选择数据，可以手工增删改表中的数值与坐标轴数据

小试牛刀：利用IF创建简单的动态图表

• 复选框：将数据复制到新表格中，添加开发工具选项卡 → 文件 - 选项 - 自定义功能区 - 右侧勾选开发工具 - 开发工具 - 插入 - 复选框；右键复选框1 - 编辑文字：彩盒（宠物用品同理）右键 - 拖拉外表狂可移动位置（移到H2,H4）
• 控件：右键彩盒/宠物用品复选框 - 设置格式控件 - 控制 - 单元格链接：选中G2（此时勾选复选框的话G2会变为TRUE，不勾选为FALSE）
• 公式：随便找个单元格G8，输入 = IF($G$2,$B$2:$B$13,$F$2:$F$13)，表示如果G2为true，则返回B2-13数据列，否则返回F2-13空白数据列，注意绝对引用
• 定义公式：复制公式，ESC退出，选择公式 - 定义名称，名称输入彩盒，引用位置粘贴公式 = IF($G$2,$B$2:$B$13,$F$2:$F$13)，宠物用品同理，此时G8单元格无用，可以删掉
• 制作图表：在任意空白单元格插入折线图，右键图表 - 选择数据 - 图例项：添加 - 定义名称为彩盒，Y值输入 = sheet1！彩盒（英文模式下的！）确定出现相应折线图，点击空白单元格，宠物用品同理
• 同一纵坐标：同时勾选彩盒和宠物用品，右键纵坐标 - 设置坐标轴格式 - 最小值0，最大值1400000，如果要去掉小数点，在设置坐标轴格式 - 类别为数字，小数位数为0
• 移动控件：将彩盒和宠物用品复选框文字删除 - 移动框框到相应图例前方，如果看不见将图表置于底层，将图表和控件组合，方便一起移动

2.利用offset函数与控件创建动态图表

offset函数概述

• = OFFSET(reference,rows,cols,[height],[width])
• 以某个单元格为基准，下移n行，右移n列，取n行n列

offset函数的动态引用示例

• counta($A:$A)计算A列有多少非空单元格取表格中所有的数据区域 = OFFSET($A$1,0,0,COUNTA($A:$A),11)
• 公式 - 定义名称 - 名称内输入数据区域；引用位置输入 = offset($A$1,0,0,COUNTA($A:$A),11)
• 选中任意有数据的单元格 - 插入 - 数据透视表 - 表/区域：数据区域
• 做一个数据透视表选择：行标签：产品类别；数值：金额
• 此时，在原表格下边增加行，数据透视表刷新后，新增金额也随之更新

动态图表1：永远返回最后10行数据

• 将图表2中的数据复制到新的Excel表中，取最后10行数据的话，以B1单元格为基准，一共17行的话下移7行右移0列，就可以取最后10行，规律就是总行数 - 10就是下移的行数，输入公式 = OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)
• 公式 - 定义名称 - 名称：成交量；引用位置 = OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)
• 插入 - 折线图 - 右键空图表 - 选择数据 - 图例项：添加 - 定义名称为成交，Y值输入 = sheet2！成交量
• 日期也对应引用，= OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)；公式 - 定义名称 - 名称：日期；引用位置：=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)；右键折线 - 选择数据 - 水平分类轴标签输入 = sheet2！日期

动态图表2：通过控件控制图表数据

• 每点一次第一个滚动条，取的行数就往下走一行，第一个滚动条空值OFFSET往下走的位置，第二个滚动条控制图展现多少数据
• 将数据复制到新的Excel表格中
• 开发工具 - 插入 - 滚动条 - 拉出合适的长度（插入两个滚动条）在第一个滚动条上右键 - 设计控件格式 - 最小值：1；单元格链接：$D$2（直接点）第二个滚动条同样操作，链到$D$4
• 实现成交量动态区域 = OFFSET($B$1,$D$2,$D$4,1)，公式 - 定义名称 - 名称：成交量；引用位置：=OFFSET($B$1,$D$2,0,$D$4,1)
• 插入 - 柱形图 - 右键图表 - 选择数据 - 添加图例项 - 系列名称：成交量；系列值：=sheet3！成交量；在第一个滚动条上右键 - 设计控件格式 - 最小值：1；单元格链接：￥D$2（直接点）第二个滚动条同样操作，链到$D$4
• 实现成交量动态区域=OFFSET($B$1,$D$2,0,$D$4,1)，公式 - 定义名称 - 名称：成交量；引用位置：=OFFSET($B$1,$D$2,0,$D$4,1)
• 插入 - 柱形图 - 右键图表 - 选择数据 - 添加图例项 - 系列名称：成交量；系列值：=sheet3！成交量
• 实现日期动态区域 =OFFSET($A$1,$D$2,0,$D$4,1)，公式 - 定义名称 - 名称：日期；引用：=OFFSET($A$1,$D$2,0,$D$4,1)
• 点击表格柱形 - 右键 - 选择数据 - 水平（分类）轴标签 - 编辑 - 轴标签区域：=sheet3！日期

第二十二讲：甘特图与动态甘特图

1.甘特图

• 选中数据A1:C9 - 插入选项卡 - 插入堆积条形图

• 点中生成的图表区域 - 图表设计，把图表修改成想要的颜色

• 右击条形框 - 设置数据系列格式 - 填充：无填充；边框颜色：无线条；阴影：无阴影

• 右键横坐标 - 设置坐标轴格式 - 坐标轴选项里边界最小值：41760；最大值：41790；数字选项里类别选择日期，类型选择只有月和日

• 美化表格：右键条形 - 设置数据系列格式 - 间隙宽度：10%；右键纵坐标 - 设置坐标轴格式 - 勾选逆序类别；右键网格线 - 设置网格线格式 - 线型 - 短划线类型：虚线。即完成甘特图，也可根据实际需求继续美化

2.动态甘特图

• 选中数据A1:C9，将数据复制到新表中；在E1单元格新建【已完成】辅助列；在F1单元格新建【未完成】辅助列；在B11单元格任意输入一个项目日期，假设今天是【2014/5/11】
• 在E2单元格输入公式：=IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))（如果今日日期小于计划开始日，说明已完成0天；如果今日日期大于计划开始日，说明已完成0天；如果今日日期大于计划开始日 + 计划持续天数，说明已完成天数为计划持续天数；如果以上两种都不是，说明已完成天数为今日日期 - 计划开始日）
• 在F2单元格输入公式：=C2 - E2；选中A1 - B9，按住Ctrl，选中E1-E9，插入堆积条形图，隐藏蓝颜色条形，设置纵坐标轴格式，设置分类间距，逆序列，网格线
• 开发工具选项卡插入【滚动条】- 右键滚动条 - 设置控件格式 - 控制：最小值：0；最大值：30；单元格链接：点击D11（空白单元格），B11单元格改为=41760+D11
• 插入选项卡 - 文本框：横排文本框 - 选中文本框 - 在编辑栏输入 = ￥B$11

第二十三讲：Excel图表与PPT

1.双坐标柱形图的补充知识：主次坐标设置以及避让

根据A1:C6的数据生成柱形图，由于营业额和指标完成度数字相差太多，所以柱形的比例也不对，因此应该给指标完成度增加一个次坐标轴

首先可以点击【指标完成度】柱形，右键设置数据系列格式，将它设置在次坐标轴上，此时两个颜色的柱形会重叠显示

如果不想让主次坐标轴上的数据重叠，可以用两个空的柱形将两个有颜色的柱形分别往左往右挤开

在图标区域右击 - 选择数据，点击添加，系列名称自由命名，数据选择一组空数据即可，但是要使数据和原某一系列数据一样大小，创建两个空白系列，确定

现在我们看到两组数据已经被挤开了，我们可以将我们设置的两组空白数据分别放置在主次坐标轴，选中图表区域，点击格式选项卡 - 将系列3放到主坐标

图表区域右击 - 选择数据，调整4个图例项的位置，确保4个柱形图不会重叠展示即可，最后再对表格进行美化

2.饼图美化

饼图美化常见设置

• 选择A1:B6数据，插入一个三维饼图
• 右键图表区，选择三维旋转，将高度设置为原图表的30%，饼图会变薄

• 再选择饼图，右击设置数据系列格式，对饼图进行三维设置

• 选中饼图，右击选择添加数据标签，点击图标设计选项卡 - 添加图表元素 - 数据标签居中。再点击饼图上的数字，点击开始选项卡，修改字体，颜色和大小，即完成饼图美化

制作双层饼图

• 根据下面效果图显示有两层饼图，先做最上层的饼图

• 选中B2:C10销售区域汇总的两列数据，插入二维饼图

• 右键饼图 - 选择数据，添加图例项 - 在Y值内选中汇总下的三个数字 - 确定

• 此时饼图没有任何变化，拉开上面饼图下面也没有显示，原因是两层饼图在同一个坐标轴，所以此时需要将上面的饼图调整到次坐标轴上，点击上层饼图右击设置数据系列格式，将系列绘制在次坐标轴

• 此时，再拉上层饼图则会全部缩小，再单个选中上层缩小的饼图往中间聚拢

• 选中上层饼图，右击添加数据标签，再右击选择设置数据标签格式，并调整标签选项勾选类别名称，百分比，将标签位置设为标签内。在大饼图下并调整标签选项勾选类别名称，百分比，将标签位置设为最佳匹配，同时右键图表区，选择数据，在图例项选择系列2，降水平轴标签选择一科到三科，确定

• 再对饼图进行美化，调整文字字体，颜色，大小即可

3.PPT中的图表

图表粘贴

• 如果选择一张图表在PPT中粘贴，直接粘贴会变为PPT本身设置的颜色，因为默认使用了PPT的配色方案，如果要修改PPT的配色方案：选择设计选项卡，选中自己希望的颜色即可
• 在粘贴Excel中的图表时，在Excel中复制出来，在空白PPT模板中右键点击，可选择粘贴选项
• 使用目标主题，此时颜色会选用PPT中本来设置好的颜色
• 保留原格式，此时颜色会保持为原来Excel中的配色
• 同时选中使用目标主题和链接到Excel数据，此时颜色会选用PPT中本来设置好的颜色，并且数据会随着Excel中数据的变动而更新，可在图表工具中的设计中点击刷新数据
• 保留原格式，此时颜色会保持为原来Excel中的配色
• 同时选中使用目标主题和链接到Excel数据，此时颜色会选用PPT中本来设置好的颜色，并且数据会随着Excel中数据的变动而更新，可在图表工具中的设计中点击刷新数据
• 同时选中保留原格式和链接到Excel数据，此时颜色会保持为原来Excel中的配色，并且数据会随着Excel中的数据的变动而更新，可在设计中点击刷新数据

图表动画

• 将Excel中的图表作为图表粘贴在PPT中，选择动画选项卡，选择进入效果擦除即可生成一个简单的进入动画
• 点击动画选项卡下的动画窗格，点击已经设置的动画效果，底部会出现点击效果选项，选择图表动画，在组合图表中按需选择即可

第二十四讲：宏表函数

1.利用宏表函数获取信息

get.cell函数

• GET.CELL(type_num,reference)，其作用是返回引用单元格的信息
• type_num指明单元格中信息的类型，用数字表示，范围为1-66，具体数字代表的类型可见[get.cell]表
• reference为引用的单元格或区域范围
• 用法：函数不能直接在单元格内写，必须先定义名称（公式 → 定义名称 → 引用位置）

get.workbook函数

• GET.WORKBOOK(type_num,name_text)，其作用是用来提取工作簿的信息数据
• type_num：必需，指明要得到的工作簿信息类型的数字代码
• name_text：可选，指明打开的工作簿的名字，如果省略，则默认为当前活动的工作簿
• 用法：前提是定义名称（如“工作表名”）即引用位置=get.workbook()

HYPERLINK函数

• 如果想做成超链接，点击表名即可直达表格内的单元格，需要用到HYPERLINK函数
• HYPERLINK(link_location,friendly_name)，用于创建一个快捷方式（跳转），是excel超级链接的函数实现方法
• link_location：为要打开的文件名称或完整路径。可以是本地硬盘，UNC路径或URL路径
• friendly_name：单元格中显示的跳转文本或数字值，如果省略，第一个参数显示为跳转文本

2.宏表函数常见应用

EVALUATE函数

• EVALUTE(formula_text)，将参数字符串表达式进行运算求出结果
• 注：宏函数并不能单独在单元格中运行，在名称定义中运行

SUBSTITUTE函数

• SUBSTITUTE(text,old_text,new_text,[instance_num])，替换文本内容
• text：不省略参数，为需要替换其中字符的文本，或对含有文本的单元格的引用
• old_text：不省略参数，为需要替换的旧文本
• new_text：不省略参数，但有默认值空，为换成的新文本内容
• instance_num：替换第几个

最后

以上就是香蕉大侠为你收集整理的王佩丰 Excel 基础24讲 | 学习笔记（全）第一讲：认识Excel第二讲：Excel单元格格式设置第三讲：查找，替换与定位第四讲：排序与筛选第五讲：分类汇总与数据有效性第六讲：认识数据透视表第七讲：认识函数与公式第八讲：IF函数逻辑判断第九讲：COUNTIF函数第十讲：SUMIF函数第十一讲：Vlookup函数第十二讲：Match + Index第十三讲：邮件合并第十四讲：日期函数第十五讲：条件格式与公式第十六讲：简单文本函数第十七讲：数学函数第十八讲：Vlookup与数组第十九讲：ind的全部内容，希望文章能够帮你解决王佩丰 Excel 基础24讲 | 学习笔记（全）第一讲：认识Excel第二讲：Excel单元格格式设置第三讲：查找，替换与定位第四讲：排序与筛选第五讲：分类汇总与数据有效性第六讲：认识数据透视表第七讲：认识函数与公式第八讲：IF函数逻辑判断第九讲：COUNTIF函数第十讲：SUMIF函数第十一讲：Vlookup函数第十二讲：Match + Index第十三讲：邮件合并第十四讲：日期函数第十五讲：条件格式与公式第十六讲：简单文本函数第十七讲：数学函数第十八讲：Vlookup与数组第十九讲：ind所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。