超级大牛的员工花名册及自动计算
《公司员工花名册》，是很多公司基础性人力资源工作，公司规模越大，耗费的时间越多，严重影响其他工作进度，且费事不讨好。
笔者结合前期工作经验，总结并设计出了一套高效的花名册表格。基本摆脱了各类人事类月报的烦恼，腾出了更多的时间去做其他更重要的事情。
主要设计思路：基础信息自动化、选择信息菜单化、关联信息标准化、计算信息公式化。
一、基础信息自动化
1、身份证信息
百度会告诉我们很多，在此不多说，简单介绍一下：
A、长度限制
B、性别自动填写
=IF(ISODD(MID(T5,8,10)),"男","女")
C、出生日期自动填写
=TEXT(MID(T5,7,8),"0000!/00!/00")
D、年龄自动填写，并自动更新
=DATEDIF(TEXT(MID(T5,7,8),"#-00-00"),TODAY(),"Y")
E、籍贯自动填写
=LOOKUP(VALUE(LEFT(T5,2)),{11,"北京市";12,"天津市";……"台湾省";81,"香港特别行政区";82,"澳门特别行政区";"","0"})
注意：由于人口流动、县市行政区划有变动，故少量会有点问题。如重庆以前归属四川，很多重庆人的身份证前两位也是51。手动改写即可。
2、其他信息
年龄结构、工龄分类等等，属于推算数据，也实现了推理数据自动化。
二、选择信息菜单化
1、常规菜单：状态、是否退伍、政治面貌、最高学历等，设计了常规下拉菜单。
2、复杂菜单：一级部门、离职原因、招聘渠道、薪增类别、民族等，用了公式，设计了更好的下拉菜单。
在此不在多说，不是本文重点，有需要的请看我的公众号或今日头条《考勤表》相关小品文章。
3、联动菜单：本来想设计联动菜单，但想了一下，没有多大实际意义，设计了一半就放下了。如有兴趣的，加微信私聊。
三、关联信息标准化
1、日期标准化
所有的日期，均设置为000/00/00格式，列宽最窄，格式统一、美观。
yyyy/mm/dd
2、电话号码标准化
000 0000 0000规定了样式；号码位数长度也规定了。也是为了方便阅读，减少出错的概率。
3、员工编号长度
员工编号的长度，请各位亲根据实际情况去定义，建议6位，小企业4-5位。但一定要有，为后期工资、档案管理、请假单等等打下基础，尤其是在姓名重复的时候，很管用的。
四、计算信息公式化
1、合同到期提醒
=TODAY()-30
劳动合同到期前30天自动标记为红色底纹。
2、隔行自动变为灰色底纹
表格很长，容易看错行，因此设计了各种功能，增加可读性，减少出错概率。当插入、删除行后，底纹会自动调整。不会出现双色底纹在一起的情况。
=MOD(ROW(),2)=0
3、筛选后自动填写序号
=SUBTOTAL(103,B$5:B5)*1
实操过程中，经常会筛选出特定人员的信息，以提供给其他人。自动填写序号，就方便多了。
4、自动统计（本文核心）
公式量太庞大，我就不一一列举了。
主要设计的思路是：利用前一系列的规定、菜单、计算，利用公式自动根据日期区间、类别、条件等全自动统计，无需人工干预。
难点在于四个条件判断：起始日期、终止日期、条件1、条件2，普通话就是：对满足4个条件的单元格计算数量。
如上图=SUMPRODUCT((花名册!$AK$5:$AK$4900-$AV$2>=0)*(花名册!$AK$5:$AK$4900-$AV$4<=0)*(花名册!$P$5:$P$4900=D6)*(花名册!$N$5:$N$4900="调入"))
这个公式只是举例，请各位花点时间消化一下，模版中几百处予以运用。
它表示，对2018年1月1日至1月31日区间调入的经营班子数量予以统计个数。其中，1月1日、1月31日，也是自动填写的。您只需选择即可。
上述设计好了以后，只要你人事基础数据是正确的，就一定可以向前查任何月份的相应信息。你就不会给领导说"领导，给我X天时间，我计算一下去年的情况"，而你只需要对领导说："稍等，10秒即可！"
另外，《类别统计表》中，公式会自动判断。如当前是2017年10月，则，前1-10月的人数、占比会自动计算，不需要再去查或重新手动数个数。
总之，本表耗费了笔者上百小时的设计、优化、验证，个人百度了很多很多，也在实践中看到了很多企业的表格，还没有看到比它更牛、更实用的《员工花名册》（本表还有需要再优化的余地）——献给所有在基层奋斗的有志青年！
【焦点说管理】于2018年国庆前
本文内容属于原创、绝非剽窃或抄袭，如喜欢、有用，请分享给您的朋友、同事。让我们一起进步！大家好，才是真的好！
若需要上述Excel原文、本文Word版（打印出来，与Excel逐一对照），请私信，并回复【花名册】
若对您有帮助，请您：不吝言辞留言+点赞+关注
