巧用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