为了规范表格数据录入,我们经常使用Excel的“数据有效性”功能,在单元格中设置下拉菜单,引用固定区域数据作为标准录入内容。很多时候,这方面的数据都是通过公式生成的。如果直接引用一个固定的范围,比如D1:D50,实际返回的数据可能只有8个,下拉菜单会有很多空白,不方便选择。这个例子向你展示了如何删除下拉菜单中的空白。
一.问题描述
1.使用公式返回D列中某些表的非重复值,作为下拉菜单的数据源。d列数据的数量不确定。
2.为了显示数据有效性的所有候选数据,我们一般选择较大的范围,如D1:D8区域。生产数据的有效性如下:
3.用这种方法制作的下拉菜单将包含不定数量的空格。如果空白太多,用下拉菜单选择数据就很不方便。
第二,解决方案
1.选择要设置下拉菜单的E1单元格,然后选择公式-定义名称。
2.定义一个名为“数据”的名称,在[参考位置]中输入以下公式,然后单击[确定]。=OFFSET($D$1,SUMPRODUCT(N(LEN($D:$D)0)),)
3.选择单元格E1,然后选择数据-数据有效性。
4.选择“系列”,在“源”中输入=数据,然后选择[确定]。
5.这样,E1的下拉菜单中就只有非空白的单元格内容。E1的下拉菜单将自动更新为D列不为空的单元格的内容。
6.使用公式的简单说明:=OFFSET($D$1,sum product(N(LEN($ D $ D)0)),)
其中LEN($D:$D)0确定单元格内容长度是否大于0,即如果D列中的单元格为非空单元格,则返回TRUE,然后SUMPRODUCT计算非空单元格的个数。最后,使用OFFSET函数获取从D1到d列最后一个非空单元格的值