使用 ARRAYFORMULA() 获取每行的 MAX() [英] Getting MAX() of each row with an ARRAYFORMULA()

问题描述

使用数组公式，我想找到一个范围内每一行的最大值，并得到结果范围以进一步处理它.

Using an array formula I want to find the max value of each row of a range and get the resulting range to work with it further.

只要我添加了 MAX() 语句，问题就会出现，因为它在数组公式中的行为似乎很奇怪.即使您的命令会在 MAX() 语句中为您提供多个值，它也始终只返回一个值.

The problem occurs as soon as I add the MAX() statement since it does seem to behave strangely within an array formula. Even if you ad commands which will give you multiple values within the MAX() statement it does always only return one single value.

例如这将为您提供我想要获得最大值的范围:

E.g. this will give you the ranges wich I want to get the max of:

=ARRAYFORMULA(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))

结果如下:

F1:I1
F2:I2
F3:I3
F4:I4
F5:I5
F6:I6
F7:I7
F8:I8
F9:I9
F10:I10
F11:I11 

如果我现在添加 INDIRECT() 使它们成为实际范围并添加 MAX() 它应该返回每个范围的最大值，因为数组公式应该像以前一样遍历 ROW(E1:11).然而，这个新公式的结果

If I now add INDIRECT() to make those to actual ranges and add MAX() it should return the max of each of those ranges since the array formula should go through the ROW(E1:11) as it did bevor. However, the result of this new formula

=ARRAYFORMULA(MAX(INDIRECT(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))))

而是一个单一的值，第一个范围的最大值.

rather is one single value, the maximum of the first range.

我什至试图通过为数组公式添加一个 IF() 语句来遍历行来绕过这个问题.这样做，它确实给了我所有 11 行的结果，但是，结果总是相同的(第一行的最大值).

I have even tried to bypass the problem by adding an IF() statement for the array formula to iterate through the rows. Doing so, it did give me a result for all 11 rows, however, the result always was the same (the max of the first row).

新公式:

=ARRAYFORMULA(IF(ROW(E1:E11) = ROW(E1:E11); MAX(INDIRECT(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))); ""))

新的结果(左列是公式的结果，试图将每一行的最大值放在它的右边):

The new result (left column are the results of the formula, trying to get the max of each row to its right):

10  7   10  4   1
10  10  8   1   2
10  4   5   9   4
10  10  10  2   2
10  10  10  5   10
10  10  6   9   5
10  4   5   7   3
10  6   9   4   7
10  5   5   7   3
10  9   2   3   10
10  10  3   9   10

推荐答案

=QUERY(TRANSPOSE(QUERY(TRANSPOSE(F1:I), 
 "select "&REGEXREPLACE(JOIN( , ARRAYFORMULA(IF(LEN(F1:F&G1:G&H1:H&I1:I), 
 "max(Col"&ROW(F1:F)-ROW(F1)+1&"),", ""))), ".\z", "")&"")),
 "select Col2")

这篇关于使用 ARRAYFORMULA() 获取每行的 MAX()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！