Question

EXCEL数组公式在数据有效性中序列来源中如何运用？

想在数据有效性中以某数组公式的结果作为序列值来源，不知可否？如果行，想先将数组公式定义名称，再在有效性中直接等于该定义名称。试过打大括号的方式不行，不打也不行。该怎么定义呢？比如查找返回多数值公式=INDEX($E$1:$E$1000,SMALL(IF($C$1:$C$1000=$N10,ROW($C$1:$C$1000),4^8),COLUMN(A1)))三键齐按（ctrl+shift+回车）后得到的是一个数组。把这个公式重新定义名称然后在数据有效性中运用，提示

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

追问

对，你说的和我遇见的情况一模一样，看来你理解我的意思，请问如果数组公式的结果是动态的，怎么用offset来得到动态结果。因为动态结果都要做辅助列很麻烦。

追答

你把数据截图，带行号和列标，像本人回答你问题的截图一样，并说明如何动态的。否则无法凭空来闭门造车。

追问

就拿你的表格作为例子吧，有什么方法怎么不通过辅助列，在N列单元格内容确定时后面的单元格下拉列表中直接出现对应的序列。比如说，当N1值为“五”时，O1的单元格下拉菜单就会出现AHOV的序列，当N2值为“六”时，O2的单元格下拉菜单就会出现GNU的序列？如果按照你的方法，每查一个值，就要重新建一个辅助列并且重新引用，岂不很麻烦。

追答

要避免麻烦，就是不能用有空格的方式录入数据。另外的方法是用VBA，可以不受空格的限制。但得有点VBA基础。

Answer 2

m列是数组公式产生的结果吧，定义名称的公式=offset($m$2,,,countif($m$2:$m$1000,"?*"))

追问

如果这样是不是要做辅助列，如果被查找值N10往下拉，会返回不同的结果，要返回的区域也会随着改变。

追答

是要做辅助列，但你说的n10往下拉不明白怎么回事，把问题作为内容（邮件主题一定要包含“excel”,本人以此为依据辨别非垃圾邮件，以免误删）,excel样表文件（把现状和目标效果表示出）作为附件发到yqch134@163.com帮你看下

追问

已发送到您的邮箱

追答

已回

追问

刚看了下，虽然不是我想要的结果，但也是一种解决问题的方式，谢谢啦

Answer 3

直接引用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 下拉复制

Answer 4

假设如A2：A4单元格不为空，A1显示“可用”并填充绿色，反之显示：不可用，并填充红色，且当A1单元格为可用时，用下拉列表形式选择A2：A4之内容； 1、在A1单元格输入公式：=IF(COUNTA(A2:A4)>0,"可用","不可用") 回车，再选中 A1单元格，点击>条件...