一个公式，搞定多级下拉菜单

在Excel365中设置数据验证时，对于数据来源中的重复值单元格下拉菜单中会自动去除重复，只保留不重复值。如下图，在数据验证中选择包含重复值的B3:B6单元格区域为数据来源：

设置完成后，单击下拉按钮，弹出的备选项中只保留一个A和一个B，自动去掉了重复值。

利用这个特性，可以在Excel 365中利用一个公式，实现任意N级的关联下拉菜单。

如下图，在名称为“数据”的工作表中以标准二维表的方式存储着省、市、县、区的信息（数据来源于Excelhome会员的分享帖）：

要求在另外一个工作表中生成如下图的级联下拉菜单：

操作步骤如下：

步骤1：单击A2单元格，在数据验证中，选择数据来源为“数据”工作表的A列“省”信息。

步骤2：单击B2单元格，利用以下公式定义“下拉菜单”的名称：

=LET(s,BYROW(数据!$A2:A37,LAMBDA(x,CONCAT(x))),t,CONCAT(数据验证!$A2:A2),OFFSET(数据!B1,MATCH(t,s,),,SUM(N(s=t))))

步骤3：选中B2:D2单元格区域，在数据验证中的“来源”处输入以下公式：

='下拉菜单'

设置完成后，B列会根据A列内容返回下拉菜单内容：

C列会根据A列和B列信息返回下拉菜单内容：

D列会根据A列、B列和C列的信息返回下拉菜单内容：

上述用于定义名称的公式主要使用了相对引用偏移的技巧，下面简要说明一下公式的原理。

在B2单元格时，“下拉菜单”名称对应的公式为（公式截图时将公式放在了B4单元格，但公式仍是B2单元格的公式，下同）：

=LET(s,BYROW(数据!$A2:A37,LAMBDA(x,CONCAT(x))),t,CONCAT(数据验证!$A2:A2),OFFSET(数据!B1,MATCH(t,s,),,SUM(N(s=t))))

上述公式中定义的名称“s”按行合并“数据”工作表中的A2:A37的内容，由于只有“省”，所以只返回省份信息。

公式中定义的名称“t”合并B2单元格左侧的A2:A2单元格内容，此时也只返回省份名称“广东”。

匹配t（“广东”）在s（只包含省信息的字符串）中的位置，就是“数据”工作表中“广东”的起始位置，统计t在s中出现的次数，就是“广东”的数据行数。然后使用OFFSET函数获取“广东”右侧的“市”信息。

在C2单元格时，“下拉菜单”名称对应的公式为（由于使用了相对引用，所以数据引用范围自动进行了扩展，如下图红色方框部分）：

=LET(s,BYROW(数据!$A2:B37,LAMBDA(x,CONCAT(x))),t,CONCAT(数据验证!$A2:B2),OFFSET(数据!C1,MATCH(t,s,),,SUM(N(s=t))))

上述公式中定义的名称“s”按行合并“数据”工作表中的A2:B37的内容，也即“省+市”的信息。

公式中定义的名称“t”合并C2单元格左侧的A2:B2单元格内容，此时返回省份+市的信息“广东广州”。

匹配t（“广东广州”）在s（省+市的字符串）中的位置，就是“数据”工作表中“广东省广州市”的起始位置，统计t在s中出现的次数，就是“广东广州”的数据行数。然后使用OFFSET函数获取“广东广州”右侧的“区”信息。

“下拉菜单”名称在D列会按同样的方式，自动将相对引用扩展，因此再多级别也能自动适应，从而达到一个公式实现N级下拉菜单的目的。

示例文件：

https://pan.baidu.com/s/1CzrFKi4KMliF64pk60KELw?pwd=cejx

图文制作：超人

原载：超人一筹高效办公