Excel 实现二级下拉列表效果

首先感谢阿辉提供的应用场景和原始素材，又涨知识了。

用 Excel 填表时，遇到地址（省、市、区、街道……）或多级类目选择（父类、子类、子类的子类……）时，通常希望 Excel 能根据选中的类别，自动列出该类下的所有子类项；选定第二级子类后，再自动提示二级子类的所有子类项，以此类推。网上大致查了一下，大致有两种思路，一种是用 OFFSET+MATCH 组合，另一种是用 INDIRECT。前一种比较复杂，这里只介绍简单的 INDIRECT 及类别为全英文时的处理方法。

1 实现原理

INDIRECT 函数：

语法：INDIRECT(ref_text, [a1])

作用：将单元格的值 ref_text 作为单元格地址，间接地、迂回（函数名的由来）得到新地址的值。

2 中文版处理

1. 创建类别数据源：罗列出所有类别及子类别（示例为横向，也可以纵向）；

   

2. 批量创建名称：F5 定位整个类别区域后，组合键 Ctrl + Shift + F3 批量命名各个子类区域（纵向则勾选 首行）；

   

3. 建立第一级下拉：【数据】–【数据验证】–设置验证条件为“序列”–输入第一级类别的区域名称（如 cat_lv1）：
   

4. 建立第二级下拉：利用 INDIRECT 函数，将第一级下拉单元格的值作为参数传入，得到一级名称对应的子项单元格区域：
   

5. 验证效果：
   

3 英文版处理

由于中文可以直接作为区域名称使用，配合 INDIRECT 将十分方便。但在英文条件下，一级类别的文本很可能是几个用空格间隔的单词，直接作为区域引用是无效的。需要处理成 Excel 可以识别的名称才行。Excel 在新建名称时，会默认将空格替换成下划线，具体来说，就是把 first category of sth 处理成 first_category_of_sth 的形式，后者就是有效名称了。利用 Excel 内置函数 SUBSTITUTE 可以实现等效替换——

1. 创建数据源、批量创建名称、建立第一级下拉：（同上，略）

2. 建立二级下拉：（INDIRECT + SUBSTITUTE）
   【来源】中的公式：INDIRECT(SUBSTITUTE(A12," ","_"))

3. 验证效果：
   

4 小结与拓展

1. Excel 区域名称不能包含 空格；
2. 动态下拉列表的关键，在于得到本级列表的区域名称。示例借助了 SUBSTITUTE 函数，实际情况可灵活调整。

5 示例文件

链接：https://pan.baidu.com/s/1feE5Xfsu23odudFICY644Q
提取码：ole8