2011年7月21日 星期四

Excel-列出排名前10名

有網友閱讀另一篇文章:

Excel-關於數列的排名(http://isvincent.blogspot.com/2011/06/excel_5404.html)

問到:如果根據下圖的左表要查出前10名(參考下圖右表),該如何做呢?

當然你可以透過排序等操作,可以得到結果,但是網友可能想要以公式自動產生。

首先,定義名稱:

選取儲存格A1:D25,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,設定:編號、姓名、分數、名次等名稱。再次選取儲存格A1:D25,將其名稱定義為:資料。

接著,輸入公式:

儲存格H2:=LARGE(分數,F2)

儲存格G2:{=VLOOKUP(SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)),資料,2)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

因為資料表中可能會有名次重覆的問題,所以:

COUNTIF($H$2:H2,H2):可以算出在H欄中的儲存格之前有幾個和自己重覆。

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

SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)):可以找出名次所對應的編號。

接著根據編號查表得到姓名:

VLOOKUP(SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)),資料,2)

 

關於函數說明,請參考微軟網站:

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

1 則留言:

  1. 真是太感謝您了,寫不出公式的我幸好有發現這篇教學文躲過被主管釘的命運

    回覆刪除

檢視其他文章

好康東東