如何用 Excel 设计一个员工年假统计及查询系统，HR 应该学起来
在11月26日的时候，我们有发布过一篇<年休假的这些规定及具体计算原来是这样的，必须学起来> 的文章，让我们都清楚知道了，原来年休假的具体规定及计算方式，但是对于HR来说，这还远远不够，如果有更具体的计算机统计的表格或系统，或大大减轻工作的压力，及提高效率。
其实也不难，我们可以用大家比较熟悉的 Excel 来设计这样一个计算、记录及查询的表，具体步骤如下：
一、建立一个基础表格，加上基本的数据资料，最好也可以将希望实现的效果表格列好，例如下图：
以上是非常简单的表格，基本上手动输入进去都可以，只不过有些栏位其实是设置了公式函数在里面的。以及如果是考勤或年假记录人员，日常的年假记录也可以以此表格操作即可。
表中的一些数据是使用了函数的，比如每月的可休假天数，我们后面会讲到。
二、计算在职工龄及累计工龄，并测算员工开始工作日期（入职日期-入职前工龄）
在表格“入职日期”后面，增加”在职工龄（月）“的栏位，以序号1为例设立公式为：(TODAY()-G8)/365*12，其中G8为”日期“，公式的意思也是很简单的，就是”（today() 今天的日期 - 入职日期）/365天/12个月“，就等于在职了多少个月。
为何要以月为单位呢，其实是与后面的“入职前社保缴交月数（月）”对应，那为何“入职前社保缴交月数（月）”要以月数来表示呢，那是因为社保的记录一般是以月数统计的，方便员工之间输入月数，而不需要再计算年，让 Excel 自动帮忙计算，减少工作步骤及错误率。我觉得这个理念在其它的工作中也可以借鉴。
“实时累积工作年限（月）”和“实时累积工作年限（年）”就很简单了，比如“实时累积工作年限（月）”就等于”在职工龄（月）“+“入职前社保缴交月数（月）”，而“实时累积工作年限（年）”等于“实时累积工作年限（月）”/12。
为何计算了月，又要计算年呢，是不是多此一举？其实不然，这是为了多重检查，因为我们知道1年工龄是5天年休假，10年工龄是10天年假，20年是15天年假，所以为了清晰明了，用年来换算会更好一点。
最后就是“测算开始工作日期（入职日期-入职前工龄）”，其实这是为后面的的计算做辅助的，以及它的公式也是很简单，还是以序列1为例，公式就是：DATE(YEAR(G8),MONTH(G8)-I8,DAY(G8))，意思是在入职日期的基础上，减去”入职前社保缴交月数（月）”，就可以得出员工具体开始累计的入职日期了，是不是很巧妙的计算？
具体见下图：
三、计算累计年假变更日期
可能会有人有疑问，为何要计算这个日期？其实，我想说的是，这是一个步骤啊，一步一步来，不过问题也是个好问题。
通过这个“累计年假变更日期”，就可以知道员工在哪一年、哪一个月的哪一天年假会发生变化，这样我们就可以为后面中途可能会发生年假变动的情况做计算及辨识。
比如，“满12个月(5天年假)”的公式计算为：DATE(YEAR(L8),MONTH(L8)+12,DAY(L8)-1)，意思是从“测算开始工作日期（入职日期-入职前工龄）”加上12个月，再减去1天，那么就是员工满12个月可休年休假的日期。
而满10年和20年因为规定的原因，会和“满12个月(5天年假)”有些不一样，例如10年工龄年休假的公式为：DATE(YEAR(L8)+10,MONTH(L8),DAY(L8)-1)，意思是从“测算开始工作日期（入职日期-入职前工龄）”加上10年，再减去1天，那么就是员工满10年可休年休假的日期。满20年的，那么就加上20年再减去1天，也就是员工满10年可休年休假的日期。
具体如下图所示：
四、具体当年年休假天数的计算
接下来的工作会上升一个级别。
首先，要插入两行，用来展示及辅助，一个是“年份”，例如：年份：2019，其中2019一定是占一个单元格，并且是数据格式。
其次，将每月第一天和月末最后一天标出来，也很简单，例如每年第一天就是："年份"&"/"&1&"/"&1，例如输入2019，那么结果就是：2019年1月1日，同理2019年12月31日的公式也是很简单："年份"&"/"&12&"/"&31。年初和年尾都有了，其它每个月的年初和年尾就更好计算了，比如"年份"&"/"&2&"/"&1，就是2019年2月1日，又或者直接在2019年1月1日的基础上加1个月：DATE(YEAR(T2),MONTH(T2)+1,DAY(T2))，其中T2就是”2019年1月1日“。2019年1月31日也很简单，用2019年2月1日减去1天即可。其它的天数可以以此类推。
具体如下图所示：
最后，就可以计算年休假了，但是为了步骤清晰准确，还是增加了“年初1月1号享有年假天数”和“年底12月31号享有年假天数”，这个位置也是有公式的，可以避免需要手动修改，公式为：E2&"-"&1&"-"&1&"享有年假天数" 与 E2&"-"&12&"-"&31&"享有年假天数"，其中E2就是当年年份。
接下来就是计算员工在年初和年底的年假天数了，公式会稍微复杂起来了，年初的公式是：IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($T$2+1),"y")))=0,0,IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($T$2+1),"y")))>=20,15,IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($T$2+1),"y")))>=10,10,5)))，其中L8是“测算开始工作日期（入职日期-入职前工龄）”，E2就是当年年份，T2是“年初1月1号”。
那么年底的公式就是：IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($AP$3+1),"y")))=0,0,IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($AP$3+1),"y")))>=20,15,IF((IF(YEAR(L8)=$E$2,0,DATEDIF(L8,($AP$3+1),"y")))>=10,10,5)))，其中L8是“测算开始工作日期（入职日期-入职前工龄）”，E2是当年年份不变，而AP3就是“年底12月31号”。
接下来就是当年度年假变更日期，也是为了辅助计算和辨识，标题可以用公式表示："当年度（"&E2&"）"&"年假变更日期"，E2就是当年年份。而返回的日期公式为：IF(YEAR(M8)=$E$2,M8,IF(YEAR(N8)=$E$2,N8,IF(YEAR(O8)=$E$2,O8,0)))，其中M8、N8与O8就是“累计年假变更日期”，E2还是当年年份。公式的意思是：如果“累计年假变更日期”的年度与当年年份一致，那么就返回相应的日期，否则就不显示。从下图就可以看得到，如果是2019年有年假的，那么就会返回相应的日期，其它的就不显示。
最后就是当年度年休假天数的计算了，标题也可以用公式自动计算显示：E2&"年度应有年假天数"，E2还是当年年份不变。而具体的年假天数的计算公式是：IF(R8<>0,INT((P8*(R8+1-$T$2)/365)+(Q8*($AP$3+1-R8)/365)),Q8)，R8是“当年度年假变更日期”，P8是每年1号应有年假天数，T2是当年1月1号日期，Q8是每年年底最后1号应有年假天数，AP3是当年最后一天也就是12月31号日期。公式的意思是，如果当年度没有年假变更，那么就等于1月1号的年假天数，否则分段计算变更日期前后的年假天数，最后取整数返回年假天数。取整的意思是小数点后面的不计算，不管是2.1还是2.9，都只显示整数2天。
这样我们当年的年假天数就算自动计算出来了，当然，公式里面有很多带“$”的符号，其实那是给单元格“固定位置”的意思，便于我们公式复制到其它地方，单元格的位置也不会变。
五、每月享有年休假的天数及已休情况
按照我们做好的表格，就可以开展年休假享有天数及已休情况做统计了，虽然每个人每个月享有及休假不一样，但是也可以通过人工输入及公式一起计算，其实如果会机器人换算，其实也可以让员工自动发电邮通知机器人，然后机器人登记，再返回给员工休假情况，希望后续我能写得出来。
看回记录表格部分，每月可休其实可以通过当年度享有年休假天数及当年累计天数比例进行计算，比如1月的公式：INT(($T$3-$T$2+1)*(S8/365))，意思是用月底日期-月初日期+1天*当年年假天数/365天，就是1月份可休年假天数。以此类推，2月份可休年假就可以用2月月底日期-1月1号日期+1**当年年假天数/365天，直到12月份。
但是除了1月份以外，2月份开始，在计算了可休天数外，还需要减去前几个月已休的天数，而已休天数是手动输入的。
最后的备注是写明具体休假日期及具体情况，这样就完成了整个年休假天数的计算及登报统计表。
六、年假查询窗口
最后如果需要，可以制作一个针对员工的年假查询窗口，为了保密性，可以采用一个编辑过的查询码，比如入职年份加工号后三位代替。
而查询的栏位和结果可以依据年假统计表格，通过查询码用 Vlookup 的方式显示。通过还可以返回如果输入错误，则返回错误的提醒，具体公式为：=IF(ISERROR(VLOOKUP($B5,'2019年假明细'!$B:$AR,D2,0)),"(代码输入错误！)",(VLOOKUP($B5,'2019年假明细'!$B:$AR,D2,0)))，公式的意思是如果 Vlookup 查询是错误的，那么显示“代码输入错误”，否则就返回员工年假的情况。
再就是用隐藏工作表，锁定工作薄和工作表的方式，限制员工随意查看及修改。
以上就是整个员工年休假计算及查询系统的设计步骤，仅供参考，特别是HR。
如果想要这份年假的Excel电子版，可以私信本人。
如果喜欢本文，请点赞，收藏或转发吧。
2019-12-09
