巧用Excel函數(shù)實(shí)現(xiàn)中職招生數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)

（佛山市高明區(qū)高級(jí)技工學(xué)校 廣東佛山 528000）

在日常的工作中，經(jīng)常會(huì)遇到大量的數(shù)據(jù)需要進(jìn)行統(tǒng)計(jì)分析。Excel具有強(qiáng)大的數(shù)據(jù)處理能力，但方法不當(dāng)也是事倍功半。往年學(xué)校招生決策時(shí)，都是采用自動(dòng)篩選加人工統(tǒng)計(jì)的方法進(jìn)行基礎(chǔ)的數(shù)據(jù)分析，致使決策滯后。在實(shí)際運(yùn)用的摸索中，探究出巧用Excel函數(shù)實(shí)現(xiàn)招生數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)，希望此文能夠開拓視角，幫助大家解決工作中遇到其他類似數(shù)據(jù)統(tǒng)計(jì)問題。

一、職業(yè)學(xué)校招生信息錄入及統(tǒng)計(jì)過程中存在的問題

當(dāng)今的初中畢業(yè)生在網(wǎng)上填報(bào)志愿時(shí)存在很大的隨意性，對專業(yè)的選擇也搖擺不定，導(dǎo)致歷年網(wǎng)上錄取的學(xué)生到校注冊率不高，流動(dòng)性較大。中職學(xué)校的招生工作通常在中考成績公布后、網(wǎng)上錄取公布之前就拉開了序幕。這樣就存在著幾個(gè)需要協(xié)調(diào)的問題：各專業(yè)每天現(xiàn)場招錄了多少人？預(yù)留多少名額給網(wǎng)上錄取的學(xué)生？火爆專業(yè)錄取的及時(shí)控制和協(xié)調(diào)好各專業(yè)錄取的平衡？因此各專業(yè)的錄取人數(shù)，繳費(fèi)注冊人數(shù)、待錄人數(shù)和初步判斷網(wǎng)錄和現(xiàn)場錄可能存在的重復(fù)等數(shù)據(jù)需要即時(shí)的統(tǒng)計(jì)，它們影響著招生負(fù)責(zé)人及時(shí)做出分析決策、調(diào)整和下達(dá)招生命令、協(xié)調(diào)把控全局的作用。[1]

二、巧用函數(shù)實(shí)現(xiàn)招生信息的快速錄入和數(shù)據(jù)統(tǒng)計(jì)

對于有豐富招生經(jīng)驗(yàn)的領(lǐng)導(dǎo)來說，現(xiàn)場招錄新生數(shù)據(jù)的及時(shí)統(tǒng)計(jì)是他們做出準(zhǔn)確判斷和果斷決策的先決條件。如何快速錄入和統(tǒng)計(jì)分析出一組數(shù)據(jù)呢？下面就以一張“新生報(bào)名登記表”為例，通過使用SUMPRODUCT等函數(shù)功能來解決關(guān)鍵數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)分析的問題。

首先在Excel工作薄中建立三張工作表，分別為：“招新生登記表”用來錄入新生信息；“數(shù)據(jù)分析表”用來實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)；“使用說明”介紹使用方法和注意事項(xiàng)?！罢行律怯洷怼毙枰獙?shù)據(jù)進(jìn)行統(tǒng)籌安排和約束；在“數(shù)據(jù)分析”表中利用Excel強(qiáng)大的函數(shù)功能事先設(shè)定好函數(shù)，使其輕松實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)，第一時(shí)間為招生決策提供最準(zhǔn)確、最有價(jià)值的數(shù)據(jù)統(tǒng)計(jì)信息。

1.快速錄入——“數(shù)據(jù)有效性”來幫忙

招生點(diǎn)工作人員需要將新生的招錄信息錄入到Excel表格中，一要速度、二要準(zhǔn)確、三要方便數(shù)據(jù)的統(tǒng)計(jì)。例如：錄入專業(yè)可能存在“汽車維修（高）”或“汽高”等不同的表達(dá)，這樣的不確定性會(huì)導(dǎo)致函數(shù)統(tǒng)計(jì)的錯(cuò)誤，而且錄入文字也浪費(fèi)時(shí)間。如何實(shí)現(xiàn)快速錄入又保障函數(shù)統(tǒng)計(jì)正確性？且看Excel“數(shù)據(jù)有效性”命令來幫忙。

“數(shù)據(jù)有效性”可以限定格式，錄入學(xué)生信息時(shí)只需單擊選擇單元格的下拉列表項(xiàng)即可。該命令即可規(guī)范錄入，又能讓函數(shù)的設(shè)計(jì)變得簡潔、有效，最大限度的避免錯(cuò)誤和統(tǒng)計(jì)失敗，并且提高了錄入的速度。

在“招新生登記表”中，需要對“畢業(yè)中學(xué)、性別、報(bào)讀/錄取專業(yè)名稱、錄取狀態(tài)、是否注冊、現(xiàn)場/網(wǎng)絡(luò)錄取可能存在的重復(fù)”等六個(gè)字段進(jìn)行“數(shù)據(jù)有效性”的定義。以字段“錄取狀態(tài)”為例，它只可能存在2種狀態(tài)：已錄和待錄。設(shè)置步驟如下：

