Excel函數(shù)在企業(yè)多工種考試編排考場中的應用

李莉

【摘 要】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.

[責任編輯：陳澤琦]