【正文】

“两基”资料统计中电子表格函数的点滴应用与体会

 

 

——应用于“基本普及九年义务教育”各类表格统计的信息管理

 

 

新疆生产建设兵团第六师103团学校 吴洪英

 

 　【摘 要】 随着计算机技术的日渐成熟，信息技术和办公自动化(OA)技术的发展与应用，引发了办公领域里的一场革命，使人们从靠纸张传递信息发展到全新的网络信息时代，使文档工作一体化得以实现。学校的计算机信息管理工作中能够很好的在“两基”（“两基”是基本实施九年义务教育和基本扫除青壮年文盲的简称）资料统计中合理正确运用电子表格应用程序，用于组织，计算和分析数据，可以完成多种任务，尤指数据计算中函数的正确运用及自动处理的功能更是强大，与手工管理相比拥有其无可比拟的优势；例如：检索快速、查找方便、可靠性高、存储量大、保密性好、信息保存时间长、数据更新快速便捷等。合理正确地运用计算机对学校的信息进行有效的管理，是学校计算机信息管理水平走向科学化、正规化、自动化、信息化的重要条件和标志，更是学校全面实现教育信息化的关键。
　　【关键词】 电子表格；函数；If()；Countif()；Sumproduct()

　　电子表格应用程序中的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据以及修改、查询、检索、排序和统计汇总等等。
　　文中所涉及的函数主要有这么几个：If()、Datedif()、Countif()、Sumproduct()。这几个函数在“两基”资料分析统计中经常用得到。函数作为电子表格应用程序处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，您甚至可以用电子表格应用程序来设计复杂的统计管理表格或者小型的数据库系统。这样做的好处是：比用计算机语言和数据库软件编制的各类MIS（管理信息系统）软件用起来，更会得心应手的多。
　　说明：电子表格应用程序有两类被广泛使用着：一是Ms Office Excel；一是Wps Office Et。下文中的实例来源于新疆农六师一0三团学校2009年“两基”统计资料，以Ms Office Excel 2003为主创作平台。当然用Wps Office Et打开并应用也可以。因此在本文中，所有表格的截图都是在Wps Office 2012版的电子表格（ET）即Wps表格中完成的。
　　一、信息输入——建立基础信息表
　　若想很好地把我们各自手头上已有的运用电子表格应用程序整理好的资料，也能够恰当的套用到我的这个工作簿中，那还要有一个准备性的工作，就是按照我的各列列标题的，准确、规范地填写其各列内容，这样才能为后续列项，及各类统计数据的处理正确运用电子表格函数打好基础。
　　首先，将以往的电子表格文件另存为：08-09年两基全团户籍花名册及其各类统计表.Xls。（因为“两基”资料统计的是2008年9月1日至2009年8月31日之间的户籍范围内的人口变动情况）这与我们从2001年开始使用的教育年报管理信息系统统计的是每学年开始起的所有信息的统计不同，“两基”是对过往一学年里至学年末人口变动情况的普查。
　　其次，对“户籍”工作表进行窗口冻结。因为户籍表的列项较多，横向的行记录也很庞大，冻结后，比较利于后续操作且列标题总是被显示在前端。具体操作方法是：单击“M4”单元格→单击“窗口”菜单项→单击“冻结窗口”选项。
　　第三，对“户籍”工作表，以A列，A3单元格的列标题：“序号”即A3单元格为起始单元格至AT16713单元格为结止单元格进行排序，且主要关键字：以从小到大的升序进行排列。
　　具体操作步骤：
　　单击“A3”单元格→移动水平滚动条至AT列→移动垂直滚动条至16713行→按住Shift键不放，再单击AT16713单元格（至此选中了要进行排序的区域或者在名称框内输入A3:AT16713也行）→单击“数据”菜单项→单击“排序”选项→在“排序”对话框里单击列表中的“有标题行”→单击“主要关键字”下拉列表中的“序号”→单击“升序”选项→单击“确定”按钮。如下图1。

 

 

 

 

 

 

