《Excel-2016数据处理与分析》教学课件09综合案例：员工薪酬福利数据的处理与分析

模块9综合案例：员工薪酬福利数据的处理与分析

对数据进行编辑处理，如数据列的添加或减少，数据的调用、计算、汇总等，为薪酬数据的整体分析和个体分析做好准备；使用静态图表和动态图表直观展示分析的薪酬福利情况如岗位对比、年度对比、行业对比等，为人力资源部门或公司领导层对薪酬福利的调整提供坚实的数据支撑。学习目标（1）加法和乘法公式。（2）排序。（3）分类汇总。（4）柱形图和饼图。（5）编辑数据点着色。（6）添加数据标签。（7）复制工作表。（8）重命名工作表标签名称。学习重点（9）SUMPRODUCT函数。（10）SUMIF函数。（11）AVERAGEIF函数。（12）推荐图表。（13）图表样式。（14）数据序列。（15）VLOOKUP函数。9.1

案例制作说明薪酬福利属于企业成本支出，也是吸引和留住人才的手段。因此，对年度薪酬福利数据的分析是人力资源部门和领导非常看重的工作，发现薪酬福利中不完善的地方并及时做出调整：薪酬支出多少、相对增长或是减少了多少，相对于行业高了多少或是低了多少，以及对来年薪酬福利金额的预算等，以保证企业正常、高效地运转。9.1.1

案例效果本模块年度薪酬福利数据处理与分析效果如图9-1所示。9.1.1案例效果续图9.1.2

制作流程在本案例中始终围绕着员工薪酬福利明细数据进行引用、计算、管理和分析操作。从一张薪酬数据明细表发展衍生到四张表。运用到的操作主要有3类：数据区域制作、公式计算和图表展示，数据管理只用到排序和分类汇总两个小知识点。本案例的制作流程如图9-2所示。9.1.2制作流程9.2

制作部门年度薪酬汇总表部门是公司的重要构成模块，要判定部门人力成本投入是否合理、是否利于发展，最直接的方式是对部门薪酬福利数据进行汇总展示。其中，汇总展示分为两大步：第一大步是分类汇总，第二大步是用图表展示。9.2.1

分类汇总部门年度薪酬数据对部门薪酬福利数据进行年度分析，最直接的方法是分类汇总，然后在2级汇总数据上插入图表进行展示分析。为了不破坏源数据，对源数据表格进行复制，也就是对“数据明细”表进行复制，具体操作如下。步骤1打开素材文件中的“素材\模块9\9.2\员工薪酬福利.xlsx”文件，按住Ctrl键拖动“数据明细”标签，复制“数据明细”表格，如图9-3所示。9.2.1分类汇总部门年度薪酬数据9.2.1分类汇总部门年度薪酬数据步骤2选择H列并在其上右击，在弹出的快捷菜单中选择“删除”命令，如图9-4所示。步骤3在H1单元格中输入“总金额”，在H2单元格中输入公式“=（D2+E2+F2）*G2”，并向下填充，计算出每位员工的年度薪酬金额，如图9-5所示。9.2.1分类汇总部门年度薪酬数据步骤4选择“所在部门”列中任一单元格，单击“数据”选项卡“排序和筛选”组中的“降序”按钮，将不同部门归类，如图9-6所示。9.2.1分类汇总部门年度薪酬数据步骤5单击“数据”选项卡，单击“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框，如图9-7所示。步骤6选择“分类字段”选项为“所在部门”，将“汇总方式”选项选择为“求和”，在“选定汇总项”列表框中选中“总金额”复选框，单击“确定”按钮，如图9-8所示。9.2.1分类汇总部门年度薪酬数据9.2.1分类汇总部门年度薪酬数据步骤7通过以上操作，部门年度薪酬数据汇总完成，将工作表标签名称“数据明细（2）”修改为“部门薪酬数据分析”，如图9-9所示。实现步骤视频9.2.2

展示部门年度薪酬数据为了让部门薪酬数据能更加直观地展示，我们采用两个方案：一是隐藏3级汇总明细数据，只显示1级和2级汇总数据；二是将部门薪酬数据用柱形图展示。操作步骤如下。步骤1单击按钮，隐藏3级汇总明细数据，只显示1级和2级汇总数据，如图9-10所示。步骤2按住Ctrl键选择B1：B47和H1：H47单元格区域，单击“插入”选项卡“图表”组中的“推荐的图表”按钮，打开“插入图表”对话框，如图9-11所示。9.2.2展示部门年度薪酬数据9.2.2展示部门年度薪酬数据步骤3单击“所有图表”选项卡，选择“柱形图”类型选项，选择“簇状柱状图”图表选项，单击“确定”按钮插入簇状柱形图，如图9-12所示。步骤4将图表标题更改为“部门年度薪酬分析”，如图9-13所示。9.2.2展示部门年度薪酬数据步骤5在数据系列上右击，在弹出的快捷菜单中选择“添加数据标签”命令，添加数据标签，如图9-14所示。9.2.2展示部门年度薪酬数据步骤6双击数据系列打开“设置数据系列格式”窗格，单击“填充与线条”选项卡，在“填充”展开选项中选中“依数据点着色”复选框，让数据系列分别着不同的填充色，如图9-15所示。实现步骤视频9.3

来年人力成本预算财务预算是公司年度必要工作，人力成本预算是其中的重头戏。在本例中我们首先需要复制一张明细数据表格，然后制作预算区域，接着使用公式函数计算。9.3.1

