2015年9月6日 星期日

Excel-依序列出前幾名(INDEX,COUNTIF,SMALL,LARGE,陣列公式)

在 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)}

沒有留言:

張貼留言

檢視其他文章

好康東東