《Excel在财务会计中的应用》（第3版） 教案 项目四 工资管理教案

Excel在工资管理中的应用电子教案项目四Excel在工资管理中的应用项目介绍本任务主要内容为工资结算单、工资数据的查询与统计分析、工资费用分配表。项目目标1.知识目标了解工资核算的业务处理流程掌握工资项目的组成及计算方法熟练掌握IF函数、OR函数等函数的使用方法2.能力目标学会使用Excel设置工资项目公式的能力学会使用Excel筛选功能进行工资数据查询的能力学会使用Excel数据透视表进行工资数据分析的能力3.素质目标培养学生严谨细致的工作态度培养学生诚信纳税的爱国精神培养学生勤于思考的学习习惯培养学生自主学习的学习能力项目重难点1.重点掌握编制工资结算单、编制工资费用分配表的操作方法。2.难点工资结算单公式的定义。项目内容任务1录入员工基础工资数据；任务2编制工资结算单任务3工资数据的查询与统计分析任务4编制工资费用分配表相关知识任务1录入员工基础工资数据任务1-1建立基本工资信息表本任务是建立基本工资信息表，为后面的工资表中工资项目的计算提供依据。任务实施：一、定义基本工资信息表格式操作步骤如下：（1）打开Excel2010，新建空白工作簿，并另存为“D:\东方公司\工资管理2202.xlsx”。（2）双击工作表标签Sheet1，更名为“基本工资信息表”。（3）选择A1单元格，输入工作表标题“东方公司员工基本工资表”。（4）选中单元格A1:E1，设置字体为“宋体”，字号为“12”，加粗。选择对齐方式为“跨列居中”。（5）在A2至E2单元格内分别录入“员工编号”、“员工姓名”、“所属部门”、“职工类别”、“基本工资”项目，设置其字体为“宋体”，字号为“12”，加粗，黄色底纹。设置A至E列的对齐方式为“居中”。（6）设置“员工编号”所在A列的格式为“文本”类型，设置“基本工资”所在E列的格式为“会计专用”类型，小数位数为2，货币符号无。二、定义单元格的有效性为了输入方便并防止出错，可对某些数据列添加有效性控制。操作步骤如下：1.对“所属部门”所在列添加有效性控制（1）选择C列，单击功能区中的“数据”—“数据工具”—“数据有效性”按钮，打开“数据有效性”对话框。（2）在“设置”选项卡中的“允许”下拉列表框中选择“序列”选项；在“来源”框中输入“行政部，财务部，技术部，采购部，销售部，生产一车间，生产二车间，生产三车间”。（3）最后单击“确定”按钮，则完成对“所属部门”数据有效性的设置。2.对“职工类别”所在列添加有效性控制同理，设置D列的数据有效性为序列“总经理,部门经理,管理人员,采购人员,销售人员,生产人员”。三、录入基本工资信息表方法一：直接输入法根据表2-1中所给数据分别录入“员工编号”、“员工姓名”、“所属部门”、“职工类别”、“基本工资”项目基本数据。方法二：记录单输入法Excel会将数据清单当成数据库来处理，对数据清单的各种编辑操作常用到“记录单”。通过“记录单”功能不仅可以进行数据的录入工作，还可以实现数据的添加、删除、修改和查询操作。操作步骤如下：（1）将鼠标指针移动到将要输入新数据的单元格A11，然后单击“记录单”功能按钮，打开“记录单”对话框。（2）在“记录单”对话框的左侧列出了数据库结构的内容，其中每一项内容对应一个文本框，用户只需要在文本框中输入数据的内容即可。（3）每输入一条记录后单击“新建”按钮即可接着输入下一条记录，单击“关闭”按钮即可完成记录的输入。（4）如果用户对输入的数据进行修改，可以先选定数据表中的任意单元格，再单击“记录单”功能按钮，通过单击“上一条”或“下一条”按钮，或者拖动滚动条来进行记录定位。选定要修改的记录后，对其进行修改。（5）如果需要删除某条记录，在选定某条记录后，单击“记录单”对话框中的“删除”按钮即可。（6）完成东方公司所有员工基本工资信息录入工作后，关闭“记录单”对话框。四、美化工作表本案例中，工作表标题及列标题的美化(字体、字号、底纹等操作)在录入数据的过程中已经完成，表格边框及列宽和行高设置需要在全部数据录入完成后进行。操作步骤如下：（1）选择单元格区域A2:E27，设置网格线为所有框线。（2）调整A:E列至合适的列宽。同理，定义“岗位工资标准”“绩效工资标准”操作方法与“基本工资信息表”类同。任务1-2建立专项附加扣除表本任务是根据东方公司职工个人提供的“子女教育”、“继续教育”、“大病医疗”、“住房贷款利息”、“住房租金”、“赡养老人”六项专项附加扣除信息建立专项附加扣除表。具体资料见教材表4-4。任务实施：操作步骤如下：（1）打开”工资管理2202.xlsx”工作簿，双击工作表标签Sheet2，输入一个新的工作表表名“专项附加扣除表”。（2）复制“基本工资信息表！A:D”列到“专项附加扣除表！A:D”列。（3）选中“专项附加扣除表”工作表，删除第一行。（4）在E1:K1单元格内分别输入“子女教育”、“继续教育”、“大病医疗”、“住房贷款利息”、“住房租金”、“赡养老人”、“合计”。（5）选择E:K列，设置数字格式为“会计专用”，小数位数为2，货币符号无。（6）按照表4-4的内容输入每位职工六项专项附加扣除的信息。（7）定义K2单元格的公式为“=SUM(E2:J2)”，向下拖拉填充柄复制到K26单元格。（8）选择单元格区域E1:K26，设置网格线为所有框线。任务2编制工资结算单任务2-1设置工资结算单格式结合相关资料，李悦设计东方公司的工资结算单不仅包括基本工资信息表中的工资项目，还包括“岗位工资”、“绩效工资”、“请假天数”、“请假扣款”、“应发工资”、“扣社保费”、“扣公积金”、“扣个税”及“实发工资”等工资项目。任务实施：操作步骤如下：（1）打开”工资管理2202.xlsx”工作簿，双击工作表标签Sheet3，将Sheet3命名为“工资结算单”。（2）打开“基本工资信息表”工作表，选中单元格A2:E2，按组合键“Ctrl+C”进行复制操作。打开“工资结算单”工作表，选中单元格A1，按组合键“Ctrl+V”进行粘贴，将内容全部复制至“工资结算单”工作表中。（3）选中单元格F1:Q1，依次输入工资项目名称:“岗位工资”、“绩效工资”、“应发工资”、“扣社保费”、“扣公积金”、“扣个税”及“实发工资”、“专项附加扣除”、“5月累计应纳税所得额”、“5月累计预缴个税”、“4月累计应纳税所得额”和“4月累计预缴个税”。使用格式刷，复制单元格A1:E1的格式到单元格F1:Q1（4）选择H至Q列，设置格式为“会计专用”。（5）选中单元格A2:Q26，单击功能区中的“开始”—“字体”—“边框”—“所有框线”按钮，设置网格线。任务2-2设置工资项目本任务主要包括应发工资的计算、代扣款项的计算和实发工资的计算。任务实施：李悦设置相关工资项目公式如下：一、设置“岗位工资”项目根据公司规定,“岗位工资”是根据“职工类别”的不同来设置的，具体要求见表4—2。操作步骤如下：选择单元格F2，单击功能区中的“公式”—“插入函数”按钮，打开“插入函数”对话框，选择常用函数下的IF函数，单击确定按钮。设置IF函数嵌套的参数如下图公式含义：如果D2单元格的值为“总经理”，则返回值为“4000”，否则又有4种情况，所以在第3个参数里继续单击IF函数作进一步判断，如果D2单元格的值为“部门经理”，则返回值为“3500”，如果不是，则继续单击IF函数进行判断，如果D2单元格的值为“管理人员”，则返回值为“3000”，如果不是，则继续单击IF函数进行判断，如果D2单元格的值为“销售人员”，则返回值为“2000”，如果不是，则IF函数值为“2500”。二、设置“绩效工资”项目根据公司规定，绩效工资是依据公司和各部门的效益决定的，具体要求见表4—3。操作步骤如下：选择单元格G2，公式设置为“=IF(OR(C2="行政部",C2="技术部"),2000,IF(C2="销售部",2500,1500))”三、设置“应发工资”项目“应发工资”项目的计算公式为：应发工资＝基本工资+岗位工资+绩效工资操作步骤如下：选择单元格H2，公式设置为“=E2+F2+G2”。四、设置“扣社保费”项目根据公司规定，“扣社保费”的计算基数是“应发工资”项目。四项社会保险费中，基本养老保险费个人缴费比例为8%，基本医疗保险费个人缴费比例为2%，失业保险费个人缴费比例为0.3%，合计10.3%。“扣社保费”项目的计算公式为：扣社保费＝应发工资*10.3%操作步骤如下：选择单元格I2，公式设置为“=H2*0.103”五、设置“扣公积金”项目根据公司规定，“扣公积金”的计算基数也是“应发工资”项目。住房公积金个人缴费比例为6%。“扣公积金”项目的计算公式为：扣公积金＝应发工资*6%。操作步骤如下：选择单元格J2，公式设置为“=H2*0.06”六、设置“专项附加扣除”项目“专项附加扣除”项目的数据来自“专项附加扣除表”。操作步骤如下：选择单元格M2，公式设置为“=专项附加扣除表!L2”。七、设置“2月累计应纳税所得额”项目“2月累计应纳税所得额”项目的计算公式为：2月综合所得累计应纳税所得额=应发工资×2-5000×2-扣社保费×2-扣公积金×2-专项附加扣除×2操作步骤如下：选择单元格N2，公式设置为“=H2*2-5000*2-I2*2-J2*2-M2*2”八、设置“2月累计预缴纳个税”项目根据2月累计应纳税所得额，查找表2-6“个人所得税税率表”，计算2月累计预缴纳个税。操作步骤如下：选中单元格O2，公式设置为：=ROUND(IF(N2<=0,0,IF(N2<=36000,N2*0.03,N2*0.1-2520)),2)九、设置“1月累计应纳税所得额”项目选择单元格P2，公式设置为“=H2-5000-I2-J2-M2”，结果如图4-22所示。十、设置“1月累计预缴纳个税”项目选中单元格Q2，公式设置为：=ROUND(IF(P2<=0,0,IF(P2<=36000,P2*0.03,P2*0.1-2520)),2)十一、设置“扣个税”项目“扣个税”项目的计算公式为：扣个税=2月累计预缴纳个税-1月累计预缴纳个税十二、设置“实发工资”项目“实发工资”项目的计算公式为：实发工资=应发工资-扣社保费-扣公积金-扣个税操作步骤如下：选择单元格L2，公式设置为“=H2-I2-J2-K2”。任务3工资数据的查询与统计分析因为工作需要，财务部门或薪资管理人员经常需要了解一下某个部门或职工的工资情况，并对公司或部门员工的工资情况按照一定的标准进行排序、分类汇总。本任务将利用排序、筛选功能进行工资的查询，使用数据透视表和数据透视图进行数据的简单处理和分析。任务3-1工资表数据排序排序是将数据区域中的记录，按字段名的数据值大小进行排列，从小到大排序称为升序，从大到小排序称为降序，用来排序的字段或条件称为排序关键字。在Excel中进行排序时，数字和日期按数值大小进行排列；字母按字母顺序进行排列；汉字转化为汉语拼音，并根据首个汉字字母比较规则进行比较排序。任务实施：一、方法一：利用功能区中的“升序”按钮/“降序”按钮排序案例1：利用“降序”按钮对东方公司工资结算单按“基本工资”由高到低进行降序排列。操作步骤如下：（1）打开工资结算单，选择“基本工资”E列，单击功能区中的“数据”—“排序和筛选”—“降序”按钮。（2）在弹出的“排序提醒”对话框中选中“扩展选定区域”单选按钮。注:选中“扩展选定区域”单选按钮，则基本工资对应的其他列也随排序位置的变化而变化。（3）最后单击“排序”按钮，排序后人员编号的顺序发生变化。二、方法二：利用功能区中的“排序”命令排序案例2：利用“排序”命令对东方公司工资结算单按“基本工资”由低到高进行升序排序。操作步骤如下：（1）选择数据区域中的一个单元格，单击功能区中的“数据”—“排序和筛选”—“排序”命令，弹出“排序”对话框。（2）单击对话框中“主要关键字”下拉列表框的向下箭头，选取主要关键字“基本工资”，排序依据选择“数值”，次序选择“升序”。（3）单击“确定”按钮，按基本工资“升序”排序。任务3-2工资表数据分类汇总分类汇总是指对所有资料分类进行汇总，即将所有资料按同一类别放到一起，再进行同类数据的计算、统计等操作。Excel的分类汇总首先需对工作表进行排序，分类汇总时选择的分类项即为分类字段，其他需统计的字段称为选定汇总项，汇总方式可以是求和、求最大最小值、统计个数等。对东方公司工资结算单按“职工类别”为分类字段汇总显示实发工资的合计数。任务实施：操作步骤如下：（1）按“职工类别”对工作表进行排序。（2）选择数据清单内的任一单元格，单击功能区中的“数据”—“分级显示”—“分类汇总”，打开“分类汇总”对话框。（3）在“分类字段”下拉列表框中选择“职工类别”选项，在“汇总方式”下拉列表框中选择“求和”选项，在“选定汇总项”选择框中选中“实发工资”复选框。（4）单击“确定”按钮，分类汇总完成。（5）要取消分类汇总，再执行一次单击功能区中的“数据”—“分级显示”—“分类汇总”命令操作，在打开的“分类汇总”对话框中单击“全部删除”按钮即可。任务3-3工资表数据筛选财务数据往往是复杂的、繁多的，工作人员经常需要在密密麻麻的数据中找出一些符合条件的数据，有没有一种更加简便的方法呢?这就需要用到Excel的筛选功能。筛选功能可以使Excel工作表只显示符合条件的数据而隐藏其他数据，是一种查找数据的快速方法。任务实施：一、使用自动筛选功能查询姓名为“李明”的员工的工资情况操作步骤如下：（1）选择工资结算单数据清单内的任一单元格，单击功能区中的“数据”—“排序和筛选”—“筛选”按钮，进入筛选状态，在每个字段名称右侧出现一个下拉三角按钮。（2）单击字段名称为“员工姓名”列右侧的下拉三角按钮，出现筛选对话框，单击“文本筛选”选择框中的“全选”复选框，去掉全部对号，再重新选择“李明”复选框。（3）单击“确定”按钮。二、使用自动筛选功能查询所属部门为“采购部”的员工的工资情况操作步骤如下：（1）单击“所属部门”列右侧的下拉三角按钮，选择“文本筛选”右侧“等于(E)...”。（2）打开“自定义自动筛选方式”对话框，选择“所属部门”为“等于”、“采购部”。（3）单击“确定”按钮。任务3-4工资表数据统计分析任务实施：一、利用数据透视表按员工“所属部门”和“职工类别”计算“应发工资”的汇总数操作步骤如下：（1）选择工资结算单数据清单内的任一单元格，单击功能区中的“插入”—“数据透视表”命令，在弹出的“创建数据透视表”对话框中选择需要汇总的数据区域，选择放置透视表的位置为“新工作表”。（2）单击“确定”按钮，新建工作表Sheet1。（3）将右侧的“所属部门”、“职工类别”、“应发工资”分别拖至下方的“行标签”、“列标签”、“数值”处，定义值字段设置的数字格式为会计专用，小数位数2，货币符号无。即产生“应发工资”按“所属部门”和“职工类别”所生成的数据透视表。（4）将新建的工作表Sheet1更名为“工资总额透视表”，移到工资结算单后。二、在数据透视表基础上完成数据透视图操作步骤如下：（1）把光标定位在数据透视表内任一单元格，选择功能区中的“插入”—“图表”—“柱形图”按钮，在弹出的对话框中选择“簇状柱形图”。（2）单击“确定”按钮。在数据透视图的不同位置右击，出现快捷菜单，可对数据透视图的布局、位置、格式等进行调整。任务4编制工资费用分配表工资费用的分配是将公司本月承担的“三项经费”、“社会保险费”和“住房公积金”按照职工所在部门进行分配，编制工资费用分配表，为生成相关工资费用凭证提供数据。李悦设计本任务包括以下两部分：（1）设置工资费用分配表格式；（2）设置工资费用分配表公式。本任务用到的知识主要有IF函数、OR函数等。任务4-1设置“工资费用分配表”格式任务实施：操作步骤如下：（1）打开”工资管理2202.xlsx”工作簿，将“工资总额透视表”工作表后面插入新工作表，并更名为“工资费用分配表”。（2）选择单元格A2，右击，在快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，打开“边框”选项卡，单击按钮，即可对该单元格划斜线。打开“对齐”选项卡，在“文本对齐方式”选项区域，在“水平对齐”选项中，选中“靠左(缩进)”选项，在“垂直对齐”选项中，选中“靠上”选项，在“文本控制”选项区域，选中“自动换行”复选框。（3）在A2单元格内输入“分配项目部门”，单击编辑栏，在“分配项目部门”前面加空格，加到自动换行即可。（4）选中第2行至第10行，执行功能区中的“开始”—“单元格”—“格式”—“行高”。在“行高”对话框中，输入“20”，单击“确定”按钮。（5）选中A列，执行功能区中的“开始”—“单元格”—“格式”—“列宽”。在“列宽”对话框中，输入“15”单击“确定”按钮。选中B列至H列，设置列宽为“13”。（6）选中A1:H10，设置边框为所有框线。任务4-2设置“工资费用分配表”公式根据东方公司的资料，单位承担的“三项经费”占“应发工资”项目的比例分别是:职工福利费为14%、工会经费为2%，职工教育经费为8%；单位承担的社会保险费占“应发工资”项目的比例分别是:养老保