Excel函數在企業多工種考試編排考場中的應用

李莉

【摘 要】Excel是電腦普及以來應用最廣泛的辦公軟件之一，在很多公司中，Excel在各個部門的核心工作中發揮著重要的作用。文章以某集團技能大賽考試的考場編排為例，闡述利用Excel函數實現快速編排考場。

【關鍵詞】Excel;多工種考試;編排考場

【中圖分類號】TP391.3 【文獻標識碼】A 【文章編號】1674-0688（2019）07-0157-03

某集團公司是一家國有大型煤礦企業，該公司重視人才培養，利用多種方式培養選拔人才，尤其是“以考促學、以賽促學”的方式，在建設學習型企業中發揮了重要的作用。從2006年開始，該公司每年舉辦職工技能大賽，有上百個工種，幾千名職工同時進行考試，形成“百個工種大比武，萬名員工爭狀元”的比、學、趕、超氛圍。大賽分理論考試和實踐操作，在理論考試中，為了防止考生作弊，要求在編排考場時，相鄰的座位按不同工種間隔排開。在大賽開始前，編排考場就成為一項非常艱巨的任務，往往是幾個老師逐個考場地編排，還時常發生疏漏。在大賽進行3年后，筆者有幸參加了考場的編排工作，利用Excel的函數功能，快速完成了考場編排任務。不僅如此，還實現了在每個考場安排表中標注考場工種編號、考試人數等功能，極大地方便了考場封裝試卷。下面筆者就某年該公司有2 000多名員工參加的技能大賽為例，對Excel函數在編排考場中的應用做闡述。

1 基礎數據準備

（1）考場情況：需對考場數、每個考場的容納量進行核查，以便按考場的容納量安排考生，本例中每個考場容納量為35人。

（2）考生數據：包括考生姓名、單位、考號?？继柧幣牛呵?位為工種編號（如以“0”開頭，請將單元格設置為文本型后再錄入），后2位為工種排隊順序號。

（3）根據考生數據，編排考場人員。操作步驟：①將考生數據中的考生考號復制到一個輔助工作表中（工作表名稱為“考場編排”，可自行定義），此表僅作為編排考場用，不需打印。②利用文本函數LEFT，將編號中的工種編號提取出來，以便后面進行排序。B列中的公式：=LEFT（A2，3），提取A列前3位文本，即提取出了工種編號。③利用文本函數RIGHT，將編號中的工種序號提取出來。C列中的公式：=RIGHT（A2，2）*1，提取A列后2位文本，即提取出了工種序號。提取出序號后，因為是文本型數值，為了以后應用方便，在公式后“*1”，將文本型數值轉換為數值型。④安排考場。一般編排的規則是，每個考場安排2～4個工種。在本例中，35人的考場，每個工種安排不能超過18人，如超過，則會出現同工種考生座位相鄰的情況。如遇工種人數較少，或剩余人數較少的情況，可多工種湊數，形成“18+17”人數的形式，人數多的工種排在最前或最后，以保證打亂工種時，人數多的工種不會相鄰。根據排序序號，可很直觀地看出每個工種的人數，在本例中，序號為001的工種一共28人，序號為002的工種29人，在001工種中選擇“考場”列的前17（或18）個，統一錄入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人未安排，超過的人數則需往后安排到第3考場……按以上規則將“考場”列全部錄入考場號。⑤按考場進行排序。以“考場”列為第1關鍵字，“排序”列為第2關鍵字進行排序。排序后可發現，所有編號均按考場排列好。因此，工種號為001和002的數據按原有的排序號進行了排列，實現了工種的交錯。

（4）考場安排統計表：考場編排完畢后，依照制作“考場安排統計表”（工作表名稱為“考場安排統計表”），通過此表，將每個考場的人數安排、工種安排、每個工種安排的人數及考號范圍都一一錄入，以便今后封裝試卷和進行人數的核驗、統計。

（5）工種統計表：此表中錄入的是工種對應的編號、總人數、每個考場安排人數等。主要有兩個用途：一為工種編號對應表，通過工種與編號的對應，在考場安排表中快速輸入工種;二是人數核對，每個工種安排到哪個考場考試，所有人員是否全部安排（如圖1所示）。

此表A-I列需在“考場安排統計表”安排好后進行錄入，“人數核對”列中公式為“=E4+G4+I4”，與參賽人數進行對比，如不一致，則需檢查是否有考生未安排至考場。

（6）最終實現的考場安排表如圖2所示。

2 制作考場安排表

（1）建立新的工作表（如圖3所示），名稱為“1”（1考場）。根據考場情況，先錄入空的考場安排表：此考場為7行5列模式，每個考場可容納35人。為了更加直觀和美觀，在表中每個座位旁邊均有留空。

（2）將“考場編排”工作表中排好的第1考場人員編號復制到M列中，并在L列加入序號，以便核對人數（輔助單元格）。

（3）在相應的單元格中依次錄入公式，即可將編號填入“考場安排表”中（可按實際需要調整編號安排順序）?！熬幪枴绷兄袃热菪薷模翱紙霭才疟怼敝械臄祿部上鄳摹?/p>

