在Excel数组的每一行或每一列中查找最大值 [英] Find maximum value in each row or column of an Excel array

问题描述

在Excel中，我有一个包含数组的命名范围，例如下面的数组.

In Excel I have a named range that contains an array, for example like the array below.

0 0 0 7
0 0 6 5
0 5 3 2
4 3 2 1

我想在数组的每一行(或每一列)中找到最大值.对于上面数组中的列，我希望结果为:

I'd like to find the maximum value in each row (or column) of the array. For the columns in the array above I want the result to be:

Array={4,5,6,7}

如果有帮助，则最大值始终将是列的最高编号，而行的最左侧编号.

If it helps, the maximum is always going to be the topmost number for a column and leftmost number for a row.

我想要一个工作表公式，而不是VBA函数.

I would like a worksheet formula rather than a VBA function.

推荐答案

使用命名范围Rng和原始帖子中发布的值尝试此公式.

With a named range Rng and values as posted in original post try this formula.

=SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng)))

它返回每个列的最大值的数组. {4,5,6,7}

It returns an array of max of each column {4,5,6,7}

这将返回每一行的最大值.

This returns a max of each row.

=SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),ROW(Rng)-MIN(ROW(Rng)),,,COLUMNS(Rng)))

和数组 {7; 6; 5; 4}.

这篇关于在Excel数组的每一行或每一列中查找最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！