【Excel技能大升级】告别手动输入！一招教你打造智能联动下拉菜单，效率翻倍！

本节我们继续介绍一个更为高端的技能，二级数据验证。

想象一下，当你在一级菜单选择一个项目时，二级菜单自动更新与之相关的选项——这就是动态数据验证的魅力所在。它不仅能够减少错误和重复工作，还能让你的数据表格看起来更加专业。

例如在【P2】单元格中选择【安徽省】，在【Q2】单元格下拉选项中就会提供“安徽省”的二级城市:安庆市、安庆市、亳州市等等。

但很多人却对这些进阶技巧知之甚少，甚至在遇到数据录入问题时束手无策。今天，我们就来揭开动态数据验证的神秘面纱，教你如何利用Excel的这一强大功能，让你的数据录入变得轻松又高效。

让我们一起探索如何设置动态下拉列表，以及如何通过二级数据验证实现数据的智能联动，提升你的工作效率，让你的数据管理更加得心应手！

📝 如何设置双重数据有效性

步骤1：设置省份的一级数据有效性

• 选中【P2】单元格，选择【数据】选项卡，点击【数据验证】。
• 在【允许】中选择【序列】，在【来源】中引用省份标题即【B1:M1】单元格区域，单击【确定】按钮。

这时下拉选项中就有了与第2行一样的省份下拉选项。

步骤2：给省份下的城市定义名称

接着给每个省份下的城市分别定义一个名称，例如安徽省下的城市命名为“安徽省”，福建省下面的城市命名为“福建省”等等。

选中【B2:B18】单元格区域，在名称框中输入“安徽省”，按<Enter>键确认。这样就给【B2:B18】单元格区域定义了一个名称叫“安徽省”。

此时可以通过在名称管理器中选择“安徽省”这个名字快速将这个区域的城市选中。

但是如果一个一个的去设置这些名称会特别慢，没有快捷的办法呢？

可以使用【公式】下的【名称管理器】来批量创建名称，所有关于名称的选择和设置都在这个位置。

选择【B1:M21】单元格区域，单击【公式】选项卡下的【根据所选内容创建】按钮，在弹出来的对话框中勾选【首行】复选框。

这里我们不需要【最左列】，因为我们只需要根据顶部单元格进行创建它的名称就可以了，所以将【最左列】复选框取消勾选，最后单击【确定】按钮。

在此时表格没有任何的变化，但是我们单击【公式】下的【名称管理器】按钮，在弹出来的【名称管理器】对话框中就会看到每个省份的名称。

单击【名称框】下拉按钮，选择“安徽省”，就会自动选中“安徽省”下面列表作为它的一个数据源的区域。

步骤3：用indirect函数，将城市关联到二级有效性当中

下面再来设置对应城市的二级联动数据有效性。

选中【Q2:Q9】单元格区域→单击【数据】选项卡下的【数据验证】按钮→在弹出来的对话框中选择【序列】选项。

接着重点来了，在这里填写的参数是“序列名称”，而序列名称是变化的。

那么我们就要引用一个函数：inderict，它的用法是将括号当中的参数转成为一个数据区域，将字符串返回到序列。

在【来源】中输入：“=indirect()”，其中函数和公式中填写的标点符号一定是英文状态下的。

现在这个序列的名称是根据【P】列所对应的值来确定的，所以函数的参数直接选中【P2】单元格，但是这里我们引用单元格是随着行号的变化而逐渐变化，所以需要将行号“2”前面的美元符号删除，取消绝对引用，这样它的序列【来源】就是：

= inderict($P2)

这时在【P2】单元格中选择“福建省”，在【Q2】单元格中就会显示出“福建省”对应的城市名称。

但是还有一个问题，当选择了“山西省”，在下拉选项中会有很多的空白区域，这怎么办？

其实他是我们预留出的空间，当在下面追加一个“1”，在这个下拉的区域也会跟着联动去增加“1”，所以它不仅没有影响，反而可以可以提供一个额外增量的区域。

但是是不是有的小伙伴说这样的样式非常难看呢？有没有什么办法可以自动扩充单元格区域，不需要事先预留？

