首页 > Excel专区 > Excel教程 >

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常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.