我用 Excel 数据验证替换了错误检查电子表格，并在错误发生之前就抓住了它们
我过去每周都会抽出时间，通过运行辅助公式、扫描条件格式中的红色单元格以及修正过去几天偷偷出现的错别字来清理我的电子表格。清理工作从未变得更短，因此我仔细研究了 Excel 的数据验证对话框。我现在不再在错误已经破坏我的数据透视表之后去寻找它们，而是在有人试图输入无效值的那一刻就阻止它们。
数据验证阻止错误输入，而不是事后再去发现它
整个设置大约每条规则需要30秒
我的旧设置并不完全坏掉。我在数据旁边有一列 IF 和 COUNTIF 公式静静运行，还有一些条件格式规则，当出现问题时会把单元格变红。每到星期五，我都会滚动查看并修复那些高亮显示的内容。问题是，这种方法是被动的——等我发现一个打字错误时，它已经弄乱了 SUMIFS 或者把数据透视表的某个类别拆分开了。
数据验证颠倒了这个顺序。它位于“数据”选项卡下，基本思路很简单：你告诉 Excel 单元格或区域中允许什么，Excel 会拒绝其他内容。要设置规则：
- 选择你想要保护的区域。
- 转到 数据 > 数据验证。
- 从“允许”下拉菜单中选择一个规则类型，例如，整数、小数、日期、时间、文本长度、列表或自定义。
- 设置参数（最小值和最大值、来源列表或公式）。
- 点击 确定。
在我的销售表中，我对“销售单位”列使用了整数规则，以确保其值在1到100之间。该列的实际值从7到44，因此设置上限为100既有余地，又不会允许像输入错误的1500这样不合理的数值。仅靠这个规则就能捕捉到那种错误，否则我可能需要用辅助公式和红色突出显示才能发现。
下拉列表消除了我最大的打字错误来源
一个来源列表，整个表格没有拼写错误
这是我最依赖的数据验证部分，也是我希望早些时候就整合到工作流程中的部分。我的销售电子表格有一个产品类别列，在我添加列表规则之前，同一列中存在“Electronics”、“ELECTRONICS”和“electronics”作为三个不同的值。“Home & Garden”和“Home and Garden”也将同一类别分成了两个。
当我从那些数据创建数据透视表时，本应是一个产品线却出现了重复行。以下是我解决它的方法：
- 在工作表的其他地方设置一个小范围，包含四个允许的值：电子产品、服装、家居与花园和运动。
- 选择产品类别列。
- 转到 数据 > 数据验证 > 允许 > 列表。
- 将来源设置为该范围（或将其转换为 Excel 表格并将其作为命名范围引用，这样当我添加新类别时下拉列表会自动扩展）。
- 点击 确定。
现在该列中的每个单元格都显示一个小箭头，并且条目仅限于那四个选项。下一次我刷新数据透视表时，它就自己整理好了。
将源指向 Excel 表格比直接在验证对话框中输入数值更好，因为你可以在一个地方添加或删除类别，并且每个下拉列表都会自动更新。
自定义公式可以执行内置选项无法实现的规则
一个公式可以阻止重复项、强制格式或捕捉逻辑错误
数据验证中的自定义选项是该功能在处理更复杂规则时真正有用的地方。你编写一个公式，对于有效的条目返回 TRUE，对于其他所有条目返回 FALSE，然后 Excel 会处理其余部分。
我发现自己经常使用的三条规则：
- 阻止重复条目。对于订单 ID 列，
=COUNTIF($E$2:$E$1000,E2)=1可以防止任何人记录相同的 ID 两次。这比事后运行重复检查要干净得多。 - 强制执行格式。如果你表格中的产品代码应该总是以“PRD-”开头，
=LEFT(A2,4)="PRD-"会拒绝任何不符合的内容。对于保持协作者间代码的一致性非常有用。 - 捕捉逻辑错误。如果 B 列保存结束日期而 A 列保存开始日期，
=B2>=A2会阻止有人输入早于开始日期的项目结束日期。
这个权衡值得注意。自定义公式只会返回通过或失败——Excel不会告诉用户他们的输入被拒绝的原因，这就是下一部分的用途。
保持自定义公式简单。任何你一眼就能看懂的东西，在一年后当你忘记最初为什么设置规则时，都会更容易调试。
输入消息和错误警报使规则显而易见
一个清晰的提示总比沉默的失败好
数据验证对话框还有另外两个同样重要的选项卡：输入信息和错误警告。它们可以将一个无声的规则变成协作者实际上可以使用的东西。
输入消息选项卡会添加一个小提示，当有人选择单元格时会弹出。我用它来解释在任何人开始输入之前的预期——类似于“输入1到100之间的值”或“从下拉菜单中选择一个类别”。它不会阻止任何操作；它只是回答别人本来得向我询问的问题。
错误提醒标签更有趣，因为它为你提供三种样式可供选择：
- 停止 完全拒绝该条目。最适合硬性规则，例如唯一 ID 或类别标签。
- 警告 在接受值之前要求用户确认。适用于软规则，例如，允许但不寻常的超过30%的折扣。
- 信息 仅通知用户，但仍然接受输入。
这里值得做的另一件事是编写有用的提示文本。Excel 的默认消息是“此值与为此单元格定义的数据验证限制不匹配”，这对任何人都没有帮助。然而，“请输入项目开始日期之后的日期”则更加清晰，实际上有帮助。
我接下来要把我的验证规则带到哪里
在动态数组之上添加验证层是下一步
数据验证并不能完全替代条件格式——仍然有必要突出显示那些虽然符合规则但看起来不寻常的异常值。它改变的是操作的顺序。我接下来想尝试的是将验证规则与动态数组公式配合使用，这样当我添加新的类别或销售人员时，下拉列表的源列表会自动扩展。