肯定是有的，我们要请出一个神器-超级表，利用超级表做动态的数据验证。

🔧 动态的双重数据验证

步骤1：参数表套用表格格式，命名超级表

想要实现动态的数据验证，无可厚非就就要使用超级表，将省份和城市分别套用表格格式，并命名超级表。

接着给这个超级表明个名字。单击超级表中任意有字单元格，在【表设计】选项卡下的【表名称】文本框中输入：“省份”，按回车键确认。

接着将“城市”也套用表格格式，选中城市表格中任意单元格，单击【开始】选项卡下【套用表格格式】按钮，选择一个样式。

同理，给这个超级表也命个名字。单击超级表中任意有字单元格，在【表设计】选项卡下的【表名称】文本框中输入：“城市”，按回车键确认。

步骤2：一级数据验证

接下来对省份进行数据验证，使用INDIRECT函数引用超级表中的省份字段。

首先对【省份】做数据验证，选中【Q2:Q6】单元格区域，选择【数据】选项卡→单击【数据验证】按钮→在弹出来的对话框中选择【序列】选项。在【来源】中我们要呼吁出一个indirect函数，indirect函数的编写方式是：=indirect("表名[字段名]")。

所以根据函数的编写要求，找到引用区域的表名称为“省份”，字段名称也就是【A1】单元格的名称，即“省份”。带入公式就是：=INDIRECT("省份[省份]")，然后单击【确定】按钮。

这时一级数据有效性就设置完成了，并且由于“省份”表格是一个可以自动扩充的超级表，所以向下追加参数时，不仅在【Q】列的下拉选项中会同步的更新进来，而且还不会出现前面空白选项的情况。

步骤3：indirect二级数据验证

继续设置城市单元格的数据验证，动态引用省份选择，实现联动。

选中【P2:P6】单元格区域，选择【数据】选项卡，单击【数据验证】按钮，在弹出来的对话框中选择【序列】。在【来源】中利用indirect函数动态引用下序列区域。将indirect函数的编写方式：=indirect("表名[字段名]")带入到这里。这里名称为“城市”，字段名称为【O】列单元格的序列名称。并且对于单元格地址转化为文本，要在其两端加上英文状态下的双引号，并且单元格地址两端还要加上“&”。带入公式就是：=indirect("城市["&$Q2&"]")。

这时就设置好了“城市”的数据验证。并且因为这张表是超级表，下拉选项就会随着名称的扩充而扩充。

这时动态数据源就可以被引用过来了，当选择不同的“省份”时，“城市”名称也随着联动更新。记住一下它的语法，=indirect("表名[字段名]")。

📝动态数据验证的魅力：探索了二级数据验证的高级技巧，体验了在选择一级菜单后，二级菜单自动更新相关选项的动态数据验证功能，提升了数据表格的专业性和工作效率。在这里我们利用超级表实现动态的数据验证，通过命名超级表和使用INDIRECT函数，实现了省份和城市之间的智能联动。

【写在最后】

🌟 【Excel双重数据有效性：动态联动，效率倍增】

• 🚀 设置省份的一级数据有效性：学习了如何为省份设置一级数据有效性，通过引用省份标题单元格区域，实现了省份的下拉选择功能。
• 🏷️ 给省份下的城市定义名称：掌握了如何为每个省份下的城市定义名称，以便在数据验证中引用，实现了城市名称的快速选取。
• 🔧 使用INDIRECT函数实现二级有效性：了解了如何使用INDIRECT函数将城市关联到二级有效性中，实现了基于省份选择的城市名称的动态更新。

🌈 本节总结 双重数据有效性在许多场景都可以用到，并且可以结合函数进行使用，灵活用出多种用途。这不仅提升了你的工作效率，还让你的数据管理更加得心应手！

🔥 下期预告：在下一节课中，我们将继续深入探讨Excel的高级功能，让你的数据管理技能更上一层楼。敬请期待！

👋 感谢聆听：感谢大家的认真聆听，我们下节课再见！继续带你探索Excel的奥秘，让工作变得更加轻松愉快。

📅 记得关注我们：不错过每一次的Excel技巧分享，让我们一起成为数据处理的高手！