查找和更正公式中的错误

查找和更正公式中的错误

全部显示全部隐藏有时，公式不仅会产生错误值，而且还会返回意外结果。下面列出了一些可用于查找和调查这些错误的原因并确定解决方法的工具。您要做什么？了解如何输入简单公式更正输入公式时的常见错误更正公式中的常见问题打开或关闭错误检查规则依次更正常见公式错误在工作表上标记常见公式错误并对其进行更正更正错误值使用“监视窗口”来监视公式及其结果向“监视窗口”中添加单元格从“监视窗口”中删除单元格对嵌套公式进行分步求值显示公式与单元格之间的关系了解如何输入简单公式公式是对工作表中的值执行计算的等式。公式以等号(=)开头。例如，在下面的公式中，结果等于3加1。=3+1公式也可以包括下列部分或全部内容：函数（函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）、引用、运算符（运算符:一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。）和常量（常量:不进行计算的值，因此也不会发生变化。例如，数字210以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。）。公式的组成部分

函数：PI()函数返回值pi：3.142...

引用：A2返回单元格A2中的值。

常量：直接输入公式中的数字或文本值，如2。

运算符：^（脱字号）运算符表示将数字乘方，*（星号）运算符表示将两个或多个数字相乘。返回页首更正输入公式时的常见错误下表概括了用户在输入公式时可能会犯的一些最常见错误，并说明如何更正这些错误：请确保...详细信息每个函数都以等号(=)开头

如果省略等号，则键入的内容会显示为文本或日期。例如，如果键入SUM(A1:A10)，则MicrosoftExcel会显示文本字符串SUM(A1:A10)并且不会计算该公式。如果键入11/2，则Excel会显示一个日期，如“11月2日”或“11/02/09”，而不是11除以2。所有左括号和右括号匹配

请确保所有括号都成对出现。在公式中使用函数时，函数中的每个括号都必须位于正确的位置这一点很重要，因为这样函数才能正常工作。例如，公式=IF(B5<0),"Notvalid",B5*1.05)将不能工作，因为此处有两个右括号而只有一个左括号。正确的公式如下所示：=IF(B5<0,"Notvalid",B5*1.05)。用冒号表示区域

引用单元格区域时，请使用冒号(:)分隔对单元格区域中第一个单元格的引用和对最后一个单元格的引用。例如，A1:A5。输入所有必需参数

有些工作表函数具有必需参数（参数:函数中用来执行操作或计算的值。参数的类型与函数有关。函数中常用的参数类型包括数字、文本、单元格引用和名称。），而有些函数（如PI）则不需要参数。此外，还要确保没有输入过多的参数。例如，UPPER函数只接受一个文本字符串作为其参数。输入正确类型的参数

有些工作表函数（如SUM）要求使用数字参数。而有些函数（如REPLACE）则要求其参数至少有一个为文本值。如果将错误类型的数据用作参数，则Excel就可能会返回意外结果或显示错误。函数的嵌套不超过64层

可以在函数中输入（或嵌套）64层以下的函数。例如，公式=IF(SQRT(PI())<2,"Lessthantwo!","Morethantwo!")包含三个函数：PI函数嵌套在SQRT函数内，后者又嵌套在IF函数内。将其他工作表名称包含在单引号中

