Microsoft Excel
基础、函数与实战教程

第一部分 入门篇已学完

1. Excel 是什么、版本与文件格式已学完

Microsoft Excel 是微软 Office 办公套件中的电子表格软件，用于数据录入、计算、分析、可视化和自动化处理。它以“工作簿”为文件单位，一个工作簿包含多个“工作表（Sheet）”，每张工作表由行列交叉的“单元格”构成网格。

常见应用场景：

财务记账、预算表、报销单

销售数据统计与分析报表

项目进度管理、甘特图

数据清洗、透视分析、可视化图表

结合宏与 VBA 实现办公自动化

1.1 版本说明

本文菜单与快捷键主要按 Windows 桌面版编写，函数兼容范围覆盖 Excel 2019、2021、2024 与 Microsoft 365。macOS 的快捷键和加载项不同；“在线教程”指阅读方式，不代表所有示例都能在 Excel 网页版运行。版本标注约定如下：

【2019+】：指 Excel 2019 及之后的永久版，以及支持该功能的 Microsoft 365；具体平台仍需看附录

【365/2021+】：Excel 2021、2024 与 Microsoft 365 支持的函数

【365】：以 Microsoft 365 为适用环境；部分函数已进入 Excel 2024，具体以附录和函数官方页面为准

1.2 文件格式对比

1.3 Excel 的规格上限

2. 认识 Excel 界面已学完

打开 Excel 后，界面主要由以下几部分组成：

3. 工作簿与工作表基本操作已学完

3.1 新建、保存、打开

新建工作簿：Ctrl+N，或“文件”→“新建”选择模板

保存：Ctrl+S；另存为：F12 或“文件”→“另存为”

打开已有文件：Ctrl+O

自动恢复：Excel 默认每隔几分钟自动保存临时文件，意外关闭后可在“文件”→“信息”→“管理工作簿”中恢复

3.2 工作表管理

新建工作表：点击标签栏“+”号，或右键标签→“插入”

重命名：双击工作表标签直接输入新名称

删除：右键标签→“删除”（此操作不可撤销）

移动/复制：右键标签→“移动或复制工作表”，勾选“建立副本”即为复制

标签颜色：右键标签→“工作表标签颜色”，便于分类管理

隐藏/取消隐藏：右键标签→“隐藏”/“取消隐藏”

同时选中多个工作表（编组）：按住 Ctrl 点击多个标签，可批量输入或格式化

4. 数据输入、数据类型与自动填充已学完

4.1 三种基本数据类型

Excel 只有三类基础数据：文本、数值、逻辑值。日期、时间、货币、百分比本质上都是“带特定显示格式的数值”。判断某格是文本还是数值，最简单的方法是看默认对齐方式：文本靠左，数值靠右。

文本：默认左对齐；若数字需按文本处理（如以 0 开头的编号），先输入英文单引号 ' 再输入数字，例如 '0012

数值：默认右对齐，支持整数、小数、百分比、科学计数法等

日期/时间：输入 2026/7/29 或 2026-7-29 识别为日期；输入 20:30 识别为时间

单元格内换行：Alt+Enter

4.2 日期的本质是序列号（新手必读）

默认 1900 日期系统中，1900/1/1 对应序列号 1。为兼容旧软件，Excel 保留了不存在的 1900/2/29；旧工作簿也可能使用 1904 系统，两者相差 1462 天。时间是一天的小数部分：0.5 表示 12:00。跨文件复制日期后整体偏移时，先核对日期系统。

理解这一点，很多“怪现象”就都说得通了：

两个日期可以直接相减，结果就是相差的天数：=B2-A2

给日期加 7 就是加一周：=A2+7

日期单元格突然显示成 46000 这样的数字，不是数据坏了，只是格式被改成了“常规”——按 Ctrl+Shift+3 改回日期格式即可

反过来，若想查看某个日期的序列号，把格式改为“常规”即可

4.3 文本型数字与真数值（错误高发区）

“看起来是数字，实际是文本”是 Excel 中最常见的隐蔽问题。它会导致求和结果为 0、VLOOKUP 明明有值却返回 #N/A、排序顺序错乱。

识别方法：

数字默认靠左对齐，就要怀疑它是文本

单元格左上角出现绿色三角标记，选中后有“以文本形式存储的数字”提示

用公式验证：=ISNUMBER(A1) 返回 FALSE 即为文本

选中一列数字，状态栏若只显示“计数”而没有“求和”“平均值”，说明它们不是数值

三种转换方法：

1. 选中带绿色三角的区域，点击左侧感叹号图标→“转换为数字”（最快）

2. 选中该列→“数据”→“分列”→直接点“完成”（批量修复的通用方案）

3. 在空白单元格输入 1 并复制，选中目标区域→选择性粘贴→运算“乘”

反向问题也存在：数字被当成数值，但你需要它保留前导零（如工号 0012）。此时应先把单元格格式设为“文本”再输入，或使用自定义格式代码 0000 让其显示为四位数。

4.4 自动填充（填充柄）

选中单元格后右下角会出现小十字“填充柄”，拖动即可：

复制内容：普通文本或数字直接拖动为复制

序列填充：输入“1、2”后选中两格再拖动，按等差数列递增；输入“一月”拖动可自动补全后续月份

双击填充柄：当左侧列有连续数据时，双击可一次性填充到与左列等长

自定义序列：“文件”→“选项”→“高级”→“编辑自定义列表”，添加自己的填充序列（如部门名称）

5. 规范表格设计原则（核心基础）已学完

这一节没有任何按钮操作，却是整本教程最重要的部分。后面讲到的排序、筛选、函数、数据透视表能否顺利工作，几乎完全取决于你的表格结构是否规范。绝大多数“Excel 用不起来”的问题，根源都在这里。

