评论

这么牛X的漂亮动态日历,是怎么用Excel做出来的?(不是透视表)

👆发送【交流】

立即进【秋叶同学会】交流Excel!

本文作者:小爽

本文编辑:竺兰

文末获取配套案例文件下载方式~

大家好,我是在研究日历做法的小爽~

不知不觉,2023 年已经过去几个月啦~

之前我们介绍过,利用 数据透视表制作日历

也介绍过利用 函数制作日历

不过,有个小伙伴问,能不能用 PQ 制作日历

我突然想到 PQ 法做日历,好像没给大家介绍过。

PowerQuery( PQ ) 里面也有很多日期类函数,也可以制作日历。(只不过难度会比数据透视表大一点点,而且还会涉及几个 M 函数。)

既然小伙伴们想学,那今天小爽将会带大家一步步编写 M 函数公式,来制作这个日历。

构造数据

在制作之前,我们先构造一个查询表,月的单元格,可以事先利用数据验证设置一个下拉列表。

具体步骤:

❶ 将查询表导入到 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 可以获得日期对应的当月的周数。

到这里,我们已经把所需要的三个数据弄出来了。

转表透视

由于日历是个表,所以我们还需要将数据进行整合合并在一起形成一个表。

❶ 新建步骤,拼接成表。 步骤命名为「数据」

= 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 做好的日历表加载到工作表中,就搞定了 !

依次点击【文件】选项卡-【关闭并上载至】,选择「现有工作表」并指定单元格位置。

自动更新

由于 PQ 每一次更改查询表的年月,都需要刷新一次,很麻烦。

所以,我们可以加个工作表事件,当 A2 和 A3 单元格发生值改变的时候,将表格全部进行更新。👇 👇 👇

首先,将表格另存为.xlsm 格式。

然后,按住快捷键【Alt+F11】进入 VBA 编辑器中。

在当前工作表下。

输入这段 VBA 代码。

PrivateSub Worksheet_Change(ByVal Target AsRange) IfIntersect([A2:B2], Target) Is Nothing Then ExitSub ThisWorkbook.RefreshAll End Sub

由于用到了 VBA 代码,所以我们必须将文件保存为 xlsm 格式,否则无法使用。

这下,每次更改查询表中的年月,日历也会自动刷新啦。

总结一下

本文主要介绍了日历的 PQ 做法,涉及以下日期 M 函数:

❶ 利用#date(年,月,日) 构造一个日期;

❷ Date.EndOfMonth(日期),可以返回日期当月的最后一天的日期;

❸ Date.Day 可以获取日期的日;

❹ Date.DayOfWeekName 可以获取日期的星期数;

❺ Date.WeekOfMonth 可以获取日期当月对应的周数。

还有涉及转表(Table.FromColumns),以及表透视(Table.Pivot)的函数。

综合来讲,PQ 做法跟数据透视表制作日历表,思维上比较相似。

数据透视表做法是通过日期函数获取月份,天数,星期数,周数作为数据源,然后通过创建透视表达到制作日历表的方式。

PQ 做法也是比较类似,但是相比于数据透视表方法要稍微复杂些。

最后,做个小调查,

不过 Excel 中实用、有趣的技巧可远远不止我今天介绍的这些。

如果你 Get 了 Excel 思维,即便是只用一个快捷键【Ctrl+E】,也能快速搞定数据👇

如果你想学习更高效的 Excel 技巧,不如来参加我们的秋叶 Excel 3 天集训营~

秋叶 Excel 3 天集训营

仅需 1 元!

3 天提升你的 Excel 能力

赶紧扫码抢课吧

👇 👇 👇

如何下载案例文件?

案例文件已经给大家准备好了,点击下方的秋叶 Excel 公众号卡片关注后,

回复【日历

即可免费下载!返回搜狐,查看更多

责任编辑:

平台声明:该文观点仅代表作者本人,搜狐号系信息发布平台,搜狐仅提供信息存储空间服务。
阅读 ()
大家都在看
推荐阅读