arrays - 如何在Excel中混合动态数组和常量数组
问题描述
如何在 Excel 中将常量数组与动态数组混合,我正在寻找一种非 VBA 方法来做到这一点。让我解释一下场景-
说,我有一列 7 个值。如果我想测试每个值的条件,我可以生成一个相同数量的值的数组。现在,我想将结果数组与实际上具有 n+1 个值(在本例中为 8)的另一列相乘(求和),并且我知道要添加到此动态数组的最后一个值是常数,例如FALSE
(0
)。
在上面的示例中,我实际上想在动态数组的末尾添加一个常量 0/False ($A1:$A7<5),而不添加虚拟/隐藏行/单元格/列?我可以做吗?
我已经尝试过这些变化,但这些都不起作用
=SUMPRODUCT({($A$1:$A$7<5);0}*(E1:E8))
=SUMPRODUCT({($A$1:$A$7<5),0}*(E1:E8))
{=SUMPRODUCT({($A$1:$A$7<5);0}*(E1:E8))} # array formula
解决方案
我不知道在 Excel 中实际向现有数组添加额外元素的方法(就像在 Google 表格中一样),但您可以通过测试当前行是否小于或等于A 列,如果不是,则采用默认值。
在 Microsoft 365 中:
=SUM(IF(ROW(E1:E8)<=ROWS(A1:A7),A1:A7<5)*E1:E8)
在 Excel 2019 中,公式相同,但必须使用数组输入CtrlShiftEnter
上式中的 false 默认值来自于 if 语句中只有一个参数,所以如果行数大于 7,则默认给出 False。您可以通过输入第二个参数(例如)将其更改为 1,例如
=SUM(IF(ROW(E1:E8)<=ROWS(A1:A7),A1:A7<5,1)*E1:E8)
一个更动态的版本是
=SUM(IF(ROW(E1:INDEX(E:E,COUNT(E:E)))<=ROWS(A1:INDEX(A:A,COUNT(A:A))),A1:INDEX(A:A,COUNT(A:A))<5)*E1:INDEX(E:E,COUNT(E:E)))
如果您不使用 Microsoft 365,则必须再次输入数组。
在 Microsoft 365 中,您可以使用 Let() 大大缩短此时间:
=LET(A,A1:INDEX(A:A,COUNT(A:A)),E,E1:INDEX(E:E,COUNT(E:E)),SUM(IF(ROW(E)<=ROWS(A),A<5)*E))