如何更正 #VALUE! 错误

发布时间：2022-11-09 文章分类：Excel函数 投稿人：王小丽 字号： 默认 | 大 | 超大 打印

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

修复某特定函数的错误使用的是哪个函数？使用的是哪个函数？

AVERAGE

CONCATENATE

COUNTIF、COUNTIFS

DATEVALUE

DAYS

FIND、FINDB

IF

INDEX、MATCH

SEARCH、SEARCHB

SUM

SUMIF、SUMIFS

SUMPRODUCT

TIMEVALUE

TRANSPOSE

VLOOKUP

*以上都不是

详细信息请参阅更正AVERAGE或SUM函数中的#VALUE!错误

详细信息请参阅更正CONCATENATE函数中的#VALUE!错误

详细信息请参阅更正COUNTIF/COUNTIFS函数中的#VALUE!错误

详细信息请参阅更正DATEVALUE函数中的#VALUE!错误

详细信息请参阅更正DAYS函数中的#VALUE!错误

详细信息请参阅更正FIND/FINDB和SEARCH/SEARCHB函数中的#VALUE!错误

详细信息请参阅更正IF函数中的#VALUE!错误

详细信息请参阅更正INDEX和MATCH函数中的#VALUE!错误

详细信息请参阅更正FIND/FINDB和SEARCH/SEARCHB函数中的#VALUE!错误

详细信息请参阅更正AVERAGE或SUM函数中的#VALUE!错误

详细信息请参阅更正SUMIF/SUMIFS函数中的#VALUE!错误

详细信息请参阅更正SUMPRODUCT函数中的#VALUE!错误

详细信息请参阅更正TIMEVALUE函数中的#VALUE!错误

详细信息请参阅更正TRANSPOSE函数中的#VALUE!错误

详细信息请参阅更正VLOOKUP函数中的#VALUE!错误

在此列表中看不到你的函数？请尝试下面列出的其他解决方案。

减法问题

如何进行基本的减法运算如果未使用过Excel，则键入的减法公式可能错误。键入减法公式的方法有以下两种：

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

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

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

  1. 首先进行快速测试。在一张新的工作簿中，在单元格A1中键入2。在单元格B1中键入4。然后在C1中键入如下公式：=B1-A1如果收到#VALUE!错误，转到下一步。如果未收到错误，请尝试本页上的其他解决方案。
  2. 在Windows中，打开“区域”控制面板。
    • Windows10：单击“开始”，键入“区域”，然后单击“区域”控制面板。
    • Windows8：在“开始”界面，键入“区域”，单击“设置”，然后单击“区域”。
    • Windows7：单击“开始”，然后键入“区域”，然后单击“区域和语言”。
  3. 在“格式”选项卡上，单击“其他设置”。
  4. 找到“列表分隔符”。如果列表分隔符设置为减号，请将其更改为其他符号。例如，更改为常见的逗号列表分隔符。分号也很常见。但是，其他列表分隔符可能更适合你的特定区域。
  5. 单击“确定”。
  6. 打开工作簿。如果单元格存在#VALUE!错误，请双击进行编辑。
  7. 如果应为减法减号的位置为逗号，请将其更改为减号。
  8. 按Enter。
  9. 对存在错误的其他单元格重复此过程。

如何对日期进行减法运算用一个单元格引用减去另一个单元格引用在两个单独的单元格中键入两个日期。在第三个单元格，用一个单元格引用减去另一个单元格引用。在本示例中，单元格D10为开始日期，单元格E10为结束日期。F10的公式为=E10-D10。

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

对存为文本的日期进行减法运算时出现#VALUE!错误调宽日期列。如果日期右对齐，则表示是一个日期。但是，如果左对齐，则表示不是一个真正的日期。而是文本。Excel不会将文本识别为日期。可帮助解决此问题的一些解决方案如下。

检查前导空格

  1. 双击要在减法公式中使用的日期。
  2. 将光标放在开头，查看是否可以选择一个或多个空格。在单元格开头选中空格的情况如图所示：

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

  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上，请按+1）。
  4. 选择使用dd/mm/yy格式的其他区域设置，例如“英语(英国)”。应用格式后，结果将为31/12/2017，它是一个真正的日期，而不是文本日期。

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

空格和文本问题

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

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

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

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

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

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

6.设置筛选器单击筛选箭头，然后取消选中“全选”。然后，选中“空白”复选框。

7.选中任何未命名的复选框选中旁边没有任何内容的任何复选框，如上所示。

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

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

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

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

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

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

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

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

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

其他可尝试的解决方案

尝试确定错误源选择错误首先选择存在#VALUE!错误的单元格。

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

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

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

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

IFERROR隐藏的错误以下是在公式中添加了IFERROR的同一公式。可以通过以下方式阅读此公式：“计算公式，但如果有任何类型的错误，将其替换为短划线。”请注意，还可以使用””不显示任何内容，以代替两个短划线。也可以替换为自己的文本，如：”TotalError”。

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

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

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

在Excel社区论坛中发布问题

另请参阅Excel中的公式概述

如何避免损坏的公式

标签 , ,