5.1 区分“数据表”与“报表”

“数据表”是用来存储和计算的原始数据，“报表”是给人看的最终呈现。二者应分开放在不同的工作表中：原始数据保持规范整洁，报表通过透视表、公式或图表从原始数据生成。把二者混在一张表里（一边是明细一边是花哨的汇总），是后续所有麻烦的开始。

5.2 一维表 vs 二维表

一维表（也称长表、清单式表格）是标准的数据存储结构：每一行是一条完整记录，每一列是一个字段。

一维表（推荐用于存储）

日期 地区 产品 销售额

2026-01-05 华东 A 1200

2026-01-05 华南 A 800

2026-02-03 华东 B 1500

二维表（交叉表）把某个字段的值铺开成了多个列，适合阅读，但不适合作为原始数据存储：

二维表（适合展示，不适合存储）

地区 1月 2月 3月

华东 1200 1500 1800

华南 800 900 1100

原则：用一维表存储数据，需要二维展示时用数据透视表生成。若已有二维表需要转回一维，用 Power Query 的“逆透视列”转换，并核对行数与汇总金额（见第 23 章）。

5.3 表格设计七条设计原则

1. 首行必须是唯一且明确的表头，且只占一行——不要用两行合并的复杂表头

2. 不要有合并单元格。合并单元格会破坏排序、筛选、透视表和公式填充，是数据表的头号杀手

3. 不要有空行和空列。Excel 依靠连续区域判断数据边界，一个空行会让 Ctrl+A、筛选和透视表只识别到一半数据

4. 一个单元格只放一个信息。“张三 138xxxx1234”应拆成“姓名”和“电话”两列

5. 同一列的数据类型必须一致。金额列里混入“待定”“暂无”等文字，会让求和与平均值全部失效

6. 不要在数据区域中间插入小计行。小计交给透视表或分类汇总生成，原始数据只保留明细

7. 不要用颜色作为唯一的信息载体。“标黄表示已付款”这种约定无法参与计算，应单独增加一列“付款状态”

5.4 快速自检清单

在把一张表交给公式或透视表之前，用这四步自检：

1. 按 Ctrl+A：如果选中范围小于你的实际数据，说明中间有空行或空列

2. 按 Ctrl+End：如果光标跳到了远超实际数据的位置，说明存在残留的空白格式，删除多余行列后保存可减小文件体积

3. 用 =ISNUMBER(A2) 检查数值类型。默认对齐只能提供线索，手动设置过对齐后不能据此判断

4. “开始”→“查找和选择”→“定位条件”→“合并单元格”：确认数据区内没有合并单元格

6. 行列操作与窗口冻结/拆分已学完

6.1 行列基本操作

插入行/列：右键行号或列标→“插入”，或选中后 Ctrl+Shift+加号(+)

删除行/列：右键→“删除”，或 Ctrl+减号(-)

调整行高/列宽：拖动行号或列标边界；或右键→“行高/列宽”精确设置数值

自动调整列宽：双击列标右边界，或“开始”→“格式”→“自动调整列宽”

隐藏/取消隐藏：右键→“隐藏”；取消隐藏需先选中相邻两侧的行列再右键→“取消隐藏”

6.2 冻结窗格

表格很长或很宽、需要固定表头查看时使用：“视图”→“冻结窗格”。

冻结首行：始终显示第一行

冻结首列：始终显示第一列

冻结拆分窗格：先选中某个单元格（如 B2），再点此选项，会同时冻结该单元格上方的所有行与左侧的所有列

6.3 拆分窗口

“视图”→“拆分”可将窗口分成上下或左右（或四个）独立滚动的区域，便于对照查看同一张表的不同部分。与冻结窗格不同的是，拆分出的每个区域都能各自滚动。

7. 单元格格式化已学完

7.1 数字格式

选中单元格按 Ctrl+1 打开“设置单元格格式”，在“数字”选项卡中设置：

7.2 字体、边框、底纹、对齐

字体设置：在“开始”选项卡调整字体、字号、颜色、加粗/斜体/下划线

边框：“开始”→边框按钮下拉，或 Ctrl+1→“边框”选项卡自定义线型与位置

底纹（填充色）：“开始”→油漆桶图标选择填充颜色

对齐方式：水平/垂直对齐，以及自动换行、缩小字体填充、跨列居中等文本控制

合并单元格：仅在纯展示用的报表标题处使用，数据区域请改用“跨列居中”

7.3 格式刷与样式

格式刷：选中已设置好格式的单元格，点击“开始”→格式刷图标，再刷过目标区域即可复制格式；双击格式刷图标可连续刷多处，按 Esc 退出

清除格式：“开始”→“清除”→“清除格式”，只清除样式不删除内容

套用单元格样式：“开始”→“单元格样式”，快速应用预设的标题、强调、警告等样式

8. 复制、粘贴与选择性粘贴已学完

普通复制粘贴使用 Ctrl+C 和 Ctrl+V。选择性粘贴（Ctrl+Alt+V）还能分别控制数值、公式、格式、列宽和运算方式：

第二部分 公式与函数篇已学完

9. 公式基础与单元格引用已学完

9.1 公式的基本写法

Excel 中所有公式都以等号 = 开头：

=A1+B1    （加法）

=A1-B1    （减法）

=A1*B1    （乘法）

=A1/B1    （除法）

=A1^2     （乘方）

=(A1+B1)*C1  （用括号控制运算顺序）

