excel下拉列表无空值制作下拉菜单大家都不陌生，但是要自动去除下拉菜单中的空值，甚至要去重，你知道怎么做吗？需求：1.如何去除下拉菜单中的空值？所以我们需要方案2解决方案2–去除不连续的空值：万能数组公式indexsmallifrow终于闪亮登场！
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))：
* SUMPRODUCT 函数被誉为函数中的神器，关于其部分用法，可以参考 Excel函数(四) – sumproduct函数计数、排名、求和等等
OFFSET (基准位置，向下或上偏移几行，向右或左偏移几列，引用区域的高度，引用区域的宽度)
* 请注意：
1) 这个方法不能去除数据列上方的空值，所以我们必须从有值的行开始读，本例中是 $B$2
2) 这个方法只能去除向下的连续空值，如果有隔行的空值，也无法处理。所以我们需要方案 2
解决方案2 – 去除不连续的空值：
万能数组公式 indexsmallifrow 终于闪亮登场！
1. 增加辅助列 C，在 C1 中输入公式：=INDEX(B:B,SMALL(IF($B:$B<>"",ROW($B:$B),4^8),ROW(1:1)))&""
2. 按 CtrlShiftEnter 让数组生效
3. 向下拖动公式，就得到一列去除了所有空值的列表
函数翻译：
IFrow 函数：
SMALL 函数：
Index 函数：
&""：
按CtrlShiftEnter：
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 列中输入如下数组公式，然后按CtrlShiftEnter：=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)),)
