这 6 个 Excel 坑,我每年都得替新人填一遍

月底关账，新人把两张表 VLOOKUP 一对，满屏 #N/A；SUM 一拉，金额显示 0。微信弹过来一句"哥，数据是不是丢了"。这种场景，有些同学一年要遇到好几次。其实不是数据丢了，是踩了 Excel 几个老坑。下面这 6 个，我每年都得替新人填一遍——你大概率也遇过，只是当时糊弄过去了。

真实场景：从 ERP 导出的订单号是数值，从客服系统拷过来的订单号带格式成了文本（左上角带绿三角）。拿着文本去数值列里 VLOOKUP，自然一个都匹配不上。还有些系统导出的订单号前面带了不可见空格，" 1001" 和 "1001" 也不是一个东西。

为什么：VLOOKUP 是"类型敏感"的，文本 "1001" 和数值 1001 在它眼里是两样东西，绝不互通。

实跑验证：我把 3 个文本订单号拿去查数值映射，匹配 0 行（全 #N/A）；用 VALUE() 转成数值再查，3 行全中。所以看到 #N/A 别急着怀疑数据，先看类型。

怎么做：

• 临时救急：=VALUE(A2) 包一层，或者 =A2*1 强制转数值；
• 根治：选中那列 → 数据 → 分列 → 一路"下一步"到"列数据格式"选"常规"，一次性转干净；
• 顺手清空格：=TRIM(CLEAN(A2))，专门对付带不可见字符的导出；
• 更稳的写法：换成 =XLOOKUP(查找值, 查找列, 返回列)（Office 365 / 2021 起支持），它不要求查找值在首列，还能直接指定"找不到返回什么"；老版本用 =INDEX(返回列, MATCH(查找值, 查找列, 0))。

还有个隐藏坑：VLOOKUP 第四参数（匹配方式）漏写。很多人写 =VLOOKUP(a, b, c)，不写最后的 FALSE，Excel 默认按"近似匹配"走，会返回"最接近但不大于"的那一行——看起来有结果，其实对错了行。记住：精确匹配永远写 FALSE（或 0），宁可多打两个字，别拿错数。

坑二：数字存成文本，SUM 出来是 0

真实场景：从网页或某个老系统把金额表粘进 Excel，数字左上角带绿三角。一 SUM，结果是 0 或者只加了一部分，看着像数据丢了——其实数据在，只是 Excel 把那些格当成了文字，求和时直接跳过。

为什么：文本不参与算术运算。SUM 遇到文本型数字，默认忽略，不做任何提示。这种"静默出错"最坑人，因为表面看不出毛病。

实跑验证：一列 5 个数，前 3 格是文本、后 2 格是数值，直接求和得 900（只加了后两个）；用分列转成数值后求和，才是真实的 1500。差了 600，报表上对不出来。

怎么做：

• 选中有绿三角的格 → 左边黄色感叹号 → "转换为数字"；
• 批量：数据 → 分列 → 常规，整列转；
• 公式层兜底：用 =SUM(VALUE(A2:A100)) 这类写法（数组公式环境，或新版本直接动态数组），先转再算，避免被文本偷家。

怎么一眼认出文本数字：数值默认右对齐、文本默认左对齐；文本数字左上角还带绿三角。排错时扫一眼对齐方式，比逐个点单元格快得多。

坑三：合并单元格，毁掉排序和透视

真实场景：为了好看，表头把"2026 年各地区"合并成一格，下面分华东/华北。一按排序，数据全乱；想做个透视表，字段直接错位，根本选不中。新人最爱干这事，因为"看起来整齐"。

为什么：合并单元格后，只有左上角那个格有值，其余是空的。排序按"空值"走，透视表按"空字段"走，全废。

怎么做：

• 取消合并：开始 → 合并后居中（点掉）→ 再"居中"只是视觉居中，不影响数据；
• 想要表头好看又不坑：用"跨列居中"（格式 → 对齐 → 水平对齐选"跨列居中"），看着合并了，实际每格都有值；
• 做透视前，先把源数据整理成一维表：一行一条记录、一列一个字段，别搞二维交叉表。这是 Excel 所有分析功能的前提。

