Using MIN() inside ARRAYFORMULA()

问题:

I've seen some examples of using SUM() inside an ARRAYFORMULA() in Google Spreadsheets (and oddly enough, they all seem like workarounds) but I can't figure out how to apply them to using MIN() instead.

Let's say I have columns A, B and C and I just want to get the result of MIN(A:C) on the D column, just for the three cells that would match each row. The straightforward way should be ARRAYFORMULA(MIN(A1:C)) but surely enough that doesn't work.

How can I programmatically calculate the MIN() of some cells within a row, for all the rows in a Google Spreadsheet?

回答1:

MIN() always returns a single value, regardless of the range size, so ARRAYFORMULA() does not change the output - it only helps convert formulas that do not handle a range.

As a quick answer, you could simply rewrite the logic of MIN() using something like IF(): =ARRAYFORMULA(if(A:A < B:B, if (A:A < C:C, A:A, C:C), if(B:B < C:C, B:B, C:C)))

回答2:

in D1 try this workaround/formula:

=index(ArrayFormula(transpose(query(transpose(A:C),"select "&join("),","min(Col"&row(indirect("A1:A"&count(A:A))))&")"))),,2)

and see if that works ?

Basically this

• first transposes numbers in A:C
• calculates the minimum for each column (QUERY function)
• then those minimum values are transposed back into rows

回答3:

=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C), 
 "select "&REGEXREPLACE(JOIN( , ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C), 
 "min(Col"&ROW(A1:A)-ROW(A1)+1&"),", ""))), ".\z", "")&"")),
 "select Col2")