Excel教程

Excel函数
Excel技巧

【EXCEL教程】3秒搞定排班表模板

当前位置:网站首页 > Excel教程

【EXCEL教程】3秒搞定排班表模板

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-03-17 22:13:49 * 浏览: 1153

今天,行政专员小爱一脸讨好的过来找我:“猪爸爸,大家都说你Excel水平很好,能不能帮我弄个自动的排班表啊,就是我到时间一打开,下个月的排班表的日期啊,星期几啊,就能自动调整好,每个人休息天数,每天上班人数这些自动计算,1秒自动生成模板呢?我只需要把大家的休息直接排进去就好了。不然每回都得手动的调整日期和对应星期,计算大家的休息那些,虽然花的时间不是很多,可是人家感觉好麻烦啊~“

我想了想:“小爱呀,你要求这么多,一顿烧烤是搞不定的哦,你看,是不是得两餐呢。”

小爱咬咬牙:“行,两顿就两顿。“

我窃喜:“得咧,你看~~“

1、 在A1单元格输入公式“=EOMONTH(TODAY()+15,-1)+1”,并选择自定义,类型输入“m月排班表”,如下图。


2、D2单元格输入“=A1”,E2单元格输入”=D2+1“,按住鼠标左键拉到AH列,复制公式。如下图。设备单元格格式为自定义,输入”d”。使日期只显示天数。

3、 D3单元格输入”=weekday(D2,2),求得日期对应为星期几,设置单元格格式“数字“-”特殊“-“中文小写数字”,如下图。

4、 AI4单元格,输入公式“=COUNTIFS(D4:AH4,"<>"&"")”,复制公式到对应AI5到AI23的单元格中,从而计算各人员的休息天数,如下图。

5、 D10单元格,同理输入公式“=COUNTIFS(D4:D9,"="&"")”,求得当天售后部上班人数,将公式复制到对应其他部门及日期,如下图。

6、 选择D2:AH23区域,应用”条件格式“-“新建规则“-”使用公式确定要设置格式的单元格“,输入公式”=D$3>5“,设置格式“填充“,选择绿色。从而将周六、周日对应列修改为绿色,使周末的日期更直观。


7、 当到达离下月间隔小于15天之后,打开表格,即可得到下个月的排班表。我修改了下日期,各位可以看看效果。这里还有1点不足的就是当没有31号时,正常显示31号的那一列,会变成下月1号,需要手动的删除掉该列,如果各位有什么解决方法,欢迎留言交流。


小爱说,猪爸爸,我越来越崇拜你了,但这些公式我都看不懂呀,你能给我解释下么。

我豪情万丈的答道,简单,你看~~


第一个法宝:EOMONTH 函数

语法是EOMONTH(start_date, months),作用就是返回 start-date 之前或之后用于指示月份的该月最后一天的序列号。

参数说明


Start_date:是代表开始日期的一个日期。应使用 DATE 函数来输入日期,或者将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2009,6,20) 输入日期 2009 年 6 月 20 日。如果把日期以文本的形式输入,则会出现问题


Month:为 start_date 之前或之后的月数。正数表示未来日期,负数表示过去日期。

这里我用today()+15,计算15天后的日期,那个你每个月20来号打开表格的时候,日期就已经是下个月了,这样子EMONTH函数就求得了这个月的最后一天,之后再加上1天,就变成了下个月第一天了。


第二个法宝:WEEKDAY函数

语法是WEEKDAY(serial_number,return_type)

serial_number 是要返回日期数的日期,它有多种输入方式:带引号的本串(如"2001/02/26")、序列号(如35825 表示1998 年1 月30 日) 或其他公式或函数的结果(如DATEVALUE("2000/1/30"))。


return_type为确定返回值类型的数字,数字1 或省略则1 至7 代表星期天到星期六,数字2 则1 至7 代表星期一到星期天,数字3则0至6代表星期一到星期日。这里我们填写数字2,从而对使数字1-7对应周一到周天,通过该函数,就得到了日期对应是星期几了。


第三个法宝:COUNTIFS函数

这个之前猪爸爸的“COUNTIFS函数实例-自动汇总近7天销售数据”有详细讲过,有兴趣的童鞋可以看回这篇文章。


第四个法宝:条件格式

通过用公式设定当weekday返回的结果大于5,即返回结果为6或7(即周六或周七),符合规则,填充底色为绿色。这个讲起来比较复杂,呼声多的话,下次单独写一篇条件格式的实例。



今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。