2011年5月27日 星期五

Excel-各種排序

在 Excel 提供了排序的函數,例如:RANK,這次來練習不一樣的排序方式。

先定義各種資料名稱:姓名、分數、輔助分別為A欄、B欄、G欄有資料的部分。

(1) RANK排序

儲存格C3:=RANK(B3,分數)

(2) COUNTIF排序

儲存格D3:=COUNTIF(分數,">"&B3)+1

其原理是計算在所有分數中,大於自己的分數個數,再加1,即是自己的排名。

(3) 不產生相同名次的排名

由於以上兩種方式若遇同分的狀況,將會出現相同排名,並且下個分數會跳過同分的名次。如果想要產生不重覆的名次,該如何設計呢?首先要產生一組輔助資料

儲存格G3:=B3+(1000-ROW())/10000

將每個分數加上(1000-列號)/10000,即將分數加上一個不重覆的數字,而列號愈小,加上的數字愈小,反之亦反。

儲存格E3:=RANK(G3,輔助)

由於輔助欄位的資料不會重覆,所以該名次也不會有相同的狀況,只是同分者,列號較小,名次較少。

(4) 依名次列出姓名

儲存格F3:=INDIRECT(ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1))

LARGE(輔助,ROW(1:1)),輔助,0):找出輔助資料中第1大者。

MATCH(LARGE(輔助,ROW(1:1)),輔助,0):找出輔助資料中最大者在第幾列。

ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1):找出最大值在的位址,加2是因為第一列由列號3開始。

INDIRECT(ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1)):INDIRECT函數將最大值的位址轉成該位址的內容。

複製儲存格C2:F2,往下儲存格貼上。

沒有留言:

張貼留言

好康東東