excel函数得分卡_星期五的Excel函数：前10名平均得分-CSDN博客

excel函数得分卡

If you have a worksheet with 20 scores listed, how can you calculate an average top 10 scores? And if there are only 11 scores, can the formula automatically adjust, to average just the top 4 scores?

如果您的工作表列出了20个分数，那么如何计算平均前10个分数？并且，如果只有11个分数，公式是否可以自动调整以平均仅前4个分数？

从一个简单的问题开始 (Start With a Simple Problem)

Someone emailed me for help with this question, so let's start with the simple problem – top 10 of 20 scores. Then we'll make a flexible solution, for the tougher challenge.

有人通过电子邮件向我寻求帮助，所以让我们从一个简单的问题开始-20分中的前10名。然后，我们将为更严峻的挑战提供一个灵活的解决方案。

查找最高或最低分数 (Find the Highest or Lowest Scores)

One approach to the problem is to create a table, and find the top 10 scores. In this example, the numbers 1-10 are typed in column E.

解决该问题的一种方法是创建表格，并找到前10个得分。在本示例中，数字1-10在E列中键入。

In cell F2, the SMALL function finds the first smallest score, in the list of scores:

在单元格F2中，SMALL函数在分数列表中找到第一个最小的分数：

=SMALL($B$2:$B$21,$E2)

=小($ B $ 2：$ B $ 21，$ E2)

To find the largest scores, the LARGE function is used, with this formula in cell G2:

为了找到最高分，使用LARGE函数，并在单元格G2中使用以下公式：

=LARGE($B$2:$B$21,$E2)

=大($ B $ 2：$ B $ 21，$ E2)

Then, the AVERAGE function calculates the average of those top 10 scores.

然后，AVERAGE函数计算前10个得分的平均值。

ROW单细胞配方 (One Cell Formula With ROW)

Instead of creating a table, with numbers 1-10 typed in a column, we can use an array-entered formula.

我们可以使用输入数组的公式来代替创建表(在列中键入数字1-10)。

In cell F6, the AVERAGE and LARGE functions are combined with the ROW function.

在单元格F6中，AVERAGE和LARGE函数与ROW函数结合在一起。

=AVERAGE(LARGE($B$2:$B$21,ROW(1:10)))

=平均(大($ B $ 2：$ B $ 21，ROW(1:10)))

After typing the formula, press Ctrl+Shift+Enter to array-enter the formula.

键入公式后，按Ctrl + Shift + Enter数组输入公式。

If you highlight the ROW(1:10) part of the formula, and press F9, to calculate, you'll see the list of 10 numbers.

如果突出显示公式的ROW(1:10)部分，然后按F9键进行计算，则会看到10个数字的列表。

This formula produces the same result as the original table, but in a single cell.

该公式产生的结果与原始表相同，但在单个单元格中。

最高分查询表 (Lookup Table of Top Scores)

Ideally, each person will have 20 scores, and the top 10 will be averaged. However, if a person has fewer than 20 scores, there is a lookup table that shows the number of top scores to average.

理想情况下，每个人将获得20分，并且将对前10名进行平均。但是，如果一个人的分数少于20，则有一个查找表，该表显示平均得分最高的分数。

For example, if there are only 11 scores available, then the top 4 will be averaged.

例如，如果只有11个分数可用，则将对前4个分数进行平均。

To find the number of top scores to average, we can use a VLOOKUP formula. This formula counts the scores in column B, and finds that number in the lookup table, which is named CountLU.

要找到平均得分最高的分数，我们可以使用VLOOKUP公式。此公式计算B列中的分数，并在查找表中找到该数字，该表名为CountLU。

=VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE)

= VLOOKUP(COUNT($ B $ 2：$ B $ 21)，CountLU，2，TRUE)

创建灵活的ROW范围 (Create a Flexible ROW Range)

Instead of using a set range for the ROW reference:

而不是将设置范围用于ROW参考：

=AVERAGE(SMALL($B$2:$B$21,ROW(1:10)))

=平均(小($ B $ 2：$ B $ 21，ROW( 1:10 )))

we can create a flexible range.

我们可以创建一个灵活的范围。

With the INDEX function, you can create a range that has its number of rows set by the VLOOKUP result.

使用INDEX函数 ，您可以创建一个范围，该范围的行数由VLOOKUP结果设置。

=AVERAGE(SMALL($B$2:$B$21, ROW($A$1:INDEX($A:$A, VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE)))))

= AVERAGE(SMALL($ B $ 2：$ B $ 21，ROW( $ A $ 1：INDEX($ A：$ A，VLOOKUP(COUNT($ B $ 2：$ B $ 21)，CountLU，2，TRUE)) )))))

Or, use the INDIRECT function, with R1C1 style, to set a reference to the rows.

或者，使用INDIRECT函数 (具有R1C1样式)来设置对行的引用。

=AVERAGE(SMALL($B$2:$B$21, ROW(INDIRECT("R1:R" & VLOOKUP(COUNT($B$2:$B$21),CountLU,2,TRUE),FALSE))))

= AVERAGE(SMALL($ B $ 2：$ B $ 21，ROW( INDIRECT(“ R1：R”＆VLOOKUP(COUNT($ B $ 2：$ B $ 21)，CountLU，2，TRUE)，FALSE) )))

Now, the number of top scores will change automatically, based on the number of scores available.

现在，最高分数的数量将根据可用分数的数量自动更改。

下载平均得分最高的10个工作簿 (Download the Average Top 10 Scores Workbook)

To see the formulas for averaging the top 10 scores, you can download the Average Top Scores sample file. The workbook is in Excel 2007 format, and is zipped. There are no macros in the file.

要查看平均10个最高得分的公式，您可以下载平均最高得分示例文件。该工作簿为Excel 2007格式，并且已压缩。该文件中没有宏。