这年头,Excel都可以DIY日历了,你不会还不知道吧?! |
您所在的位置:网站首页 › excel制作日历 › 这年头,Excel都可以DIY日历了,你不会还不知道吧?! |
大家好,我是在研究新年日历的小爽~ 新的一年需要新的日历,最近坐在电脑前的我看到了 Excel,突然想到,我可以给自己做个日历啊,既能省钱,还能按照自己的心意 DIY!!! 如下图: 通过控件切换月份,日历中的日期,农历,以及休班状态自动更新! 是不是特别炫酷? 接下来,我就来揭开这份日历表的层层面纱。 在gong众号【秋叶Excel】后台回复: 虎虎 ,就可以获得这份日历表的源文件啦。 01 制作外观首先我们要制作日历表的外观,在日历表中,每个格子是三行两列的,行格为 6,列格为 7。 E3 单元格为 2022,对应数字格式为 0 年,E4 单元格为 1,对应数字格式为 0 月。 事先准备好参数表。(在gong众号【秋叶Excel】后台回复: 虎虎 ,就可以获取啦~) ▋插入控件 在【开发工具】选项卡下,点击【插入】-【数值调节钮】。 右键控件,选择【设置控件格式】。 设置对象窗口中,最小值为 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 如下图,在 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)$F$4格式中字体设置为白色,单击【确定】按钮。 此时,我们所想要的效果就完成了,要想更美观的话,我们可以把农历单元格(如 Q8:R8 等)进行合并单元格操作。 04延伸拓展你以为这就结束啦?当然还没有。 由于不同人的休假,还有调班时间不同,我们可以在参数表中对应位置选择好对应状态,日历表中就可以自动更新。 对于特殊日子,我们也可以在参数表中输入,比如某某日是你的生日,日历表中也会自动更新。 我们也可以在日历下面添加备注,如下图所示,然后就可以直接进行打印啦。 案例中的日历中使用的都是主题色,所以我们也可以通过更改主题色,变身成为另外的样子! 05写在最后最后,总结一下: 本文介绍了日历表更高级的做法,具备农历加休班状态,公式自动更新的日历。 ❶ 制作日历外观 确定好日历所需要的行列数,根据自己的需求制作好外观。 ❷ 设置公式 日期数的确定:利用当前月的 1 号前一个星期日数来定位日期首个单元格。休班状态的查找:使用 vlookup 函数进行查找。农历的查找:使用 vlookup 函数进行查找。 ❸ 设置格式 针对休班状态出现的 0 值,我们可以使用数字格式将 0 值进行隐藏,文中我们使用的;;;@利用条件格式,将不属于当月的日期进行隐藏。 ❹ 简单的进行延伸拓展由于本文的日历表是用公式制作的,所以比较灵活,我们可以根据自己的需求,再 DIY 制作属于自己的日历表。当然啦,日历的玩法不局限这些,大家可以开开自己的脑洞,做出更多好玩的日历!! 在公中号【秋叶Excel】后台回复 虎虎 ,就可以把这份日历拿走啦~ |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |