《Excel在财务会计中的应用（第2版）》项目四 工资管理课件

《Excel在财务会计中的应用》项目四

工资管理《Excel在财务会计中的应用》《Excel在财务会计中的应用》模块二工资管理项目情境

完成了基于Excel账务处理模块的电算化工作之后，李悦深刻体会到Excel电算化的快速和高效，同时他的工作也得到了领导们的一致认可。但李悦并不满足，为了随时了解职工工资情况，及时对工资数据进行汇总分析，便于公司进行薪酬控制与管理，李悦开始尝试用Excel设计东方公司的工资管理系统。

他首先整理出与工资管理模块相关的部门设置情况、员工基本情况及公司岗位设置情况等信息，具体如下：

东方公司现设8个部门，包括：行政部、财务部、技术部、采购部、销售部、生产一车间、生产二车间、生产三车间。职工类别6类，包括：总经理、部门经理、管理人员、采购人员、销售人员、生产人员。员工人数25名。《Excel在财务会计中的应用》模块二工资管理知识链接

新个人所得税法于2019年1月1日起全面实施，主要变化有：

（1）建立对综合所得税按年计税的制度此次改革，将工资薪金、劳务报酬、稿酬和特许权使用费4项劳务性所得纳入综合征税范围，在年计税的基础上，实行“代扣代缴、自行申报、汇算清缴、多退少补”的征管制度。（2）适当提高基本减除费用标准将综合所得的基本减除费用从原来的3500元/月提高至5000元/月（每年6万元）。（3）首次设立专项附加扣除在提高基本减除费用的基础上增加子女教育、继续教育、大病医疗、住房贷款利息或者住房租金、赡养老人等支出，进一步增强税制的公平性。（4）调整优化个人所得税税率结构以改革前工薪所得3%-45%七级超额累进税率为基础，扩大3%、10%、20%三档低税率的级距，缩小25%税率的级距，30%、35%、45%三档较高税率级距不变。《Excel在财务会计中的应用》模块二工资管理【项目分析】12345录入员工基础工资数据编制工资结算单工资数据的查询与统计分析编制工资费用分配表打印工资发放条《Excel在财务会计中的应用》模块二工资管理项目目标能力目标学会使用Excel设置工资项目公式的能力学会使用Excel筛选功能进行工资数据查询的能力学会使用Excel数据透视表进行工资数据分析的能力素质目标培养学生全面严谨的态度培养学生廉洁自律的立场知识目标了解工资核算的业务处理流程掌握工资项目的组成及计算方法熟练掌握IF函数、OR函数等函数的使用方法

《Excel在财务会计中的应用》模块二工资管理任务4编制工资费用分配表2.1设置工资结算单格式2.2设置工资项目3.1工资表数据排序3.2工资表数据分类汇总3.3工资表数据筛选3.4工资表数据统计分析4.1设置“工资费用分配表”格式4.2设置“工资费用分配表”公式任务3工资数据的查询与统计分析任务2编制工资结算单任务1录入员工基础工资数据1.1建立基本工资信息表1.2建立专项附加扣除表任务5打印工资条5.1利用排序功能生成工资条后打印5.2页面设置后打印工资条《Excel在财务会计中的应用》模块二工资管理建立基本工资信息表建立专项附加扣除表任务1录入员工基础工资数据《Excel在财务会计中的应用》模块二工资管理任务1.1建立基本工资信息表一、定义基本工资信息表格（1）打开Excel2010，在D盘“东方公司”文件夹下建立“1905”文件夹。在“1905”文件夹下新建名为“工资管理.xlsx”的工作簿。（2）双击工作表标签Sheet1，输入一个新的工作表表名“基本工资信息表”。（3）选择A1单元格，输入工作表标题“东方公司员工基本工资表”。（4）选中单元格A1:E1，设置字体为“宋体”，字号为“12”，加粗。选择对齐方式为“跨列居中”。（5）在A2至E2单元格内分别录入“员工编号”、“员工姓名”、“所属部门”、“职工类别”、“基本工资”项目，设置其字体为“宋体”，字号为“12”，加粗，黄色底纹。设置A至E列的对齐方式为“居中”。（6）设置“员工编号”所在A列的格式为“文本”类型，设置“基本工资”所在E列的格式为“会计专用”类型，小数位数为2，货币符号无。《Excel在财务会计中的应用》模块二工资管理二、定义单元格的有效性1.对“所属部门”所在列添加有效性控制（1）选择C列，单击功能区中的“数据”—“数据工具”—“数据有效性”按钮，打开“数据有效性”对话框。（2）在“设置”选项卡中的“允许”下拉列表框中选择“序列”选项；在“来源”框中输入“行政部，财务部，技术部，采购部，销售部，生产一车间，生产二车间，生产三车间”。（3）最后单击“确定”按钮，则完成对“所属部门”数据有效性的设置，如图4—3所示。

