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

How to get the highest values from 2 columns in Excel?

提问by user3151946

I have a design software which extracts data in to an Excel sheet format The output is divided into 2 columns, each of these columns has more than 1000 rows. To make use of this data I need to summarize it to a maximum of the 5 highest values from both of the 2 columns. Therefore, this doesn't mean that it's the maximum of one column and its corresponding value, but it may mean that the 2nd largest value of column 1 & the 4th largest value of column 2.

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

For example ( if we quoted some of the output data):

例如（如果我们引用了一些输出数据）：

The values i should pick here are:

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

If there is any possible way to achieve that, it will be great

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

Thanks ..

谢谢 ..

example file: http://goo.gl/UIEFEv

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

example file 2: http://goo.gl/VSvuVf

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

回答by barry houdini

Here's a formula solution. I used 20 rows and extracted the rows which contain the top 5 for each column - you can extend to as many rows as required.

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

With data in A1:B20 use this formula in D1 confirmed with CTRL+SHIFT+ENTERand copied across to E1 and down both columns:

在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))),"")

=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))),"")

Note: there are only eight rows extracted because some of the rows contain values in the top 5 for both columns. I added the highlighting in colums A and B to more clearly illustrate

注意：只提取了 8 行，因为有些行包含两列的前 5 行中的值。我在 A 和 B 列中添加了突出显示以更清楚地说明

see screenshot below

看下面的截图

Edit:

编辑：

From the comments below it seems that you want a combination of rows which contain the highest value for that column....and rows which contain the highest totalfor bothcolumns.

从下面的评论看来，您想要组合包含该列最高值的行......以及包含两列最高总数的行。

In the original formula there are two conditions joined with "+", i.e.

原公式中有两个条件用“+”连接，即

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

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

The "+" gives you an "OR"type functionality, e.g. in this case rows are included if individual values are in the top 5 in that particular column. You can add other conditions, so if you want to also add any rows which are in the top 5 considering the total of both columns then you can add another "clause", i.e.

“+”为您提供了"OR"类型功能，例如，在这种情况下，如果单个值位于该特定列的前 5 位，则包括行。您可以添加其他条件，因此如果您还想添加考虑到两列总数的前 5 行中的任何行，那么您可以添加另一个“子句”，即

($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))

($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))

....and including that in the complete formula you get this version:

....并将其包含在完整的公式中，您将获得此版本：

=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))),"")

=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))),"")

You could refine that further by using combinations of +and *(for AND), e.g. for the new condition you might only want to include rows with a total in the top 5 if one of the single values is in the top 10 for that column...

您可以通过使用+和*(for AND) 的组合进一步细化，例如，对于新条件，如果单个值之一在该列的前 10 名中，则您可能只想包含总和在前 5 名中的行...

Explanation:

解释：

The above part shows how you can use +for the ORconditions. In the formula if those conditions are TRUEthen the IFfunction returns the "relative row number" of the range (using ROW(A$1:A$20)-ROW(A$1)+1).

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

SMALL function then extracts the kth smallest value, k being defined by ROWS(D$1:D1) which starts at 1 in D1 (or E1) and increments by 1 each row.

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

INDEXfunction then takes the actual value from that row.

INDEX函数然后从该行中获取实际值。

When you run out of qualifying rows SMALLfunction will return a #NUM!error which IFERRORhere converts to a blank

当您用完符合条件的行时，SMALL函数将返回一个#NUM!错误，IFERROR此处将其转换为空白

回答by John Dirk Morrison

The question is a little unclear but if what you mean is to get the 5 highest values of Column A and their corresponding values in Column B then the five highest values in Column B and the corresponding values in Column A then the (non automated) solution is pretty simple.

问题有点不清楚，但是如果您的意思是要获得 A 列的 5 个最高值及其在 B 列中的相应值，那么 B 列中的五个最高值和 A 列中的相应值则是（非自动化）解决方案很简单。

1. Click on a cell with a header title in it.
2. Click on 'Data' in the top menu.
3. Click on 'Filter' in the 'Sort & Filter' section.
4. Click on the button on Column A - select 'Sort Largest to Smallest'
5. Grab the top five values from both columns then click on the button in column B and repeat.

1. 单击带有标题标题的单元格。
2. 单击顶部菜单中的“数据”。
3. 单击“排序和过滤器”部分中的“过滤器”。
4. 单击 A 列上的按钮 - 选择“从大到小排序”
5. 从两列中获取前五个值，然后单击 B 列中的按钮并重复。