張運明 周世林

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

關(guān)鍵詞 Excel；函數(shù)；機位

中圖分類號：TP317.3 文獻標(biāo)識碼：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 前言

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

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

2 建立和設(shè)置表頭

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

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

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

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

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

（“FileName”，A2），F(xiàn)IND（“]”，CELL（“FileName”，A2））+

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

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

3 為輔助列設(shè)置公式

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

選擇R2：R4000區(qū)域，輸入數(shù)組公式“{=INDEX（$A$4：

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

選擇S2：S4000區(qū)域，輸入數(shù)組公式“{=MOD（ROW（）-2，

$K$2）+1}”。MOD函數(shù)以K2單元格的值“40”為除數(shù)計算余數(shù)，結(jié)果加上“1”，就產(chǎn)生1～40的機號，且不斷循環(huán)。

4 為師生用表設(shè)置公式

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

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

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

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

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

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

（INDIRECT（"1："&K2）））}”。INDIRECT函數(shù)產(chǎn)生引用“″1：40″”，

ROW函數(shù)產(chǎn)生行號“{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函數(shù)用于屏蔽錯誤值。

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

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

將K4：K63區(qū)域的公式復(fù)制到P63單元格。

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

5 結(jié)語

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

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