2.对“职工类别”所在列添加有效性控制同理，设置D列的数据有效性为序列“总经理,部门经理,管理人员,采购人员,销售人员,生产人员”。图4—3设置“所属部门”数据有效性任务1.1建立基本工资信息表《Excel在财务会计中的应用》模块二工资管理三、录入基本工资信息表方法一：直接输入法分别录入“员工编号”、“员工姓名”、“所属部门”、“职工类别”、“基本工资”项目基本数据。方法二：记录单输入法（1）将鼠标指针移动到将要输入新数据的单元格，然后单击“记录单”功能按钮，打开“记录单”对话框（2）在“记录单”对话框的左侧列出了数据库结构的内容，其中每一项内容对应一个文本框，用户只需要在文本框中输入数据的内容即可。（3）每输入一条记录后单击“新建”按钮即可接着输入下一条记录，单击“关闭”按钮即可完成记录的输入。（4）如果用户对输入的数据进行修改，可以先选定数据表中的任意单元格，再单击“记录单”功能按钮，通过单击“上一条”或“下一条”按钮，或者拖动滚动条来进行记录定位。选定要修改的记录后，对其进行修改。（5）如果需要删除某条记录，在选定某条记录后，单击“记录单”对话框中的“删除”按钮即可。（6）完成东方公司所有员工基本工资信息录入工作后，关闭“记录单”对话框。任务1.1建立基本工资信息表《Excel在财务会计中的应用》模块二工资管理四、美化工作表本案例中，工作表标题及列标题的美化(字体、字号、底纹等操作)在录入数据的过程中已经完成，表格边框及列宽和行高设置需要在全部数据录入完成后进行。操作步骤如下：（1）选择单元格区域A2:E27，设置网格线为所有框线。（2）调整A:E列至合适的列宽。同理，根据表4—2定义“岗位工资标准”,根据表4—3定义“绩效工资标准”,操作方法与“基本工资信息表”类同。任务1.1建立基本工资信息表《Excel在财务会计中的应用》模块二工资管理基本工资信息表(最终效果)《Excel在财务会计中的应用》模块二工资管理设置工资结算单格式设置工资项目任务2编制工资结算单《Excel在财务会计中的应用》任务2.1设置工资结算单格式

