EXCEL在会计和财务管理中运用第5章

第5章经典实例：员工工资管理●5.1要点分析●5.2制作员工工资计表●5.2.1设置员工工资表的格式●5.2.2计算员工销售提成奖金●5.2.3计算个税●5.2.4计算员工税后工资●5.3制作员工工资单●5.3.1使用VLOOKUP函数法制作员工工资单●5.3.2使用OFFSET函数法制作员工工资单●5.3.3使用VBA程序制作员工工资单●5.3.4使用邮件合并制作员工工资单●5.3.5预览打印员工工资单●5.4制作工资发放零钞备用表●5.4.1建立员工工资发放零钞备用表●5.4.2关于各员工所需零钞数量的计算●5.4.3汇总各面值钞票所需的数量●5.4.4美化员工工资发放零钞备用表●5.5全年工资统计分析●5.5.1制作全年工资汇总表●5.5.2统计每个部门和岗位的工资分布●5.5.3统计分析全年各月的工资变化●5.5.4制作社会保险汇总表●5.5.5制作每个员工的全年个人所得税汇总表●5.5.6制作每个员工的全年工资明细表●5.2制作员工工资统计表制作员工工资统计表要求简洁、明了，方便最后的统计与分析。员工工资统计表一般包括“员工编号”、“姓名”、“所属部门”、“职位”、“基本工资”、“岗位工资”、“奖金”、“应扣个税”和“税后工资”等字段，如图5-1所示。●5.2.1设置员工工资表的格式为了使员工工资表更加规范、美观，还需要对其格式进行相应的设置，具体操作步骤如下。步骤1选中A1:I1单元格区域，然后打开“设置单元格格式”对话框，并切换到“对齐”选项卡，在此设置“水平对齐”和“垂直对齐”参数为“居中”，再选中“合并单元格”复选框，如图5-2所示。图5-1员工工资表初始效果●5.1要点分析步骤2切换到“字体”选项卡，设置字体为“楷体”，字形为“加粗”，字号为20，如图5-3所示。图5-2设置对齐方式图5-4设置填充色图5-3设置字体格式图5-5设置字段所占单元格格式步骤3切换到“填充”选项卡，在“背景色”选项组中选择一种颜色，这里单击“橙色”图标，如图5-4所示。设置完毕后单击“确定”按钮。步骤4使用类似的方法设置A2:H2单元格区域，效果如图5-5所示。步骤5在员工工资表中输入相关信息，如图5-6所示。步骤6选择要输入员工编号的单元格区域A3:A14单元格区域，输入公式“=ROW()-2”，输入公式之后，按下Ctrl+Enter组合键，将公式输入到选区中的每一个单元格中，并得到计算结果，如图5-7所示。图5-6输入相关信息图5-7利用公式计算出员工编号步骤7选中A1:I14单元格区域，然后打开“设置单元格格式”对话框，接着在“边框”选项卡中选择线条样式，并单击“外边框”和“内部”按钮，如图5-8所示。步骤8切换到“对齐”选项卡，在此设置文本对齐方式，如图5-9所示，再单击“确定”按钮。步骤9返回工作表，此时会发现选中的单元格被添加了边框样式，效果如图5-10所示。步骤10选中E3:I14单元格区域，然后打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中单击“会计专用”选项，设置“小数位数”为2，如图5-11所示。图5-8添加边框图5-10查看添加边框后的效果图5-9设置水平和垂直居中对齐方式图5-11设置数字格式步骤11设置完成后单击“确定”按钮，选中单元格中的数据即保留两位小数，并以￥显示，如图5-12所示。步骤12若要隐藏工作表中的网格线，可在“视图”选项卡的“显示”组中，取消选中“网格线”复选框，隐藏工作表中的网格线，效果如图5-13所示。●5.2.2计算员工销售提成奖金图5-12查看设置数字格式之后的效果在“员工工资.xlsx”工作簿中把Sheet2工作表重命名为“提成”，然后在工作表中录入各员工的销售情况，包括员工的姓名，员工售出商品的商品编码、商品名称、成交总金额、提成比例等信息，如图5-14所示，下面根据提成比例，在“提成金额”列中计算出员工该得的提成金额，具体操作步骤如下。步骤1接着上面的操作，在“提成”工作表中选中F2单元格，输入公式“=D2*E2”，如图5-15所示。步骤2按Enter键计算出第一笔销售提成金额。然后再次选中F2单元格，向下拖动填充柄，复制公式一直到F29单元格，计算出其他销售提成金额，如图5-16所示。步骤3切换到“员工工资”工作表，选中G3单元格，输入公式“=SUMIF(提成!$A$2:$A$29,B3,提成!$F$2:$F$29)”，如图5-17所示。接着按下Ctrl+Shift+Enter组合键确认复合公式，计算出第一位员工的奖金。图5-13隐藏网格线图5-14某公司销售登记表图5-15输入公式图5-16计算销售提成金额图5-17计算出第一位员工的奖金步骤4选中G3单元格，向下拖动填充柄，复制公式一直到G14单元格，计算出其他员工的奖金，如图5-18所示。图5-18获取其他员工奖金●5.2.3计算个税只要工资超过一定的金额，就需要按一定的税率缴纳个人所得税。表5-1所示是如今正在采用的个人所得税纳税标准。级数全月应纳税所得额税率速扣数/元1不超过1500元的部分3.00%02超过1500元～4500元的部分5.00%1053超过4500元～9000元的部分20.00%5554超过9000元～35000元的部分25.00%10055超过35000元～55000元的部分30.00%27556超过55000元～80000元的部分35.00%55057超过800000元的部分45.00%13505表5-1所得税缴纳标准注：起征点为3500元，应税所得＝应发合计－起征点图5-19工资计算各种比率表图5-20插入“应发所得税”列步骤3选中H3单元格，输入公式“=IF(E3+F3+G31500,0,E3+F3+G3)”，按Enter键计算出第一位员工当月应税所得额，如图5-21所示。步骤4再次选中H3单元格，向下拖动填充柄，复制公式一直到H14单元格，获取其他员工当月应发工资，如图5-22所示。步骤5选中I3单元格，输入公式“=IF(H3-3500=0,0,IF(H3-3500=1500,(H3-3500)*0.03,IF(H3-3500=4500,(H3-3500)*0.1-105,IF(H3-3500=9000,(H3-3500)*0.2-555,IF(H3-3500=35000,(H3-3500)*0.25-1005,IF(H3-3500=55000,(H3-3500)*0.3-2755,IF(H3-3500=80000,(H3-3500)*0.35-5505,IF(H3-350080000,(H3-3500)*0.45-13505,0))))))))”，按Enter键计算出第一位员工应扣个税金额，如图5-23所示。步骤1在“员工工资.xlsx”工作簿中创建“工资计算各种比率表”工作表，然后在该工作表中创建如图5-19所示的表格。步骤2切换到“员工工资”工作表，然后插入“应税所得额”列，如图5-20所示。图5-21计算出第一位员工当月应税所额图5-23计算第一位员工应扣个税金额图5-22获取其他员工当月应税所得额图5-24计算出所有员工应扣个税金额步骤6选中I3单元格，向下拖动填充柄，复制公式一直到I14单元格区域，获取其他员工应扣个税金额，如图5-24所示。●5.2.4计算员工税后工资应扣个税金额计算出来后，下面就要计算员工税后的工资了，具体操作步骤如下。步骤1选中J3单元格，输入公式“=E3+F3+G3-I3”，按Enter键计算出第一位员工税后的工资，如图5-25所示。步骤2选中J3单元格，向下拖动填充柄，复制公式一直到J14单元格，计算出其他员工税后的工资，如图5-26所示。图5-25计算出第一位员工税后工资图5-26计算出其他员工税后工资●5.3制作员工工资单工资单是发放工资时使用的一个清单，在发工资时，通常都需要将工资单一并发送到员工手中，这样员工可以一目了然地知道当月工资的发放情况。●5.3.1使用VLOOKUP函数法制作员工工资单使用VLOOKUP函数可以在表格中查找指定的数值，但首先要制作在工资单中用到的各个数据表格。员工的工资单是每位员工的工资情况，它是以单独的形式显示，每条工资记录中都包含了对应的工资项目。在制作的工资表中，在表格顶端只有一行工资项目，只有通过制作工资单的方式，才能满足每条记录都包含工资项目。步骤1在工作簿中新建“工资单”工作表，然后在工作表创建“企业员工工资发放工资表”表格，接着选中A3单元格，在“公式”选项卡下的“函数库”组中单击“插入函数”按钮，如图5-27所示。步骤2弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择所需要的函数，在此选择VLOOKUP函数，再单击“确定”按钮，如图5-28所示。图5-27单击“插入函数”按钮图5-28选择VLOOKUP函数步骤3弹出“函数参数”对话框，在Lookup_value文本框中输入要查找的值，在此输入“A3”；Tablearray文本框中输入“员工工资!$A$3:$J$14”；在Col_indexnum文本框中输入“2”，再单击“确定”按钮，如图5-29所示。步骤4返回工作表，此时可以获取编号为“1”的姓名，如图5-30所示。图5-29设置VLOOKUP函数参数图5-30获取编号“1”对应的员工姓名步骤5在C3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,3)”，如图5-31所示。步骤6按Enter键获取编号“1”对应的员工所属的部门，如图5-32所示。步骤7在D3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,4)”；在E3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,5)”；在F3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,6)”；在G3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,7)”；在H3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,8)”；在I3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,9)”；在J3单元格中输入公式“=VLOOKUP(A3,员工工资!$A$3:$J$14,10)”。公式输入完成后，即可获取员工“王小”的工资单信息，如图5-33所示。步骤8选中A2:J3单元格区域，向下拖动填充柄，一直复制A25:J25单元格区域，获取其他员工的工资单，如图5-34所示。图5-31在C3单元格中输入公式图5-32获取编号“1”对应的员工所属的部门图5-33获取第一位员工的工资单图5-34获取其他员工的工资单图5-35选择OFFSET函数图5-36“函数参数”对话框●5.3.2使用OFFSET函数法制作员工工资单OFFSET函数是以指定的引用为参考系，通过确定偏移量得到新的引用。下面就介绍使用OFFSET函数制作员工工资单的具体步骤。步骤1在工作簿中新建“OFFSET制作工资单”工作表，然后选中A3单元格，打开“插入函数”对话框，在“或选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择OFFSET函数，再单击“确定”按钮，如图5-35所示。步骤2弹出“函数参数”对话框，在Reference文本框中输入“员工工资!B4”；在Rows文本框中输入要移动的行数；在Cols文本框中输入要移动的列数，如图5-36所示。步骤3单击“确定”按钮，返回工作表，此时可以看到返回的结果为“1”，如图5-37所示。图5-37显示返回的结果步骤4在B3单元格中输入公式“=OFFSET(员工工资!C4,-1,-1)”；在C3单元格中输入公式“=OFFSET(员工工资!D4,-1,-1)”；在D3单元格中输入公式“=OFFSET(员工工资!E4,-1,-1)”；在E3单元格中输入公式“=OFFSET(员工工资!F4,-1,-1)”；在F3单元格中输入公式“=OFFSET(员工工资!G4,-1,-1)”；