excel函数从数据区域提取值并按降序排列

Excel教程 2022-01-04 21:49:22

excel函数从数据区域提取值并按降序排列，如下图1所示，在工作表中存储着捐款数据。

图1

现在要获取大于某金额的捐款人员和金额，并按降序排列，如下图2所示。

图2

如何使用公式来实现？

先不看答案，自已动手试一试。

解决方案

为简单起见，我们分两步来获取想要的结果。

第1步：获取捐款金额并降序排列

在单元格G2中输入数组公式：

=IFERROR(LARGE(IF($D$2:$D$26>$I$2,$D$2:$D$26),ROW(A1)),””)

下拉至单元格区域末尾。

公式中：

IF($D$2:$D$26>$I$2,$D$2:$D$26)

获取大于指定金额的数字组成的数组：

{195;205;FALSE;FALSE;FALSE;FALSE;220;FALSE;220;195;FALSE;FALSE;195;180;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;175;220;FALSE;FALSE;165}

将其作为LARGE函数的参数，取其中第ROW(A1)=1大的值，即得到：

220

下拉公式，ROW(A1)会相应变化为ROW(A2)、ROW(A3)、…，即2、3、…，获取相应的值。

此时的结果如下图3所示。

图3

下面，要提取金额对应的捐款人的姓名，但是金额有重复值，如何提取人名呢？那就要确保是唯一值，才能精确匹配。因此，我们使用了一个辅助列。

第2步：提取捐款人姓名

以列A为辅助列，在其中添加数据，该数据是列D中的数值与其在前面单元格中出现的次数连接而成，以确保数据唯一。

在单元格A2中输入公式：

=D2 &” ” & COUNTIF($D$2:D2,D2)

下拉至数据单元格区域末尾，结果如下图4所示。

图4

注意，在公式中我们连接了一个符号“ ”，这是为了防止连接数字后，会存在重复的情况，虽然可能性不大，但这是一个预防措施。

现在，我们创建了一个没有重复数据的列，可以用来查找捐款人姓名了。在单元格F2中输入公式：

=IFERROR(VLOOKUP(G2& ” ” & COUNTIF($G$2:G2,G2),$A$2:$B$26,2,0),””)

下拉至单元格区域末尾，结果如下图5所示。

图5

我们改变要获取的金额，效果如下图6所示。

图6

上一篇：掀开面纱，看看Excel文件到底是什么下一篇：excel常用函数判断回文