趣玩Excel之日历表,实现时间高效管理,你会吗?

文 / 雷哥

编辑 / 小鱼儿

大家好,想咨询下大家会使用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,从此不加班》作者!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20191006A049LJ00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励