如果公式中引用了其他工作表或工作簿中的值或单元格，并且这些工作簿或工作表的名称中包含非字母字符，那么必须用单引号(')将其名称引起来。在公式中引用工作表名称时，在其后放置一个感叹号(!)

例如，若要在同一工作簿中名为QuarterlyData的工作表中返回单元格D3的值，请使用下面的公式：='QuarterlyData'!D3。包含外部工作簿的路径

请确保每个外部引用（外部引用:对其他Excel工作簿中的工作表单元格或区域的引用，或对其他工作簿中的定义名称的引用。）都包含工作簿的名称和路径。对工作簿的引用包括该工作簿的名称且必须用方括号([])括起来。此引用必须还包含工作簿中相应工作表的名称。例如，若要包含对名为Q2Operations.xlsx的工作簿（当前在Excel中处于打开状态）中名为Sales的工作表上A1至A8单元格的引用，则公式如下所示：=[Q2Operations.xlsx]Sales!A1:A8。如果要引用的工作簿在Excel中未打开，仍然可以在公式中包含对此工作簿的引用。您可以提供此文件的完整路径，如下面的示例所示：=ROWS('C:\MyDocuments\[Q2Operations.xlsx]Sales'!A1:A8)。此公式返回另一工作簿中A1至A8单元格区域内的行数(8)。备注

如果完整路径中包含空格字符，则如上例中所示，必须将路径用单引号引起来（在路径开头处工作表名称后面，感叹号之前）。输入无格式的数字

在公式中输入数字时不要设置数字格式。例如，如果要输入的值为￥1,000，则在公式中输入1000。如果您在数字中输入逗号，Excel会将其视作分隔符。如果希望显示数字，以便它们显示千位、百万位分隔符或货币符号，请在输入数字之后设置单元格格式。例如，如果要将3100与单元格A3中的值相加，并输入公式=SUM(3,100,A3)，则Excel会将数字3与100相加，然后将该总和与A3的值相加，而不是将3100与A3相加。或者，如果输入公式=ABS(-2,134)，则Excel会显示错误，因为ABS函数只接受一个参数。避免除数为零

将某个单元格除以包含零或不包含值的单元格会导致#DIV/0!错误。返回页首更正公式中的常见问题您可以实施某些规则来检查公式中的错误。这些规则的作用与拼写检查器（用于检查在单元格中所输入的数据的错误）类似。这些规则不能保证工作表（工作表:在Excel中用于存储和处理数据的主要文档。也称为电子表格。工作表由排列成行或列的单元格组成。工作表总是存储在工作簿中。）没有错误，但对发现常见错误却大有帮助。您可以单独打开或关闭其中的任何规则。可以使用两种方法标记并更正错误：一种是一次检查一个错误（就像拼写检查器一样），另一种是在输入数据时立即在工作表上进行检查。无论使用哪种方法，在找到错误时，都会在发生错误的单元格的左上角出现一个三角形。包含公式错误的单元格可以使用Excel所显示的选项来解决某个错误，也可以通过单击“忽略错误”忽略该错误。如果忽略特定单元格中的某个错误，则该单元格中的该错误就不会再出现在以后的错误检查中。但是，您可以重置以前忽略的所有错误以使其重新出现。打开或关闭错误检查规则依次单击“文件”选项卡、“选项”和“公式”类别。在“错误检查规则”下，选中或清除下列任意规则的复选框：所含公式导致错误的单元格

公式未使用所需的语法、参数或数据类型。错误值包括#DIV/0!、#N/A、#NAME?、#NULL!、#NUM!、#REF!和#VALUE!。每个错误值都有不同的原因和解决方法。备注

如果错误值是直接在单元格中输入的，则它会以该错误值进行存储，而不会被标记为错误。但是，如果另一个单元格中的公式引用了该单元格，则该公式会根据该单元格返回错误值。表中不一致的计算列公式

计算列可能包含与列公式不同的公式，这将创建一个例外。当执行下列任一操作时，将会创建计算列例外：在计算列单元格中键入数据而非公式。在计算列单元格中键入公式，然后单击快速访问工具栏上的“撤消”。在已经包含一个或多个例外的计算列中键入新的公式。将数据复制到与计算列公式不匹配的计算列中。备注

如果复制的数据包含公式，该公式就会覆盖计算列中的数据。移动或删除由计算列中某一行引用的另一个工作表区域上的单元格。包含以两位数表示的年份的单元格

在用于公式时，该单元格所包含的文本日期可能被误解为错误的世纪。例如，公式中的日期=YEAR("1/1/31")可能是1931年或2031年。使用此规则可以检查有歧义的文本日期。文本格式的数字或者前面有撇号的数字

该单元格中包含存储为文本的数字。从其他源导入数据时，通常会存在这种现象。存储为文本的数字可能会导致意外的排序结果，因此最好将其转换为数字。与区域中的其他公式不一致的公式

公式与其他相邻公式的模式不匹配。许多情况下，相邻公式的差别只在于各自引用的内容不同。在以下四个相邻公式的示例中，Excel将在公式=SUM(A10:F10)旁边显示一个错误，这是因为相邻公式是按一行递增的，而公式=SUM(A10:F10)增加了8行

-Excel认为正确的公式应为=SUM(A3:F3)。

12345A公式=SUM(A1:F1)=SUM(A2:F2)=SUM(A10:F10)=SUM(A4:F4)如果某公式中使用的引用与相邻公式中的引用不一致，Excel就会显示错误。遗漏了区域中的单元格的公式

如果在原数据区域和包含公式的单元格之间插入了一些数据，则该公式可能无法自动包含对这些数据的引用。此规则将公式中的引用与包含该公式的单元格的相邻单元格的实际区域进行比较。如果相邻单元格包含其他值并且不为空，则Excel会在该公式旁边显示一个错误。例如，应用此规则时，Excel在公式=SUM(A2:A4)旁边插入一个错误，这是因为单元格A5、A6和A7与该公式所引用的单元格以及包含该公式的单元格(A8)相邻，而这些单元格包含本应在该公式中引用的数据。

12345678A发票15,0009,0008,00020,0005,00022,500=SUM(A2:A4)包含公式的未锁定单元格

公式未受到锁定保护。默认情况下，所有单元格均受到锁定保护，因此该单元格已设置为取消保护。当公式受到保护时，必须先取消保护才能对其进行修改。请检查并确保不需要保护该单元格。对包含公式的单元格进行保护可以防止这些单元格被更改，并且有助于避免将来出错。引用空单元格的公式

公式包含对空单元格的引用。这可能会导致意外结果，如下面的示例所示。假设您要对以下一列单元格中的数字计算平均值。如果第三个单元格为空，那么它就不会包含在计算中，因此会得到错误结果22.75。如果第三个单元格包含0，就会得到正确结果18.2。

1234567A数据2412

4510=AVERAGE(A2:A6)表中输入的无效数据

表中存在有效性错误。检查单元格的有效性设置，方法是：在“数据”选项卡上的“数据工具”组中单击“数据有效性”。返回页首依次更正常见公式错误警告

如果以前已对工作表进行错误检查，则除非重置这些忽略的错误，否则不会出现忽略的任何错误。选择要进行错误检查的工作表。如果要手动计算工作表，请按F9以重新计算。在“公式”选项卡上的“公式审核”组中，单击“错误检查”组按钮。在找到错误时，将显示“错误检查”对话框。如果以前忽略了一些错误，则可以通过执行下列操作重新检查这些错误：单击“选项”。在“错误检查”部分中，单击“重新设置忽略错误”。单击“确定”。单击“继续”。备注

重置忽略的错误会重置当前工作簿中的所有工作表。将“错误检查”对话框放在编辑栏（编辑栏:位于Excel窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。）正下方。单击对话框右侧的操作按钮之一。可用的操作会因每种错误类型而有所不同。备注

如果单击“忽略错误”，将标记此错误，后面的每次检查都会忽略它。单击“下一步”。继续进行直至完成错误检查。返回页首在工作表上标记常见公式错误并对其进行更正单击“文件”选项卡。单击“选项”，然后单击“公式”类别。确保在“错误检查”下选中了“允许后台错误检查”复选框。若要更改标记错误发生位置的三角形的颜色，请在“使用此颜色标识错误”框中，选择所需的颜色。单击“确定”关闭“Excel选项”对话框。若要更正工作表中的错误，请选择一个左上角带三角的单元格。在该单元格旁边，单击显示的“错误检查”按钮，然后单击所需的选项。可用的命令会因每种错误类型而有所不同，并且第一个条目会对错误进行描述。如果单击“忽略错误”，将标记此错误，后面的每次检查都会忽略它。重复上述两个步骤。返回页首更正错误值如果公式无法正确计算结果，Excel将会显示错误值，例如#####、#DIV/0!、#N/A、#NAME?、#NULL!、#NUM!、#REF!和#VALUE!。每种错误类型都有不同的原因和不同的解决方法。下表包含指向详细描述这些错误的文章的链接，以及帮您入门的简要说明。指向详细的文章的链接说明更正#####错误当某列不足够宽而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。例如，用过去的日期减去将来的日期的公式（如=06/15/2008-07/01/2008）将得到负的日期值。更正#DIV/0!错误当一个数除以零(0)或不包含任何值的单元格时，Excel将显示此错误。更正#N/A错误当某个值不可用于函数或公式时，Excel将显示此错误。更正#NAME?错误当Excel无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称可能拼写错误。更正#NULL!错误当指定两个不相交的区域的交集时，Excel将显示此错误。交集运算符是分隔公式中的引用的空格字符。例如，区域A1:A2和C3:C5不相交，因此，输入公式=SUM(A1:A2C3:C5)将返回#NULL!错误。更正#NUM!错误当公式或函数包含无效数值时，Excel将显示此错误。更正#REF!错误当单元格引用无效时，Excel将显示此错误。例如，您可能删除了其他公式所引用的单元格，或者可能将已移动的单元格粘贴到其他公式所引用的单元格上。更正#VALUE!错误如果公式所包含的单元格有不同的数据类型，则Excel将显示此错误。如果启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。返回页首使用“监视窗口”来监视公式及其结果当单元格在工作表上不可见时，您可以在“监视窗口”工具栏（工具栏:包含可用于执行命令的按钮和选项的栏。要显示工具栏，请按Alt然后按Shift+F10。）中监视这些单元格及其公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。）。使用“监视窗口”可以方便地在大型工作表中检查、审核或确认公式计算及其结果。使用“监视窗口”，无需反复滚动或定位到工作表的不同部分。该工具栏可像其他任何工具栏一样进行移动和固定（固定:固定到窗口的上、下或两侧。可固定任务窗格、工具箱、菜单栏和所有工具栏。）。例如，可将其固定到窗口的底部。该工具栏可以跟踪单元格的下列属性：工作簿、工作表、名称、单元格、值以及公式。备注

每个单元格只能有一个监视点。向“监视窗口”中添加单元格选择要监视的单元格。要用公式选择工作表上的所有单元格，请在“开始”选项卡的“编辑”组中，单击“查找和选择”，单击“定位条件”，然后单击“公式”。在“公式”选项卡上的“公式审核”组中，单击“监视窗口”。单击“添加监视点”。单击“添加”。将“监视窗口”工具栏移动到窗口的顶部、底部、左侧或右侧。要更改列的宽度，请拖动列标题右侧的边界。要显示“监视窗口”工具栏中的条目引用的单元格，请双击该条目。备注

仅当其他工作簿处于打开状态时，包含指向这些工作簿的外部引用（外部引用:对其他Excel工作簿中的工作表单元格或区域的引用，或对其他工作簿中的定义名称的引用。）的单元格才会显示在“监视窗口”工具栏中。返回页首从“监视窗口”中删除单元格如果“监视窗口”工具栏（工具栏:包含可用于执行命令的按钮和选项的栏。要显示工具栏，请按Alt然后按Shift+F10。）未显示，请在“公式”选项卡的“公式审核”组中，单击“监视窗口”。选择要删除的单元格。要选择多个单元格，请按Ctrl并单击所需单元格。单击“删除监视”。返回页首对嵌套公式进行分步求值有时，理解嵌套公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。）如何计算最终结果是很困难的，因为存在若干个中间计算和逻辑测试。但是，通过使用“公式求值”对话框，可以按计算公式的顺序查看嵌套公式的不同求值部分。例如，如果您能够查看下列中间结果，那么公式=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)就更容易理解了：对话框中显示的步骤说明=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)最先显示的是此嵌套公式。AVERAGE函数和SUM函数嵌套在IF函数内。=IF(40>50,SUM(G2:G5),0)单元格区域F2:F5包含值55、35、45和25，因此AVERAGE(F2:F5)函数的结果为40。=IF(False,SUM(G2:G5),0)由于40小于50，因此IF函数的第一个参数（logical_test参数）中的表达式为False。0IF函数返回第三个参数（value_if_false参数）的值。SUM函数不会进行求值，因为它是IF函数的第二个参数（value_if_true参数），它只有在表达式为True时才会返回。选择要求值的单元格。一次只能对一个单元格进行求值。在“公式”选项卡的“公式审核”组中，单击“公式求值”。单击“求值”以检查带下划线的引用的值。求值结果将以斜体显示。如果公式的下划线部分是对其他公式的引用，请单击“步入”以在“求值”框中显示其他公式。单击“步出”将返回到以前的单元格和公式。备注

