简单EXCEL万年历制作实例
按照百度文库里的一个教程做了个万年历成品,结果不让上传,只好也做个教程了,练练日期函数和逻辑函数。同时在原作的基础上,最大限度的采用了函数的形式,减少隐藏行列带来的不便。原作网址:。
运行环境office 2007
(1)首先做好框架,摆好布局,可自由发挥。
主要有五个部分:
今天的年月日(B1),星期(E1),时间(G1),日历界面(B3:H9),查询界面(B11:H11)
首先,合并B1、C1,放年月日的地方
其次,合并G1,H1,放北京时间用
然后,绘制出如下图B3:H9的日历框架
最后,合并B11:D11,在F11和H11分别补上年月,作出查询年月位置
(2)现在开始核心制作
第一部分:年月日(B1)
=TODAY()
第二部分:星期(E1)
=IF(WEEKDAY(B1,2)=7,"星期日","星期"&TEXT(WEEKDAY(B1,2),"[dbnum1]"))
-----------------------------------------------------------------------------------------------------------
注:如果WEEKDAY(B1,2)=7成立,输出:星期天;否则,输出:星期+返回值。[dbnum1]范围值为中文小写。即如阿拉伯数字“6”转换为“六”
语法:WEEKDAY(serial_number,return_type)
返回某日期为星期几。默认情况下,其值为 1(星期天)到 7(星期六)之间的整数。
Return_type 返回的数字
1 或省略 数字 1(星期日)到数字 7(星期六),同 Microsoft Excel 早期版本。
2 数字 1(星期一)到数字 7(星期日)。
3 数字 0(星期一)到数字 6(星期日)。
语法:TEXT(value, format_text)
value 必需。数值、计算结果为数值的公式,或对包含数值的单元格的引用。
format_text 必需。使用双引号括起来作为文本字符串的数字
格式
pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载
,例如,"m/d/yyyy"
-----------------------------------------------------------------------------------------------------------
第三部分:时间(G1)
=TEXT(NOW(),"北京时间:"&"hh:mm")
-----------------------------------------------------------------------------------------------------------
注:NOW()范围的有年月日+时间,通过TEXT转换成hh:mm形式
-----------------------------------------------------------------------------------------------------------
第四部分:查询窗口
日历界面为最复杂环节,放在最后,同时首先完成查询窗口也方便于日历界面的制作
这里用到数据的有效性,对年月的数据进行限制,设置如下图,此为年,月雷同。
第五部分:日历界面
(1)在B4输入
=IF(IF(WEEKDAY(DATE($E$11,$G$11,1),2)=7,"星期日","星期"
&TEXT(WEEKDAY(DATE($E$11,$G$11,1),2),"[dbnum1]"))=B3,1,0)
-----------------------------------------------------------------------------------------------------------
注:没有使用辅助数据,函数嵌套的比较眼花
第一个IF(由内到外)
如果查询的X年X月1号=7,返回“星期日”,否则返回“星期+中文小写”
第二个IF
如果第一个IF返回值和B3相等,刚输出1,否则0
-----------------------------------------------------------------------------------------------------------
(2)在C4输入
=IF(B4>0,B4+1,IF(IF(WEEKDAY(DATE($E$11,$G$11,1),2)=7,"星期日","星期"
&TEXT(WEEKDAY(DATE($E$11,$G$11,1),2),"[dbnum1]"))=C3,1,0))
-----------------------------------------------------------------------------------------------------------
注:如果B4>0即=1,则C4=B4+1=2,否则同B4方式判断,相同返回输出
-----------------------------------------------------------------------------------------------------------
(3)复制公式或用填充柄,将C4公式复制到H4
(4)因行4必有>0,一个月最少28,B8未必有日子,故B5:H7只需一个比一个大一
B5输入:=H4+1,填充B6,B7
C5输入:=B5+1,填充C5:H7
(5)B8输入
=IF(IF($G$11=2,IF(OR($E$11/400=INT($E$11/400),AND($E$11/4=INT($E$11/4),$E$11/100<>INT($E$11/100))),29,28),IF(OR($G$11=4,$G$11=6,$G$11=9,$G$11=11),30,31))>H7,H7+1,0)
-----------------------------------------------------------------------------------------------------------
其中蓝色部分是通过闰年和大小月判断查询月份的天数,如果天数>H7,则B8=H7+1
-----------------------------------------------------------------------------------------------------------
(6)C8输入
=IF(IF($G$11=2,IF(OR($E$11/400=INT($E$11/400),AND($E$11/4=INT($E$11/4),$E$11/100<>INT($E$11/100))),29,28),IF(OR($G$11=4,$G$11=6,$G$11=9,$G$11=11),30,31))>B8,IF(B8>0,B8+1,0),0)
并将公式复制或填充剩下单元格,注意B9是和H8相比,见下
=IF(IF($G$11=2,IF(OR($E$11/400=INT($E$11/400),AND($E$11/4=INT($E$11/4),$E$11/100<>INT($E$11/100))),29,28),IF(OR($G$11=4,$G$11=6,$G$11=9,$G$11=11),30,31))>H8,IF(H8>0,H8+1,0),0)
-----------------------------------------------------------------------------------------------------------
比上面多了一个条件,由于B8可能为0,所以B8不为0时,即>0,才能+1
-----------------------------------------------------------------------------------------------------------
(3)装饰美化
设置单元格行高,列宽,字体,背景,成品如图:
再美观些可利用原作那里的“高级选型”,不显示0,也可适当改变公式,将返回值0,改为””,判断是否>0,改为<>””v即可,如图
例如:
C4函数:
=IF(B4<>"",B4+1,IF(IF(WEEKDAY(DATE($E$11,$G$11,1),2)=7,"星期日","星期"
&TEXT(WEEKDAY(DATE($E$11,$G$11,1),2),"[dbnum1]"))=C3,1,""))
C8函数
=IF(IF($G$11=2,IF(OR($E$11/400=INT($E$11/400),AND($E$11/4=INT($E$11/4),$E$11/100<>INT($E$11/100))),29,28),IF(OR($G$11=4,$G$11=6,$G$11=9,$G$11=11),30,31))>B8,IF(B8<>"",B8+1,""),"")