输入公式后按 Enter 确认。按 Ctrl+`（数字 1 左边的键）可在“显示公式”与“显示结果”两种视图间切换，便于整表核对逻辑。

9.2 单元格引用：相对、绝对、混合

选中公式中的地址后按 F4，可在四种引用方式间循环切换。

9.3 跨工作表与跨工作簿引用

=Sheet2!A1        （引用同一工作簿中 Sheet2 的 A1）

='1月销售'!B2       （表名以数字开头或含空格时需加单引号）

=[销售数据.xlsx]Sheet1!A1 （引用另一个已打开的工作簿）

9.4 定义名称

“公式”→“定义名称”可以给单元格或区域起一个易记的名字（如 Tax_Rate），公式中直接用名称代替地址：=B2*Tax_Rate。这样既提高可读性，也便于全表统一维护。

9.5 循环引用

如果公式直接或间接引用了自己所在的单元格（例如在 A1 中输入 =A1+1），就形成了循环引用，Excel 会弹出警告并把结果显示为 0。状态栏左下角会提示循环引用所在的单元格地址。

解决办法是修正公式逻辑。只有在做迭代计算（如某些财务模型）时，才需要在“文件”→“选项”→“公式”中勾选“启用迭代计算”，并设置最多迭代次数。

10. 常用函数详解（数学/统计/文本/逻辑/查找/日期/通配符）已学完

函数的通用写法为：=函数名(参数1, 参数2, …)。以下按类别介绍最常用、最重要的函数。

10.1 数学与求和类

SUMPRODUCT 的两个经典用法：

=SUMPRODUCT(B2:B10, C2:C10)

单价×数量后直接求出总金额，无需辅助列

=SUMPRODUCT((A2:A100="华东")*(B2:B100>1000))

统计“华东且金额大于1000”的记录条数（等效于 COUNTIFS）

10.2 统计类

密集排名（并列不跳号，如 1、1、2）可用以下写法。先在 B2:B5 输入 90、90、80、70，C2 输入公式并向下填充，结果应为 1、1、2、3。示例区域必须全部是数值，不能包含空白或错误值：

=SUMPRODUCT(($B$2:$B$5>B2)/COUNTIF($B$2:$B$5,$B$2:$B$5))+1

10.3 文本处理类

10.4 逻辑判断类

=IFS(A1>=90,"优秀",A1>=80,"良好",A1>=60,"及格",TRUE,"不及格")

10.5 查找与引用类（重点）

查找函数用于按编号补充姓名、价格或类别。先检查编号是否唯一，以及两张表中的编号类型是否一致。

VLOOKUP 与 INDEX+MATCH 的等效写法：

=VLOOKUP(A2, 员工信息表!$A:$C, 2, 0)

=INDEX(员工信息表!$B:$B, MATCH(A2, 员工信息表!$A:$A, 0))

XLOOKUP 反向查找（VLOOKUP 无法做到）：

=XLOOKUP(A2, 员工信息表!$C:$C, 员工信息表!$A:$A, "未找到")

VLOOKUP 的四大常见错误：

1. 第 3 参数列号搞错。它是相对于“数据表”区域的第几列，不是工作表的列号。若数据表写作 C:F，想取 D 列的值，列号应是 2 而不是 4

2. 忘记锁定数据表区域。向下填充时区域会跟着偏移，应写成 $A:$C 或 $A$2:$C$100

3. 省略第 4 参数会启用近似匹配。按编号匹配时明确写 FALSE 或 0；按分数区间等做近似匹配时可用 TRUE，但首列必须升序排列，并测试边界值

4. 查找值与数据源类型不一致。一边是数值 1001、一边是文本 "1001" 时永远匹配不上，返回 #N/A。先用第 4.3 节的方法统一类型

10.6 日期与时间类

=DATEDIF(入职日期, TODAY(), "Y") & "年" & DATEDIF(入职日期, TODAY(), "YM") & "个月"

10.7 通配符的使用

在 COUNTIF、SUMIF、SUMIFS、COUNTIFS、VLOOKUP、MATCH、SEARCH 等函数的条件参数中，可以使用通配符做模糊匹配：

=SUMIF(B:B, "*笔记本*", C:C)

对产品名中包含“笔记本”的所有记录求和

11. 动态数组函数（Excel 2021 / Microsoft 365）已学完

Excel 2021 与 Microsoft 365 支持“动态数组”：一个公式的结果可以自动“溢出”填充到多个单元格，不再需要 Ctrl+Shift+Enter 输入数组公式。

组合使用——筛选华东区数据并按第 4 列降序排列：

=IF(COUNTIF(C2:C100,"华东")=0,"无匹配记录",SORT(FILTER(A2:D100,C2:C100="华东"),4,-1))

12. 公式常见错误与审核工具已学完

12.1 错误代码速查

12.2 公式审核工具

“公式”选项卡下的“公式审核”组是排错利器：

追踪引用单元格 / 追踪从属单元格：用箭头显示公式与相关单元格之间的依赖关系

显示公式（Ctrl+`）：整表切换查看公式而非结果，快速核对逻辑

错误检查：自动扫描全表并标记可能有问题的公式

公式求值：单步执行公式的每一部分，精确定位是哪一步出错

监视窗口：把关键单元格加入监视列表，滚动到工作簿任何位置都能持续查看其值

第三部分 数据管理篇已学完

13. 排序已学完

简单排序：选中数据列中任意单元格，“数据”→“升序 / 降序”

多条件排序：“数据”→“排序”，可添加多个条件（如先按部门，再按销售额降序）

自定义顺序排序：在排序对话框的“次序”中选择“自定义序列”，可按职级、月份等业务顺序排列，而非按拼音

按颜色排序：排序依据可选“单元格颜色”“字体颜色”或“条件格式图标”

横向排序：排序对话框→“选项”→“按行排序”，用于对列进行左右重排

14. 筛选（自动筛选/高级筛选）已学完

14.1 自动筛选

