如何更正 #VALUE! 错误

如何更正 #VALUE! 错误

在 Excel 中,#VALUE! 表示“键入公式的方式错误。或者引用的单元格错误。”这种错误非常普遍,并且很难找到具体原因。本页中的信息展示了导致这种错误的常见问题及其解决方案。可能需要尝试一种或多种解决方案才能解决你的特定错误。

修复某特定函数的错误

减法问题

如果未使用过 Excel,则键入的减法公式可能错误。键入减法公式的方法有以下两种:

用一个单元格引用减去另一个单元格引用

单元格 D2 为 $2,000.00,单元格 E2 为 $1,500.00,单元格 F2 为 =D2-E2,结果得出 $500.00

在两个单独的单元格中键入两个值。在第三个单元格,用一个单元格引用减去另一个单元格引用。在本示例中,单元格 D2 为预算金额,单元格 E2 为实际金额。F2 的公式为 =D2-E2

也可以使用 SUM 函数,但数字应采用正负形式

单元格 D6 为 $2,000.00,单元格 E6 为 $1,500.00,单元格 F6 为公式 =SUM(D6,E6),结果得出 $500.00

在一个单元格中键入正值,在另一个单元格中键入负值。在第三个单元格中,使用 SUM 函数将两个单元格相加。在本示例中,单元格 D6 为预算金额,单元格 E6 为实际金额并用负数表示。F6 的公式为 =SUM(D6,E6)

如果使用 Windows,即使运行最基本的减法公式,也可能遇到 #VALUE! 错误。以下解决方案可解决你的问题:

  1. 首先进行快速测试。在一张新的工作簿中,在单元格 A1 中键入 2。在单元格 B1 中键入 4。然后在 C1 中键入如下公式:=B1-A1如果收到 #VALUE! 错误,请转到下一步。如果未收到错误,请尝试本页上的其他解决方案。

  2. 在 Windows 中,打开“区域”控制面板。

    • Windows 10:单击“开始”,键入“区域”,然后单击“区域”控制面板。

    • Windows 8:在“开始”界面,键入“区域”,单击“设置”,然后单击“区域”。

    • Windows 7:单击“开始”,然后键入“区域”,然后单击“区域和语言”。

  3. 在“格式”选项卡上,单击“其他设置”。

  4. 找到“列表分隔符”。如果列表分隔符设置为减号,请将其更改为其他符号。例如,更改为常见的逗号列表分隔符。分号也很常见。但是,其他列表分隔符可能更适合你的特定区域。

  5. 单击“确定”

  6. 打开工作簿。如果单元格存在 #VALUE! 错误,请双击进行编辑。

  7. 如果应为减法减号的位置为逗号,请将其更改为减号。

  8. 按 Enter。

  9. 对存在错误的其他单元格重复此过程。

用一个单元格引用减去另一个单元格引用

单元格 D10 为 1/1/2016,单元格 E10 为 4/24/2016,单元格 F10 为 公式 =E10-D10,结果得出 114

在两个单独的单元格中键入两个日期。在第三个单元格,用一个单元格引用减去另一个单元格引用。在本示例中,单元格 D10 为开始日期,单元格 E10 为结束日期。F10 的公式为 =E10-D10

也可以使用 DATEDIF 函数

单元格 D15 为 1/1/2016,单元格 E15 为 4/24/2016,单元格 F15 为 公式 =DATEDIF(D15,E15,"d"),结果得出 114

在两个单独的单元格中键入两个日期。在第三个单元格中,使用 DATEDIF 函数查找日期之差。若要进一步了解 DATEDIF 函数,请参阅计算两个日期之差

调宽日期列。如果日期右对齐,则表示是一个日期。但是,如果左对齐,则表示不是一个真正的日期。而是文本。Excel 不会将文本识别为日期。可帮助解决此问题的一些解决方案如下。

检查前导空格

  1. 双击要在减法公式中使用的日期。

  2. 将光标放在开头,查看是否可以选择一个或多个空格。在单元格开头选中空格的情况如图所示: 单元格中为 1/1/2016,前面带有选定空格

    如果单元格存在此问题,请继续执行下一步。如果看不到一个或多个空格,请转到下一节,检查计算机的日期设置。

  3. 单击列标题选择包含日期的列。

  4. 单击“数据”>“分列”。

  5. 单击“下一步”两次。

  6. 在向导的第 3 步(共 3 步),在“列数据格式”下,单击“日期”。

  7. 选择日期格式,然后单击“完成”。

  8. 对其他列重复此过程,确保列中的日期不包含前导空格。

