摘 要 :该文以使用Excel函数制作月历为例,介绍了8个Excel常用函数,展示了Excel的强大功能.本实例既可以显示当月的月历,还可以查询任意年月所属的月历.同时,特殊的节日还会显示相应的提醒文字.
关 键 词 :月历;函数;条件;Excel
中图分类号:TP37 文献标识码:A 文章编号:1009-3044(2014)09-2063-03
Excel函数是指软件内部预先定义的特殊公式,它可以对一个或多个数据进行操作,并有返回值,它包括函数名、参数、括号三个部分.
1.构建框架
1.1基础表格
新建一个如图1的工作表,输入文本,并设置单元格的字体、填充颜色等属性.
1.2创建下拉列表
在I1、I2中分别输入1900、1901,然后选中I1、I2,用“填充柄”向下拖拉至I151,输入1900―2050年份序列.同样,在J1至J12中输入1―12月份序列.选中D14,执行“数据―有效性”命令,点击“允许”的下拉按钮,选择“序列”,在“来源”中输入“I1:I151”,确定.用同样的方法,将F14数据来源设为J1至J12.于是,当选中D14或F14时,会出现下拉按钮,点击下拉按钮,即可选择年份或月份.
2.函数、公式及操作
2.1获取系统日期
2.1.1 TODAY()函数
TODAY()函数可以提取当前系统的日期,此函数无参数.如果系统日期发生改变,按F9即可更新数据.
2.1.2 实现获取系统日期
合并C2、D2,输入公式:等于TODAY(),执行“单元格格式-数字-分类-日期”命令,在右侧“类型”中设置一种日期格式.
2.2 星期判断
2.2.1 WEEKDAY()函数
WEEKDAY(serial_number,[return_typel])函数可判断指定日期是星期几.其中serial_number参数表示指定的日期或引用含有日期的单元格,return_typel参数是可选项,代表星期的表示方式,当此参数缺省值为1.该参数为1时,星期日为1、星期六为7;该参数为2,星期一为1、星期日为7;该参数为3时,星期一为0、星期日为6.
2.2.2 DATE()函数
DATE(year,month,day)函数可获取指定年月的第几天.其中year参数为指定的年份(小于9999);month参数为指定的月份数值(可大于12);day参数为指定的天数.
2.2.3 IF()函数
IF(Logical,VT,VF)函数可根据给定条件进行判断,为真则执行VT运算,否则执行VF运算.其中Logical参数代表逻辑判断表达式,结果为真或假;VT缺省值为“TRUE”; VF缺省值为“FALSE”.
2.2.4绝对引用和相对引用
引用是指复制已定义好的公式或函数,如果将地址的行号或列标前加“$”号则表示“绝对引用”,复制时不会发生改变,而没有加“$”号的地址为“相对引用”,复制时会动态变化.
2.2.5实现星期判断
选中F2,输入公式:等于IF(WEEKDAY(c2,2)等于7,"日",WEEKDAY(c2,2)).此公式表示,如果C2中当前日期的星期是“7”,则在F2中显示“日”,否则,直接显示出星期的数值.
选中B3,输入公式:等于IF(WEEKDAY(DATE($D$14,$F$14,1),2)等于B4,1,0).选中B3,将上述公式复制到C3至H3中.此公式表示,如果指定年月的第1天的星期数与B4相同,在B3中显示“1”,否则显示“0”.此操作为下一步判断“查询年月的第一天为星期几”获取一个对照值.
2.3获取系统时间
2.3.1NOW()函数
NOW()函数主要功能是提取当前系统日期和时间,该函数无参数.如果系统日期发生改变,按F9即可更新数据.
2.3.2用NOW()函数获取系统时间
选中H2,输入公式:等于NOW().执行“单元格格式-数字-分类-时间”命令,在右侧“类型”中选择一种时间格式.
2.4 获取月份天数
2.4.1 OR()函数
OR(log1,log2, ...,logN)函数表示各参数间“或”的关系,返回结果为逻辑值,仅当所有参数值均为逻辑“假”时,返回的函数结果为逻辑“假”,否则都返回逻辑“真”.其中logN为表达式或逻辑值,N的最大值为30.如果指定的逻辑条件参数结果为非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”.
2.4.2 AND()函数
AND(log1,log2, ...,logN)函数表示各参数间“且”的关系,返回结果为逻辑值,仅当所有参数值均为逻辑“真”时,返回的函数结果为逻辑“真”,否则都返回逻辑“假”.其参数及错误提示同OR()函数.
2.4.3 INT()函数
INT (num)函数是将参数向下取整为最接近的整数,不四舍五入.其中num表示需要取整的数值或引用单元格.
2.4.4实现获取月份天数
在单元格A14中输入如下公式等于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,F14=11),30,31))
此公式用于计算F14中的“月份”所对应的天数为28、29、30还是31天.如果 “月份”为“2”时,如果“年份”能被400整除,或能被4整除但不能被100整除,则该月为29天,否则为28天.如果“月份”不为2,而是4、6、9、11时,则该月为30天,否则为31天. 3 显示日期
3.1显示第一行日期
选中B8,输入公式:等于IF(B3等于1,1,0).
选中C8,输入公式:等于IF(B8>0,B8+1,IF(C3等于1,1,0)).将C8中的公式复制到D8至H8中.
3.2显示第二到四行日期
选中B9,输入公式:等于H8+1.将B9中的公式复制到B10、B11中.
选中C9,输入公式:等于B9+1.将C9中的公式复制到C10、C11中.同时选中C9至C11,将其中的公式复制到D9至H11中.
3.3显示第五、第六行日期
选中B12,输入公式:等于IF(H11>等于A14,0,H11+1).
选中C12,输入公式:等于IF(B12>等于$A$14,0,IF(B12等于0,0,B12+1)).将C12中的公式复制到D12―H12和C13中,再把C13复制到H13中.
选中B13,输入公式:等于IF(H12>等于A14,0,IF(H12等于0,0,H12+1)).
4.后期制作
4.1判断节日
合并B5至H5用于显示节日.
以判断教师节和国庆节为例,在B5中输入公式:等于IF(AND(MONTH(C2)等于9,DAY(C2)等于10),"教师节",IF(AND(MONTH(C2)等于10,DAY(C2)等于1),"国庆节",0))即可实现判断教师节和国庆节的功能.
4.2表格保护
4.2.1锁定
执行“右键全选设置单元格格式―保护―锁定”命令将表格全部锁定.用同样的方法把D14和F14解锁.隐藏无关的单元格.
4.2.2保护
执行“工具―保护―保护工作表”命令,输入.这样,整个工作表中除了D14和F14中的内容可以改变外,其它单元格中的内容均不能输入和更改.
5.结束语
图2是完成的可查询月历.在制作月历的过程中,Excel函数的使用,使我充分体会到了Excel的强大功能.依赖Excel函数,Excel软件为用户提供了一个简易快速的数据统计、数据分析平台.