Excel在创建职工信息统计表中的应用

[摘要] Microsoft Office中的Excel 是具有强大的数据分析和管理功能的电子表格软件，是办公室工作中很常用也很重要的一款软件。运用Excel函数功能并配合使用一些编辑技巧可以高效、准确地完成数据统计和管理工作。本文介绍了用Excel创建职工信息统计表，并根据职工不同职称、学历、年龄分别进行统计的方法和步骤。

[关键词] Excel；函数；统计

[中图分类号] F232 [文献标识码] A [文章编号] 1673 - 0194（2013）05- 0011- 02

各企事业及机关单位经常需要对职工信息进行统计和管理，比如说统计职工人数、性别、年龄、学历、职称分布的情况等数据信息。Microsoft Office中的Excel 是具有强大的数据分析和管理功能的电子表格软件，是办公室工作中很常用也很重要的一款软件。运用Excel函数功能并配合使用一些编辑技巧对数据信息进行统计分析，方便快捷，省时省力。现以Excel 2007为例，介绍创建职工信息统计表的方法和步骤。

1 创建职工信息数据表

（1）启动Excel 2007，创建新的工作簿。

（2）首先合并A1至G1单元格，输入“职工信息统计表”，然后输入职工基本信息：姓名、身份证号码、出生日期、性别、年龄、学历、职称。

1）多于15位数字的输入[1]。身份证号码多为15位或18位数字，而在单元格中输入15位以上数字后单元格显示方式会自动变成科学计数，且15位数后全变成零了，因此在输入18位身份证号码前，选定单元格B3，按右键弹出快捷菜单，单击“设置单元格格式数字文本”，将单元格格式设置为文本；或者在B3单元格输入身份证号码前输入一个单引号（英文状态下）“ ' ”，都可以输入18位的身份证号码。

2）从身份证号码提取出生日期、性别、年龄的信息[2]。从身份证号码中提取出生日期。从B3单元格提取“职工1”的出生日期并在C3单元格录入，操作步骤：选中单元格C3，输入函数公式：“=IF（LEN（B3）=15，19&MID（B3，7，2）&"年"，MID（B3，7，4）&"年"）&IF（LEN（B3）=15，MID（B3，9，2）&"月"，MID（B3，11，2）&"月"）&IF（LEN（B3）=15，MID（B3，11，2）&"日"，MID（B3，13，2）&"日"）” 并回车，立刻显示出生日期为：1978年04月24日。如果只需要出生年月，可以用left函数公式“=left（C3，8）”提取出生日期单元格C3“1978年04月24日”左起8个字符，即“1978年04月”。

从身份证号码中提取性别。从B3单元格提取“职工1”的性别并在D3单元格录入，操作步骤：选中D3单元格，输入函数公式：“=IF（MOD（（IF（LEN（B3）=15，RIGHT（B3），MID（B3，17，1））），2）=0，"女"，"男"）” 并回车，立刻显示职工1性别为“男”。

从出生日期中提取年龄。从B3单元格提取“职工1”的年龄并在E3单元格录入，操作步骤：在E3单元格中输入函数公式（1）：“=CONCATENATE（DATEDIF（C3，"2011-08-31"，"y"））”并回车，计算职工1至2011年8月31日的年龄；函数公式（2）“=CONCATENATE（DATEDIF（C3，TODAY（），"y"））” 并回车，计算职工1截止至当天的年龄。

3）使用智能填充技巧将其他职工的出生日期、性别、年龄录入。选中连续的单元格（C3：E3），将光标移到单元格右下角，当光标变成小黑十字时（即填充柄），按着鼠标左键向下拖动至E8单元格，将单元格（C3：E3）单元格中的公式连续复制到同列中下列连续的单元格中，即可录入其他职工的出生日期、性别、年龄。

4）由于出生日期、性别、年龄3列数据都是用公式计算的，计算出来的数据不能直接复制或者移动，否则就会显示公式出错，所以我们要将公式算出来的数据转换为数值。操作步骤：首先选定C3至E3单元格，按右键弹出快捷菜单选择“复制”，单击开始菜单选择“粘贴粘贴值确定”。E列是年龄，即使简单地把单元格格式改成“数值”也无济于事，这些文本型的数字不能作各种排序、求和等函数运算，这时我们需要将它们转换成数值格式。操作方法：选中需要转换单元格（E3：E8），单元格旁边会出现一个智能标记，单击智能标记，在随后弹出的下拉列表中选中“转换为数字”选项，即可快速完成转换。

2 统计职工信息

