如何用 excel 制作漂亮的动态日历 -j9九游会登陆入口
2023-04-11 13:27
原文标题:《这么牛 x 的漂亮动态日历,是怎么用 excel 做出来的?(不是透视表)》
大家好,我是在研究日历做法的小爽~
不知不觉,2023 年已经过去几个月啦~
之前我们介绍过,利用数据透视表制作日历。
也介绍过利用函数制作日历。
不过,有个小伙伴问,能不能用 pq 制作日历?
我突然想到 pq 法做日历,好像没给大家介绍过。
powerquery (pq) 里面也有很多日期类函数,也可以制作日历。(只不过难度会比数据透视表大一点点,而且还会涉及几个 m 函数。)
既然小伙伴们想学,那今天小爽将会带大家一步步编写 m 函数公式,来制作这个日历。
1、构造数据
在制作之前,我们先构造一个查询表,月的单元格,可以事先利用数据验证设置一个下拉列表。
具体步骤:
❶ 将查询表导入到 pq 编辑器中。选中 a1:b2 单元格区域,在【数据】] 选项卡下,单击【来自表格 / 区域】,进入 pq 编辑器中。
单击 fx 可以新增一个公式步骤。(后续新增步骤都是点这里哦~)
鼠标移动到需更改的步骤上,按右键,单击【重命名】即可修改步骤名称。(后续重命名步骤都是点这里哦~)
ps:命名好步骤名称有助于提高公式的可读性。
❷ 新增步骤,获取查询表中年和月的第一天,步骤名称命名为「月份第一天」。
= #date(源[年]{0},源[月]{0},1)
小 tips:
= #date (年,月,日) 主要是用来构造一个日期。
源 [年]{0} 获取表中的年。
源 [月]{0} 获取表中的月。
❸ 新建步骤,获取查询表中年月的最后一天。步骤名称命名为「月份最后一天」。
= date.endofmonth(月份第一天)
date.endofmonth 函数可以返回日期当月的最后一天的日期。
❹ 新建步骤,将第一天和最后一天日期进行扩展。步骤命名为「月日期」。
= list.transform({number.from(月份第一天)..number.from(月份最后一天)},date.from)
简单解释一下:在 m 函数表达式中,列表的表示方式是用 {中括号},如下图,{1,2},就是 1,2 形成的列表。
如果要表示 1 到 9 的列表,就是 {1,2,3,4,5,6,7,8,9},可简写为 {1..9},如下图:
由于日期的本质是个数值,所以我们可以先将日期利用 number.from 先转为数值,然后再进行扩展。最后利用 date.from 转为日期即可。
{number.from(月份第一天)..number.from(月份最后一天)}
现在,一整个月的日期我们都做出来了。
观察日历表,可以发现,我们还需要得到日期中的日,星期数,以及每月周数 的相关数据。
所以我们下面三个步骤就是为了获取这三块的内容。
❺ 新建步骤,获取日期的天数。步骤命名为「获取日」。
= list.transform(月日期,date.day)
date.day 可以获取日期中的日。
❻ 新建步骤,获取星期数。步骤命名为「获取星期数」。
= list.transform(月日期,date.dayofweekname)
date.dayofweekname 可以获得日期的星期数。
❼ 新建步骤,获取日期对应的当前月的周数。步骤命名为「周数」。
= list.transform((月日期),date.weekofmonth)
date.weekofmonth 可以获得日期对应的当月的周数。
到这里,我们已经把所需要的三个数据弄出来了。
2、转表透视
由于日历是个表,所以我们还需要将数据进行整合合并在一起形成一个表。
❶ 新建步骤,拼接成表。步骤命名为「数据」。
= table.fromcolumns({周数获取星期数获取日})
table.fromcolumns 可以按列转换为表。
❷ 日历表是个二维数据,所以我们还需要将星期数 (column2 列) 进行透视处理。
选中 [colum2] 列,在【转换】选项卡下,单击【透视列】,出现透视列弹窗,值列选择 [column3] 列,单击【确定】按钮。
到这里,我们发现,星期数并不是按照我们想要的效果进行排序的。
只需要更改第二参数,就可以改变日期的顺序。
原本的公式:
= table.pivot数据 list.distinct数据[column2]), "column2", "column3", list.sum)
修改后的公式:
= table.pivot(数据, {"星期一","星期二","星期三","星期四","星期五","星期六","星期日"}, "column2", "column3", list.sum)
当然,如果你想要的日期是 从星期日开始的,也可以通过改变第二参数的顺序来实现。
❸ 最后一步就是将我们不需要的 column1 列,也就是显示月周数的列,进行删除即可。
选中 column1 列,按鼠标右键-删除。
现在,我们的日期就制作完成啦~
❹ 最后将 pq 做好的日历表加载到工作表中,就搞定了!
依次点击【文件】选项卡-【关闭并上载至】,选择「现有工作表」并指定单元格位置。
3、自动更新
由于 pq 每一次更改查询表的年月,都需要刷新一次,很麻烦。
所以,我们可以加个工作表事件,当 a2 和 a3 单元格发生值改变的时候,将表格全部进行更新。👇👇👇
首先,将表格另存为.xlsm 格式。
然后,按住快捷键【alt f11】进入 vba 编辑器中。
在当前工作表下。
输入这段 vba 代码。
private sub worksheet_change(byval target as range) if intersect([a2:b2], target) is nothing then exit sub thisworkbook.refreshallend sub
由于用到了 vba 代码,所以我们必须将文件保存为 xlsm 格式,否则无法使用。
这下,每次更改查询表中的年月,日历也会自动刷新啦。
4、总结一下
本文主要介绍了日历的 pq 做法,涉及以下日期 m 函数:
❶ 利用#date (年,月,日) 构造一个日期;
❷ date.endofmonth (日期),可以返回日期当月的最后一天的日期;
❸ date.day 可以获取日期的日;
❹ date.dayofweekname 可以获取日期的星期数;
❺ date.weekofmonth 可以获取日期当月对应的周数。
还有涉及转表(table.fromcolumns),以及表透视(table.pivot)的函数。
综合来讲,pq 做法跟数据透视表制作日历表,思维上比较相似。
数据透视表做法是通过日期函数获取月份,天数,星期数,周数作为数据源,然后通过创建透视表达到制作日历表的方式。
pq 做法也是比较类似,但是相比于数据透视表方法要稍微复杂些。
本文来自微信公众号:,作者:小爽