最近剛好有人問到要對名單重排的問題，在 Excel 中如何對同一欄的資料以亂數重新排序？（參考下圖）
【公式設計與解析】
Excel 中有很好的排序工具，但是沒有提供亂數排序的功能，偏偏在實務上常會需要用到亂數重排的應用，所以必須以手動方式建立公式來處理。每按一次F9鍵，資料即會重新排序，然後再依需要取用。
1. 產生亂數
儲存格C2：=RAND()
本例需要藉助輔助欄位來產生亂數，使用 RAND 函數即可。
複製儲存格C2，貼至儲存格C2:C26。
2. 亂數重排
儲存格D2：=OFFSET($B$1,RANK(C2,$C$2:$C$26),0)
(1) RANK(C2,$C$2:$C$26)
利用 RANK 函數排序，傳回一個名次數值。因為是以亂數來排序，所以名次重覆的機率應該很低。
(2) OFFSET($B$1,RANK(C2,$C$2:$C$26),0)
將第(1)式傳回的名次數值，代入 OFFSET 函數傳回以儲存格B1起始，對應的相對位置。
複製儲存格D2，貼至儲存格D2:DＤ26。
3.驗證是否重覆
儲存格E2：=COUNTIF($B$2:$B$26,D2)
利用 CONUTIF 函數檢驗儲存格D2在儲存格B2:B26中有幾個。如果傳回1，表示正確。如果不是1（0或1以上），表示結果不正確。
複製儲存格E2，貼至儲存格E2:E26。
【延伸練習】
練習以 SUMPRODUCT 函數來執行驗證。
儲存格E2：=SUMPRODUCT((D2=$B$2:$B$26)*1)
以條件：D2=$B$2:$B$26，判斷儲存格D2在儲存格B2:B26陣列中有幾個相符，傳回 TRUE／FALSE 陣列。
(D2=$B$2:$B$26)*1：用以將 TRUE／FALSE 陣列轉換為 1／0 陣列。
最後將傳回的 1／0 陣列予以加總。
【延伸學習】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)
留言列表