使用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屋！