Excel公式練習68: 從數據區域提取值並按降序排列

學習Excel技術，關注微信公眾號：

excelperfect

 

本次的練習是：如下圖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