12345打开“工资管理.xlsx”工作簿，双击工作表标签Sheet3，将Sheet3命名为“工资结算单”。打开“基本工资信息表”工作表，选中单元格A2:E2，按组合键“Ctrl+C”进行复制操作。打开“工资结算单”工作表，选中单元格A1，按组合键“Ctrl+V”进行粘贴，将内容全部复制至“工资结算单”工作表中。选中单元格F1:Q1，依次输入工资项目名称:“岗位工资”、“绩效工资”等项目使用格式刷，复制单元格A1:E1的格式到单元格F1:Q1、选择H至Q列，设置格式为“会计专用”选中单元格A2:Q26，单击功能区中的“开始”—“字体”—“边框”—“所有框线”按钮。设置网格线模块二工资管理《Excel在财务会计中的应用》任务2.2设置工资项目操作步骤一、设置“岗位工资”项目二、设置“绩效工资”项目三、设置“应发工资”项目四、设置“扣社保费”项目五、设置“扣公积金”项目六、设置“专项附加扣除”项目七、设置5月“累计应纳税所得额”项目八、设置“5月累计预缴纳个税”项目九、设置“4月累计应纳税所得额”项目十、设置“4月累计预缴纳个税”项目十一、设置“扣个税”项目十二、设置“实发工资”项目模块二工资管理《Excel在财务会计中的应用》工资结算单(最终效果)模块二工资管理《Excel在财务会计中的应用》1.AND功能：返回逻辑值。所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法：=AND(logical1，logical2，……)2.OR函数功能：返回逻辑值。在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。语法：=OR(logical1，logical2，……）3.NOT函数

功能：返回逻辑值。用于对参数值求反，当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE。语法：=NOT(logical）拓展逻辑函数模块二工资管理《Excel在财务会计中的应用》ROUND函数功能：按照指定的小数位数进行四舍五入计算。语法：=ROUND(number,num_digits)5.ROUNDDOWNROUNDDOWN函数是一个数学与三角函数。功能：按照指定的小数位数向下舍入。语法：=ROUNDDOWN(number,num_digits)6.ROUNDUP函数ROUNDUP函数是一个数学与三角函数。功能：按照指定的小数位数向上舍入。语法：=ROUNDUP(number,num_digits)模块二工资管理拓展数学与三角函数《Excel在财务会计中的应用》工资表数据分类汇总工资表数据排序

工资表数据筛选工资表数据统计分析模块二工资管理任务3工资数据的查询与统计分析《Excel在财务会计中的应用》任务3.1工资表数据排序解1解2方法一利用功能区中的“升序”按钮/“降序”按钮排序。案例1：利用“降序”按钮

对东方公司工资结算单按“基本工资”由高到低进行降序排列。方法二利用功能区中的“排序”命令排序案例2：利用“排序”命令对东方公司工资结算单按“基本工资”由低到高进行升序排序。模块二工资管理《Excel在财务会计中的应用》3步骤2按“职工类别”对工作表进行排序1选择数据清单内的任一单元格，单击功能区中的“数据”—“分级显示”—“分类汇总”打开“分类汇总”对话框。在“分类字段”下拉列表框中选择“职工类别”选项，在“汇总方式”下拉列表框中选择“求和”选项，在“选定汇总项”选择框中选中“实发工资”复选框单击“确定”按钮，分类汇总完成，要取消分类汇总，再执行一次单击功能区中的“数据”—“分级显示”—“分类汇总”命令操作，在打开的“分类汇总”对话框中单击“全部删除”按钮即可任务3.2工资表数据分类汇总要求：对东方公司工资结算单按“职工类别”为分类字段汇总显示实发工资的合计数。

模块二工资管理《Excel在财务会计中的应用》“分类汇总”结果（最终效果）模块二工资管理《Excel在财务会计中的应用》任务3.3工资表数据筛选一、使用自动筛选功能查询姓名为“李明”的员工的工资情况操作步骤如下：（1）选择工资结算单数据清单内的任一单元格，单击功能区中的“数据”—“排序和筛选”—“筛选”按钮，进入筛选状态，在每个字段名称右侧出现一个下拉三角按钮，（2）单击字段名称为“员工姓名”列右侧的下拉三角按钮，出现筛选对话框，单击“文本筛选”选择框中的“全选”复选框，去掉全部对号，再重新选择“李明”复选框（3）单击“确定”按钮,完成筛选二、使用自动筛选功能查询所属部门为“采购部”的员工的工资情况操作步骤如下：（1）单击“所属部门”列右侧的下拉三角按钮，选择“文本筛选”右侧“等于(E)...”（2）打开“自定义自动筛选方式”对话框，选择“所属部门”为“等于”、“采购部”（3）单击“确定”按钮，完成筛选模块二工资管理《Excel在财务会计中的应用》任务3.4工资表数据统计分析一、利用数据透视表按员工“所属部门”和“职工类别”计算“应发工资”的汇总数操作步骤如下：（1）选择工资结算单数据清单内的任一单元格，单击功能区中的“插入”—“数据透视表”命令，在弹出的“创建数据透视表”对话框中选择需要汇总的数据区域，选择放置透视表的位置为“新工作表”（2）单击“确定”按钮，新建工作表Sheet1。（3）将右侧的“所属部门”、“职工类别”、“应发工资”分别拖至下方的“行标签”、“列标签”、“数值”处，定义值字段设置的数字格式为会计专用，小数位数2，货币符号无。即产生“应发工资”按“所属部门”和“职工类别”所生成的数据透视表。（4）将新建的工作表Sheet1更名为“工资总额透视表”，移到工资结算单后。

二、在数据透视表基础上完成数据透视图操作步骤如下：（1）把光标定位在数据透视表内任一单元格，选择功能区中的“插入”—“图表”—“柱形图”按钮，在弹出的对话框中选择“簇状柱形图”。（2）单击“确定”按钮,完成数据透视图。（3）在数据透视图的不同位置右击，出现快捷菜单，可对数据透视图的布局、位置、格式等进行调整。模块二工资管理《Excel在财务会计中的应用》工资总额数据透视图（最终效果）模块二工资管理《Excel在财务会计中的应用》模块二工资管理设置“工资费用分配表”格式设置“工资费用分配表”公式任务4编制工资费用分配表《Excel在财务会计中的应用》任务4.1设置“工资费用分配表”格式1234选择单元格A2，右击，在快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，打开“边框”选项卡，单击按钮，即可对该单元格划斜线。打开“工资管理.xlsx”工作簿，将“工资总额透视表”工作表后面插入新工作表，并更名为“工资费用分配表”在A2单元格内输入“分配项目部门”，单击编辑栏，在“分配项目部门”前面加空格，加到自动换行即可选中第2行至第10行，执行功能区中的“开始”—“单元格”—“格式”—“行高”。在“行高”对话框中，输入“20”，单击“确定”按钮。。56选中A列，执行功能区中的“开始”—“单元格”—“格式”—“列宽”。在“列宽”对话框中，输入“15”单击“确定”按钮。选中B列至H列，设置列宽为“13”。选中A1:H10，设置边框为所有框线。模块二工资管理《Excel在财务会计中的应用》任务4.2设置“工资费用分配表”公式

操作步骤（1）定义“工资总额”项目计算公式（2）定义“职工福利费”项目计算公式（3）定义“工会经费”项目计算公式（4）定义“职工教育经费”项目计算公式（8）定义“合计”行计算公式（7）定义“合计”列计算公式（6）定义“住房公积金”项目计算公式（5）定义“社会保险费”项目计算公式模块二工资管理《Excel在财务会计中的应用》工资费用分配表（最终效果）模块二工资管理《Excel在财务会计中的应用》利用排序功能生成工资条后打印页面设置后打印工资条模块二工资管理任务5打印工资条《Excel在财务会计中的应用》任务5.1利