如何更正 #VALUE! 错误
Applies ToMicrosoft 365 专属 Excel Microsoft 365 Mac 版专属 Excel Excel 网页版 Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel Web App

在 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 Command 按钮图标的图像+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 隐藏所有错误,而不仅仅是 #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 中的公式概述

如何避免损坏的公式

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。