[Excel 也可以很好玩：职场故事版]第9章 每月的期盼_发薪日

菜鸟的 Excel智慧职场路 192 第 9章 每月的期盼：发薪日 对于喜欢追逐新潮的年轻人，每月的工资都用在吃大餐、买靓衫、朋友聚会上，于是 形成了典型的“月光族”。月光族的口号是：“吃光用光，身体健康，挣多少花多少。”只 要吃得开心，穿得漂亮，想花就花，根本不在乎钱财。可是花光了后该怎么办？于是，公 司每月的发薪日就成了大家翘首企盼的日子。 知道大家对发工资的急切期盼心情，因此，人资部主管张新总是在每月初就会把考勤、 业绩考核、 工资表 工资表下载工资表格下载工资表 下载最全工资表下载社保工资表下载 的制作这几件事排在最重要的位置。 早上上班李璐就被张新叫到办公室，张新说：“李璐，马上又要到发薪日了，咱们部 门的工资专员朱芳有事请假了，你来做一下工资表。” 李璐：“好，但我原来没做过，不知道做得好不。” 张新：“我这里有上个月的工资表文件，一会儿发给你，基本上就在这张表上进行修 改就可以，主要是考勤扣款这部分每月都有变化，然后是将社保和个税的代扣款计算准确 就可以了。你 Excel用得很熟练，应该很快就可以做好的。” 李璐：“那行，我做好后再给你审核。” 张新：“要尽量快点哟，工资表做好后，还要交给总经理审核签字，如果老总出差就 签不到字。所以我们尽量提前几天把工资表做好。” 9.1 重新建立工资表 李璐打开张新发过来的工资表文件，看了一下里面的数据，基本上都是手工输入的， 只有几个汇总数据是通过 SUM函数计算的。这种工资表做起来确实费时费力，每个人的 项目都要逐个去计算。李璐想，Excel就拥有强大的查表和计算功能，应该可以很快就完 成工资的计算。只是要做这样一套工资表格，开始需要花些时间。做吧，一来当作使用 Excel 练练手，另外，做好后也可方便同事以后的工资计算。 李璐决定先用部分后勤管理人员的工资数据来制作这一套工资表格。 9.1.1 搭建工资表框架 李璐首先将张新发来的工资表结构复制了一份，粘贴到一个新的 Excel工作簿中，作 为工资表的框架，并将该工作簿保存为“2月工资表.xlsx”，如图 9-1所示。 第 9章 每月的期盼：发薪日 193 图 9- 1 工资框架 从图 9-1所示的工资表框架可以看到，公司后勤管理人员的工资主要由基本工资、职 务工资、工龄工资、绩效这几部分组成，其中基本工资一般都不会变化（除了调资之外）， 而职务工资会随员工任职的变化而变化，而工龄工资随着员工在公司上班的时间变化，绩 效是根据每月的绩效考核来核发。这几部分内容都需要参照其他表格中的数据来生成，不 用手工输入。 再看扣款部分，考勤扣款根据考勤统计的数据计算得出，代扣社保按国家法规以工资 的一定比例扣除，代扣个税按税率扣除。这些扣款都需要通过计算得到。 9.1.2 员工基本情况表 根据公司的规定，员工基本工资各不相同，在入职时会定一个基数，然后会不定时的 进行调整（包括上调和下调），基本工资记入员工的档案，另外员工的职务、工龄这些信 息也保存在员工档案内。为了方便计算，李璐将部分员工的基本情况复制出来，删除不需 要的列，得到如图 9-2所示的表格。 图 9- 2 员工资料 菜鸟的 Excel智慧职场路 194 9“员工资料”这份表格，就可以将这里的数据引入到工资表中，这样就不会在工资 表中漏掉某位同事了。李璐按以下步骤开始创建公式引入这些数据。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，删除 A3:A15单元格区域中原 来的数据。 （2）单击选择 A3单元格输入以下公式： =[人资数据.xlsx]员工资料!A2 以上公式引用“人资数据.xlsx”工作簿中的“员工资料”工作表中的数据，注意单元 格的引用为相对引用，方便后面公式的复制。 （3）向右拖动 A3单元格的填充柄到 C3单元格，得到如图 9-3所示的数据。 （4）确保如图 9-3所示，选中了 A3:C3单元格区域，向下拖动填充柄，得到如图 9-4 所示的填充数据。从图中可看到后面有一行数据全为 0， 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 在“员工资料”表中已没有 人员信息了，删除最后一行为 0的数据即可。 图 9- 3 向右复制公式 图 9- 4 向下复制公式 9.1.3 计算工龄工资 根据公司的薪酬制度规定，员工每年工龄有 100元工龄工资。如图 9-2所示的“员工 资料”表中保存着员工的工龄，从该表中获取工资后再乘以 100即可得到工龄工资。 对于这种从一个表中查询获取数据的操作称为查表，李璐正好学习了 Excel的查表函 数。Excel的查表函数主要有以下几个： � LOOKUP函数 � VLOOKUP函数 � HLOOKUP函数 在可以使用 VLOOKUP函数搜索某个单元格区域的第一列，然后返回该区域相同行上 任何单元格中的值。正好，查询员工的工龄就是以员工的姓名为基准，找到姓名后，再在 同一行的第 4列就可找到工龄。VLOOKUP函数的使用方法如下： VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 第 9章 每月的期盼：发薪日 195 � lookup_value：要在表格或区域的第一列中搜索的值。 � table_array：包含数据的单元格区域。 � col_index_num：从 table_array参数中返回的匹配值的列号。col_index_num参数为 1 时，返回 table_array第一列中的值；col_index_num为 2时，返回 table_array第二列 中的值，依此类推。 � range_lookup：设为 TRUE或为 FALSE，用来控制返回精确匹配值或近似匹配值。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 E3单元格输入以下 公式： =VLOOKUP(A3,[人资数据.xlsx]员工资料!$A$2:$D$13,4)*100 表示在“人资数据.xlsx”的“员工资料”工作表中查找当前工作表（“2月工资表.xlsx” 中的“Sheet1”工作表）的 A3单元格中的值，找到后返回“员工资料！$A$2:$D$13”的 第 4列（即工龄），再将返回的工龄乘以 100，即可得到工龄工资。 （2）向下拖动 E3单元格的填充柄到 E14单元格，即可计算出全部的工龄工资，如图 9-5所示。 图 9- 5 计算工龄工资 9.1.4 计算职务工资 根据员工的职务不同，每月工资中将发放不等的职务工资。而员工的职务会随着工作 业绩进行调整，因此职务工资应该也是变化的。在“人资数据.xlsx”工作簿中创建如图 9-6 所示的“职务工资”工作表保存不同职务的职务工资，这样，也可通过 VLOOKUP函数查 表计算出每位员工的职务工资。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 D3单元格输入以下 公式： =VLOOKUP(B3,[人资数据.xlsx]职务工资!$A$2:$B$6,2,FALSE) （2）向下拖动 D3单元格的填充柄到 D14单元格，即可计算出全部的职务工资，如 菜鸟的 Excel智慧职场路 196 图 9-7所示。 图 9- 6 职务工资 图 9- 7 计算职务工资 9.1.5 计算绩效 对于每月绩效的计算，也需要通过查表的方式来完成。首先查看一下绩效考核的情况， 如图 9-8所示。在该表中，每位员工有一个绩效奖金的定额，还有一个考核得分，评分按 5分制进行，当得分为 4分时，计发 100%的绩效奖金，评分为 5分时，计发 5/4倍的绩效 奖金，类似地，如果得 3分，计发 3/4倍的绩效奖金。 图 9- 8 绩效考核数据 根据以上的规则，通过查询“绩效考核.xlsx”工作簿中的“Sheet1”工作表即可计算 出相应的绩效工资。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 F3单元格输入以下 公式： =VLOOKUP(A3,[绩效考核.xlsx]Sheet1!$A$3:$C$14,2,FALSE)* VLOOKUP(A3,[绩效考核.xlsx]Sheet1!$A$3:$C$14,3,FALSE)/4 第 9章 每月的期盼：发薪日 197 以上公式中，通过两次查表来获取数据：第一次查表获取对应员工的绩效奖金定额， 第二次查询获取得分。将这两个数相乘后除以 4即可得到实际应发的绩效工资。 （2）向下拖动 F3单元格的填充柄到 F14单元格，即可计算出全部的职务工资，如图 9-9所示。 图 9- 9 计算绩效工资 9.1.6 计算应发小计 通过以上步骤将各项应发工资都已填入到工资表中，接着定义应发小计。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 H3单元格。 （2）在功能区“开始”选项卡的“编辑”组中，单击自动求和按钮 ，Excel 自动使用 SUM函数对 C3:G3单元格区域进行求和，生成的公式如下： =SUM(C3:G3) （3）向下拖动 H3单元格的填充柄到 H14单元格，即可计算出全部的应发小计，如 图 9-10所示。 图 9- 10 计算应发小计 菜鸟的 Excel智慧职场路 198 9.2 代扣款的计算 公司在发放工资时，有一些法定的代扣款，如社保、个税，另外公司内部管理中也会 有一些扣款项目，如缺勤扣款等。对于这些扣款项目，也是有规则的，李璐也决定用公式 进行自动计算。 9.2.1 计算考勤扣款 根据公司的考勤 管理制度 档案管理制度下载食品安全管理制度下载三类维修管理制度下载财务管理制度免费下载安全设施管理制度下载 规定，对于迟到、早退，每次扣款 30元，而对于旷工则是 旷一罚三，即旷工一天要扣除三天的工资。 对于考勤扣款的计算，也需要查询考勤统计表中每个员工的缺勤情况，然后再根据规 则计算扣款金额。打开“2月考勤数据.xlsx”工作簿，查看“考勤统计表”工作表如图 9-11 所示。这里显示的考勤统计表是经过修正的，根据考勤异常登记表中登录的情况对一些旷 工、迟到、早退情况进行了调整。 图 9- 11 考勤统计表 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 I3单元格，输入以下 公式。 =(VLOOKUP(A3,[2月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,24,FALSE)+ VLOOKUP(A3,[2月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,25,FALSE))*30+ VLOOKUP(A3,[2月考勤数据.xlsx]考勤统计表!$A$2:$Z$13,26,FALSE)*H3/22*3=SUM(C3:G3) 在以上公式中，第一个 VLOOKUP函数查询迟到次数，第二个 VLOOKUP函数查询 早退次数，将这两个数据相加乘以 30，即得到迟到、早退的扣款。第三个 VLOOKUP函 数查询旷工天数，然后乘以 H3/22*3，表示按月应发小计除以 22天，得到每天的工资数， 乘以 3是旷一罚三的作用。 （2）向下拖动 I3单元格的填充柄到 I14单元格，即可计算出全部的考勤扣款情况， 如图 9-12所示。 第 9章 每月的期盼：发薪日 199 图 9- 12 计算考勤扣款 从图 9-12可看出，在考勤扣款中有些小数部分的位数保留了 4位，这应该是显示格式 的设置问题，在后面进行统一设置。 9.2.2 代扣三险一金 根据相关法律法规的规定，企业为员工购买社会保险的同时，员工个人也支付相应比 例。这些款项统一由企业缴存，因此，应在工资表中代扣个人应缴部分。 个人需缴纳的社会保险费由三部分：养老保险、失业保险和医疗保险。其中养老保险 个人缴纳工资基数的 8%，失业保险为 1%，医疗保险为 2%。 三险一金中的“一金”是指住房公积金，住房公积金不是强制性的。不过，作为福利 的一部分，HG公司为员工缴存了住房公积金，按基本工资额的 6%缴存。 因此，三险一金个人扣款总额为基本工资的 17%。有了这个比例，就可以方便地定义 公式计算出代扣金额了。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 J3单元格，输入以下 公式。 =C3*17% （2）向下拖动 J33单元格的填充柄到 J14单元格，即可计算出全部的考勤扣款情况， 如图 9-13所示。 菜鸟的 Excel智慧职场路 200 图 9- 13 计算代扣社保 9.3 代扣税款的计算 前面也提到过，在员工工资中代扣个人所得税是企业的法定义务。因此，在制作工资 表时需要计算这项代扣款。因为个人所得税的计算比较复杂，所以李璐将其放在最后来做。 为此，李璐还专门上网查了一下个人所得税的相关内容。 9.3.1 个人所得税怎么算 对于个人所得税的计算，李璐还没搞明白。反正自己现在的收入还达不到缴纳个人所 得税的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 ，因此也不怎么关注。 但是，现在要做工资表，就需要把计算方法弄明白，才不会给同事算错。 1．个人所得税税率表 李璐知道月工资收入超过 3500元就需要缴纳个人所得税，但具体怎么计算还不知道。 于是，李璐百度了一下个人所得税税率表，发现从 2011年 9月 1开始实行了新的 7级超 额累进个人所得税税率表。个税起征点为 3500元。 李璐在“人资数据.xlsx”工作簿中新建名为“个税税率表”的工作表，将网上找到的 这个表复制到 Excel表格中，方便计算时查表。为了 Excel计算方便，还将原来的级数 1~7 改为每档的最高金额，改造后的个人所得税税率表如图 9-14所示。 第 9章 每月的期盼：发薪日 201 图 9- 14 个税税率表 2．个人所得税的计算方法 李璐又从网上查看个人所得税的计算方法，计算应缴纳个税的公式为： 应缴纳的个税＝[(应发工资 － 三险一金)－3500]×税率－速算扣除数 以上公式中的三险一金是指个人承担的养老、医疗、失业、公积金，不包括公司承担 部分。 有了以上公式，再根据图 9-14所示的税率表，就可以计算员工的个人所得税了。例如， 某员工本月应发工资为 8200，其三险一金个人承担部分为 910，则计算其个税的步骤如如 下： （1）计算出其“应纳税所得额”，也就是应发工资减三险一金，再减 3500，即： 8200 – 910 – 3500 = 3790 （2）根据 3790这个应纳税所得额，该数小于 4500，在图 9-14所示的税率表中查找， 得知其税率为 10%，速算扣除数为 105。根据上面的公式，可按下式计算出个税金额。 （8200 – 910 – 3500）* 10% - 105 = 274 9.3.2 Excel 计算个人所得税 手工计算个人所得税的方法李璐已经学会了，但是，这种手工计算方法怎么在 Excel 中定义公式呢？ 1．用 VLOOKUP函数查表 李璐首先想到的是用 VLOOKUP函数进行查找，于是在税率表中创建公式进行测试。 （1）在 F5单元格输入一个金额 5000，用来测试查找的税率是否准确。 （2）在 G5单元格中输入以下公式： =VLOOKUP(F5,A4:D10,3) （3）这时在 G5单元格可看到公式计算结果为 0.1，即查找到的税率为 10%。可是， 这和左边表中的数据是不相符的（5000元的纳税额，税率应该为 20%，位于 10%的下一 行），如图 9-15所示。再输入其他的数，如 3000进行测试，得到的为 0.03，正确的税率应 该是下一行的 10%。 菜鸟的 Excel智慧职场路 202 图 9- 15 用 VLOOKUP函数查税率表（错误结果） 为什么会出现这种情况呢？李璐经过查找资料发现，VLOOKUP函数的第 4个参数若 为 TRUE或省略时，该函数在查找时，若没有完全一致的数据与之匹配，就会找到小于它 的最大数。如图 9-15中的 5000，在 A4:A10区域中没有，则会找到小于它的最大值 4500， 然后返回该行第 3列的数。 怎么办呢？李璐在思考：VLOOKUP会找到小于它的最大数，每次从表中找到的税率 都位于正确值的上面的一档，那就将“级数”中的金额都向下移一行不就 OK了？ 于是李璐将 A3:A9单元格区域的数据向下移了一行，这时在 F5单元格输入 18000， G5显示税率为 0.25（即 25%），如图 9-16所示，OK。 图 9- 16 修改后 VLOOKUP查税率表（正确结果） 从税率表中查找到税率，再用相同的方法查找到速算扣除数，个人所得税的计算就简 单了。 可是，用 VLOOKUP函数查表的方法要改变原来的税率表，感觉不是太好。如果某天 将正常的税率表复制过来，则计算的结果就会出错。李璐觉得还是该另找方法。 2．用数组公式计算 在李璐遇到有难题时，总是该马波上场，这次也不例外，李璐又把马波呼叫出来了。 马波：“计算个税的公式啊？首先，你看个税税率表的数据是一个二维表，其实对于 这种二维表的情况，可以考虑用数组公式。数组公式一般人都不会，不过好在你已经懂一 些了。” 李璐：“怎么定义数组公式？” 第 9章 每月的期盼：发薪日 203 马波：“这就要用到一个新函数：FREQUENCY。” FREQUENCY函数可以计算数值在某个区域内的出现频率，然后返回一个垂直数组。 例如，有以下公式： =FREQUENCY(3000,{1500;4500;9000;35000;55000;80000}) 这个公式的输出结果是多少呢？ 李璐马上在一个新工作表的 A1单元格中输入这个公式，结果为 0，如图 9-17所示。 图 9- 17 FREQUENCY函数运算结果 马波：“不对，FREQUENCY函数的返回值是一个数组，因此，你必须选择 A1:A7这 7个单元格，然后定义成数组公式，才能得到正确的结果。如果不定义为数组公式，就只 能得到结果数组中第 1个元素的值。” 于是李璐将 A1:A7单元格区域选中，然后在编辑栏显示的公式中单击鼠标，按组合键 ++，得到如图 9-18所示的结果。 图 9- 18 数组公式运算结果 马波：“从图 9-18可看出，FREQUENCY函数将 3000与后面的数组进行频率归并， 找到 3000属于 1500~4500这个区间，于是在结果数组的第 2个元素中加 1，而其他各区间 并没有数据，所以都为 0。” 李璐：“计算得到这个数组有什么用啊？” 马波：“还没看出来啊，结果数组中第 2元素为 1，正好对应个税表中第二档，这样就 可以找到这一档的税率，可以用以下公式计算。” =FREQUENCY(3000,{1500;4500;9000;35000;55000;80000})*{0.03;0.1;0.2;0.25;0.3;0.35;0.45} 同样定义为数组公式，得到的结果如图 9-19所示。 从图 9-19的计算结果可看到，只有 A2单元格中有一个值为 0.1，表示找出了 3000元 的税率为 10%。 菜鸟的 Excel智慧职场路 204 图 9- 19 计算出税率 李璐：“哦，这样的啊，通过数组计算，最后得到税率，接着就是用 3000去乘以这个 数组，求得未扣除速算数的税款？” 马波：“对，用以下公式就可以了。” =3000 * FREQUENCY(3000,{1500;4500;9000;35000;55000;80000})* {0.03;0.1;0.2;0.25;0.3;0.35;0.45} 以上公还是要定义为数组公式，得到的结果如图 9-20所示。 图 9- 20 计算出税款（含速算扣除数） 李璐：“现在算出了 300，怎么减速算扣除数呢？” 马波：“仍然用数组公式，具体公式如下。” =3000 * FREQUENCY(3000,{1500;4500;9000;35000;55000;80000})* {0.03;0.1;0.2;0.25;0.3;0.35;0.45} – {0;105;555;1005;2755;5505;13505} 同样需要将公式定义为数组公式，计算的结果如图 9-21所示。 图 9- 21 应缴纳税款 李璐：“这一步计算结果有很多数，怎么区分哪个是需要的呢？” 第 9章 每月的期盼：发薪日 205 马波：“ 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 一下计算的结果，发现什么特征没有？我们要的数肯定是 195。” 李璐：“其他数要么为 0，要么为负，我们要的数是最大数。” 马波：“对，这时我们再使用一个求最大数的函数MAX，就齐活了。完整的计算公式 如下。” =MAX(3000 * FREQUENCY(3000,{1500;4500;9000;35000;55000;80000})* {0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;105;555;1005;2755;5505;13505}) 将这个公式定义在 B2单元格，还是要定义为数组公式，运算结果如图 9-22所示。 图 9- 22 最终的公式 李璐：“哦，妈呀，这个公式整这么复杂。” 马波：“其实公式并不复杂，我逐步演示给你看，方便你理解。如果一开始就把这个 公式给你，估计你是不容易理解。” 李璐：“也是，这样逐步推进，理解起来是要方便些。那我在实际应用中只需要将那 些数据替换成单元格区域就可以了吧。” 马波：“对，这个就你自己去整吧，如果弄出来了，说明你把这个公式理解了，否则， 就还要再把前面的操作看一下。” 李璐把“人资数据.xlsx”和“2月工资表.xlsx”两个工作簿打开，开始定义个人所得 税的计算公式。 （1）在“2月工资表”工作簿的“Sheet1”工作表中，单击选择 K3单元格，输入以 下公式。 =IF((H3-[人资数据 .xlsx]个税税率表 !$C$1-I3-J3)<=0,"",MAX((H3-[人资数据 .xlsx]个税税率 表!$C$1-I3-J3)*FREQUENCY((H3-[人资数据.xlsx]个税税率表!$C$1-I3-J3),[人资数据.xlsx]个税税 率 表 !$A$4:$A$9)*[ 人 资 数 据 .xlsx] 个 税 税 率 表 !$C$4:$C$10-[ 人 资 数 据 .xlsx] 个 税 税 率 表!$D$4:$D$10)) =C3*17% 输入以上公式后，按++组合键完成数组公式的定义。 （2）向下拖动 K3单元格的填充柄到 K14单元格，即可计算出全部的代扣个税情况， 如图 9-23所示。 菜鸟的 Excel智慧职场路 206 图 9- 23 计算代扣个税 9.3.3 多发 1 毛少领 5000 元 通过计算个人所得税，李璐感觉自己又学到许多知识，不仅是对 Excel数组公式的应 用有了更进一步的了解，对个人所得税的知识也掌握了不少。 这时李璐想起了年前被媒体炒得火热的年终奖个人所得税问题，于是决定再深入了解 一下。通过网络查询，李璐知道了年终奖个人所得税的计算方法如下： 根据国家税务局的规定，明确年终奖是全年的业绩考核，因此会根据年终奖金额除以 12后的金额来查找税率。年终奖的计税与当月工资仍然有关系，如果当月工资低，未达到 3500的标准，可以在年终奖里继续抵扣。 计算年终奖个人所得税的方法，就是先用年终奖金额除以 12后的值查找税率，然后 用年终奖金额乘以税率，再减去税率所在档次的速算扣除数即可。 例如：12月工资 2500，年终奖 30000，应缴纳多少个人所得税？ 计算步骤如下： （1）因为 12月工资不足 3500（差 1000），因此，应从年终奖中减少 1000，即年终奖 计税额为 29000。 （2）用 29000/12=2416.67，查税率表，未超 4500，属于第一档，税率为 3%，速算扣 除数为 0。 （3）计算应缴税金：29000*3%=870。 可是怎么会出现多发年终奖，反而少领钱的问题呢？ 原来是出在税率表中档次之间的分界点处。为了彻底弄清这个问题，李璐在 Excel中 做了一个表，如图 9-24所示。 在此表中，李璐专门找了一个临界点 4500来计算，当年终奖为 54000元时，分档金 额为 54000/12=4500元，从税率表查看可知道，4500元这一档的税率为 10%，速算扣除数 为 105，因此应缴税款为 54000*10%-105=5295元。 而当年终奖为 54000.10元时，分档金额为 54000.10/12=4500.01元，超过了 4500元， 则税率应为 20%，速算扣除数为 555，因此应缴税款为 54000.10*20%-555=10245.02元。 第 9章 每月的期盼：发薪日 207 图 9- 24 年终奖个税比较 原来如此，知道这个原因以后，做年终奖分配时就要注意避开这些分界点的金额，让 同事能领到更多的年终奖。 9.4 切断公式引用 通过边学边做的过程，李璐终于把工资表中最复杂的部分弄完了，剩下的都是些简单 工作了。还需要定义一些简单的汇总计算公式，设置一下格式，工资表就算完成了。 9.4.1 完善工资表的公式 李璐收回思绪，开始完善工资表中的公式。扣款项目已经计算完成，还需要定义一个 扣款小计，将所有扣款列累加起来就行。然后还要需要定义实公式计算发工资，在最后一 行中定义一个合计公式，计算工资的合计金额。 （1）单击选择M3单元格，输入以下公式： =SUM(I3:L3) （2）向下拖动M3单元格的填充柄到M14单元格。 （3）单击选择 N3单元格，输入以下公式： = H3-M3 （4）向下拖动 N3单元格的填充柄到 N14单元格。 （5）单击选择 A15单元格，输入“合计”。 （6）单击选择 C15单元格，输入以下公式： =SUM(C3:C14) （7）向右拖动 C15单元格的填充柄到 N15单元格。 定义完所有公式后，得到的工资表如图 9-25所示。 菜鸟的 Excel智慧职场路 208 图 9- 25 工资表 9.4.2 自动更新的工资表 在李璐制作这份工资表时，其中大部分单元格的数据都是通过引用其他工作簿、工作 表中的数据，或定义公式计算得出。对于这种引用其他工作簿、工作表数据的表格，如果 其他工作簿、工作表中的数据发生变化时，当前工资表中的数据也会随之变化。 因此，这些工作簿就构成了一套工资系统，下个月只需要把业绩考核、考勤数据更新 到对应的工作簿中，工资表就自动生成了。如果某位同事的基本工资、职务、工龄等信息 有变化，只需要更新“人资数据.xlsx”工作簿中的“员工资料”工作表中的数据，工资表 中的对应数据也会自动变化。 OK，这次做这份工资表格虽然花了几个小时的时间，不过，以后每个月就可节省时 间了，只需要把参照的这些工作簿修改好，再打开工资表工作簿更新数据就搞定，几分钟 的事！ 默认情况下，当打开工资表工作簿时，会自动更新这些修改后的数据。也可在功能区 “数据”选项卡的“链接”组中，单击“编辑链接”命令按钮，打开如图 9-26所示的“编 辑链接”对话框，在该对话框中列出了当前工作簿引用的其他工作簿，选中某个工作簿之 后，单击右侧的“更新值”即可更新当前工作簿中的内容。 图 9- 26 编辑链接 第 9章 每月的期盼：发薪日 209 单击图 9-26所示对话框左下角的“启动提示”按钮，打开如图 9-27所示的“启动提 示”对话框，在该对话框中可设置打开工作簿时是否显示更新链接的提示。 图 9- 27 启动提示 9.4.3 不再需要链接 通过 Excel的更新链接可以很方便、快速地制作出工资表，但是，当一份工资表制作 完成后，是不希望其中的数据再发生变化。这时，就需要断开链接，可是，下个月做工资 表时又希望能快速生成工资表。更新、不更新是一对矛盾。 一个解决方案就是将生成的工资表复制一份，并以值的方法粘贴到另一个工作表中。 这样，就可将当月工资数据固化。具体操作步骤如下： （1）在“Sheet1”工作表中选中所有数据，将这些数据复制到剪贴板。 （2）切换到“Sheet2”工作表，修改其标签为“2月工资”，单击选择 A1单元格。 （3）在功能区“开始”选项卡的“剪贴板”组中，单击“粘贴”按钮下方的下拉箭 头，从下拉菜命令列表中选择“选择性粘贴”命令，打开如图 9-28所示的“选择性粘贴” 对话框。 图 9- 28 选择性粘贴 （4）单击选择“数值”单选按钮，单击“确定”按钮完成粘贴，如图 9-29所示。这 时的工资表中无任何公式，都是最终的数据。当修改其他工作簿或工作表中的数据时，不 会对该表造成任何数据影响。 菜鸟的 Excel智慧职场路 210 图 9- 29 无公式的工资表 9.4.4 工资表也需要美化 工资表是要交给总经理签字的，因此，也应将其做得美观大方。 首先，对于人民币来说，最多只能显示到分，这是最小单位，而现在工资表中的数据 有的显示了小数点后 4位，因此需要进行调整。 （1）李璐拖动鼠标选择 C3:N15这片显示数字的区单，单击右键，从弹出的快捷菜单 中选择“设置单元格格式”命令，打开如图 9-30所示对话框。 图 9- 30 设置单元格格式 （2）在“数字”选项卡的“分类”列表中单击“数值”，然后在右侧设置显示 2位小 数，并勾选“使用千位分隔符”复选框，单击“确定”按钮，所有数字都显示 2位小数。 对于表格的格式化，李璐已经非常熟练了，不一会就把基本格式做出来，通过打印预 览得到如图 9-31所示的工资表效果。 第 9章 每月的期盼：发薪日 211 图 9- 31 打印出的工资表 9.5 自动生成工资条 李璐将工资表打印出来交给张新审核签字。 张新拿着工资表说：“不错，这么快就做出来了。这份表就先放我这里，我拿去给财 务部和总经理签字。” 李璐：“好，那我就交差了哟，老大。” 张新：“还没完哟，工资表你是打印出来了，发给每个员工的工资条呢？” 李璐：“哦，还要做工资条。行，我马上去做。” 张新：“别急，还要给你说个事。这个工资表的电子文件你要设置一个密码，不要让 其他人员查看到里面的数据。” 李璐：“好，打工资条，设密码。我记住了。” 9.5.1 生成工资条 要生成工资条，要求每次输出一行表头，接着输出一个员工的工资数据，然后空一行 再重复前面的过程。 要做一个生成工资条的公式需要使用到以下几个函数： � ROW：返回单元格的行号。 � COLUMN：返回单元格的列号。 � MOD：返回两数相除的余数。 � OFFSET：以指定的引用为参照系，通过给定偏移量得到新的引用。 以上函数中，通过 OFFSET函数在工资表中指定一个参照单元格，然后偏移一定的距 离，以得到需要的工资数据。 定义的工资条公式如下： =IF(MOD(ROW(),3),OFFSET('2月工资'!$A$2,(MOD(ROW()-1,3)>0)* ROUND(ROW()/3,),COLUMN(A1)-1),"") 菜鸟的 Excel智慧职场路 212 在定义公式时需要考虑三种情况： （1）第一行显示工资表的表头。 （2）第二行显示一位员工的工资数据。 （3）显示一个空行。 在公式中可通过函数MOD(ROW(),3)来得到三种状态，分别为 1、2、0，当计算的结 果为 1或 2时输出数据，当结果为 0时输出空行。 公式中的(MOD(ROW()-1,3)>0)* ROUND(ROW()/3,)这一部分计算出 OFFSET函数的 行偏移量，(MOD(ROW()-1,3)>0)的计算结果有三种状态，分别是：FALSE、TRUE、TRUE， 当其值为 FALSE时，不管 ROUND(ROW()/3,)的计算结果为多少，整个算式的结果都为 0， 这样就可输出工资表的表头（行偏移为 0）。而当(MOD(ROW()-1,3)>0)的结果为 TRUE时， 将根据 ROUND(ROW()/3,)的计算结果输出一行数据，一个空行。 生成工资表的具体操作如下： （1）在“工资条”工作表中选中 A1单元格，输入上面定义的公式。 （2）向右拖动 A1单元格的填充柄到 N1单元格。 （3）确保 A1:N1单元格区域处于被选择的状态，向下拖动填充柄到第 35行，即可得 到如图 9-32所示的工资条 图 9- 32 工资条 9.5.2 工资表的保密 张新专门交待了要给工资表进行保密设置，应该是不允许别人随便打开工资表，以免 工资数据外泄。这就需要设置工作簿的打开权限。 （1）在功能区单击“文件”打开 Excel的后台视图，如图 9-33所示。 第 9章 每月的期盼：发薪日 213 图 9- 33 Excel后台视图 （2）在后台视图中单击“保护工作簿”按钮，将弹出一个命令列表。 （3）选择“用密码进行加密码”命令，弹出如图 9-34所示的“加密文档”对话框， 在“密码”框中输入密码。 （4）单击“确定”按钮，将弹出如图 9-35所示的“确认密码”对话框，再次输入密 码。 图 9- 34 加密文档 图 9- 35 确认密码 （5）单击“确定”按钮，完成加密设置。这时后台视图可看到如图 9-36所示的提示， 表示该文档需要密码才能打开。 （6）保存工作簿后退出 Excel。重新打开该文档时，将出现如图 9-37所示的对话框， 要求用户输入密码，若密码输入错误，将弹出如图 9-38所示的错误提示。 菜鸟的 Excel智慧职场路 214 图 9- 36 Excel后台视图 图 9- 37 打开文档要求输入密码 图 9- 38 密码错误提示