excel制作万年历查询表（用Excel做一个动态万年历）

说到用Excel制作万年历，很多小伙们们都学得太难了。

虽然 Excel提供了各式各样的日历模板，需要时可搜索选择使用。

但是，这些模板有一个共同点，就是公式冗长复杂，难以理解，这让很多想要参考模板自行制作个性化万年历的小伙伴们望而却步。

例如，在“具有季节性插图的任意年份日历 1”模板中，全年日历由每月一个（共 12 个）工作表组成，无法通过一个工作表查询所有月份日期。

同时，计算日期的公式中嵌套了多个函数，表达式的逻辑思路也比较复杂，如下图所示。而且，每月日历的公式都略有不同，不利于批量复制粘贴。

那么，有没有什么简便方法制作万年历了，答案是肯定的。

只要你善于转换思路，理清日期和星期之间的逻辑关系，就会发现其实在Excel中制作动态万年历真的So easy！

只需运用4 2个函数就能够轻松实现。“4 2”是指在表格格中运用4个常用函数设置公式，以及在条件格式中运用2个函数设置公式。

4个函数分别是WEEKDAY、DATE、TODAY 和VLOOKUP 函数。

另外在“条件格式”中将要运用的2个函数是 OR 和EOMONTH 函数。这些函数都是常用的简单函数，将要设置的公式也非常简短、浅显易懂。操作步骤如下。

借鉴上图模板格式绘制框架→在 B1 单元格中输入当前日期“2019-11-28”→设置单元格格式为“日期”-“2012 年3月”，B1单元格中即显示“2019年11月”→在B3:H3 单元格区域中依次输入数字 1~7 →设置单元格格式为“日期”-“星期三”，如下图所示。

在 K1:O2 单元格区域添加辅助表→设置字段名称和单元格格式→分别在 K2 和 L2单元格中输入年份“2019”和月份“11”→分别在 M2、N2、O2 和 B1 单元格中设置以下公式。

•M2 单元格：“=DATE(K2,L2,1)”，将 K2 和 L2 单元格中的年份、月份及数字“1”组合成标准日期。

•N2 单元格：“=WEEKDAY(M2)”，计算 M2 单元格中日期的星期数。

•O2 单元格：“=N2-1”，计算第 1 日的星期数与星期日之间的差距天数。数字“1”代表每星期的第一天，即星期日。

•B1 单元格：“=M2”，直接引用 M2 单元格中的日期。

辅助表效果如下图所示。

插入两个【数值调节钮】窗体控件，用于控制和调节年份和月份。分别按照左下图和右下图所示设置控件格式。

分别在日历中的以下单元格中设置公式，计算日期。

•B4 单元格：“=$B$1-$O$2”，用 B1 单元格中的日期数“2019-11-1”减去 O2 单元格中的差距天数即可得到星期日的日期为“2019-10-27”→将单元格的格式自定义为“d”，仅显示日期数。

•C4 单元格：“=B4 1”→向右填充公式至 D4:H4 单元格区域。

•B6 单元格：“=H4 1”，将 H4 单元格中的本月第 1 个星期的最后一天日期加 1，即得到第 2个星期第 1 天的日期。

•C6 单元格：“=B6 1”→向右填充公式至 D6:H6 单元格区域，逐日累加第 2 个星期每天的日期。按照上述方法填充 B8:H8、B10:H10、B12:H12 和 B14:H14 单元格区域公式，效果如下图所示。

运用“条件格式”工具对 B4:H14 单元格区域设置以下格式，发挥不同效果。设置方法如下表 所示。

日历中同样设置公式提示申报截至“倒计时”的提示。在 D2 单元格中输入 12 月申报截止日期→在以下单元格中设置公式或自定义格式。

•D1单元格：“=TODAY()”→单元格的自定义格式为“今天是 : m 月 d 日 星期 aaa”，同时显示日期和星期数。

•G1单元格：“=IF(D2-D1<0," 已截止 "," 倒计时 :"&D2-D1&" 天 ")”，计算倒计时天数，并返回指定结果。

动态万年历的最终效果如下图所示。

动态万年历就这样轻松完成了，是不是非常简单？下面测试动态查询效果：将K2和L2单元格中的年份和月份设置其他数字，如“2021”和“1”，代表查询2021年1月的日历。可看到日历显示如下图所示。

说明一点：我们添加辅助表的目的是为了简化公式，便于大家理解。如果小伙伴们能够熟练运用函数设置嵌套公式，可以将辅助表中的公式与万年历中的公式嵌套组合。