用EXCEL函数制作万年历

用EXCEL函数制作万年历

用EXCEL函数制作一个万年历就像在EXCEL中创建一个钟表图一样，用John Walkenbach 的话来说——其实没有任何理由，但创建有挑战性的工作表却是很有意义的。

用EXCEL函数制作万年历这个想法缘起因某种原因需要N年的月份日期排列，如若照抄系统日期未免显得技术含量太低，在EP和EH以及问问和微软EXCEL专区论坛上也没有发现类似可以借鉴的技术帖，一不做二不休，既然全世界都没有，那就自己动手来制作一个。

先来说下解决思路：用DATE()函数获得年月日返回一个数字代码，作为WEEKDAY()函数的参数返回一周中第几天的数值，此数值作为LOOKUP()函数的参数，检索出七种星期一排列类型中的一种，然后用MID()函数结合COLUMN()函数将这种类型中的字符提取后进行分散填充，用INT()函数将文本转化为数字，再根据第二列尾数将余下数值进行填充，用IF()、AND()、MOD()、OR()进行辅助性运算，用条件格式处理不合条件数据。

如上所述，需要用到：

一、11个EXCEL函数：分别是IF()、INT()、MID()、LOOKUP()、WEEKDAY()、DATE()、COLUMN()、ROW()、AND()、MOD()、OR()。各函数具体用法参照EXCEL帮助文件进行了解，在此不予赘述。

二、6个公式：

=IF(INT(MID(LOOKUP(WEEKDAY(DATE($A$1,$B$1,1),1),{1,2,3,4,5,6,7},{"1234567","0123456" ,"0012345","0001234","0000123","0000012","0000001"}),COLUMN(),1))=0,"",INT(MID(LOOKUP( WEEKDAY(DATE($A$1,$B$1,1),1),{1,2,3,4,5,6,7},{"1234567","0123456","0012345","0001234","00 00123","0000012","0000001"}),COLUMN(),1)))

=IF($G$3=1,(ROW()-4)*7+COLUMN()+1,IF($F$3=1,(ROW()-4)*7+COLUMN()+2,IF($E$3=1,(RO W()-4)*7+COLUMN()+3,IF($D$3=1,(ROW()-4)*7+COLUMN()+4,IF($C$3=1,(ROW()-4)*7+COLUMN( )+5,IF($B$3=1,(ROW()-4)*7+COLUMN()+6,IF($A$3=1,(ROW()-4)*7+COLUMN()+7,""))))))) =IF(AND(MOD($A$1,4)<>0,$B$1=2),29,"")

=IF(AND(MOD($A$1,4)<>0,$B$1=2),30,IF(AND(MOD($A$1,4)=0,$B$1=2),30,""))

=IF(AND(MOD($A$1,4)<>0,$B$1=2),31,IF(AND(MOD($A$1,4)=0,$B$1=2),31,""))

=IF(OR($B$1={4,6,9,11}),31,"")

三、2个条件格式：

重复值、单元格值>31。

操作步骤如下：

一、新建EXCEL文档，点击A1单元格，然后点击开发工具→插入表单控件→滚动条。调整合适大小和位置，然后右击，设置属性，弹出如下对话框进行设置如下图：

然后按照上述步骤对B2单元格进行调整。

备注：最大值最小值可以按照自己需要进行更改，步长设置为1。

二、选择A2到G8，对此区域进行美化处理。然后填充星期日至星球六到A2:G2区域，如下图：

三、在A3单元格输入公式：

=IF(INT(MID(LOOKUP(WEEKDAY(DATE($A$1,$B$1,1),1),{1,2,3,4,5,6,7},{"1234567","0123456" ,"0012345","0001234","0000123","0000012","0000001"}),COLUMN(),1))=0,"",INT(MID(LOOKUP( WEEKDAY(DATE($A$1,$B$1,1),1),{1,2,3,4,5,6,7},{"1234567","0123456","0012345","0001234","00 00123","0000012","0000001"}),COLUMN(),1)))，横向填充到G3单元格。

四、在A4单元格输入：

=IF($G$3=1,(ROW()-4)*7+COLUMN()+1,IF($F$3=1,(ROW()-4)*7+COLUMN()+2,IF($E$3=1,(RO W()-4)*7+COLUMN()+3,IF($D$3=1,(ROW()-4)*7+COLUMN()+4,IF($C$3=1,(ROW()-4)*7+COLUMN( )+5,IF($B$3=1,(ROW()-4)*7+COLUMN()+6,IF($A$3=1,(ROW()-4)*7+COLUMN()+7,"")))))))，然后填充句柄至G8。

五、删除D8到G8，此四个单元格依次输入以下公式：

D8=IF(AND(MOD($A$1,4)<>0,$B$1=2),29,"")

E8=IF(AND(MOD($A$1,4)<>0,$B$1=2),30,IF(AND(MOD($A$1,4)=0,$B$1=2),30,""))

F8=IF(AND(MOD($A$1,4)<>0,$B$1=2),31,IF(AND(MOD($A$1,4)=0,$B$1=2),31,""))

G8=IF(OR($B$1={4,6,9,11}),31,"")

以上四个公式是将每月29日（平年2月），30日（闰年2月）、31日（4月、6月9月、11月）根据要求显示出来，后面条件格式设定重复值隐藏数据中还要讲解到。

六、全选A2:G8，设定条件格式：

1、点击条件格式→突出显示单元格规则→大于，将大于31的数值字体颜色设定为背景色进行隐藏。