下载掌阅APP，畅读海量书库
立即打开
数据验证（在Excel 2010及早期版本中叫作数据有效性）可以限制用户输入的数据类型、范围、格式等，帮助用户确保数据输入的准确性和一致性，避免不规范数据的产生，提高数据质量。
在Excel中，调用数据验证工具的方法是单击“数据”选项卡下“数据工具”组中的“数据验证”按钮，如图3-1所示。
图3-1 数据验证工具的调用方法
Excel中的数据验证工具在职场办公中的主要作用分为以下3种。
1）让报表智能提示输入信息。
2）禁止用户输入不规范数据。
3）创建下拉菜单，提升输入效率。
为了让读者更深入地理解并应用Excel的数据验证工具，下面结合几个案例进行讲解。
在确保报表数据准确无误及维护数据有效性方面，Excel的数据验证工具发挥着重要作用。以下是一个示例：某玩具店计划在2025年国庆节期间（2025年10月1日至2025年10月8日）举办现场抽奖活动。如图3-2所示，为了防止工作人员在登记奖品发放表时输入无效数据，该店管理人员希望报表能够提供以下两种智能提示。
1）当工作人员将鼠标定位在表格A列区域的单元格时，提示“注意：请输入2025-10-1至2025-10-8内的日期”。
2）当工作人员将鼠标定位在表格C列区域的单元格时，提示“注意：请输入1～999之间的整数”。
想让表格根据工作人员选择的不同位置分别弹出所需的提示文字（见图3-2），可以利用Excel中的数据验证工具实现。
图3-2 让报表智能提示输入信息
1.操作步骤
让报表智能提示输入信息的具体操作步骤如下：选中要设置日期提示信息的区域（如A2:A10单元格区域），调用“数据验证”工具；在“数据验证”对话框中单击“输入信息”选项卡，在下方的输入框中分别输入标题和信息；单击“确定”按钮，如图3-3所示。
C列的发放数量自动提示信息设置方法同理，仅需将最后一步中的提示信息调整为数量的输入范围即可，如图3-4所示。
图3-3 自动提示输入信息的设置步骤
图3-4 设置发放数量的自动提示信息
至此，就完成了奖品发放表不同区域的自动提示输入信息。
2.扩展说明
在本案例中，我们讨论了在两个不同区域输入数据时如何设置自动提示信息。实际上，如果用户有更多区域需要设置自动提示信息，可以按照类似的方法进行操作。具体来说，用户需要分别选中每个区域，然后设置对应的提示信息。
需要注意的是，尽管在表格中设置输入提示信息可以使表格智能地提示用户，但它并不能完全禁止用户输入无效数据。如果用户无视提示并强行输入了无效数据，仍然会导致数据错误。因此，在此基础上，我们还可以继续设置表格中允许输入的数据类型。这样，当发现无效数据时，系统可以强制打断用户的错误输入，并要求其重新输入，从而确保数据的有效性。3.2节将具体讲解如何实现这一功能。
如何禁止用户在报表内输入不规范的数据呢？我们以3.1节的案例为例继续说明。为了保证在输入奖品发放表时的数据准确无误，希望当工作人员输入不符合规范的数据时，Excel能强制打断错误输入让其重试。比如当在A3单元格输入“2025-10-9”时弹出提示框，如图3-5所示；当工作人员单击“重试”按钮时，可以重新输入。
图3-5 强制打断输入错误日期
1.操作步骤
禁止在报表内输入不规范数据的设置方法如下：打开“数据验证”对话框后，将“允许”类型选为“日期”，按照允许输入的日期范围输入“开始日期”和“结束日期”，单击“确定”按钮，如图3-6所示。
用同样的方式设置C列的发放数量，将验证条件设置为1～999之间的整数，如图3-7所示。
图3-6 设置允许输入的日期范围
图3-7 设置发放数量的允许范围
这样就进一步完善了表格的输入限制，能够禁止在表格中输入不规范的数据。用户必须确保输入的数据符合规范，才能完成输入操作。
2.扩展说明
如果要清除单元格中已有的数据验证，只需选中需清除的区域，然后单击图3-7左下角的“全部清除”按钮即可。
上述案例的场景需求仅对日期格式和整数类型的数据进行了设置。在实际工作中，用户可以根据需求设置更多种类的数据验证条件。
如图3-8所示，Excel支持的数据验证条件可以分为以下3种。
图3-8 数据验证条件的允许类型
1）数据类型：可以限制用户输入的数据类型，如数字、文本、日期等。
2）数据范围：可以限制用户输入的数据范围，如最小值、最大值、介于两个值之间等。
3）数据格式：可以限制用户输入的数据格式，如货币、百分比、分数等。
在“数据验证”对话框“允许”下拉列表的最下方有一个选项叫作“自定义”，它允许用户在Excel的数据验证条件中使用Excel函数公式。这一点非常重要，因为它极大地扩展了Excel数据验证功能的使用范围。用户在遇到一些复杂问题时，可以利用自定义公式来解决。为了让读者更深入地理解这一点，并能在实际工作中应用该功能，3.3节将结合实例来讲解如何使用自定义公式进行数据验证。
当用户遇到一些复杂的问题时，例如在确保表格数据唯一性的场景中，需要先检查用户要输入的内容是否在表格中已经存在，然后再执行禁止输入重复值的操作。以某服饰电商企业为例，为了提高客户满意度，该企业承诺提供七天无理由退货；客户在收到商品并试穿后，如果对商品不满意可以退回；工作人员收到退货后，会按照订单号对退回的商品进行检查。如果确认退回的商品不影响二次销售，会在退货登记表中记录商品信息并转交财务部门，如图3-9所示；财务人员将会为该笔订单办理退款。
图3-9 某服饰电商企业退货登记表
为了避免在退货登记表中输入重复的订单号导致重复退款的情况，可以利用Excel的数据验证工具对表格进行设置，以禁止用户在B列中输入重复值。
1.操作步骤
禁止在表格中输入重复值的具体设置步骤如下：单击B列列标，以便选中整个B列；然后依次单击“数据”→“数据工具”→“数据验证”选项，在弹出的“数据验证”对话框中选择“自定义”选项；输入Excel公式后单击“确定”按钮，如图3-10所示。
2.公式解析
在“数据验证”对话框中输入的自定义公式为
=COUNTIF(B:B,B1)=1
该公式的作用是判断B1单元格（用户选定范围时的当前单元格）中的值是否仅在B列中出现一次。如果满足条件，则允许输入；如果不满足条件，则说明输入的订单号存在重复，应禁止输入。
3.扩展说明
COUNTIF函数是工作中常用的Excel统计函数，语法结构为：
COUNTIF（ 统计区域，统计条件 ）
图3-10 禁止输入重复订单号的设置方法
COUNTIF（B:B,B1）的含义是统计B1单元格的值在整个B列中出现的次数。如果该结果为1，则说明输入的订单号是首次出现，与之前登记的订单号不存在重复，满足Excel数据验证条件，应允许用户输入。
本案例旨在帮助读者理解自定义公式在Excel数据验证工具中的扩展应用，更多的Excel函数公式应用会在第4章中详细讲解。
在Excel表格中，如何创建下拉菜单进行自助输入呢？来看下面的示例，某生鲜水果店为了准确核对库存和计算成本，每天都需要记录商品损耗表，如图3-11所示。在这个表格中，工作人员会登记因水分流失和分拣作业导致的商品损耗数量。
图3-11 某水果店的商品损耗表
由于店内的商品种类繁多，为了提高工作效率，减少输入错误，工作人员希望在输入数据时能够使用下拉菜单来选择商品，从而实现自助输入。
1.操作步骤
在表格中创建下拉菜单的操作步骤如下：选中需要创建下拉菜单的区域（如整个B列），调用Excel数据验证工具，在“数据验证”对话框中的验证条件“允许”列表中选择“序列”，在“来源”输入框中输入“砂糖橘，芦柑，脐橙”，如图3-12所示。
图3-12 设置下拉菜单列表来源
设置完成后，用户选中B列单元格（如B5单元格）时，单元格右侧会出现下拉按钮。单击按钮即可展开下拉菜单，如图3-13所示。
图3-13 设置好的下拉菜单列表样式
下拉菜单列表中的选项可以根据需要添加或编辑。仅需在数据验证条件的序列来源中添加选项，用英文逗号分隔即可，如图3-14所示。
图3-14 在下拉列表中添加选项
将序列来源修改为“砂糖橘，芦柑，脐橙，金橘”（务必用英文逗号间隔，使用中文逗号会报错）之后，下拉菜单列表中会增加“金橘”选项。
2.扩展说明
在实际工作中，如果下拉菜单需要设置的选项较多，且添加新选项较为频繁，可以采用自动扩展式下拉菜单。这种下拉菜单将各选项所在区域定义为表，并在序列来源中引用该表区域，利用表区域的自动扩展功能来实现下拉菜单的自动扩展。
自动扩展式下拉菜单的设置方法为：在H1:H3单元格区域输入下拉菜单列表中需要显示的选项（如砂糖橘、芦柑、脐橙），然后单击“插入”选项卡下的“表格”按钮，在弹出的“创建表”对话框中保持自动输入的来源，单击“确定”按钮，如图3-15所示。
设置完成后，定义好的“表1”可以在“公式”选项卡下的“名称管理器”中查看其引用范围，如图3-16所示。
由于“表1”具备自动扩展的特性，当在其引用位置下方连续输入数据时，表的引用范围即可同步自动扩展。因此，只需将序列来源设置为“表1”的引用区域，就可以将表的自动扩展特性赋予下拉菜单，具体操作步骤如下：选中需要创建下拉菜单的单元格区域（如B2:B9单元格），调用Excel数据验证工具，在序列来源中输入“=$H$2:$H$4”，如图3-17所示。
图3-15 将下拉列表选项所在区域创建为表
图3-16 在“名称管理器”中查看“表1”的引用范围
图3-17 将表区域设置为序列来源
这样就完成了自动扩展式下拉菜单的设置。当在H5单元格中输入“金橘”时，下拉菜单列表中会同步增加“金橘”选项；当在H6单元格中输入“丑橘”时，下拉菜单列表中也会同步增加“丑橘”选项，如图3-18所示。
图3-18 添加选项后下拉菜单自动扩展
在不需要更改下拉菜单列表选项时，可以将H列隐藏。这样做不会影响已设置好的下拉菜单，同时还能保持表格界面整洁，并起到保护下拉菜单序列来源的作用。
除了创建一级下拉菜单，还可以使用Excel的数据验证功能创建二级下拉菜单（也称为级联下拉菜单）。这种功能可以帮助用户在需要根据上一层菜单的选择来限制下一层菜单选项的情况下智能输入，特别适用于数据输入时需要遵循逻辑顺序或层级关系的场景。在3.5节中，我们将结合案例具体讲解如何创建二级下拉菜单。
如何创建二级下拉菜单呢？让我们来看一个示例：为了快速提升销量，某家具品牌积极参加全国各地的家装博览会。通过这些大型一站式营销采购平台，该品牌成功签订了数单大额合同。合同签订后，品牌指定专人记录合同信息，并进行持续跟进。图3-19展示了该品牌的合同记录表（A:D列）以及已签约的省市（G:J列）。
图3-19 某家具品牌的合同记录表及已签约省市
由于涉及的省市较多，且在开展期间合同信息会持续增加，为了提高输入效率，避免错误，该家具品牌希望在表格的B:C列设置二级下拉菜单。这样，当工作人员在B列选择某个已参展的省份时，C列的下拉菜单会自动显示该省份对应的城市列表。这种智能输入方式将大大提高数据录入的准确性和效率。
这个案例中的问题可以利用Excel数据验证+自定义名称+INDIRECT函数的组合方案来解决，其中包含以下3个关键点。
1）按照省市所在区域为其创建相应的自定义名称。
2）将一级下拉菜单的序列来源设置为已参展省份的名称。
3）将二级下拉菜单的序列来源设置为INDIRECT函数引用的省份，以自动引用该省份所对应的城市。
1.操作步骤
明确思路和关键点后，创建下拉菜单的具体操作步骤如下。
1）选中已签约的省市（如G1:J6单元格区域），按“Ctrl+G”组合键或F5功能键弹出“定位”对话框；单击“定位条件”按钮，打开“定位条件”对话框；单击“常量”复选框，再单击“确定”按钮，如图3-20所示。这一步的目的是批量选中包含已签约省市的单元格（不包含空单元格）。
图3-20 选中已签约省市
2）单击“公式”选项卡，选择“根据所选内容创建”；在弹出的对话框中确保“首行”复选框被勾选，然后单击“确定”按钮，如图3-21所示。
图3-21 按照省市所在区域创建相应的自定义名称
这步操作的作用是将G1:J1（首行位置）的省名称定义为对应的区域名称。
①名称“省”将引用G2:G4单元格区域。
②名称“广东”将引用I2:I6单元格区域。
③名称“江苏”将引用H2:H5单元格区域。
④名称“浙江”将引用J2:J4单元格区域。
在“公式”选项卡下的“名称管理器”中可以查看这些定义好的名称，并检查它们的引用位置是否正确，如图3-22所示。当进行这类多步操作时，应该及时检查关键步骤的达成效果。这是数据处理工作中应该养成的良好工作习惯，可以及时纠错并避免时间和算力损失。
图3-22 在“名称管理器”中检查已定义名称的引用位置
3）省名称的引用位置检查无误后，就可以开始创建下拉菜单，并将下拉菜单的序列来源设置为省名称，以便自动关联该省所对应的城市列表。
方法为：选中需要填写省份的区域（如B2:B6单元格区域），单击“数据”选项卡，选择“数据验证”选项，打开“数据验证”对话框，将“允许”条件设置为“序列”，在“来源”输入框中输入“=省”，然后单击“确定”按钮，如图3-23所示。
这时可以检查一下设置好的一级下拉菜单是否能够达到想要的效果。选中B5单元格，单击其右侧的下拉菜单，可以发现下拉列表中自动出现了省份选项列表，如图3-24所示。
图3-23 设置一级下拉菜单的序列来源
图3-24 检查一级下拉菜单的省份选项列表
4）下面继续设置二级下拉菜单的城市序列来源，方法为：选中需要选择城市的区域（如C2:C6单元格区域），单击“数据”选项卡，选择“数据验证”选项，打开“数据验证”对话框，将“允许”条件设置为“序列”，在“来源”输入框中输入“=INDIRECT(B2)”，然后单击“确定”按钮，如图3-25所示。
2.公式解析
图3-25中出现的公式为：
=INDIRECT(B2)
图3-25 设置二级下拉菜单的城市序列来源
这些常用函数在第4章会详细讲解，这里简单解析一下该公式的原理。INDIRECT函数的引用位置是由选中的区域（C2:C6）决定的。之所以用B2，是因为当前的活动单元格为C2（可以通过编辑栏左侧的名称框确认），而在C2中输入的市应该归属于B2单元格所在的省。INDIRECT（B2）的作用就是根据B2的省份（江苏）引用该省份对应的城市列表区域（H2:H5）。这些省市对应关系可以在图3-22的名称管理器中查看。
设置好二级下拉菜单后，再次检查设置效果。先在B列中选择省份（如选择江苏），再在C列点开二级下拉菜单，查看列表显示的是否为归属江苏省的城市列表，如图3-26所示。
这里的操作应该遵循先选省份后选城市的业务逻辑关系。如果用户未在B列中选择省份（B5为空），而直接单击C列的二级下拉菜单按钮，是无法展开下拉菜单列表的。
在B6单元格更换省份的选择（如选择广东）后，在C6单元格单击下拉菜单按钮，即可看到下拉菜单列表已经自动切换至归属广东省的城市列表了，如图3-27所示。
图3-26 选择江苏省后自动显示归属江苏省的城市
图3-27 选择广东省后自动显示归属广东省的城市
至此，就完成了二级下拉菜单的全部设置。现在，Excel能够根据用户选择的省份自动展示该省份对应的城市列表。
3.扩展说明
在上述案例中，随着该品牌业务的发展，其省份下的城市选项将不断增加，并且随着在更多省市参加家装博览会，签约省市的数量也会相应增加。这种业务增长对二级下拉菜单提出了自助式扩展的需求，即当输入新的省市数据时，这些数据能被已定义好的名称自动纳入其引用范围。
在创建单级自助式下拉菜单时，我们利用表区域的自动扩展特性来实现下拉菜单序列来源的动态扩展。同时，为了使已定义好的名称引用也能自动扩展，我们可以将这些名称引用的区域同样创建为表区域。
具体方法为：先选中省份区域（如G1:G4单元格区域），按“Ctrl+T”组合键或单击“插入”选项卡下的“表格”按钮，在弹出的“创建表”对话框中，勾选“表包含标题”复选框，然后单击“确定”按钮，如图3-28所示。
图3-28 将省份区域转换为表区域
将省份区域创建为表区域后，省份应该具备自动扩展功能了。下面检查达成效果：在G5单元格中输入“山东”，在B6单元格中单击下拉菜单按钮，可以看到下拉菜单列表选项中已经自动添加了“山东”，如图3-29所示。
图3-29 检查省份区域是否已可以自动扩展
接着用同样的方法将其他省份的名称（如江苏、广东、浙江）也创建为表区域，操作步骤同前，此处不再赘述。
将省份名称创建为表区域后，可以在“名称管理器”中及时检查表区域的引用位置是否正确，如图3-30所示。
现在，我们已经成功实现了自助扩展式二级下拉菜单的功能。当用户添加新的省市选项时，一级和二级下拉菜单的列表都能够自动扩展。
图3-30 在“名称管理器”中查看表区域的引用位置
在实际工作中，数据分析和报告编制前的数据初步清理是至关重要的环节，它可以帮助工作人员及时发现并更正错误，确保数据的准确性和有效性。
Excel数据验证工具中的“圈释无效数据”功能正是基于此需求应运而生的。下面结合一个案例演示批量圈释无效数据的方法。某企业为了统计2024年的销售数据，从系统中导出了订单表（此处仅展示部分），如图3-31所示。现在需要将订单表中的无效数据批量圈释出来。
图3-31 某企业2024年的订单表（部分）
在圈释无效数据之前，首先要将无效数据的定义标准告知Excel。这就需要根据业务逻辑和要求，先在Excel数据验证中建立条件约束，比如对A列的日期要求是2024年内，对D列的数量要求是正整数，如图3-32所示。
图3-32 根据业务逻辑设置数据验证条件
有了数据验证标准和条件约束，调用Excel数据验证工具中的“圈释无效数据”命令，就可以用红色椭圆批量圈释无效数据了，如图3-33所示。
图3-33 用红色椭圆批量圈释无效数据（见彩插）
当数据修改至符合数据验证标准和条件约束后，红色椭圆会自动消失。当不再需要突出显示时，用户也可以通过图3-33中的“清除验证标识圈”按钮来撤销圈释。