在 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)))
自已想想公式的意義。
【延伸學習】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)
【補充說明】
關於函數的詳細說明，請參考微軟網站：
OFFSET：http://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 必須是正數。 |
MATCH：http://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 引數內的值必須以遞減次序排序。 |
ADDRESS：http://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：相對參照 |
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
|
INDIRECT：傳回文字串所指定的參照位址。 |
|
語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
留言列表