制作下拉菜单大家都不陌生，但是要自动去除下拉菜单中的空值，甚至要去重，你知道怎么做吗？，下面我们就来聊聊关于excel 下拉列表无空值?接下来我们就一起去了解一下吧!

excel 下拉列表无空值（去除Excel下拉菜单中的空值和重复值）

excel 下拉列表无空值

制作下拉菜单大家都不陌生，但是要自动去除下拉菜单中的空值，甚至要去重，你知道怎么做吗？

今天教大家用几个函数和函数组实现这个需求。

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

需求：

1. 如何去除下拉菜单中的空值？

2. 如何去除不连续空值？

3. 如何在上述基础上去重？

案例：

先看一下用普通方法制作的下拉菜单会呈现什么效果。

1. 用 B 的数据源在 A 列制作下拉菜单，其中标黄的为空值

2. 下拉菜单中包含上下空行

解决方案1 – 去除向下连续空值：

1. 在 Source 中输入公式：=OFFSET($B$2,,,SUMPRODUCT(N(LEN($B:$B)>0)),)

这次用到了一个新函数 OFFSET，我们分解开来翻译一下：

SUMPRODUCT(N(LEN($B:$B)>0))：

  • 统计 B 列中非空值的个数

    * SUMPRODUCT 函数被誉为函数中的神器，关于其部分用法，可以参考 Excel函数(四) – sumproduct函数计数、排名、求和等等

    OFFSET (基准位置，向下或上偏移几行，向右或左偏移几列，引用区域的高度，引用区域的宽度)

  • 本例中我们只使用了“基准位置”和“引用区域的高度”两个参数，即引用 B 列中非空值

    * 请注意：

    1) 这个方法不能去除数据列上方的空值，所以我们必须从有值的行开始读，本例中是 $B$2

    2) 这个方法只能去除向下的连续空值，如果有隔行的空值，也无法处理。所以我们需要方案 2

    解决方案2 – 去除不连续的空值：

    万能数组公式 index small if row 终于闪亮登场！

    1. 增加辅助列 C，在 C1 中输入公式：=INDEX(B:B,SMALL(IF($B:$B<>"",ROW($B:$B),4^8),ROW(1:1)))&""

    2. 按 Ctrl Shift Enter 让数组生效

    3. 向下拖动公式，就得到一列去除了所有空值的列表

    函数翻译：

    IF row 函数：

  • 读取 B 列不为空的行值，为空返回 4^8（4 的 8 次方）

  • 4 的 8次方 = 65536，是 2003 版Excel 的最大行数，所以被当成惯例使用。目的是为了配合 Small 函数使用

    SMALL 函数：

  • ROW(1:1)或row()：当前行值

  • 将 B 列行值与当前行值比较，取其小（空值被定义成了最大行值 65536）

    Index 函数：

  • 读取 B 列中的非空行

    &""：

  • 将结果转换为文本值

    按Ctrl Shift Enter：

  • 数组公式，必须这样按才能生效，公式前后会自动出现 {}

    4. 现在我们用方案 1 中的公式配合 C 列使用，在 Source 中输入如下公式，就可以了：

    =OFFSET($C:$C,,,SUMPRODUCT(N(LEN($C:$C)>0)),)

    解决方案3 - 如何在上述基础上去重？

    1. 增加辅助列 H，在第1行输入如下公式，向下拖动：

    =COUNTIF(B$1:B1,B1)

    公式翻译：对重复数据进行排序统计

    2. 增加辅助列 I，输入公式： =IF(H1=1,1)

    翻译：提取不为空，且不重复的唯一值

    *这一列完全可以不要，本文是教学贴，增加这一辅助列是为了帮助大家看得更清楚

    3. 在 E 列中输入如下数组公式，然后按Ctrl Shift Enter：=INDEX(B:B,SMALL(IF($I:$I=1,ROW($B:$B),4^8),ROW(1:1)))&""

    * 跟 C 列公式的却别在于：if 条件不是判断是否不为空，而是判断是否=1，即出现1次，且不重复

    4. 现在我们再回到下拉菜单，将 Source 中的公式引用参数改成 E 列，就可以了：

    =OFFSET($E:$E,,,SUMPRODUCT(N(LEN($E:$E)>0)),)