學不完．教不停．用不盡

每隔一段時間，就有老師會問到在實務上會遇到的問題：如何運用 Excel 來產生隨機座位表？

例如下圖中，每按一次 F9 鍵，就可以產生一個隨機座位表，該如何處理？

【公式設計與解析】

觀察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)