当前位置:首页>维修大全>综合>

如何用Excel制作日历

如何用Excel制作日历

更新时间:2023-09-21 23:03:35

如何用Excel制作日历

如何用Excel制作日历?

用Excel制作日期是一个比较有挑战的问题,这个问题涉及到Excel中多个函数的应用。下面我就分享一下我制作的日历,希望有兴趣的小伙伴们跟我交流。

首先介绍一下在制作这个日历的过程中,我都使用了哪些Excel工具:

数据验证:在这里用于制作下拉选择列表

文本日期转换数值日期的函数:DATEVALUE

星期函数(weekday)

文本函数:TEXT、LEFT、RIGHT、MID、LEN

条件判断函数:IF,IFERROR

设置单元格格式--日期格式

设置条件格式

废话补多少,进入正题。

制作日历模板和年月列表

第一步:画一个如下图所示的模板,大家可以根据自己的喜好设置格式。

第二步:设置模板中的星期

在显示“星期”的行输入序列数字“2,3,4,5,6,7,1”。

选中该区域,调出“设置单元格格式”对话框--选择“日期”--选择“类型”为“周三”得到如下效果。

第三步:制作“年”和“月”的下拉列表

如下图所示,我在L列和M列分别将“年”和“月”列出。

按下图所示,选中显示年份的单元格--点击“数据”选项卡下的“数据验证”--选择“允许”为“序列”,来源为刚刚输入的年份列表区域--点击确定。

用同样的方法设置月份的下拉列表。

最后将年份和月份的列表区隐藏起来。

根据选择的“年”、“月”确定当月“1日”是星期几和最后一天是几号

如下图所示,在辅助区域分别输入公式:

公式1:=DATEVALUE(LEFT($C$1,4)&"/"&MID($D$1,1,LEN($D$1)-1))

公式2:=WEEKDAY($J$4)

公式3:=TEXT(EOMONTH($J$4,0),"d")*1

公式解析:

公式1:

DATEVALUE函数用于将文本格式的日期转换成数值格式的日期。

LEFT函数:在这里用于从C1单元格的值“2019年”中截取“2019”。

MID函数:在这里用于从D1单元格的值“6月”中截取“6”。

把LEFT函数和MID函数截取的值用“&”连接符连接起来并在中间插入“/”符号,并用DATEVALUE函数转换成数字类型的日期后就得到了如:201961这样的日期。

在Excel中如果你只输入了年和月,并将该单元格的格式设置为日期格式,那么该单元格会自动将该日期指向该月的1号。

公式2:

WEEKDAY($J$4):用于根据J4单元格的日期是星期几。

公式1和公式2可以嵌套在一起使用。

公式3:

EMOHTH函数:在这里用于根据J4单元格的值确定该月的最后一天是几号。

TEXT函数:在这里用于从EMONTH函数获取的值中提取最后的日期。

*1:在这里将TEXT函数的值转换为数值类型。

开始日历的制作

经过前面的准备工作后,我么现在可以开始日历的制作了。

第一步:确定当月一号在日历模板中的位置。

这一步最麻烦,虽然我们已经知道了“1号”是星期几,但我们现在需要在日历模板中的正确的位置将其显示出来,这就需要进行判断。

在“周一”对应的A3单元格输入公式:=IF(A2=$J$5,1,"")。这个公式是用于判断“周一”所在的单元格的值与J5单元格的值是不是相等,如果相等则返回“1”(说明1号时星期一),不相等则返回空值。

在“周二”对应的单元格B3输入公式:=IFERROR(IF(A3<>"",A3+1,IF(B2=$J$5,1,"")),"")。这个公式首先判断A3单元格是不是1,如果是1则返回A3的值“+1”;如果A3单元格的值不是“1”,则返回公式:IF(B2=$J$5,1,"")(这个公式的判断方式与A3单元格的公式一样)的返回值。

IFERROR函数的作用是当IF函数的返回值是错误值时,保证返回的是空值。

设置好单元格的引用方式,然后将B3单元格的公式向后拖动的G3(周日)单元格。

第二步:利用公式生成后续日期

如下图所示,在A4单元格里输入公式:=G3+1,在B4单元格里输入公式:A4+1,这两个公式应该很好理解,及不做解释了。

将A4单元格的公式向下拖动,将B4单元格的公式向右拖动然后再向下拖动填充。

第三步:把错误的日期清理掉

如上图所示,6月份只有30天,而上图中却显示到了“37”,所以需要将多出来的部分给清理掉。

我们将后面两行的公式进行修改:

在A7单元格输入公式:=IFERROR(IF((G6+1)>$J$6,"",(G6+1)),""),向下拖动填充。

这个公式用于判断"G6+1"的值是否大于“J6”单元格的值(当月的最后一天),如果大于"J6"的值则返回空值,如果不大于则返回“G6+1”。

在B7单元格输入公式:=IFERROR(IF((A7+1)>$J$6,"",(A7+1)),""),向右向下拖动填充。这个公式的判断原理跟上一个公式一样。

设置完成后,就不会再显示大于当月最后一天的日期了。

将当天的日期突出显示

如下图所示,选中模板中的数值区域,点击“开始”选项卡下的“条件格式”按钮,在弹出的下拉菜单中选择“新建规则”。

在弹出的“新建规则”对话框中选择“使用公式确定要设置格式的单元格”。

在下面的输入区输入公式:=DATEVALUE(LEFT($C$1,4)&"/"&MID($D$1,1,LEN($D$1)-1)&"/"&A3)=TODAY(),这个公式用于判断模板中的日期与TODAY函数的值是否相等。

点击格式,根据需要设置格式的样式,后点击确定。

好啦,我的日历表基本制作步骤就是这样,小伙伴们可以根据自己的喜好对表格的样式进行设置,最好对表格中的进行保护设置,以免别人不小心把你辛苦设置的公式给弄没了。

我是Exce大白,欢迎大家跟我交流Excel的使用技巧和心得。

更多栏目