=Excel 使用指南
首页 / 函数 / 公式
FORMULA / DEBUG

Excel 公式:从 =A1+B1 到嵌套与报错排查

公式的本质是一句话指令:告诉 Excel 拿哪些格子、做什么运算、结果放在哪。写不对只有三种原因——引用错了、运算顺序错了、数据类型错了。这一页把三类原因逐条拆开,并给一套可复用的排查顺序。

=IF(SUMIFS($E$2:$E$99,$B$2:$B$99,"华东")>10000,"达标","未达标")
函数名IF除号前的等号表示「这是一条公式」
判断条件SUMIFS(…)结果是一个数,才能拿来比大小
绝对引用$E$2:$E$99下拉复制时区域不会跟着跑偏
返回值"达标"文本必须加英文双引号,中文引号无效

运算符与优先级

公式里同时出现多个运算时,谁先算由优先级决定。记不住顺序时最稳的做法是加括号——括号永远优先,且不会带来任何性能代价。

优先级运算符含义示例
1:区域引用,冒号A1:B10
2空格交集引用B:B 1:1 → 取两者交点
3,联合引用,逗号SUM(A1:A5,C1:C5)
4−负号−A1
5%百分比50%
6^乘幂2^10 = 1024
7* 与 /乘、除同级,从左到右
8+ 与 −加、减同级,从左到右
9&文本连接"共"&A1&"件"
10= < > <= >= <>比较,结果为 TRUE/FALSEA1>=60
一个真实陷阱:=A1&B1+1 不会得到「A1B1 加 1」,因为 + 的优先级高于 &,实际算的是 A1&(B1+1)。要拼接再加,必须写成 (A1&B1)+1。

三种引用,决定了复制时会不会跑偏

这是新手最容易卡住的一环。公式往下拖、往右拖时,引用会按规则跟着移动,加不加 $ 决定哪一部分被锁住。

ABCD
1 A1相对引用行与列都会变↓ 复制到 B3 变成 B3
2 $A$1绝对引用行列都不变↓ 复制到 B3 仍是 $A$1
3 $A1混合:锁列列不动,行会变↓ 复制到 B3 变成 $A3
4 A$1混合:锁行行不动,列会变↓ 复制到 B3 变成 B$1
FIG.1 — 四种引用类型及其在复制时的变化规律

实操里有一个快捷判断法:想锁谁就在谁前面加 $。要固定一行数据(比如税率放在 B1),往下拉时要锁行,写成 B$1;要做乘法表这种行列都参与的情况,混合引用正好能一次公式填满整张表。

按 F4 可以在四种状态间循环切换,不用手动敲美元符号。选中公式里的引用后连按 F4,依次变成 $A$1 → A$1 → $A1 → A1。

八种错误值,对照着查

Excel 的错误值不是随机出现的,每种都对应一类明确的原因。看到错误先在这里对号,比漫无目的地改公式快得多。

#DIV/0!

除数为 0 或空单元格。

用 IFERROR 包一层,或先判断分母是否为空
#VALUE!

运算里混进了文本。常见于「看起来是数字的文本」参与加减。

用 ISNUMBER 定位,或用 VALUE 转换
#N/A

查找函数找不到匹配项。这是「结果不存在」,不是公式写错。

检查查找值有无多余空格,或用 IFNA 兜底
#REF!

引用失效。多为删除了被公式引用的行或列。

撤销删除,或用 INDIRECT 重新指向
#NAME?

函数名拼错,或文本没加引号导致被当成名称。

检查拼写与引号是英文半角
#NUM!

数值超出可表示范围,如对负数开平方。

检查参数是否为非法值
#SPILL!

动态数组的溢出区域被其他内容挡住。

清空溢出目标区域,或改用 @ 取单值
#CALC!

公式产生了循环引用或计算无法继续。较新版本才有的提示。

检查公式是否直接或间接引用了自身

排查顺序:从外往内剥

公式嵌套三层以上时,盯着看是看不出来的。用「公式求值」把它一步步算给你看,是最有效的手段,比到处插 IFERROR 掩盖问题强得多。

  1. 定位报错的层级把公式里最外层的函数单独拿出来测,确认它拿到的输入是不是预期值。
  2. 用「公式 → 公式求值」逐步走每点一次「求值」,下划线标出即将计算的部分。走到哪一步出现错误值,问题就在那一段。
  3. 用 F9 临时求值在编辑栏选中一段子表达式按 F9,会立即显示它的计算结果。看完要按 Esc 退出,否则结果会被写死进公式。
  4. 检查数据类型用 ISNUMBER 或 ISTEXT 确认参与运算的确实是数字。文本型数字是最高频的隐性错误源。
  5. 检查引用范围用 ROWS 或 COLUMNS 确认区域行数对不对,多选或少选一行很常见。
  6. 最后才考虑加容错确认逻辑无误后再套 IFERROR。一开始就套,等于把错误藏起来。