检查计算机的日期设置

Excel 使用计算机的日期系统。如果单元格的日期不是使用同一个日期系统输入的,那么 Excel 不会将其识别为真正的日期。

例如,假设计算机将日期显示为 mm/dd/yyyy。如果在单元格中键入类似的日期,Excel 就会将其识别为日期,因此将能够在减法公式中使用。但是,如果键入类似 dd/mm/yy 的日期,Excel 就不会将其识别为日期。而是将其视为文本。

此问题有以下两种解决方案:可以将计算机使用的日期系统更改为与希望在 Excel 中键入的日期系统一致。也可以在 Excel 中创建新列,并使用 DATE 函数基于存为文本的日期创建真正的日期。以下是操作方法(假设计算机日期系统为 mm/dd/yyy,单元格 A1 中的文本日期为 31/12/2017):

  1. 创建如下公式:=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

  2. 结果将为 12/31/2017

  3. 如果希望公式显示为 dd/mm/yy,请按 CTRL+1(在 Mac 上,请按 MAC 命令按钮图标的图像 +1)。

  4. 选择使用 dd/mm/yy 格式的其他区域设置,例如“英语(英国)”。应用格式后,结果将为 31/12/2017,它是一个真正的日期,而不是文本日期。

注意: 上述公式使用 DATERIGHTMIDLEFT 函数编写。请注意,编写公式时假设文本日期中的日有两个字符、月有两个字符,年有四个字符。可能需要自定义公式,以适应自己的日期。

空格和文本问题

当公式引用包含空格的其他单元格(更复杂的情况是引用隐藏的空格)时,通常会发生 #VALUE! 错误。这些空格会使单元格看起来是空白单元格,但实际上它们并不是空白单元格。

1.选择引用的单元格

所选列

查找公式引用的单元格并选择它们。在大多数情况下,一种很好的做法是删除整列的空格,因为可以同时替换多个空格。在本示例中,单击 E 选择整列。

2. 查找和替换

“开始”选项卡>“查找和选择”>“替换”

在“开始”选项卡上,单击“查找和选择”>“替换”。

3.将空格替换为无内容

查找含有空格的框,替换为空白框

在“查找内容”框中,键入一个空格。然后,在“替换为”框中,删除可能存在的任何内容。

4.替换或全部替换

替换所有按钮

如果确定应删除列中的所有空格,请单击“全部替换”。如果希望循序渐进逐个将空格替换为无内容,可先单击“查找下一个”,确定不需要空格后单击“替换”。完成上述操作后,#VALUE! 错误可能得到解决。如果未解决,请转到下一步。

5.启用筛选器

“开始”>“排序和筛选”>“”>“筛选”

有时,空格以外的隐藏字符也会使单元格看起来是空白单元格,但它们并不是真正的空白单元格。单元格中的单撇号就可能导致此问题。若要删除列中的这些字符,请转到“开始”>“排序和筛选”>“筛选”。

6.设置筛选器

未选中“全选”复选框且选中“(空白)”复选框的筛选菜单

单击筛选箭头 筛选箭头 ,然后取消选中“全选”。然后,选中“空白”复选框。

7.选中任何未命名的复选框

选中未命名的复选框

选中旁边没有任何内容的任何复选框,如上所示。

8.选择空白单元格,然后删除

选中筛选后的空白单元格

当 Excel 返回空白单元格时,选择它们。然后,按 Delete 键。这将清除单元格中的所有隐藏字符。

9.清除筛选器

筛选菜单,清除筛选...

单击筛选箭头 筛选箭头 ,然后单击“从...中清除筛选”,使所有单元格均可见。

10.结果

#VALUE! 错误消失,替换为公式结果。单元格 E4 中的绿色矩形

如果空格是导致 #VALUE! 错误的罪魁祸首,那么错误可能被公式结果替换,如此处的示例所示。如果未替换,请对公式引用的其他单元格重复此过程。也可尝试本页中的其他解决方案。

注意: 请注意,在本示例中,单元格 E4 有一个绿色三角形,并且数字左对齐。这表示数字存为文本。这可能会在以后导致更多问题。如果看到此问题,建议将存为文本的数字转换为数字

