每隔一段時間,就有老師會問到在實務上會遇到的問題:如何運用 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)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()