excel - 如何从 Excel 中的 2 列中获取最高值？

Question

我有一个设计软件，可以将数据提取为 Excel 表格格式
输出分为 2 列，每列有 1000 多行。
为了利用这些数据，我需要将它总结为两列中最多 5 个最高值。因此，这并不意味着它是一列的最大值及其对应的值，而是可能意味着第 1 列的第 2 个最大值和第 2 列的第 4 个最大值。

例如(如果我们引用了一些输出数据):



我应该在这里选择的值是:



如果有任何可能的方法来实现这一点，那就太好了

谢谢 ..

示例文件:http://goo.gl/UIEFEv

示例文件 2 : http://goo.gl/VSvuVf

Answer 1

这是一个公式解决方案。我使用了 20 行并提取了每列包含前 5 行的行——您可以根据需要扩展到任意多的行。

对于 A1:B20 中的数据，在 D1 中使用此公式，通过 CTRL+SHIFT+ENTER 确认并复制到 E1 并向下复制两列:
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
注意:仅提取了 8 行，因为其中一些行包含两列的前 5 行中的值。我在 A 列和 B 列中添加了突出显示以更清楚地说明

看下面的截图



编辑:

从下面的评论中，您似乎想要包含该列最高值的行的组合......以及包含最高 的行。总计 对于 两个列。

在原始公式中，有两个条件用“+”连接，即
($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)
“+”给你一个 "OR"类型功能，例如在这种情况下，如果单个值位于该特定列的前 5 位，则包含行。您可以添加其他条件，因此如果您还想添加前 5 名中的任何行，考虑到两列的总数，那么您可以添加另一个“子句”，即

($A$1:$A$20>=大($A$1:$A$20,5))+($B$1:$B$20>=大($B$1:$B$20,5) + ($A$1:$A$20+$B$1:$B$20>=大($A$1:$A$20+$B$1:$B$20,5))

....并将其包含在完整的公式中，您将获得此版本:
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5))+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
您可以通过使用 + 的组合进一步细化它。和 * (对于 AND )，例如对于新条件，如果单个值之一在该列的前 10 名中，则您可能只想包含总计在前 5 名中的行...

解释:

以上部分展示了如何使用 +对于OR条件。在公式中，如果这些条件是 TRUE然后 IF函数返回范围的“相对行号”(使用 ROW(A$1:A$20)-ROW(A$1)+1 )。

然后，SMALL 函数提取第 k 个最小值，k 由 ROWS(D$1:D1) 定义，该值从 D1(或 E1)中的 1 开始，每行递增 1。
INDEX然后函数从该行获取实际值。

当您用完符合条件的行时SMALL函数将返回 #NUM!错误 IFERROR这里转换为空白