Excel在學生機位分配中的應用

張運明 周世林

摘 要 學生上機可能面臨機少人多這種機位不足的問題。人工分配機位，比較麻煩和費時。借助Excel超強的計算能力，精心設置函數，可以打造出機位分配模板，大大提高分配效率，輕松實現機位分配。

關鍵詞 Excel；函數；機位

中圖分類號：TP317.3 文獻標識碼：B

文章編號：1671-489X（2016）04-0056-02

Application of Excel in Student Assignment//ZHANG Yunming， ZHOU Shilin

Abstract Students may face the problem that the number of computers is less than that of the students. It takes time to assign the computers by people. With the help of Excel， we can make the template assign the computers which can improve the efficiency of assignment.

Key words Excel； function； reservation

1 前言

諸多原因可能造成一些學校學生上機時人數多于計算機數這種機位不足的問題。即便有條件將學生分到兩間教室上機，也將帶來教學不便、管理缺位的新問題和安全風險。機少人多，需要教師公平分配上機機會，改進教學管理。如果人工分配，肯定能夠做到公平分配。分配一個班容易，分配多個班就費時了；班級人數和機數都恒定不變容易，變化較大時也很費力。

如果借助Excel超強的計算能力，打造出機位分配模板，可大大提高分配效率。下面就在Excel 2013環境中介紹如何構建模板。

2 建立和設置表頭

工作表取名“高1.1班”，A～H列為學生用表，J～P列為教師用表，R～S列為輔助列，表頭樣式如圖1所示。

設置單元格格式 右擊J2單元格，在彈出的快捷菜單中選擇“設置單元格格式”，彈出“設置單元格格式”對話框；選擇“數字”標簽，從“分類”中選擇“自定義”，在“類型”框中輸入“G/通用格式"人"”（英文狀態下雙引號）；單擊“確定”按鈕，完成設置。這樣，在J2單元格中輸入“60”，會顯示“60人”。

為K2單元格設置自定義單元格格式“G/通用格式"機"”，

輸入“40”，會顯示“40機”。為B3：H3和J3：P3區域自定義單元格格式“"第"G/通用格式"次"”，輸入“1”，會顯示“第1次”。

提取工作表名稱 在B2單元格中輸入公式“=MID（CELL

（“FileName”，A2），FIND（“]”，CELL（“FileName”，A2））+

1，255）”。公式會自動提取本工作表的名稱“高1.1班”；如果本工作表的名稱作了修改，B2單元格中顯示的內容就會隨之變化。式中，CELL函數提取文件路徑和文件名，如文件放在桌面，文件名為“機位分配”，提取結果就為“"C：＼Users＼Administrator＼Desktop＼[機位分配.xlsx]高1.1班"”；FIND函數查找出現字符“]”的位置并加上“1”，這里計算結果為“43”，作為MID函數查找的起始位置；MID函數就從第43個字符的位置查起，查找長度為“255”個字符，最后截取結果為“高1.1班”。

引用標題 在B1單元格中輸入公式“=J1”，直接從J1單元格中引用標題。

3 為輔助列設置公式

輔助列排定輪次，起過渡作用。

選擇R2：R4000區域，輸入數組公式“{=INDEX（$A$4：

$A$103，MOD（ROW（）-2，$J$2）+1）}”。花括號是數組公式的標志，輸完公式后同時按下鍵盤上的Ctrl+Shift+Enter組合鍵會自動產生。式中，A4：A103是指A列的一個班，按100人設計。ROW函數產生當前單元格的行號。MOD函數以J2單元格的值“60”為除數計算余數，結果加上“1”，就產生1～60的序數，且不斷循環。INDEX函數就從A列中以1～60的循環序數取得實際的學生名字，學生名字就按1～60的周期不斷循環。

選擇S2：S4000區域，輸入數組公式“{=MOD（ROW（）-2，

$K$2）+1}”。MOD函數以K2單元格的值“40”為除數計算余數，結果加上“1”，就產生1～40的機號，且不斷循環。

4 為師生用表設置公式

學生用表按照學生姓名來排列機號，方便學生查閱。

在B4單元格中輸入公式“=IFERROR（VLOOKUP（$A4，OFFSET（$R$2，$K$2*（B$3-1），，$K$2，2），2，），）”。OFFSET函數

以A4單元格為起點，偏移的行數為“$K$2*（B$3-1）”，列數為0，以K2和B3單元格中數字的計算結果來偏移，會形成一個個動態的起點；然后以K2單元格的機數為偏移的高度，寬度為2列。這樣就形成每40人為一組的人、機一一對應的數組。VLOOKUP函數就根據A4單元格的名字在這些數組的第2列查找機號，如果查找不到，IFERROR函數就將錯誤值顯示為0。

利用填充柄將B4單元格的公式復制到B103單元格，再將B4：B103區域的公式復制到H103單元格。

教師用表按照機號來排列學生姓名，方便教師查閱。

為J4：J63區域輸入數組公式“{=IF（ROW（）-3>$K$2，，ROW

（INDIRECT（"1："&K2）））}”。INDIRECT函數產生引用“″1：40″”，

ROW函數產生行號“{1；2；3；4；5；6；7；8；9；10；11；12；13；14；15；16；17；18；19；20；21；22；23；24；25；26；27；28；29；30；31；32；33；34；35；36；37；38；39；40}”，實際為40臺計算機的機號。IF函數用于屏蔽錯誤值。

為K4：K63區域輸入數組公式“{=IFERROR（IF（ROW（）-3>$K$2，，INDEX（$A：$A，MATCH（$J$4：$J$103，B$4：B$103，）+3）

），）}”。MATCH函數根據J列的機號在B列的機號中精確查找位置，結果加上“3”（指表頭的3行），再利用INDEX函數返回A的相應位置，得到學生姓名。

將K4：K63區域的公式復制到P63單元格。

公式輸入完畢，效果如圖2所示。

5 結語

使用時，在學生用表中錄入或粘貼學生姓名，在教師用表中設置計算機數量、學期，修改工作表名稱，其他數據全部自動生成。教師可以對學生姓名按一定順序自主排序，顯得機位分配更為合理公平。當然，也可以進一步用隨機函數產生隨機順序，實現完全的機會公平。機器故障可能導致學生上機機會不公平，可預留一定數量機器暫時不分配。

插入列，再復制公式，可增加上機次數。復制工作表，修改工作表名稱，可排定多個班級。學生用表還可以用作點名冊，方便教師加強課堂管理。