坑四：日期是文本，不能减、不能算

真实场景：从某个系统导出"2026/7/16"，看着像日期，但拿它减另一个日期报错，DATEDIF 直接 #VALUE!。因为那一列本质是文本，Excel 不认它是日期。

为什么：文本日期只是一串长得像日期的字符，不参与日期运算。常见于 CSV 导出、或者单元格格式被手动设成了"文本"。

怎么做：

• 转日期：=DATEVALUE(A2)，或者双击单元格再回车让它重新识别；
• 批量：数据 → 分列 → 最后一步"列数据格式"选"日期"；
• 偷懒技巧：在空白列写 =--A2（两个减号强制转数值，日期会变成序列号），再设回日期格式；
• 判断是不是真日期：用 =ISNUMBER(A2)，返回 TRUE 才是真日期，FALSE 就是文本。这招排查最快。

坑五：相对引用下拉跑偏，单价列跟着跑了

真实场景：B 列单价、C 列数量，D 列写 =B2*C2 算金额，往下拉填充。结果第 3 行开始算出来全是错的——因为下拉时 B2 变成了 B3、B4，单价列整体下移，对不上了。

为什么：Excel 默认是相对引用，公式往下拉，引用的行号跟着变。单价这种"固定参照"，不锁就会跑。

怎么做：

• 锁列不锁行：=$B2*C2，下拉时 B 列不动，行号照样走；
• 全锁：=$B$2，行和列都不动（适合查固定参数表）；
• 最快操作：选中公式里的引用，按 F4 循环切换 $ 组合（相对→锁列→锁行→全锁）；
• 跨表引用也常吃这个亏：比如 =Sheet2!A2 往下拉，A2 会变成 A3、A4；该锁的地方写成 =Sheet2!$A2，列钉死、行照走，跨表拉数据才稳。

坑六：手工 SUM 漏行，改了数还不更新

真实场景：每月手工在表底写 =SUM(B2:B50) 合计各区域销量。下个月数据加到 B60，公式还停在 B50，漏了 10 行；或者中间插入一行，SUM 范围没跟着扩， totals 悄悄少了。改了源数据，手工合计也常常忘了一起改。

为什么：手工圈定范围是"写死"的，数据增减、插入删除都不会自动跟着变。

实跑验证：区域=华东、产品=A 的销量，用条件求和 =SUMIFS(销量列, 区域列, "华东", 产品列, "A") 算出来是 60（10+50），不管中间插不插行、数据加到哪，公式都跟着变。

怎么做：

• 多条件求和用 =SUMIFS(求和列, 条件列1, 条件1, 条件列2, 条件2, …)，条件随便加，范围自动覆盖整列引用；
• 单条件用 =SUMIF(条件列, 条件, 求和列)；
• 想自由拖维度分析，直接上透视表：插入 → 透视表，把字段拖进去，数据怎么变都不用改公式；
• 范围尽量写整列（如 B:B），但注意：SUM 整列会把表头那格文本忽略（安全），可一旦表头是数字就会算进去；SUMIFS 整列引用只认满足条件的行，相对更稳。新人直接养成"用 SUMIFS / 透视表代替手工 SUM"的习惯，漏行这事基本就不发生了。

顺手给个排查顺序，下次看到报表不对，照这个走一遍，多数能定位：

1. 先看对齐方式：数字右对齐、文本左对齐，一眼区分文本型数字；
2. 看绿三角：有就是文本，分列转数值；
3. 看引用：公式下拉结果错，按 F4 补 $；
4. 看合并：排序/透视乱，先取消合并、转一维表；
5. 看类型：日期用 =ISNUMBER(A2) 验，返回 FALSE 就是文本。

这 6 个坑，表面看是"操作不熟"，根子都在"Excel 把数据和显示分得很清"——文本就是文本，合并就是空，相对就是会变。记住这层逻辑，比背快捷键管用。下次再看到 #N/A 或 SUM 是 0，先别慌，多半是上面某一个。