单元格中的文本或特殊字符可能会导致 #VALUE! 错误。但是,有时很难确定哪些单元格存在这些问题。解决方案:使用 ISTEXT 函数检查单元格。请注意,ISTEXT 无法解决错误,只能查找可能导致错误的单元格。

#VALUE! 示例

H4 为 =E2+E3+E4+E5 且结果为 #VALUE!

以下是存在 #VALUE! 错误的公式示例。错误可能因单元格 E2 导致。“00”之后存在特殊字符,显示为一个小框。也可在单独的列中使用 ISTEXT 函数检查文本,如下图所示。

使用 ISTEXT 的同一示例

单元格 F2 为 =ISTEXT(E2) 且结果为 TRUE

在第 F 列中添加 ISTEXT 函数。除值为 TRUE 的单元格外,所有单元格都正常。这表示单元格 E2 包含文本。若要解决此问题,可以删除单元格的内容,重新键入 1865.00 的值。也可以使用 CLEAN 函数清除字符,或者使用 REPLACE 函数将特殊字符替换为其他值。

使用 CLEAN 或 REPLACE 后,需要复制结果,并使用“开始”>“粘贴”>“选择性粘贴”>“值”。可能还需要将存为文本的数字转换成数字

具有 + 和 * 等数学运算符的公式可能无法计算包含文本或空格的单元格。在这种情况下,请尝试改为使用函数。函数通常会忽略文本值,并将所有内容计算为数字,从而消除 #VALUE! 错误。例如,键入 =SUM(A2:C2) 代替 =A2+B2+C2。或者键入 =PRODUCT(A2,B2) 代替 =A2*B2

其他可尝试的解决方案

选择错误

单元格 H4 为公式 =E2+E3+E4+E5,结果为 #VALUE!

首先选择存在 #VALUE! 错误的单元格。

单击“公式”>“公式求值”

带 " "+E3+E4+E5 的“公式求值”对话框

单击“公式”>“公式求值”>“求值”。Excel 将逐步对公式的各个部分单独求值。在本例中,公式 =E2+E3+E4+E5 由于单元格 E2 中存在隐藏空格而被破坏。查看单元格 E2 并不能看到空格。但是,可以通过如下方式查看。它显示为 " "

有时可能只想将 #VALUE! 错误替换为其他内容,如自己的文本、零或空白单元格。在这种情况下,可以在公式中添加 IFERROR 函数。IFERROR 会检查是否存在错误,如果存在,则将其替换为选定的另一个值。如果不存在,则将计算原始公式。IFERROR 仅适用于 Excel 2007 和更高版本。对于早期版本,可使用 IF(ISERROR())

警告: IFERROR 会隐藏所有错误,而不仅仅是 #VALUE! 错误。不建议隐藏错误,因为错误通常表明需要修复一些内容,而不是隐藏。不建议使用此函数,除非绝对肯定公式按预期正常运行。

存在 #VALUE! 错误的单元格

单元格 H4 为 =E2+E3+E4+E5 且结果为 #VALUE!

以下是因单元格 D2 中的隐藏空格导致出现 #VALUE! 错误的公式示例。

IFERROR 隐藏的错误

单元格 H4 为 =IFERROR(E2+E3+E4+E5,"--")

以下是在公式中添加了 IFERROR 的同一公式。可将公式读作:“计算公式,但如果存在任何类型的错误,则将错误替换为两个短划线。”请注意,也可以使用 "" 不显示任何内容,以代替两个短划线。也可以替换为自己的文本,如:"Total Error"

遗憾的是,你将发现 IFERROR 实际上不解决该错误,而只是隐藏它。因此,请确定隐藏错误好过修复该错误。

数据连接可能在某个时刻变得不可用。若要修复此错误,请恢复数据连接,或考虑导入数据(如果可以)。如果无权访问连接,可请求工作簿创建者创建新文件。理想情况下新文件仅包含值而不包含连接。通过复制所有单元格和仅粘贴为值可达到此目的。若要仅粘贴为值,可单击“开始”>“粘贴”>“选择性粘贴”>“”。此操作可清除所有公式和连接,因此也可删除所有 #VALUE! 错误。

如果不确定此时该做什么,可在 Excel 社区论坛中搜索类似问题或发布你自己的问题。

Excel 社区论坛的链接

在 Excel 社区论坛中发布问题

另请参阅

Excel 中的公式概述

如何避免损坏的公式

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×