Step1：在需要錄入數(shù)據(jù)的位置單擊，即選定單元格；

Step2：執(zhí)行“數(shù)據(jù)”菜單的“有效性”命令；

Step3：在彈出的對話框中進(jìn)行設(shè)置，有效性條件“允許”設(shè)置為“序列”，“來源”下方的文本框中輸入：1.已錄,2.待錄。

Step4：點(diǎn)擊“確定”完成設(shè)置。

2.自動(dòng)統(tǒng)計(jì)——SUMPRODUCT函數(shù)顯身手

在招生過程中，領(lǐng)導(dǎo)最關(guān)心的一組數(shù)據(jù)：各專業(yè)的錄取人數(shù)，繳費(fèi)注冊人數(shù)、待錄人數(shù)和初步判斷網(wǎng)錄和現(xiàn)場錄可能存在的重復(fù)。通過檢測這組數(shù)據(jù)的變化，招生負(fù)責(zé)人可及時(shí)做出分析決策、調(diào)整和下達(dá)招生命令、協(xié)調(diào)把控全局。

從壟作坡耕地和復(fù)墾循坡耕地的樣點(diǎn)的 7Be含量分布情況來看(表1)，在一個(gè)侵蝕季節(jié)內(nèi)的雨季開始前期(5月)和雨季后期(8月)不同階段，壟作坡耕地表土 7Be含量的相對變化是雨季后期(8月)大于雨季開始前期(5月)，農(nóng)作物種植和鋤耕活動(dòng)促進(jìn)了表層土壤顆粒中 7Be的再分散趨勢，并且在及雨季開始后土壤顆粒出現(xiàn)明顯運(yùn)移。而在復(fù)墾循坡耕地的情況則相反，是雨季開始前期(5月)大于雨季后期(8月)，表明無翻地情況的休耕坡地的坡度與坡位等微地形特征造成了 7Be含量的不均勻分布，其后由撂荒坡地轉(zhuǎn)變?yōu)榉N植坡地的鋤耕擾動(dòng)出現(xiàn)表層土壤顆?；旌隙?7Be含量的相對均勻分布特征。

如何快速統(tǒng)計(jì)出這一組數(shù)據(jù)呢？利用SUMPRODUCT函數(shù)功能，就能輕松搞定。

SUMPRODUCT函數(shù)它有一項(xiàng)非?？岬墓δ埽憾鄺l件計(jì)算，即計(jì)算符合2個(gè)或2個(gè)以上條件的個(gè)數(shù)。例如：=Sumproduct（（A1:A50=數(shù)值1）*（B1:B50=數(shù)值2）），用來統(tǒng)計(jì)第1行到第50行，A列等于數(shù)值1,同時(shí) B列等于數(shù)值2的個(gè)數(shù)。

SUMPRODUCT函數(shù)如此強(qiáng)大，大家來看看它在招生關(guān)鍵數(shù)據(jù)的統(tǒng)計(jì)上是如何大顯身手的。

（1）數(shù)據(jù)表

表1 部分關(guān)鍵數(shù)據(jù)表

如上表1中的H、M、N和R列分別表示：報(bào)讀專業(yè)、錄取狀態(tài)、否繳費(fèi)注冊、是否重復(fù)，這些都是SUMPRODUCT函數(shù)統(tǒng)計(jì)時(shí)所需的關(guān)鍵數(shù)據(jù)。在設(shè)計(jì)表格時(shí)，考慮到文件的實(shí)用性，設(shè)計(jì)“招新生登記表”和“數(shù)據(jù)分析表”2個(gè)表格中，在統(tǒng)計(jì)表格中在預(yù)先定義好函數(shù)。[2]

（2）一招搞定函數(shù)統(tǒng)計(jì)

簡單的原理、相同的函數(shù)輕松搞定全部關(guān)鍵數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)，為招生決策及時(shí)的提供數(shù)據(jù)依據(jù)。以數(shù)控高級(jí)工專業(yè)為例，數(shù)據(jù)統(tǒng)計(jì)的函數(shù)如下（跨表使用）：

眼花嗎？其實(shí)原理很簡單：求各列數(shù)據(jù)同時(shí)滿足條件的個(gè)數(shù)?？雌饋韽?fù)雜，知曉原理之后就會(huì)覺得簡單，設(shè)置其他專業(yè)的統(tǒng)計(jì)函數(shù)，只需要將上述公式中的專業(yè)名稱修改一下就可以了。

3.有效核對——COUNT家族和SUM聯(lián)手巧保障

事先定義好統(tǒng)計(jì)函數(shù)的“招新生登記表”在實(shí)際運(yùn)用初期，發(fā)現(xiàn)各招生點(diǎn)信息匯總后的數(shù)據(jù)總序號(hào)和統(tǒng)計(jì)表的合計(jì)人數(shù)對不上？數(shù)據(jù)統(tǒng)計(jì)正確性的問題產(chǎn)生了，該如何及時(shí)發(fā)現(xiàn)和防范統(tǒng)計(jì)誤差呢？