三个定位用的小公式 =ISNUMBER(A2) // FALSE 说明是文本,不能直接参与运算 =ROWS(B2:B99) // 数一下区域到底有多少行 =LEN(A2)&"|"&A2 // 看清首尾有没有隐形空格

文本型数字:最隐蔽的一类错

从系统导出的编号、从网页复制的数字,经常带着「文本」属性。它们在格子里右对齐还是左对齐不明显,求和时却被跳过,VLOOKUP 也匹配不上。三种处理方式:

方法做法适用
分列选中列 → 数据 → 分列 → 直接完成整列一次性转换,最快
乘 1辅助列写 =A2*1 或 =--A2要保留原列、结果在新列时
VALUE=VALUE(A2)公式内部转换,意图最明确

注意「分列」这一步对纯数字串有效,如果单元格里是 138 0013 8000 这种带空格的,要先 SUBSTITUTE 去掉空格再转。

循环引用与浮点误差

两个老问题,碰到时容易怀疑人生。

循环引用指公式间接或直接引用了自己。比如在 B1 写 =B1+1,Excel 会提示循环引用并在状态栏标出位置。多数情况是误操作,改正即可。少数情况是刻意做迭代计算(如累加),这时需要在「文件 → 选项 → 公式」里勾选「启用迭代计算」并设最大迭代次数,默认 100 次、最大 32767 次。

浮点误差来自二进制表示:=0.1+0.2=0.3 会返回 FALSE。这不是 Excel 的 bug,任何用 IEEE 754 双精度的系统都一样。处理办法是用 ROUND 把结果修约到需要的位数再比较:=ROUND(0.1+0.2,10)=0.3 返回 TRUE。做金额比对时,统一先修约到 2 位再判断,能避免掉一批莫名其妙的「不等」。

另一个坑:Excel 只保留 15 位有效数字。超过 15 位的长数字(如银行卡号、身份证号)后几位会被截成 0。这类数据必须先设为文本格式再输入,否则录进去就已经失真了。

几个写公式的习惯,能省很多事

这些不是语法要求,是长期维护的经验:

先写结构再填细节。比如要做多条件汇总,先把 =SUMIFS(,,) 的骨架敲出来,再逐个参数补,比一次写完更少出错。

长公式用换行分段。在编辑栏按 Alt+Enter 可以给公式换行,只影响显示,不影响计算。嵌套三层以上时,分行写可读性差别巨大。

把常量提出来。税率、阈值这类固定值放在单独单元格并命名,公式里引用名称而不是写死数字。改政策时只改一处。

给关键中间结果留一列。与其写一个巨长的嵌套公式,不如拆成几列各自计算,最后一列汇总。调试成本低得多,也便于别人接手。

常见问题

公式和函数到底有什么区别?

公式是你输入的整条表达式,以 = 开头;函数是公式里调用的具名运算块,比如 SUM、IF。一条公式可以不含任何函数(=A1+B1),也可以含十几个函数。函数清单见 函数公式大全。

为什么改了源数据,公式结果没变?

三个可能:一是计算模式被设成了「手动」,在「公式 → 计算选项」里改回自动;二是公式引用的是被筛选隐藏的行,看起来没变其实变了;三是单元格格式设成了文本,公式被当成文字显示。最后一种情况,把格式改回常规后双击进单元格再回车即可重算。

VLOOKUP 明明有匹配却返回 #N/A

按概率排:查找值带多余空格(用 TRIM 处理)、查找值是文本而源数据是数字(或反过来)、第 4 参数漏写 FALSE 走了近似匹配、查找值在被查区域的左侧。第四条是 VLOOKUP 的固有限制,改用 XLOOKUP 可以直接向左查。

数组公式还要按 Ctrl+Shift+Enter 吗

现代版本(Microsoft 365、Excel 2021 及以后)支持动态数组,直接回车即可,结果会自动溢出。只有旧版本或需要兼容旧文件时才用三键组合。判断方法:如果公式两侧自动出现了花括号 {} 且不能手动删除,说明是旧式数组公式。

公式太长超过限制怎么办

单条公式上限 8,192 个字符,嵌套上限 64 层。真到这个量级,说明思路该换了:用辅助列拆分、改用 LAMBDA 定义自定义函数,或者把逻辑搬到 Power Query 里做。硬堆一条巨型公式,日后没人维护得动。