手把手教你做好工资表下

　　我们在上一篇《手把手教你做好工资表（中）》中解决了自动生成应发工资表问题，并留下了最后一个问题，即在员工存在异动的情况下，如何让工资表智能地抓取＂发工资当期＂员工的最新数据的问题，我们在这篇文章中做一个详细的说明。
　　员工异动信息表的设计与维护
　　此处我们需要先回顾一个知识点：在前文中提到我们在管理员工信息时，需要维护三个数据表：员工个人信息表、员工企业信息表、员工异动信息表。我们再来看一下这三个表的作用：
　　员工个人信息表：记录员工的学历、地址、联系方式等信息，在我们进行工资表计算过程中除了有特殊补贴（如学历补贴等）之外，一般用不到此表。
　　員工企业信息表：记录了员工刚入职时的部门、职务、薪酬、入职时间等状况，后期我们把离职时间也补充在该表内，它更像是原始数据，是静态的。我们用它来筛选解决＂自动生成工资表名单＂的问题。
　　员工异动信息表：持续记录员工每一次的异动信息，包括转正、晋升、调岗、调薪、降职等等动作带来的变化，我们需要在其中找到距离当期最近一次的员工数据来计算工资才是准确的。比如一个员工2月入职，5月转正，7月调岗，我们计算8月的工资表时，就必须抓取到该员工7月的数据；而我们做6月份工资表的时候，就必须使用5月份的数据。在此有些同行可能会感到奇怪，身处8月份我只需要做7月的工资表，6月份的工资表已经在上个月结束了，我为什么还要关心过去的工资表呢？在这里我说明一下：智能工资表是可以生成（包括还原）任意一个月的应发工资基本表的，既可正向也可逆向，这就意味着它具备强大的模拟功能，只有这样才能具备支持企业人力成本预算的可能性。
　　做好员工异动信息表的设计和维护
　　员工异动信息表字段和顺序应该与员工企业信息表一致，并在末尾增加＂异动性质＂和＂异动时间＂两个字段（图1）。
　　员工的每一次异动应单独记录，不得删掉或在之前的异动信息基础上修改，录入数据时必须填写异动性质和异动时间。
　　员工的异动性质一般包括转正、晋升、调岗、调薪、降职等，可根据自身企业状况进行设定。
　　在录入员工异动信息表时，只需填写异动后该员工的最新数据信息（比如新的部门、职级、工资、补贴等信息），同时务必将员工的工号填写正确，否则将严重影响到后面的计算。
　　自动判断抓取数据的计算过程
　　实际工作中，应发工资基础表、员工企业信息表、员工异动信息表这三个表应该在三个Sheet中建立，但是为了便于本内容的讲解，我把这三个表集中在了一个页面上进行讲解（图2），以便于读者了解其中的逻辑关系，希望大家在明白它们之间的关联后，根据自己的具体表格情况进行跨页应用。
　　大家可以看到，我们在上一篇文章中解决了应发工资名单自动生成的问题，现在我们需要解决上图中红色线框中抓取数据的问题。在这个例子中我们可以看到这样一个具体的情况：
　　员工：何晓亮 工号：S004
　　1.于2017年5月2日入职，任初级经理，基本工资3500元
　　2.于2017年6月1日提前转正，任中级经理，基本工资4000元
　　3.于2017年8月1日获得晋升，任高级经理，基本工资5000元
　　目前需制作2017年8月的工资表，计算步骤如下：
　　第一步，给员工企业信息表及异动表区域命名（利用名称管理器手动起名功能，如图3，图4所示的操作步骤）。
　　B4单元格：取值ID
　　目的和意义：将员工工号引用至B4，使三个表格的取值ID位置相同
　　计算公式：B4=F4，向下复制单元格。
　　F19单元格：
　　目的和意义：控制工资表计算的时间，只填写每月1日的具体时间
　　计算公式：无。手动录入具体时间，如需计算7月，则填入：2017-7-1。
　　D13单元格：有效判断
　　目的和意义：在工资表计算时间之前产生的异动才是对工资计算有效的数据，利用公式将其筛选出来，符合条件的值为1，否则为0。
　　计算公式：
　　D13=（N13<=$F$19）*（F13<>0），向下复制公式。
　　C13单元格：取值判断
　　目的和意义：在工资表计算时间之前可能产生多次异动，需要找出最近的一次异动数据，它需要满足三个条件：（1）同一个员工；（2）异动时间满足计算时间条件；（3）离当期最近的一次数据记录。符合条件的值为1，否则为0。
　　计算公式：
　　C13={IF（F13=＂＂，＂＂，--（N13=MAX（（$G$13：$G$17=G13）*（$D$13：$D$17=1）*$N$13：$N$17）））}，此处为数组公式，写完后同时按住shift+ctr再回车，公式外侧即可自行出现{ }符号。向下复制公式。
　　B13单元格：取值ID
　　目的和意义：将员工的工号与C列的判断结果组合起来，形成每个员工唯一的取值ID，以便于在最后一步中工资基础表使用VLOOKUP抓取数据。
　　计算公式：B13 =IF（C13=1，F13&＂-＂&C13，＂＂），向下复制公式，不符合条件的值为空。
　　D22单元格：取值判断
　　目的和意义：判断员工在异动表中是否存在有效的异动信息，如果有显示1。
　　计算公式：
　　D22=--（COUNTIF（$F$13：$F$17，F22）>0），向下复制公式。
　　C22单元格：取值区域
　　目的和意义：如果D列的值为1，计算结果为＂异动表＂，意味着要去异动表中抓取数据，否则就应该在＂企业表＂中抓取数据，这里的＂异动表＂和＂企业表＂与之前我们给区域命名的字段一致。
　　计算公式：C22=IF（D22=0，＂企业表＂，＂异动表＂），向下复制公式。
　　B22单元格：取值ID
　　目的和意义：如果前面的判断要去异动表中抓取数据，则工号和D列合并，形成一个新的ID，否则就直接把工号引用过来。
　　计算公式：B22 =IF（D22=0，F22，F22&＂-＂&D22），向下复制公式。
　　H19单元格：位置计算
　　目的和意义：把＂部门信息＂、＂职级信息＂这些字段在上述表格中的位置用MATCH计算出来，为下面一次性使用VLOOKUP函数拓展道路。
　　计算公式：
　　H19 =MATCH（H21，$B$3：$N$3，0），向右复制公式。
　　H22单元格：抓取数据
　　目的和意义：根据所需计算的工资表时间，自动从《员工企业信息表》《员工异动信息表》两个表内抓取准确的员工信息。
　　计算公式：H22=VLOOKUP（$B22，INDIRECT（$C22），H$19，0），向右复制公式，向下复制公式。
　　这样我们就基本完成了工资基础表的智能抓取工作。其实在工作中可能有些同行还会遇见诸如以下的问题：
　　1.异动按天数折算问题；
　　2.时间影响工资是否当期发放问题；
　　3.职级影响试用期时长问题等。
　　这些问题其实都可以在工资表技术中得到非常妥善的解决，希望大家也能集思广益提出更好的解决办法。工资表技术就先介绍到这里，下一期我们将进行《职级体系》模型技术的详细介绍。
　　责编/寇斌