Excel #REF! 错误：原因与解决方法

#REF! 表示公式引用了一个不存在的单元格。通常的原因是删除了行、列或工作表：删除 C 列后，Excel 会把 =B2*C2 改写成 =B2*#REF!，此后结果就一直是 #REF!。删除后立即按 Ctrl+Z（Mac 上按 Cmd+Z），列和公式都能恢复。

公式重置

D2fx

=B2*#REF!

	A	B	C	D
1	Product	Price	Qty	Total
2	Apple	1.2	10	#REF!
3	Pear	1.5	20	#REF!
4	Plum	0.8	15	#REF!
5	Bread	2.4	5	#REF!

#REF! 公式引用了一个不存在的单元格。

数量列被删除后又重新输入了，但公式仍然显示 #REF!：引用一旦丢失，Excel 永远不会自动修复。点击 D2，把 #REF! 替换成 C2 并按 Enter。整列都会跟着更新，D2 显示 12。

#REF! 是怎么进入公式的

只要公式用到的单元格消失了，Excel 就会在公式中写入 #REF!：

删除区域内部的单元格是安全的：删除 C 列时，=SUM(B2:D2) 会变成 =SUM(B2:C2)。删除区域的第一个或最后一个单元格只会让区域缩小。所以 =SUM(B2:D2) 比 =B2+C2+D2 更安全，后者会变成 =B2+#REF!+C2。

为什么 VLOOKUP 返回 #REF!

VLOOKUP 的第三个参数是在表格区域内数列。如果它大于区域的列数，结果就是 #REF!。

公式重置

F2fx

=VLOOKUP(E2,A2:C6,4,FALSE)

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Pear	#REF!
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

#REF! 公式引用了一个不存在的单元格。

A2:C6 有三列，所以第 4 列不存在。把 4 改成 3，F2 显示 25。这种情况最常发生在从查找表中删除一列之后：区域缩小了，写死的列号却没变。XLOOKUP 或 INDEX 加 MATCH 可以避免这个问题，因为它们直接指定返回列，如 =XLOOKUP(E2,A2:A6,C2:C6)。其余参数见 VLOOKUP。

INDEX 和 OFFSET 的 #REF!

当行号或列号超出区域时，INDEX 返回 #REF!；当 OFFSET 移动到第 1 行之上或 A 列之前时，也返回 #REF!。

公式重置

B2fx

=INDEX(A2:A6,6)

	A	B	C
1	Score	Result	What it asks for
2	88	#REF!	6th value of 5
3	72	95	3rd value of 5
4	95	#REF!	2 rows above A2
5	64	81	4 rows below A2
6	81

#REF! 公式引用了一个不存在的单元格。

A2:A6 中有五个分数，所以 INDEX(A2:A6,6) 是 #REF!，而 INDEX(A2:A6,3) 返回 95。第 0 行不存在，所以 OFFSET(A2,-2,0) 是 #REF!，OFFSET(A2,4,0) 则落在 A6：81。当位置来自另一个公式（MATCH、COUNT）时，先检查那个公式。更多内容见 INDEX 一文。

当 INDIRECT 的文本不是有效地址（=INDIRECT("ZZZ1")，因为最后一列是 XFD），或指向一个已关闭的工作簿时，它也会返回 #REF!。

复制公式时出现 #REF!

相对引用会随公式一起移动。向上或向侧面复制得足够远，引用就会跑出工作表：

C3:  =B2*2        (one row up, one column back)
copy C3 to B2:  =A1*2
copy C3 to A2:  =#REF!*2    (there is no column before A)

公式复制到另一个工作表或工作簿后，如果指向那里不存在的单元格，也会发生同样的事。用 $ 锁定不能移动的单元格（=$B$2*2），或者从编辑栏复制公式文本，而不是复制单元格。绝对引用讲解了 $。

找出并清除工作簿中的每个 #REF!

1. 按 Ctrl+F（Mac 上按 Cmd+F），输入 #REF!，打开 选项，把 查找范围 设为 公式，然后点击 查找全部。列表会显示每个包含无效引用的公式。
2. 要一次修复多个，用 Ctrl+H（Mac 上按 Control+H）：查找 #REF!，替换为正确的引用，但前提是每处都应该改成同一个单元格。
3. 检查 公式 > 名称管理器：如果某个名称的 引用位置 列显示 #REF!，所有使用它的公式都会出错。
4. 如果被删除的数据已经找不回来，公式也不再需要，就选中这些单元格，用值替换公式（复制，然后 开始 > 粘贴 > 值）。错误值仍然是错误，所以之后要把这些单元格删除。

修复返回 #REF! 的查找

公式重置

F2fx

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Plum
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： =VLOOKUP(E2,A2:C6,4,FALSE) 返回了 #REF!。在 F2 中写一个能正常工作的查找公式，返回 E2 中产品的库存。

检查显示答案

只要查找公式在这里返回 60，并且随数据变化，就能通过：列号为 3 的 VLOOKUP、=XLOOKUP(E2,A2:A6,C2:C6) 或 =INDEX(C2:C6,MATCH(E2,A2:A6,0)) 都可以。