Excel Filtered Values to Array

Question

给出如下结构的Excel表格:

Col.A  Col.B  Col.C  Col.D  Col.E  Col.F  Col.G
2      3      2      5      2      2      
5      5      2      5      5      5      5

E1中使用的公式是

=FILTER($A1:$D1,$A1:$D1=2)

和E2

=FILTER($A2:$D2,$A2:$D2=5)

Excel返回不同单元格E1、F1和E2、F2和G2中的值。

是否可以存储例如在E1中公式的输出作为单元格E1本身的数组

{2;2}

和E2

{5;5;5}

可以用类似

的公式进一步分析

=SUMPRODUCT({2;2}=2)

?

理想情况下，不使用数组公式。

我知道我可以使用

直接引用单元格

=SUMPRODUCT($A1:$D1=2)

但这不是我想要达到的目的。

提前感谢。

Answer 1

不，没有办法让Excel中的单个单元格保存数组。这就是为什么他们发明了泄漏。

方法如下:

="{"&TEXTJOIN(",",TRUE,FILTER($A1:$D1,$A1:$D1=2))&"}" 

或

=ARRAYTOTEXT(FILTER(A1:D1,A1:D1=2),1)

可以创建一些东西，看起来像一个数组，如{2,2}，它实际上是创建一个文本字符串，而不是一个实际的数组。此文本字符串不容易转换为数组。

这样做需要解析字符串。使用MID,LEFT,RIGHT或FILTERXML在,上分割字符串并提取每个部分以将文本字符串转换回数组。例如

这个过程，虽然可能需要很长的配方和过于复杂的使用。

只需在所需的公式中嵌套FILTER即可，例如:

=SUMPRODUCT(--(FILTER($A1:$D1,$A1:$D1=2)=2))

现在我们避免了创建字符串并解析它的需要，这会增加计算周期并使公式过于复杂。

它还具有总是引用原始值的好处。