时间戳

首页 > 解决方案 > 在不是单元格引用的 Excel 数组的每一行或每一列中查找最大值

excel - 在不是单元格引用的 Excel 数组的每一行或每一列中查找最大值

问题描述

我需要在 LET 调用内部的数组中找到一列的最大值，这不是使用一个单元格的单元格引用。

这适用于作为单元格引用的数组，但是当我尝试在不是单元格引用的数组上使用它时，它会失败。

例如，使用此数据。

12  2  3
 3  7  5
 7  8  9

=LET(Rng,$A$1:$C$3,SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng))))如您所料返回 [12, 8, 9] 但=LET(Rng,$A$1:$C$3*1,SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng))))返回#VALUE

这可以在单个单元格中完成还是我被迫使用多个单元格？

编辑：所需的输出是

12  8  9

我有一个适用于绑定到单元格的数组的公式，我没有一个适用于不绑定到单元格的数组的公式，例如LET

编辑 2：最终我需要一些适用于 36x36 阵列的东西。

标签： excelexcel-formulaoffice365dynamic-arrays

解决方案

这是使用更传统的索引和排序的替代方法：

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(A1:C3,MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

这个想法是将二维数组转换为一维数组（col），然后首先按原始数组中的列号对其进行排序，然后再按数组中的值排序。最后从结果数组中提取每三个元素。这分别显示了这些步骤：

在此处输入图像描述

这是总体结果：

在此处输入图像描述

我忘记了问题的重点是它应该适用于数组和范围：

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(A1:C3*1,MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

或者

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(RANDARRAY(3,3),MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

矩形阵列最大列数的一般形式（例如 4 行 x 3 列）

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(1,c,r,r),col,
INDEX(arr,MOD(sa,r)+1,QUOTIENT(sa,r)+1),sortcol,SORTBY(col,QUOTIENT(sa,r),1,col,1),INDEX(sortcol,sb))

对于 min，只需更改排序顺序：

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(1,c,r,r),col,
INDEX(arr,MOD(sa,r)+1,QUOTIENT(sa,r)+1),sortcol,SORTBY(col,QUOTIENT(sa,r),1,col,-1),INDEX(sortcol,sb))

矩形阵列最大行数的一般形式

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(r,1,c,c),col,
INDEX(arr,QUOTIENT(sa,c)+1,MOD(sa,c)+1),sortcol,SORTBY(col,QUOTIENT(sa,c),1,col,1),INDEX(sortcol,sb))

再次为 min 更改排序顺序：

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(r,1,c,c),col,
INDEX(arr,QUOTIENT(sa,c)+1,MOD(sa,c)+1),sortcol,SORTBY(col,QUOTIENT(sa,c),1,col,-1),INDEX(sortcol,sb))

推荐阅读