2011年6月26日 星期日

Excel-關於數列的排名

在 Excel 中可以很方便的由一個數列中找出某數的排名,如何來找出某個排名的內容呢?

首先,將A欄和B欄有資料的儲存格分別命名為「編號」和「數列」。

(1) 找出某數在數列的排名

儲存格C2:=RANK(B2,數列) 

複製儲存格C2,往下貼在儲存格C2:C21。

或是

儲存格C2:=COUNTIF(數列,">"&B2)+1

其原理是找出本身儲存格之前有幾個比自己為大者。

(2) 找出某排名在數列中的數

現在要和(1)相反,由排名找出數列中的數,要注意會有同名次問題,所以試試以下的做法:

儲存格G2:=LARGE(數列,E2)

利用LARGE函數找出由大到小的數列排列。

儲存格F2:{=IF(COUNTIF($G$2:G2,G2)>1,SMALL(IF(G2=數列,編號,FALSE),COUNTIF($G$2:G2,G2)),SMALL(IF(G2=數列,編號,FALSE),1))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格F2:G2,往下貼在儲存格F2:G21。

SMALL(IF(G2=數列,編號,FALSE),1)):利用陣列公式找出某數和數列相符所對應的編號,其結果類似:Fasle,False,2,False, …, Fasle的陣列,利用SMALL可以取出這個唯一的數值。

因為同一個數值有可能在數列中出現多次,所以以「COUNTIF($G$2:G2,G2)>1」來判斷,如果超過1個,則在SMALL函數中取第COUNTIF($G$2:G2,G2))個最小值(如果某數為第2個重覆,則(COUNTIF($G$2:G2,G2))=2)。

因為使用SMALL函數,所以相同數值中,編號較小者,會先被列出來,編號較大者會被較後被列出。

註:以上儲存格F2公式可以簡化為:

儲存格F2:{=SMALL(IF(G2=數列,編號,FALSE),COUNTIF($G$2:G2,G2))}

沒有留言:

張貼留言

好康東東