excel - 如何使用 INDEX() 进行嵌套数组计算？

Question

想象一下，我有几个(即 > 100)数字列向量。向量很大且长度相等(例如 20k 个项目)。向量不相邻，因此它们不构成矩阵。

我想要的是用向量进行一些逐行计算，例如

对于每一行，所有向量中的第一个非零值是多少？

或者

对于每一行，所有向量中的最大值是多少？

看这个简化的例子，它应该得到所有向量的最大值，所有行都是 3(实际上显示的值是 1):



如果我可以将向量复制为矩阵并获取跨越给定行的所有向量的行范围列，而不是列范围，那将很容易。但由于数据的大小，这不是选项。我认为这与其他 SO 问题有关:Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array? .

Answer 1

您可以使用 CHOOSE 将大小相等的列组合成一个范围，例如对于您的 3 范围示例:
=CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6)
然后直接在公式中使用它，例如在 G2 中复制下来以获取示例中每行中的 MAX
=MAX(INDEX(CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6),F2,0))
或者您可以将 CHOOSE 部分定义为命名范围[如果您有 100 个范围特别有用]，例如命名该矩阵并使用
=MAX(INDEX(Matrix,F2,0))
您需要根据范围数修改 {1,2,3} 部分，以便在您有 100 个范围时可以使用快捷方式
=CHOOSE(TRANSPOSE(ROW(INDIRECT("1:100"))),Range1, Range2.....Range100)
现在需要用 CTRL+SHIFT+ENTER 确认

要获得第一个非零值，您可以使用此版本
=INDEX(INDEX(Matrix,F2,0),MATCH(TRUE,INDEX(Matrix,F2,0)<>0,0))
也用 CTRL+SHIFT+ENTER 确认