一、引用：$ 到底锁什么
A1 相对引用：复制时行列都变
$A$1 绝对引用：行列都不变
A$1 锁定行，列可变
$A1 锁定列，行可变向下填充公式时，查找区域必须锁行（$A$2:$B$100），否则区域会跟着下移，越往下查范围越小——这是 VLOOKUP 报 #N/A 的最常见原因。
按 F4 可以循环切换四种引用方式。
二、VLOOKUP 的三个限制
=VLOOKUP(查找值, 查找区域, 返回第几列, FALSE)- 只能向右查：返回值必须在查找列的右边。要往左查，用
INDEX + MATCH或XLOOKUP。 - 列号是硬编码：写
3表示第 3 列，中间插入一列就全错。用MATCH动态算列号更稳。 - 默认近似匹配：第四个参数省略时是
TRUE（近似匹配），要求数据升序，否则得到莫名其妙的结果。永远显式写FALSE或0。
=INDEX(B:B, MATCH(D2, A:A, 0))INDEX + MATCH 可以任意方向查找，插列也不受影响。
三、XLOOKUP（新版）
=XLOOKUP(查找值, 查找列, 返回列, "未找到")优势：
- 不需要数列号。
- 可以向左查。
- 内置「未找到」的默认值，不用套
IFERROR。 - 支持反向查找（从后往前）。
如果团队都用 Microsoft 365 或较新版本 WPS，优先用它。
四、条件统计
=SUMIFS(求和列, 条件列1, 条件1, 条件列2, 条件2)
=COUNTIFS(条件列1, 条件1, 条件列2, 条件2)
=AVERAGEIFS(平均列, 条件列, 条件)注意与 SUMIF 的参数顺序不同：SUMIF(条件列, 条件, 求和列)，而 SUMIFS(求和列, 条件列, 条件, ...)。顺序反了不会报错，只会算错，这是最容易踩的坑。
条件可以是：
- 值：
"北京" - 比较：
">1000"（要加引号） - 通配符：
"张*" - 引用：
">"&D2
五、IF 与 IFS
=IF(A2>=90,"优",IF(A2>=80,"良",IF(A2>=60,"及格","不及格")))
=IFS(A2>=90,"优", A2>=80,"良", A2>=60,"及格", TRUE,"不及格")IFS 从上到下判断，第一个为真的返回。最后的 TRUE 相当于 else。
超过三层嵌套就难维护了，考虑用 LOOKUP 做区间映射：
=LOOKUP(A2, {0,60,80,90}, {"不及格","及格","良","优"})六、文本与日期
=TEXT(A2,"yyyy-mm-dd") 日期格式化
=TEXT(A2,"0.00%") 百分比
=LEFT/RIGHT/MID(A2, n) 截取
=TRIM(A2) 去多余空格（不含全角空格）
=SUBSTITUTE(A2,"-","") 替换
=A2&"-"&B2 拼接注意：TEXT 的结果是文本，不能直接参与数值计算。日期在 Excel 里本质是数字，TEXT 之后就变成字符串了。
七、错误值怎么排查
| 错误 | 原因 |
|---|---|
#N/A | 查找不到（值不存在、空格差异、类型不一致、区域没锁） |
#REF! | 引用的单元格被删除 |
#VALUE! | 参数类型错（文本参与运算） |
#DIV/0! | 除数为 0 |
#NAME? | 函数名拼错或缺引号 |
##### | 列宽不够，拉宽即可 |
#N/A 的处理：
=IFERROR(VLOOKUP(...), "未找到")但 IFERROR 会掩盖真实错误（比如区域写错也显示「未找到」）。调试阶段先不要包 IFERROR，看清错误类型再加。
八、几个实用习惯
- 用表格（Ctrl+T）而不是普通区域，公式会自动扩展。
- 关键列用数据验证限制输入，减少脏数据。
- 计算列与原始数据分开放，别在数据表里插计算列。
- 长公式用
Alt+Enter换行，按逻辑分段。 - 涉及金额的计算注意浮点误差，比较时用
ROUND处理到分。
小结
$ 锁住查找区域是 VLOOKUP 不报错的前提；第四个参数显式写 0；SUMIFS 与 SUMIF 参数顺序不同；嵌套太深用 LOOKUP 或 IFS；#N/A 先查空格与类型，别急着 IFERROR。