（4）在“考場安排表”中，根據錄入的編號，查詢對應的工種，并顯示出來。①工種、編號對應的數據區域定義名稱，以便在查詢的公式中使用。可利用現有的“工種統計表”中數據定義。選中數據區域，在名稱框中輸入“gz”，按回車鍵，即可定義名稱為“gz”的數據區域。②根據編號查詢對應的工種名稱。在B5單元格中，輸入“=IF（ISERROR（VLOOKUP（MID（B6，1，3）*1，gz，2，FALSE）），""，VLOOKUP（MID（B6，1，3）*1，gz，2，FALSE））”。③將上述公式復制到需要顯示工種內容的所有單元格中（注意：此處僅需復制，不需逐個輸入），Excel會根據不同單元格，自動引用相應的單元格。至此，考場安排表基本信息錄入完畢。為了美觀，可將輔助單元格中的內容字體改為白色。

3 在每個考場顯示工種、起止編號、人數

在多工種的考試中，為了更好地讓監考人員核實考場中各工種的人數及試卷，在考場安排表中顯示每個工種的人數無疑是很重要的。我們可以看到，在最終實現的“考場安排表”中從D26單元格開始，到J27單元格是顯示考場的工種、編號和人數的，這些內容可以隨著考場號的變化自動更新。步驟如下。

（1）B2單元格中內容為考場號，因此我們以B2單元格確定當前的考場。為了更方便地在公式中查詢到考場號，B2單元格中需要一個數值而不是字符串。如“第1考場”，在B2單元格中輸入“1”，設置單元格格式—自定義數字格式— "第"#"考場"，在B2單元格中直接輸入數值，即可自動顯示“第*考場”。

（2）B26-B29單元格（輔助單元格）中輸入以下內容：=MATCH（$B$2，考場安排統計表！A：A，0）;=MATCH（$B$2，考場安排統計表！A：A，0）+1;=MATCH（$B$2，考場安排統計表！A：A，0）+2;=MATCH（$B$2，考場安排統計表！A：A，0）+3。使用MATCH函數查找B2單元格中的內容（考場號），在“考場安排統計表”A列中對應數字的行號。因最多一個考場只有4個工種，B27-B29中依次+1，即可將本考場中所有工種都查找到對應的行號，本例返回的結果依次是2、3、4、5。

（3）A26-A29單元格（輔助單元格）輸入下列公式：="考場安排統計表！A"&B26&"：$I$185"。這個公式結果是一個字符串，旨在將B26單元格查找到的行號與“考場安排統計表！A”文本串及后面的"：$I$185"連接起來。本例中，B26查詢到“1”考場第1個工種在第2行，與上述字符串連接的結果如下：“考場安排統計表！A2：$I$185”，這是“考場安排統計表”中A2單元格到I185單元格的一個區域，這個字符串將在后面的公式中用到。依次向下填充3行，實現相對應內容的查詢。

（4）D26-D29單元格輸入下列公式：=IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），3，0）），""，VLOOKUP（$B$2，INDIRECT（A26），3，0））。

此公式用來返回B2單元格中的內容（即考場號），在A26單元格的字符串引用區域中第3列的內容，即返回了當前考場的工種編號，在這里同樣可以用“自定義數字格式”來顯示“*號工種”，用法同前。IF函數和ISERROR函數用來判斷VLOOKUP函數是否返回了數值，如返回的是錯誤值，即顯示為空。公式中用到了INDIRECT函數，用來返回由文本字符串指定的引用。依次向下填充3行，實現相對應內容的查詢。

（5）F26-F29單元格輸入下列公式：=IF（ISERROR（VLOOKUP（D26，gz，2）&"："），""，VLOOKUP（D26，gz，2）&"："），此公式用來返回D26單元格的內容在之前定義的“gz”數據區域（“工種統計表”中的數據）中第2列的內容，即工種號對應的工種名稱。依次向下填充3行，實現相對應內容的查詢。

（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單元格中的內容，在A26單元格的字符串引用區域中第6～8列的內容，即返回了當前考場工種的起止編號。依次向下填充3行，實現相對應內容的查詢。

（7）J26-J29單元格輸入下列公式：=IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），5，0）），""，VLOOKUP（$B$2，INDIRECT（A26），5，0））。

此公式用來返回B2單元格中的內容，在A26單元格的字符串引用區域中第5列的內容，即返回了當前考場工種的人數。依次向下填充3行，實現相對應內容的查詢。

4 總結

Excel在多工種、多單位需打亂次序的考試中得到普遍的應用，從2009年至今，筆者單位的技能大賽一直在運用Excel工作簿進行考場的編排工作。因筆者水平有限，所以僅用Excel函數實現了上述功能，在實際運用中，需要具備一定Excel基礎的人才能熟練掌握。今后，筆者將進一步探索利用編程語言+數據庫完成此工作，在數據的嚴謹性、程序的易用性方面會更趨完善。

參 考 文 獻

[1]Microsoft.Excel幫助文件[EB/OL].https：//support.office.com/zh-cn/excel，2003-05-03.

[2]Excel Home.Excel 2013函數與公式應用大全[M].北京：北京大學出版社，2016.

[3]Excel Home.Excel 2013實戰技巧精粹[M].北京：人民郵電出版社，2015.

[責任編輯：陳澤琦]