2016年10月15日 星期六

Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)

每隔一段時間,就有老師會問到在實務上會遇到的問題:如何運用 Excel 來產生隨機座位表?
例如下圖中,每按一次 F9 鍵,就可以產生一個隨機座位表,該如何處理?
Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)

【公式設計與解析】
觀察I欄、J欄、K欄,除了座號和姓名之外,在I欄中多了一個亂數欄位,其儲存格內容:『=RAND()』。
假設學生有42個,選取儲存格I1:I43,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:亂數。
儲存格A4:=OFFSET($J$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)
複製儲存格A4,貼至儲存格A4:G4。複製儲存格A4:G4,再貼至下方對應的各列位置。
(1) INT(((ROW(A4)-4)/3)*7)
●式子中的參數『4』,乃因為儲存格A4是第 4 列。
●式子中的參數『7』,乃因為每一列有 7 個座位。
●式子中的參數『3』,乃因為每 3 列一組。
當公式向下複製時會產生儲存格A4=1、儲存格A7=8、儲存格A10=15、...。
(2) INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)
當公式向右複製時,COLUMN(A4)=1→COLUMN(B4)2→ ... →COLUMN(G4)=7。
當公式向右複製時,第(2)式會產生 1, 2, 3, 4, 5, 6, 7。
當公式向下複製時,第 7 列會依序產生 8, 9, 10, 11, 12, 13, 14。
當公式向下複製時,第 10 列會依序產生 15, 16, 17, 18, 19, 20, 14。
(3) SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4))
在 SMALL 函數中利用第(2)式的傳回值取得亂數陣列中的第 1, 2,3, ..., 43 個較小值。
(4) MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+COLUMN(A4)),亂數,0)
利用第(3)式傳回的亂數值,透過 MATCH 函數找到位於亂數欄位(I欄)的位置(傳回列號)。
(5) OFFSET($J$1,第(4)式,0)
由第(4)傳回的列號,代入 OFFSET 函數找到對應的J欄中的儲存格內容。
同理:
儲存格A5:=OFFSET($K$1,MATCH(SMALL(亂數,INT(((ROW(A4)-4)/3)*7)+
COLUMN(A4)),亂數,0),0)

沒有留言:

張貼留言

好康東東