Excel 下拉菜单选一个少一个选项

关于下拉菜单，已经说过好几期了，每一次都对应不同的需求。

比如Excel下拉菜单去空/去重（填坑贴）。

这次教一个新需求，下拉菜单要选一个少一个，怎么实现？

我用的版本是 Excel 2016，其他版本的界面可能略有不同。

案例：

比如下图，当选择了“壮林”后，下面的行的下拉列表里就没有“壮林”这个选项了。

解决方案：

1. 增加辅助列 C，输入以下公式，目的是计算出 B 的原始名单在 A 列中出现的次数：

=COUNTIF(A:A,B:B)

2. 增加辅助列 D，输入以下函数组，按 Ctrl +Shift + Enter 生效：

=INDEX(B:B,SMALL(IF(C:C=0,ROW($B:$B),4^8),ROW(1:1)))&""

函数组的翻译可以参考去除Excel下拉菜单中的空值和重复值

唯一的区别是我们在 IF 中改用了 C:C=0 这个条件，整个公式目的是列出 B 列减去 A 列的合集，本例中，“壮林”已被自动扣除

3. 现在回到 A 列的下拉菜单制作，在 Source 中输入以下公式，就可以了：

=OFFSET($D$2:$D$100,,,SUMPRODUCT(N(LEN($D$2:$D$100)>0)),)

公式的翻译可以参考去除Excel下拉菜单中的空值和重复值

为什么不引用整个 D 列呢？因为在本例中，第一行被设定为名称，不需要做进下拉菜单中，所以从 D2 开始取值