學不完．教不停．用不盡

在 Excel 的工作表中有一個含有姓名和數值的二樓資料，如何能依序由大到小列出前幾名？在下圖中，是由 22 個數值中取出前 10 名，並且由大至小依序列出。

【公式設計與解析】

(1) 由大至小依序列出數值

儲存格F3：=LARGE(數值,ROW(1:1))

其中ROW(1:1)在向下複製時，會產生ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、...。再使用 LARGE 函數找出第 1, 2, 3, .. 大的數值。

(2) 由大至小依序列出數值位於第幾列

觀察上圖，其中會有相同大小的數值(例如第7,8列和9,10列)，所以要特別處理。

為了說明，特別產生D欄來輔助。

儲存格D3：｛=SMALL(IF(F3=$B$3:$B$24,ROW($B$3:$B$24),FALSE),
COUNTIF($F$3:F3,F3))｝

IF(F3=$B$3:$B$24,ROW($B$3:$B$24),FALSE)：找出和儲存格F3相同者，傳回其列號(ROW($B$3:$B$24)=3～24)，否則傳回 FALSE。

以儲存格F8的數值傳回：{FALSE;4;FALSE;FALSE;FALSE;FALSE;FALSE;10;FALSE;...}

COUNTIF($F$3:F3,F3)：計算由儲存格F3起始，在目前儲存格之前有幾個和自己重覆。以儲存格F8的數值為例傳回 2。

最後透過 SMALL 函數取得對應的列號，以儲存格F8的數值為例傳回 10。

(3) 由大至小依序列出數值對應的姓名

儲存格E3：｛=INDEX($A$3:$B$24,儲存格D3-2,1)｝

使用 INDEX 函數將 (2) 代入，可以查表得到對應的姓名。

完整的公式內容：

儲存格E3：｛=INDEX($A$3:$B$24,SMALL(IF(F3=$B$3:$B$24,
ROW($B$3:$B$24),FALSE),COUNTIF($F$3:F3,F3))-2,1)｝