Excel函数在人口计划生育统计工作中的应用_图文_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
Excel函数在人口计划生育统计工作中的应用
E​x​c​e​l​函​数​在​人​口​计​划​生​育​统​计​工​作​中​的​应​用
阅读已结束，如果下载本文需要使用
想免费下载本文？
下载文档到电脑，查找使用更方便
还剩7页未读，继续阅读
你可能喜欢Excel函数在人口计划生育统计工作中的应用--南阳市人口与计划生育委员会
&&您好！欢迎您来到南阳市人口与计划生育委员会。 |
所有栏目工作动态公示公告领导讲话计划规划政策法规奖励扶持计生论坛人事信息人口基础信息核查协会专栏信息化建设
生殖健康避孕药具表格下载优生优育学习实践活动专栏站内搜索专题专栏在线调查
当前位置：&计生论坛内容页
近年来，镇平县人口计生委在省市各级领导关心指导下，按照“互联互通、资源共享、提高效率、推动工作”的原则，逐步加快全县人口和计划生育信息化建设步伐，全面提升信息化在人口计生工作中的应用水平，构建了以人口信息网、政府电子政务网和计划生育信息数据库 “两网一库”为依托，以河南省人口和计划生育管理信息系统（PFPMIS）为龙头，以乡村MIS系统为“终端”的发展格局。全县建成了集信息开发应用、管理服务一体化，上下贯通、左右联接、运转协调、便捷高效，比较完整的信息化体系,有力推进人口计生工作的网络化、科学化、现代化，人口计生系统初步形成了“用数字化的理念来思考，用网络化的方式来沟通，用信息化的技能来工作”的浓厚氛围，提升了全县人口计生工作服务管理水平。
随着信息化的建设及应用，计划生育生育统计工作从纸质台帐时代进入了电子台帐时代，工作效率得到了显著的提高。下面通过计划生育生育统计工作实践中的两个实例来介绍一下EXCEL函数在工作中的应用。一是利用EXCEL函数对 《全县50岁以上计划生育家庭户登记表》个案信息进行汇总。二是利用EXCEL函数对PFPMIS数据库查询数据进行统计，生成报表数据。在这两个实例中使用excel函数大大提高了工作效率，从而帮助我们快速准确地完成工作任务。
一、宏、SUMPRODUCT函数和countif函数
在介绍这两个实例之前先简单介绍一下两个实例例中用到的EXCEL知识：宏、SUMPRODUCT函数和countif函数。
1、术语宏。宏是微软公司为其OFFICE软件包设计的一个特殊功能，目的是让用户文档中的一些任务自动化。OFFICE中的WORD和EXCEL都有宏。
如果在EXCEL中重复进行某项工作，可用宏使其自动执行。宏是将一系列的EXCEL命令和指令组合在一起，形成一个命令，以实现任务执行的自动化。您可创建并执行一个宏，以替代人工进行一系列费时而重复的 EXCEL操作。
2、SUMPRODUCT函数简介：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。
说明：数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
示例：如果您将示例复制到空白工作表中，可能会更易于理解该示例。
SUMPRODUCT函数实现多条件统计功能，如在本例中统计A列值为3同时B列值为4的行数，可将公式改为=SUMPRODUCT（(A2：A4=3)*（ B2:B4=4)），计算结果为1，说明:A2=3 返回1，B2=4返回1，A3=8≠3返回0，B3=6≠4返回0，A4=1≠3返回0，B4=9≠4返回0，
1×1+0×0+0×0=1。
3、COUNTIF函数简介：计算区域中满足给定条件的单元格的个数。
COUNTIF(range,criteria)
Range为需要计算其中满足条件的单元格数目的单元格区域。
Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "apples"。
说明：Microsoft Excel 提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF 工作表函数。
二、EXCEL函数应用实例
（一）应用函数对《计划生育家庭户调查表》进行分类统计
由于PFPMIS中没有计划生育家庭的信息，为了了解我县计划生育家庭规模，我们通过快速调查采集到全县50岁以上计划生育家庭的信息，录为《镇平县50岁以上计划生育家庭登记表》（如上图）。表中的一行记录一个计划生育家庭户的信息。我们需要依据此表统计出全县50岁以上计划生育家庭户中领取独生子女光荣证户、独男户、独女户、领证独男户、领证独女户、双女结扎户等的数量。
1、领取独生子女光荣证户的计算。表中的N列如果为“是”则说明该户是领取独生子女光荣证户，使用条件统计函数countif，在相应单元格输入“=COUNTIF(N4:N8694,"是")”后按下回车键单元格的值为296，即50岁以上领取独生子女光荣证户数。（见下图）
2、独男户的计算。
即统计表中从第4行起到最后第8694 行J列（现有男孩）为1、K列为0的行数，使用SUMPRODUCT函数，我们在U4单元格中输入“=SUMPRODUCT((J4:J8694=1)*(K4:K8694=0))”后按下回车，单元格值为1261，即要计算的独男户数。（见上图）
3、其他户数的计算。同样可以使用SUMPRODUCT函数：政策内双女户的计算使用公式“=SUMPRODUCT((J4:J8694=0)*(K4:K8694=2))”，独女户的计算使用公式“=SUMPRODUCT((J4:J8694=0)*(K4:K8694=1))”，领证独男户使用公式“=SUMPRODUCT((J4:J8694=1)*(K4:K8694=0)*（N4：N8694="是"）)”，双女结扎户的计算使用公式“=SUMPRODUCT((J4:J8694=0)*(K4:K8694=2)*（M4：M8694="女性绝育术"）)”。
（二）利用EXCEL函数将PFPMIS数据库出生节育查询结果统计为出生节育报表
1、工作需求。利用EXCEL函数对PFPMIS数据库出生节育查询结果统计为出生节育报表的工作需求有以下几点：一是PFPMIS系统生成报表的时间为每月6日，因而乡级无法在6日以前上报报表，也无法在6日前分析本乡镇上月末主要工作指标的运行情况；二是PFPMIS生成的报表以后不再再次生成，个别迟报的信息便不能及时准确地反映PFPMIS报表中。为了解决这一问题，我们可以利用PFPMIS查询结果生成出生节育报表和一览表。
2、工作表介绍。在上图所示的工作簿中，报表工作表是乡级出生、节育情况报表；出生情况查询工作表中存放的是从河南省人口和计划生育管理信息系统（PFPMIS）查询到的一个乡2008年全年的全部出生信息（出生情况查询的结果应包括出生后死亡的小孩）；手术查询工作表中存放的是从河南省人口和计划生育管理信息系统（PFPMIS）查询到的一个乡2008年全年的全部手术信息；城镇出生查询工作表中存放的是从河南省人口和计划生育管理信息系统（PFPMIS）查询到的一个乡2008年全年城镇居民的全部出生信息；城镇手术查询工作表中存放的是从河南省人口和计划生育管理信息系统（PFPMIS）查询到的一个乡2008年全年城镇居民的全部手术信息。每一个出生信息、每一例手术信息都在工作表的一行中记录。
报表工作表“导入村名”按钮的作用是从出生情况查询表（见下图）将该乡所辖村名按顺序填充到报表工作表的A列中，这一操作是通过编写的宏实现的，各村在报表工作表中的顺序和MIS报表中的顺序完全一致。“统计时段”后面的两个单元格输入时间段，如要生成九月份出生、节育情况报表分别输入、。
3、操作方法。在报表工作表、出生查询工作表、手术查询工作表、城镇出生查询工作表、城镇手术查询工作表中粘入PFPMIS查询结果（可以是日以来的信息），点击导入村名将该乡的村名导入，统计时段设为、，按回车稍等即可得到该乡的九月份出生、节育情况报表，其它月份报表通过修改统计时段后按回车得到（下图为生成的九月份报表）。
4、计算方法介绍。报表中所用数据都是通过SUMPRODUCT函数计算得来的。15行各单元格中的公式写好后，15行以后各行的公式通过EXCEL的自动填充功能完成。
如男孩出生总数的计算，以B15列为例的计算公式为“=SUMPRODUCT((出生情况查询!$F$3:$F$10000="男")*($L$2<=出生情况查询!$G$3:$G$10000)*(出生情况查询!$G$3:$G$10000<=$M$2)*(出生情况查询!$A$3:$A$10000=$A15))”。
该公式的作用是在出生情况查询表中统计各村在统计时段内出生的男孩数。由于我县各个乡镇每年的出生数只有几百人，所以我们从第三行到第一万行统计完全可以满足要求。“出生情况 查 询!$F$3:$F$10000="男"”在每一行中判断该出生是否男孩。“$L$2<=出生情况查询!$G$3:$G$10000”和“出生情况查 询!$G$3:$G$10000<=$M$2” 在每一行中判断该出生是否在统计时段内，L2、M2单元格内是输入的统计起止时间，$表示绝对引用，在填充生成的公式中L2、M2不会变化。出生情况查询!$A$3:$A$10000=$A15)用来在每一行中判断该出生是否属于“贾庄居委”即所在的村，A15单元格的值为“贾庄居委”，由于自动填充生成A15以下单元格公式时列号不需要变化，在A前加了一个$符号使用绝对引用。
由于宫内节育器种类较多，公式中使用了多个SUMPRODUCT函数相加来完成放置宫内节育器手术数的计算，每一类宫内节育器使用一个SUMPRODUCT函数来计算。如S15内的公式为“=SUMPRODUCT((手术查询!$K$3:$K$10000<=报表!$M$2)*(报表!$L$2<=手术查询!$K$3:$K$10000)*(手术查询!$A$3:$A$10000=$A15)*(手术查询!$J$3:$J$10000="带铜宫内节育器"))+SUMPRODUCT((手术查询!$K$3:$K$10000<=报表!$M$2)*(报表!$L$2<=手术查询!$K$3:$K$10000)*(手术查询!$A$3:$A$10000=$A15)*(手术查询!$J$3:$J$10000="宫内节育器"))+SUMPRODUCT((手术查询!$K$3:$K$10000<=报表!$M$2)*(报表!$L$2<=手术查询!$K$3:$K$10000)*(手术查询!$A$3:$A$10000=$A15)*(手术查询!$J$3:$J$10000="带药带铜宫内节育器"))+SUMPRODUCT((手术查询!$K$3:$K$10000<=报表!$M$2)*(报表!$L$2<=手术查询!$K$3:$K$10000)*(手术查询!$A$3:$A$10000=$A15)*(手术查询!$J$3:$J$10000="其他带铜宫内节育器"))”。
报表工作表“导入村名“按钮的作用是通过名为“按钮1_单击”的宏所示来完成的，该宏中的程序为（下图为在excel中编辑VBA程序的Visual Basic 编辑器窗口）：
Sub 按钮1_单击()
Dim Cel As Range, Res
Dim q As String
Set d = CreateObject("Scripting.Dictionary")
Worksheets("报表").Activate
Range("a15:a50").ClearContents
Worksheets("出生情况查询").Activate
p = [a65536].End(xlUp).Row
For Each Cel In Range("a3:a" & p)
If Not d.exists(Cel.Value) Then
d.Add Cel.Value, Cel.Value
Res = d.Items
Worksheets("报表").Activate
For i = 0 To d.Count - 1
Cells(i + 15, 1) = Res(i)
这段VBA程序前半部分的作用是在出生查询工作表A列中将重复出现的村名筛选出来不重复地放入一个字典对象内，程序后半部分的作用是将字典对象内不重复的村名放入报表工作表A列中从A15单元格起以下各单元格中。
Excel办公软件功能强大，这两个实例只是其强大功能的一点应用，用好这个软件可以起到事半功倍的作用，给我们的工作带来更多便利。
版权所有：南阳市人口和计划生育委员会
电话：1 地址：南阳市两相西路1099号&
邮政编码：473000
技术支持：访问本页面，您的浏览器需要支持JavaScript