Question

EXCEL数组公式在数据有效性中序列来源设置问题

需求：A页单位类型筛选的值 给 B页 单元格加下拉选项， 例如 部门A列 下面的单元格 点击可以下拉选择 11，22，9999，（随A页的内容动态变化） B页单元格的来源怎么设置？用公式怎么设置数据有效性





Answer 1

Excel中的数据有效性（高版本中已称为“数据验证”）不能用数组公式，包括用数组公式定义的名称。要使用数组公式的结果，比较直接的方法是用辅助列数获得组公式的结果，再在数据验证中引用该辅助列区域。如果数组公式的结果是动态的，可以考虑用offset来得到动态结果。

因为不清楚你的数据是怎样的。下面模拟了26行数据，数组公式的作用是获取C列为星期日对应的E列数据，应该接近你提问的意思的吧（满足C列等于N10，取E列的数据）。如图，先在P列中得到数组公式的结果：

P1=IFERROR(INDEX(E$1:E$27,SMALL(IF(C$1:C$26=N$10,ROW($1:$26),4^8),ROW(1:1))),"")
同时按Ctrl+Shift+Enter三键输入数组公式，下拉到出现空白单元格

然后，在S2中设置数据验证，序列直接引用P1:P4，这里没有考虑动态数据。这样达到了数据验证用数组公式结果的目的。

R2中数组验证直接用数组公式，下拉只有一个数据。而如果用定义名称的方法，就会弹出提问所示的警示窗口，说明是不能这样来设置数据验证的。

Answer 2

直接引用M列，就可以了，为何要 自定义名称？
INDEX($E$1:$E$1000,SMALL(IF($C$1:$C$1000=$N10,ROW($C$1:$C$1000),4^8),COLUMN(A1)))
数组右拉
然后 C3:引用该行区域
比如=$M3:$z3
C3 下拉复制

追问

你这个不符合我的需求，我希望是下拉得到 A页 过滤的值