Question

excel数据有效性问题,下拉菜单相关,望高手解答

如图所示,

在对应关系表里面的A列有许多的数字,中间有许多空格,以后可能会继续往下增加,我希望在sheet1里面的某个单元格加一个数据有效性的下拉菜单,希望这个下拉菜单里面只显示A列有值的内容,例如20 30 40 50这样的,并且以后再对应关系A列里面增加新值时,能够显示出来,我自己弄得下拉菜单总是忽略不了空值,望高手解答一下

Answer 1

这个需要通过辅助列来实现：

即有空行的数据，归整为没有空行的数据，然后再在数据有效性中引用归整的数据。

以上图为例，

B1单元格公式：=IF(ROW()<=COUNTA(A:A),INDEX(A:A,SMALL(ROW(A$1:A$1000)*((1-(A$1:A$1000<>""))*10^9+1),ROW())),"")

按 【CTRL+SHIFT+回车】 输入数组公式

然后下拉足够多的行，确保行数大于有空行的数组总数

然后按截图设置数据有效性（根据B列数据个数自适应）

示例可见附件

追问

看了一下 最主要是保证下拉菜单里面没有空值,由于数据有效性选择范围太广,E列的那个下拉菜单最后多出好多空格 有没有办法让下拉菜单里面只有有效值 不要有那么多空白

追答

请把有效性公式改为：=INDIRECT("B1:B"&COUNT(B:B))
公式中的“B”请改为实际的列号
另外，以上公式仅适用于数据列中全是数值数据。如果有文本的话会显示不完整。

Answer 2

用辅助列对“对应关系“的A列去除空值的单元格

再在sheet1对该辅助列使用数据有效性

去除空值用数组公式

C1输入数组公式

=IF(ISERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100),8^8),ROW(A1)))),"",INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100),8^8),ROW(A1))))

以同按CTRL+SHIFT+回车  这三个键作为结束

再下拉

Answer 3

数据有效性中的忽略空值, 不代表可以忽略全部空值, 它只能忽略连续区域后面多选的空白单元格区域
中间的空值, 你需要另外做一个辅助区域来放置连续的数据