当前位置:首页 > 表格制作 >

如何用excel制作月份自动变更的考勤表

发布时间:2020-06-25 09:33   来源:松丫网 作者:溪门飞雪 围观:次 地址:https://www.obcry.com/

对于做考勤的都希望能做一个自动变更月份的考勤表,只要系统时间一到,表中的日期就会自动变更为当前日期。这就不需要再每个月手动去更改时间了,能不能实现这样的呢?
答案是肯定的!博主溪门飞雪今天就给大家分享一下,我在13年时制作的一张自动更改时间考勤表。
如何用excel制作月份自动变更考勤表
首先,我们观察一下我这张表,这张表在使用时,只需要将左上角黄色区域的月份和年份选择为本月即可,考勤区,有下拉,直接选择即可。这个表做起来有点复杂,不过本文博主会详细介绍步骤。
 
1、做框架
 
框架的话,看各个公司的需求,样式自己定就好,没有说统一的标准。考勤表主表区域需要有31列,因为每月最多的时候有31天。而行嘛,至少两行作日期,日期一行,星期一行,其他的就随意。
做框架
2、本月应出勤数
 
对于正规的企业来说,出勤数和国家规定的工作日是一样的,该放假就放假,该双休就双休。这里我们说的就是按照国家规定的来,如果你的公司不是这样的,则根据各自情况确定。
比如,按照国家的标准,本月应上班21天。这是通过函数公式计算出来的,公式是:=NETWORKDAYS(DATE($G$1,$J$1,1),EOMONTH(DATE($G$1,$J$1,1),0))
本月应出勤数
【公式解读:“NETWORKDAYS”计算的是两个日期之间的完整工作日的数量;“DATE”计算的是一个日期,“EOMONTH”这个函数计算的是每个月的最后一天,不一定是31号,可能是30号,也可能是29号、28号。】
 
3、表头公式设置
 
表头“溪门飞雪有限公司2019年9月考勤表”,这个也是设置了公式的,公式为:=TEXT(DATE(C1,I1,1),"溪门飞雪有限公司e年m月考勤表")
表头公式设置
【公式解读:“TEXT”是将单元格的值转换为文本;“date”就是日期,“e年m月”自动匹配结果中的年月值。】
表头公式设置方法一
当然,这里我们不一定要弄这么复杂,如果你搞不懂“e年m月”,不会用,也可以直接使用“yyyy”和“mm”,四个“y”表示显示四个字符的年份,如果改为两个“y”,则结果显示为“19”年,月份也是一样的,一般使用一个“m”就可以。公式:=TEXT(DATE(C1,I1,1),"溪门飞雪有限公司yyyy年mm月考勤表")
 
4、日期的公式设置
 
接下来,就是最重要的部分,日期函数公式的设置。日期的设置主要是随着月份的变化,天数变化,小月将不显示31号,2月不能显示30,31号。公式:=IF(MONTH(DATE($C$1,$I$1,COLUMN(A1)))=$I$1,DATE($C$1,$I$1,COLUMN(A1)),"")   输入公式后,一直将公式拖动或者复制到最后边即可。
日期的公式设置
【注意事项:公式输入在日期这一行,刚才我们输入“1,2,3……”只是为了方便大家看,这些位置其实是需要由公式显示日期的。那么,结果出现了上图这样的情况,这个时候我们需要对日期这31个单元格进行格式自定义,全部自定义为“d”。】
日期的公式设置 自定义格式
【公式解读:“if”是判断函数,主要是判断月大月小;“MONTH”返回的是月份;“COLUMN(A1)”返回结果是“1”,这里主要为了向右拖动公式日期能够自动变化,“COLUMN(A2)”返回结果是“2”,依此类推。这里有个判断,假如“某一天”是“某月的天”,则显示这天的日期,否则变为空。】
日期的公式设置结果
5、星期的公式设置
 
这也很重要,这得根据上边的日期来,和日期对应。公式:=IF(MONTH(DATE($C$1,$I$1,COLUMN(A1)))=$I$1,DATE($C$1,$I$1,COLUMN(A1)),"") 输入后,依次往右拖动或者复制到最右边即可。
星期的公式设置
【注意事项:和日期一样,也出现这样的情况,这时候我们需要自定义格式,将格式设为“周三”或者直接“星期三”,或者自定义为“aaa”即可。】
星期的公式设置 自定义格式
【公式解读:其实这个公式和日期公式是一样的,就是显示格式不一样而已。】
星期的公式设置 结果
 
6、设置颜色变换
 
当做好之后,不是很好看,我们可以将每个周的周日用特殊颜色标记出来,这样,就将月分成了周。我们先选中日期和星期,两行都全部选中,然后依次点击功能区中的“条件格式”,“新建规则”,“使用公式确定要设置格式的单元格”。
设置颜色变换
在“为符合此公式的值设置格式”下方的输入框中输入公式:=WEEKDAY(D3,2)=7 ,
设置颜色变换 新建规则
然后点击下方的“格式”,打开格式面板,设置自己喜欢的格式,确定即可。
设置颜色变换 结果
7、设置主表下拉选项
 
为方便打考勤,我们可以将考勤的选项设置好,比如,缺勤的打“×”,出勤的打“√”,婚假填“婚”等等。设置方法也比较简单,运用的是“数据有效性”。这里要注意的是,之前的版本,比如07版,03版,这个功能叫做数据有效性,而在13版中,改名为“数据验证”。
在输入时,我们先在表格,或者其他地方写好,中间用英文的逗号隔开,比如,这里我们写入:√,事,病,婚,差,年,旷,迟1,迟2,迟3,迟4,迟5,迟6,迟7,迟8,迟9,迟10 。“迟10”指的是迟到10分钟,当时笔者在公司时,迟到一分钟视为迟到一小时,迟到一小时视为旷工,这里可以根据自己公司的情况设置。
设置主表下拉选项
设置好之后,只需将格式复制到所有单元格即可。
设置主表下拉选项 结果
补充:在最上方当天日期那里最好设置为手动填入日期,这样的话以保证日期不会自动变更。比如,有的人在月末最后一天的时候做考勤表,结果,没做完,到了下月了,这时候,日期自动变化了,就不太好,所以,这里可以直接手动填写。当然,如果你想要自动变化也可以。输入公式:=YEAR(NOW())  自动年份;输入公式:=MONTH(NOW())  自动月份。
 
有什么不明白的地方,可以在评论区留言,也可以直接加我微信答疑。
.
  • 上一篇:没有了
  • 下一篇:如何用Excel表格制作工资条
  • 你要不要说点什么呢?
    • 全部评论(0
      还没有评论,快来抢沙发吧!
    CopyRight © 2011-2016 松丫网 All Rights Reserved 滇ICP备14007501号-4

    本站内容均来自松丫网,未经许可不得转载 联系扣扣:2093381517 微信:Bluesky838