如何在Microsoft Excel中隐藏错误值和指示符

Your Excel formulas can occasionally produce errors that don’t need fixing. However, these errors can look untidy and, more importantly, stop other formulas or Excel features from working correctly. Fortunately, there are ways to hide these error values.

您的Excel公式有时会产生不需要修复的错误。 但是，这些错误看起来很麻烦，更重要的是，这些错误会阻止其他公式或Excel功能正常工作。 幸运的是，有一些方法可以隐藏这些错误值。

使用IFERROR函数隐藏错误 (Hide Errors with the IFERROR Function)

The easiest way to hide error values on your spreadsheet is with the IFERROR function. Using the IFERROR function, you can replace the error that’s shown with another value, or even an alternative formula.

在电子表格上隐藏错误值的最简单方法是使用IFERROR函数。 使用IFERROR函数，您可以将显示的错误替换为另一个值，甚至替换公式。

In this example, a VLOOKUP function has returned the #N/A error value.

在此的示例VLOOKUP函数已返回＃N / A错误值。

This error is due to there not being an office to look for. A logical reason, but this error is causing problems with the total calculation.

此错误是由于找不到办公室。 出于逻辑原因，但是此错误导致总计算出现问题。

The IFERROR function can handle any error value including #REF!, #VALUE!, #DIV/0!, and more. It requires the value to check for an error and what action to perform instead of the error if found.

IFERROR函数可以处理任何错误值，包括#REF！，＃VALUE！，＃DIV / 0！等。 它需要该值来检查错误以及执行什么操作而不是发现错误。

In this example, the VLOOKUP function is the value to check and “0” is displayed instead of the error.

在此示例中，VLOOKUP函数是要检查的值，并且显示“ 0”而不是错误。

Using “0” instead of the error value ensures the other calculations and potentially other features, such as charts, all work correctly.

使用“ 0”代替错误值可确保其他计算以及可能的其他功能(例如图表)均正常工作。

后台错误检查 (Background Error Checking)

If Excel suspects an error in your formula, a small green triangle appears in the top-left corner of the cell.

如果Excel怀疑您的公式错误，则单元格的左上角会出现一个绿色小三角形。

Note that this indicator does not mean that there is definitely an error, but that Excel is querying the formula you’re using.

请注意，此指示符并不意味着肯定存在错误，而是Excel正在查询您正在使用的公式。

Excel automatically performs a variety of checks in the background. If your formula fails one of these checks, the green indicator appears.

Excel在后台自动执行各种检查。 如果您的公式未通过这些检查之一，则会出现绿色指示符。

When you click on the cell, an icon appears warning you of the potential error in your formula.

当您单击单元格时，会出现一个图标，警告您公式中可能存在错误。

Click the icon to see different options for handling the supposed error.

单击该图标可查看用于处理假定错误的其他选项。

In this example, the indicator has appeared because the formula has omitted adjacent cells. The list provides options to include the omitted cells, ignore the error, find more information, and also change the error check options.

在此示例中，由于公式已省略了相邻的单元格，因此出现了指示符。 该列表提供了包括省略的单元格，忽略错误，查找更多信息以及更改错误检查选项的选项。

To remove the indicator, you need to either fix the error by clicking “Update Formula to Include Cells” or ignore it if the formula is correct.

要删除指标，您需要通过单击“更新公式以包括单元格”来修复错误，或者在公式正确的情况下忽略该错误。

关闭Excel错误检查 (Turn Off the Excel Error Checking)

If you do not want Excel to warn you of these potential errors, you can turn them off.

如果您不希望Excel对这些潜在错误发出警告，则可以将其关闭。

Click File > Options. Next, select the “Formulas” category. Uncheck the “Enable Background Error Checking” box to disable all background error checking.

单击文件>选项。 接下来，选择“公式”类别。 取消选中“启用后台错误检查”框以禁用所有后台错误检查。

Alternatively, you can disable specific error checks from the “Error Checking Rules” section at the bottom of the window.

或者，您可以从窗口底部的“错误检查规则”部分禁用特定的错误检查。

By default, all of the error checks are enabled except “Formulas Referring to Empty Cells.”

默认情况下，所有错误检查均处于启用状态，但“公式引用空单元格”除外。

More information about each rule can be accessed by positioning the mouse over the information icon.

通过将鼠标置于信息图标上，可以访问有关每个规则的更多信息。

Check and uncheck the boxes to specify which rules you would like Excel to use with the background error checking.

选中并取消选中这些框，以指定您希望Excel与后台错误检查一起使用的规则。

When formula errors do not need fixing, their error values should be hidden or replaced with a more useful value.

当公式错误不需要修复时，应将其错误值隐藏或替换为更有用的值。

Excel also performs background error checking and queries mistakes it thinks you’ve made with your formulas. This is useful but specific or all error checking rules can be disabled if they interfere too much.

Excel还执行背景错误检查，并查询它认为您使用公式造成的错误。 这很有用，但是如果特定规则或所有错误检查规则干扰过多，则可以将其禁用。

翻译自: https://www.howtogeek.com/442863/how-to-hide-error-values-and-indicators-in-microsoft-excel/