项目五 薪酬管理（课件）《Excel立体化项目教程》（人民邮电出版社）

项目五 薪酬管理 Excel立体化项目教程（Excel 2016）（微课版） 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 任务一　制作员工提成奖金汇总表 任务二　汇总员工工资表 内容导航 一、任务目标 二、相关知识 （一）嵌套函数的用法 （二）常用函数介绍 （三）SmartArt图形的用法 三、任务实施 （一）使用SmartArt图形创建提成比例表 （二）使用记录单录入数据 （三）美化表格内容 （四）按照提成金额评估工作能力 项目实训 2024/8/17 2 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 一、任务目标 老洪告诉米拉，奖金管理制度中多以销售业绩为依据分配奖金。因此，在制作员工提成奖金汇总表时，首先要确定每一位员工的销售额，判断其是否达到了分配奖金的标准，然后按照相应的提成比例来计算奖金。因为奖金关系到每一位员工的切身利益，所以老洪提醒米拉计算时一定要认真、仔细，切不可马虎。本任务完成后的最终效果如图所示。 2024/8/17 3 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 二、相关知识 （一）嵌套函数的用法 函数的嵌套就是指将某个函数的返回值作为另一个函数的参数来使用，函数的参数可以是数值、文本和单元格引用等。例如，在IF函数中嵌套SUM函数和AVERAGE函数，如图所示。 2024/8/17 4 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 二、相关知识 （二）常用函数介绍 IF函数 IF函数是常用的条件函数，它常用来判断真假值，并根据逻辑计算的真假值返回不同的结果。其语法结构为IF（logical_test,value_if_true,value_if_false） MAX函数、MIN函数 SUMIF函数 MAX函数的功能是返回选择的单元格区域中所有数值的最大值，MIN函数则用来返回所选单元格区域中所有数值的最小值。其语法结构为MAX/MIN（number1,number2,...） COUNT函数的功能是返回包含数字及参数列表中数字的单元格的个数，通常利用它来计算单元格区域或数字数组中数字字段的输入项个数。其语法结构为COUNT（value1,value2,...） SUMIF函数的功能是根据指定条件对若干单元格中的数据求和。其语法结构为SUMIF（range,criteria,sum_range） COUNT函数 2024/8/17 5 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 二、相关知识 （三）SmartArt图形的用法 列表图 循环图 层次结构图 棱锥图 SmartArt图形是常见的用于表现群体关系（如雇员、职称、层次等）的一种图形。它可以形象地反映组织内各机构、岗位等上下左右相互之间的关系，是组织结构的直观反映，也是对该组织功能的一种侧面诠释。 2024/8/17 6 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 单击“SmartArt”按钮 1 （一）使用SmartArt图形创建提成比例表 完成本任务需要先打开素材文件“员工提成奖金汇总表.xlsx”工作簿，然后利用SmartArt图形创建一个提成比例表，以方便计算提成金额，具体操作如下。 选择SmartArt图形 2 2024/8/17 7 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 调整显示级别 4 更改图形颜色 5 （一）使用SmartArt图形创建提成比例表 输入文本内容 3 应用SmartArt图形样式 6 2024/8/17 8 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 选择单元格并单击“记录单”按钮 1 （二）使用记录单录入数据 由于销售部出现了人员变动，现需要更正数据记录，主要包括新记录的录入和原有记录的修改，具体操作如下。 修改数据 2 增加新记录 3 2024/8/17 9 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 插入工作表行 1 （三）美化表格内容 下面美化表格内容，首先在“Sheet1”工作表中增加表头和公司名称，然后对表格中的数据进行合并和美化设置，具体操作如下。 合并并居中单元格 2 设置字体格式 3 2024/8/17 10 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 单击 “对齐设置 ”按钮 4 （三）美化表格内容 选择边框颜色 6 选择“其他颜色”选项 5 设置表格边框效果 7 2024/8/17 11 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 输入公式 1 （四）按照提成金额评估工作能力 美化表格后，下面按照提成金额和相应的提成比例，通过IF函数来评估员工的工作能力，具体操作如下。 2024/8/17 12 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 设置函数参数 2 （四）按照提成金额评估工作能力 计算总提成金额 3 2024/8/17 13 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 任务一　制作员工提成奖金汇总表 任务二　汇总员工工资表 内容导航 一、任务目标 二、相关知识 （一）数据透视表 （二）数据透视图 三、任务实施 （一）输入提成工资数据 （二）使用数据透视表汇总数据 （三）利用数据透视图分析数据 项目实训 2024/8/17 14 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 一、任务目标 提成金额、社保扣除、加班工资等基础数据已经汇总整理完毕，老洪安排米拉将所有工资数据汇总成电子表格，以便分析和管理。要完成该任务，需要在不同工作簿中进行单元格引用，同时需要掌握数据透视表和数据透视图的创建与编辑知识。本任务完成后的最终效果如图所示。 2024/8/17 15 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 二、相关知识 （一）数据透视表 数据透视表是一种交互式报表，使用它可以对大量数据进行合并和比较，并创建交叉列表，从而清晰地反映数据。数据透视表不仅便于查看工作表中的数据，还便于分析和处理数据。在工作表中创建数据透视表的方法为：在【插入】/【表格】组中单击“数据透视表”按钮，打开“创建数据透视表”对话框，选择创建数据透视表的单元格区域，单击“确定”按钮，即可创建数据透视表，并在工作界面右侧出现相应的透视表任务窗格。 2024/8/17 16 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 二、相关知识 （二）数据透视图 数据透视图是以图表的形式表示数据透视表中的数据，可以像数据透视表一样选择需要的数据进行显示。数据透视图的创建方法为：在【插入】/【图表】组中单击“数据透视图”按钮 ，打开“创建数据透视图”对话框，选择用于创建数据透视图的单元格区域，单击“确定”按钮。 1 2 3 4 筛选数据 设置图表选项 调整字段位置 设置字段 2024/8/17 17 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 选择单元格并单击“复制”按钮 1 （一）输入提成工资数据 员工提成奖金汇总表中已经计算出了每一位员工的提成金额，因此，只需将该表格中的数据引用到员工工资表中即可完成提成工资数据的录入，具体操作如下。 选择性粘贴数据 2 2024/8/17 18 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 打开“创建数据透视表”对话框 1 （二）使用数据透视表汇总数据 下面使用数据透视表汇总表格中的数据，并美化表格，具体操作如下。 选择数据所在的单元格区域 2 2024/8/17 19 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 将字段拖曳到相应的下拉列表中 3 （二）使用数据透视表汇总数据 选择“值字段设置”选项 4 2024/8/17 20 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 更改值字段汇总方式 5 （二）使用数据透视表汇总数据 取消勾选无须汇总的员工姓名复选框 6 2024/8/17 21 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 根据数据透视表创建数据透视图 1 （三）利用数据透视图分析数据 成功创建数据透视表后，下面在数据透视表的基础之上创建数据透视图，具体操作如下。 选择“更改图表类型”命令 2 2024/8/17 22 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 选择“簇状条形图”选项 3 （三）利用数据透视图分析数据 为图表添加标题 4 更改图表标题 5 2024/8/17 23 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 三、任务实施 设置标题格式 6 （三）利用数据透视图分析数据 筛选不合格员工 2 调整数据透视图的大小和位置 7 2024/8/17 24 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 内容导航 项目实训 实训一　制作员工工序记录明细表 实训二　制作员工培训效果评估表 任务一　制作员工提成奖金汇总表 任务二　汇总员工工资表 2024/8/17 25 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 实训一 制作员工工序记录明细表 【实训思路】 完成本实训需要先利用公式计算出各员工工序的合计金额，然后插入数据透视表，最后筛选字段，并美化数据透视表。 【实训要求】 要完成本实训，需要熟练掌握数据透视表的插入与编辑方法。本实训完成后的最终效果如图所示 2024/8/17 26 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 实训一 制作员工工序记录明细表 【步骤提示】 （1）打开“员工工序记录明细表”工作簿，新建“Sheet2”工作表，选择A1单元格。 （2）打开“创建数据透视表”对话框，将要分析的数据区域设置为“Sheet2”工作表中的A2:H19单元格区域。在打开的“数据透视表字段”任务窗格中设置要添加到报表的字段，将“工序名称”“班组”“姓名”“合计金额”4个字段分别拖入“筛器”“列”“行”“值”4个下拉列表框中。 （3）单击“工序名称”字段右侧的下拉按钮 ，在弹出的下拉列表中仅勾选“切割”和“抛光”两个复选框。 （4）在【数据透视表工具 设计】/【数据透视表样式】组中，为插入的数据透视表应用预设样式，这里选择“数据透视表样式中等深浅6”样式。 2024/8/17 27 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 实训二 制作员工培训效果评估表 【实训思路】 完成本实训需要先使用IF函数对员工进行综合评价，评价标准为若自我评价+部门评价+总经理评价之和大于80，显示为“满意”，否则显示为“不满意”，然后插入数据透视图，最后筛选数据并更改图表类型为面积图。 【实训要求】 要完成本实训，需要先利用IF函数对员工进行综合评价，然后利用数据透视图比较和分析员工的培训成绩，最后筛选数据并更改图表类型。本实训完成后的最终效果如图所示。 2024/8/17 28 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 实训二 制作员工培训效果评估表 【步骤提示】 （1）打开“员工培训效果评估表”工作簿，在H3单元格中输入“=IF(SUM(E4:G4)>80,"满 意","不满意")”，确认计算公式无误后，按【Enter】键显示计算结果，并 复制H3单元格中的公式至H4:H20单元格区域。 （2）选择“Sheet1”工作表中的任意一个包含数据的单元格，在【插入】/【图表】组中单击“数据透视图”按钮 ，在打开的“创建数据透视图”对话框中单击 按钮。 （3）在数据透视表字段中添加“姓名”“培训内容”“培训方式”“总经理评价”4个字段后，插入的数据透视图中会显示相应的图表效果。 （4）选择插入的数据透视图，在【数据透视图工具 设计】/【类型】组中单击“更改图表类型”按钮 ，在打开的“更改图表类型”对话框中选择“面积图”选项。 2024/8/17 29 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 30 Thank you! $$