《学习情境3 excel在薪资管理岗位应用.ppt.ppt》由会员分享，可在线阅读，更多相关《学习情境3 excel在薪资管理岗位应用.ppt.ppt（50页珍藏版）》请在三一办公上搜索。
1、,课件开启中,请稍后,Excel在会计和财务中应用,工作目标 本章主要介绍Excel 在企业工资管理中的应用，包括基本信息的设置、员工考勤表的录入、工资计算、工资数据汇总与分析、工资费用数据查询等功能。工作思路 通过本章学习使读者初步掌握如何运用Excel软件中的函数、宏、图表分析等功能进行工资处理的基本方法与思路,并建立有关图表、图形进行分析。,工作任务一 薪资管理基本表格的建立项目活动一 职工基本信息表工作情境1：本章以某民办高校华岳经济管理学院为例，设计一个基本工资管理系统。该学院有关工资的基本信息资料如经济管理学院共有会计系、经贸系、机械系、信管系组成，职称有教授、副教授、讲师、助教等
2、组成。每个员工的工资项目有基本工资、岗位工资、津贴、课时费、考勤扣款、养老保险、医疗保险、个人所得税、实发工资等项目组成。共有专职人员15人，其余以兼职教师为主。根据单位实际情况来设置学院部分员工个人基本信息表。,工作步骤：1、建立“工资管理工作簿”，将工作表Sheet1命名为“教师基本信息表”，并保存在“工资管理”工作簿中。2、设计并输入“教师基本信息表”，并根据单位实际情况直接输入单位人员编号、姓名。3、由于部门、职务、职称、性别这几个项目的资料是固定的且内容较少，可采用下拉列表的形式输入。下面以部门为例进行讲解，基本操作方法为：选定单元格区域C3：C 17，然后选择“数据”“数据有效性”
3、命令，打开“数据有效性”对话框，在“设置”选项卡中“有效性条件”栏中的“允许”下拉列表框中选择“序列”，并在“来源”中输入学院所属部门（例如：办公室、会计系、经贸系、机械系、信管系）这几个类别，如图所示。单击“确定”按钮，结果如图所示。,工作情境2：在工资管理工作簿中建立工资基本初始信息表，在基本信息表中根据不同职务来设置岗位工资、津贴标准表，根据不同职务来设置基本工资和课时费用标准表，设置考勤扣款表、社会保险扣缴比例表等。技能点：建立工资基本初始信息表。1、把工作表Sheet2命名为“工资基本信息表”。2、根据已知实验资料建立工资基本初始信息表,为工资各初始信息表定义名称。选定单元格B2：D
4、7区域定义名称为“职务岗贴”；选定单元格F2：G6区域定义名称为“基本工资”，选定单元格I2：J5区域定义名称为“考勤扣款”；选定单元格B10：D14区域定义名称为“课时数及课时费”；选定单元格F9：I19区域定义名称为“保险扣交”；选定单元格区域F9：I19名称为“个税率”：单元格G9定义名称为“起征点”。名称定义是否成功及结果可以通过选择“插入”“名称”“定义”命令打开的“定义名称”对话框中查找。,项目活动二 职工出勤情况表 工作情境1：根据实验资料，已知职工考勤主要分为病假、事假及旷工3类，同时由于每月的天数可能是30天或31天，故要考虑天数的设置；由于周六、周日不上班不用考勤，也应该设
5、置每天对应的星期。技能点：制做职工出勤情况明细表。工作步骤：1、打开工资管理工作簿，将工作表Sheet3命名为“职工考勤及汇总表”。2、设计职工考勤明细表,(1)单元格P8(为单元格P8：O8合并)为当前日期，其公式为“=today()；(2)单元格A11：A41为本月1,2,，31号所对应的天数。该列数字通过填充柄直接填充自动生成日期。然后B11单元格中输入11月1日对应的周次数，其它单元格使用填充柄直接填充至B40区域中输入产生如图3-7的结果。3、根据人事部门的考勤记录，输入职工考勤明细表记录。,工作情境2：根据单位实际出勤情况，进行日常考勤记录汇总。技能点：制做职工考勤汇总表。工作步骤
6、：1、在“教师考勤表”上方，设计“职工考勤汇总表”。单元格C4：Q4区域表示每位员工病假天数汇总；单元格C5：Q5区域表示每位员工事假天数汇总；单元格C6：Q6区域表示每位员工旷工天数汇总。,2、对每位员工进行病假、事假及旷工天数进行汇总。在“何能”病假天数汇总单元格C4输入公式为“=COUNTIF(C11：C40,$B$4)，其他员工病假天数汇总公式可通过填充柄将C4中的公式复制到其它单元格。在“何能”事假天数汇总单元格C5输入公式为“=COUNTIF(C11：C41,$B$5)，其他员工事假天数汇总公式可通过填充柄将C5中的公式复制到其它单元格。在“何能”旷工天数汇总单元格C6输入公式为“
7、=COUNTIF(C11：C41,$B$6)，可通过填充柄将C6中的公式复制到其它单元格。,3、日常考勤应扣款计算。根据不同假别的每天扣款数及每位员工的病假、事假及旷工天数汇总数据，计算每位员工的考勤扣款数。每位员工的本月考勤扣款合计数据所在单元格区域为C7：Q7，其中单元格C7公式为“=VLOOKUP（$B$4,考勤扣款，2，FALSE）*C4+VLOOKUP（$B$5，考勤扣款，2，FALSE）*C5+VLOOKUP（$B$6，考勤扣款，2，FALSE）*C6,项目活动三 职工工资表工作情境1：根据己知实验资料的工资项目组成，进行工资各项目数据的计算。技能点：工资计算。工作步骤：1、在“E
8、xcel与工资管理”工作簿中插入一张工作表，并命名为“基本工资表”。2、根据已知工资项目组成，设计基本工资表各项目,3、生成各基本项目。(1)日期项目的单元格公式为=IF(B3”“,MONTH(TODAY()&”月”，”)表示自动产生本月的月份，编号手工输入。(2)姓名、部门及职称数据通过vlookup()函数从“教师信息表”中自动生成。单元格C3公式为:=vlookup(B3，教师信息表,2,false)本列其他单元格公式通过复制C3得到。单元格D3公式为“=lookup(B3，教师信息表,3,false)，本列其他单元格公式通过复制D3得到。单元格E3公式为“=vlookup(B3，教师信
9、息表,5,false)，本列其他单元格公式通过复制E3得到。,4、基本工资、岗位工资及津贴数据。(1)在基本工资单元格3输入公式=VLOOKUP(VLOOKUP(B3，教师信息表,5，EALSE),基本工资，2,FALSE)单元格F4：F 17公式通过复制单元格F3公式得到。其中：参数“教师信息表”为教师基本信息表定义的名称；“基本工资”为“基本工资基本初始信息表”中职称与基本工资对照表定义的名称；VLOOKUP(B3，教师信息表，5，FALSE)表示为在“教师信息表”中查找员工编号所对应的职称；VLOOKUP(VLOOKUP(B3，教师信息表，5,FALSE)，基本工资，2，FALSE)是表
10、示在职称与基本工资对照表查找职称所对应的基本工资。,(2)在岗位工资单元格G3输入公式=VLOOKUP(VLOOKUP(B3，教师信息表，4,FALSE),职务岗贴，2,FALSE)单元格G4：G17公式通过复制单元格G3公式所得到。其中：参数“教师信息表”同上解释；“职务岗贴”为职务与岗位工资、津贴对照表定义的名称；VLOOKUP(B3，教师信息表，4,FALSE)表示在“教师信息表”中查找员工编号所对应的职务；=VLOOKUP(VLOOKUP(B3，教师信息表，,4,FALSE)，职务岗贴，,2,FALSE)表示为在职务与岗位工资、津贴对照表查找职务所对应的岗位工资。(3)在津贴单元格H3
11、输入公式“=VLOOKUP(VLOOKUP(B3，教师信息表，4,FALSE)，职务岗贴，3,FALSE)。单元格H4：H 17公式通过复制单元格H3公式得到。,5、生成课时费数据。由于课时费数据是通过每月每位教师实际上课时数与标准课时数对比，再乘以每位教师职称对应的课时费计算得到。它计算起来相对比较复杂，为此我们再设计一个课时费计算表，以计算每位教师的课时费数据。注意，如果教师的上课时数未达到标准课时数，则课时费为负。,工作情境1：为了方便每位员工能够清楚地了解自己的工资收入及扣除情况，每个企业都要给员工发一个自己本月的工资条，以便核对自己的工资数据正确与否。工资条数据是根据企业本月工资表的
12、数据生成的，是在原来数据基础上，在每位员工的数据前面再加上一个表头而己。技能点：工资条的自动生成设计工资条的方法很多，这里提供两种方法：一种是采用Word的邮件合并功能与Excel数据产生联结，从而生成Word形式的工资条；一种是用自动排序的方法来产生工资条。一、采用Word邮件合并功能实现工资条的生成工作步骤：1、创建Word模板。在Word中新建一个文件，页面设置为A4纸，按照应打印工资项目的名称，制作出一个表格，在一页纸中设计能打印个工资条，将其另存为工资条模板备用。,创建WORD工资条模板2、选择邮件合并工具栏。选择“工具”“信函与邮件”“邮件合并”命令，单击“邮件合并”向导第一步“选
13、择文档类型”“下一步”按钮，打开“选择开始文档”对话框，选择对话框，选择“使用当前文档”选项，单击“下一步”按钮，打弃“选择收件人”对话框，择“使用现有列表”选项,3、选中包含工资条中所有数据的工作表。单击“浏览”按钮，选择Excel与工资管理”工作簿所在的位置，并单击“确定”按钮，打开“选择表格”对话框，选择“基本工资表，结果如图所示。,4、为每一项目建立数据连接。单击“下一步”按钮打开“撰写信函”对话框，将光标插入表格第一个项目的位置(例如，“月份”下的单元格)，单击“其他项目”命令，打开“插入合并域”对话框，单击“数据库域”单选按钮，再选中“域”列表框中的“月份”选项。单击“插入”按钮就
14、可以将月份合并域插入到表格中,5、生成工资条。工资条文档中的文字也可像普通文字那样进行设置，完成以后，单击“下一步”按钮打开“预览信函”对话框，单击“编辑收件人列表”命令，打开“合并到新文档”对话框，即可在打开的对话框中选择合并记录的范围。如果需要生成所有员工的工资条，可以选中“全部”单选按钮；打印当前记录时可选中“当前记录”单选按钮；否则可以选中“从”单选按钮，然后输入记录的起止序号。单击“确定”按钮就会在新文档窗口生成工资条。,二、运用排序实现工资条的自动生成工作步骤：1、新建工作表并命名为“生成工资条”，然后打开生成工资条数据的“基本工资表”，选定工资数据区域并复制该区域，然后选择“生成
15、工资条”工作表，选择A3单元格，单击右键，选择“选择性粘贴”命令，打开“选择性粘贴”对话框，单击“确定”按钮，复制“基本工资表”所有数据到“生成工资条”表格中。2、使用填充柄来进行填充，将工资项目的表头复制若干份,3、再输入数字，注意输入时，使用填充柄将表头每行输入奇数，例如“1、3、5、7、9”将具体工资数据每行输入偶数，例如“2、4、6、8、10”再选择“数据”/“排序”/“升序”，经升序排序后，生成工资条。,项目活动四 职工社会保险费表工作情境1：为了减轻个人与社会组织的劳动，国家规定企业员工的社会保险由企业代为缴纳。因此企业必须设计有关社会保险代扣代缴数据表。技能点：设计企业代扣代缴社
16、会保险表。工作步骤：1、插入一工作表，并命名为“社会保险表”。2、根据社会保险的项目，设计社会保险项目表格并输入已知数据,3、计算各项社会保险项目的数据。根据工资表的相关项目的己生成数据，直接查找个人各项社会保险项目数据，并根据“工资基本初始信息表”及个人社会各保险项目数据生成单位各项社会保险项目数据。(1)养老保险项目计算。养老保险项目所在单元格区域为F4：G18，其中个人应缴养老保险所在单元格F4的公式为“=VLOOKUP(C4，基本工资表，MATCH($F$2，基本工资表!$B$2：$O$2),FALSE)”，其中，参数“基本工资表”为基本工资表单元格区域B2：017定义的名称；单位应缴
17、养老保险所在单元格G4公式为“=F4*VLOOKUP($G$3，保险扣交，2,FALSE)/VLOOKUP($F$3，保险扣交，2,FALSE)”，其中“保险扣缴”为“工资基本初始信息表”中社会保险扣缴比例表区域定义的名称。其他单元格公式通过复制单元格F4、G4得到。,(2)医疗保险项目计算。医疗保险项目在单元格 H4：I18：区域，个人应缴医疗保险所在单元格H4公式为“=VLOOKUP(C4，基本工资表，MATCH($H$2，基本工资表!$B$2：$0$2),FALSE)；单位应缴医疗保险所在单元格I4公式为“=H4*VLOOKUP($I$3，保险扣交，3,FALSE)/VLOOKUP($H
18、$3，保险扣交，3,FALSE)。其他单元格公式通过复制单元格H4、I4得到。(3)合计项的计算。合计项在单元格J4：J18区域，其中单元格J4公式为“=SUM(F4：I4)，其他单元格公式通过复制单元格J4得到。,项目活动五 个人所得税代扣代缴表工作情境：根据单位实际情况来设计企业代扣代缴个人所得税表。工作步骤：1、插入一工作表，并命名为“个人所得税代缴汇总表”。2、根据个人所得税的项目，设计代扣代缴个人所得税表格并输入己知数据。3、生成个人所得税代缴表各项数据。根据“基本工资表”的相关项目的已生成数据，直接查找个人所得税项目数据，并填入相关项目单元格。,(1)应税金额项目数据生成。应税金额
19、项目所在单元格区域为E3：E17，其中单元格E3的公式为=VLOOKUP(B3，基本工资表，12,FALSE)其中“基本工资表”是为基本工资表B2：O17区域定义的名称，其他单元格公式通过复制单元格E3得到。（2）个人所得税项在单元格F3：F17区域，其中单元格F3公式为=VLOOKUP(B3，基本工资表，12,FALSE)其他单元格公式通过复制单元格F3得到。(3)合计应缴个税项目的生成。合计应缴个税项目所在单元格为F18，其公式为“=SUM(F3:F17)。,项目活动六 职工福利表工作情境：为了更直观的反映单位职工福利情况，设计单位职工福利表。技能点：设计职工福利表工作步骤：1、插入一工作
20、表，并命名为“职工福利表”。2、根据职工福利项目，设计职工福利项目表格并输入已知数据,3、计算各项职工福利项目的数据。根据基本工资表的相关项目的己生成数据，直接查找个人各项福利表项目数据，并根据“工资基本初始信息表”生成单位各项职工福利项目数据。(1)住房补贴项目计算。住房补贴项目所在单元格区域为D3：D16，其中住房补贴所在单元格D3的公式为“=VLOOKUP(A4，基本工资表，4，FALSE)*30%，其中，参数“基本工资表”为基本工资表单元格区域B2：017定义的名称；30%是住房补贴系数，其他单元格公式通过复制单元格D3的公式得到。(2)伙食补贴项目计算。伙食补贴项目在单元格 E3：E
21、16区域，伙食补贴所在单元格E4公式为“=VLOOKUP(A4，基本工资表，4，FALSE)*18%；其中，参数“基本工资表”为基本工资表单元格区域B2：017定义的名称；18%是伙食补贴系数，其他单元格公式通过复制单元格E3得到。,（3）交通补贴项目计算。交通补贴项目在单元格 F3：F16区域，个人应缴医疗保险所在单元格E4公式为“=VLOOKUP(A4，基本工资表，4，FALSE)*10%；其中，参数“基本工资表”为基本工资表单元格区域B2：017定义的名称；8%是交通补贴系数，其他单元格公式通过复制单元格F3得到。（4）医疗补助的计算同上，医疗补助的系数为8%。(5)合计项的计算。合计项
22、在单元格H3：H16区域，其中单元格H3公式为“=SUM(D3：G3)，其他单元格公式通过复制单元格H3得到。,工作任务二 工资数据的汇总分析项目活动一 工资数据总额汇总表工作情境：运用Excel对员工工资的基本数据进行处理的好处，不仅仅是利用计算机计算的简便和快捷，更重要的是对这些数据所进行的分析，这可以给管理者提供很大的帮助，可运用“数据透视图”和“数据透视表”功能来进行简单的数据处理和分析。技能点：依据部门和职工类别的统计分析,一、计算每一部门“实发合计”的汇总数工作步骤:1、插入一张新的工作表，并将其命名为“工资汇总表”，复制“基本工资表”工作表的数据到新表。2、选中要进行数据分析的区
23、域A2：O17，选择“数据”/“数据透视表”中的“数据透视表”命令，弹出“创建数据透视表”对话框，在“选择放置数据透视表的位置”选项组中选择“新工作表”单选按钮。3、单击“确定”按钮，进入数据透视表界面，将“实发合计”项目拖到图表数据区域，将“部门”项目拖到“在此处放置系列字段”处，将“月份”项目拖到“在此处放置页字段”处。,4、在“数据透视表工具”中选择“选项”/“工具”功能组中的“数据透视图”命令，出现“插入图表”对话框，选择柱形图中的三维簇状柱形图，,5、单击“确定”按钮。6、美化数据透视图。要想在数据透视图上显示数字，首先选定数据透视图上的图条，然后单击右键，选择“添加数据标签”命令。
24、相关知识：也可以通过“数据透视表工具”中的“设计”、“布局”、“格式”和“分析”菜单中的各功能组进行图表标题、格式等相关设置。二、计算各部门“实发合计”所占的百分比1、在“数据透视表字段列表”的“数据”区域单击“求和项”下拉表，选择“字段设置”，出现“字段设置”对话框，打开“值显示方式”选项卡，在“值显示方式”下拉列表框中选择“占同行数据总和的百分比”选项。,项目活动二 工资数据的汇总分析工作情境1：公司要求按年龄与实发合计分段，并做出相应的统计分析。技能点：根据年龄段或基本工资段的统计分析工作步骤：1、插入一张工作表，并将其命名为“工资汇总表，在原工资数据基础上增加3列：“年龄段”和“应发合
25、计段”,2、生成“年龄”、“年龄段”列和“应发合计段”列数据。(1)在“年龄段”单元格区域E4：E18输入公式，其中E4单元格的公式为“=IF(D3=40,”年龄=40,IF(D3=30,30=2500,应发合计=2500,IF(F3=2000,2000=应发合计2500,应发合计2000)，将G4单元格的公式复制到S列的其他单元格。,3、按照项目活动一，将“年龄段”拖到分类字段处，将“应发合计段”和“年龄段”拖到系列字段处，把“月份”拖动到“页字段”处，将“应发合计”拖动到“数据区域”，并设置“值字段设置”的汇总方式为“计数”，为应发合计在“=2500”段的所有年龄阶段的数据透视表及数据透视
26、图,工作情境2：数据的查询有许多种方法，在Excel中最常用的方法有筛选法和公式定义法。一、利用自动筛选功能进行工资数据的查询工作步骤：1、选择“工资汇总表”工作表，如果要利用筛选功能进行工资数据查询，首先要进入筛选状态。选择“数据”/“筛选”/“自动筛选”命令，进入自动筛选状态。(1)以“职工姓名”为依据进行查询。例如：查询职工姓名为“李佑”的职工的工资情况。单击“职工姓名”列按钮，并选择“文本筛选”项中的等于命令，出现“自定义自动筛选方式”对话框，“显示行”中设置为“等于”和“李佑”，单击“确定”按钮。,工作情境3：利用高级筛选功能进行工资数据的查询一、以“部门”和“实发工资”为依据进行查询。例如，查询会计系应发工资小于3000或工商系应发工资大于2500的职工的工资情况。工作步骤：1、设置筛选条件区域。根据本题，筛选条件为“部门=会计系”并且“应发工资2500在基本工资表的适应区域设置该筛选条件。,2、选择“数据”/“筛选”/“高级筛选”命令，设置条件区域。3、单击“确定”按钮，结果如下：,谢谢！,Thank you very much!,下一章,
