微X扫码观看全套Excel、Word、PPT视频
文:老菜鸟|excel教程
要提有效率就必须有一张比较好的考勤表。这张考勤表最少要具备以下几点:
简化输入考勤项目
醒目表示周末数据
能自动汇总数据
今天老菜鸟教你制作一份智能考勤表,同期具备以上三点,快来瞧瞧吧!
在动手制作之前,先来瞧瞧最后的效果展示:
QQ群:885401280,下载课件同步操作练习
在演示中,能够发掘以下几点:
标题输入非常简单,只需要输入一月份数字;
当月天数会自动变化,日期会按照月份自动调节表示,同期周六周日会自动变色;
考勤数据运用下拉菜单完成输入,同期有提示信息;
表格右侧有考勤结果统计区,按照填写内容自动产生统计数据。
下面就来教大众如何做出这般一个表格,会触及到非常多《Excel极速贯通班》里讲过的知识点。
考勤主体的制作
首要,做出一个最基本的表格,如图所示:
QQ群:316492581下载配套练习课件
这个表格的做法非常简单,相信大众都能够完成,接下来便是一点点的细化工作,必定要用心看哦。
将第1行进行合并,第二行最后五个单元格,分别三个合并起来、两个合并起来,完成后效果如图:
在第1行的合并单元格里点击右键,选取“设置单元格格式”。在弹出的对话框中自定义格式为:鑫盛机构0月份考勤统计表。
重视,这儿是数字0不是字母O。
设置完成后点击确定,而后在第1行输入一个数字瞧瞧效果吧。
是不是很神奇,其实这便是利用了自定义格式的特性。
第二行里的“当月天数”直接输入就可,后面填入公式:
=DAY(EDATE(DATE(2018,A1,1),1)-1)
简单介绍一下这个公式的含义,用到了三个日期函数:
1.DATE函数的格式为:DATE(年、月、日),亦便是按照指定的年月日得到一个日期,在本例中,年份为2018(倘若是下一年度的话,改为2019就可),月份取的是第1行输入的数字,日就取1,这般得到的日期便是考勤表当月的1号。
2.EDATE函数的格式为:EDATE(起始日期,相隔月数),亦便是按照指定日期和相隔月数得到一个新的日期,本例中的起始日期便是当月1日,相隔月数为1暗示次月1日这个日期。
3.DAY函数的格式为:DAY(日期),得到指定日期是当月的第几天,在本例中指定日期是次月1日前1天(EDATE函数后面有个减1),换句话说,便是当月的最后一天。再经过DAY函数得到当月一共多少天。
注:日期函数常常都是几个函数协同运用,况且针对同一个问题,会有多种思路,有些公式的思路非常巧妙,这亦是学习函数的乐趣之一。
接下来要设置的便是星期了,在C4单元格填入公式:
=TEXT(DATE(2018,$A1,C3),"aaa"),右拉就可。
公式=TEXT(DATE(2018,$A$1,C$3),"aaa")中用到了两个函数TEXT和DATE,分别解释一下这两个函数的功效:
DATE函数刚才已然讲过了,在这个公式中,年还是2018,月用的A1,由于公式要向右拉,为了防止右拉的时候A1出现变化,因此在列号前面加了$锁定,日就用第三行对应的数字暗示,这般就得到了当月所有的日期。
TEXT函数的格式为:TEXT(要指定格式的数据,格式代码)。这个函数算是一个比较高级的函数了,虽然结构比较简单,然则格式代码非常多,因此亦是一个多功能函数。本例中的格式代码为“aaa”,便是用一个字来表示星期,有兴趣的读者能够自己试试代码“aaaa”“ddd”和“dddd”分别是什么效果吧。
注:在运用TEXT函数的时候,格式代码必须加引号(引号在英文状态输入)。
至此,表格大体上已然完成为了,有些单位可能不区分上午和下午,但有些单位是区分的,怎样将每一个人的一行变成两行,难道一行一行插进么?肯定不是,这儿用到了一个非常简单的小技巧,一块经过动画演示来瞧瞧吧:
在这个操作中,触及到几个技巧:批量插进空行的技巧、运用格式刷的技巧、快速填充重复内容的技巧,期盼大众能够多练习,熟悉把握这些技巧。
至此,这个考勤表的主题已然做好了,效果是这般的:
运用要求格式和数据有效性加工表格
接下来需要完成两项工作:
1.让表格中的日期按照实质状况表示,同期周六周日会自动变色。
2.考勤数据运用下拉菜单完成输入,同期有提示信息。
来瞧瞧怎样实现这些效果。针对第1项内容,要用到要求格式这个功能,过程虽然不多,然则非常多细节地区,大众慢慢跟着来:
选取数据区域,点击【要求格式】-【新建规则】:
进一步选取【使用公式确定要设置格式的单元格】,输入公式:=C$3>$AF$2,而后点击【格式】按钮:
公式解析:很简单,便是比较第三行的每一个日期是不是大于当月天数,倘若大的话,根据设置的格式执行,这一步要重视$的位置。
选取【自定义】,在类型中输入三个分号:;;;,重视是英文状态下的分号,完成后点确定。
格式代码解释:具体含义比较繁杂,记住输入三个分号表率不表示单元格的内容。
再点确定,此时候能够发掘,不属于当月日期的数据已然看不到了:
进一步设置周六周日自动变色,还是选取数据区域,新建规则,输入公式=OR(C$4="六",C$4="日")后设置格式:
这个公式亦很好理解,OR为满足两个要求中任意一个就按设置的格式执行,亦便是第四行径“六”或“日”,就表示指定的颜色。
点击设置单元格格式按钮后,选取【填充】,选取一个颜色,点确定。
再次点击确定就可看到效果:
咦,后面空的为何亦涂色了,是哪里出错了吗?
仔细瞧瞧,发掘这个地区正好是周六,只不外被前面设置的格式隐匿了,然则涂色的时候还是生效的,怎么处理这个问题呢?很简单,选择数据区域后打开要求格式的管理规则:
在下面一条规则后打钩勾选“倘若为真则停止”,而后点击上移按钮,点击确定。
看到效果了吧。
下面要对考勤数据进行有效性设置,在进行这一步之前,让咱们先来完成统计区域的制作。
统计区域的制作
这一起的制作就很简单了,按照自己的实质需要,添加备注一栏,填写考勤项目和图例,在每一个图例下面运用COUNTIF函数完成统计,公式为:
=COUNTIF($C5AG6,AI$4)/2:
COUNTIF函数的格式为:COUNTIF(统计区域,要统计的内容),通俗的说,便是每一个人对应的两行(统计区域)中每一个考勤项目(要统计的内容)显现的次数,由于例子中每日的数据是两行,因此统计结果要除以2。
至于这些合并单元格的处理非常简单,合并一个再用格式刷去刷就好了。
此刻回过头来设置考勤数据的下拉菜单。设置有效性之前,先做一个准备工作,将图例以及说明复制出来备用,而后再去设置有效性。这部分操作看动画演示吧:
至此,这个考勤表就完成为了。相信经过今天的内容,你必定有非常多收获吧,不妨认识一下咱们的技巧课程,你必定会有更加多收获的。
加号主微X,免费领取Excel技巧视频学习
想全面系统学习Excel,不妨关注部落窝教育《1星期Excel直通车》视频课或《Excel极速贯通班》。
《1星期Excel直通车》视频课
包括Excel技巧、函数公式、
数据透视表、图表。
一次购买,永久学习。
最实用接地气的Excel视频课
《1星期Excel直通车》
风趣易懂,快速有效,带您7天学会Excel
38 节视频大课
(已更新完毕,可永久学习)
理论+实操一应俱全
主讲老师:滴答
Excel技术大神,资深培训师;
课程粉丝100万+;
研发有《Excel小白脱白系列课》
《Excel极速贯通班》。
原价299元
限时特价99元,随时涨价
少喝两杯咖啡,少吃两袋零食
就能习得受用一辈子的Excel职场技能!
长按下面二维码立即购买学习
购课后,加客服微X:603830039领取练习课件
让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文,可直接购买。
|