本文转载自公众号:Excel之家ExcelHome，作者:爱上深呼吸李强
二级下拉菜单可以很好地规范数据录入，也可以用作动态交互式图表的控件，今天就跟大家分享三种二级下拉菜单制作方法。
01
—
普通青年
第一步：整理A:B列省市类别为D:G列格式，便于后期定义名称。
第二步：定义首行省份序列为名称'省份'，用于数据有效性引用。
第三步：定义城市名称-通过首行省份名批量定义，用于数据有效性中引用。这里面用到快捷键F5-定位常量，可以过滤掉无数据的区域。然后'根据所选内容创建'，可批量定义城市序列名称。
第四步：分别为省市对应单元格设置数据有效性，这里设置省份一级菜单时，有效性条件选择序列，来源=省份（之前定义的名称）即可。设置城市二级下拉菜单数据有效性时用到了indirect函数（关于该函数使用方法，请仔细查看F1函数帮助）。按下图操作，一个省市二级下拉菜单就做好啦。
02 —
文艺青年
文艺青年的做法与普通青年原理上和方式上大体相同，那么是如何展示文艺范的呢？
Step1：对省份列表按照升序或者降序排列（目的是让包含相同内容的省份彼此相邻）
Step2：对省份的列表进行了一点加工，定义了如下名称：
在定义省份序列的名称时，用到的高频函数简单解释下：
offset函数根据行列偏移以及行列数，生成动态引用区域；
match函数确认已输入字符的在省份行中的起始位置；
countif用于统计包含已输入内容的项目个数。
&'*'用于模糊匹配
通过定义名称，会为数据有效性引用的序列重新设定一个省份范围。
Step3：与普通青年相类似，在进行省份一级菜单制作时，在D18单元格的数据有效性设置对话框中，直接输入已经定义好的名称'=文艺青年省份”即可。
Step4：取消出错警告中'输入无效数据时显示出错警告'的复选框。
Step5：城市二级下拉菜单制作方法与普通青年的第四步骤一致，也是在数据有效性中借助indirect函数，'允许'设置为序列，来源=INDIRECT($D$18)
效果如下：
这种类似于联想式下拉菜单的方法，避免了一级菜单中几十个省份的冗长名单，不便于选择。只需输入'广'，下拉菜单中便只有'广东'和'广西'；若输入'湖'，则下拉菜单中则只有'湖南'和'湖北'，非常方便和智能。
注：1）对于一级菜单，需要满足排序后可包含相同关键字的内容要相邻，这是其最大的局限性。2）如果不是省市的使用场景，而是包含相同关键字的内容有多个时，这种方式优势会很明显。
03
—
IT青年
IT青年可以通过VBA编程，直接用省市列表借助字典功能，实现省市二级下拉的效果。按住Ctrl F3查看已定义的名称时，也不会在名称列表中出现几十个被定义的名称，以至于看起来非常混乱。就像下面这样：
此外，可以避开繁琐的基础操作，省去前期数据整理的大量工作，不必再将数据整理成如下格式，保持省份和城市两列即可。
通过以下代码，可以取得与普通青年完全相同的省市二级下拉菜单效果。
划重点：以下代码需要与要定义的省市二级下拉菜单位于同一个工作表中，并且代码写好后需要保存工作簿后重新打开，这两个工作表事件才能正常运行，不知是否又是微软的Bug。
Private Sub Worksheet_Activate() Application.ScreenUpdating = False '暂停刷新屏幕 Application.EnableEvents = False '禁用事件 Dim s As Object arr = Sheets('省市列表-IT青年').Range('A1').CurrentRegion If UBound(arr) < 2 Then MsgBox '数据库中没有数据可供检索！', vbInformation: Exit Sub Set s = CreateObject('Scripting.Dictionary') For I = 2 To UBound(arr) If Not s.Exists(arr(I, 1)) Then s(arr(I, 1)) = '' End If Next With Range('D30').Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(s.keys, ',') On Error Resume Next End With Set s = Nothing Application.EnableEvents = True Application.ScreenUpdating = True '恢复刷新屏幕End Sub
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False '暂停刷新屏幕 Application.EnableEvents = False Dim brr(), n As Integer If Target.Address = '$D$30' Then '选择省份 arr = Sheets('省市列表-IT青年').Range('A1').CurrentRegion Range('E30') = '' For I = 2 To UBound(arr) If arr(I, 1) = Target Then n = n 1: ReDim Preserve brr(n) brr(n) = arr(I, 2) End If Next With Range('E30').Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(brr, ',') On Error Resume Next End With End If Application.EnableEvents = True '启用事件 Application.ScreenUpdating = True '恢复刷新屏幕End Sub
这里省份一级菜单用到了Worksheet_Activate事件，城市二级下拉菜单用到了Worksheet_Change事件。
其中D30和E30单元格分别为省市下拉菜单所在位置，Range('A1').CurrentRegion代表与A1单元格相连的区域，也即被空行和空列隔开的单元格区域。这些均可以根据实际情况按需修改，其他代码部分其实并没有多大必要逐字逐句地去理解和记忆，拿来主义为我所用就好。
怎么样，小伙伴们学会了吗？你觉得自己是哪种青年呢？
联系客服
