一份能当手册用的 Excel 实战指南

先说一个反直觉的结论：

大部分人 Excel 用不好，不是因为函数记得少，而是因为跳过了最底下那一层。

我把 Excel 的能力分成四层。你可以对照看看，自己卡在第几层。

第一层 · 数据规范

合并单元格为什么是坑

文本型数字绿三角

突出显示重复值

冻结窗格固定表头

这一层不产生任何"看得见的成果"，但它决定了上面三层能不能成立。

三条铁律：

其一，一维表原则。一行一条记录，一列一个字段，首行且仅首行是字段名，字段名唯一。没有合并单元格，没有空行空列，数据源里不放合计行。

其二，一个格子只放一个属性。「北京-朝阳-100㎡」这种写法，等于主动放弃了筛选、排序、透视三种能力。

其三，数字就是数字。左上角绿色小三角代表文本型数字，求和不参与。修法是选中列 → 数据 → 分列 → 完成。

一份合格的数据源长什么样？简单说：它应该像数据库的一张表，而不是像一张给人看的排版作品。

第二层 · 函数公式

F4 锁定引用，填充不跑偏

VLOOKUP vs XLOOKUP 插入列对比

XLOOKUP 查找匹配

Ctrl+` 一键看穿公式

Alt+= 自动求和

这一层只有一个真正的门槛：引用。

公式向下或向右填充时，引用会不会跟着跑，全由一个美元符号决定。规则一句话——美元符号在谁前面，谁就不动。

A1：行列都变

$A$1：行列都不变

$A1：列不变，行变

A$1：行不变，列变

编辑公式时把光标放在引用上，反复按 F4 就能在这四种之间循环，不用手打美元符号。

引用搞明白了，函数就只是查字典的事。真正高频的只有八个：SUMIFS、COUNTIFS、IF、IFS、IFERROR、XLOOKUP、TEXTJOIN、SUBTOTAL。

关于查找函数，如果你还在用 VLOOKUP，建议换成 XLOOKUP。它解决了三个老问题：只能从左往右查、插入列后列序号错位、默认近似匹配需要手动写 FALSE。并且它可以直接指定"找不到时显示什么"。

需要提醒的是，XLOOKUP、FILTER、UNIQUE、SORT、LET 这些动态数组函数属于 Microsoft 365 与 Excel 2021 之后的特性，2019 及更早版本没有，请用 INDEX+MATCH 组合替代。

第三层 · 数据分析

透视表拖字段出报表

图表选型：对比/趋势/占比

SUBTOTAL 筛选后正确求和

切片器联动看板

这一层的核心工具只有一个：数据透视表。

它不需要写任何公式，拖几下就能出多维报表。标准流程是：整理数据源 → Ctrl+T 转成表格 → 插入透视表 → 拖字段 → 刷新。

几个值得记住的技巧：

值显示方式里可以直接算占比，不用写公式；

行标签里的日期可以右键组合，一键按月、按季、按年分组；

切片器可以让多个透视表联动，几下点击就是一个交互看板；

源数据变了按 Alt+F5，或者 Ctrl+Alt+F5 刷新全部。

图表部分，第一原则是选对类型，而不是选漂亮。随时间变化用折线，类别比较用柱形，占比用饼图但类别不超过五个，两个变量的关系用散点。

还有一个容易被忽略的点：筛选之后求和，要用 SUBTOTAL(109, 区域)，而不是 SUM。SUM 会把隐藏行也计入，结果永远对不上。

第四层 · 效率与自动化

Ctrl+T 把区域变成表格

Ctrl+Enter 批量填充空白

数据验证下拉列表

条件格式数据条

逆透视 二维转一维

Ctrl+Shift+↓ 一秒选中整列

#N/A 排查三步

六种错误值速查

判断标准很简单：一个动作如果要重复做十次以上，就值得自动化。

轻量方案是快捷键和 Ctrl+T 表格。

中等方案是 Power Query。如果你每个月都要重复同一套清洗动作——导入、拆列、改类型、合并、去空行——那就该用它了。它把每一步操作记录下来，下个月点一次刷新全部重做。其中「逆透视列」是把二维交叉表转成一维表的关键一步，也是做透视表的前提。

重量方案是宏与 VBA。不用写代码也能用：视图 → 宏 → 录制宏，正常做一遍操作，停止录制，下次用快捷键运行。注意保存时要选 .xlsm 格式，否则宏会丢失。

最后

回到开头那句话：Excel 的能力是分层的，跳过底层往上爬，越往上越吃力。

如果你的表现在还是合并单元格加手工小计，那第一件事不是去学新函数，而是把表重做一遍。

顺序对了，剩下的都是体力活。

如果这篇对你有用，欢迎点个在看，或者转发给那个总在问你 Excel 问题的同事。