我正在尝试找出一个公式来找到一列的最大值,然后使用第二列和第三列的值来解决平局,然后在不同的单元格中显示与该数据相关的人员姓名。
我提供了带有测试数据的图像来尝试说明我的需求:
橙色框的右侧显示了根据我想要使用的条件排名前 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 列中的任何行匹配。