|
制作下拉菜单大家都不陌生，但是要自动去除下拉菜单中的空值，甚至要去重，你知道怎么做吗？ 今天教大家用几个函数和函数组实现这个需求。 我用的版本是 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 列中非空值的个数 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. 增加辅助列 E，在第1行输入如下公式，向下拖动： =COUNTIF($B:$B,B1) 公式翻译：统计 B 列的名称出现次数  2. 增加辅助列 F， 在第 1 行输入：=COUNTIF($B:$B,B1) 在第 2 行输入：=IF(OR(E2=0,AND(E2>1,E2-E1=0)),0,1)，向下拖动公式 第2 行公式翻译： AND 函数：如果本行>1（表示重复出现），并且本行减去上一行=0（表示第2次以上重复出现）时，输出0 OR 函数：如果本行=0（表示为空），也输出0 If 函数：除上述条件外，输出 1 * 现在就明白为什么第一行的公式要特立独行了吧？因为计算第一行减去上一行时，会出错 3. 现在我们在 D 列输入如下数组公式，然后按Ctrl +Shift + Enter：=INDEX(B:B,SMALL(IF($F:$F=1,ROW($B:$B),4^8),ROW(1:1)))&'' * 跟 C 列公式的却别在于：if 条件不是判断是否不为空，而是判断是否=1，即出现1次，且不重复 4. 现在我们再回到下拉菜单，把 source 中引用的 C 列改成 D 列就可以了 =OFFSET($D:$D,,,SUMPRODUCT(N(LEN($D:$D)>0)),)
|
|
|