选中表头任意单元格，“数据”→“筛选”（Ctrl+Shift+L），每列表头出现下拉箭头。可按值勾选，也可按文本、数字、日期条件筛选（如“大于”“介于”“前 10 项”），还支持按颜色筛选。

14.2 高级筛选

“数据”→“高级”适用于复杂的多条件组合，需要先在表格外单独设置一个“条件区域”（条件区域的表头必须与数据表表头完全一致）：

写在同一行的多个条件是“且”关系（AND）

写在不同行的条件是“或”关系（OR）

勾选“将筛选结果复制到其他位置”，可在不影响原数据的情况下导出结果

勾选“选择不重复的记录”，可在筛选的同时去重

15. 数据验证已学完

“数据”→“数据验证”用于规范录入、从源头减少错误：

下拉列表：验证条件选“序列”，来源可直接输入用英文逗号分隔的选项（如：是,否），或引用某个单元格区域

限制输入范围：如只允许 0–100 的整数、限制日期区间、限制文本长度

自定义公式：灵活度最高，例如禁止重复录入 =COUNTIF($A:$A,A2)=1

输入提示与出错警告：在对应选项卡中自定义提示文字，提升填表体验

圈释无效数据：“数据验证”下拉→“圈释无效数据”，可把已录入的违规值全部圈出来

16. 数据分列已学完

当一列数据混合了多种信息时（如“张三,138xxxx1234”），使用“数据”→“分列”：

分隔符号：按逗号、空格、Tab 或自定义符号拆分成多列

固定宽度：按字符位置手动拖动分割线拆分

在向导第 3 步可为每列指定格式，把长数字列设为“文本”能避免身份证号变成科学计数法

常见妙用：把文本型数字批量转成真数值——选中该列，分列，直接点“完成”

17. 删除重复项与分类汇总已学完

17.1 删除重复项

“数据”→“删除重复值”，可指定按哪些列判断重复，Excel 保留第一条并删除其余重复行。

17.2 分类汇总

适用于已按分类字段排序好的数据：“数据”→“分类汇总”，选择分类字段、汇总方式和汇总项，Excel 会在每个分类下方自动插入汇总行，并生成可折叠的分级显示（左侧的 1/2/3 与 +/- 按钮）。

18. 超级表（Table）已学完

选中数据区域后按 Ctrl+T（或“插入”→“表格”）即可转换为“超级表”，好处很多：

自动扩展：在表格下方或右侧新增数据时，格式、公式、条件格式会自动延续

结构化引用：公式可用表名与列名代替地址，如 =SUM(表1[销售额])，可读性远高于 =SUM(D2:D999)

自动汇总行：可对每列选择求和、平均、计数等汇总方式

表头始终可见：向下滚动时列标会自动变成表头名称，无需冻结窗格

与透视表、切片器无缝配合：新增数据后刷新透视表即可纳入，无需重设数据源

19. 条件格式已学完

“开始”→“条件格式”可根据单元格的值自动改变外观，是数据可视化最轻量的手段。

整行高亮案例——选中 A2:D100，新建规则→“使用公式确定要设置格式的单元格”：

=$C2>1000

这里的关键是列绝对、行相对（$C2）：锁定列保证整行都参照 C 列判断，行号不锁定保证每行独立判断自己的值。

19.1 规则优先级与“如果为真则停止”

同一区域可以叠加多条规则，Excel 按“管理规则”列表中从上到下的顺序依次判断，排在越上面优先级越高。当多条规则设置了冲突的格式（如都改填充色）时，靠上的规则获胜。

调整优先级：“条件格式”→“管理规则”，用上下箭头移动规则顺序

“如果为真则停止”：勾选后，一旦该行满足此规则，就不再继续判断下面的规则，常用于构建互斥的分级配色

清除规则：“条件格式”→“清除规则”，可选择清除所选区域或整个工作表

第四部分 数据分析篇已学完

20. 数据透视表已学完

数据透视表是 Excel 最强大的汇总分析工具：不写任何公式，就能对成千上万行数据做多维度分类汇总与交叉分析。

20.1 创建步骤

1. 确保数据源是规范的一维表（见第 5 章），并建议先按 Ctrl+T 转成超级表

2. 点击数据区域任意单元格，“插入”→“数据透视表”

3. 确认数据源区域，选择放置位置（推荐“新工作表”）

4. 在右侧“数据透视表字段”窗格中，把字段拖入“筛选器、列、行、值”四个区域

20.2 值汇总方式与值显示方式

这是两个不同的设置，很多人只用了前者：

值汇总方式：决定“怎么算”——求和、计数、平均值、最大值、最小值等。右键值区域→“值汇总依据”

值显示方式：决定“怎么展示”——右键值区域→“值显示方式”，可切换为占比、同比、环比等

20.3 分组、计算字段与刷新

日期分组：右键日期字段→“组合”，可按年、季度、月自动分组

数值分组：右键数值字段→“组合”，可按区间分段统计（如 0–10、10–20）

计算字段：“数据透视表分析”→“字段、项目和集”→“计算字段”，可基于现有字段新增公式列，如“利润率 = 利润 / 销售额”

钻取明细：双击任意汇总数字，会自动新建工作表列出构成该数字的所有明细行

刷新：源数据变化后，右键→“刷新”或“数据”→“全部刷新”（快捷键 Ctrl+Alt+F5）

20.4 切片器与日程表

“数据透视表分析”→“插入切片器”，可为字段生成按钮式可视化筛选器；日期字段建议用“插入日程表”，以时间轴形式筛选更直观。

21. 数据透视图已学完

基于透视表创建的图表：“插入”→“数据透视图”，或在已有透视表上点击“数据透视表分析”→“数据透视图”。图表会随透视表的字段布局与筛选自动联动，并自带字段筛选按钮，适合做动态汇总看板。若嫌图上的筛选按钮影响美观，可右键→“隐藏图表上的所有字段按钮”。