图1
　　 
　　第四，对“户籍”工作表中被选中的区域，做自动筛选。具体操作步骤：单击“数据”菜单项→在筛选级联菜单中单击“自动筛选”选项。便于后续操作。
　　第五，对每一列的列标题下所填写的内容做统一的语言规范。例如：
　　1、对于J列，J3单元格的列标题为“民族”的列内，例如：汉族简写为汉，维吾尔族简写为维，土家族简写这土家，土族就写为土族等等。
　　2、对于L列，L3单元格的列标题为“出生年月日”的列内，在英文状态下输入：例如2008.08.08即可。
　　3、对于M列，M3单元格的列标题为“文化程度”的列内：学前；小学；初中；高中；中专；大专；本科；研究生；博士生。
　　4、对于N列，N3单元格的列标题为“学前”，请在N4单元格内输入公式：“=IF(M4>=“学前”,“√”,“”)”，再击“回车”键，即可得出想要的结果，移动鼠标至N4单元格右下角的“黑色正方型填充柄”并“双击”，则可将IF函数公式复制至最后一条记录N16713单元格内或者是按住鼠标左键向下拖拉至N16713单元格。【强调：此操作方法适合下文中，所有复制公式的操作，遇到相同的操作时下文不再赘述。】其目的：能够很好地根据M列“文化程度”运用条件函数IF判断出为“学前”的人口状况。
　　5、对于O列，O3单元格的列标题为“小学学校”，请在从O4至O16713单元格内正确输入普查到的学龄段内学生的“学校名称”，其中在本团内就读的学生，写清楚为：团小学，团一小，团三小，团十连小学，团砖厂小学【这是新疆生产建设兵团所有团场学校才会有的教学点，如果是某一所学校的统计，只写清楚：“某某小学”即可】，这样做的好处是，便于统计出户籍所在地学生在本地学校就读和外地就读的人口的确切人数。
　　6、对于P列，P3单元格的列标题为“小学年（班）级”，请在从P4至P16713单元格内正确输入普查到的学龄段内学生的“年（班）级名称”，教学点无班级的输入为：一年级至六年级；完全小学的输入为：一年级1班至六年级4班。
　　7、对于Q列，Q3单元格的列标题为“中学学校”，请在从Q4至Q16713单元格内正确输入普查到的学龄段内学生的“学校名称”，其中在本团内就读的学生，写清楚为：团中学；在团外中学就读的，明确知道学校名称的写全名，不清楚的写成：“团外初中”和“团外高中”即可。
　　8、对于R列，R3单元格的列标题为“中学年（班）级”，请在从N4至N16713单元格内正确输入普查到的学龄段内学生的“年（班）级名称”，输入为：初一1班至高三5班；或者输入为：初一、初二、初三、高一、高二、高三。
　　9、对于S列（升入高中及以上者）；对于T列（辍学）；对于U列（其它）；对于V列（是否残疾）；对于W列（人户分离）；根据各自实际普查到的结果，一律输入为：“√”。如果需要特殊说明的，请填写在X列（备注）中。
　　第六，在X列（备注）后增加两列：因为我们“两基”普查是以年龄界限为基础而展开的，故而新增加的两列尤为关键和重要。
　　Y列（Y3单元格的列标题为“函数公式认的年月日”），并在Y4单元格内输入公式：“=IF(LEN(L4)=10,MID(L4,1,4)&”-“&MID(L4,6,2)&”-“&MID(L4,9,2))”，再击“回车”键，即可得出想要的结果（既将原来L4单元格内的“2008.08.08”成功转换为“2008-08-08”的年月日格式），【同强调：】。其目的：运用IF函数公式完成了年月日的格式的转换操作且利于下一列中运用Datedif()得出正确的年龄结果。
　　公式含义说明：IF函数说明
　　IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。它的语法为：IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。
　　“=IF(LEN(L4)=10,MID(L4,1,4)&”-“&MID(L4,6,2)&”-“&MID(L4,9,2))”此嵌套函数意义：LEN(L4)=10,指L4单元格内字符的长度；MID(L4,1,4)指从L4单元格内从第1个字符开始取值，共取前四个字符即年；&”-“&两个连字符中间取字符-；再通过If()函数得到结果。
　　Z列（Z3单元格的列标题为“结止年龄2009-08-31”），并在Z4单元格内输入公式：“=DATEDIF(Y4,DATE(2009,8,31),“y”)”，再击“回车”键，即可得出想要的结果（得出准确的年龄值），【同强调：】。
　　公式含义说明：根据“Y4,”单元格内的日期与“DATE(2009,8,31),”通过DATEDIF()函数得出准确的“y”年龄值的计算结果。
　　（需要指出的是：如果在最初我们做户籍表这张工作表时，有“身份证号”这样一列数据，那么我们应用电子表格应用程序通过“身份证号”取出“性别”和“出生日期”以及“年龄”将会更加的便捷。）
　　此时，最好按照Z列的标题，以年龄从小到大的升序，进行排序。
　　二、 信息处理
　　（一）如何准确高效地整理“两基”户籍表中人口变动的项目
　　“两基”户籍工作表中每年续新资料人口变动（通过当地派出所得到：或是纸制；或是数据库形式）的项目分为两大类：一是减少的人口，其中又分为三种情况：死亡人口、移出人口、迁出人口；一是新增加的人口，其中分为两种情况：新生人口和迁入人口。
　　因此，需要在Z列后新增5个列标题项，它们分别是：如下图2

 

 

 

 

