用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,"二十日")
最终效果显示为：
这样，一个带显示农历日期且能突出显示当前日期的日历表就搞定了。
如何用Excel制作实时更新的日历
首先做好这样的一个模板，接下来我们要制作一个日历表，随着不同的年份月份呈现出对应的日期。
在A1合并单元格输入公式：=TEXT(TODAY(),"yyyy年M月") 让它只显示年份和月份，然后在A2：G2输入星期
在A3单元格输入公式：=COLUMN(B1)+(ROW(1:1)-1)*7-WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()),1)) 往下拖拉填充
选中A3：G8 设置条件格式单元格值小于或等于0 时设置字体为白色
再次选中A3：G8区域设置条件格式-使用公式设置格式输入公式：=DAY(TODAY())=A3 当前电脑日期等于区域中单元格的日期时设置单元格底纹填充蓝色、字体白色
然后再次选中A3：G8区域设置条件格式输入公式：=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))<A3 当前月份最末尾日期小于区域中单元格的数字时设置白色字体
好了，现在日历表就已经做好了，再对它的外观进行美化。
当我们的日
期发生改变时，我们制作的日历表也会跟随着变化。
用EXCEL制作万年历
用EXCEL制作一份万年历本文所涉及到的函数有：1、AND (logical1,logical2, ...)2、DATE (year,month,day)3、DAY (serial_number)4、IF (Logical,Value_if_true,Value_if_false)5、INT (number)6、MONTH (serial_number)7、NOW ()8、OR (logical1,logical2, ...)1、启动EXCEL2003，新建一个工作表，取名保存（如万年历.xls），并在相应的单元格中，输入如图1所示的文本。
2、同时选中B1、C1、D1单元格，按“格式”工具栏上的“合并及居中”按钮，将其合并成一个单元格，并输入公式：=TODAY（）。
选中B1（合并后的）单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），在“数字”标签中的“分类”下面选中“日期”选项，再在右侧“类型”下面选中“二○○一年三月十四日”选项，“确定”退出，将日期设置成中文形式。
注意：TODAY（）函数用于提取当前系统日期，请将系统日期一定要调整准确哟。
3、选中F1单元格，输入公式：=IF(WEEKDAY(B1,2)=7,"日",WEEKDAY(B1,2))；选中H1单元格，输入公式：=NOW（）。
选中F1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“特殊”选项，再在右侧“类型”下面选中“中文小写数字”选项，“确定”退出，将“星期数”设置成中文小写形式；选中H1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“时间”选项，再在右侧“类型”下面选中一款时间格式，“确定”退出。
注意：①上述前面一个公式的含义是：如果（IF）当前日期（B1）是星期“7”（WEEKDAY(B1,2)=7），则在F1单元格中显示“日”，否则，直接显示出星期的数值（WEEKDAY(B1,2)）。
excel表格如何做时间表
竭诚为您提供优质文档/双击可除excel表格如何做时间表篇一：如何用excel表格制作日历今天我们介绍用excel制作万年历的方法。
这个万年历可以显示当月的月历，还可以随意查阅任何日期所属的月历，非常方便。
如果你愿意，还可以让它在特殊的日子里显示不同的提醒文字，一起来试试吧！本文所涉及到的函数有：1、and(logical1,logical2,...)2、date(year,month,day)3、day(serial_number)4、iF(logical,Value_if_true,Value_if_false)5、int(number)6、month(serial_number)7、now()8、oR(logical1,logical2,...)1、启动excel20xx，新建一个工作表，取名保存（如万年历.xls），并在相应的单元格中，输入如图1所示的文本。
2、同时选中b1、c1、d1单元格，按“格式”工具栏上的“合并及居中”按钮，将其合并成一个单元格，并输入公式：=today（）。
选中b1（合并后的）单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），在“数字”标签中的“分类”下面选中“日期”选项，再在右侧“类型”下面选中“二○○一年三月十四日”选项，“确定”退出，将日期设置成中文形式。
注意：today（）函数用于提取当前系统日期，请将系统日期一定要调整准确哟。
3、选中F1单元格，输入公式：=iF(weekday(b1,2)=7,"日",weekday(b1,2))；选中h1单元格，输入公式：=now（）。
选中F1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“特殊”选项，再在右侧“类型”下面选中“中文小写数字”选项，“确定”退出，将“星期数”设置成中文小写形式；选中h1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“时间”选项，再在右侧“类型”下面选中一款时间格式，“确定”退出。
用Excel做电子万年历
用Excel制作电子万年历先看一下最终效果（秀一下萌娃（*^_^*））：本电子万年历的功能包括：1.自动显示当月日历，突出显示当日日历；2.显示当月所有周别；3.显示当月所有节日和特殊日子（可自定义）；4.可查询年月，包括该月的节日和特殊日子；5.具有个性化表盘时钟。
需要用到的Excel函数和符号有：if、year、month、today、day、date、weekday、text、DBNum、vlookup、iserror、weeknum、concatenate、mod、mid、now、minute、hour、$、>、&需要用到的Excel技巧有：1.插入背景；2.设置行高和列宽；3.输入有序文本；4.设置数据有效性；5.快速选择整列；6.设置条件格式；7.选择性粘贴；8.设置单元格格式；9.隐藏网格线；10.制作圆环图和饼图、保存和调用图表模板；11.图表的组合；12.冻结窗格；13.工作表加密制作步骤：第一步，设置万年历背景和区域：在“页面布局”选项卡选择“背景”，选择需要作为背景的图片，单击“插入”，插入后效果如图：在第一张图片中间选择一个25行*8列的区域作为日历区域（这里选择了C5:J29区域），框线按下图设置（也可以按照其他方式设置），并调整单元格的行高和列宽。
第二步，设置万年历标题：在“C6”（C6:H6单元格合并）单元格输入“电子万年历”；“C9”（C9:D9单元格合并）单元格输入“查询年月”；“F9”单元格输入“年”；“H9”单元格输入“月”；C11:J11单元格分别输入“周别”、“日”、“一”、“二”、“三”、“四”、“五”、“六”。
第三步，设置可查询的年月：在“N5”单元格输入“年份”，“O6”单元格输入“月份”；在“N6:N206”单元格分别输入年份“1900”、“1901”、“1902”……“2099”、“2010”（当然还可以往后输入更多）；在“O6:O17”单元格分别输入月份“1”、“2”、“3”……“11”、“12”。
如何用EXCEL表格制作日历
今天我们介绍用Excel制作万年历的方法。
这个万年历可以显示当月的月历，还可以随意查阅任何日期所属的月历，非常方便。
如果你愿意，还可以让它在特殊的日子里显示不同的提醒文字，一起来试试吧！本文所涉及到的函数有：1、AND (logical1,logical2, ...)2、DATE (year,month,day)3、DAY (serial_number)4、IF (Logical,Value_if_true,Value_if_false)5、INT (number)6、MONTH (serial_number)7、NOW ()8、OR (logical1,logical2, ...)1、启动EXCEL2003，新建一个工作表，取名保存（如万年历.xls），并在相应的单元格中，输入如图1所示的文本。
2、同时选中B1、C1、D1单元格，按“格式”工具栏上的“合并及居中”按钮，将其合并成一个单元格，并输入公式：=TODAY（）。
选中B1（合并后的）单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），在“数字”标签中的“分类”下面选中“日期”选项，再在右侧“类型”下面选中“二○○一年三月十四日”选项，“确定”退出，将日期设置成中文形式。
注意：TODAY（）函数用于提取当前系统日期，请将系统日期一定要调整准确哟。
3、选中F1单元格，输入公式：=IF(WEEKDAY(B1,2)=7,"日",WEEKDAY(B1,2))；选中H1单元格，输入公式：=NOW（）。
选中F1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“特殊”选项，再在右侧“类型”下面选中“中文小写数字”选项，“确定”退出，将“星期数”设置成中文小写形式；选中H1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“时间”选项，再在右侧“类型”下面选中一款时间格式，“确定”退出。
利用Excel制作万年历（图解）
利用Excel制作万年历（图解）步骤方法一：在C2中输入=TODAY（）回车。
TODAY（）函数用于提取当前系统日期。
在F2中输入=IF(WEEKDAY(C2,2)=7,"日",WEEKDAY(C2,2)) 回车。
在H2中输入 =NOW() 回车。
NOW（）用于提取当前系统时间。
二：第四步：在D14中设置下拉菜单，数值为1900—2029。
三：在F14中设置下拉菜单，数值为1—12。
四：将查询日期调为当前日期。
五：第一步：在A3中输入：=IF(F14=2,IF(OR(D14/400=INT(D14/400),AND(D14/4=INT(D14/ 4),D14/100<>INT(D14/100))),29,28),IF(OR(F14=4,F14=6,F14=9,F 14=11),30,31))回车。
第二步：B3中输入=IF(WEEKDAY(DATE($D$14,$F$14,1),2)=B4,1,0)回车。
选中B3向右复制公式至H3。
第三步：B6中输入=IF(B3=1,1,0)回车。
第四步：在B7中输入=H6+1回车，选中B7，向下复制公式至B9。
第五步：在B10中输入=IF(H9>=A3,0,H9+1)回车。
第六步：在B11中输入=IF(H10>=A3,0,IF(H10>0,H10+1,0))回车。
第七步：在C6中输入=IF(B6>0,B6+1,IF(C3=1,1,0))回车。
第八步：在C7中输入=B7+1回车。
第九步：选中C7向下复制公式至 C9。
第十步：在C10中输入=IF(B10>=$A$3,0,IF(B10>0,B10+1,IF(C6=1,1,0)))回车。
第十一步：选中C6，向右复制公式至H6。
然后进行如下操作：选中C7，向右复制公式至H7。
选中C8，向右复制公式至H8。
选中C9，向右复制公式至H9。
选中C10，向右复制公式至H10。
万年历
如何只用一个公式在Excel里制作一张自动更新年历（详解）February 2nd, 2010 by Ling Leave a reply »继续前几天没扯完的话题。
那天我是看了一篇关于在Excel制作一个日日更新的实用月历，但是文中用到的公式有点复杂。
突发奇想，花了点时间改进了一下公式，现在只需同一个公式，然后设置一下单元格的格式，就能生成一张自动更新的年历表。
说说它的功能先1. 自动生成当年的年历2. 高亮今日的日期3. 显示的日历格式与系统的语言相同。
中文系统显示中文的月份名字，如“一月2010”，英文如上，日文则会自动换成日文的星期名字，如“土曜日”。
阅读此文之前，大家可以先试试看这个日历。
纯Excel文件，没有任何宏命令，不过需要Office 2007以上版本的支持。
—————————————-Start—————————————-说明：此文涉及的软件是Office 2010 Beta，如果截图与你的电脑有出入的话，应该也不会有太大的障碍。
输入数据打开Office 2010，选择公式标签，名称管理器。
这里需要自己新建三个名称。
具体如何输入参看下图，注意输入的时候公式中间不能有空格。
1. Calendar =DaysAndWks+DateOfFirst-WEEKDAY(DateOfFirst,2)2. DateOfFirst =DATE(YEAR(NOW()),(INT((COLUMN()-1)/7)+1+INT((ROW()-1)/8)*4),1)3. DaysAndWks =MOD(COLUMN()-1,7)+(MOD(ROW()-1,8)-2)*7+(MOD(ROW(),8)=1)*21∙全选单元格，调整宽度为40。
∙选中A1:AB24这个区域。
∙按下F2建，输入=calendar，按Ctrl+回车。
∙然后表格应该就会被密密麻麻的数字填满了。
参看上面的小图。
到这里完成的话，你的年历已经完成了75%了，最后只是一个美化工作。
用EXCEL制作一份万年历
用EXCEL制作一份万年历今天我们介绍用Excel制作万年历的方法。
这个万年历可以显示当月的月历，还可以随意查阅任何日期所属的月历，非常方便。
如果你愿意，还可以让它在特殊的日子里显示不同的提醒文字，一起来试试吧！本文所涉及到的函数有：1、(logical1,logical2, ...)2、3、4、5、6、7、8、1、启动EXCEL2003，新建一个工作表，取名保存（如万年历.xls），并在相应的单元格中，输入如图1所示的文本。
2、同时选中B1、C1、D1单元格，按“格式”工具栏上的“合并及居中”按钮，将其合并成一个单元格，并输入公式：=TODAY（）。
选中B1（合并后的）单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图2），在“数字”标签中的“分类”下面选中“日期”选项，再在右侧“类型”下面选中“二○○一年三月十四日”选项，“确定”退出，将日期设置成中文形式。
注意：TODAY（）函数用于提取当前系统日期，请将系统日期一定要调整准确哟。
3、选中F1单元格，输入公式：=IF(WEEKDAY(B1,2)=7,"日",WEEKDAY(B1,2))；选中H1单元格，输入公式：=NOW（）。
选中F1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“特殊”选项，再在右侧“类型”下面选中“中文小写数字”选项，“确定”退出，将“星期数”设置成中文小写形式；选中H1单元格，打开“单元格格式”对话框，在“数字”标签中的“分类”下面选中“时间”选项，再在右侧“类型”下面选中一款时间格式，“确定”退出。
注意：①上述前面一个公式的含义是：如果（IF）当前日期（B1）是星期“7”（WEEKDAY(B1,2)=7），则在F1单元格中显示“日”，否则，直接显示出星期的数值（WEEKDAY(B1,2)）。
②上述第二个函数（NOW（））用于提取当前系统日期和时间，也请将系统日期和时间调整准确。
用Excel制作万年历的3种方法
Abstract：The calendar can be programmed in high-level language. Without an existing platform，the programming process is relative tedious. This paper introduces how to make a calendar without programming on the existing Excel platform by making use of functions provided by Excel，data formula and VBA development environment function to make a calendar，and compares it with the program⁃ ming method. The three methods are making a calendar by using date function，making a calendar by using array formula and making a calendar by using VBA development tool. The first two methods do not need programming，only use Excel function nested combination and function to complete the task，the third method is programmed in Excel's own VBA development environment. The results show that it is simpler and more efficient to make calendars with Excel than other high-level languages. Making a calendar on Excel platform is a tribute to Excel's powerful function，practicability and multiple ways to solve problems. Key Words：Excel date function；array formula；making calendar
Excel做个自动的万年历，这个操作实在是太溜了~
Excel做个⾃动的万年历，这个操作实在是太溜了~万年历。
是⽣活中⽐较常见的⼀个东西，在Excel中也能做出来同样的效果。
本期要点：条件格式，⽇期函数，控件，⾃定义格式。
具体效果如下图所⽰：对于上⾯的万年历，许多⼩伙伴们都问了，那怎么样才能做出这样的⼀个万年历呢，这个万年历是练习Excel的最好的素材，可以综合地练习函数，条件格式，控件、⾃定义格式等的协同应⽤。
下⾯⽼师和⼤家⼀起来学习万年历的制作。
01制作⽇历的框架在⼀个空⽩的⼯作表中制作⼀个空⽩的表格，做好颜⾊的美化。
如下图所⽰：02 01添加控件操作步骤：在相应的位置单击【开发⼯具】-【插⼊】，选择【数值调节钮】，然后拖放⿏标在相应的位置插⼊，然后右键单击【设置控件格式】，在打开的对话框中的【当前值】输⼊“2019”，【最⼩值】为1900，【最⼤值】为9999，【步长】为1，【单元格链接】为B1;同样地设置⽉份的调节按钮。
如下图所⽰：03 01VLOOKUP函数在B4单元格中输⼊公式：=IF(MONTH(DATE($B$1,$D$1,1)-WEEKDAY(DATE($B$1,$D$1,1),2) COLUMN(A:A)-1 ROW(1:1)*7-7)=$D$1,DATE($B$1,$D$1,1)-WEEKDAY(DATE($B$1,$D$1,1),2) COLUMN(A:A)-1 ROW(1:1)*7-7,'')按Enter键完成后下向下填充⾄整个⽇历表。
效果如下：选中区域B4:H9单元格，然后将单元格格式⾃定义为：dd。
如下图所⽰：选中区域B4:H9单元格，然后将单元格格式⾃定义为：dd。
如下图所⽰：05 01设置条件格式为了突出显⽰当前⽇期的单元格，所以要⾼亮显⽰，效果与具体操作如下：。