☰

如何在 Excel 中的多列中查找最大单元格值？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中的多列中查找最大单元格值？

我正在尝试找出一个公式来找到一列的最大值，然后使用第二列和第三列的值来解决平局，然后在不同的单元格中显示与该数据相关的人员姓名。

我提供了带有测试数据的图像来尝试说明我的需求：

示例数据

橙色框的右侧显示了根据我想要使用的条件排名前 5 的人，基本上，我希望框 1 显示在字段 K 中值最高的人，然后是框 2 中第二高的人，等等。如果框 K 中的值相同，我希望总计列中的值作为决胜局，如果这仍然不能打破平局，我希望使用列 I 作为最后的决胜局。

显然，我希望保持表中的排序不变，并且表中的值会定期更改（因此，手动将所有数据复制到辅助数据表以使用排序函数是行不通的，除非该过程可以自动化。）任何帮助都将不胜感激。我尝试了 VLOOKUP、INDEX 和 MAX 函数的变体，但都没有任何效果。

答案1

我会用：

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),2)

对于名称和：

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),11)

表示 K 列中的分数。

使用按照公式对数据进行排序SORTBY()，并将该排序数据集用作FILTER()的输入范围。这样无需付出任何实际努力，即可设置数据以遵循您的决胜局，而且奇怪的是（每个带有FILTER()“奇怪”的奇怪事物都会被描述为），MIN()如果没有设置排序，使用 就无法正常工作（正如我所写的那样）。因此，这就是为什么SORTBY()在将数据范围传递给 之前FILTER()。

为了使函数中的条件FILTER()起作用，条件范围也必须进行排序，然后设置>=为 K 列中最大的五个结果。

由于某种原因，我没有花太多时间去弄清楚，而是花了很多时间去克服，直接将 K 列分数与五个最大的 K 列值进行比较是行不通的。但是使用LARGE()查找最高的五个，然后MIN()选择其中最低的，然后比较排序列 K 值作为FILTER()标准确实有效。所以你看。

结果，在 Excel 内部生成了一个按所需顺序排列的表格值表，并将其截断为五行。使用INDEX()允许选择感兴趣的列，第 2 列用于获取名称，第 11 列用于获取 K 列分数。因此，这两个公式仅在其末尾的列不同（该列传递给 ）INDEX()，用于获取输出表的一列名称和另一列的 K 列分数。

请记住，如果其他人在所有三列中都与 Joe 匹配，如果该人在源范围内高于 Joe，那么他将排在第 5 位，而 Joe 将排在第 6 位。反之亦然，如果他在源范围内低于 Joe。该公式无法以任何方式处理这种情况，我的意思是完全无法处理。

但它确实按照字面要求做了。

这也表明，人们不需要解决困难，因为这样他们就能够做他们尝试过的事情。人们通常可以简单地克服困难，然后继续前进，而不知道是什么让逻辑方法奏效。我总是喜欢解决每件事，但这是一个现实的世界，工作成果通常比花费大量时间的完美成果更可取。所以人们经常不得不妥协，把理解推迟到另一天。这很有效……

答案2

用一个准则组合策略对最重要的统计数据进行加权（@fixer1234），得到以下公式选择。

此公式在以下情况下有效：

  • 应用第三个标准后，平局是不可能的。

    =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, 0))

  • 名称是唯一的（或者匹配的名称并不符合所有 3 个条件）。

    =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $D$23:$D23, $C$2:$C$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))

  • 始终通过将结果作为用户 ID 返回（辅助列从 开始E24）。

    =INDEX( $B$2:$B$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $E$23:$E23, $B$2:$B$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))

  • 对于名称，使用公式返回的用户 ID =VLOOKUP($E24,$B$2:$C$20,2)。$E24

如何使用

  • 无论选择哪个主公式，都将其粘贴到结果的第一行。这些是 CSE 公式，因此在输入公式或进行更改后，请按Ctrl- Shift- Enter。
  • 将公式复制下来拖拽。这些公式不返回数组/范围，如果输入数组公式并选择所有 5 个单元格，则这些公式不起作用。
  • $E$23:$E23此公式和唯一名称公式中均有$D$23:$D23扩展范围，范围从公式第一行上方的单元格开始。扩展范围的第一个引用单元格不得与 ids 列或 names 列中的任何行匹配。

相关内容