用Excel做人事统计.docVIP

用Excel做人事统计 不管是高校、企业还是政府机关，每年完成单位各种各样的人事情况统计、制作人员名册的工作都是必不可少的。SQL Server、Access等数据库软件入门门槛较高，不适合普通工作人员使用。笔者在不断摸索中发现了用Excel数组公式进行人员名册管理、统计的巧妙方法，现介绍如下。 本文将以制作高校教师年底统计名册、报表的实例来向大家介绍具体的操作。我们还要求当增加或删除员工记录后，报表能自动更新。 一、准备数据 1．将现有的教师名册从数据库中导出，存为Excel表格，根据名册要求调整格式，制作成人员名册； 2．在菜单中选择“插入→名称→定义”，分别定义各个列的含义，这样我们才可以在公式中直接引用该列的集合。例如：“单位”引用位置是“=名单!$A$2:$A$344”表示在名单这个表单内A列第2行到344行的这个范围内的单元格集合，我们称为单位。定义好我们需要的名称后保存工作表。 3．选择“插入→工作表”选项，插入一个新工作表，并更名为“学院情况统计”。在表格内建立表头和需要的统计栏目。所有准备工作到此结束，下面我们将为表格填充内容。 二、统计实战 1．首先进行各学院总人数的统计。在学院1的总人数栏中填入公式“=COUN TIF(单位,“学院1”)”（不含引号，下同）。COUNTIF能计算区域中满足给定条件的单元格的个数。标准格式为COUNTIF(Range,Criteria)，Range为满足条件的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以是数字、表达式、单元格引用或文本。COUNTIF (单位,“学院1”)含义为统计单位集合中内容为“学院1”的单元格总数。 2．在学院1的女栏目中输入“=SUM((单位=学院1)*(性别=女))”，输入完毕后，按下“Ctrl+Shift+Enter”键，生成数组公式{=SUM((单位=学院1)*(性别=女))}，计算结构也自动显示在单元格内。公式的含义为计算单位为“学院1”且性别为“女”的单元格总数。“*”代表“与”关系。 依此类推，在学院1后面单元格依次填入下列公式： 博士：=SUM((单位=学院1)*(学历=博士)) 硕士：=SUM((单位=学院1)*(学历=硕士)) 研究生比例：不属于统计内容，只要利用公式计算（博士+硕士）/总人数即可。 填写完学院1所有公式后，利用复制功能将学院1的所有公式复制到学院2的公式栏目。再利用替换功能，将学院2所有公式单元格的内容中“学院1”替换为“学院2”，则可以得到学院2的公式。可重复使用，填写完其他单元格，最后利用汇总功能完成合计栏。这样，一个统计报表就生成了。 3．数组公式与数组运算不同，数组公式通常以“Ctrl+Shift+Enter”结束，在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可产生多值或对一组值进行操作，也可以生成更复杂的公式。 如：求学院1中正高级职务人员中有博士学位且性别为女，年龄在35岁以下的人数，我们可以写为： =SUM((单位=“学院1”)*(学历=“博士”)*(性别=“女”)*(年龄 三、总结 利用Excel的数组公式制作名册和统计报表大大降低了统计难度，也方便了数据的共享。利用数据保护功能将公式单元格保护起来形成模板，分发给其他人，使用者只要懂得简单的Excel编辑知识就可以使用。今年的统计工作完成后，明年只要将新数据复制到名册内，就自动形成了新一年的统计名册。大大简化了年终统计带来的烦恼。