图2
　　最好给以固定的编号：例如：2012年死亡人口，可写成：12S001（12指代年份、S指代死亡人口简写、001指代序号）。因此会有S代表死亡人口，Y代表移出人口，C代表迁出人口，X代表新生人口，R代表迁入人口。这样进行编号的好处是：便于数据的统计计算或者是筛选出统计结果。
　　从而我们可以运用：单击“编辑”菜单→单击“查找”选项→在“查找”对话框的“查找内容(N)”里输入：某某某（对照我们从派出所得到的减少人口名单依次查找）→单击“查找全部”即可。这样查找到的结果可能不止一人，因为户籍工作表中有户主和姓名两个列标题，故而，我们应该找的是“姓名”列标题下的人的姓名。
　　用上述方法，我们可以逐一查找到减少人口，而我们只需要做的是在每查找到一个人时，在列标题为“死亡人口年份S001”列内标记好编号，如：9S001……9S123；在列标题为“移出人口年份Y001”列内标记好编号，如：9Y001……9Y123；在列标题为“迁出人口年份C001”列内标记好编号，如：9C001……9C123；然后再按这三列标题分别排序后，先选中要选的内容复制（记住：这里只能是复制，不是剪切，为的是保留这些行的存在，粘入新增人口信息）各项记录粘贴至同一工作簿中新建的死亡人口、移出人口、迁出人口这三张工作表中，每排序并复制粘贴完成后，再回到户籍工作表中，用Delete键删除选中的内容并保留空行。
　　新增加的人口，分别手工录入在：新生人口和迁入人口工作表。并按照户籍工作表的列项，逐列粘贴且勿忘在AD列（新生人口）编号为：9X001…9X123；在AE列（迁入人口）编号为：9R001……9R123。
　　更新P列（“小学年（班）级”），从六年级至一年级依次将六年级替换为无，五年级替换为六年级……。且在替换前，先要将O列中P列为六年级的学校名称清空。
　　更新R列（“中学年（班）级”），高三至初一依次将高三替换为无，高二替换为高三……。且在替换前，先要将Q列中R列为高三的学校名称清空。高一新生，或是初中上中职，高职的，勿忘在S列标记“√”。
　　（二）针对在校内和校外就读状况，通过IF函数进行统计
　　从O列至W列开始，因为分为校内和校外两种读书的状况，且运用了统一的语言来填写每个人的信息。这样做的好处是，可以运用条件函数准确无误地统计标记出人口在“校外或校内”的状况。例如：AF列，列标题为：“在团内小学”，在AF4单元内输入公式：“=IF(O4>=“团*”,“团内小学”,“”)”，再击“回车”键，【同强调：】。其目的：将团内小学的人口标记出来。
　　同理，用上述相同的操作方法并按列标题顺序依次得出：见下表

 

 

 

 

 

 

 

 

 

 

 

 

　　（三）开始按年龄段进行数据统计
　　两基工作各列项统计项目名称及其计算公式解释说明（见下表）
　　请注意：公式在输入完毕后，需要同时按下Ctrl+Shift+Enter这三个组合键来确定计算结果。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

（下转第45页）
（上接第41页）
　　以上表格中的公式应用，仅是我在“两基”信息管理资料统计中的一小部分，限于文章对字数的限制，这里不再赘述。
　　三、 信息输出——跨表引用
　　将“户籍”工作表统计出的数据结果引入相应工作表。
　　最后是各类“两基”统计表中数据的表间引用的正确操作和运用。
　　例如：
　　户 籍 情 况 统 计 表
　　（截止2009年8月31日）             单位：户、人

 

 

 

 

　　其余近20张工作表，都是应用跨表引用，得到相应数据结果的。重点在于信息输入和信息处理的第一部分，数据统计及跨表引用都由电子表格自动完成。
　　结束语：
　　本篇文章中的内容是我在这几年里进行“两基”资料数据录入更新和统计过程中，积累的点滴经验和合理正确运用电子表格函数和跨表引用的一些数据计算和统计方法及自动处理中的强大功能应用，从而将繁琐复杂的“两基”资料的统计工作，变得简单且易于操作，更方便了信息的更新和管理。既减少了人力、物力与财力的投入，也更好地为“两基”中“基本实施九年义务教育”的信息采集、信息录入、信息更新、信息管理提供了一个好的且便于上手运用的模板。其优点在于，即使非计算机专业的人员，只要会电子表格的基本操作，也会使用这个模板。愿与负责“两基”工作的同行进行交流。有不妥之处，请多多指证。