巧用Excel函數實現中職招生數據的自動統計

（佛山市高明區高級技工學校 廣東佛山 528000）

在日常的工作中，經常會遇到大量的數據需要進行統計分析。Excel具有強大的數據處理能力，但方法不當也是事倍功半。往年學校招生決策時，都是采用自動篩選加人工統計的方法進行基礎的數據分析，致使決策滯后。在實際運用的摸索中，探究出巧用Excel函數實現招生數據的自動統計，希望此文能夠開拓視角，幫助大家解決工作中遇到其他類似數據統計問題。

一、職業學校招生信息錄入及統計過程中存在的問題

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

二、巧用函數實現招生信息的快速錄入和數據統計

對于有豐富招生經驗的領導來說，現場招錄新生數據的及時統計是他們做出準確判斷和果斷決策的先決條件。如何快速錄入和統計分析出一組數據呢？下面就以一張“新生報名登記表”為例，通過使用SUMPRODUCT等函數功能來解決關鍵數據的自動統計分析的問題。

首先在Excel工作薄中建立三張工作表，分別為：“招新生登記表”用來錄入新生信息；“數據分析表”用來實現數據的自動統計；“使用說明”介紹使用方法和注意事項。“招新生登記表”需要對數據進行統籌安排和約束；在“數據分析”表中利用Excel強大的函數功能事先設定好函數，使其輕松實現數據的自動統計，第一時間為招生決策提供最準確、最有價值的數據統計信息。

1.快速錄入——“數據有效性”來幫忙

招生點工作人員需要將新生的招錄信息錄入到Excel表格中，一要速度、二要準確、三要方便數據的統計。例如：錄入專業可能存在“汽車維修（高）”或“汽高”等不同的表達，這樣的不確定性會導致函數統計的錯誤，而且錄入文字也浪費時間。如何實現快速錄入又保障函數統計正確性？且看Excel“數據有效性”命令來幫忙。

“數據有效性”可以限定格式，錄入學生信息時只需單擊選擇單元格的下拉列表項即可。該命令即可規范錄入，又能讓函數的設計變得簡潔、有效，最大限度的避免錯誤和統計失敗，并且提高了錄入的速度。

在“招新生登記表”中，需要對“畢業中學、性別、報讀/錄取專業名稱、錄取狀態、是否注冊、現場/網絡錄取可能存在的重復”等六個字段進行“數據有效性”的定義。以字段“錄取狀態”為例，它只可能存在2種狀態：已錄和待錄。設置步驟如下：

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

Step2：執行“數據”菜單的“有效性”命令；

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

Step4：點擊“確定”完成設置。

2.自動統計——SUMPRODUCT函數顯身手

在招生過程中，領導最關心的一組數據：各專業的錄取人數，繳費注冊人數、待錄人數和初步判斷網錄和現場錄可能存在的重復。通過檢測這組數據的變化，招生負責人可及時做出分析決策、調整和下達招生命令、協調把控全局。

從壟作坡耕地和復墾循坡耕地的樣點的 7Be含量分布情況來看(表1)，在一個侵蝕季節內的雨季開始前期(5月)和雨季后期(8月)不同階段，壟作坡耕地表土 7Be含量的相對變化是雨季后期(8月)大于雨季開始前期(5月)，農作物種植和鋤耕活動促進了表層土壤顆粒中 7Be的再分散趨勢，并且在及雨季開始后土壤顆粒出現明顯運移。而在復墾循坡耕地的情況則相反，是雨季開始前期(5月)大于雨季后期(8月)，表明無翻地情況的休耕坡地的坡度與坡位等微地形特征造成了 7Be含量的不均勻分布，其后由撂荒坡地轉變為種植坡地的鋤耕擾動出現表層土壤顆粒混合而使 7Be含量的相對均勻分布特征。

如何快速統計出這一組數據呢？利用SUMPRODUCT函數功能，就能輕松搞定。

SUMPRODUCT函數它有一項非常酷的功能：多條件計算，即計算符合2個或2個以上條件的個數。例如：=Sumproduct（（A1:A50=數值1）*（B1:B50=數值2）），用來統計第1行到第50行，A列等于數值1,同時 B列等于數值2的個數。

SUMPRODUCT函數如此強大，大家來看看它在招生關鍵數據的統計上是如何大顯身手的。

（1）數據表

表1 部分關鍵數據表

如上表1中的H、M、N和R列分別表示：報讀專業、錄取狀態、否繳費注冊、是否重復，這些都是SUMPRODUCT函數統計時所需的關鍵數據。在設計表格時，考慮到文件的實用性，設計“招新生登記表”和“數據分析表”2個表格中，在統計表格中在預先定義好函數。[2]

（2）一招搞定函數統計

簡單的原理、相同的函數輕松搞定全部關鍵數據的自動統計，為招生決策及時的提供數據依據。以數控高級工專業為例，數據統計的函數如下（跨表使用）：

眼花嗎？其實原理很簡單：求各列數據同時滿足條件的個數。看起來復雜，知曉原理之后就會覺得簡單，設置其他專業的統計函數，只需要將上述公式中的專業名稱修改一下就可以了。

3.有效核對——COUNT家族和SUM聯手巧保障

事先定義好統計函數的“招新生登記表”在實際運用初期，發現各招生點信息匯總后的數據總序號和統計表的合計人數對不上？數據統計正確性的問題產生了，該如何及時發現和防范統計誤差呢？

從不同的角度思考同一個問題，用不同的方法統計同一個數據，若得出相同結論，那么數據的正確性就能到有效的保障。Excel表格提供了豐富的函數，我們一起看看COUNT函數家族和SUM函數如何聯手巧妙的保障招生統計結果的有效核對，幫助我們及時發現和更正統計錯誤。

保障一、從細處著眼核查

設計一組COUNTIF函數，用來核對SUMPRODUCT函數統計出來的數據是否有誤，即從細處著眼核查。以數控高級工專業為例，用COUNTIF函數對招生登記表的H列，求出滿足特定專業的單元格個數。其函數和邏輯關系表述如表2所示。

表2 函數和邏輯關系表述

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

保障二、從全局把控，數據兩兩呼應

（1）在“數據分析表”即上圖統計數據的基礎上，用SUM函數直接對上方求和，即由分而總，按已錄取人數、待錄人數和注冊人數分別統計，結果用來相互印證。

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

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

多方位多角度考慮，采用不同的函數，若數據間能兩兩呼應，相互印證，即可確保統計的數據正確無誤。倘若數據之間不能相互呼應，出現不一致情況，“保障一”所描述的數據一定先出錯，從細處著眼，能很快發現錯誤的源頭。

三、結論與建議

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

巧用Excel函數實現招生數據的自動統計在實際運用中成效顯著，實現招生及時決策、調整和下達招生命令、有效協調把控全局。在實際的工作中，大家會遇到很多類似的統計問題，建議三招解決自動統計。招數一：使用EXCEL中“數據有效性”命令設計數據項列表。招數二：使用SUMPRODUCT函數搞定同時滿足多個條件的自動統計。招數三：采用COUNT、COUNTIF和SUM函數保障數據的有效性。

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

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