今天给大家分享一下使用Excel纯函数制作Excel万年历。先给大家看一下效果。

制作这个万年历需要如下工具：

函数：DATA,LEFT,WEEKDAY

名称管理器：需要指定名称方便操作

开发控件：数据开发钮（用于选择年份）

数据有效性：用来选择初始星期一或者星期日

一、确定日历范围

我们参考日历：每个月为5行7列，那么我们以一个月为一个单位确定日历的大小范围。

所以我们可以基本确定日历的区域为：

表头：4行

日历范围：

行数：（5行+1行（月份栏位））*（12个月/4个月）=18行

列数：4（四个月）*7（星期七天）+4（月份之间间隔）=32列

表尾：4行

二、设定周始日

数据——数据验证——数据验证——选择序列——来源（星期一，星期日）如图1所示：

三、自定义名称

选择公式——名称管理器——新增如下三个名称

日历年份：=日历!$X$3

星期日期和周：={0,1,2,3,4,5,6} + {0;1;2;3;4;5}*7

周始日：=日历!$AE$1

操作如图2所示：

依次添加三个名称。

四、使用控件设置可以调节的日历年份

开发工具——数值调节钮——右键设置控件格式

最小值：1900

最大值：2999

步长：1

单元格链接：日历年份（上一步定义的名称）

操作如图3：

五、开始制作日期

星期公式：=LEFT（TEXT（B7,"aaa"）,1)）

日期公式：{=星期日期和周+DATE（日历年份，1,1）-WEEKDAY(DATE(日历年份，1,1），（周始日=“星期一”）+1）+1}

提示：

日期公式为数组函数，故需要全选区域，然后输入公式，Ctrl+Shift+Enter组合键完成填充。

六、设置条件格式

为了将非本月的日期显示为灰色，使用条件格式。

选中区域——开始——条件格式——使用公式确定要设置格式的单元格：

=MONTH(B7)<>MONTH($B$5)

提示：日期部分单元格格式使用自定义格式：d

月份部分单元格格式使用自定义格式：m"月”

再对日历头尾最终效果如下：