Excel: 设置动态的二级下拉菜单

文章背景： 在进行数据录入时，为了提高录入效率和规范用户操作，会使用数据验证功能（Data Validation），得到一级菜单和二级菜单，供用户选择输入。本文要讲述的是如何通过offset、match和counta函数，得到动态的二级下拉菜单。

1 示例信息

2 一级菜单设置

3 二级菜单设置

4 几点补充

1 示例信息

假设有一份人员名单，总共有三列，姓名，省份和城市。省份列，可以设置一级菜单；城市列，希望根据输入的省份，自动产生相应的城市供用户输入，这个可以通过设置二级菜单来实现。

基于给定的参数表（参数表内的数据后期可以动态增加），下面就来介绍一级菜单和二级菜单的设置。

2 一级菜单设置

（1） 定义单元格名称

省份：OFFSET(参数表!A2,0,0,COUNTA(参数表!A:A)-1,1)

通过函数offset产生动态的一级下拉菜单，好处是后期如果要添加新的省份名称，那么单元格名称省份的内容也会动态更新。

（2）选中需要的单元格区域（C3:C13），设置一级菜单。

3 二级菜单设置

选中需要的单元格区域（D3:D13），进行数据验证的设置，其中，在Source项，输入的内容如下：OFFSET(参数表!B2,0,MATCH(C3,省份,0),COUNTA(OFFSET(参数表!B2,0,MATCH(C3,省份,0),1000,1)),1)

(1) 上面的数据源中，用到了两个OFFSET函数，这样做的好处是，后期如果添加了新的城市，二级菜单的内容也会动态更新。

（2）上面数据源的公式，可以拆分成两块，OFFSET(参数表!B2,0,MATCH(C3,省份,0),COUNTA(Counter),1)，其中，Counter = OFFSET(参数表!B2,0,MATCH(C3,省份,0),1000,1)。

注意：Counter中的1000只是随意设置的大数，是为了确保能够满足动态添加的需要。如果数据源输入的内容是Counter，则下拉菜单中会出现很多空白项。因此，这里通过COUNTA(Counter)，来获得对应城市的可选项个数，再通过OFFSET(参数表!B2,0,MATCH(C3,省份,0),COUNTA(Counter),1)，获得对应城市的可选项信息。

视频演示： http://mpvideo.qpic.cn/0bc3reac4aaanuakt3z2ijrfbcodf2eqalqa.f10002.mp4?dis_k=5c35a25c0fb08cf119e063c0b93e3250&dis_t=1663655114&vid=wxv_2322147442512789505&format_id=10002&support_redirect=0&mmversion=false

4 几点补充

（1）offset函数

返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。

OFFSET(reference, rows, cols, [height], [width])

(2) match函数

使用 MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

MATCH(lookup_value, lookup_array, [match_type])

match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array中的值匹配。此参数的默认值为 1。

match_type参数的值为0，则查找MATCH 查找完全等于 lookup_value 的第一个值。

(3) COUNTA函数

COUNTA 函数计算范围中不为空的单元格的个数。

(4) INDEX函数

INDEX 函数返回表格或区域中的值或值的引用。

在进行二级菜单的设置时，发现网上有不少文章提到，可以通过INDEX 函数来实现。如果每次二级菜单引用的单元格区域是固定的，那么确实可以通过INDEX 函数来实现。但是本文设置的单元格名称省份，里面用到了函数OFFSET和函数COUNTA。因此，省份属于动态单元格名称(dynamic named ranges)，通过INDEX函数引用时会报错。所以，本文没有采用INDEX 函数设置二级菜单。

参考资料：

[1] 求助动态二级下拉菜单的制作(https://club.excelhome.net/thread-1620256-1-1.html)

[2] OFFSET 函数(https://support.microsoft.com/zh-cn/office/offset-%E5%87%BD%E6%95%B0-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)

[3] MATCH 函数(https://support.microsoft.com/zh-cn/office/match-%E5%87%BD%E6%95%B0-e8dffd45-c762-47d6-bf89-533f4a37673a)

[4] COUNTA 函数(https://support.microsoft.com/zh-cn/office/counta-%E5%87%BD%E6%95%B0-7dc98875-d5c1-46f1-9a82-53f3219e2509)

[5] INDEX 函数(https://support.microsoft.com/zh-cn/office/index-%E5%87%BD%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd)

[6] Excel里面如何建立二级下拉菜单(https://zhuanlan.zhihu.com/p/51088269)

[7] #ref! error using indirect with a named range(https://www.mrexcel.com/board/threads/ref-error-using-indirect-with-a-named-range.620787/)