2013年9月13日 星期五

Excel-用公式將學生名條亂數重排(OFFSET,INDEX,MATCH,LARGE)

先前一篇文章:將學生名條亂數重排是在 Excel 中,利用手動方式將學生名條亂數加以重新排列,有人又問到如何利用公式來執行亂數重排呢?(參考下圖)

【準備工作】

先選取儲存格C1:C29,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:亂數。

【輸入公式】

以下提供三種公式供練習:

公式一:

儲存格E2:=OFFSET($A$1,MATCH(LARGE(亂數,ROW(1:1)),亂數,0),COLUMN(A:A)-1,,)

LARGE(亂數,ROW(1:1)):在亂數陣列中取出最大的一個數。其中ROW(1:1)=1,往下複製時會產生2, 3, 4, …。

MATCH(LARGE(亂數,ROW(1:1)),亂數,0):將上式中取得的亂數藉由 MATCH 函數找出其位於亂數中的第幾個。

將上式(MATCH)代入 OFFSET 函數中,找出對應的儲存格,並顯示其內容。

複製儲存格E2,貼至儲存格E2:F29。

 

公式二:

儲存格E2:=INDIRECT(ADDRESS(MATCH(LARGE(亂數,ROW(1:1)),亂數,0)+1,COLUMN(A:A)))

參考公式一之說明,將 MATCH 函數找到的儲存格位置,置入 ADDRESS 函數,轉成儲存格位址,再藉由 INDIRECT 函數,取出該儲存格的內容。

複製儲存格E2,貼至儲存格E2:F29。

 

公式三:

儲存格E2:=INDEX(CHOOSE({1,2,3},亂數,座號,姓名),MATCH(LARGE(亂數,ROW(1:1)),亂數,0),COLUMN(B1))

參考公式一之說明,再加上此公式利用陣列觀念配合 CHOOSE 函數來做查表的動作,讀者可以參考另一篇文章:利用CHOOSE函數來輔助查表(VLOOKUP,INDEX,MATCH)中有詳細的說明。

複製儲存格E2,貼至儲存格E2:F29。

 

只要每按一下 F9 鍵,即可產生一個亂數排序的結果。

沒有留言:

張貼留言

檢視其他文章

好康東東