动态数组函数可以在Excel中的列表验证(“下拉列表")中用作源吗? [英] Can dynamic array functions be used as a source in a list validation ("dropdown") in Excel?

问题描述

我想在Excel中使用新的动态数组函数(例如 FILTER ， UNIQUE ， SORT )作为列表验证的来源

I would like to use the new dynamic array functions in Excel (like FILTER, UNIQUE, SORT) as a source for list validations.

在下面的示例中，应显示 A 列中的所有值，其中 B 列中的对应值至少为500.我使用以下公式:

In the following example, all values in column A where the corresponding value in column B is at least 500 should appear. I use the following formula:

=FILTER($A$2:$A$7;$B$2:$B$7>=500)

不幸的是，当我使用单元格 D2 中的公式作为源时，收到错误消息:

Unfortunately, when I use the formula from cell D2 as a source, I get an error message:

我还尝试过将公式放入名称中，然后引用该名称作为来源.但这给了我同样的错误.唯一的解决方法是使用井号引用单元格 D2 :

I have also tried to put the formula into a name and then reference the name as a source. But that gives me the same error. The only workaround was to reference cell D2 with a hash sign:

=D2#

但是，如果我可以直接使用这些动态数组函数(通过名称管理器或在验证界面中)，则更可取.有什么方法可以实现?

However, I would prefer if I could use those dynamic array functions directly (either via name manager or in the validation interface). Is there any way to achieve that?

推荐答案

尝试一下，它对我有用.请注意，引用M1是验证列的第一个值.(我用的是M)

Try this, it works to me. Note that the reference M1 is the first value of the validation column. (I used M in my case)

=INDEX($B:$B;MATCH(M1;$A:$A;0))>=500

这篇关于动态数组函数可以在Excel中的列表验证(“下拉列表")中用作源吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！