Excel公式練習28：獲取指定班級的學生姓名

06-18

公式

在單元格D5中的數組公式：

=IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=D$2,ROW($A$2:$A$10)-ROW(A$2)+1),ROWS(G$4:G4))),"")

如圖2所示。

圖2

向下拖至單元格中沒有數據為止。

公式解析

公式中的：

IF($B$2:$B$10=D$2

將單元格區域B2:B10中的數據與單元格D2中的數據相比較，生成數組{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}。

公式中的：

ROW($A$2:$A$10)-ROW(A$2)+1

生成數組{1;2;3;4;5;6;7;8;9}，作為IF函數的第2個參數。

這樣，上述兩個公式聯合生成數組{FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9}，作為SMALL函數的第1個參數。

公式中的：

ROWS(G$4:G4)

根據當前單元格所在位置生成一個數字，在G4中的數字為1，在G5中為2，將此數字作為SMALL函數的第2個參數。

SMALL函數產生的結果數值INDEX函數的行參數，在單元格區域A2:A10中獲取相應的值。

將單元格D5中的公式向下拖動時，相對引用單元格自動調整，並獲取相應單元格的值。

小結

SMALL函數忽略參數中的任何非數字的數據。

IF函數根據條件判斷一次性生成相應的數組。

公式適用於Excel2007及以上版本。

下期預告

Excel公式練習29：總是獲取某列數值中的最後5個數值之和

如下圖所示，在單元格區域A1:A10中有一系列數值，但有些單元格為空，使用公式求該區域最後5個數值之和，不計空格。