如何对Google Spreadsheets中每一列的最大值求和? [英] How to sum the maximum value of each column in Google Spreadsheets?

问题描述

我有一个Google电子表格数字.如何从每个列中获取最大值，并仅使用一个公式对它们进行汇总?(没有临时单元，没有脚本.)

I have a Google Spreadsheet of numbers. How do I take the maximum value from each column, and summarize them using only one formula? (No temp cells, no scripts.)

1  2  1
0  1  3
0  2  0

对于上表，结果应为6(1 + 2 + 3，每列的最大值).但是我也想要一种适用于更大表的解决方案.

For the table above the result should be 6 (1+2+3, the maximum value of each column). But I'd like a solution that works for much larger tables, too.

作为一个更普遍的问题，我想了解如何使用任意运算符(如 MAX 和 SUM )将2D范围折叠为1D数组.情况).

As a more general question, I'd like to find out how I could fold 2D ranges into 1D arrays using an arbitrary operator (like MAX and SUM in this case).

推荐答案

假设您的数据在A2:D范围内，以获取每一行的最大值(数组输出)，请尝试

Assuming your data in range A2:D, to get the maximum of every row (array output) try

=query(transpose(query(if(row(A2:D)>=transpose(row(A2:D)),transpose( A2:D)),"select max(Col1),max(Col2),max(Col3),max(Col4) ",0)),"Select Col2", 0)

如果您需要处理很多列，可能会更好

If you need to process a lot of columns, this may be better

=ArrayFormula(QUERY(TRANSPOSE(QUERY(TRANSPOSE( A2:D) , "Select "&"MAX(Col"&JOIN( ", MAX(Col",ROW(INDIRECT( "YY1:YY"&ROWS(A2:A)))&")"))), "Select Col2", 0))

总而言之，只需将SUM()包裹在以上公式中即可.

To sum, just wrap SUM() around the above formulas.

这篇关于如何对Google Spreadsheets中每一列的最大值求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！