2.1 计算职工总数及其中女性人数

用COUNTA函数统计全部职工人数，COUNTA函数功能是返回单元格区域中非空值的单元格个数。操作步骤：选定单元格B11，输入公式“=COUNTA（A3：A8）”，然后按Enter键确认，计算出职工总人数。用Countif函数[3]可以统计女性职工人数情况，COUNTIF函数功能是统计某个单元格区域符合指定条件的单元格数目，操作步骤：选定单元格B12，输入公式“=COUNTIF（D3：D8，"女"）”，按Enter键确认，计算出职工中女性人数。

2.2 统计职称、学历分布

用COUNTIF函数对职工中不同职称的职工分别进行统计。计算职称为正高级的职工人数：选定单元格B13，输入公式“=COUNTIF（G3：G8，"正高级"）”，按Enter键确认；计算职称为副高级的职工人数：选定单元格B14，输入公式“=COUNTIF（G3：G8，"副高级"）”按Enter键确认；计算出职称为中级的职工人数，在单元格B15输入公式“=COUNTIF（G3：G8，"中级"）”；计算职称为初级的职工人数，在单元格B16输入公式“=COUNTIF（G3：G8，"初级"）”；计算无职称的职工人数，在单元格B17输入公式“=COUNTIF（G3：G8，"无职称"）”。

同样用COUNTIF函数可以完成职工学历统计。操作步骤：分别选定单元格C11、D11、E11、F11、G11，依次输入函数公式“=COUNTIF（F3：F8，"博士"）”；“=COUNTIF（F3：F8，"硕士"）”；“=COUNTIF（F3：F8，"本科"）”；“=COUNTIF（F3：F8，"专科"）”；“=COUNTIF（F3：F8，"高中阶段及以下"）”，然后按Enter键确认，即可计算出学历为博士、硕士、本科、专科及高中阶段及以下的职工人数。

2.3 双重条件计数

COUNTIFS函数是Excel 2007新增函数，用于计算某个区域中满足多重条件的单元格数目。在C12至G12单元格需要计算的职工人数有双重条件，条件1性别为女性，条件2学历分别为博士、硕士、本科、专科或高中阶段及以下，用COUNTIFS可以满足。选定单元格C12，输入函数公式：“=COUNTIFS（D3：D8，"女"，F3：F8，"博士"）”按Enter键确认，计算女性博士生的职工人数；选定D12单元格，输入函数公式“=COUNTIFS（D3：D8，"女"，F3：F8，"硕士"）”按Enter键确认，计算女性硕士生的职工人数；余下E12至G12单元格以此类推即可。同样用COUNTIFS函数可以计算条件1：职称为正高级、副高级、中级、初级或者无职称；条件2：学历分别为博士、硕士、本科、专科、高中阶段及以下的职工数 。

2.4 统计年龄分布

用Excel怎样统计出职工各年龄段内的人数分布呢？大多数人使用COUNTIF或COUNTIFS函数，公式要在B20：B27单元格内统计显示E3：E8内30岁及以下、31～35岁、36～40岁、41～49岁、50～55岁、56～60岁、61岁以上各年龄段内人数分布情况，要输入7条公式，十分麻烦。其实，Excel已经为我们提供了一个进行频度分析的frequency数组函数[3]，frequency 函数是以一列垂直数组返回某个区域中数据的频率分布，用一条数组公式就能轻松地统计出给定的年龄范围内不同年龄段各分数段的人数分布，C20：C26为各年龄段的分段点。操作步骤如下：选中需要显示各年龄段人数的单元格区域B20：B27，在编辑栏内输入函数“=FREQUENCY（E3：E8，C20：C26）”， 然后同时按Ctrl+Shift+Enter组合键产生数组公式“{=FREQUENCY（E3：E8，C20：C26）}”，这里要注意“{ }”不能手工键入，必须按下“Crtl+Shift+Enter”组合键由系统自动产生，即可统计出各年龄段的人数。

使用办公软件准确高效地处理各类数据，不仅可以减轻劳动强度和提高工作效率，而且可以激发工作积极性与热情，使现代科技渗透到日常管理之中。

主要参考文献

[1]肖文雅，王燕，王涵.Excel 2003中的填充技巧和特殊数据输入方法[J].中国医学教育技术，2010，24（4）.

[2]钱秀峰.Excel中函数嵌套功能的实际应用[J].人力资源管理，2010（4）.

[3]赵磊.Excel中进行数据分析的几个常用函数[J].中小企业管理与科技，2010（18）.