论文部分内容阅读
［摘 要］ 本文在完全Excel环境下，利用代码置换原理，通过信息代码为人员信息库自动赋值，再根据统计的需要将人员信息转换成不同类别、不同层次的统计码，最后通过Excel函数以数据连接的方式对各类、各层次统计码进行自动统计，从而实现对干部年报、工资年报等复杂报表的自动填报以及数据源和报表之间动态数据的同步更新。该设计既满足了年度报表工作的需要，也为日常的人力资源信息管理提供了一种行之有效的方法。
［关键词］ 干部年报；统计自动化；Excel；实现
［中图分类号］F270.7［文献标识码］A［文章编号］1673-0194（2008）09-0094-04
人事报表是为管理决策提供支持的有效的数据集合，报表工作在人力资源管理中占有重要位置。随着人力资源管理信息化进程的逐步加快，人事报表需求日渐增多，统计工作量也越来越大，而目前应用的报表控件又存在价格昂贵、刚性强、灵活性差等问题。为此已有人将目光投向了Microsoft Office系统的Excel平台上，或将Excel作为Visual Studio.Net 2003/2005的动态数据输出平台设计报表模板；或在Excel背景下利用VBA编程语言实现账务与报表等信息的自动生成等。上述方法均需要借助于编程语言来实现，对一般的人事工作者存在难度，笔者尝试完全在Excel环境下，不借助任何编程语言，巧妙利用Excel函数、自定义公式来解决干部年报等复杂报表的自动统计问题，取得了较好的效果。
1 设计思路
以干部年报为例，Excel环境下干部年报统计自动化流程设计思路如图1所示。上述流程都在一个Excel工作簿中进行，包括信息代码表、干部年报和人员信息库3个Excel工作表。具体运行流程是：通过信息代码为人员信息库自动赋值，再根据统计的需要将人员信息转换成不同类别、不同层次的统计码，最后在干部年报工作表中通过Excel函数以数据连接的方式对各类、各层次统计码进行自动统计，从而实现对干部年报的自动填报及其与人员信息库之间动态数据的同步更新。
2 设计过程
2. 1统计指标收集
在统计指标的收集上，本着实用和最小冗余的原则。干部年报表式复杂，对人员信息在类别和层次上划分详细，所需信息量多。但在Excel平台上，部分信息完全可凭借Excel强大、灵活的数据处理功能间接获得，从而省去了对这部分信息的收集环节，减轻工作量。通过对干部年报所需信息的分析，将其分为两类：一类是固有信息，主要有姓名、身份证号、性别、民族、政治面貌、学历、专业资格、任职岗位、薪资标准和党政职务等；另一类是派生信息，包括出生日期、年龄、身份和兼职4项指标，称其为派生信息，是因为这部分信息可通过Excel函数、预设的代码等从固有信息中派生出来，不必手工录入。
2. 2指标代码设定（见表1）
设定指标代码的目的是通过代码向人员信息库对应的指标赋值，将指标数据自动转换为代码，为下一步的统计码设定做准备。可设定代码的指标应具有固定的备选值，且备选值数量较多，这样才能充分发挥代码的作用。表1中，学历和学位代码按照学历层次设定（列I），政治面貌代码按照党派类别设定（列L），年龄代码按照区间段设定（列O）。考虑到2006年新工资制的施行、事业单位聘用制改革，以及实际工作中兼职情况的存在，岗位代码在设置上较复杂一些：表中C列为身份代码（专业人员为1，管理人员为2，工勤人员为3）；D列为岗位级别代码，与新工资制相对应；E列为岗位层次代码，其中专业技术岗位从正高级到见习分为6个层次，管理岗位从正处级到办事员分为6个层次（层次的多少与单位的行政级别有关），工勤岗位层次则从高级技师到学徒分为8个层次；F列为专业类别代码，该类代码与单位所属行业、专业结构设置有关（根据本单位实际情况，表1中专业人员仅设置了科研人员和会计人员两类）。
2. 3人员信息库建立
人员信息库工作表的结构设计见表2，表中部分显示包含了干部年报统计所需的全部信息。
信息的输入方式有3种：
① 姓名、身份证号两项信息需手工录入，其中身份证号可通过设定单元格的条件格式，对输入的数据长度不符合要求的特殊显示，以提醒用户纠错；
② 性别、民族、政治面貌、学历、专业资格、任职岗位、薪资标准、党政职务等8项信息通过Excel的数据有效性功能创建下拉列表，列表中备选值与代码表中一致，数据录入时从备选值中提取，这样既保证了数据输入的准确性，又为其后的代码赋值提供了保证；
③ 出生日期、年龄代码等派生信息利用Excel函数通过表内和表间数据连接的方法自动获得，第② ③输入方式的计算公式举例如下：
E4(出生日期) = if(len($B4)=15,19*power(10,6) mid($B4,7,6),mid($B4,7,8))*0.0001
F4(年龄) = int((year(today()) mouth(today())*0.01 day(today())*0.0001)-$E4)
G4(年龄代码) = if($A4="",0,vlookup($A4,代码表!$N$3:$O$55,2))
P4(任职岗位代码) = if($A4="",0,vlookup($A4,代码表!$B$3:$F$68,5,false))
L4(身份代码) = VLOOKUP($P4,代码表!$B$3:$F$68,2,FALSE)
M4(兼职) = VLOOKUP($R4,代码表!$B$3:$F$68,2,FALSE)
2. 4干部年报表式设计和统计码设定
2. 4. 1干部年报表式设计
统计表的格式设计基本参照干部年报的表式，也可根据日常工作的需要对统计指标的类别和层次做更详细的划分，如学历和年龄，见表3（部分显示）。
表3中，列标题按人员类别即专业技术人员、管理人员和工勤人员划分，其中专业人员中又分为科研人员和会计人员两类。行标题按人员的性别、民族、政治面貌、学历、年龄等自然情况划分。表中列C和行3中的数字分别是列标题和行标题中个指标项的代码值，该代码是该表表式设计的关键，因为它是下步统计公式的数据源之一。其中，行3中政治面貌、学历代码等各指标项代码与代码表中对应的指标代码一致（表1中L列、I列）；列C中的代码与岗位设置代码中的专业代码一致（表1中F列）。
2. 4. 2统计码设定
统计码位于人员信息工作表中，与人员一一对应，分为3部分（见表4）：① 依据任职岗位设定的统计码（AA列至AE列），作为干部年报中管理人员情况统计的数据源；② 依据薪资标准设定的统计码（AF列至AJ列），作为干部年报中专业技术人员情况统计的数据源；③ 依据人员身份设定的统计码（AK列至AO列），作为人员按干部、工人分类统计时的数据源。
统计码的设定方法是：通过表内链接将相关信息的代码值组合为有序的可计算的数值，如表4中单元格AI4的值10103意为：职工某1的专业技术职务为研究员（1）、执行薪资标准为研究员4（1）、学历为硕士研究生（3）。具体设定公式如下（以第②类即专业技术人员统计码为例）：
AF4(性别) = if($A4="",0,$M4*1000 $S4*10 IF($C4="男",1,2))
AG4(民族) = if($A4="",0,$M4*1000 $S4*10 IF($D4="汉",1,2))
AH4(政治面貌) = $M4*1000 $S4*10 $I4
AI4(学历) = $M4*10000 $S4*100 $K4
AJ4(年龄) = $M4*10000 $S4*100 $G4
上述公式中，M为兼职代码，S为薪资标准代码，I为政治面貌代码，K为学历代码，G为年龄代码（见表2）。
2. 5干部年报数据的自动生成
年报（表3）数据的自动生成借助于表中行3和列C的代码、表4中的统计码以及表内、表间的链接公式来实现。例如要统计本单位专业技术职务为研究员、薪资标准为研究员4、学历为硕士研究生的人员数量，只需计算表4中AI列中值为“10103”的单元格数量即可。具体的统计公式如下（以表3中“科研人员”为例）：
按性别分组统计： E16 = Countif(人员信息库!$AF:$AF,1000 $C16*10 E$3)
按民族分组统计： G16 = Countif(人员信息库!$AG:$AG,1000 $C16*10 G$3)
按政治面貌分组统计： H16 = Countif(人员信息库!$AI:$AI,1000 $C16*10 H$3)
按学历分组统计： L16 = Countif(人员信息库!$AJ:$AJ,10000 $C16*100 L$3)
上述公式中，Countif(range,criteria)函数的条件值即criteria值的组合顺序正与人员信息库中统计码的组合顺序相对应。因对表3中行3和列C中代码的单元格引用均为混合引用，所以只需将不同分组的统计公式设置在科研人员该分组的左上角单元格（研究员），再通过“自动填充柄”分别向右、向下填充至科研人员在该分组的末尾单元格即可，即同类人员的各统计分组只需一个统一的链接公式（仅“小计”除外）。在信息代码表中，科研、管理和工勤人员的身份代码分别是1、2和3，所以要实现对管理人员和工勤人员的情况统计，只需将上述公式中的1 000和10 000分别置换为2 000、
20 000和3 000、30 000即可。
因对干部年报中人员信息的统计划分较详尽，且统计数据始终处于动态更新中，所以该方案不仅能够满足年度报表的需求，也能满足日常人事报表对统计数据的需求。
3 讨 论
本设计的核心是信息的代码置换，依照上述原理和方法，建立人员工资信息库并以其为数据源，通过对代码、报表表式和链接公式的合理设计，同样可实现对工资年报、离退休年报等报表的自动统计，如此可大大地节省相关报表的劳动量，提高工作效率，将人事工作者从繁杂的数据统计中解放出来。该设计的不足之处在于：统计结果仅停留在Excel平台上，只能做纸质输出，还不能实现基层单位与上级部门之间的数据交换，这有待在以后的管理实践中继续探索。
主要参考文献
［1］ 朱顺泉. 基于Excel 2003环境下的账务与报表会计信息系统的设计方案［J］. 中国管理信息化，2007，（4）：3-5.
［2］ 杨建宏，王晓东，张琴琴. 基于Excel通用报表系统的设计与实现［J］. 电脑知识与技术，2007，（1）.
［3］ 庞敏. 用Excel生成报表的通用组件模型［J］. 交通与计算机，2005，（6）：112-114.
［4］ 周健，孟昭峰. 利用Excel进行自动分析计算的方法［J］. 中国管理信息化，2007，（4）：43-45.
［关键词］ 干部年报；统计自动化；Excel；实现
［中图分类号］F270.7［文献标识码］A［文章编号］1673-0194（2008）09-0094-04
人事报表是为管理决策提供支持的有效的数据集合，报表工作在人力资源管理中占有重要位置。随着人力资源管理信息化进程的逐步加快，人事报表需求日渐增多，统计工作量也越来越大，而目前应用的报表控件又存在价格昂贵、刚性强、灵活性差等问题。为此已有人将目光投向了Microsoft Office系统的Excel平台上，或将Excel作为Visual Studio.Net 2003/2005的动态数据输出平台设计报表模板；或在Excel背景下利用VBA编程语言实现账务与报表等信息的自动生成等。上述方法均需要借助于编程语言来实现，对一般的人事工作者存在难度，笔者尝试完全在Excel环境下，不借助任何编程语言，巧妙利用Excel函数、自定义公式来解决干部年报等复杂报表的自动统计问题，取得了较好的效果。
1 设计思路
以干部年报为例，Excel环境下干部年报统计自动化流程设计思路如图1所示。上述流程都在一个Excel工作簿中进行，包括信息代码表、干部年报和人员信息库3个Excel工作表。具体运行流程是：通过信息代码为人员信息库自动赋值，再根据统计的需要将人员信息转换成不同类别、不同层次的统计码，最后在干部年报工作表中通过Excel函数以数据连接的方式对各类、各层次统计码进行自动统计，从而实现对干部年报的自动填报及其与人员信息库之间动态数据的同步更新。
2 设计过程
2. 1统计指标收集
在统计指标的收集上，本着实用和最小冗余的原则。干部年报表式复杂，对人员信息在类别和层次上划分详细，所需信息量多。但在Excel平台上，部分信息完全可凭借Excel强大、灵活的数据处理功能间接获得，从而省去了对这部分信息的收集环节，减轻工作量。通过对干部年报所需信息的分析，将其分为两类：一类是固有信息，主要有姓名、身份证号、性别、民族、政治面貌、学历、专业资格、任职岗位、薪资标准和党政职务等；另一类是派生信息，包括出生日期、年龄、身份和兼职4项指标，称其为派生信息，是因为这部分信息可通过Excel函数、预设的代码等从固有信息中派生出来，不必手工录入。
2. 2指标代码设定（见表1）
设定指标代码的目的是通过代码向人员信息库对应的指标赋值，将指标数据自动转换为代码，为下一步的统计码设定做准备。可设定代码的指标应具有固定的备选值，且备选值数量较多，这样才能充分发挥代码的作用。表1中，学历和学位代码按照学历层次设定（列I），政治面貌代码按照党派类别设定（列L），年龄代码按照区间段设定（列O）。考虑到2006年新工资制的施行、事业单位聘用制改革，以及实际工作中兼职情况的存在，岗位代码在设置上较复杂一些：表中C列为身份代码（专业人员为1，管理人员为2，工勤人员为3）；D列为岗位级别代码，与新工资制相对应；E列为岗位层次代码，其中专业技术岗位从正高级到见习分为6个层次，管理岗位从正处级到办事员分为6个层次（层次的多少与单位的行政级别有关），工勤岗位层次则从高级技师到学徒分为8个层次；F列为专业类别代码，该类代码与单位所属行业、专业结构设置有关（根据本单位实际情况，表1中专业人员仅设置了科研人员和会计人员两类）。
2. 3人员信息库建立
人员信息库工作表的结构设计见表2，表中部分显示包含了干部年报统计所需的全部信息。
信息的输入方式有3种：
① 姓名、身份证号两项信息需手工录入，其中身份证号可通过设定单元格的条件格式，对输入的数据长度不符合要求的特殊显示，以提醒用户纠错；
② 性别、民族、政治面貌、学历、专业资格、任职岗位、薪资标准、党政职务等8项信息通过Excel的数据有效性功能创建下拉列表，列表中备选值与代码表中一致，数据录入时从备选值中提取，这样既保证了数据输入的准确性，又为其后的代码赋值提供了保证；
③ 出生日期、年龄代码等派生信息利用Excel函数通过表内和表间数据连接的方法自动获得，第② ③输入方式的计算公式举例如下：
E4(出生日期) = if(len($B4)=15,19*power(10,6) mid($B4,7,6),mid($B4,7,8))*0.0001
F4(年龄) = int((year(today()) mouth(today())*0.01 day(today())*0.0001)-$E4)
G4(年龄代码) = if($A4="",0,vlookup($A4,代码表!$N$3:$O$55,2))
P4(任职岗位代码) = if($A4="",0,vlookup($A4,代码表!$B$3:$F$68,5,false))
L4(身份代码) = VLOOKUP($P4,代码表!$B$3:$F$68,2,FALSE)
M4(兼职) = VLOOKUP($R4,代码表!$B$3:$F$68,2,FALSE)
2. 4干部年报表式设计和统计码设定
2. 4. 1干部年报表式设计
统计表的格式设计基本参照干部年报的表式，也可根据日常工作的需要对统计指标的类别和层次做更详细的划分，如学历和年龄，见表3（部分显示）。
表3中，列标题按人员类别即专业技术人员、管理人员和工勤人员划分，其中专业人员中又分为科研人员和会计人员两类。行标题按人员的性别、民族、政治面貌、学历、年龄等自然情况划分。表中列C和行3中的数字分别是列标题和行标题中个指标项的代码值，该代码是该表表式设计的关键，因为它是下步统计公式的数据源之一。其中，行3中政治面貌、学历代码等各指标项代码与代码表中对应的指标代码一致（表1中L列、I列）；列C中的代码与岗位设置代码中的专业代码一致（表1中F列）。
2. 4. 2统计码设定
统计码位于人员信息工作表中，与人员一一对应，分为3部分（见表4）：① 依据任职岗位设定的统计码（AA列至AE列），作为干部年报中管理人员情况统计的数据源；② 依据薪资标准设定的统计码（AF列至AJ列），作为干部年报中专业技术人员情况统计的数据源；③ 依据人员身份设定的统计码（AK列至AO列），作为人员按干部、工人分类统计时的数据源。
统计码的设定方法是：通过表内链接将相关信息的代码值组合为有序的可计算的数值，如表4中单元格AI4的值10103意为：职工某1的专业技术职务为研究员（1）、执行薪资标准为研究员4（1）、学历为硕士研究生（3）。具体设定公式如下（以第②类即专业技术人员统计码为例）：
AF4(性别) = if($A4="",0,$M4*1000 $S4*10 IF($C4="男",1,2))
AG4(民族) = if($A4="",0,$M4*1000 $S4*10 IF($D4="汉",1,2))
AH4(政治面貌) = $M4*1000 $S4*10 $I4
AI4(学历) = $M4*10000 $S4*100 $K4
AJ4(年龄) = $M4*10000 $S4*100 $G4
上述公式中，M为兼职代码，S为薪资标准代码，I为政治面貌代码，K为学历代码，G为年龄代码（见表2）。
2. 5干部年报数据的自动生成
年报（表3）数据的自动生成借助于表中行3和列C的代码、表4中的统计码以及表内、表间的链接公式来实现。例如要统计本单位专业技术职务为研究员、薪资标准为研究员4、学历为硕士研究生的人员数量，只需计算表4中AI列中值为“10103”的单元格数量即可。具体的统计公式如下（以表3中“科研人员”为例）：
按性别分组统计： E16 = Countif(人员信息库!$AF:$AF,1000 $C16*10 E$3)
按民族分组统计： G16 = Countif(人员信息库!$AG:$AG,1000 $C16*10 G$3)
按政治面貌分组统计： H16 = Countif(人员信息库!$AI:$AI,1000 $C16*10 H$3)
按学历分组统计： L16 = Countif(人员信息库!$AJ:$AJ,10000 $C16*100 L$3)
上述公式中，Countif(range,criteria)函数的条件值即criteria值的组合顺序正与人员信息库中统计码的组合顺序相对应。因对表3中行3和列C中代码的单元格引用均为混合引用，所以只需将不同分组的统计公式设置在科研人员该分组的左上角单元格（研究员），再通过“自动填充柄”分别向右、向下填充至科研人员在该分组的末尾单元格即可，即同类人员的各统计分组只需一个统一的链接公式（仅“小计”除外）。在信息代码表中，科研、管理和工勤人员的身份代码分别是1、2和3，所以要实现对管理人员和工勤人员的情况统计，只需将上述公式中的1 000和10 000分别置换为2 000、
20 000和3 000、30 000即可。
因对干部年报中人员信息的统计划分较详尽，且统计数据始终处于动态更新中，所以该方案不仅能够满足年度报表的需求，也能满足日常人事报表对统计数据的需求。
3 讨 论
本设计的核心是信息的代码置换，依照上述原理和方法，建立人员工资信息库并以其为数据源，通过对代码、报表表式和链接公式的合理设计，同样可实现对工资年报、离退休年报等报表的自动统计，如此可大大地节省相关报表的劳动量，提高工作效率，将人事工作者从繁杂的数据统计中解放出来。该设计的不足之处在于：统计结果仅停留在Excel平台上，只能做纸质输出，还不能实现基层单位与上级部门之间的数据交换，这有待在以后的管理实践中继续探索。
主要参考文献
［1］ 朱顺泉. 基于Excel 2003环境下的账务与报表会计信息系统的设计方案［J］. 中国管理信息化，2007，（4）：3-5.
［2］ 杨建宏，王晓东，张琴琴. 基于Excel通用报表系统的设计与实现［J］. 电脑知识与技术，2007，（1）.
［3］ 庞敏. 用Excel生成报表的通用组件模型［J］. 交通与计算机，2005，（6）：112-114.
［4］ 周健，孟昭峰. 利用Excel进行自动分析计算的方法［J］. 中国管理信息化，2007，（4）：43-45.