制作下拉菜单大家都不陌生,但是要自动去除下拉菜单中的空值,甚至要去重,你知道怎么做吗?
今天教大家用几个函数和函数组实现这个需求。
我用的版本是 Excel 2016,其他版本的界面可能略有不同。
需求:
1. 如何去除下拉菜单中的空值?
2. 如何去除不连续空值?
3. 如何在上述基础上去重?
案例:
先看一下用普通方法制作的下拉菜单会呈现什么效果。
1. 用 B 的数据源在 A 列制作下拉菜单,其中标黄的为空值
![c1d1c0976ab1726ea793ca62ce3a5a5a.png](https://i-blog.csdnimg.cn/blog_migrate/16a1ec71154a8fa9e3bed0bb2582abe0.jpeg)
2. 下拉菜单中包含上下空行
![d323b9480ff02382ab027f32217a6d8e.png](https://i-blog.csdnimg.cn/blog_migrate/4606825365ff21c87891a68eec73b650.jpeg)
解决方案1 – 去除向下连续空值:
1. 在 Source 中输入公式:=OFFSET($B$2,,,SUMPRODUCT(N(LEN($B:$B)>0)),)
![689be482b1c5437d2106f686405bb9b6.png](https://i-blog.csdnimg.cn/blog_migrate/fbe21a615a542018d863ba99af6d392a.jpeg)
![c796ef824c11ce6d40d0eeff38983920.png](https://i-blog.csdnimg.cn/blog_migrate/550c100e8dcbcfa82930a323883da94e.jpeg)
这次用到了一个新函数 OFFSET,我们分解开来翻译一下:
SUMPRODUCT(N(LEN($B:$B)>0)):
- 统计 B 列中非空值的个数
* SUMPRODUCT 函数被誉为函数中的神器,关于其部分用法,可以参考 Excel函数(四) – sumproduct函数计数、排名、求和等等
OFFSET (基准位置,向下或上偏移几行,向右或左偏移几列,引用区域的高度,引用区域的宽度)
- 本例中我们只使用了“基准位置”和“引用区域的高度”两个参数,即引用 B 列中非空值
* 请注意:
1) 这个方法不能去除数据列上方的空值,所以我们必须从有值的行开始读,本例中是 $B$2
2) 这个方法只能去除向下的连续空值,如果有隔行的空值,也无法处理。所以我们需要方案 2
![83d3cfce3b8574986e6209d90fa20b4a.png](https://i-blog.csdnimg.cn/blog_migrate/e2c4c3b09b5b1883f255d12fd43762db.jpeg)
![e4a0046105946299a6a34e0056cc2576.png](https://i-blog.csdnimg.cn/blog_migrate/314ee23dc9eee41b6343f1f3625c4fb7.jpeg)
解决方案2 – 去除不连续的空值:
万能数组公式 index + small + if + row 终于闪亮登场!
1. 增加辅助列 C,在 C1 中输入公式:=INDEX(B:B,SMALL(IF($B:$B<>