22. 图表制作与美化已学完

22.1 创建图表

选中数据区域（含表头），在“插入”选项卡选择图表类型；或按 Alt+F1 在当前表快速插入默认柱形图，按 F11 创建独立的图表工作表。

22.2 图表类型选择建议

22.3 美化技巧

点击图表后右上角有三个快捷按钮：图表元素（增删标题、坐标轴、数据标签）、图表样式、图表筛选器

双击任意元素（坐标轴、数据系列、图例）可打开对应的格式设置窗格精细调整

组合图：右键某个数据系列→“更改系列图表类型”，单独改为折线并勾选“次坐标轴”

添加趋势线：右键数据系列→“添加趋势线”，可选线性、指数、移动平均等

减少视觉噪音：删除多余的网格线和图例，直接为数据添加数据标签，图表会清爽很多

22.4 动态图表

最简单的动态方案是基于“超级表”创建图表，数据增减后图表自动同步。更进阶的做法是结合“定义名称 + OFFSET/INDEX”或“数据验证下拉框 + INDEX”，实现根据下拉选择切换显示内容的图表。

23. Power Query 数据获取与转换已学完

Power Query（“数据”→“获取数据”）是 Excel 内置的数据清洗与整合工具，适合处理多个文件或多种格式的数据。编辑器会记录每一步转换；源数据更新后，执行“刷新”即可按原顺序重新运行这些步骤。

23.1 典型流程

1. “数据”→“获取数据”，选择数据源：可从文件夹批量合并多个 Excel/CSV、从数据库、从网页等

2. 进入 Power Query 编辑器，执行清洗操作，每一步都记录在右侧“应用的步骤”中，可随时回退修改

3. 常见清洗动作：删除重复行、拆分列、合并列、更改数据类型、筛选行、填充空值、逆透视列

4. 点击“关闭并上载”，结果导入为 Excel 表格或数据模型

5. 源文件更新后，在 Excel 中点击“全部刷新”即可，无需重复手工清洗

23.2 逆透视列（最常用技巧）

当拿到的是二维表（各月份分别占一列），而分析需要一维表时：选中除首列外的所有列→“转换”→“逆透视列”，一键把宽表转为“属性 + 值”的标准长表结构。这正是第 5.2 节所说的从二维回到一维的最快方法。

24. 假设分析已学完

24.1 模拟运算表

“数据”→“模拟分析”→“模拟运算表”：设定一个或两个输入变量的多种取值，批量计算出对应结果。常用于敏感性分析，如不同利率与年限组合下的月供对比表。

24.2 单变量求解

“数据”→“模拟分析”→“单变量求解”：已知想要的目标结果（如利润 10 万元），反推需要的输入值（如销量应达到多少）。Excel 通过迭代自动求解。

24.3 方案管理器

“数据”→“模拟分析”→“方案管理器”：保存多组输入值组合（乐观 / 正常 / 悲观情景），一键切换查看对应结果，还能生成方案摘要报告做横向对比。

24.4 规划求解

先在“文件”→“选项”→“加载项”中勾选“规划求解加载项”启用。启用后“数据”选项卡会出现“规划求解”，用于在满足多个约束条件的前提下，求出让目标单元格达到最大、最小或指定值的最优解，适合资源分配、成本最小化等运筹优化问题。

第五部分 进阶与自动化篇已学完

25. 宏与 VBA 入门已学完

25.1 显示开发工具选项卡

功能区默认不显示“开发工具”，需手动开启：“文件”→“选项”→“自定义功能区”，在右侧勾选“开发工具”。

25.2 录制宏（无需写代码）

1. “开发工具”→“录制宏”，为宏命名并选择保存位置

2. 正常执行一遍你想自动化的操作（如格式化表格、生成汇总）

3. “开发工具”→“停止录制”

4. 通过“开发工具”→“宏”（Alt+F8）选中宏名→“执行”，即可一键重放；也可把宏指定给按钮或快捷键

关于“个人宏工作簿”：录制宏时若把保存位置选为“个人宏工作簿”，代码会存入一个名为 PERSONAL.XLSB 的隐藏工作簿。它会随 Excel 自动打开，因此其中的宏在你打开的任何文件里都能调用，非常适合存放日常通用工具。若选择“当前工作簿”，则该宏只能在这个文件中使用，且文件必须另存为 .xlsm。

25.3 VBA 编辑器与代码示例

按 Alt+F11 打开 VBA 编辑器（VBE），插入模块后即可编写录制宏无法实现的复杂逻辑，如循环、条件判断、批量处理多个文件。

示例一——遍历 A 列并在 B 列标注是否及格：

Sub 标注成绩()

Dim i As Integer

For i = 2 To 100

If Cells(i, 1).Value = "" Then Exit For

If Cells(i, 1).Value >= 60 Then

Cells(i, 2).Value = "及格"

Else

Cells(i, 2).Value = "不及格"

End If

Next i

MsgBox "标注完成！"

End Sub

示例二——自定义函数，可在单元格中像普通函数一样调用 =MyDiscount(A1)：

Function MyDiscount(price As Double) As Double

If price > 1000 Then

MyDiscount = price * 0.8

Else

MyDiscount = price * 0.9

End If

End Function

运行宏：在 VBE 中按 F5，或在 Excel 中按 Alt+F8 选择宏名运行

含宏的文件必须保存为 .xlsm，否则代码会在保存时被静默丢弃

宏的执行结果无法用 Ctrl+Z 撤销，运行前请务必先备份文件

来源不明的宏文件不要启用宏，宏可以执行删除文件等系统级操作

26. 保护、加密与协作已学完

26.1 三种保护级别及其真实强度

26.2 只保护部分区域

