Excel 如何去除二级下拉菜单里的空白行？-excel下拉菜单空白,表格下拉选项空白

方法/步骤

1
A列为序列引用的内容，对C1单元格进行数据有效性的设置。
选中C1，点击菜单zhidao 数据-数据有效性，在有效性条件的允许条件中选择“序列”，然后在文本框中输入=OFFSET($A$1,,,COUNTA($A:$A))，确定退出。

2
我们对A列内容进行编辑，可以发现，C1下拉菜单中空白没有了，并且还根据A列内容自动更新了。

END
OFFSET函数介绍

OFFSET函数格式如下：
OFFSET(reference,rows,cols,[height],[width])
这是一个版偏置函数，reference是偏置的基准，rows和cols都是偏移量，前者是行偏移，后者是列偏移，[height]和[width]是偏移后返回范围。如果rows和cols值为0，可省略不写，但半角逗号需保留；[height]和[width]值为1，可省略不写。

END
COUNTA函数介绍

1
COUNTA函数格式如下：
OFFSET(value1,[value2],...)
这是一个计数函数，计算区域权里非空单元格个数。value1为必需，后面的可选。如图所示，H列非空单元格个数为5，运用COUNTA函数可以轻松进行计数。

D列非空单元格吧，为什么不用COUNTA而用SUMPRODUCT呢，例如：
=OFFSET($D$1,,COUNTA($D:$D))

可以改百成这样：OFFSET($H$1,,,SUM(IF($H$1:$H$2000="",0,1)))

不过有2点度要注意：

1. $H$1:$H$2000这个范围不能整列，否则会知出道错，可以把范围设置大一点

2. 如果H列数据未连续（中间有空值行），那么下拉菜单也会出现相应出现空行，并专且菜单个数也会相应少掉（中间几个空行就少掉几个）属
   

，利用公式在D列返回某些表格的不重复值，作为下拉菜单的数据源。D列数据的个数不确定。

2
为了使数据有效性能够显示所有的备选数据，所以一般我们选择一个较大的范围，比如说D1:D8区域。制作数据有效性如下：

3
这样制作的下拉菜单中就会包括数目不定的空白，如果空白非常多的话在用下拉菜单选择数据时就非常不方便。

END
解决方案：
选中要设置下拉菜单的E1单元格，选择【公式】-【定义名称】。

定义一个名称为“Data”的名称，在【引用位置】输入下面的公式并点击【确定】。
=OFFSET($D$1,,,SUMPRODUCT(N(LEN($D:$D)>0)),)

选中E1单元格，选择【数据】-【数据有效性】。

选择“序列”，“来源”处输入=Data，然后【确定】。

这样，在E1的下拉菜单中就只有非空白单元格的内容了。E1的下拉菜单会自动更新成D列不为空的单元格内容。

6
使用公式的简单说明：
=OFFSET($D$1,,,SUMPRODUCT(N(LEN($D:$D)>0)),)
其中的LEN($D:$D)>0判断单元格内容长度是不是大于0，也就是如果D列单元格为非7a64e58685e5aeb9335空单元格就返回TRUE，然后SUMPRODUCT统计出非空单元格个数。最后用OFFSET函数从D1开始取值至D列最后一个非空单元格。