#用Excel做(半)自动化周报会遇到两大难题,如何让Excel自己识别本月有几周,本周有几天呢?如果无法实现这一步的自动化,那就等于是每月手动作一张表。
问题说明
例举最终呈现的样式:
这张表注重本月每一周数据而削弱年和月的展示。在这张表上,问题就具体为:
- 粉色的周标题,具体要做到第几周呢?
- 每周下面的各个数据,如何按照每周的天数分配呢?(例如目标设置,一般都设立月目标,再根据每周工作日的长短,分配下去,不能按周维度平均分配,不然第一周2天的目标就与第二周7天目标一样了,这样很不合理。)
逻辑思路
从月份上找出以下规律:
- 每月最多有6周,所以为了自动化呈现数据,要有6个一模一样的小单元;
- 每月2-4周一定都是7天,所以2-4周可以用完全相同的天数7来计算;
- 每个月第一周的天数可以根据每月1号是周几计算出来;
- 如果有第5周,那么根据第5周的第一天是几号计算第5周的天数;
- 每月第6周(假设有)可以根据每月最后一天是周几计算出来。
函数汇总
- 第1周有几天:=8-WEEKDAY(DATE(年,月,1),2),这是以周一为每周第一天计算;
- 设有第5周,这周的第一天是几号:=(7-第一周天数)+21+2,21是第2周到第4周天数总和;
- 第6周有几天:=WEEKDAY(DATE(年,月,DAY(DATE(年,月+1,0))),2),是周几就有几天。其中weekday这个公式中的第三个参数“日”需要用到day函数计算整个月的天数,而嵌套的date函数在此用的是下一个月第0天来表示。
注意事项
在这张表中,天数用作分母,所以当一个月没有第6周时,那么由于分子为0,所以计算出的数为0,在可视化的表格中是合理的。
然而,另一种情况,你必须要确定有没有第6周,不然第6周的公式计算的是第5周的天数,那么表格数据就混乱了。这种情况下可以添加一个IF函数做判断。
已知一个月最长是31天,由此可得:
=IF(DAY(DATE(年,月+1,0))>31,0,WEEKDAY(DATE(年,月,DAY(DATE(年,月+1,0))),2)