电子表格不是小型数据库。硬把它当数据库用，往往就是灾难的起点。把它当成看得见的计算工具：原始数据保持朴素，转换过程写清楚，汇总结果放在最上层。下面这套方法，我会放心用于预算、运营和分析。
🎙️ 发布并录制于： · 更新于 ·
相对引用会移动，绝对引用不会。这句话很好记。真正容易出错的，是没看出哪个输入是每一行共用的规则。税率放在 B1，就锁定成 $B$1。价格在 C 列逐行变化，就让 C2 保持相对引用。编辑引用时按 F4，可以轮换美元符号的位置。
B2 是数量 · C2 是单价 · F1 是税率
=B2*C2*(1+$F$1) ✓ 向下复制：行号会变，税率不变
=B2*C2*(1+F1) ✗ 向下复制：F1 会变成 F2、F3……
混合引用适合网格计算
=$A2*B$1 锁定 A 列；锁定第 1 行F1 变成了空白的 F2。修复方法：点开第一个错误单元格，查看公式栏，把规则单元格改成 $F$1，再向下填充。最后用税前的数量乘单价，核对总计。只要可用，就用 XLOOKUP，不用 VLOOKUP。键列和返回列可以直接指定。它能向左查找，默认还是精确匹配。更重要的是，先说清楚“找不到键”代表什么，不要拿空白把问题藏起来。
=XLOOKUP(A2, Products[SKU], Products[Price], "MISSING SKU")
#N/A 没有提供 [if_not_found] 参数时出现
修复：1. 把失败的 SKU 复制到 Products 筛选框
2. 比较每个字符和数据类型
3. 添加缺失商品，或修正输入
4. 使用 "MISSING SKU"，不要用 ""，让坏数据继续可见00127 被读成数字 127。商品表里的 SKU 是文本，所以公式返回了真实错误 #N/A。把两边的键列都转成文本，并恢复开头的零，问题就解决了。如果一开始就套上 IFERROR(...,0)，只会得到一条看似合理、实际错误的零元记录。别再把数据复制到名叫“Final v7”的第二个工作表。实时视图应该由公式生成，不该靠每月重复操作。FILTER 负责选行，SORT 负责排序。源数据要使用规范表格，这样新增行会自动纳入。
=SORT(FILTER(Orders, Orders[Status]="Late", "No late orders"), 5, -1)
返回逾期订单，第 5 列较新或较大的记录排在前面
#VALUE!
FILTER 使用的范围高度不同：
=FILTER(A2:F500, G2:G499="Late")
修复：让两个范围都在第 500 行结束，或者直接使用表格列。#VALUE!；另一套手工补救办法则把每位客户的状态错移了一行。先把范围尺寸改对，再用一个已知订单 ID 测试，最后将结果数与 COUNTIF(Orders[Status],"Late") 核对。数据透视表回答的是：“按什么分组，一共有多少？”把类别放进行，把指标放进值。需要时，再把字段放进列或筛选器。我的规矩是：先做透视表，再做图表。汇总本身如果荒唐，漂亮图表只会让荒唐更有说服力。
问题：按地区和月份统计收入
Rows: Region
Columns: Order date → group by Month
Values: Revenue → Sum not Count
Filter: Status ≠ Cancelled
检查：数据透视表总计 = 同一筛选条件下源数据 Revenue 的 SUM$1,200 USD 存成了文本。透视表悄悄选择 Count，结果显示 184，而不是 $213,400。先删掉货币文字，把整列转为数字并刷新透视表。再把“Summarize values by”改成 Sum，最后用源数据核对总计。电子表格中的真日期，是显示成日历日期的序列号。看起来像日期的文本，仍然只是文本。排序错乱、月度透视表空白、日期算术失效，往往都源于这个区别。每个单元格只放一个日期。导入时用 ISO 2026-07-24 这类不会歧义的格式，之后再设置显示格式。
=A2+30 30 个自然日之后
=EDATE(A2,1) 下个月的同一天
=EOMONTH(A2,0) 本月最后一天
=NETWORKDAYS(A2,B2) 工作日数，包含起止日期
#VALUE! from ="July 24, 2026"-A2
修复：确认区域设置后，用 DATEVALUE 转换导入的文本。03/04/2026 看成 3 月 4 日，英国办公室却理解为 4 月 3 日。没有任何公式报错，交付指标却错了整整一个月。保留原始导入数据，明确解析日、月、年，并显示成 24 Jul 2026。如果日期前两个数字都不大于 12，要重点抽查。多数“查找错误”其实是脏文本造成的。TRIM 能去掉普通的多余空格。CLEAN 能清除许多不可打印字符。SUBSTITUTE 适合处理已经确认的异常字符。原始列要保留，另建一个清理后的键列，这样每一步转换都能复查。
=UPPER(TRIM(CLEAN(A2)))
从网页复制的不间断空格不会被 TRIM 清除：
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
#N/A 虽然 "ACME-42" 看起来完全一样
诊断：=LEN(A2) 和 =UNICODE(RIGHT(A2,1))
修复：在辅助列中删掉查明的字符。Northwind 的每次查找都失败，因为导入值末尾藏着一个不间断空格。点开单元格也看不出异常，但 LEN 返回 10，而不是 9。替换字符 160，清理空格并确认长度，再让查找公式使用清理后的键。不要把错误涂成白色，也不要上来就套 IFERROR。先读错误消息。#N/A 表示没有匹配项。#VALUE! 表示输入类型不对。#REF! 表示公式指向的位置已经不存在。三种问题，要用三种方法修。
#N/A → 检查键、数据类型、空格，以及源数据中是否存在
#VALUE! → 检查本应为数字或日期的位置是否混入文本
#REF! → 行、列或工作表被删除或移动，或者依赖项已关闭
删除 D 列后出现的真实损坏公式：
=SUM(B2:C2)+#REF!
修复：能撤销就先撤销；否则从旧版本确认原来的数据源，
恢复引用，再用结果已知的行测试。#REF!。为了赶期限，他把错误改成零，结果低估了成本。正确做法是立刻停止编辑，恢复上一版文件，比较公式，找回汇率列或命名区域，再用两种货币各加一项控制总计。现代公式可以用一个公式返回多个单元格，这就是溢出数组。左上角的单元格掌管整个结果，周围必须留空。不要在结果中间手工输入。要引用整个结果，可以用 J2# 这样的溢出运算符。
=UNIQUE(Orders[Region]) 一个公式，返回多行
=SORT(UNIQUE(Orders[Region]))
#SPILL! "There's already data in E7."
修复：选择警告 → Select Obstructing Cells → 移动或清空这些单元格；
取消单元格合并；把公式放到表格之外。
不要直接清空：先检查究竟是什么挡住了结果。#SPILL!，原因是 E37 中留着手工备注产生的一个英文单引号。从公式所在位置看，按 Delete 像是毫无作用。使用“Select Obstructing Cells”定位 E37，检查后清空内容，再保护溢出结果区域，禁止手工输入。我明确反对十二层嵌套 IF 公式。它本质上是没有命名、没有测试的代码，只靠括号掩饰复杂度。业务规则如果是映射关系，就放进表格，用 XLOOKUP。规则如果是一组区间，就按升序保存阈值，再做近似匹配。规则应该放在别人能看懂的地方。
=IF(A2<10,"Tiny",IF(A2<25,"Small",IF(A2<50,"Medium",
IF(A2<100,"Large",IF(... twelve levels ...)))))
阈值表：
Minimum | Band
0 | Tiny
10 | Small
25 | Medium
50 | Large
=XLOOKUP(A2, Bands[Minimum], Bands[Band],, -1)
现在，修改规则只需编辑一行，不必给公式做手术。可靠的工作簿，接手时应该平淡无奇。原始输入没有被改动。规则放在命名单元格或表格中。错误在解决前始终可见。汇总结果能与源数据对上。发送之前，按这张清单检查一遍。
□ 一张矩形原始数据表；每列只放一个字段
□ 使用稳定 ID 作为查找键，不用名称
□ 共用规则单元格用 $ 锁定，或设为命名区域
□ 日期是真日期；金额是真数字
□ XLOOKUP 找不到时显示 "MISSING"，绝不悄悄返回零
□ 数据透视表已经刷新，总计已核对
□ #N/A、#VALUE!、#REF!、#SPILL! 均已调查
□ 边界情况已经测试；12 层 IF 已改为表格
□ 干净副本能在另一台电脑上正常打开