從不同的角度思考同一個(gè)問題，用不同的方法統(tǒng)計(jì)同一個(gè)數(shù)據(jù)，若得出相同結(jié)論，那么數(shù)據(jù)的正確性就能到有效的保障。Excel表格提供了豐富的函數(shù)，我們一起看看COUNT函數(shù)家族和SUM函數(shù)如何聯(lián)手巧妙的保障招生統(tǒng)計(jì)結(jié)果的有效核對，幫助我們及時(shí)發(fā)現(xiàn)和更正統(tǒng)計(jì)錯(cuò)誤。

保障一、從細(xì)處著眼核查

設(shè)計(jì)一組COUNTIF函數(shù)，用來核對SUMPRODUCT函數(shù)統(tǒng)計(jì)出來的數(shù)據(jù)是否有誤，即從細(xì)處著眼核查。以數(shù)控高級(jí)工專業(yè)為例，用COUNTIF函數(shù)對招生登記表的H列，求出滿足特定專業(yè)的單元格個(gè)數(shù)。其函數(shù)和邏輯關(guān)系表述如表2所示。

表2 函數(shù)和邏輯關(guān)系表述

只要用兩種不同函數(shù)統(tǒng)計(jì)出來的數(shù)據(jù)能保持一致，即：已錄取人數(shù)+待錄人數(shù)=核對用數(shù)據(jù)，說明統(tǒng)計(jì)數(shù)據(jù)正確。

保障二、從全局把控，數(shù)據(jù)兩兩呼應(yīng)

（1）在“數(shù)據(jù)分析表”即上圖統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上，用SUM函數(shù)直接對上方求和，即由分而總，按已錄取人數(shù)、待錄人數(shù)和注冊人數(shù)分別統(tǒng)計(jì)，結(jié)果用來相互印證。

（2）從大處著眼，減少條件約束，保障統(tǒng)計(jì)正確性。最能直觀反映招錄情況的是M列的“錄取狀態(tài)”，對M列進(jìn)行單一條件的統(tǒng)計(jì)，數(shù)據(jù)準(zhǔn)確且具有較強(qiáng)的核對功能。如：函數(shù)COUNTIF（招新生登記表！M:M,"=1.已錄"）、COUNTIF（招新生登記表！M:M,"=2.待錄"）對M列統(tǒng)計(jì)已錄和待錄人數(shù)，只要“已錄+待錄=總的報(bào)名人數(shù)序號(hào)”就表示數(shù)據(jù)統(tǒng)計(jì)無誤。

（3）拋開所有的信息干擾，直接使用COUNT函數(shù)統(tǒng)計(jì)B列（序號(hào)）包含數(shù)字的個(gè)數(shù),也就是合計(jì)報(bào)名的人數(shù)。函數(shù)為：=COUNT（招新生登記表！B:B），同時(shí)這個(gè)數(shù)應(yīng)該和“招錄新生登記表”中的A列總序號(hào)、上述1和2中得到的合計(jì)人數(shù)都保持一致。

多方位多角度考慮，采用不同的函數(shù)，若數(shù)據(jù)間能兩兩呼應(yīng)，相互印證，即可確保統(tǒng)計(jì)的數(shù)據(jù)正確無誤。倘若數(shù)據(jù)之間不能相互呼應(yīng)，出現(xiàn)不一致情況，“保障一”所描述的數(shù)據(jù)一定先出錯(cuò)，從細(xì)處著眼，能很快發(fā)現(xiàn)錯(cuò)誤的源頭。

三、結(jié)論與建議

近兩年的招生都使用此統(tǒng)計(jì)表完成招生信息的錄入和統(tǒng)計(jì)，跟之前傳統(tǒng)的方法相比，工作效率顯著的提升，同時(shí)解決了以往招生過程中決策相對滯后問題和其他困難。實(shí)踐證明：方法決定效率。

巧用Excel函數(shù)實(shí)現(xiàn)招生數(shù)據(jù)的自動(dòng)統(tǒng)計(jì)在實(shí)際運(yùn)用中成效顯著，實(shí)現(xiàn)招生及時(shí)決策、調(diào)整和下達(dá)招生命令、有效協(xié)調(diào)把控全局。在實(shí)際的工作中，大家會(huì)遇到很多類似的統(tǒng)計(jì)問題，建議三招解決自動(dòng)統(tǒng)計(jì)。招數(shù)一：使用EXCEL中“數(shù)據(jù)有效性”命令設(shè)計(jì)數(shù)據(jù)項(xiàng)列表。招數(shù)二：使用SUMPRODUCT函數(shù)搞定同時(shí)滿足多個(gè)條件的自動(dòng)統(tǒng)計(jì)。招數(shù)三：采用COUNT、COUNTIF和SUM函數(shù)保障數(shù)據(jù)的有效性。

[1]王道才.用Excel完成醫(yī)療卡信息錄入校對工作[J].電腦知識(shí)與技術(shù)，2016(1):36-39

[2]馮曉偉.一次性填充Excel中大量不相鄰空格[J].電腦知識(shí)與技術(shù)，2017(1):43-44