考勤表的制作

（图 合伙人noah）

告别11月，迎来了2016年的最后一个月——12月您好！

今天是2016年12月的第二天，让我们一起来看看合伙人成员在干嘛呢？

 

时间：2016年12月2日

地点：EXCEL中国合伙人驻广州市白云区办公室

人物：无敌大师姐『Coffee』

不会妹『仰望~星空』

拍摄：花花女神

 

星空：大师姐，可以向你请教个问题吗？我在制作考勤表，怎样能根据年月快速得到当月的日与星期呢？

Coffee：可以通过控件来快速实现日与星期的同步更新。

星空：不懂。

Coffee：你看我这有个考勤表，我只要移动滚动条，就会出现对应年份月份的日与星期。

 

星空：哇塞，师姐可以教教我吗？

Coffee：当然可以，先教你做控件吧！

 

Coffee：插入滚动条后，右键-设置控件格式

 

Coffee：从哪一年到哪一年你可以根据自己的需要去设置，年份的控件做好后，月份的控件也是同样的方法，月份是从1月至12月，那么最小值为1，最大值为12，注意要填写单元格链接，后面日与星期的数据都会随着这个链接单元格年月的变动而改变。

 

星空：好的，师姐，真没想到一个小小的控件有这么大作用。

Coffee：再来看看这个标题的制作吧。

 

公式：A2=TEXT(DATE(C1,F1,1),'yyyy年m月员工考勤表')

Coffee：知道这个公式什么意思吗？

星空：不知道。

Coffee：DATE返回日期时间代码中代表日期的数字，DATE的使用格式为DATE(年,月,日),年份在单元格C1，月份在单元格F1，把他们作为DATE的第一第二参数，当移动滚动条时相关日期会随之改变，这个日你写当月的某日都行。TEXT根据指定的数值格式将数字转成文本，注意要加英文状态下的双引号。

星空：公式在A2单元格，为啥内容跑那么远了呢？

Coffee：这是设置了单元格对齐方式。

 

Coffee：如果想知道今天具体是哪一天、星期几，你可以像这表右上角这样设置。

 

公式：=TEXT(TODAY(),'今天是:yyyy年m月d日 aaaa')

Coffee：TODAY()返回日期格式的当前日期，该函数不需要参数的。

同样用TEXT函数转换格式，4个a是将日期转成星期几的形式。

Coffee：现在来看看对应的日与星期到底怎么实现的。

 

公式：C3=TEXT(IF(MONTH(DATE($C1,$F1,COLUMN(A1)))=$F1,DATE($C1,$F1,COLUMN(A1)),''),'d')，向右填充。

星空：这么复杂啊，这公式看得我头都晕了。

Coffee：理清思路就不复杂了，DATE($C1,$F1,COLUMN(A1))部分容易理解吧，单元格C1是年份，单元格F1是月份，那COLUMN(A1)就是日了，因为公式右拉时要得到1,2,3…等这样的序号，用COLUMN函数构造，比如COLUMN(A1)等于1，右拉时变成COLUMN(B1)，相当于等于2。函数MONTH是返回月份，IF部分就是如果MONTH(DATE($C1,$F1,COLUMN(A1)))部分得到的月份等于F1单元格的月份，就返回DATE($C1,$F1,COLUMN(A1))这个日期，否则为空。最后用TEXT函数转换格式，只要日的话用'd'。

 

公式：C4=TEXT(IF(MONTH(DATE($C1,$F1,COLUMN(A1)))=$F1,DATE($C1,$F1,COLUMN(A1)),''),'aaa')，向右填充。

Coffee：得到星期的公式与得到日的公式差不多，只是在用TEXT函数转换格式时有点不一样，前面说了4个a是将日期转成星期几的形式，而3个a是将日期转成几的形式。

星空：哦，那这些星期六星期日填充黄色是怎么设置的？

Coffee：用条件格式，先选中要设置的区域-条件格式-新建规则

步骤如下图：

 

公式：=OR(C$4='六',C$4='日')

Coffee：设好后只要满足条件就会在条件区域范围内对应整列填充黄色。

星空：蒙了，我先缕缕，有问题再向师姐请教。

Coffee：可能当时是听懂了，但并未完全掌握，很快就会忘，自己动手操作才是硬道理。

星空：好的，谢谢师姐！

 

都说授人以鱼不如授人以渔，但帮人是情分，不帮是本分，关键还得靠自己。2016年的最后一个月，年前的最后一次学Excel，好好把握！

作者：仰望～星空