从数据源提取符合条件的数据，并按照数值由大到小排序

从数据区域提取符合条件的数据，并按照由大到小排序，可以先筛选出符合条件的数据，然后将筛选出的数据粘贴到目标区域，最后再进行排序。使用这种方法比较简单，不过缺点是，当数据更新时，需要重复执行筛选排序的操作。

本文接下来介绍用函数法和power query法，从数据源提取符合条件的数据，并自动排序。使用这两种方法的优点是，当数据更新时，不用重复操作就可以更新结果。

本文案例演示使用的是Excel2016版本，使用的函数为SUMPRODUCT、MATCH、INDEX函数。如果使用的是OFFICE365版本，可以使用FILTER+SORT函数。

1

案例描述

如下图所示，A1:B8为成绩表。要求提取成绩大于等于80的记录，且提取的记录按成绩由高到低排序，结果如E1:F5所示。

2

函数法

1、在C列构建辅助列，并在C2单元格输入以下公式：

=IF(B2>=80,SUMPRODUCT(--($B$2:$B$8>B2))+COUNTIF($B$2:B2,B2),"")

拖动C2单元格填充柄，向下复制公式。

公式解析：

（1）SUMPRODUCT(--($B$2:$B$8>B2))，计算B2:B8区域中大于B2单元格中数值的个数。

（2）COUNTIF($B$2:B2,B2)，计算B2单元格的值在$B$2:B2中出现的次数。B2单元格数值为“88”，该数值第1次出现，因此COUNTIF($B$2:B2,B2)=1。B4单元格数值也为“88”，但是第2次出现，因此因此COUNTIF($B$2:B4,B4)=2。

（3）SUMPRODUCT+COUNTIF函数得到的结果，实际是B2单元格的数值在B2:B8中的排序，如下图所示。B6单元格的数值为“91”，数值最大，因此排序为“1”；B2单元格的数值为“88”，仅次于B6单元格的值，因此排序为“2”；而B4单元格的数值和B2单元格相同，也为“88”，但是由于是第2次出现，因此排序为“3”。

（4）IF函数的作用是，当B列中的数值小于80时，在C列中不显示排序号，显示为空文本。

2、在E2单元格输入以下公式：

=IFERROR(INDEX(A$2:A$8,MATCH(ROW()-1,$C$2:$C$8,0)),"")

拖动E2单元格填充柄，将公式复制到F2单元格，并拖动填充柄，将E2、F2单元格公式向下复制。

E2、F2单元格的公式是INDEX+MATCH函数组合用于查找的经典用法，不再赘述。想要了解更多INDEX+MATCH函数组合，可以阅读文章：INDEX+MATCH函数用于查询的6种典型用法

3、按快捷键【Ctrl+T】，将数据源A1:B8转为超级表。

当数据源中新增记录时，函数返回的结果会自动更新，如下图所示。

3

power query方法

使用power query法非常简单。

1、选中A1:B8任意单元格，单击【数据】-【从表格】。

在弹出的【创建表】对话框中，单击确定。

2、在打开的power query编辑器中，单击【成绩】列的筛选按钮，选择【数字筛选器】-【大于或等于】。

在打开的【筛选行】对话框中，输入筛选条件“80”。然后单击确定。

3、再次单击【成绩】列的筛选按钮，选择【降序排序】。

得到的结果如下图所示：

4、单击【关闭并上载】，将表格加载到工作表中。

得到的结果如下图所示：

5、如果数据源更新，单击【数据】-【刷新】，即可更新结果。如下图所示：