1、第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
2、.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 设置员工工资表的格式,为了使员工工资表更加规
3、范、美观，还需要对其格式进行相应的设置，具体操作步骤如下。 步骤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、。设置完毕后单击“确定”按钮。 步骤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、选项卡，在此设置文本对齐方式，如图5-9所示，再单 击“确定”按钮。 步骤9 返回工作表，此时会发现选中的单元格被添加了边框样式，效果如图5-10所示。 步骤10 选中E3:I14单元格区域，然后打开“设置单元格格式”对话框，在“数字”选项卡的“分类”列表框中单击“会计专用”选项，设置“小数位数”为2，如图5-11所示。,图5-8 添加边框,图5-10 查看添加边框后的效果,图5-9 设置水平和垂直居中对齐方式,图5-11 设置数字格式,步骤11 设置完成后单击“确定”按钮，选中单元格中的数据即保留两位小数，并以￥显示，如图5-12所示。 步骤12 若要隐藏工作表中的网格线，可在“视图”选项卡
6、的“显示”组中，取消选中“网格线”复选框，隐藏工作表中的网格线，效果如图5-13所示。, 5.2.2 计算员工销售提成奖金,图5-12 查看设置数字格式之后的效果,在“员工工资.xlsx”工作簿中把Sheet2工作表重命名为“提成”，然后在工作表中录入各员工的销售情况，包括员工的姓名，员工售出商品的商品编码、商品名称、成交总金额、提成比例等信息，如图5-14所示，下面根据提成比例，在“提成金额”列中计算出员工该得的提成金额，具体操作步骤如下。 步骤1 接着上面的操作，在“提成”工作表中选中F2单元格，输入公式“=D2*E2”，如图5-15所示。 步骤2 按Enter键计算出第一笔销售提成金额。
7、然后再次选中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
8、所示。,图5-18 获取其他员工奖金, 5.2.3 计算个税,只要工资超过一定的金额，就需要按一定的税率缴纳个人所得税。表5-1所示是如今正在采用的个人所得税纳税标准。,表5-1 所得税缴纳标准,注：起征点为3500元，应税所得应发合计起征点,图5-19 工资计算各种比率表,图5-20 插入“应发所得税”列,步骤3 选中H3单元格，输入公式“=IF(E3+F3+G380000,( H3-3500)*0.45-13505,0)”，按Enter键计算出第一位员工应扣个税金额，如图5-23所示。,步骤1 在“员工工资.xlsx”工作簿中创建“工资计算各种比率表”工作表，然后在该工作表中创建如图5-1
9、9所示的表格。 步骤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所
10、示。 步骤2 选中J3单元格，向下拖动填充柄，复制公式一直到J14单元格，计算出其他员工税后的工资，如图5-26所示。,图5-25 计算出第一位员工税后工资,图5-26 计算出其他员工税后工资, 5.3 制作员工工资单,工资单是发放工资时使用的一个清单，在发工资时，通常都需要将工资单一并发送到员工手中，这样员工可以一目了然地知道当月工资的发放情况。, 5.3.1 使用VLOOKUP函数法制作员工工资单,使用VLOOKUP函数可以在表格中查找指定的数值，但首先要制作在工资单中用到的各个数据表格。 员工的工资单是每位员工的工资情况，它是以单独的形式显示，每条工资记录中都包含了对应的工资项目。在制作
11、的工资表中，在表格顶端只有一行工资项目，只有通过制作工资单的方式，才能满足每条记录都包含工资项目。 步骤1 在工作簿中新建“工资单”工作表，然后在工作表创建“企业员工工资发放工资表”表格，接着选中A3单元格，在“公式”选项卡下的“函数库”组中单击“插入函数”按钮，如图5-27所示。 步骤2 弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择所需要的函数，在此选择VLOOKUP函数，再单击“确定”按钮，如图5-28所示。,图5-27 单击“插入函数”按钮,图5-28 选择VLOOKUP函数,步骤3 弹出“函数参数”对话框，在Lookup_valu
12、e文本框中输入要查找的值，在此输入“A3”；Table array文本框中输入“员工工资!$A$3:$J$14”；在Col_index num文本框中输入“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(
13、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)”。公式输入完成后，即可获取
14、员工“王小”的工资单信息，如图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 在工作簿中新建“OFF
15、SET制作工资单”工作表，然后选中A3单元格，打 开“插入函数”对话框，在“或选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择OFFSET函数，再单击“确定”按钮，如图5-35所示。 步骤2 弹出“函数参数”对话框，在Reference文本框中输入“员工工资!B4”；在Rows文本框中输入要移动的行数；在Cols文本框中输入要移动的列数，如图5-36所示。,步骤3 单击“确定”按钮，返回工作表，此时可以看到返回的结果为“1”，如图5-37所示。,图5-37 显示返回的结果,步骤4 在B3单元格中输入公式“=OFFSET(员工工资!C4,-1,-1)”；在C3单元格中输入公
16、式“=OFFSET(员工工资!D4,-1,-1)”；在D3单元格中输入公式“=OFFSET(员工工资!E4,-1,-1)”；在E3单元格中输入公式“=OFFSET(员工工资!F4,-1,-1)”；在F3单元格中输入公式“=OFFSET(员工工资!G4,-1,-1)”；在G3单元格中输入公式“=OFFSET(员工工资!H4,-1,-1)”；在H3单元格中输入公式“=OFFSET(员工工资!I4,-1,-1)”；在I3单元格中输入公式“=OFFSET(员工工资!J4,-1,-1)”；在J3单元格中输入公式“=OFFSET(员工工资!K4,-1,-1)”。公式输入完成后，可以看到显示第一位奇数行员工
17、的工资单，如图5-38所示。,步骤5 选中A2:J3单元格区域，向下拖动填充柄，复制公式一直到A14:J14单元格区域，获取奇数行的员工的工资单，如图5-39所示。,图5-38 显示第一位员工的工资单,图5-39 获取奇数行员工工资单,步骤6 选中A15单元格，输入公式“=OFFSET(员工工资!B5,-1,-1)”，按Enter键获取第一个偶数行的员工编号，如图5-40所示。,步骤7 选中A15单元格区域，向右拖动填充柄，复制公式一直到J15单元格，获取第一个偶数行员工工资单，如图5-41所示。 步骤8 选中A14:J15单元格区域，向下拖动填充柄，复制公式一直到A25:J25单元格区域，获
18、取其他偶数行员工工资单，最终员工工资单表如图5-42所示。,图5-40 获取第一个偶数行员工编号,图5-41 获取第一个偶数行的员工工资单,图5-42 成功使用OFFSET函数法制作员工工资单,图5-43 单击Visual Basic按钮,图5-44 Visual Basic编辑窗口, 5.3.3 使用VBA程序制作员工工资单,除了上面所介绍的方法外，还可以通过编写简单的VBA程序，实现工资单制作的自动化。具体操作步骤如下。 步骤1 在工作簿中新建“VBA”工作表，然后在“开发工具”选项卡下的“代码”组中单击Visual Basic按钮，如图5-43所示。 步骤2 激活Visual Basic
19、编辑窗口，在“工程-VBAProject”窗格中双 击“Sheet1(员工工资)”选项，如图5-44所示。,步骤3 弹出代码编辑窗口，输入如图5-45所示的代码。 步骤4 在菜单栏中选择“运行”|“运行子过程/用户窗体”命令，如图5-46所示。 步骤5 关闭编辑器，返回工作表，此时已经将员工工资转换成为员工工资单， 在“VBA”工作表中显示，如图5-47所示。,图5-45 代码编辑窗口,图5-46 选择“运行子过程/用户窗体”命令,图5-47 查看使用宏制作的员工工资单, 5.3.4 使用邮件合并制作员工工资单,Word邮件合并功能适合制作内容类似、结构相同的多个文档。使用邮件合并功能的两个必
20、备条件是主文档和数据源，主文档包含在每个文档中都出现的相同内容，数据源包含每个文档中的差异部分。,通过建立主文档与数据源的连接使这两部分数据可以联系在一起，然后将数据源中的不同字段插入主文档中的指定位置，最后完成数据的合并工作。 主文档需要在Word中进行编辑，而数据源常用的格式是Excel工作簿或Word表格，也可以是Access数据库或文本文件。如果使用Word表格作为数据源，那么需要确保Word表格外侧上方没有多余的标题，否则Word将无法正确识别数据源中的内容。 下面就来介绍如何利用邮件合并法制作员工工资单，具体操作步骤如下。 步骤1 打开Word文档，根据实际需要绘制一份工资单，如图
21、5-48所示。 步骤2 在“邮件”选项卡下的“开始邮件合并”组中单击“选择收件人”按钮，在展开的下拉列表中选择“使用现有列表”命令，如图5-49所示。,图5-48 绘制一份工资单,图5-49 选择“使用现有列表”命令,步骤3 弹出“选取数据源”对话框，找到并选中前面创建的“员工工资.xlsx”文件，单击“打开”按钮，如图5-50所示。 步骤4 弹出“选择表格”对话框，选择员工工资所在工作表，如图5-51所示，单 击“确定”按钮。 步骤5 将光标定位到工资单中需要插入数据的位置，然后在“邮件”选项卡下 的“编写和插入域”组中单击“插入合并域”按钮，在展开的下拉列表中选择“员工编号”命令，如图5-
22、52所示。,图5-51 选择员工工资所在工作表,步骤6 按照上面的步骤分别将数据源一项一项插入工资单相应的位置，效果如图5-53所示。,图5-50 选择数据源,图5-52 选择“员工编号”命令,图5-53 数据源全部插入相应位置,步骤7 在“邮件”选项卡下的“完成”组中单击“完成并合并”按钮，在展开的下拉列表中选择“编辑单个文档”命令，如图5-54所示。 步骤8 弹出“合并到新文档”对话框，选中“全部”单选按钮，再单击“确定”按钮，如图5-55所示。,图5-54 选择“编辑单个文档”命令,图5-55 “合并到新文档”对话框,步骤9 此时会显示第一位员工的工资单，如图5-56所示。 步骤10 如
23、果就这样进入打印操作，一页纸只能打印一条工资单信息，因此在表格下面插入一个空行，然后选中空行，在“插入”选项卡下的“文本”组中单击“文档部件”按钮，在展开的下拉列表中选择“域”命令，如图5-57所示。,图5-56 显示第一位员工的工资单,图5-57 选择“域”命令,步骤11 弹出“域”对话框，在“域名”列表框中选择“Next”域，如图5-58所示，单击“确定”按钮。 步骤12 选中整个表格，复制、粘贴创建多个表格，效果如图5-59所示。,步骤13 在“邮件”选项卡下的“完成”组中单击“完成并合并”按钮，并选择“编辑单个文档”命令，在打开的对话框中直接单击“确定”按钮，Word自动在一个新建文档
24、中显示多个员工工资单，如图5-60所示。,图5-58 “域”对话框,图5-59 复制表格,图5-60 成功利用邮件合并法制作员工工资单,图5-61 调整为合适大小,图5-62 选择页面方向, 5.3.5 预览打印员工工资单,在制作好工资单之后，还需要将其打印出来，并进行裁剪才可以获得各员工的工资单。在打印工资单之前，还需要对其进行页面设置。 步骤1 打开要打印的工作簿文件，切换到“VBA”工作表，在“页面布局”选项卡下的“调整为合适大小”组中，将“宽度”设置为“1页”，将“高度”设置为“自动”，如图5-61所示。 步骤2 打开“页面设置”对话框，在“页面”选项卡中选中“横向”单选按钮，如图5-
25、62所示。,步骤3 切换到“页边距”选项卡，在此设置表格的页边距值，并选中“水平”复选框，如图5-63所示。 步骤4 单击“打印预览”按钮，返回打印选项面板，如图5-64所示，可以看到设置页面后的打印效果。接着在“份数”文本框中输入1，单击“打印”按钮开始打印。,图5-63 设置页边距和居中方式,图5-64 单击“打印”按钮, 5.4 制作工资发放零钞备用表,当企业在以现金形式发放工资时，就需要准备大量的零钞，以便工资的正常发放。首先需要根据员工工资建立员工工资发放零钞备用表，并计算出各员工所需零钞数目以及各面值钞票所需数量。,图5-65 员工工资发放零钞备用表框架, 5.4.1 建立员工工资
26、发放零钞备用表,目前，人民币的面值包括100元、50元、20元、10元、5元等，本例中只计算到面值1角的零钞，所以将“分”面值的舍去。下面新建“工资发放零钞备用表.xlsx”工作簿，然后在Sheet1工作表中创建“工资发放零钞备用表”表格即可，如图5-65所示。, 5.4.2 关于各员工所需零钞数量的计算,可以根据各员工的工资额进行计算，将工资额按人民币的大小进行拆分，如面值为100元、50元、20元、10元等。下面就来介绍两种计算各员工所需零钞数量的方法，一种是使用QUOTIENT()函数计算，另一种是使用INT()函数计算。 1使用QUOTIENT()函数计算 QUOTIENT()函数是返
27、回商的整数分部，可以使用该函数来计算指定面值所需要的数量，具体操作步骤如下。,图5-66 单击“插入函数”按钮,步骤1 接着上面操作，选中C3单元格，然后在“公式”选项卡下的“函数库”组中单击“插入函数”按钮，如图5-66所示。 步骤2 弹出“插入函数”对话框，选择函数类别为“数学与三角函数”选项， 在“选择函数”列表框中选择所需要的函数，在此选择QUOTIENT函数，如图5-67所示，再单击“确定”按钮。,步骤3 弹出“函数参数”对话框，在Numerator文本框中输入“B3”，在Denominator文本框中输入“100”，如图5-68所示。 步骤4 单击“确定”按钮，返回工作表，此时可以
28、看到在目标单元格中显示了返回的值，即工资“285.99”中所需要100元面值的张数为28，如图5-69所示。 步骤5 选中D3单元格，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“数学与三角函数”选项，在“选择函数”列表中选择QUOTIENT函数，再单击“确定”按钮，如图5-70所示。 步骤6 弹出“函数参数”对话框，在Numerator文本框中输入“MOD(B3，100)”，在Denominator文本框中输入“50”，表示将B3单元格除以100后的余数，再除以50取整，如图5-71所示。,图5-67 选择QUOTIENT函数,图5-68 设置函数参数,步骤7 单击“确定”按钮，返
29、回工作表，此时可以看到在目标单元格中显示了计算的结果，如图5-72所示。,图5-69 显示返回的结果,图5-70 选择QUOTIENT函数,图5-71 设置函数参数,图5-72 显示返回的50元面值的张数,2使用INT()函数计算 INT()函数是将数字向下舍入到最接近的整数。通过该函数，同样可以计算各面值所需要的数量。在计算后面的面值时，需要与前面的值发生关系，即减去已经计算面值张数的相应金额。 步骤1 选中E3单元格，然后打开“插入函数”对话框，在“或选择类别”下拉列表框中选择“数学与三角函数”选项，在“选择函数”列表框中选择INT函数，再单击“确定”按钮，如图5-73所示。 步骤2 弹出
30、“函数参数”对话框，在Number文本框中输入需要进行计算的内容，在此输入“(B3-100*C3-50*D3)/20”，再单击“确定”按钮，如图5-74所示。 步骤3 此时返回工作表，可以看到在目标单元格中显示了计算的结果。即表示应发工资减去28张100元面值和0张50元面值后的余额，再除以20的取整结果，如图5-75所示。 步骤4 选中F3单元格，输入计算所需要10元面值张数的公式“=INT(B3-100*C3-50*D3- 20*E3)/10)”，按Enter键，即可看到计算的结果，如图5-76所示。,图5-73 选择INT函数,步骤5 在G3单元格中输入公式“=INT(B3-100*C3
31、-50*D3-20*E3-10*F3)/5)”，按Enter键得结果，如图5-77所示。 步骤6 在H3单元格中输入公式“=INT(B3-100*C3-50*D3-20*E3-10*F3-5*G3)/1)”，在I3单元格中输入公式“=INT(B3-100*C3-50*D3-20*E3-10*F3-5*G3-1*H3)/0.5)”，即可得到第一位员工工资发放所需要各面值人民币张数，如图5-78所示。,图5-74 设置函数参数,图5-75 显示返回的结果,图5-76 获取所需要10元面值的张数,步骤7 选中单元格区域C3:I3，向下拖动填充柄，复制公式一直到C14:I14单元格区域，即可得到各员工
32、工资发放所需要各面值人民币的张数，如图5-79所示。,图5-77 计算所需1元面值的张数,图5-78 第一位员工工资发放所需要各面值人民币张数,图5-79 各员工工资发放所需要各面值人民币的张数, 5.4.3 汇总各面值钞票所需的数量,当得到各员工工资发放所需要各面值钞票的数量之后，就可以对各面值钞票的数量进行汇总，得到各面值钞票所需要的总数量，以便做好员工工资发放的准备工作。 步骤1 在C16单元格中输入公式“SUM(C3:C14)”，如图5-80所示，按Enter键，获取100元面值的总张数。,步骤2 选中C16单元格，向右拖动填充柄，复制公式一直到I16单元格，即可得到各面值所需要的总张
33、数，如图5-81所示。,图5-80 输入公式获取100元面值所需总张数, 5.4.4 美化员工工资发放零钞备用表,至此所有的数据已经计算完毕。为了使工资发放零钞备用表更加美观，还需要对表格进行一些美化。下面将通过套用表格格式功能，为表格套用样式进行美化。具体操作步骤如下。 步骤1 在“开始”选项卡下的“样式”组中单击“套用表格格式”按钮，然后在展开的库中选择所需要的表样式，“表样式中等深浅12”选项，如图5-82所示。 步骤2 弹出“套用表样式”对话框，单击文本框右侧的 按钮，如图5-83所示。 步骤3 此时在工作表中选择数据源区域，在此选择单元格区域A2:I16，再按Enter键还原对话框，
34、如图5-84所示 步骤4 在“创建表”对话框中选中“表包含标题”复选框，再单击“确定”按钮。 步骤5 在“表格工具”下的“设计”选项卡中单击“工具”组中的“转换为区域”按钮，如图5-85所示。 步骤6 弹出Microsoft Excel提示对话框，在此单击“是”按钮即可，如图5-86所示。,图5-81 复制公式得到各面值所需总张数,步骤7 返回工作表，员工工资发放零钞备用表的最终效果如图5-87所示。,图5-82 选择所需要的表格样式,图5-83 设置表数据的来源,图5-84 选择数据源,图5-86 单击“是”按钮,图5-85 单击“转换为区域”按钮,图5-87 员工工资发放零钞备用表最终效果
35、, 5.5 全年工资统计分析,企业到了年底，财务会对这一年中所发的工资进行统计分析，本节就将详细介绍全年工资统计分析。, 5.5.1 制作全年工资汇总表,公司每个月都会给员工发放工资，财务每个月也会做统计，到了年底如何将112月的工资汇总在一张表格上面呢？下面就将介绍如何制作全年工资汇总表，具体操作步骤如下。 步骤1 新建“工资汇总表.xlsx”工作簿，并在工作簿中创建112月的工资表和“全年工资汇总”工作表，如图5-88所示。 步骤2 在“数据”选项卡下单击“获取外部数据”按钮，在展开的下拉列表中选择“现有连接”命令。 步骤3 弹出“现有连接”对话框，在对话框中单击“浏览更多”按钮，如图5-
36、89所示。,图5-88 工资汇总表,图5-89 单击“浏览更多”按钮,步骤4 弹出“选取数据源”对话框，选择所需的数据源，这里选择“工资汇总表”工作簿，如图5-90所示，再单击“打开”按钮。 步骤5 弹出“选择表格”对话框，如图5-91所示，选择所需要的表格，再单击“确 定”按钮。,步骤6 弹出“导入数据”对话框，在“请选择该数据在工作簿中的显示方式”中选择“数据透视表”单选按钮，在“数据的放置位置”中选择“现有工作表”单选按钮，在文本框中输入“全年工资汇总!$A$1”，如图5-92所示，然后单击“属性”按钮。 步骤7 弹出“连接属性”对话框，在“使用状况”选项卡中选中“打开文件时刷新数据”复
37、选框，如图5-93所示。 步骤8 切换到“定义”选项卡，在“命令文本”文本框中输入如图5-94所示的SQL语句。 步骤9 依次单击“确定”按钮，这时将会在工作表中创建空白数据透视表，然后在“数据透视表字段列表”窗格中对字段进行布局，最终得到如图5-95所示的效果。,图5-90 “选取数据源”对话框,图5-91 “选择表格”对话框,图5-92 “导入数据”对话框,图5-93 选中“打开文件时刷新数据”复选框,图5-94 输入SQL语句,图5-95 全年工资汇总表效果, 5.5.2 统计每个部门和岗位的工资分布,要更好地统计公司每个部门和岗位的工资分布情况，通常可以使用图表来实现这个要求。下面就来
38、创建部门和岗位的工资分布图，具体操作步骤如下。,图5-96 重新布局全年工资汇总表透视表,图5-97 选择“三维柱形图”选项,步骤1 在“数据透视表字段列表”窗格中将“姓名”字段拖动到“报表筛选”列表框中；将“部门”字段拖动到“列标签”列表框中；将“岗位”字段拖动到“行标签”列表框中；将“实发工资”字段拖动到“数值”列表框中，得到重新布局后的数据透视表，如图5-96所示。 步骤2 选中数据透视表内的任一单元格，然后在“插入”选项卡下的“图表”组中单击“柱形图”按钮，在展开的下拉列表中选择“三维簇状柱形图”选项，如图5-97所示。,步骤3 此时，创建出如图5-98所示的数据透视图。 步骤4 单击
39、“数据透视图工具”下的“布局”选项卡，在“标签”组中单击“图表标题”按钮，在展开的下拉列表中选择“图表上方”选项，如图5-99所示。 步骤5 设置图表标题为“部门和岗位的工资分布”，然后再对标题的文字进行格式设置，效果如图5-100所示。 步骤6 右击数值轴，从快捷菜单中选择“设置坐票轴格式”命令，然后在弹出的“设置坐标轴格式”对话框中单击“坐标轴选项”选项，接着在“坐标轴标签”下拉列表框中选择“高”选项，如图5-101所示。,图5-98 成功创建数据透视图,图5-99 选择“图表上方”选项,图5-100 设置图表标题,图5-101 设置数值坐标轴位置,步骤7 在“数字”选项的“类别”下拉列表
40、中，选择“会计专用”，如图5-102所示。 步骤8 单击“关闭”按钮，返回工作表，调整图表的大小与位置，部门和岗位的工资分布透视图效果如图5-103所示。,图5-102 设置数值坐标轴数字格式,图5-103 部门和岗位的工资分布图, 5.5.3 统计分析全年各月的工资变化,使用饼图可以非常清晰地表示出各月工资所占的比例，并能标注上各自所占百分比，统计分析出各月的工资变化。分析全年各月的工资变化的操作步骤如下。 步骤1 接上一节操作，在“数据透视表字段列表”窗格中对字段进行布局，得到如图5-104所示的数据透视表。 步骤2 选取工作表的A4:B17单元格区域，在“插入”选项卡下的“图表”组中单
41、击“饼图”按钮，在展开的下拉列表中选择“分离型三维饼图”选项，如图5-105所示。 步骤3 右击新创建的分离型三维饼图的标题，从弹出的快捷菜单中选择“删除”命令，删除图标标题，如图5-106所示。 步骤4 单击“数据透视图工具”标签下的“布局”选项卡，在“标签”组中单 击“图例”按钮，从展开的下拉列表中选择“无(关闭图例)”命令，如图5-107所示，取消图例的显。,图5-104 重新布局数据透视表,图5-105 选择“分离型三维饼图”选项,步骤6 右击图表区域，从快捷菜单中选择“设置图表区格式”命令，在弹出的“设置图表区格式”对话框中，选择“填充”选项，选择“无填充”单选按钮。 步骤7 在“设
42、置图表区格式”对话框中单击左侧窗格中的“边框颜色”选项，并在右侧窗格中选中“无线条”单选按钮，再单击“关闭”按钮。,图5-106 删除标题,图5-107 选择“无(关闭图例)”命令,步骤8 右击饼图的颜色块，从弹出的快捷菜单中选择“设置数据标签格式”命令，然后在弹出的“设置数据标签格式”对话框中，选择“标签选项”选项，如图5-108所示，选中“类别名称”、“百分比”、“显示引导线”复选框和“数据标签外”单选按钮设置为选中状态，而其余保持为非选中状态。 步骤9 经过以上操作之后，体现全年各月工资变化的饼图的最终效果如图5-109所示。,图5-108 设置标签选项,图5-109 全年各月工资变化的
43、饼图, 5.5.4 制作社会保险汇总表,在企业员工工资管理中，员工社会保险是政府通过立法强制实施，运用保险方式处理劳动者面临的特定社会风险，为其暂时或永久丧失劳动能力并失去劳动收入时，提供基本收入保险的法定保险制度。对于企业来说，一般为员工缴纳“三险一金”或“五险一金”。具体“五险”为养老保险、医疗保险、失业保险、生育保险和工伤保险，“一金”为住房公积金。,表5-2 社会保险及住房公积金金额比例,关于“五险”和“一金”。政府相关部门已经明确规定了单位与个人所承担的比例关系，如表5-2所示。,1计算员工的应扣养老保险金额 计算应扣养老保险金额的计算公式为：养老保险(基本工资+岗位工资)养老保险扣
44、缴比例。 步骤1 接上一节操作，在“工资汇总表.xlsx”工作簿中切换到“社会保险汇总”工作表，如图5-110所示。 步骤2 在E2单元格中输入公式“=ROUND(员工工资!E3+员工工资!F3)*社会保险及住房公积金比例!$C$4,2)”，按Enter键计算第一位员工应扣养老保险金额，如图5-111所 示。 步骤3 选中E2单元格，向下拖动填充柄，复制公式一直到E13单元格，获取其他员工应扣养老保险金额，如图5-112所示。,图5-110 “社会保险汇总”工作表,图5-112 获取其他员工应扣养老保险金额,2计算员工的应扣医疗保险金额 步骤1 接着上面操作，选中F2单元格，输入公式“=ROU
45、ND(员工工资!E3+员工工资!F3)*社会保险及住房公积金比例!$C$5,2)+10”按Enter键，计算出第一位员工应扣医疗保险金额(此处加10元是指大病统筹)。 步骤2 选中F2单元格，向下拖动填充柄，一直复制公式到F13单元格，获取其他员工应扣医疗保险金额，如图5-113所示。,图5-113 所有员工应扣医疗保险金额,3计算员工的应扣失业保险金额 计算应扣失业保险金额的公式为“失业保险(基本工资+岗位工资)失业保险扣缴比例”。 步骤1 选中G2单元格，输入公式“=ROUND(员工工资!E3+员工工资!F3)*社会保险及住房公积金比例!$C$6,2)”，按Enter键计算出第一位员工的应
46、扣失业保险金额。 步骤2 选中G2单元格，向下拖动填充柄，复制公式一直到G13单元格，获取其他员工应扣失业保险金额，如图5-114所示。,图5-114 所有员工应扣失业保险金额,4计算员工的应扣住房公积金金额 步骤1 接着上面操作，选中H2单元格，输入公式“=ROUND(员工工资!E3+员工工资!F3)*社会保险及住房公积金比例!$C$9,2)”，按Enter键，计算出第一位员工应扣住房公积金金额。 步骤2 选中H2单元格，向下拖动填充柄，复制公式一直到H13单元格，获取其他员工应扣住房公积金金额，如图5-115所示。,图5-115 所有员工应扣住房公积金金额, 5.5.5 制作每个员工的全年
47、个人所得税汇总表,在前面5.2.3节中已经介绍过如何计算个人所得税的方法，这里就不再重复介绍，利用之前所学到的知识，制作出112月员工详细的工资表(这里再不将介绍制作方法)，下面将利用数据透视表汇总每个员工全年的个人所得税。具体操作步骤如下。 步骤1 新建“工资明细.xlsx”工作簿，并制作出112月员工的详细工资表。,图5-116 “Excel选项”对话框,步骤2 新建“全年个人所得税”工作表，然后打开“Excel选项”对话框，在左侧列表中选择“自定义功能区”选项，然后在右侧窗格中单击“从下列位置选择命令”下拉列表框右侧的下三角按钮，选择“所有命令”选项，接着从下面的列表中选择“数据透视表和
48、数据透视图向导”选项，如图5-116所示，单击“添加”按钮。 步骤3 单击“确定”按钮，“数据透视表和数据透视图向导”就被添加到“新建选项卡”下，如图5-117所示，然后单击“数据透视表和数据透视图向导”按钮。,图5-117 “数据透视表和数据透视图向导”被添加到新建选项卡中,步骤4 弹出“数据透视表和数据透视图向导”对话框，在“请指定待分析数据的数据源类型”中选中“多重合并计算区域”单选按钮，在“所需要创建的报表类型”中选中“数据透视表”单选按钮，如图5-118所示。 步骤5 单击“下一步”按钮，在“请指定所需的页字段数目”下选中“自定义页字段”单选按钮，如图5-119所示。 步骤6 单击“
49、下一步”按钮，在“选定区域”文本框中输入“1月“!$E$1:$J$13”，单击“添加”按钮，此时在“所有区域”文本框中会显示刚刚添加的区域，如图5-120所示。,图5-118 建立数据透视表步骤1,步骤7 分别添加“2月!$E$1:$J$13”、“3月!$E$1:$J$13”、“4 月!$E$1:$J$13”、“5月!$E$1:$J$13”、“6月!$E$1:$J$13”、“7 月!$E$1:$J$13”、“8月!$E$1:$J$13”、“9月!$E$1:$J$13”、“10 月!$E$1:$J$13”、“11月!$E$1:$J$13”、“12月!$E$1:$J$13”区域，在“请先指定要建立在数据透视表中的页字段数目”中选中“0”单选按钮，如图5-121所示，然后单击“下一步”按钮。,图5-119 建立数据透视表步骤2,图5-121 建立数据透视表步骤2,图5-120 键入要汇总的数据区域,
