EXCEL多条件统计报表的自动生成方法研究 ——以学生体质健康标准数据管理为例
2012-03-03 01:28阅读：
EXCEL多条件统计报表的自动生成方法研究
——以学生体质健康标准数据管理为例
清流一中 罗维兴
摘
要
在教学评价的数据管理过程中，我们经常要对数据进行符合多个条件的统计。本文以学生体质健康标准数据管理为例，研究分析利用excel软件的公式进行多条件统计报表的生成方法。研究结果认为excel的数组公式sumproduct可以实现教学评价数据的多条件统计，为方便体育教师在实际工作中的数据管理，也可为教学中的其它学科的同类应用提供借鉴。
1、
前言
《国家学生体质健康标准》（以下简称《标准》）的实施是一项庞大的系统工程，涉及到各级各类学校、各级教育行政部门和体育行政部门，是一项促进学生健康成长，养成良好的生活方式和锻炼习惯，对全民健康素质的提高具有基础性作用的重要政策。为加快推动《标准》的实施，并准确地反映我国学生体质健康
状况，推进学校体育工作科学化、信息化，也为行政管理和决策提供依据，根据
《标准》实施办法”的有关规定，教育部建设了“国家学生体质健康标准数据管理系统
MIS”，并要求全国各级各类学校今后均应通过互联网络，直接将本校《标准》的测试数据，报送至教育部“国家数据库”。为确保准确、规范、科学地处理、汇总上报的数据，同时减轻各级相关部门的工作量，提高工作效率，教育部体卫艺司组织开发了《国家学生体质健康标准数据管理系统——数据上报软件》，是采集、存储、统计、分析全国学生体质情况测试数据的大型综合计算机信息管理系统。该软件可以对学生体质健康测试成绩进行自动评分，和打印学生个人的总评表。但是不能自动生成自定义格式的班级表，年级报表及上交到县进修学校的“福建省学生体质健康标准测试统计表”。
我们在教学中也会遇到许多报表的制作，其本质主要是对原始数据进行符合多个条件的统计。本文根据我校体育组近年来的实际使用经验，阐述利用Excel公式进行多条件统计，解决实际工作中的问题。
2、
明确任务
任务的本质是对原数据进行符合多个件的统计。
本研究的原数据采用上报软件导出的数据表。其特点是全校学生每人一条记录，每条记录的字段有：年级编号、班号、班级、学号、民族代码、姓名、性别、出生日期、学生来源、家庭住址、身高、体重、身高体重分数、身高体重等级、肺活量、肺活量体重指数、肺活量体重分数、肺活量体重等级；以及耐力类、柔韧、力量类、灵巧类的编号、成绩、等级和奖惩项目编号、奖惩成绩、测试总分、总分等级共34个字段。
本研究要制作的是班级表、学校统计表、和上报表（福建省学生体质健康标准测试统计表）。与原始数据的详细特点相比，报表的特点主要是概览符合某些条件要求的记录的统计
结果。要解决的问题就是按要求利用公式实现对原始数据的自动统计。
3、
选择公式
3.1
Excel一般统计公式（常用函数）的不足
使用Excel一般统计公式（常用函数）sum,if,count,countif,sumif等主要是进行无条件或单条件求和、计数等，只使用一个公式可以实现全体或单一条件的总分，平均分，及人数的统计。如果是使用公式组合，加入条件逻辑运算公式（and、or、not）则会出现公式太冗长，易出错，不易套用（自动序列）等不足。
3.2
Excel数组公式sumproduct的特点
为了实现对多组数据之间进行运算excel中引入了数组公式概念，设计了一系列的数组公式。Sumproduct就是可以实现利用单个公式就可以对多维数据进行多条件统计的公式。sumproduct函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。其用法：SUMPRODUCT(array1,array2,array3, ...)其中Array1, array2, array3, ... 为
2 到 30
个数组，其相应元素需要进行相乘并求和。官方解释还特别说明两点：
·
数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
· 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
因此我们在实际应用中应注意保持数组参数的维数要相同，即每个数组中的数据个数是一样的；同时为了保证数组元素是数值型元素，在公式应用时添加--运算。--在excel里叫做减负运算，其目的是将字符串格式的数字转变成真正意义上的数字，从而参加运算。我们可以将它理解为两个减号，结果是负负得正，没有改变原数据的正负，但将其变成了数字。
4、解决办法
使用excel公式自动化生成可打印报表。
在实际工作中，我们会需要打印任课班级的成绩表以及按年段统计的学校统计表，在上报软件中没有相关功能。此类功能可以通过设置excel表格的公式以及打印格式来实现自动化生成。总体思路是利用上报软件的导出数据表作为数据源，在EXCEL表格中设置公式以及打印格式，快捷方便的解决问题。图1是整个解决办法的数据簿设计：原始数据放在Sheet1表中，要生成的报表（班级表、分析表、上报表）各放在一个表中。
![说明: C:\Users\lwxdesk\AppData\Local\Temp\ksohtml\wps_clip_image-12700.png]( "EXCEL多条件统计报表的自动生成方法研究 <wbr> <wbr>——以学生体质健康标准数据管理为例")
4.1班级表
班级表主要的数据有班级名称、座号、姓名、性别、各项考试数据及得分和评价。
年级编号
|
班级
|
姓名
|
身高
|
体重
|
身高体重分数
|
肺活量
|
肺活量体重分数
|
耐力类项目成绩
|
耐力类项目分数
|
柔韧、力量类项目成绩
|
柔韧、力量类项目分数
|
速度、灵巧类项目成绩
|
速度、灵巧类项目分数
|
测
试
总
分
|
总
分
等
级
|
以第二行为例，各列的公式如下：
=Sheet1!A2
|
=Sheet1!C2
|
=Sheet1!F2
|
=Sheet1!K2
|
=Sheet1!L2
|
=Sheet1!M2
|
=Sheet1!O2
|
=Sheet1!Q2
|
=Sheet1!T2
|
=Sheet1!U2
|
=Sheet1!X2
|
=Sheet1!Y2
|
=Sheet1!AB2
|
=Sheet1!AC2
|
=Sheet1!AG2
|
=Sheet1!AH2
|
因为班级表和原始数据对比，只是信息的减运算，只需要将被选择的数据自动链入新表只采用了一个简单的＝运算。
4.2学校统计表
在原始数据表中按班级自动统计各班总人数，男生数、女生数、及各等级人数等汇总数据，涉及的问题就是：在全校总表中统计符合年级编号、班级、性别、评级等多个条件的数据。以统计高一（1）班男生人数为例，总体思路就是sumproduct(年级编号＝高一，班级编号＝1,性别＝男)。因其中的数据有些可能不是数值型的，我们采用sumproduct(--（年级编号＝高一）*（班级编号＝1）*（性别＝男））的形式转换，使其可以符合EXCEL软件的要求。具体实现的办法如下：
4.2.1分析表
![说明: C:\Users\lwxdesk\AppData\Local\Temp\ksohtml\wps_clip_image-5227.png]( "EXCEL多条件统计报表的自动生成方法研究 <wbr> <wbr>——以学生体质健康标准数据管理为例")
班级人数统计=SUMPRODUCT(--(Sheet1!$C$2:$C$1736=B2)*(Sheet1!$A$2:$A$1736=A2))
男生人数统计=SUMPRODUCT(--(Sheet1!$C$2:$C$1736=$B2)*(Sheet1!$A$2:$A$1736=$A2)*(Sheet1!$G$2:$G$1736='1'))
女生人数统计=D2-E2
班级总分统计=SUMPRODUCT(--(Sheet1!$C$2:$C$1736=$B2)*(Sheet1!$A$2:$A$1736=$A2)*(Sheet1!$AG$2:$AG$1736))
班级平均分统计=G2/D2
优秀人数统计=SUMPRODUCT(--(Sheet1!$C$2:$C$1736=$B2)*(Sheet1!$A$2:$A$1736=$A2)*(Sheet1!$AH$2:$AH$1736=I$1))
优秀率、良好率、及格率、不及格率的算法可以利用人数进行计算。
4.2上报表
与班级报表类似，也是应用EXCEL的sumproduct公式进行条件统计。具体实现方法可以参考4.1。
![说明: C:\Users\lwxdesk\AppData\Local\Temp\ksohtml\wps_clip_image-21966.png]( "EXCEL多条件统计报表的自动生成方法研究 <wbr> <wbr>——以学生体质健康标准数据管理为例")
4．3重复使用
制作好以上表格后，每学年只需要将《国家学生体质健康标准数据管理系统——数据上报软件》的导出表覆盖到Sheet1表上即可以自动生成后面的几个表，简单、方便、快捷、高效，值得推广借鉴。
5、
结论
本文以学生体质健康标准数据管理为例，研究分析利用excel软件的公式进行多条件统计报表的自动生成方法。研究结果认为excel的数组公式sumproduct可以实现教学评价数据的多条件统计，为方便体育教师在实际工作中的数据管理，也可为教学中的其它学科的同类应用提供借鉴。
