用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的数值字体颜色设定为背景色进行隐藏。

2、点击条件格式→突出显示单元格规则→重复值，作用在于结合公式将没有29日（平年2月），30日（闰年2月）、31日（4月、6月9月、11月）的月份的数值进行隐藏。

举个例子，平年的2月是28天，D8=IF(AND(MOD($A$1,4)<>0,$B$1=2),29,"")这个公式的作用是将29日显示出来，如前所说，此区域内如果有重复值（出现2个29）的话，那么数字颜色会变成背景色，其他三个公式作用雷同。

这样，我们只通过对A1、B1右侧滚动条的调整就获得了2008-2015年任意月份的日历。

当然，动点心思修改一下，可以把它修改为带农历日期且能突出显示当前日期的日历表。

一、将滚动条删除，修改A1、B1、C1单元格公式为：
A1 =INT(TEXT(TODAY(),"yyyy"))
B1 =INT(TEXT(TODAY(),"mm"))
C1 =INT(TEXT(TODAY(),"d"))
其他单元格公式不变。

二、全选A2:G8，设定条件格式→使用公式确定要设置格式的单元格，在公示输入区域输入：=A3=INT(TEXT(TODAY(),"d"))，然后点击格式按钮，设置填充效果如下：
三、选择D1:G1，点击合并后居中按钮，输入公历转农历公式：
="阴历"&MID("庚辛壬癸甲乙丙丁戊己
",MOD(TEXT(TODAY(),"[$-130000]e"),10)+1,1)&MID("申酉戌亥子丑寅卯辰巳午未
",MOD(TEXT(TODAY(),"[$-130000]e"),12)+1,1)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(TEXT(TODAY(),"[dbnum1][$-130000]年M月"&IF(-TEXT(TODAY(),"[$-130000]d")<-10,,"初")&"D 日"),"年一月","年正月"),"二十日",1),"二十","廿"),1,"二十日")
最终效果显示为：
这样，一个带显示农历日期且能突出显示当前日期的日历表就搞定了。