运算符与优先级
公式里同时出现多个运算时,谁先算由优先级决定。记不住顺序时最稳的做法是加括号——括号永远优先,且不会带来任何性能代价。
| 优先级 | 运算符 | 含义 | 示例 |
|---|---|---|---|
| 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/FALSE | A1>=60 |
=A1&B1+1 不会得到「A1B1 加 1」,因为 + 的优先级高于 &,实际算的是 A1&(B1+1)。要拼接再加,必须写成 (A1&B1)+1。三种引用,决定了复制时会不会跑偏
这是新手最容易卡住的一环。公式往下拖、往右拖时,引用会按规则跟着移动,加不加 $ 决定哪一部分被锁住。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | A1 | 相对引用 | 行与列都会变 | ↓ 复制到 B3 变成 B3 |
| 2 | $A$1 | 绝对引用 | 行列都不变 | ↓ 复制到 B3 仍是 $A$1 |
| 3 | $A1 | 混合:锁列 | 列不动,行会变 | ↓ 复制到 B3 变成 $A3 |
| 4 | A$1 | 混合:锁行 | 行不动,列会变 | ↓ 复制到 B3 变成 B$1 |
实操里有一个快捷判断法:想锁谁就在谁前面加 $。要固定一行数据(比如税率放在 B1),往下拉时要锁行,写成 B$1;要做乘法表这种行列都参与的情况,混合引用正好能一次公式填满整张表。
按 F4 可以在四种状态间循环切换,不用手动敲美元符号。选中公式里的引用后连按 F4,依次变成 $A$1 → A$1 → $A1 → A1。
八种错误值,对照着查
Excel 的错误值不是随机出现的,每种都对应一类明确的原因。看到错误先在这里对号,比漫无目的地改公式快得多。
除数为 0 或空单元格。
用 IFERROR 包一层,或先判断分母是否为空运算里混进了文本。常见于「看起来是数字的文本」参与加减。
用 ISNUMBER 定位,或用 VALUE 转换查找函数找不到匹配项。这是「结果不存在」,不是公式写错。
检查查找值有无多余空格,或用 IFNA 兜底引用失效。多为删除了被公式引用的行或列。
撤销删除,或用 INDIRECT 重新指向函数名拼错,或文本没加引号导致被当成名称。
检查拼写与引号是英文半角数值超出可表示范围,如对负数开平方。
检查参数是否为非法值动态数组的溢出区域被其他内容挡住。
清空溢出目标区域,或改用 @ 取单值公式产生了循环引用或计算无法继续。较新版本才有的提示。
检查公式是否直接或间接引用了自身排查顺序:从外往内剥
公式嵌套三层以上时,盯着看是看不出来的。用「公式求值」把它一步步算给你看,是最有效的手段,比到处插 IFERROR 掩盖问题强得多。
- 定位报错的层级把公式里最外层的函数单独拿出来测,确认它拿到的输入是不是预期值。
- 用「公式 → 公式求值」逐步走每点一次「求值」,下划线标出即将计算的部分。走到哪一步出现错误值,问题就在那一段。
- 用 F9 临时求值在编辑栏选中一段子表达式按 F9,会立即显示它的计算结果。看完要按 Esc 退出,否则结果会被写死进公式。
- 检查数据类型用
ISNUMBER或ISTEXT确认参与运算的确实是数字。文本型数字是最高频的隐性错误源。 - 检查引用范围用
ROWS或COLUMNS确认区域行数对不对,多选或少选一行很常见。 - 最后才考虑加容错确认逻辑无误后再套
IFERROR。一开始就套,等于把错误藏起来。
文本型数字:最隐蔽的一类错
从系统导出的编号、从网页复制的数字,经常带着「文本」属性。它们在格子里右对齐还是左对齐不明显,求和时却被跳过,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 位再判断,能避免掉一批莫名其妙的「不等」。
几个写公式的习惯,能省很多事
这些不是语法要求,是长期维护的经验:
先写结构再填细节。比如要做多条件汇总,先把 =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 里做。硬堆一条巨型公式,日后没人维护得动。