李莉
【摘 要】Excel是電腦普及以來應用最廣泛的辦公軟件之一,在很多公司中,Excel在各個部門的核心工作中發(fā)揮著重要的作用。文章以某集團技能大賽考試的考場編排為例,闡述利用Excel函數(shù)實現(xiàn)快速編排考場。
【關鍵詞】Excel;多工種考試;編排考場
【中圖分類號】TP391.3 【文獻標識碼】A 【文章編號】1674-0688(2019)07-0157-03
某集團公司是一家國有大型煤礦企業(yè),該公司重視人才培養(yǎng),利用多種方式培養(yǎng)選拔人才,尤其是“以考促學、以賽促學”的方式,在建設學習型企業(yè)中發(fā)揮了重要的作用。從2006年開始,該公司每年舉辦職工技能大賽,有上百個工種,幾千名職工同時進行考試,形成“百個工種大比武,萬名員工爭狀元”的比、學、趕、超氛圍。大賽分理論考試和實踐操作,在理論考試中,為了防止考生作弊,要求在編排考場時,相鄰的座位按不同工種間隔排開。在大賽開始前,編排考場就成為一項非常艱巨的任務,往往是幾個老師逐個考場地編排,還時常發(fā)生疏漏。在大賽進行3年后,筆者有幸參加了考場的編排工作,利用Excel的函數(shù)功能,快速完成了考場編排任務。不僅如此,還實現(xiàn)了在每個考場安排表中標注考場工種編號、考試人數(shù)等功能,極大地方便了考場封裝試卷。下面筆者就某年該公司有2 000多名員工參加的技能大賽為例,對Excel函數(shù)在編排考場中的應用做闡述。
1 基礎數(shù)據(jù)準備
(1)考場情況:需對考場數(shù)、每個考場的容納量進行核查,以便按考場的容納量安排考生,本例中每個考場容納量為35人。
(2)考生數(shù)據(jù):包括考生姓名、單位、考號??继柧幣牛呵?位為工種編號(如以“0”開頭,請將單元格設置為文本型后再錄入),后2位為工種排隊順序號。
(3)根據(jù)考生數(shù)據(jù),編排考場人員。操作步驟:①將考生數(shù)據(jù)中的考生考號復制到一個輔助工作表中(工作表名稱為“考場編排”,可自行定義),此表僅作為編排考場用,不需打印。②利用文本函數(shù)LEFT,將編號中的工種編號提取出來,以便后面進行排序。B列中的公式:=LEFT(A2,3),提取A列前3位文本,即提取出了工種編號。③利用文本函數(shù)RIGHT,將編號中的工種序號提取出來。C列中的公式:=RIGHT(A2,2)*1,提取A列后2位文本,即提取出了工種序號。提取出序號后,因為是文本型數(shù)值,為了以后應用方便,在公式后“*1”,將文本型數(shù)值轉(zhuǎn)換為數(shù)值型。④安排考場。一般編排的規(guī)則是,每個考場安排2~4個工種。在本例中,35人的考場,每個工種安排不能超過18人,如超過,則會出現(xiàn)同工種考生座位相鄰的情況。如遇工種人數(shù)較少,或剩余人數(shù)較少的情況,可多工種湊數(shù),形成“18+17”人數(shù)的形式,人數(shù)多的工種排在最前或最后,以保證打亂工種時,人數(shù)多的工種不會相鄰。根據(jù)排序序號,可很直觀地看出每個工種的人數(shù),在本例中,序號為001的工種一共28人,序號為002的工種29人,在001工種中選擇“考場”列的前17(或18)個,統(tǒng)一錄入1,即將1~17號安排到1考場。剩下的18~28序號的輸入“2”,將這些考生安排到2考場。序號為002的工種,1~18號的“考場”列中輸入“1”,將2號工種的前18人安排到1考場,剩余19~29號安排至2考場。此時,1考場有001工種17人,002工種18人,本考場安排完畢。2考場有001工種11人,002工種11人,還可容納13人,此時可從后面的工種中選擇13人安排至2考場,依次累推。如遇某工種安排到第2個考場后,還有超過18人未安排,超過的人數(shù)則需往后安排到第3考場……按以上規(guī)則將“考場”列全部錄入考場號。⑤按考場進行排序。以“考場”列為第1關鍵字,“排序”列為第2關鍵字進行排序。排序后可發(fā)現(xiàn),所有編號均按考場排列好。因此,工種號為001和002的數(shù)據(jù)按原有的排序號進行了排列,實現(xiàn)了工種的交錯。
(4)考場安排統(tǒng)計表:考場編排完畢后,依照制作“考場安排統(tǒng)計表”(工作表名稱為“考場安排統(tǒng)計表”),通過此表,將每個考場的人數(shù)安排、工種安排、每個工種安排的人數(shù)及考號范圍都一一錄入,以便今后封裝試卷和進行人數(shù)的核驗、統(tǒng)計。
(5)工種統(tǒng)計表:此表中錄入的是工種對應的編號、總?cè)藬?shù)、每個考場安排人數(shù)等。主要有兩個用途:一為工種編號對應表,通過工種與編號的對應,在考場安排表中快速輸入工種;二是人數(shù)核對,每個工種安排到哪個考場考試,所有人員是否全部安排(如圖1所示)。
此表A-I列需在“考場安排統(tǒng)計表”安排好后進行錄入,“人數(shù)核對”列中公式為“=E4+G4+I4”,與參賽人數(shù)進行對比,如不一致,則需檢查是否有考生未安排至考場。
(6)最終實現(xiàn)的考場安排表如圖2所示。
2 制作考場安排表
(1)建立新的工作表(如圖3所示),名稱為“1”(1考場)。根據(jù)考場情況,先錄入空的考場安排表:此考場為7行5列模式,每個考場可容納35人。為了更加直觀和美觀,在表中每個座位旁邊均有留空。
(2)將“考場編排”工作表中排好的第1考場人員編號復制到M列中,并在L列加入序號,以便核對人數(shù)(輔助單元格)。
(3)在相應的單元格中依次錄入公式,即可將編號填入“考場安排表”中(可按實際需要調(diào)整編號安排順序)?!熬幪枴绷兄袃?nèi)容修改,“考場安排表”中的數(shù)據(jù)也可相應更改。
(4)在“考場安排表”中,根據(jù)錄入的編號,查詢對應的工種,并顯示出來。①工種、編號對應的數(shù)據(jù)區(qū)域定義名稱,以便在查詢的公式中使用??衫矛F(xiàn)有的“工種統(tǒng)計表”中數(shù)據(jù)定義。選中數(shù)據(jù)區(qū)域,在名稱框中輸入“gz”,按回車鍵,即可定義名稱為“gz”的數(shù)據(jù)區(qū)域。②根據(jù)編號查詢對應的工種名稱。在B5單元格中,輸入“=IF(ISERROR(VLOOKUP(MID(B6,1,3)*1,gz,2,F(xiàn)ALSE)),"",VLOOKUP(MID(B6,1,3)*1,gz,2,F(xiàn)ALSE))”。③將上述公式復制到需要顯示工種內(nèi)容的所有單元格中(注意:此處僅需復制,不需逐個輸入),Excel會根據(jù)不同單元格,自動引用相應的單元格。至此,考場安排表基本信息錄入完畢。為了美觀,可將輔助單元格中的內(nèi)容字體改為白色。
3 在每個考場顯示工種、起止編號、人數(shù)
在多工種的考試中,為了更好地讓監(jiān)考人員核實考場中各工種的人數(shù)及試卷,在考場安排表中顯示每個工種的人數(shù)無疑是很重要的。我們可以看到,在最終實現(xiàn)的“考場安排表”中從D26單元格開始,到J27單元格是顯示考場的工種、編號和人數(shù)的,這些內(nèi)容可以隨著考場號的變化自動更新。步驟如下。
(1)B2單元格中內(nèi)容為考場號,因此我們以B2單元格確定當前的考場。為了更方便地在公式中查詢到考場號,B2單元格中需要一個數(shù)值而不是字符串。如“第1考場”,在B2單元格中輸入“1”,設置單元格格式—自定義數(shù)字格式— "第"#"考場",在B2單元格中直接輸入數(shù)值,即可自動顯示“第*考場”。
(2)B26-B29單元格(輔助單元格)中輸入以下內(nèi)容:=MATCH($B$2,考場安排統(tǒng)計表!A:A,0);=MATCH($B$2,考場安排統(tǒng)計表!A:A,0)+1;=MATCH($B$2,考場安排統(tǒng)計表!A:A,0)+2;=MATCH($B$2,考場安排統(tǒng)計表!A:A,0)+3。使用MATCH函數(shù)查找B2單元格中的內(nèi)容(考場號),在“考場安排統(tǒng)計表”A列中對應數(shù)字的行號。因最多一個考場只有4個工種,B27-B29中依次+1,即可將本考場中所有工種都查找到對應的行號,本例返回的結(jié)果依次是2、3、4、5。
(3)A26-A29單元格(輔助單元格)輸入下列公式:="考場安排統(tǒng)計表!A"&B26&":$I$185"。這個公式結(jié)果是一個字符串,旨在將B26單元格查找到的行號與“考場安排統(tǒng)計表!A”文本串及后面的":$I$185"連接起來。本例中,B26查詢到“1”考場第1個工種在第2行,與上述字符串連接的結(jié)果如下:“考場安排統(tǒng)計表!A2:$I$185”,這是“考場安排統(tǒng)計表”中A2單元格到I185單元格的一個區(qū)域,這個字符串將在后面的公式中用到。依次向下填充3行,實現(xiàn)相對應內(nèi)容的查詢。
(4)D26-D29單元格輸入下列公式:=IF(ISERROR(VLOOKUP($B$2,INDIRECT(A26),3,0)),"",VLOOKUP($B$2,INDIRECT(A26),3,0))。
此公式用來返回B2單元格中的內(nèi)容(即考場號),在A26單元格的字符串引用區(qū)域中第3列的內(nèi)容,即返回了當前考場的工種編號,在這里同樣可以用“自定義數(shù)字格式”來顯示“*號工種”,用法同前。IF函數(shù)和ISERROR函數(shù)用來判斷VLOOKUP函數(shù)是否返回了數(shù)值,如返回的是錯誤值,即顯示為空。公式中用到了INDIRECT函數(shù),用來返回由文本字符串指定的引用。依次向下填充3行,實現(xiàn)相對應內(nèi)容的查詢。
(5)F26-F29單元格輸入下列公式:=IF(ISERROR(VLOOKUP(D26,gz,2)&":"),"",VLOOKUP(D26,gz,2)&":"),此公式用來返回D26單元格的內(nèi)容在之前定義的“gz”數(shù)據(jù)區(qū)域(“工種統(tǒng)計表”中的數(shù)據(jù))中第2列的內(nèi)容,即工種號對應的工種名稱。依次向下填充3行,實現(xiàn)相對應內(nèi)容的查詢。
(6)G26-G29單元格輸入下列公式:=IF(ISERROR(VLOOKUP($B$2,INDIRECT(A26),6,0)),"",VLOOKUP($B$2,INDIRECT(A26),6,0))&IF(ISERROR(VLOOKUP($B$2,INDIRECT(A26),7,0)),"",VLOOKUP($B$2,INDIRECT(A26),7,0))&IF(ISERROR(VLOOKUP($B$2,INDIRECT(A26),8,0)),"",VLOOKUP($B$2,INDIRECT(A26),8,0))。
此公式用來返回B2單元格中的內(nèi)容,在A26單元格的字符串引用區(qū)域中第6~8列的內(nèi)容,即返回了當前考場工種的起止編號。依次向下填充3行,實現(xiàn)相對應內(nèi)容的查詢。
(7)J26-J29單元格輸入下列公式:=IF(ISERROR(VLOOKUP($B$2,INDIRECT(A26),5,0)),"",VLOOKUP($B$2,INDIRECT(A26),5,0))。
此公式用來返回B2單元格中的內(nèi)容,在A26單元格的字符串引用區(qū)域中第5列的內(nèi)容,即返回了當前考場工種的人數(shù)。依次向下填充3行,實現(xiàn)相對應內(nèi)容的查詢。
4 總結(jié)
Excel在多工種、多單位需打亂次序的考試中得到普遍的應用,從2009年至今,筆者單位的技能大賽一直在運用Excel工作簿進行考場的編排工作。因筆者水平有限,所以僅用Excel函數(shù)實現(xiàn)了上述功能,在實際運用中,需要具備一定Excel基礎的人才能熟練掌握。今后,筆者將進一步探索利用編程語言+數(shù)據(jù)庫完成此工作,在數(shù)據(jù)的嚴謹性、程序的易用性方面會更趨完善。
參 考 文 獻
[1]Microsoft.Excel幫助文件[EB/OL].https://support.office.com/zh-cn/excel,2003-05-03.
[2]Excel Home.Excel 2013函數(shù)與公式應用大全[M].北京:北京大學出版社,2016.
[3]Excel Home.Excel 2013實戰(zhàn)技巧精粹[M].北京:人民郵電出版社,2015.
[責任編輯:陳澤琦]