|
|
|
本文主要介紹如何用Excel按數(shù)據(jù)借助ROW和IF等函數(shù)進(jìn)行特殊排序，以快速完成各種座次表、企業(yè)職工工作安排等數(shù)據(jù)排序工作。
開學(xué)差不多一個(gè)月了，此時(shí)很多學(xué)校都會(huì)根據(jù)身高等因素給學(xué)生重新安排座位。通常要求把高個(gè)排在后面、矮個(gè)排在前面。以往的做法是讓學(xué)生按高低排隊(duì)再順次排座位，結(jié)果排隊(duì)時(shí)身高差不多的學(xué)生經(jīng)常爭(zhēng)執(zhí)。今年不妨換個(gè)做法，先按學(xué)生身高隨機(jī)編出座位圖，再讓大家按圖就座，這樣就沒什么可爭(zhēng)了。不過手工排座位工作量也挺大，還是讓Excel與函數(shù)幫忙搞定吧。
1.制作學(xué)生記錄表
打開Excel2007，把sheet1工作表重命名為“學(xué)生記錄”，按需設(shè)置好表格(圖1)。在C:E列輸入學(xué)號(hào)、姓名、身高，或者從已有的表格中復(fù)制過來(lái)。在H、I列輸入身高與系數(shù)對(duì)照表，在此H2固定為1，下面的157、168則可自由修改。也可多增加幾條身高和系數(shù)，但身高要升序排列、系數(shù)降序。個(gè)別嚴(yán)重近視的學(xué)生可以在F列輸入視力系數(shù)1或2讓他排前一點(diǎn)。
在A2單元格輸入公式=ROW()-1自動(dòng)生成序號(hào)，在B2輸入公 式=IF(C2，VLOOKUP(E2，H:I，2)+F2+RAND()，)。公式中用VLOOKUP提取身高系數(shù)+視力系數(shù)+RAND()生成一個(gè)有 身高視力差異的隨機(jī)數(shù)。選中A2:B2拖動(dòng)其右下角的黑色方塊(填充柄)向下填充到B97，通常一班不會(huì)超過96人吧？現(xiàn)在選中B2，單擊“開始”選項(xiàng)卡 的“排序”選擇“降序”，就會(huì)按157以下排前面、157-168中間、168以上排后面的前提隨機(jī)排序，視力系數(shù)每增加1則可使其在這3檔中排前1檔。
2.編制座位圖
以把學(xué)生分成6組(列)為例，我們得先建一個(gè)“座位表”工作表，在A3、A4分別輸入1、7，并對(duì)B3、B4設(shè)置粗邊框。在B3輸入公式=VLOOKUP(A3，學(xué)生記錄!$A:$F，4，F(xiàn)ALSE)，雙擊填充柄把公式復(fù)制到B4。選中A3:C4鼠標(biāo)指向其填充柄，按住右鍵拖動(dòng)到Q4，松開右鍵在彈出菜單中選擇“填充序列”，即可填充出前兩排的序號(hào)和學(xué)生名。再選中A3:Q4向下拖動(dòng)填充柄到Q18，填充出96個(gè)座位和序號(hào)，學(xué)生自動(dòng)按序號(hào)出現(xiàn)在座位圖中。最后適當(dāng)調(diào)整好行高列寬，畫一個(gè)矩形代表講臺(tái)桌即可(圖2)。
注：分組數(shù)不同，只需開始時(shí)改一下A4的數(shù)字，例：分8組就改成9，其他操作都一樣。若用的是雙人桌，只要在全部設(shè)置好后直接刪除兩組間的空列使兩組合并在一起即可。
3.修飾座位圖
座位圖中沒學(xué)生的單元格會(huì)顯示錯(cuò)誤值#N/A和邊框，得讓它自動(dòng)消失。選中A:Q列，單擊“開始”選項(xiàng)卡的“條件格式”選擇“新建規(guī)則”，在 “新建格式規(guī)則”窗口中選擇規(guī)格類型為“只為包含以下內(nèi)容的單元格設(shè)置格式”，并在“單元格值”下拉列表中選擇“錯(cuò)誤”(圖3)。再單擊“格式”按鈕，在 彈出窗口中設(shè)置字體顏色為白色，在“邊框”選項(xiàng)卡中設(shè)置邊框?yàn)闊o(wú)。一路確定完成設(shè)置后，沒有學(xué)生的邊框和錯(cuò)誤值都會(huì)自動(dòng)消失。
座位左邊的序號(hào)不需要打印出來(lái)，得先隱藏起來(lái)。選中A列，單擊“數(shù)據(jù)”選項(xiàng)卡的“組合”圖標(biāo)進(jìn)行組合。同樣分別選中D、G、J、M、P列進(jìn)行組合。組合后在左上角會(huì)顯示1、2的按鈕，點(diǎn)擊1即可隱藏所有序號(hào)列(圖4)，點(diǎn)擊2則恢復(fù)顯示序號(hào)。
4.自動(dòng)排座位
通常一學(xué)期需要多次重排座位，若學(xué)生沒變，你只要在“學(xué)生記錄”工作表選中B2單擊“開始”選項(xiàng)卡的“排序”選擇“降序”，即可隨機(jī)生成一張新 座位圖。即使學(xué)生變了或需要為其他班級(jí)排座位，也只要在“學(xué)生記錄”工作表中輸入新班級(jí)學(xué)生的學(xué)號(hào)、姓名、身高，對(duì)個(gè)別高度近視的再輸入一下近視系數(shù)，再 選中B2降序排序一下，即可在“座位表”工作表中看到隨機(jī)排好的座位圖。
若需要對(duì)個(gè)別學(xué)生座位進(jìn)行調(diào)整，可通過修改座位圖的序號(hào)實(shí)現(xiàn)。本例中身高174的李麗麗因視力系數(shù)被分配到前排正中，這會(huì)影響后面學(xué)生的視線， 得把她調(diào)整到左邊。你只要在“座位表”工作表中單擊“2”按鈕顯示序號(hào)列，把序號(hào)7改成10、10改成7，即可讓她與序號(hào)7的蔡小森對(duì)調(diào)座位。修改后記得 再隱藏序號(hào)列。
現(xiàn)在可以把座位圖打印出來(lái)貼到講臺(tái)上，讓學(xué)生按圖入座了。雖然操作有點(diǎn)啰嗦，但一旦設(shè)置完成，以后就只要重復(fù)第4步即可排好座位圖，應(yīng)用起來(lái)還是挺簡(jiǎn)單的。
|
|
發(fā)表留言請(qǐng)先登錄！
|