下载掌阅APP，畅读海量书库
立即打开
扫一扫，看视频
人力资源部门不仅要计算员工工资，还要对工资数据进行统计、对比和分析，以便从工资这一环节中找出需要改进的管理措施。对于工资数据，一般从部门、岗位、员工个人等角度进行分析。本节将根据“2021年1月员工工资表”制作数据透视表，并对其进行各种统计和分析。如下图所示，是将“总监”岗位从各部门筛选出来，以对比分析相同岗位在不同部门的工资数据的结果（结果文件参见：结果文件\第3章\2021年1月员工工资表.xlsx）。
制作员工工资数据透视表，首先需要整理和规范原始数据，然后根据数据量选择是否在新的工作表中创建数据透视表。创建空白数据透视表后，首先应对其做初步的布局，如添加字段至数据、列等区域，设置报表显示形式，调整数字格式及对齐方式等，使之形成一个基本表格框架后，即可进行数据分析。对于工资数据，主要可以从部门和岗位的维度进行动态分析。本案例的具体制作思路如下图所示。
在数据透视表中，可以将每个字段在筛选、行、列、值区域间任意拖动，从不同的角度动态分析数据。下面运用数据透视表分析2021年1月员工工资数据。
数据透视表的实质其实是一个动态数据库。因此，首先要确保原始数据的规范性，然后才能根据原始数据顺利地创建数据透视表。
步骤01 规范整理原始数据表。打开“素材文件\第3章\2021年1月员工工资表.xlsx”文件，检查“2020年1月工资”工作表是否符合规范，主要从表格结构和原始数据格式两个方面着手。
（1）表格结构：删除多行表头、空白行或列或单元格；取消合并单元格；取消单元格中的换行符等。
（2）原始数据：将每个字段的数据统一设置格式。例如，将文本型数字全部转换为“数值”格式；日期型数据统一设置为“日期”格式等。
规范后的原始数据表如下图所示。
步骤02 创建数据透视表。 ➊ 选中“2021年1月工资”工作表中数据区域内的任意单元格，单击“插入”选项卡中“表格”组的“数据透视表”按钮； ➋ 弹出“创建数据透视表”对话框，Excel自动识别源数据区域，默认数据透视表的放置位置为“新工作表”，这一步可不做任何改动，直接单击“确定”按钮，如下图所示。
步骤03 查看数据透视表。完成数据透视表的创建操作后，Excel自动新增工作表，并在其中建立一个空白的数据透视表。单击透视表区域中的任一单元格后即可激活“数据透视表字段”任务窗格（右侧），主要由“字段列表”和4个区域构成。其中，“字段列表”列出原始数据表中的全部字段；4个区域是“筛选”“列”“行”“值”，分析数据时，将需要分析的字段在其中来回拖动即可。初始效果如下图所示。
创建数据透视表后，接下来对其进行初始布局，将需要分析的字段分别添加到“筛选”“行”“列”“值”4个区域中，再对数据透视表的样式和结构进行设计。
步骤01 添加字段至各区域。在“字段列表”中勾选除“员工编号”和“岗位”之外的全部字段，Excel将根据字段类型自动识别其区域类型，如下图所示。
添加字段后，数据透视表的效果如下图所示。
步骤02 以表格形式显示报表。初始数据透视表是以大纲形式显示报表的，可将其设置为表格形式，其效果更利于查看数据。 ➊ 单击数据透视表区域中的任一单元格，激活功能区中的“数据透视表工具”，选择“设计”选项卡； ➋ 单击“布局”组的“报表布局”下拉按钮； ➌ 选择下拉列表中的“以表格形式显示”命令，如下图所示。
步骤03 调整值字段名称的对齐方式、数字格式和透视表的行高和列宽。由于每个值字段名称的前面自动添加了汇总依据的文字，导致列的宽度过宽，可将其调整为“自动换行”对齐方式后调整行高和列宽。同时可以将值字段下的数字格式设置为规范的“数值”格式。 ➊ 选中C:N区域，打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中选择“数值”选项，“小数位数”和“负数”为默认格式，不做修改； ➋ 切换至“对齐”选项卡，勾选“文本控制”选项组的“自动换行”复选框； ➌ 单击“确定”按钮，关闭对话框，如下图所示。
操作完成后，调整第3行（值字段名称）的行高和C:N列的列宽即可，如下图所示。
小提示
数据透视表中值字段名称前面的文字“以下项目的总和：”是系统自动添加的汇总依据，将随着汇总依据的改变而改变。
例如，删除以上文字，将汇总依据设置为“平均值”，值字段名称即变化为“以下项目的平均值：+字段名称”，再次将汇总依据设置为“求和”，值字段名称中将再次自动添加上述文字。因此无法彻底删除，只能通过调整文字对齐方式的办法来缩小列宽。
数据透视表的最大优势是动态分析数据，而且操作简单至极，只需通过将字段在区域间来回拖动即可同步改变布局，并从不同角度对数据进行分析。同时，能够以多种方式快速汇总所有数据，如求和、计数、平均值、最大值、最小值等。下面对“2021年1月员工工资”数据透视表进行动态分析。
部门平均工资是部门工资总额除以部门人数的平均数，体现一定时期内员工工资的收入程度，是反映员工工资水平的主要指标。运用数据透视表即可迅速计算出每个部门的平均工资数据及所有部门的总平均工资数据。
步骤01
折叠部门下的明细数据。
➊
依次单击“部门”字段下每个部门名称前面的折叠按钮
，将部门下的明细数据（员工姓名）折叠起来；
➋
选中C4:N4单元格区域，右击弹出快捷菜单，选择“值汇总依据”命令；
➌
在弹出的二级快捷菜单中选择“平均值”命令，如下图所示。
步骤02 查看汇总结果。操作完成后，可看到每个值字段中的数字全部变为平均数，如下图所示。
小技巧
如果只需改变部分值字段的汇总依据，如计算“基本工资”的平均值，则只需选中C4:C10单元格区域中的任一单元格后设置汇总依据即可。
部门工资比例是指每个部门的某个工资项目总额占全公司该工资项目总额的百分比。例如，行政部的基本工资占基本工资总额的11.51%。计算部门工资比例可用于后续分析各个工资项目的部门组成结构。
在数据透视表中，计算数据的比例只需一步——设置“值显示方式”。下面计算部门基本工资比例。
步骤01 设置值显示方式。 ➊ 选中C3:N9单元格区域，单击将值汇总依据设置为“求和”； ➋ 选中C4:C10单元格区域中的任一单元格，右击弹出快捷菜单，选择“值显示方式”命令； ➌ 在弹出的二级快捷菜单中选择“列汇总的百分比”命令，如下图所示。
步骤02 查看显示结果。操作完成后，可看到C4:C10单元格区域中的所有数字已变为百分比形式，如下图所示。
为了方便查看和分析指定部门中的岗位结构及每个岗位的工资数据，可将部门名称设置为筛选条件，同时向“列”区域添加岗位字段，以便进行数据筛选和分析。
在数据透视表中，添加筛选条件的操作也十分简单，只需拖动字段名称至“筛选”区域即可。下面分析“销售部”的工资数据。
步骤01 调整数据透视表字段区域。 ➊ 单击“列”区域中的“部门”字段，将其拖动至“筛选”区域； ➋ 将“字段列表”中的“岗位”字段拖动至“行”区域。此时可看到数据透视表区域中同步呈现效果，如下图所示。
步骤02
筛选“销售部”工资数据。
➊
单击B2单元格右侧的“筛选”按钮
；
➋
在展开的下拉列表中选择“销售部”选项；
➌
单击“确定”按钮，如下图所示。
小提示
如果需要同时筛选两个或以上的项目，则勾选下拉列表中的“选择多项”复选框即可。
操作完成后，可看到数据透视表区域中仅列出了销售部的岗位和员工的名称及工资数据，如下图所示。
步骤03 统计“销售部”最高或最低应付工资数据。 ➊ 右击I5:I16单元格区域中的任一单元格，弹出快捷菜单，选择“值汇总依据”命令； ➋ 选择二级快捷菜单中的“最大值”命令（如果统计最低工资，则选择“最小值”命令），如下图所示。
操作完成后，可看到I17单元格（应付工资总计）中显示的是I5:I16单元格区域中的最大数字10130.68，如下图所示。
步骤04 计算“业务代表”的应付工资差异。这里计算其他业务代表与“曹颖萱”的应付工资差异。 ➊ 选中I8单元格，右击弹出快捷菜单，选择“值显示方式”命令； ➋ 选择二级快捷菜单中的“差异”命令； ➌ 弹出“值显示方式（以下项目的总和：应付工资）”对话框，其中“基本字段”默认为“员工姓名”，“基本项”显示的姓名是与第 ➊ 步中所选I8单元格中同一行中“员工姓名”字段下的B8单元格中的数据，因此这里无须更改，直接单击“确定”按钮，如下图所示。
操作完成后，可看到销售部I8:I16单元格区域中显示的是与“曹颖萱”应付工资的差额。而I5:I7单元格区域显示“#N/A”是由于其他岗位下面的员工均只有一名，因此无法计算同一岗位的工资差异，如下图所示。
根据岗位筛选出部门工资数据，可快速分析和对比不同部门中同一岗位级别的工资。下面分析和对比“总监”的工资数据。
步骤01 调整数据透视表字段区域。 ➊ 将“岗位”字段从“行”区域拖动至“筛选”区域； ➋ 将“部门”字段拖动至“行”区域，如下图所示。
步骤02
筛选“总监”的工资数据。
➊
单击B2单元格右侧的“筛选”按钮
；
➋
展开下拉列表后，勾选“选择多项”复选框；
➌
在文本框中输入“总监”；
➍
单击“确定”按钮。此时可看到列表中已自动勾选了每个部门的“**总监”复选框，如下图所示。
操作完成后，可看到数据透视表区域中仅列出了各部门“总监”岗位的工资数据，如下图所示。
