excel - 非相邻单元格作为数组函数的输入(MIN 和 ISBLANK)

Question

有人问了这个问题，由于具体问题的答案是一个错字，所以它被删除了:

https://stackoverflow.com/questions/59289065/excel-non-adjecent-cells-as-input-to-array-function-min-and-isblank

这是问题:

I'm trying to find the minimum of two (non-adjacent) cells per column and sum these for a number of columns (13 in total).

What complicates it is that I'd like the function to treat empty cells as zero.

I can get it to work as long as the cells are adjacent, but when they are not, excel gives a "too many arguments for this function" pop-up.

The formula I have for adjecent cells is this (not exactly pretty, sorry!):

{=SUM(MIN(IF(ISBLANK(P3:P4);0;P3:P4));MIN(IF(ISBLANK(Q3:Q4);0;Q3:Q4));MIN(IF(ISBLANK(R3:R4);0;R3:R4));MIN(IF(ISBLANK(S3:S4);0;S3:S4));MIN(IF(ISBLANK(T3:T4);0;T3:T4));MIN(IF(ISBLANK(U3:U4);0;U3:U4));MIN(IF(ISBLANK(V3:V4);0;V3:V4));MIN(IF(ISBLANK(W3:W4);0;W3:W4));MIN(IF(ISBLANK(X3:X4);0;X3:X4));MIN(IF(ISBLANK(Y3:Y4);0;Y3:Y4));MIN(IF(ISBLANK(Z3:Z4);0;Z3:Z4));MIN(IF(ISBLANK(AA3:AA4);0;AA3:AA4));MIN(IF(ISBLANK(AB3:AB4);0;AB3:AB4)))}

This gives the desired output in the column "person months total".

I have tried to use the CHOOSE function for non-adjacent cells as a test (similar to this question), but this gives the "There's something wrong with this formula" pop-up

=SUM(MIN(IF(ISBLANK(CHOOSE{1;2};P16;P18));0;CHOOSE({1;2};P16;P18)))

So now I'm wondering, can this be done at all? Am I missing something?

I would appreciate the help!

Kind regards, Amy

虽然这个问题的答案是缺少 (在第一个 CHOOSE 之后，我开始为那个可怕的长公式制作一个更简单的版本，并想把它贴在这里。所以问题是，“有没有不使用 vba 的更简单的方法？”

Answer 1

由于范围实际上是按行相邻的，但是逐列比较，我们可以以数组形式使用 MMULT。通过使用 MMULT，我们可以创建一个由最小数字和 0 组成的数组。 s 并将它们相加:

=SUM(MMULT(N(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1))),TRANSPOSE(COLUMN(A1:E1)^0)))

这是一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。



编辑:想多了，这要简单得多:

=SUM(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1)))

仍然是一个数组公式。