Excel数据有效性-二级、三级联动菜单(普通版)（二）
数据有效性通过限制单元格的录入内容，从而提高数据录入的准确性，且能降低数据录入的便捷性和数据校验工作。
在早些版本中，数据有效性在数据菜单下，叫数据有效性。而在Excel2016版在数据菜单下叫“数据验证”,本次演示使用的是Excel2016，依然采用了“数据有效性”的叫法，虽然不够规范，但方便大家理解。
如果有条件，建议大家使用最新的版本，其功能相对更加齐全，相对于低版本能更加方便的操作我们需要的内容，不仅仅是本次教程所用，而在于整体会对你的学习状态会有一个明显的提升。
设置二级、三级联动菜单（二）
联动菜单也分为两个版本：普通版和进阶版
普通版：
也就是我们网络上常见的一些做联动菜单的方法，其菜单和子项都比较少，可以手工对源数据进行分组归类也可也使用其他方法。本篇文章介绍的就是普通版。
进阶版：
进阶版将对3000多条数据进行分组归类，并做演示，由于数量级比较大，部分方法和需要注意的问题与普通版不太一样，适合对该问题有深度需求的用户。
本次为了更加真实的展示二级、三级联动菜单的效果，从网上下载了省级行政划分结构数据，共计3130条数据，如果3000条数据我们都能搞定，那么再多的分类再多的子项我们都不要发愁了。其四级、五级乃至更多的联动，方法都是一样的。下载演示文件-简书私信回复3获取下载密码
调整数据分组（选学）
该章节为拓展学习，如果你的联动菜单和子项比较少，可以跳过该章节，直接进入主题部分。点击查看详细操作步骤，提供了两种分组的方法，一个是我自己总结的方法，通过数据透视加上一些操作实现的，另一种是通过Power Query实现的，该方法参考了施阳的博客。
我们拿到的是左侧的数据，但是必须转换为右侧的分组才能进行区域名称和位置的定义，所以我们需要对数据进行转换。对于普通版知道两个方法即可。1、手工调解，如果数据不多可以手工进行设置；2、通过大鹏老师教你的利用数据透视进行转换的方法；其他还有通过数组公式和Power Query两种，甚至VBA，但是对于普通用户而言可能稍难，这些方法会在进阶篇进行讲解。
也就是说我们需要把省份归类到一起，同时在把每个省份作为标题，将对应的城市放在对应省份的下面，最后再把所有的城市作为标题，将对应的区县放在对应的城市下面，如果有更多级，依此类推，并分别对名称和区域进行定义后才能使用。
下面是效果的参考标准。每一个一级的子项都是二级的标题、二级的子项是三级的标题。这里我们需要注意一个问题，就是作为标题的名称，不能有重复，无论是哪一级，否者你无法对名称进行合理的指定。在最末级中，其不同仔项之间是可以重复的。
由于我拿到的源数据存在重复的问题，所以我对名称少做了调整。比如北京市和天津市的二级分类都有叫“市辖区”，如果我把市辖区作为标题在对对应的区县进行归类时，标题就会重复。我实际已经对源数据做了调整，用以避免掉这个问题。
效果.png
省份分组设置
将省份列复制出来后去重即可得到省份的分组。
省份去重得到分组.png
城市分组设置
-
该步骤稍微麻烦一些，可以通过数据透视再加上一些步骤可以得到我们想要的格式。首先我们需要透视该源数据，可以指定位置到另一个新的工作表当中。
数据透视.png
-
将省份放入列、市放入行、将市放入值并计数。
调整字段位置.png
- 点击设计菜单-选择总计类型为-对行和列禁用，请注意，此时只有把活动单元格选择为数据透视表中的位置，才会看到设计菜单。
取消总计.png
-
将数据区域复制出来，找一个表格粘贴为值，并选中灰色区域。
复制粘贴为值.png
- 使用快捷键Ctrl+G选择定位条件或者开始菜单-查找和选择-定位条件，打开定位对话框，选择常量并确定。
图片.png
图片.png
- 直接输入公式“=$a2”，在公式编辑状态下按，Ctrl+Enter(回车)批量填充，即可将省份下面填充对应的城市。
图片.png
图片.png
-
全选数据，原地粘贴为值，并删除A列
图片.png
- 再次条件定位，选择空值并确定，将选中的部分删除且下方单元格上移，并将处理的结果粘贴到对应的区域。
图片.png
图片.png
图片.png
图片.png
图片.png
区县分组设置
方法等同于对城市分组的设置，唯一不同的是，在数据透视时，将市放入列，县放入行，对县计数，其他步骤均一样。
图片.png
图片.png
最终效果
定义名称及区域
- 定义名称及范围我们可以使用公式菜单下的-定义的名称组中的名称管理器，但使用名称管理器进行定义，只能手工一个一个加，无法做到批量完成。所以该方法我不再做详细的讲解，仅做了解即可，而名称管理器的功能不限于此，还有其他用法，这里不再累赘。
我们使用以下两种方式：
1、快捷键Ctrl+Shift+F3
2、公式菜单-定义的名称-根据所选内容创建
两种方法其效果是等价的，都可以打开下面的窗口
图片.png
根据所选内容创建/Ctrl+Shift+F3.png
-
选中数据区域-并指定首行
图片.png
-
在定义城市部分的时候，为了增加效率和准确性，我们使用定位功能，定位常量，只选中我们需要定义的范围。
图片.png
图片.png
-
对县级的设置也是一样。
图片.png
-
但是有时候会报错，原因参见点击查看详细原因
图片.png
以上我们操作完了对数据区域名称的指定和区域的指定。在公式菜单-名称管理器，可以点击查看我们刚刚定义完的名称和区域，若后续有新增的项目，则需要在名称管理器中对应的分类下，修改应用的范围。
图片.png
图片.png
开始设置联动菜单
- 先设置省份-打开数据菜单-数据有效性/数据验证-选择序列-在数据来源数据“=省级名称”指定的名称一定要和我们分组时指定的名称一致，否者无法返回正确的结果。确定以后点击单元格右侧的按钮就可以选择我们需要的省份了。所以建议在设置城市的时候，先随便选择一个省份，在设置区县的时候也需要随便先选择一个城市。
图片.png
图片.png
-
我们再来设置城市，选中城市下面的单元格-同样打开数据有效性，输入下面的内容。
图片.png
图片.png
- 这一步设置有时候会报错，原因就是你在设置城市的时候，省份没有选择任何值，但是你直接点击是以后，你再回去先选好省份，那么城市这部分也就正常了。建议先随便选择一个上级的内容在对下一级进行操作。
图片.png
-
区县的设置等同于城市。
图片.png
至此就设置完毕了。
实现原理
其实现原理需要你对名称管理器和INDIRECT函数熟悉，不做太多展开。简单来说说，INDIRECT函数可以根据你提供的单元格地址，比如INDIRECT("A2")，则结果会返回A2单元格的内容，表面上看和直接“=A2”是一样的效果，但其实现的原理有差别，而且INDIRECT的功能不仅如此，后续有需要再给大家详细讲解。而关于我们在写省一级的时候，选择序列后直接写了=省级名称,因为名称管理器定义过的我们都可以直接拿来使用，包括我们可以定义一些常用的公式或者特定的内容，也不再展开。我们在定义二级、三级的时候使用了INDIRECT(J12)，其返回的结果是根据我们选择的省份，返回不同省份对应的城市，而这些关系我们在名称管理器中已经定义过了。如果你没有理解也没有关系，只要能做出来实现我们的目的暂时就足够了，以后随着学习的不断深入，你会慢慢理解的。
总结：
个人觉得，联动菜单的设置有几个关键点需要注意。
1、分组一定要明确，作为标题的名称不能有重复的；
2、如果你的菜单和子项比较多，那么需要借助一些方法去实现；
3、在我们指定名称的时候有时候会报错，（请确保粘贴和复制区域不要重叠），原因是由于Excel本身引起的，我们也可以通过一些方法避免掉。
4、在制作联动菜单时，有时候会提示源错误，那是因为你指定的上级的单元格没有内容，建议先随便选择一个内容后再做后面的操作。
5、学会联动菜单很重要，根据自己的能力选择合适自己的教程效果最好。