工作表保护默认锁定所有单元格。若希望别人只能填写指定区域：

1. 全选工作表→Ctrl+1→“保护”选项卡→取消勾选“锁定”

2. 选中需要保护的单元格（如公式区），重新勾选“锁定”

3. “审阅”→“保护工作表”，设置密码并勾选允许的操作（如允许排序、筛选）

26.3 批注与协作

批注 / 注释：右键单元格→“新建批注”，用于沟通讨论而不影响数据本身

共同编辑：文件保存到 OneDrive 或 SharePoint 后，可多人同时在线编辑，右上角显示协作者头像与实时光标

版本历史：“文件”→“信息”→“版本历史记录”，可查看并恢复云端的历史版本

共享：“文件”→“共享”，生成可查看或可编辑的链接

27. 打印设置已学完

打印区域：选中区域→“页面布局”→“打印区域”→“设置打印区域”

页面设置：“页面布局”中设置纸张方向、大小、页边距，以及缩放（如“将所有列调整为一页”）

打印标题：“页面布局”→“打印标题”→设置“顶端标题行”，让表头在每页顶部重复出现

分页预览：“视图”→“分页预览”，可直接拖动蓝色分页线调整分页位置

页眉页脚：“插入”→“页眉和页脚”，可添加页码、日期、文件名、公司 Logo

打印网格线：默认不打印，需在“页面布局”→“工作表选项”中勾选“网格线-打印”

28. 性能优化：让大表跑得更快已学完

工作簿变慢时，先区分打开、输入、重算和刷新分别耗时多久，再检查下面几类原因。数据量、内存、加载项与外部连接也会影响速度。

28.1 慎用整列引用

整列引用会扩大公式依赖范围，但 SUMIF 等函数有内部优化，不能一概认为每次都会逐行扫描一百多万行。重点检查整列数组运算、SUMPRODUCT 和大量重复查找；以实际重算耗时判断是否需要改写。

改为有限区域：=SUMIF($A$2:$A$5000,"华东",$B$2:$B$5000)

更好的做法是把数据转成超级表，用结构化引用 =SUMIF(表1[地区],"华东",表1[金额])，它只覆盖实际数据行，且能自动扩展

28.2 减少易失性函数

易失性函数（volatile function）的特点是：在触发重算时通常会重新求值，即使本身的引用数据没有变化；手动计算模式下需主动重算。

28.3 其他提速手段

清理无用区域：按 Ctrl+End 若跳到很远的空白处，说明存在残留格式。删除多余的整行整列后保存，文件体积往往能大幅下降

精简条件格式：反复复制粘贴会让规则成倍堆积，定期到“管理规则”中清理合并

避免过多的跨工作簿链接：改用 Power Query 导入

把不再变动的公式结果选择性粘贴为数值

数据量超过十万行时，把计算交给 Power Query 或数据透视表，而不是几十万个单元格公式

临时手动计算：“公式”→“计算选项”→“手动”，编辑完再按 F9 统一重算，可避免每次输入都触发全表计算

29. 疑难排查 FAQ已学完

以下是日常使用中出现频率最高的问题及其原因。遇到异常时，先按这里对照排查。

29.1 公式不计算，单元格里显示的是公式本身

原因一：该单元格的格式被设成了“文本”。解决：改为“常规”，然后双击单元格再按 Enter 重新激活

