EXCEL三级下拉菜单,多级联动,看完你也是大咖

为了规范数据录入、提升录入效率和准确性，我们在EXCLE的数据录入中，经常会使用一级下拉菜单，如果需要设置二三级下拉菜单，许多EXCEL的新手就会一脸懵圈。

特别是后期参数数据要不断更新，二三级下拉菜单需要联动。

实现多级下拉菜单的设置，有多种方法和途径，今天分享一种简单高效的设置方法。

在设置中我们会使用到OFFSET函数、XMATCH函数和COUNTIFS函数。

示例

三级下拉菜单（参数更新时联动）

如下面表格，左边是统计明细表，右边是数据参数表（按照设计习惯，参数表应该放在另外一个sheet分表中）。

需要在左边的统计明细表，设置三级下拉菜单。

右边的参数表，会随时更新数据，排列顺序没有规律。

设计步骤

步骤 ① 规范参数数据源
把参数表的数据，E:G的3列都按照升序排列，生成数据I:K列作为二三级下拉菜单的原始参数。（参数表增加新数据时，这3列排序后的数据都会自动更新）

LET(X,E3:.G1000,SORTBY(X,CHOOSECOLS(X,1),1,CHOOSECOLS(X,2),1,CHOOSECOLS(X,3),1))

说明：

用SORTBY+CHOOSECOLS+LET函数把E:G列的参数数据，做升序排列。

再把省份E列去重复值，生成数据M列作为一级下拉菜单的原始参数。

CHOOSECOLS(I3#,1)

说明：

CHOOSECOLS(I3#,1)函数公式的#，表示引用I3单元格数组的意思。

步骤 ② 设置一级下拉菜单

选中A列，在菜单栏选择数据，再选择有效性。

在数据有效性菜单中，选择序列，在来源中输入函数公式：=$M$3#，选择确定。一级下拉菜单即设置完成。

步骤 ③ 设置二级下拉菜单
选中B3:B10(设置二级下拉菜单的区域)，在菜单栏选择数据，再选择有效性。
在数据有效性菜单中，选择序列，在来源中输入函数公式：=OFFSET($I$2,XMATCH($A3,$I$3:$I$1000,0),1,COUNTIFS($I$3:$I$1000,$A3))，选择确定。二级下拉菜单即设置完成。

OFFSET($I$2,XMATCH($A3,$I$3:$I$1000,0),1,COUNTIFS($I$3:$I$1000,$A3))

说明：

'=OFFSET($I$2,XMATCH($A3,$I$3:$I$1000,0),1,COUNTIFS($I$3:$I$1000,$A3))

建议先在其他单元格，编辑函数公式，然后再把函数公式复制到数据有效性的来源中。
设置二三级下拉菜单时，在选择设置区域时，不能选中整列。

注意函数公式的绝对引用和相对引用。因为要在B列区域设置二级下拉菜单，所以引用的A3单元格，都是列绝对引用；I2单元格，是OFFSET函数的参照区域，是固定不变的，需要行列绝对引用。
OFFSET函数是设置二三级下拉菜单经常用到的主要函数。
Excel 中的 OFFSET函数是一个区域偏移函数，用于根据指定的起始点（基点），通过行、列偏移量以及指定的新区域大小，动态地返回一个新的单元格或区域引用。函数语法=OFFSET(参照区域, 行数, 列数, [高度], [宽度])行数：表示下移或上移的行数，正数是向下移动，负数是向上移动。列数：表示右移或左移的列数，正数是向右移动，负数是向左移动。
XMATCH($A3,$I$3:$I$1000,0)用XMATCH函数，查找"A3山东省"需要在"I列"下移的行数。结果作为OFFSET函数的第二参数，即下移行数。函数语法=XMATCH(查找值, 查找数组, [匹配模式], [搜索模式])XMATCH函数的第一参数，查找数值，可以使用数值&数值的格式，查找数组也可以使用数组&数组模式。（在设置三级下拉菜单中，使用此格式）
COUNTIFS($I$3:$I$1000,$A3)
用COUNTIFS函数，计算出"A3山东省"在"I列"的数量。这个数量也是"I列地区"的数量，结果作为OFFSET函数的第四参数，即高度。
因为设置的"地区"在J列，OFFSET函数的参照区域在I列，需要往右移动1列，第三参数为1，即列数。

步骤 ④ 设置三级下拉菜单

选中C3:C10(设置三级下拉菜单的区域)，在菜单栏选择数据，再选择有效性。

在数据有效性菜单中，选择序列，在来源中输入函数公式：=OFFSET($I$2,XMATCH($A3&$B3,$I$3:$I$1000&$J$3:$J$1000,0),2,COUNTIFS($I$3:$I$1000,$A3,$J$3:$J$1000,$B3))，选择确定。三级下拉菜单即设置完成。

三级下拉菜单函数的使用要领，与设置二级下拉菜单类似，只是XMATCH函数和COUNTIFS函数在查找时，需要同时查找2个单元格的数值。

综上，通过使用OFFSET函数、XMATCH函数和COUNTIFS函数，我们设置完成了一二三级下拉菜单。三级下拉菜单的设置，只要掌握了OFFSET函数的使用要领，设置起来还是蛮容易的。

本期，分享到此。如果您喜欢，欢迎点赞、收藏、转发加关注，以备您急需之用。