专题七-Excel在工资核算中的应用.pptx

学习情境学习情境1 1 创建员工工资核算系统创建员工工资核算系统专题七专题七 ExcelExcel在工资核算中的应用在工资核算中的应用学习情境学习情境3 3 生成工资条生成工资条技能实训技能实训1 1 开发工具的设置开发工具的设置技能实训技能实训2 2 打印工资条打印工资条技能实训技能实训3 3 分类汇总数据分类汇总数据学习情境学习情境2 2 员工薪金的计算员工薪金的计算【专题导航专题导航】（1）创建员工工资核算系统。（2）员工薪金的计算。（3）生成工资条。（4）开发工具的设置。（5）打印工资条。（6）分类汇总数据。【学习目标学习目标】（1）能够独立创建员工工资核算系统，进行简单的公式计算。（2）了解开发工具的基本用途。（3）能够生成并打印员工工资条的基本操作。【案例引入案例引入】工资数据的查询与汇总分析工资数据的查询与汇总分析 在工资管理中，企业员工的薪资数据往往繁多而且复杂。在实际工作中，需要对这些数据进行查询，或者是需要对数据按照一定的类型进行汇总。一、工资数据的查询一、工资数据的查询 （一）利用筛选功能进行工资数据的查询 工资数据的查询可以利用Excel的筛选功能进行工资数据的查询。筛选功能在专题二的“技能实训2筛选符合条件的数据”中已经做了详细介绍，在此不再赘述。（二）利用VLOOKUP（）函数进行工资数据的查询 VLOOKUP（）函数是一种“查找与引用”函数类型的函数，能够帮助管理者查找数据。利用VLOOKUP（）函数进行工资数据的查询在专题二的“学习情境3建立会计凭证表”的第三部分“自动显示会计科目”中已经做了详细介绍，在此不再赘述。二、数据的汇总分析二、数据的汇总分析 每个月末，财务部门为了分配工资费用，都会按照部门汇总统计工资费用。在Excel中，运用数据透视表来汇总数据，既方便快捷，又简单直观。数据的汇总分析在专题三中已经做了详细介绍，在此不再赘述。学习情境学习情境1 1 创建员工工资核算系统创建员工工资核算系统 工资管理在整个企业的财务管理中是很小的一部分但也是不可或缺的组成部分。员工工资核算系统包括员工出勤表、员工绩效表、员工福利部、员工保险公积金扣缴表、员工薪资管理表和工资条。一、创建一、创建“员工出勤表员工出勤表”员工出勤表主要是用来反映每个员工本月的病事假及加班情况的，以便对员工的工资进行相应的增减调整。制作“员工出勤表”的具体步骤如下：（1）新建一张工作簿，将其命名为“专题七”。（2）打开“专题七.xlsx”工作簿，将“Sheet1”重命名为“员工出勤表”。（3）单击A1单元格，输入“员工出勤表”。然后选中A1:F1单元格区域，单击“开始”选项卡中的“对齐方式”按钮 右侧的下拉按钮，在下拉菜单中选中“合并后居中”命令，并设置相应的字体格式，如图7-1所示。图7-1 设置表格标题 （4）在A2：F2单元格区域中，输入如图7-2所示的内容，并设置相应的字体格式。图7-2 设置单元格格式 （5）如图7-3所示，把每个员工的基本信息录入到表格中。图7-3 录入员工信息 （6）选中A2：F12，右键单击，选中“设置单元格格式”，完成边框的设置，如图7-4所示。图7-4 员工出勤表 二、创建二、创建“员工绩效考核表员工绩效考核表”员工绩效考核表主要是体现本月的工作量的情况，以便对每个员工当月工资进行相应的加减。创建“员工绩效考核表”的具体操作步骤如下：（1）打开“专题七.xlsx”工作簿，将“Sheet2”重命名为“员工绩效考核表”。（2）单击A1单元格，输入“员工绩效考核表”。然后选中A1:E1单元格区域，单击“开始”选项卡中的“对齐方式”按钮 右侧的下拉按钮，在下拉菜单中选中“合并后居中”命令，并设置相应的字体格式，如图7-5所示。图7-5 设置表格标题 （3）在A2：E2单元格区域中，输入如图7-6所示的内容，并设置相应的字体格式。图7-6 设置单元格格式 （4）如图7-7所示，把每个员工的基本信息录入到表格中。图7-7 录入员工信息 （5）选中A2：E12，右键单击，选中“设置单元格格式”，完成边框的设置，如图7-8所示。图7-8 绩效考核表 三、创建三、创建“员工福利表员工福利表”员工福利表主要反映企业本月为每个员工提供的各种福利、补贴情况，以便对员工的福利待遇进行管理。创建“员工福利表”的具体操作步骤如下：（1）打开“专题七.xlsx”工作簿，将“Sheet3”重命名为“员工福利表”。（2）单击A1单元格，输入“员工福利表”。然后选中A1:I1单元格区域，单击“开始”选项卡中的“对齐方式”按钮 右侧的下拉按钮，在下拉菜单中选中“合并后居中”命令，并设置相应的字体格式，如图7-9所示。图7-9 设置表格标题 （3）在A2：I2单元格区域中，输入如图7-10所示的内容，并设置相应的字体格式。图7-10 设置单元格格式 （4）如图7-11所示，把每个员工的基本信息录入到表格中。图7-11 录入员工信息 （5）选中A2：I12，单击右键，选中“设置单元格格式”，完成边框的设置，如图7-12所示。图7-12 员工福利表 四、创建四、创建“员工保险公积金扣缴表员工保险公积金扣缴表”员工保险公积金扣缴表反映的是企业每个月为员工代扣缴的“五险一金”情况，包括养老保险、住房公积金、失业保险、生育保险、工伤保险和医疗保险。创建“员工保险公积金扣缴表”的具体操作步骤如下：（1）打开“专题七.xlsx”工作簿，新建一张名为“员工保险公积金扣缴表”的工作表。（2）单击A1单元格，输入“员工保险公积金扣缴表”。然后选中A1:J1单元格区域，单击“开始”选项卡中的“对齐方式”按钮 右侧的下拉按钮，在下拉菜单中选中“合并后居中”命令，并设置相应的字体格式，如图7-13所示。图7-13 设置表格标题 （3）在A2：J2单元格区域中，输入如图7-14所示的内容，并设置相应的字体格式。图7-14 设置单元格格式 （4）如图7-15所示把每个员工的基本信息录入到表格中。图7-15 录入员工信息 （5）选中A2：I12，单击右键，选中“设置单元格格式”，完成边框的设置，如图7-16所示。图7-16 员工保险公积金扣缴表 五、创建五、创建“员工薪资管理表员工薪资管理表”员工薪资管理表主要是反映每个员工工资的组成项目，主要包括应该发工资项目、扣除项目、实发金额三个部分。创建“员工薪资管理表”的具体操作步骤如下：（1）打开“专题七.xlsx”工作簿，新建一张名为“员工薪资管理表”的工作表。（2）单击A1单元格，输入“员工薪资管理表”。然后选中A1:N1单元格区域，单击“开始”选项卡中的“对齐方式”按钮 右侧的下拉按钮，在下拉菜单中选中“合并后居中”命令，并设置相应的字体格式，如图7-17所示。图7-17 设置表格标题 （3）在A2：N2单元格区域中，输入如图7-18所示的内容，并设置相应的字体格式。图7-18 设置单元格格式 （4）如图7-19所示，把每个员工的基本信息录入到表格中。图7-19 录入员工信息 （5）选中A2：N12，单击右键，选中“设置单元格格式”，完成边框的设置，如图7-20所示。至此，员工薪酬管理表就基本制作完成了。图7-20 员工薪资管理表 【授之以渔授之以渔】由于员工薪酬管理表的项目内容比较多，为了查询方便，可以锁定一些固定的单元格。在员工薪资管理表中，把第2行和第A、B、C列进行冻结。单击D3单元格，选择“视图”选项卡下的“冻结窗口”中的“冻结拆分窗格”命令，这样，拖动水平滚动条和垂直滚动条时，第2行和第A、B、C列始终都会出现在表格中，如图7-21所示。图7-21“冻结拆分窗格”命令学习情境学习情境2 2 员工薪金的计算员工薪金的计算 创建完所需的表格之后，紧接着就是在需要设置公式的单元格中输入公式，这样在月末做工资统计的时候，只需要将数据录入薪资管理系统中，系统就会根据已设定好的公式自动计算每个员工的应发工资、扣款合计和实发工资。具体操作步骤如下：一、员工出勤表一、员工出勤表 （1）打开工作簿“专题七.xlsx”中的“员工出勤表”工作表。（2）根据员工请假条和加班确认单填写员工出勤表的相关信息，如图7-22所示。图7-22 员工出勤表的数据输入 二、员工绩效考核表二、员工绩效考核表 （1）打开工作簿“专题七.xlsx”中的“员工绩效考核表”工作表。（2）单击D3单元格，在编辑栏输入公式“=8*（22-员工出勤表！D3+员工出勤表！F3*1.5）”,如图7-23所示。图7-23 输入计算公式 （3）按“Enter”键，完成D3单元格数据的计算。（4）拖动序列填充柄复制公式到该列的下行，完成工作量的计算，如图7-24所示。图7-24 计算工作量 （5）单击E3单元格，在编辑栏中输入公式“=D3*10”，如图7-25所示。图7-25 输入计算公式 （6）输入完成后，按“Enter”键，显示D3单元格的计算结果。通过复制公式功能计算市场部其他员工的计件工资，如图7-26所示。图7-26 市场部计件工资的计算 （7）根据计算市场部计件工资的方法，计算发行部、研编部和排版部的计件工资，如图7-27所示。图7-27 计算其他部门员工“计件工资”三、员工福利表三、员工福利表 员工福利表操作步骤如下：（1）打开工作簿“专题七.xlsx”中的“员工福利表”工作表。（2）因为员工福利在一定时间内是稳定的，所以可以结合本公司的实际情况，输入各项福利的数据，结果如图7-28所示。如果遇到数据的变化再在本表的相应处做改变。图7-28 录入数据 （3）单击I3单元格，在编辑栏输入计算应发福利“合计数”的公式“=SUM（D3：H3）”，如图7-29所示。图7-29 输入计算应发福利“合计数”的公式 （4）输入完成后，按“Enter”键，显示I3单元格的计算结果。通过复制公式功能计算其他员工的应发福利“合计”，如图7-30所示。图7-30 计算其他员工的应发福利“合计”四、员工保险公积金扣缴表四、员工保险公积金扣缴表 员工保险公积金扣缴表操作步骤如下：（1）打开工作簿“专题七.xlsx”中的“员工保险公积金扣缴表”工作表。（2）因为员工保险在一定时间内是稳定的，所以可以结合本公司的实际情况，输入各种保险和公积金的数据，结果如图7-31所示。如果遇到数据的变化再在本表的相应处做改变。图7-31 录入数据 （3）单击J3单元格，在编辑栏输入计算员工各种保险和公积金的数据“合计”的公式“=SUM（D3：I3）”，如图7-32所示。图7-32 输入计算员工保险公积金扣缴“合计”的公式 （4）输入完成后，按“Enter”键，显示J3单元格的计算结果。通过复制公式功能计算其他员工各种保险和公积金的数据“合计”，如图7-33所示。图7-33 计算其他员工各种保险和公积金的数据的“合计”五、员工薪资管理表五、员工薪资管理表 员工薪资管理表操作步骤如下：（1）打开工作簿“专题七.xlsx”中的“员工薪资管理表”工作表。（2）因为员工基本工资和岗位工资在一定时间内是稳定的，所以可以结合本公司的实际情况，输入基本工资和岗位工资的数据，结果如图7-34所示。如果遇到数据的变化再在本表的相应处做改变。图7-34 录入基本工资和岗位工资 （3）单击F3单元格，输入计算绩效工资的公式“=员工绩效考核表！E3”，操作完成后拖动序列填充柄复制公式到该列的下行，如图7-35所示。图7-35 计算“绩效工资”（4）单击G3单元格，输入计算“加班工资”的公式“=员工出勤表！F3*50”，操作完成后拖动序列填充柄复制公式到该列的下行，如图7-36所示。图7-36 计算“加班工资”（5）单击H3单元格，输入计算应付福利合计的公式“=员工福利表！I3”，操作完成后拖动序列填充柄复制公式到该列的下行，如图7-37所示。图7-37 “福利合计数”的计算 （6）单击单元格I3，计算“应发工资合计”项目的公式“=SUM(D3:H3)”，操作完成后拖动序列填充柄复制公式到该列下面各行，如图7-38所示。图7-38“应发工资合计数”的计算 （7）单击单元格J3，计算“应扣请假款”项目的公式“=员工出勤表！D3*100+员工出勤表！E3*100*0.5”，操作完成后拖动序列填充柄复制公式到该列下面各行，如图7-39所示。图7-39 输入计算“应扣请假费”项目的公式 （8）单击K3单元格，输入计算“五险一金”项目的公式“=员工保险公积金扣缴表！J3”，操作完成后拖动序列填充柄复制公式到该列下面各行，如图7-40所示。图7-40 输入计算“五险一金”项目的公式 （9）单击L3单元格，输入计算“个人所得税”项目的公式“=IF（I3-K3-3500=0,0，IF（I3-K3-3500=1500，（I3-K3-3500）*0.03,IF（I3-K3-3500=4500，（I3-K3-3500）*0.1-105，IF（I3-K3-3500=9000，（I3-K3-3500）*0.2-555,IF（I3-K3-3500=35000，（I3-K3-3500）*0.25-1005，IF（I3-K3-3500=55000，（I3-K3-3500）*0.3-2755,IF（I3-K3-3500=80000，（I3-K3-3500）*0.35-5505，（I3-K3-3500）*0.45-13505）”，操作完成后拖动序列填充柄复制公式到该列的下行，如图7-41所示。图7-41 输入计算“个人所得税”项目的公式 （10）单击M3单元格，输入计算“扣款合计”项目的公式“=SUM(J3：L3)”，操作完成后拖动序列填充柄复制公式到该列的下面各行，如图7-42所示。图7-42 输入计算“扣款合计数”的公式 （11）单击N3单元格，输入计算“实发工资”项目的公式“=I3-M3”，操作完成后拖动序列填充柄复制公式到该列的下行，如图7-43所示。图7-43 输入计算“实发”工资的公式学习情境学习情境3 3 生成工资条生成工资条 在任何企业财务管理中，都少不了工资计算和设计工资条，工资条需要每月生成打印出来发放给员工，工资条项目的美观及操作速度是设计的一个重点。为了提高工资效率，可以通过创建“宏”生成工资条。本学习情境所述的创建工资条是通过录制交互操作过程逐步创建的，此种方法相对比较简单，具体操作步骤如下：（1）打开工作簿“专题七.xlsx”中的“员工薪资管理表”工作表。（2）单击“视图”选项卡中的“宏”的下拉按钮，如图7-44所示。图7-44 选择“宏”下拉按钮 （3）选择“录制宏”命令，弹出如图7-45所示的“录制新宏”对话框。图7-45“录制新宏”对话框 （4）输入宏名“工资条”，在“保存在（I）：”下拉列表框中选择“当前工作簿”，在“说明（D）：”编辑框中输入说明文字“生成工资条”，如图7-46所示，最后单击“确定”按钮。图7-46 设置“录制新宏”对话框 （5）按住“Ctrl”键的同时，沿工作表标签行拖动“员工薪资管理表”标签，建立副本，并双击将“员工薪资管理表（2）”改名为“工资条”，然后将“工资条”中的数据编辑修改为如图7-47所示。图7-47 工资条格式 （6）单击“停止录制”，结束宏录制。（7）打开“员工薪酬管理表”，在“开发工具”选项卡中选择“插入”中“按钮”命令，如图7-48所示。图7-48 插入“按钮”（8）点击“插入”下拉框中的“按钮”之后，在“员工薪资管理表”工作表空白区域划一个小方框，会出现如图7-49所示的对话框，选择“工资条”宏，单击“确定”按钮。图7-49“指定宏”对话框 （9）点击确定之后，会出现如图7-50所示的小方框。图7-50 小方框 （10）右击该按钮，选择下拉框中的“编辑文字”命令，如图7-51所示。图7-51“编辑文字”命令 （11）将“按钮5”修改为“工资条”，可以用鼠标拖动改动该按钮的大小，如图7-52所示。图7-52 编辑“按钮5”（12）以后每次要生成工资条时，只要将原“工资条”工作表删除，再单击“工资条”按钮，就可以完成上述一系列操作，自动生成“工资条”工作表。这样一套薪资核算系统就创建完成了。Excel 下个月进行工资核算的时候，可以先复制一下该文件，然后只要将各工作表表中每月都有变动的数据，如月份、出勤天数、事假天数、病假天数、加班天数等输入到薪资管理系统，其他的表格和数据都将自动生成，大大提高了工作效率。技能实训技能实训1 1 开发工具的设置开发工具的设置 Excel 2010版功能区的“开发工具”，主要是用于宏（VBA代码编程）、插入窗体、插入控件等。Excel 2010开发工具需要自己手动设置，设置的步骤如下：（1）打开工作簿“专题七.xlsx”中的“工资条”工作表。（2）鼠标放置“数据”选项卡单击右键，出现如图7-53所示的下拉框。图7-53 右键单击出现下拉框 （3）选择“自定义功能区”选项，出现“Excel选项”对话框，如图7-54所示。图7-54 “Excel选项”对话框 （4）在自定义功能区选择“主选项卡”，并且勾选“开发工具”选框，点击确定后，在功能选项卡区域出现“开发工具”选项卡，如图7-55所示。图7-55 “开发工具”选项卡技能实训技能实训2 2 打印工资条打印工资条 对于工资条，会计人员需要对每一员工所在的行进行分页，并且工资条中每个员工的工资所在页都需要打印出标题和工资项目，因此还需进行设置跨页列、行标题，最后进行打印。具体操作步骤如下：（1）打开工作簿“专题七.xlsx”中的“工资条”工作表。（2）选择“员工薪资发放条”中的第3行，点击“页面布局”选项卡中的“分隔符”下拉框，选择“插入分隔符”命令，如图7-56所示。图7-56 选择插入“分隔符”命令 （3）从第一个员工下方开始插入行分页符，进行强制分页，并依次进行直至最后一位员工，如图7-57所示。图7-57 插入分页符设置结果 （4）单击“文件”选项卡下的“打印”命令，出现“页面设置”对话框，切换到“工作表”选项卡，进行“顶端标题行”设置，如图7-58所示。该设置的结果将保证打印出来的每一个员工的工资条上面都出现第一行为标题“员工薪资发放条”，第二行为工资项目行。图7-58“顶端标题行”设置 （5）单击常用工具栏的“打印预览”按钮，则屏幕上出现打印预览对话框，如图7-59所示。图7-59 打印预览 （6）指定工资条的打印区域，进行打印。单击“文件”选项卡下的“打印”命令，出现“页面设置”对话框，切换到“工作表”选项卡，在“打印区域”输入要打印的范围，如图7-60所示，然后单击“打印”按钮，即可打印。图7-60 打印区域设置技能实训技能实训3 3 分类汇总数据分类汇总数据 为了满足数据处理的需要，经常需要按照某些指定列的取值对数据库格式的表格进行汇总计算，为此Excel 2010提供了“分类汇总”功能。对数据进行分类汇总的操作步骤如下：（1）在“企业信息管理”工作簿中将“Sheet3”工作表的名称修改为“分类汇总”，并把“办公室信息表”工作表中的内容复制到该工作表中，然后设置单元格格式，最后在“合计”列后面添加“审核人”列，如图7-61所示。图7-61 创建“分类汇总”工作表 （2）选中“审核人”列中的任意单元格，并单击“排序和筛选”组中的“降序”按钮，即可对该列的数据进行降序排序，如图7-62所示。图7-62 按照降序排序审核人 （3）单击“排序和筛选”组中的“分类汇总”按钮，即可打开“分类汇总”对话框，如图7-63所示。（4）在“分类字段”下拉列表中选择“审核人”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”列表中选择除“时间”、“审核人”之外的项目，然后单击“确定”按钮，即可看到对“审核人”进行汇总的结果，如图7-64所示。图7-63 “分类汇总”对话框 图7-64 “审核人”进行汇总的结果专题小结专题小结 本专题主要介绍了利用Excel进行薪资管理。首先通过回顾数据透视表和筛选等知识引入本专题的学习内容，然后介绍了利用Excel制作薪资管理系统，其中包括员工出勤表、员工绩效表、员工福利表、员工保险公积金扣缴表和员工薪资管理表，最后，介绍了生成和打印工资条的相关知识，实现了会计工作的方便化、快捷化，提高了工作效率和准确性。专题测试专题测试 1.依照下列步骤，完成公司员工薪资管理表。（1）新建Excel工作簿，并将“Sheet1”命名为“员工薪资管理表”。（2）工资项目内容如图7-65所示。（3）自行修饰员工薪资管理表。图7-65 员工薪资管理表 2.自己设计一张名为“基本工资数据”的工作表。要求：列出10名职工的基本信息，进行如下操作。（1）设置职工薪资管理表，包括基本工资、奖金、事病假扣款和代缴所得税四个基本内容，以及应发合计、应发工资、实发工资三个合计科目。（2）使用直接输入法输入前五个职工的数据，后五个使用记录单方式输入。（3）使用自动筛选功能进行查询。学习结束！