原因二：误开了“显示公式”视图。解决：按 Ctrl+` 切回

原因三：公式前面多了一个空格或英文单引号。解决：删掉等号前的多余字符

29.2 求和结果是 0，或比实际值小很多

最常见原因：这些“数字”其实是文本。按第 4.3 节的方法转换为数值

其次：区域中包含隐藏行且用了 SUBTOTAL，或求和区域选错了范围

还有一种情况：计算选项被设成了“手动”，按 F9 重算即可

29.3 VLOOKUP 有数据却返回 #N/A

查找值与源数据一边是数值、一边是文本

存在肉眼看不见的空格：用 =TRIM() 清洗，或用 =LEN() 对比两边的字符数

源数据中有不可见字符（常见于系统导出）：用 =CLEAN() 清理

查找区域没有锁定，向下填充时跑偏了

查找值不在查找区域的第一列——VLOOKUP 只能从区域的首列向右找

29.4 筛选或排序只作用于一部分数据

几乎可以肯定是数据中间存在空行或空列，Excel 把连续区域判断到那里就截断了。删除空行，或先按 Ctrl+T 转成超级表，即可彻底解决。

29.5 文件异常臃肿、打开很慢

按 Ctrl+End 检查是否存在大量残留的空白区域，删除多余行列后保存

检查是否堆积了大量条件格式规则

检查是否插入了未压缩的高清图片

另存为 .xlsb 二进制格式，体积通常能明显减小

29.6 日期显示成一串数字，或输入后变成了别的内容

显示成数字：格式被改成了“常规”，按 Ctrl+Shift+3 改回日期

输入 1-2 变成 1月2日：这是 Excel 的日期自动识别。若要保留原文，先把单元格设为“文本”格式再输入

不同电脑上日期格式不一致：这取决于操作系统的区域设置，跨地区协作时建议统一使用 yyyy-mm-dd 格式

29.7 打印时只有一页有表头 / 内容被切成两半

表头重复要用“页面布局”→“打印标题”设置顶端标题行；内容被切断则在“分页预览”中拖动分页线，或用“页面布局”→“缩放”将所有列调整为一页宽。

29.8 复制粘贴后行高列宽全乱了

用“选择性粘贴”→“保留源列宽”，或粘贴后使用右下角出现的“粘贴选项”按钮切换粘贴方式。若只是想要数据，直接选择“数值”即可。

30. 快捷键大全已学完

31. 实用技巧合集已学完

快速求和：选中数字下方或右侧的空格，按 Alt+= 自动生成 SUM 公式

一键生成图表：选中数据按 Alt+F1

跨列居中不合并：Ctrl+1→对齐→水平对齐→“跨列居中”，视觉居中且不破坏排序筛选

批量填充空值：Ctrl+G→定位条件→空值，输入内容后按 Ctrl+Enter，一次性填满所有空格

只复制可见行：选中区域后按 Alt+; 再复制，跳过隐藏与筛选掉的行

快速核对两列差异：选中两列后按 Ctrl+\ 定位出行内容不同的单元格，再填色标记

固化公式结果：复制→选择性粘贴→数值，避免源数据变动导致结果改变

限制重复录入：数据验证→自定义→ =COUNTIF($A:$A,A2)=1

快速美化表格：选中数据按 Ctrl+T 转为超级表，一键获得专业配色与边框

名称框妙用：直接输入 A1:D100 回车即可选中该区域，输入名称可跳转到已定义区域

快速跳到数据末尾：Ctrl+方向键，或双击单元格边框

同时查看同一文件的两处：“视图”→“新建窗口”→“全部重排”

从身份证号提取信息（第 7 位起 8 位为出生日期）：

提取为文本：

=TEXT(MID(A1,7,8),"0000-00-00")

提取为真正的日期（可参与计算、可排序）：

=DATEVALUE(TEXT(MID(A1,7,8),"0000-00-00"))

由出生日期计算周岁年龄：

=DATEDIF(DATEVALUE(TEXT(MID(A1,7,8),"0000-00-00")), TODAY(), "Y")

32. 综合实战：销售数据从清洗到看板已学完

本章把前面的知识点串成一条完整的工作流。假设你收到一份各分公司发来的销售明细，存在典型的脏数据问题，目标是产出一份可交付的分析看板。建议对照自己手头的真实数据边做边练。

32.1 场景与初始问题

原始数据的常见状况：

“客户信息”一列里混着客户名与联系电话

金额列有一部分是文本型数字，求和结果偏小

地区名称前后带空格，导致相同地区被当成不同类别

中间夹着几行空行和手工写的小计行

表头是两行合并的复杂表头

32.2 第一步：整理结构（对应第 5 章）

1. 把两行合并表头改为单行表头，取消所有合并单元格

2. 删除数据区中间的空行与手工小计行——小计后面交给透视表生成

3. 删除表格右侧和下方的残留空白区域，按 Ctrl+End 确认边界正常

4. 把整理好的原始数据单独放在一张名为“数据源”的工作表中，报表另建工作表

32.3 第二步：清洗数据（对应第 10、16 章）

1. 拆分客户信息：选中该列→“数据”→“分列”→按逗号分隔，得到“客户名称”和“联系电话”两列（分列前先在右侧插入两列空白）

2. 清除多余空格：新增辅助列输入 =TRIM(C2) 向下填充，再把结果选择性粘贴为数值覆盖原列

3. 修复文本型数字：选中金额列→“数据”→“分列”→直接点“完成”，随后确认状态栏能正常显示求和

4. 统一日期类型：用 ISNUMBER 检查，并确认序列值对应的日期正确；若为文本，在分列向导第 3 步选择与原文一致的日期顺序，例如 YMD

5. 检查重复：用 =COUNTIF($B:$B,B2)>1 标记重复订单号，人工确认后再决定是否删除

32.4 第三步：补充计算列（对应第 10 章）

先按 Ctrl+T 把数据源转为超级表（命名为“销售表”），再新增计算列，公式会自动向下填充：

金额 =[@单价]*[@数量]

月份 =TEXT([@日期],"yyyy-mm")

业务员 =IFNA(VLOOKUP([@员工编号], 员工表!$A:$B, 2, 0), "未匹配")

等级 =IFS([@金额]>=10000,"A", [@金额]>=5000,"B", TRUE,"C")

32.5 第四步：透视分析（对应第 20 章）

1. 选中超级表→“插入”→“数据透视表”→放到新工作表

2. 行放“地区”，列放“月份”，值放“金额（求和）”，得到地区×月份交叉汇总

3. 把“金额”再拖入值区域一次，右键→“值显示方式”→“列汇总的百分比”，同时看到金额与占比

4. 要按季度分析，将真实“日期”字段拖入行或列区域，再右键日期项→“组合”，选择“年”和“季度”；TEXT 生成的月份文本不能直接按日期分组

5. 再建一个透视表：行放“业务员”，值放“金额”，并按降序排序，得到业绩排行

32.6 第五步：做成看板（对应第 19、21、22 章）

1. 基于地区透视表插入柱形图，基于月份趋势插入折线图

2. 插入切片器（地区、产品类别）与日程表（日期），右键切片器→“报表连接”，让它同时控制所有透视表与图表

3. 把图表与切片器排列到同一张工作表，命名为“看板”

4. 对业绩排行表应用条件格式的“数据条”，让排名一眼可辨

5. 隐藏“数据源”工作表，并对“看板”表执行“保护工作表”，防止他人误改布局

32.7 第六步：交付与维护

下个月拿到新数据时，直接把明细追加到超级表末尾，然后“数据”→“全部刷新”，所有透视表与图表自动更新

若数据来自多个文件，改用 Power Query 的“从文件夹”批量合并，刷新一次即可完成清洗与汇总

交付前检查：计算选项是否为“自动”、是否残留辅助列、公式是否存在错误值

含宏则另存为 .xlsm；数据量很大则另存为 .xlsb 以提升打开速度

走完这一遍，你已经使用了本教程中最核心的能力：规范表格结构、清洗数据、函数计算、透视汇总、可视化呈现与自动化刷新。真正的熟练来自重复——把这套流程在自己的业务数据上再跑两三次，它就会变成肌肉记忆。

33. 学习路径建议已学完

建议按以下节奏推进，每个阶段都配合真实数据动手练习，效果远好于单纯阅读：

1. 【入门 1–2 周】熟悉界面与基本操作，重点吃透第 5 章的表格设计原则，能独立做出一张结构规范的表

2. 【进阶 2–4 周】掌握常用函数（SUMIFS、COUNTIFS、IF、VLOOKUP 或 INDEX+MATCH、TEXT、DATEDIF），能完成日常统计需求

3. 【提高 1–2 个月】熟练使用数据透视表、图表与条件格式，能独立产出一份可视化分析报告

4. 【精通 长期精进】用 Power Query 处理复杂数据源，用 VBA 实现流程自动化，并养成性能优化意识

两个建议：第一，遇到问题先回到第 29 章的 FAQ 对照排查，多数疑难都出在数据结构而非函数本身；第二，找一份自己工作或生活中的真实数据（个人记账、班级成绩单、销售明细），把第 32 章的流程完整走一遍，比背下一百个函数语法更有价值。

34. 函数速查索引（附录）已学完

本章是全书的函数工具书。共收录教程中出现的 86 个函数，提供三种查法：不记得用法时按字母查（34.1）；知道要做什么但想不起函数名时按场景查（34.2）；公式在别人电脑上报 #NAME? 时按版本查（34.3）。表格末列的“§”标注了该函数的详细讲解所在章节。

34.1 按字母速查（A–Z）

标注【2019+】【365/2021+】【365】的函数对版本有要求，详见 34.3。标注“易失”的函数会频繁触发重算，大表中应慎用，详见第 28 章。

A – C

D – I

L – R

S – Y

34.2 按场景速查（想做什么 → 用什么）

知道目标却想不起函数名时，从这里反查。

34.3 版本兼容速查

公式在自己电脑上正常、发给同事却显示 #NAME?，通常就是对方的 Excel 版本不支持该函数。下表列出本教程中有版本要求的函数及其替代方案。

34.4 易失性函数清单

以下函数属于易失性函数：在 Excel 触发重算时通常会重新求值；手动计算模式下不会随每次输入立即更新。在几万行的大表中大量使用会明显拖慢速度，优化思路见第 28 章。

本编用于查阅。需要完整操作过程时，继续阅读第二至第五编的对应案例。

Excel 版本与平台兼容矩阵已学完

说明：Microsoft 365 的功能会随更新通道变化；Excel 2024 指永久授权版。组织环境可能通过管理员策略关闭部分功能。发布前应在目标设备上实测。

统一版本标签已学完

• 【通用】Excel 2019及以上桌面版通常可用。
• 【2021+】Excel 2021、2024和Microsoft 365可用。
• 【365】以 Microsoft 365 为适用环境；是否进入永久版请查函数官方页面，不能仅凭标签判断。
• 【Windows桌面】依赖Windows桌面版；macOS或网页版需要替代方案。

公式、DAX 与 VBA 示例的使用边界已学完

本文包含语法示意、可运行的小例子和需要先建表的综合项目。中文占位参数须替换为实际引用；VBA、Power Pivot 和动态数组须在标注的平台中运行。网页检查不能证明这些示例已在所有 Excel 版本中通过测试。

如何核对公式结果已学完

• 先确认数据区域、字段名和单元格类型，再输入公式。
• 至少用一条正常记录、一条无匹配记录和一条空白记录核对结果。
• 语法示例中的方括号表示可选参数，不是要求原样输入的字符。
• 有空格的工作表名需加单引号，例如 ='销售 明细'!A2；中文名称本身并不必然要求单引号。
• 遇到错误，保留错误类型并定位原因，最后才决定是否用 IFNA 或 IFERROR 显示提示。

发布前原生 Excel 测试清单已学完

• 在 Excel 2019、2021、2024 和 Microsoft 365 中分别打开对应练习文件。
• 对动态数组、XLOOKUP、LET、LAMBDA、TAKE/DROP、VSTACK/HSTACK逐项验证版本行为。
• 在 Power Pivot 中把 DimDate 标记为日期表，再验证 TOTALYTD 和 SAMEPERIODLASTYEAR。
• 将 VBA 示例分别放入标准模块、ThisWorkbook、工作表模块或 UserForm，并核对放置说明。
• 运行所有入口宏，验证错误后 ScreenUpdating、EnableEvents 和 Calculation 能恢复。
• 用手算控制总计核对公式、DAX度量值和宏输出。

Excel教程学习路线与使用说明已学完

根据目标选择章节，不必从第一页连续读到最后一页。每条路线都以章节中的示例数据和结果核对项作为练习依据。

日常办公路线（录入、汇总与打印）已学完

• 第二编：工作簿、数据输入、格式、排序筛选、数据验证、打印。
• 第三编：SUMIFS、COUNTIFS、IF、XLOOKUP；第四编：数据透视表和基础图表。
• 完成“基础操作练习.xlsx”和“公式函数练习.xlsx”。

数据分析路线（清洗、透视与建模）已学完

• 先完成日常办公路线。
• 第三编：动态数组与条件汇总；第四编：透视表与图表。
• 第四编：Power Query、数据模型、DAX。
• 完成数据清洗、透视图表、Power Query和Power Pivot练习。

自动化路线（宏与批处理）已学完

• 先掌握规范表、公式和Power Query。
• 第五编：VBA 对象、变量、循环、错误处理、事件和三个综合项目。
• 将VBA练习另存为xlsm，在副本中运行代码。

讲师路线已学完

• 完成前三条路线。
• 阅读各编中的深度实验、评分量表和项目验收小节。
• 用同一练习分别测试Windows、macOS和网页版的差异。