忽略Excel数据验证中的空白问题

In Monday's blog, you saw how to make simple dependent data validation drop down lists. After creating the drop downs, you added some flexibility by using the IF function in the data validation formula. See a couple of problems that can occur when you refer to other cells in your data validation, and those cells are blank.

在星期一的博客中，您了解了如何制作简单的依赖数据验证下拉列表。 创建下拉列表后，可以通过在数据验证公式中使用IF函数来增加灵活性。 请参见在数据验证中引用其他单元格时看到的一些问题，这些单元格为空。

允许无效的条目 (Invalid Entries Are Allowed)

If you create a data validation formula that refers to another cell, and that cell is empty, users might be able to type invalid entries in the cell. For example, in the screen shot below, Buffalo was entered in cell B4.

如果您创建一个引用另一个单元格的数据验证公式，并且该单元格为空，则用户可能能够在该单元格中键入无效的条目。 例如，在下面的屏幕快照中，在单元格B4中输入了Buffalo。

As you can see in the list in cell B5, that isn't one of the cities allowed when the adjacent cell in column A is empty.

正如您在单元格B5中的列表中看到的那样，当列A中的相邻单元格为空时，这不是允许的城市之一。

更改忽略空白设置 (Change the Ignore Blank Setting)

To prevent people from entering invalid data when the cell referred to is empty, you can open the Data Validation dialog box, and remove the check mark from the Ignore Blank setting.

为了防止人们在引用的单元格为空时输入无效数据，可以打开“数据验证”对话框，然后从“忽略空白”设置中删除复选标记。

With the Ignore Blank setting turned off, users will see an error message if they try to enter invalid data.

关闭“忽略空白”设置后，如果用户尝试输入无效数据，将看到错误消息。

圈子无效数据的问题 (Problem with Circle Invalid Data)

I always turn off the Ignore Blank setting when using dependent data validation drop down lists, as I described above. However, last week I heard from Paul, who uses the Circle Invalid Data feature in one of his workbooks.

如上所述，我始终在使用相关数据验证下拉列表时关闭“忽略空白”设置。 但是，上周我收到了Paul的来信，Paul在他的一本工作簿中使用了“圈数无效数据”功能。

When the Ignore Blank setting is turned off, Excel treats empty cells as invalid data, when you run the Circle Invalid Data feature. That's a helpful feature when you don't want to allow empty cells, but not very helpful in this case.

关闭“忽略空白”设置时，当您运行“循环无效数据”功能时，Excel会将空单元格视为无效数据。 当您不想允许空单元格时，这是一个有用的功能，但在这种情况下不是很有用。

To remove the circles, use the Clear Validation Circles command on the Excel Ribbon's Data tab.

要删除圆，请使用Excel功能区“数据”选项卡上的“清除验证圆”命令。

取舍 (Make a Choice)

Despite extensive experiments, I couldn't find a formula that would prevent invalid entries in a dependent data validation cell, where the referenced cell is empty, without turning off the Ignore Blank setting. If you find one, please let me know, and I'll update this blog post.

尽管进行了广泛的实验，但在未关闭“忽略空白”设置的情况下，我找不到一个公式可以防止在相关数据验证单元格(其中引用的单元格为空)中防止无效条目。 如果找到一个，请告诉我，我将更新此博客文章。

In the meantime, I'd rather prevent invalid entries, than catch them later, so I'll stick with that setting change. I don't really use the Circle Invalid Data feature anyway, so I won't miss it!

同时，我宁愿防止无效条目，也不愿以后再捕获它们，因此我将坚持该设置更改。 无论如何，我实际上并没有使用“圈子无效数据”功能，所以我不会错过！

观看视频 (Watch the Video)

To see the steps for turning off the Ignore Blank setting, and the problems that can occur, watch this Ignore Blank Problems in Excel Data Validation video.

要查看关闭“忽略空白”设置的步骤以及可能发生的问题，请观看此“ Excel数据验证”中的“忽略空白问题”视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/08/11/ignore-blank-problems-in-excel-data-validation/