这年头,Excel都可以DIY日历了,你不会还不知道吧
资讯
2024-01-25
381
大家好,2022 年都已经过去 4 天了,大家去年的目标都完成了吗?
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/6103814862756b8e19de0d42f21862b0.jpg)
新的一年需要新的日历,最近我看到好多人在晒他们收到的新年日历。 然鹅,我没收到,不开心~
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/360fdcf93eb7ee14610605136c79c4ea.jpg)
这时,坐在电脑前的我看到了 Excel,突然想到,我可以给自己做个日历啊,既能省钱,还能按照自己的心意 DIY!!!
如下图: 通过控件切换月份,日历中的日期,农历,以及休班状态自动更新!
是不是特别炫酷?
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/54979b3409fd43c07d5d1bcb988096f4.jpg)
接下来,我就来揭开这份日历表的层层面纱。
01 制作外观
首先我们要制作日历表的外观,在日历表中,每个格子是三行两列的,行格为 6,列格为 7。
E3 单元格为 2022,对应数字格式为 0 年,E4 单元格为 1,对应数字格式为 0 月。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/c78d4563734d72fb5c63954a240475e3.jpg)
事先准备好参数表。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/6a32cd7debb4b80b794810cae72922a8.jpg)
▋插入控件
在【开发工具】选项卡下,点击【插入】-【数值调节钮】。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/de5ff8d9bffa9982ffcd3aca854350db.jpg)
右键控件,选择【设置控件格式】。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/371ebdf306d3dbe6a0e95d1eac63711d.jpg)
设置对象窗口中,最小值为 1,最大值为 12,单元格链接为 F4。(月份为 1-12,所以最小值为 1,最大值为 12)
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/58c6d443dd86d1042eb665a7443f830b.jpg)
PS. 如果没有【开发工具】选项卡,在【文件】-【选项】-【自定义功能区】中调出。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/cb6e9c2a2dda1e32f2e24ddd9e872f0b.jpg)
基本的外观制作好后,下面就是编写公式啦。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/9f47ed3ce59d49d448d33648849f65cc.jpg)
02 编写公式
如下图,在 E7 单元格中输入如下公式:
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7</span></code>
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/65ea8b5c4bf9d0f55a509662881bf8bd.jpg)
如下图,在 F7 单元格中输入如下公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,4,0),""),"")
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/439c8a8157dd9919fdc57838c5ea9eb3.jpg)
如下图,在 E8 单元格中输入如下公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,3,0),""),"")
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/78fa9a61563919478f063ce4b0ae13dd.jpg)
最后填充公式就可以啦~ 如动图所示,选择 E7:F9,向右向下填充公式。
小 tips: E7 单元格函数公式:
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7
其中前面一半公式是求:当前月 1 号上一个星期日的日期。
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)
后半段则是求:当前月 1 号上一个星期日的,也就是前半段公式得出的日期。 需要加上多少间隔数,才能得出当前单元格的日期。
=INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/f1c723a7ac55baad8f07edba85dc3680.jpg)
比如说,2022 年 1 月 1 号,就是当前月 1 号前一个周日的日期(2021 年 12 月 26 号)+6。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/33ec0707baa2341c9d1b8d1a446913be.jpg)
F7 单元格函数公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,4,0),""),"")
如下图,如果是左边的日期是本月的日期,则使用 vlookup 函数查找对应日期的对应休班状态,否则显示为空。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/b1b4fe74b52dbce9851dbbae9703e7db.jpg)
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/d64c776900a5aba2754dc17a3f7b7814.jpg)
E8 单元格函数公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,3,0),""),"")
如果是上边的日期是本月的日期,则使用 vlookup 函数查找对应日期的对应日,否则显示为空。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/ba1f0415d39892b10851aff279f73aaa.jpg)
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/5cf17482aa7e48d3094858b844a1ae91.jpg)
公式设置完后,下一步就是要「装饰」日历表啦~
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/7b229f260e7c1047235209b642bd89f6.jpg)
03 设置格式
▋设置休班状态格式
让 0 不显示
填充完公式后,我们发现状态单元格中有很多 0,影响表格的美观程度,所以要让这些 0 都不显示。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/b504ec3ed9de662d5828832da3c1a4f1.jpg)
这要怎么做呢?我们可以使用自定义数字格式。 按住【Ctrl】键,选中需要设置的单元格区域,再按住快捷键【Ctrl+1】弹出【设置单元格格式】对话框。
然后在类型中输入;;;@,单击【确定】按钮,此时除了文本,所有的 0 都显示为空白了。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/eeb512d93c59af903135ad5f68656238.jpg)
小 Tips:在数字格式中,正数格式;负数格式;零值格式;文本格式。
;;;@ 其实就是就是让数值都不显示,文本显示本身,@ 符号代表文本本身的字符。 设置休班颜色
将休字显示为红色,将班字显示为蓝色,这里我们可以使用条件格式。
在【开始】选项卡,单击【条件格式】,选择新建规则。
注意:活动单元格是 F7,就是下图中左边红框的位置(活动单元格指 Excel 表格中处于激活状态的单元格。可以是正在编辑的,也可以是选取的范围中的)。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/824a195f67b50762f96f38572d80cb63.jpg)
选择【使用公式确定要设置格式的单元格】,输入为=F7="休",格式设置为红色加粗字体,确定。
此时单元格中是「休」字的都显示为红色加粗字体。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/fc534dc9f627047b5ed67ef19954b87a.jpg)
同理,输入为=F7="班",格式设置为蓝色加粗字体,确定。
此时单元格中是「班」字的都显示为蓝色加粗字体。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/374f018e08f2336d03ac56029a1cfbdf.jpg)
▋不在当前月份的日期不显示
如下图,我们可以看到,不在同个月份的日期依旧显示,这显然并不是我们想要的。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/e2326e1d55d7f8d90c3cc77f466329b8.jpg)
按住【Ctrl】键,选中指定区域,点击【条件格式】-【新建规则】。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/d1b224196278d082e0651e30d41fe295.jpg)
选择【使用公式确定要设置格式的单元格】,输入:
=month(E7)ltgt$F$4
格式中字体设置为白色,单击【确定】按钮。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/0001542ea00be3f21990dbc5df7821f8.jpg)
此时,我们所想要的效果就完成了,要想更美观的话,我们可以把农历单元格(如 Q8:R8 等)进行合并单元格操作。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/3872989ded8bb25b758bfaa9232f1bf8.jpg)
04 延伸拓展
你以为这就结束啦?当然还没有。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/d897ae8641488306a31cf3ba91b4074b.jpg)
由于不同人的休假,还有调班时间不同,我们可以在参数表中对应位置选择好对应状态,日历表中就可以自动更新。
对于特殊日子,我们也可以在参数表中输入,比如某某日是你的生日,日历表中也会自动更新。
我们也可以在日历下面添加备注,如下图所示,然后就可以直接进行打印啦。
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/1809401a474180447444674986031311.jpg)
案例中的日历中使用的都是主题色,所以我们也可以通过更改主题色,变身成为另外的样子!
05 写在最后
最后,总结一下: 本文介绍了日历表更高级的做法,具备农历加休班状态,公式自动更新的日历。
制作日历外观确定好日历所需要的行列数,根据自己的需求制作好外观。
设置公式日期数的确定:利用当前月的 1 号前一个星期日数来定位日期首个单元格。
休班状态的查找:使用 vlookup 函数进行查找。农历的查找:使用 vlookup 函数进行查找。
设置格式针对休班状态出现的 0 值,我们可以使用数字格式将 0 值进行隐藏,文中我们使用的;;;@
利用条件格式,将不属于当月的日期进行隐藏。
简单的进行延伸拓展由于本文的日历表是用公式制作的,所以比较灵活,我们可以根据自己的需求,再 DIY 制作属于自己的日历表。
当然啦,日历的玩法不局限这些,大家可以开开自己的脑洞,做出更多好玩的日历!!
最后我想说,2022 新年快乐鸭~对于新的一年大家有什么期待,或者对过去的 2021 有什么想说的,留言区可以与大家一起聊聊哦~
![这年头,Excel都可以DIY日历了,你不会还不知道吧](/storage/article/20240125/e8c123eb9f332c77d6823bd00fb15ae7.jpg)
本站涵盖的内容、图片、视频等数据系网络收集,部分未能与原作者取得联系。若涉及版权问题,请联系我们删除!联系邮箱:ynstorm@foxmail.com 谢谢支持!
大家好,2022 年都已经过去 4 天了,大家去年的目标都完成了吗?
新的一年需要新的日历,最近我看到好多人在晒他们收到的新年日历。 然鹅,我没收到,不开心~
这时,坐在电脑前的我看到了 Excel,突然想到,我可以给自己做个日历啊,既能省钱,还能按照自己的心意 DIY!!!
如下图: 通过控件切换月份,日历中的日期,农历,以及休班状态自动更新!
是不是特别炫酷?
接下来,我就来揭开这份日历表的层层面纱。
01 制作外观
首先我们要制作日历表的外观,在日历表中,每个格子是三行两列的,行格为 6,列格为 7。
E3 单元格为 2022,对应数字格式为 0 年,E4 单元格为 1,对应数字格式为 0 月。
事先准备好参数表。
▋插入控件
在【开发工具】选项卡下,点击【插入】-【数值调节钮】。
右键控件,选择【设置控件格式】。
设置对象窗口中,最小值为 1,最大值为 12,单元格链接为 F4。(月份为 1-12,所以最小值为 1,最大值为 12)
PS. 如果没有【开发工具】选项卡,在【文件】-【选项】-【自定义功能区】中调出。
基本的外观制作好后,下面就是编写公式啦。
02 编写公式
如下图,在 E7 单元格中输入如下公式:
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7</span></code>
如下图,在 F7 单元格中输入如下公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,4,0),""),"")
如下图,在 E8 单元格中输入如下公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,3,0),""),"")
最后填充公式就可以啦~ 如动图所示,选择 E7:F9,向右向下填充公式。
小 tips: E7 单元格函数公式:
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7
其中前面一半公式是求:当前月 1 号上一个星期日的日期。
=DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)
后半段则是求:当前月 1 号上一个星期日的,也就是前半段公式得出的日期。 需要加上多少间隔数,才能得出当前单元格的日期。
=INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7
比如说,2022 年 1 月 1 号,就是当前月 1 号前一个周日的日期(2021 年 12 月 26 号)+6。
F7 单元格函数公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,4,0),""),"")
如下图,如果是左边的日期是本月的日期,则使用 vlookup 函数查找对应日期的对应休班状态,否则显示为空。
E8 单元格函数公式:
=IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,参数表!$A:$D,3,0),""),"")
如果是上边的日期是本月的日期,则使用 vlookup 函数查找对应日期的对应日,否则显示为空。
公式设置完后,下一步就是要「装饰」日历表啦~
03 设置格式
▋设置休班状态格式
让 0 不显示
填充完公式后,我们发现状态单元格中有很多 0,影响表格的美观程度,所以要让这些 0 都不显示。
这要怎么做呢?我们可以使用自定义数字格式。 按住【Ctrl】键,选中需要设置的单元格区域,再按住快捷键【Ctrl+1】弹出【设置单元格格式】对话框。
然后在类型中输入;;;@,单击【确定】按钮,此时除了文本,所有的 0 都显示为空白了。
小 Tips:在数字格式中,正数格式;负数格式;零值格式;文本格式。
;;;@ 其实就是就是让数值都不显示,文本显示本身,@ 符号代表文本本身的字符。 设置休班颜色
将休字显示为红色,将班字显示为蓝色,这里我们可以使用条件格式。
在【开始】选项卡,单击【条件格式】,选择新建规则。
注意:活动单元格是 F7,就是下图中左边红框的位置(活动单元格指 Excel 表格中处于激活状态的单元格。可以是正在编辑的,也可以是选取的范围中的)。
选择【使用公式确定要设置格式的单元格】,输入为=F7="休",格式设置为红色加粗字体,确定。
此时单元格中是「休」字的都显示为红色加粗字体。
同理,输入为=F7="班",格式设置为蓝色加粗字体,确定。
此时单元格中是「班」字的都显示为蓝色加粗字体。
▋不在当前月份的日期不显示
如下图,我们可以看到,不在同个月份的日期依旧显示,这显然并不是我们想要的。
按住【Ctrl】键,选中指定区域,点击【条件格式】-【新建规则】。
选择【使用公式确定要设置格式的单元格】,输入:
=month(E7)ltgt$F$4
格式中字体设置为白色,单击【确定】按钮。
此时,我们所想要的效果就完成了,要想更美观的话,我们可以把农历单元格(如 Q8:R8 等)进行合并单元格操作。
04 延伸拓展
你以为这就结束啦?当然还没有。
由于不同人的休假,还有调班时间不同,我们可以在参数表中对应位置选择好对应状态,日历表中就可以自动更新。
对于特殊日子,我们也可以在参数表中输入,比如某某日是你的生日,日历表中也会自动更新。
我们也可以在日历下面添加备注,如下图所示,然后就可以直接进行打印啦。
案例中的日历中使用的都是主题色,所以我们也可以通过更改主题色,变身成为另外的样子!
05 写在最后
最后,总结一下: 本文介绍了日历表更高级的做法,具备农历加休班状态,公式自动更新的日历。
制作日历外观确定好日历所需要的行列数,根据自己的需求制作好外观。
设置公式日期数的确定:利用当前月的 1 号前一个星期日数来定位日期首个单元格。
休班状态的查找:使用 vlookup 函数进行查找。农历的查找:使用 vlookup 函数进行查找。
设置格式针对休班状态出现的 0 值,我们可以使用数字格式将 0 值进行隐藏,文中我们使用的;;;@
利用条件格式,将不属于当月的日期进行隐藏。
简单的进行延伸拓展由于本文的日历表是用公式制作的,所以比较灵活,我们可以根据自己的需求,再 DIY 制作属于自己的日历表。
当然啦,日历的玩法不局限这些,大家可以开开自己的脑洞,做出更多好玩的日历!!
最后我想说,2022 新年快乐鸭~对于新的一年大家有什么期待,或者对过去的 2021 有什么想说的,留言区可以与大家一起聊聊哦~
本站涵盖的内容、图片、视频等数据系网络收集,部分未能与原作者取得联系。若涉及版权问题,请联系我们删除!联系邮箱:ynstorm@foxmail.com 谢谢支持!