【excel】设置可变下拉菜单（一级联动下拉菜单）

在这个一级下拉菜单后，我又写了二级联动菜单的文： 【excel】设置二级可变联动菜单

【需求】制作动态下拉菜单，显示无重复的“班级”列表

原始数据明细如下：

制作下拉菜单，显示所有班级名称，且不重复：

【思路】设置辅助列，使用UNIQUE()函数去重，并用FILTER()去掉结果中的“0”

【步骤】

step1 辅助列

在M1单元格中输入公式=UNIQUE(FILTER($A$2:$A$100,$A$2:$A$100<>""))
本例中选择的范围是前100行。因为下拉菜单中不含有表头，所以从第二行，也就是A2单元格开始。

输入公式后直接回车。
注意，虽然公式中含有数组，但不需要使用数组公式的快捷键Ctrl+Shift+Enter输出结果。

step2 设置下拉菜单

在想放置下拉菜单的地方先输入表头，如在H1单元格中输入“班级”。然后鼠标选中H2作为下拉菜单的单元格。

在H2单元格被激活后，【数据】-【数据验证】-【数据验证(V)…】：

在【数据验证】对话框中，【设置】选项卡下，允许(A)：选择【序列】，来源：输入=OFFSET($M$1,0,0,COUNTA($M:$M))：

这里OFFSET函数返回一个与M列非空单元格数量相同高度的区域（这个高度可变），但宽度只有一列（即M列）。
点击【确定】，完成。

【总结】

1、用UNIQUE()函数制作辅助列，其中FILTER()函数用来消除UNIQUE()函数结果中的”0“。
然后在用”数据验证“生成下拉菜单时，使用OFFSET()函数，引用辅助列。
2、非常重要的是，所谓”辅助列“，其实是”辅助单元格“。因为在写UNIQUE()函数时，是用到了数组，但是又不用数组显示出来，因为不能用快捷键Ctrl+Shift+Enter，而是将结果直接回车放在了单元格M1中。
如果使用快捷键Ctrl+Shift+Enter，则在改变A列里面的数据时，下拉菜单将变得不准确，如O列和P列所示：

这是因为Ctrl+Shift+Enter将结果变成固定的列数了。
以上。