筛选一行中最小值并显示列名_筛选行中的前十个值

筛选一行中最小值并显示列名

If I apply an AutoFilter to see the Top 10 Sunday sales in a list, why does Excel just show me the Top 2? Here's how the Top Ten values in filtered rows feature works.

如果我应用“自动筛选”以查看列表中排名前10位的周日销售额，为什么Excel仅向我显示前2位？ 这是过滤后的行中的前十个值功能的工作方式。

样本Excel表 (Sample Excel Table)

In the screen shot below, you can see the first few rows of the table. The values are sorted highest to lowest, and the Sunday values are colored, so they're easy to spot.

在下面的屏幕快照中，您可以看到表格的前几行。 这些值按照从高到低的顺序排序，并且星期日值是彩色的，因此很容易发现它们。

In the top 10 rows, there are 2 Sunday rows, and there are another 22 Sundays in the remaining rows. The rows are numbered in column D, to show each value’s overall position.

在前10行中，有2个星期日行，其余行中还有22个星期日。 这些行在D列中编号，以显示每个值的整体位置。

按天筛选 (Filter By Day)

To see only the Sunday records, I’ll filter the Day column. At the bottom of the screen, you can see that 24 of the records are for Sundays.

要仅查看星期日记录，我将过滤“日期”列。 在屏幕底部，您可以看到24条记录是针对星期日的。

添加前10个过滤器 (Add a Top 10 Filter)

Next, add a filter on the Qty column, to keep only the Top 10 of those records.

接下来，在“数量”列上添加一个过滤器，以仅保留这些记录的前10名。

Uh-oh! Instead of keeping the highest 10 Sundays, there are only 2 records left after the filter. It only kept the Sundays that are in the overall Top 10.

哦！ 除了保留最高的10个星期日之外，筛选器之后仅剩下2条记录。 它只保留了前十名中的星期日。

添加新列 (Add a New Column)

I don’t want just the Sundays that are in the overall Top 10 – I want the Top 10 Sundays.

我不想只排在前十名的星期日，而是想要前十名的星期日。

So, the built-in filter doesn’t do what I need, but I can add another column, to do the job.

因此，内置过滤器无法满足我的需要，但是我可以添加另一列来完成这项工作。

• First, clear all the filters, so all the rows are visible.
  首先，清除所有过滤器，以便所有行均可见。

• • = - - SUBTOTAL(9,C6)

    =--小计(9，C6)

  • NOTE: There are 2 minus signs after the equal sign. I included spaces, so they’re a little easier to see. The formula will work with or without the spaces.
    注意：等号后有2个减号。 我加入了空格，因此较容易看到。 该公式将在有或没有空格的情况下工作。

  Then, add another column to the table, with a SUBTOTAL formula. Here’s the formula in cell E6:

  然后，使用SUBTOTAL公式将另一列添加到表中。 这是单元格E6中的公式：

• Copy the formula down to the last row of data
  将公式复制到数据的最后一行

应用过滤器 (Apply the Filters)

Now that the new column has been created, try the filter again.

现在已经创建了新列，请再次尝试过滤器。

• First, apply the Sunday filter in the Day column, to see the 24 Sunday records.
  首先，在“日期”列中应用“星期日”过滤器，以查看24个星期日记录。

• Next, instead of filtering the Qty column, apply the Top 10 filter on the Test column.

  接下来，不对“数量”列进行过滤，而对“ 测试”列应用前10个过滤器。

Success! All 10 of the top Sunday results are shown in the filtered results.

成功！ 过滤后的结果将显示所有前10个星期日最佳结果。

这个怎么运作 (How It Works)

To see how this works, I’ll remove the filters from the list, and add a link to cell E6, where the SUBTOTAL formula with highest quantity is stored.

要查看其工作原理，我将从列表中删除过滤器，并添加一个指向单元格E6的链接，单元格E6中存储着数量最多的SUBTOTAL公式。

Row 6 is visible, and cell G4 shows the top amount of $1000.

第6行可见，单元格G4显示$ 1000的最高金额。

Now, filter the Day column for Sunday, and the amount in cell G4 changes to zero.

现在，过滤星期天的“天”列，单元格G4中的数量变为零。

The SUBTOTAL function only calculates a sum for visible rows, and Row 6 is no longer visible, so the current result is zero.

SUBTOTAL函数仅计算可见行的总和，而第6行不再可见，因此当前结果为零。

Only the Sunday rows will have a TEST value that is above zero, so when the Top 10 filter is applied on the TEST column, the Sunday rows have the highest values.

仅星期日行的TEST值将大于零，因此在“测试”列上应用前10个过滤器时，星期日行的值最高。

Of those Sunday values, only the Top 10 are visible, after the filter is applies.

在应用过滤器后，在那些星期天值中，只有前十名可见。

使用AGGREGATE函数 (Use the AGGREGATE Function)

If you’re using Excel 2010 or later, you can use an AGGREGATE formula, instead of SUBTOTAL. The functions are similar, but AGGREGATE has many more options than there are in SUBTOTAL.

如果您使用的是Excel 2010或更高版本，则可以使用AGGREGATE公式代替SUBTOTAL。 功能相似，但是AGGREGATE的选项比SUBTOTAL中的选项更多。

Here is the formula that you could use in cell E6:

这是您可以在单元格E6中使用的公式：

=AGGREGATE(9,3,C6)

= AGGREGATE(9,3，C6)

You can read more about the AGGREGATE function here.

您可以在此处阅读有关AGGREGATE函数的更多信息。

下载样本文件 (Download the Sample File)

To see the Top 10 filters with SUBTOTAL and AGGREGATE, you can download the sample file from my website. On the Sample Files page, go to the Functions section, and look for FN0033 – Show Top 10 For Filtered Items.

要查看SUBTOTAL和AGGREGATE的前10个过滤器，您可以从我的网站下载示例文件。 在“样本文件”页面上，转到“功能”部分，然后查找“ FN0033 –显示筛选出的项目的前10名” 。

The zipped file is in xlsx format, and does not contain macros.

压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2014/03/11/top-ten-values-in-filtered-rows/

筛选一行中最小值并显示列名