2012年5月26日 星期六

Excel-隨機重組名冊的順序

在 Excel 中,當你取一個名冊時(參考下圖左),想要隨機重組這個名冊的順序(參考下圖右),該如何處理?

首先,你要藉助一個輔助欄位-亂數。

儲存格D2:=RAND()

複製儲存格D2,貼至以下各列。

為了公式說明方便,選取儲存格D2:D26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「亂數」。

 

【公式說明】

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

複製儲存格F2,貼至儲存格F2:H26。

LARGE(亂數,ROW(1:1)):找出亂數中的最大值。當公式往下複製時,ROW(1:1)會變為ROW(2:2)、ROW(3:3)、…,即產生1,2,3,…。

MATCH(LARGE(亂數,ROW(1:1)),亂數,0):找出最大值亂數,位於亂數欄位的第幾列。

OFFSET($A$1,MATCH(LARGE(亂數,ROW(1:1)),亂數,0),COLUMN(A:A)-1,,):透過 OFFSET 函數找出座號欄位中的最大值內容。

每按一下 F9 鍵一次,即可產生一組新的隨機順序的名冊。

 

【練習】

試試另一種作法:

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

自已想想公式的意義。

 

【補充說明】

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

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

ADDRESShttp://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

ADDRESS:在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:可省略。指定要傳回之參照類型的數值。傳回此參照類型如下:

1或省略:絕對儲存格參照;2:列:絕對;欄:相對;3:列:相對;欄:絕對;4:相對參照

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

沒有留言:

張貼留言

好康東東