文 / 雷哥
编辑 / 小鱼儿
大家好,想咨询下大家会使用Excel做日历表,以便统计出自己每天花费的时间吗?
雷哥给大家展示下下图的案例,输入起始时间,自动出现起始时间开始的7天日期,具体效果见下图所示。
实现以下特点
在右边的日历上可以显示出当月的日历
起始时间开始的一周内,颜色为深绿色
非本月的日期数据均使用灰色进行展示
输入本周当日每一事件花费事件,可以实现数据的自动统计
大家看完后,是不是感觉特别神奇好玩,且非常实用呢?
下面雷哥跟大家分享下如何做出这份高大上Excel日历表。
一、做出基础的时间管理表和日历表
根据表格样式,首先做出如下图所示的表格,相信这一步大家都没有什么问题。
二、设置时间管理表设置
step1:设置日期的显示格式。选中单元格A12:A18,右击设置单元格格式——自定义——设置为 “ddd m/d”。设置完成后,显示的效果如下图所示。
解释:ddd表示日期数据显示的为星期, m为月, d为日。因此ddd m/d 显示为星期+月+日。
step2:通过公式完善A列日期数据。在A13单元格中输入数据= A12+1,拖拽完成填充。具体效果见下图所示。
解释:日期的本质就是数值,加1,表示推后一天;减1,表示提前一天。
step3:对时间进行求和。
为了使得求和后的数据显示为常见的时间 00:00格式,首先对数据格式进行设置。具体操作如下:选中需要设置的单元格,右击——自定义——设置为 “[h]:00”即可。
下面开始对时间进行求和。在Total的行和列单元格中,利用sum函数求和即可。具体操作见下图所示。
设置完成后,效果如下所示。单元格中输入时间,会自动进行求和。
三、设置日历
step1:设置日历年份数据和月份数据。
思路:如果输入计划开始的时间,提取年份即可。但是如果没有输入时间,就显示空白是不是不太好看呀。雷哥改为不输入时间时,显示当前的年份。
使用公式 =IF(ISBLANK(G8),YEAR(TODAY()),YEAR(G8)) 即可实现我们上面的要求。
解释:=isblank(G8),如果G8为空,则返还True,否则返回False;Year函数是提取日期年份的函数。看到这里,大家对公式是不是有了更加深入的了解?
解释:如果日期数据为空,则显示当前的年份2019;如果输入日期数据,则提取日期,比如输入2018/1/1,则显示2018
同理,设置日历的月份数据,使用的公式为=IF(ISBLANK(G8),MONTH(TODAY()),MONTH(G8))。具体效果见下图所示。
step2:设置年月。如图所示,单元格K3的格式设置为mmmm yyyy,单元格k3中输入公式=DATE(K2,P2,1)
k3单元格中输入=DATE(K2,P2,1), 其实使用数字组成了一个日期数据,显示的是某月的第一天。
step3:确定日历表中起始数字。
首先,按照习惯,第一列是星期日,依次是星期一到星期六。具体操作如下图所示。
下面我们来确定日历表中的起始时间。
思考:假如月初时间为2019年8月1日,星期四,起始时间为 =月初时间-4,即2019年8月1日(星期四)日历初始时间为2019年7月28日。
对上述公式理解后,初始时间公式为 =K3-WEEKDAY(K3,2)
step4:完善后续的日期数据。后一天的数据就是前一天加1即可。
step5:非本月的时间使用灰色标记。要想实现这种效果,需要使用条件格式,思路是日期数据非本月,则显示为灰色数据。具体方法如下
选中数据区域,单击开始——条件格式——管理规则——新建规则——使用公式确定要设置的单元格,输入公式=MONTH(K5)MONTH($K$3) 即可。单击确定后,可以发现,非本月的数据都已经显示为了灰色。
step6:标记本周的7天日期数据,加粗+单元格填充为绿色。
思路:只要日历中的数据跟A列的数据有完全吻合的,直接加粗和单元格填充即可。因此,还是选择条件格式进行填充。
选中数据,单击开始——条件格式——新建规则——使用公式确定要设置的单元格,输入公式=MATCH(K5,$A$12:$A$18,0),分别设置字体加粗和单元格填充。
具体操作见下图所示。
四、效果展示
1. 输入起始时间2019年10月1日,可以发现,日历中10.1-10.7字体都加粗显示了,且10.1出现在星期二的位置上。
2.时间实现自动求和。如图所示,输入时间,可以统计出每天或者每一项/周(比如读书等)所花费的时间。
五、总结
今天雷哥跟大家分享了Excel时间管理表格制作方法,里面涉及的知识点有函数,条件格式等,希望大家可以掌握。有任何问题,随时可以留言沟通~
Copyright 2019 雷哥 @雷哥Office
All Rights Reserved.
欢迎大家关注
《竞争力:玩转职场Excel,从此不加班》作者!
领取专属 10元无门槛券
私享最新 技术干货