☰

与前一个数组进行比较，计算最大值

tag-icon • tag-icon microsoft-excel worksheet-function array
与前一个数组进行比较，计算最大值

我尝试计算一行中等于或大于上面一列（部分）的 MAX() 值的值的数量。

例如：与自身上方的 3 个单元格相比。E 中显示“计数”。我用 * 标记了应计数的单元格。

在此处输入图片描述

我的最终数组大约是 200x5000...我已经尝试过（例如单元格 E4）类似这样的内容：

{=SUM(IF(A4:D4>=MAX(OFFSET(A1,ROW($1:$4)-1,0,3,1)),1,0))}

{=COUNTIF(A4:D4,">="&MAX(OFFSET(A1,0,ROW($1:$4)-1,3,1)))}

希望 ROW() 和 OFFSET() 函数能将 MAX 函数拆分到列上...但都没有给出令人满意的结果。我当然可以在另一张表上将 MAX() 拆分到 200 个不同的列上。但我做噩梦了，因为我无法在单个数组公式中完成它。

答案1

如果您严格寻找基于工作表函数的方法，可能这不是推荐的解决方案，但是在这里我使用了一个小的 UDF（用户定义函数）来简化任务。

此 UDF 接受一个范围并返回该范围内每列的最大值数组。

按ALT+F11访问 VBA 编辑器。从插入菜单插入一个模块。现在将以下代码放入其中。

Function Ret_Arr(inpt As Range) As Variant


Dim v As Variant

ReDim v(1 To inpt.Columns.Count)

For i = 1 To inpt.Columns.Count

    v(i) = Application.Max(inpt.Columns(i))

Next i

Ret_Arr = v


End Function

请注意，为了以防万一，没有在这个基本代码中放入太多验证以使其更加健壮。

这将获取范围并从每列返回一个最大值数组，您可以在数组公式中使用该数组。

如果您使用的是 Excel 2007 及更高版本，请将 VBA 代码和工作表保存为 XLSM 启用宏的工作表。

在此示例中，样本数据位于单元格 A1:D8 中。在 E2 中输入以下公式，然后按公式栏中的CTRL+ SHIFT+ENTER创建数组公式。

公式应括在花括号中，以表明它是一个数组公式

=SUM(IF($A2:$D2>=Ret_Arr($A$1:D1),1,0))

现在，对于 Ret_Arr 上方的每个范围，应返回每列的最大值数组。如果您没有将正确的范围传递给 Ret_Arr，则可能会得到错误的结果。因此，如果您要讨论的列是 A 到 D，请确保将 A 到 D 的相同范围传递给 UDF。

在此处输入图片描述

答案2

在E4，数组公式**：

=COUNT(1/(MMULT(TRANSPOSE(ROW(A$1:D3)^0),N(A4:D4>=A$1:D3))=ROWS(A$1:D3)))

按要求抄下来。

问候

**数组公式的输入方式与“标准”公式不同。您不必直接按 ENTER，而是先按住 CTRL 和 SHIFT，然后按 ENTER。如果您操作正确，您会注意到 Excel 在公式周围放置了花括号 {}（但不要尝试自己手动插入这些括号）。

相关内容