Excel在學(xué)生機(jī)位分配中的應(yīng)用

張運(yùn)明 周世林

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

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

中圖分類號(hào)：TP317.3 文獻(xiàn)標(biāo)識(shí)碼：B

文章編號(hào)：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é)生上機(jī)時(shí)人數(shù)多于計(jì)算機(jī)數(shù)這種機(jī)位不足的問(wèn)題。即便有條件將學(xué)生分到兩間教室上機(jī)，也將帶來(lái)教學(xué)不便、管理缺位的新問(wèn)題和安全風(fēng)險(xiǎn)。機(jī)少人多，需要教師公平分配上機(jī)機(jī)會(huì)，改進(jìn)教學(xué)管理。如果人工分配，肯定能夠做到公平分配。分配一個(gè)班容易，分配多個(gè)班就費(fèi)時(shí)了；班級(jí)人數(shù)和機(jī)數(shù)都恒定不變?nèi)菀祝兓^大時(shí)也很費(fèi)力。

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

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

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

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

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

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

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

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

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

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

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

輔助列排定輪次，起過(guò)渡作用。

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

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

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

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

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

學(xué)生用表按照學(xué)生姓名來(lái)排列機(jī)號(hào)，方便學(xué)生查閱。

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

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

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

教師用表按照機(jī)號(hào)來(lái)排列學(xué)生姓名，方便教師查閱。

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

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

ROW函數(shù)產(chǎn)生行號(hào)“{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}”，實(shí)際為40臺(tái)計(jì)算機(jī)的機(jī)號(hào)。IF函數(shù)用于屏蔽錯(cuò)誤值。

為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列的機(jī)號(hào)在B列的機(jī)號(hào)中精確查找位置，結(jié)果加上“3”（指表頭的3行），再利用INDEX函數(shù)返回A的相應(yīng)位置，得到學(xué)生姓名。

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

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

5 結(jié)語(yǔ)

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

插入列，再?gòu)?fù)制公式，可增加上機(jī)次數(shù)。復(fù)制工作表，修改工作表名稱，可排定多個(gè)班級(jí)。學(xué)生用表還可以用作點(diǎn)名冊(cè)，方便教師加強(qiáng)課堂管理。