当引用第二次出现在公式中，或者公式引用了另外一个工作簿中的单元格时，“步入”按钮不可用。继续操作，直到公式的每一部分都已求值完毕。若要再次查看计算过程，请单击“重新启动”。若要结束求值，请单击“关闭”。

注释

公式中某些使用IF和CHOOSE函数的部分不会进行求值；对于这此示例，将在“求值”框中显示#N/A。如果引用为空，则会在“求值”框中显示零值(0)。下列函数在每次工作表更改时都会重新计算，并会导致“公式求值”给出的结果不同于单元格中显示的结果：RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY、RANDBETWEEN。返回页首显示公式与单元格之间的关系有时，当公式使用引用单元格或从属单元格时，检查公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。）的准确性或查找错误的根源会很困难：引用单元格

是被其他单元格中的公式引用的单元格。例如，如果单元格D10包含公式=B5，那么单元格B5就是单元格D10的引用单元格。从属单元格

包含引用其他单元格的公式。例如，如果单元格D10包含公式=B5，那么单元格D10就是单元格B5的从属单元格。为了帮助检查公式，可以使用“追踪引用单元格”和“追踪从属单元格”命令以图形方式显示或追踪这些单元格与包含追踪箭头（追踪箭头:该箭头显示活动单元格与其相关单元格之间的关系。由提供数据的单元格指向其他单元格时，追踪箭头为蓝色；如果单元格中包含错误值，如#DIV/0!，追踪箭头则为红色。）的公式之间的关系。单击“文件”选项卡。单击“选项”，然后单击“高级”类别。在“此工作簿的显示选项”部分，选择所需的工作簿，然后确保在“对于对象，显示”下选择了“全部”或“无内容(隐藏对象)”。如果公式引用其他工作簿中的单元格，请打开该工作簿。Excel无法定位到未打开的工作簿中的单元格。请执行下列操作之一：若要追踪为公式提供数据的单元格（引用单元格），请执行下列操作：选择包含需要找到其引用单元格的公式的单元格。若要显示直接向活动单元格提供数据的每个单元格的追踪箭头，请在“公式”选项卡的“公式审核”组中，单击“追踪引用单元格”。蓝色箭头显示无错误的单元格。红色箭头显示导致错误的单元格。如果所选单元格被另一个工作表或工作簿上的单元格引用，则会显示一个从所选单元格指向工作表图标的黑色箭头。但是，必须首先打开该工作簿，Excel才能追踪这些从属单元格。若要标识为活动单元格提供数据的下一级单元格，请再次单击“追踪引用单元格”。要从距离活动单元格最远的引用单元格开始，一次移去一级追踪箭头，请在“公式”选项卡的“公式审核”组中，单击“移去箭头”旁边的箭头，然后单击“移去引用单元格追踪箭头”。要移去另一级追踪箭头，请再次单击该按钮。若要追踪引用特定单元格（从属单元格）的公式，请执行下列操作：选择要对其标识从属单元格的单元格。若要对从属于活动单元格的每个单元格显示追踪箭头，请在“公式”选项卡的“公式审核”组中，单击“追踪从属单元格”。蓝色箭头显示无错误的单元格。红色箭头显示导致错误的单元格。如果所选单元格被另一个工作表或工作簿上的单元格引用，则会显示一个从所选单元格指向工作表图标的黑色箭头。但是，必须首先打开该工作簿，Excel才能追踪这些从属单元格。若要标识从