Excel-数组公式作为名称范围不起作用 [英] Excel - Array formula as name range not working

问题描述

我有一个包含项目和人员的表格，我想基于此创建一个动态的名称范围.但是，它似乎不返回任何值.任何对此的见解都会有所帮助.谢谢！

I have a table with projects and people and I want to make a dynamic name range based on that. However, it seems to return no values. Any insights into this would be helpful. Thanks!

表格:

projperson
a   x1
a   x2
a   x3
b   y1
b   y2
b   y3

名称范围定义:

=IF(temp[proj]=selected_project,temp[person],"")

其中selected_project是项目列表中a或b之一(通过数据验证进行选择).此名称范围不返回任何值.

where selected_project is one of the a or b from the project list (selection through data validation). This name range doesn't return any value.

在选择项目a的情况下，我的预期输出将是{x1，x2，x3}的名称范围.

My expected output would be a name range with {x1, x2, x3} in case project a is selected.

推荐答案

在名称管理器中将其定义为:

Define it in Name Manager as:

=INDEX(temp[person],N(IF(1,SMALL(IF(temp[proj]=selected_project,ROW(temp[proj])-MIN(ROW(temp[proj]))+1),ROW(INDIRECT("1:"&COUNTIF(temp[proj],selected_project)))))))

请注意，即使不使用CSE，将这个表达式(或等同地表示该表达式的定义名称)输入到工作表中的实际单元中也不会生成所需的数组，因为没有外部公式会对该表达式起作用否则会引发一系列回报.

Note that entering this expression (or equally the Defined Name representing this expression) into an actual cell within the worksheet will not generate the required array, even if applied with CSE, since there is no external formula acting upon this expression which would otherwise induce an array of returns.

当然，任何合适的外部公式都会强制该数组返回，尽管结果表达式是否需要CSE取决于引用的是上面的表达式还是代表该表达式的Defined Name.

Of course, any suitable external formula will coerce that array return, though whether the resulting expression requires CSE or not is dependent on which of the above expression or the Defined Name representing that expression is referenced.

例如，如果以上存储为 Rng ，则非数组公式为:

For example, if the above was stored as Rng, then the non-array formula:

=COUNTA(Rng)

将使用您提供的示例解析为:

will resolve to, using the example you give, as:

=COUNTA({"x1";"x2";"x3"})

但是，除非输入了数组(即使用CSE)，否则等效表达式为:

However, unless array-entered (i.e. with CSE), the equivalent expression:

=COUNTA(INDEX(temp[person],N(IF(1,SMALL(IF(temp[proj]=selected_project,ROW(temp[proj])-MIN(ROW(temp[proj]))+1),ROW(INDIRECT("1:"&COUNTIF(temp[proj],selected_project))))))))

不会像上面那样解决，而是:

will not resolve as above, but rather to:

=COUNTA("x1")

致谢

这篇关于Excel-数组公式作为名称范围不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！