制作薪酬预算区域为了方便公式引用数据，我们特意在复制的明细数据表格中制作专门的数据预算区域，具体操作步骤如下。步骤1按住Ctrl键拖动“数据明细”标签，复制“数据明细”表格，并修改工作表标签名称为“来年人力成本预算”，如图9-16所示。步骤2在A42：D45单元格区域中输入相应的行列数据，作为预算区域部分架构数据，如图9-17所示，然后将其选中。9.3.1

制作薪酬预算区域步骤3单击“开始”选项卡中的“下框线”下拉按钮，选择“所有框线”选项，为选择区域添加边框线条，如图9-18所示。实现步骤视频9.3.2

预算来年人力成本在预算区域中已有调整系数，可直接使用SUMPRODUCT函数和简单公式得出预算数据，具体操作步骤如下。步骤1选中B43单元格，在编辑栏中输入“=SUMPRODUCT（D2：D39，G2：G39）”，按Enter键计算出“基本工资”数据并选中B44单元格，如图9-19所示。步骤2在编辑栏中输入“=SUMPRODUCT（E2：E39，G2：G39）”，按Enter键计算出“岗位工资”数据并选中B45单元格，如图9-20所示。9.3.2预算来年人力成本9.3.2预算来年人力成本步骤3在编辑栏中输入“=SUMPRODUCT（F2：F39，G2：G39）”，按Ctrl+Enter组合键计算出“绩效工资”数据，如图9-21所示。步骤4在D43：D45单元格区域，在编辑栏中输入“=B43*C43”，按Ctrl+Enter组合键确认并计算出对应的数据结果，如图9-22所示。实现步骤视频9.4管理岗位与普通职员薪酬分析在本例中我们首先需要复制一张明细数据表格，然后制作预算区域，接着使用公式函数计算。9.4.1制作薪酬统计区域为了方便公式引用数据，我们特意在复制的明细数据表格中制作数据预算区域，具体操作步骤如下。步骤1按住Ctrl键拖动“部门薪酬数据分析”标签，复制“数据明细”表格，然后修改工作表名称为“管理岗位与普通职员薪酬分析”。在A43：G45单元格区域添加专有的计算区域并添加灰色边框线条，如图9-23所示。9.4.1制作薪酬统计区域步骤2选中B45单元格，在编辑栏中输入“=SUMIF（C2：C39，"管理岗位"，H2：H39）”，如图9-24所示。步骤3选中C45单元格，在编辑栏中输入“=SUMIF（C2：C39，"职员"，H2：H39）”，如图9-25所示。9.4.1制作薪酬统计区域9.4.1制作薪酬统计区域步骤4选中D45单元格，在编辑栏中输入“=AVERAGEIF（C2：C39，"管理岗位"，H2：H39）/12”，如图9-26所示。步骤5选中E45单元格，在编辑栏中输入“=AVERAGEIF（C2：C39，"职员"，H2：H39）/12”，如图9-27所示。实现步骤视频9.4.2

分析对比薪酬福利数据相应的数据已经计算完成，为了能更加直观地展示和分析内部薪酬福利数据的情况及与行业薪酬福利的关系，分别创建静态图表和动态图表，具体操作方法如下。步骤1选择A43：C45单元格区域，单击“插入”选项卡“图表”组中的“推荐的图表”按钮，如图9-28所示。步骤2在打开的“插入图表”对话框中选择“簇状柱形图”选项，单击“确定”按钮，如图9-29所示。9.4.2分析对比薪酬福利数据9.4.2分析对比薪酬福利数据步骤3将图表标题更改为“年度薪酬同比分析”，如图9-30所示。步骤4选择图表，单击“设计”选项卡“图表样式”组中的“其他”下拉列表按钮，如图9-31所示。9.4.2分析对比薪酬福利数据步骤5在弹出的下拉列表中选择“样式6”选项，如图9-32所示。9.4.2分析对比薪酬福利数据步骤6选择A43：C43单元格区域，按住Ctrl键再选择A45：C45单元格区域，单击“插入”选项卡“图表”组中的“插入饼图或圆环图”下拉按钮，在“二维饼图”组中选择“饼图”选项，如图9-33所示。步骤7将图表名称修改为“2018年管理岗位与普通职员薪酬占比”，然后选择图表，单击“设计”选项卡，在“图表样式”列表框中选择“样式11”选项，如图9-34所示。9.4.2分析对比薪酬福利数据9.4.2分析对比薪酬福利数据步骤8选择A43：A45单元格区域，按住Ctrl键再选择D43：G45单元格区域，单击“插入”选项卡“图表”组中的“插入柱形图或条形图”下拉按钮，在“二维柱形图”组中选择“簇状柱形图”选项，如图9-35所示。9.4.2分析对比薪酬福利数据9.4.2分析对比薪酬福利数据步骤9将图表名称修改为“年度行业薪酬对比”，选择图表，单击“设计”选项卡，在“图表样式”列表框中选择“样式7”选项，如图9-36所示。9.4.2分析对比薪酬福利数据步骤10复制标题行，选择A47单元格，按Ctrl+V组合键粘贴，删除“所在部门”“岗位属性”“月份数”“总金额”字段数据，如图9-37所示。9.4.2分析对比薪酬福利数据步骤11选中A48单元格，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，如图9-38所示。步骤12选择“允许”为“序列”，“来源”为A2：A39单格区域，单击“确定”按钮，如图9-39所示。9.4.2分析对比薪酬福利数据9.4.2分析对比薪酬福利数据