常用函数
(一)日期与时间
1. 了解Excel 的日期、时间系统
◎Excel 将日期和时间存储为可用于计算的序列号。Windows 系统下的Excel 默认使用“1900年系统”:1900年1月1日的序列号是1,而1900年1月2日的序列号是2,以此类推,直至9999年12月31日的序列号是2958465。
◎Excel 将时间也记录成为具有唯一性的序列号。把24小时设为1,可细分至秒。 例如,上午10点17分58秒的序列号可以这样求得:
10点,10/24=0.416666667(保留9位小数,下同)
17分,17/(24*60)= 0.011805556
58秒,58/(24*60*60)= 0.000671296
——加在一起得到0.429143519。
序号 函数名 含义 序号 函数名 含义
1 DATE 返回给定日期的序列号 11 NOW 返回当前系统时间
2 DATEVALUE 返回给定文本日期的序列号 12 SECOND 返回给定时间对应的秒数值
3 DAY 返回给定日期对应的日数值 13 TIME 返回给定时间的序列号
4 DAYS360 按照一年 360 天返回两个给定日期间相差的天数 14 TIMEVALUE 返回给定文本时间的序列号
5 EDATE 返回给定日期在若干个月之前(后)的对应日期 15 TODAY 返回当前系统日期
6 EOMONTH 返回给定日期之前(后)若干个月的月末日期 16 WEEKDAY 返回给定日期的星期数
7 HUOR 返回给定时间对应的小时数值 17 WEEKNUM 返回给定日期为一年中第几周
8 MINUTE 返回给定时间对应的秒数值 18 WORKDAY 返回给定日期之前(后)若干个工作日的日期
9 MONTH 返回给定日期对应的月份数值 19 YEAR 返回给定日期对应的年份数值 10 NETWORKDAYS 返回两个给定日期之间的工作日数 20 YEARFRAC 返回两个给定日期之间天数占全年天数的百分比
2. 日期与时间函数(全部重点讲解)
(1)返回序列号:DATA 、DATEVALUE 、TIME 、TIMEVALUE
①日期与时间函数——返回序列号(DATE )
◎返回给定日期的序列号。
◎DATE (year,month,day )
●year - 代表年份的四位数字,取值范围1900-9999。可以是数值、单元格引用或者公式。
如果year 介于0到1899之间(包含这两个值),系统会将该值与1900相加来计算年份。例如,DATE (111,5,2) 将返回2011年5月2日(1900+111)。
如果year 小于0或大于等于10000,系统将返回错误值 #NUM!。
●month- 代表月份的整数,取值范围为1-12,可以是数值、单元格引用或者公式 如果month 大于12,则给定年份加1,月份为month 值减去12。例如,DATE (2010,17,8)
返回表示2011年5月8日的序列号。
如果month 小于1,则给定年份减1,月份为12减去month 的绝对值。例如,DATE (2011,-3,5) 返回表示2010年9月5日的序列号。
●day - 代表日的整数,取值范围为1-31 ,可以是数值、单元格引用或者公式。
如果day 大于指定月份的天数,则day 从指定月份的第一天开始累加该天数。例如,DATE (2011,1,38) 返回表示2011年2月7日的序列号。
如果day 小于1,则day 从指定月份的第一天开始递减该天数,然后再加上 1 天。例如,DATE (2008,1,-15) 返回表示2007年12月16日的序列号。
◎如果在输入该函数之前单元格格式为“常规”, Excel 会将单元格格式更改为与“控制面板”的区域日期和时间设置中指定的日期和时间格式相同的格式。则系统将自动将函数结果用日期格式显示,而不是显示数字格式的序列号。若要显示序列号,只需将单元格格式设成数字格式。
②日期与时间函数——返回序列号(DATEVALUE )
◎返回给定文本日期的序列号。
◎DATEVALUE (date_text)
date_text-采用Excel 日期格式的日期文本,或者是包含采用Excel 日期格式文本的单元格引用。
date_text必须表示1900年1月1日到9999年12月31日之间的某个日期。如果参数date_text的值超出上述范围,则函数返回错误值 #VALUE!。
如果省略参数date_text中的年份部分,则函数会使用计算机系统的当前年份;如果省略日,则视为1日;如果date_text中还包括时间信息,则该信息将被忽略,仅返回日期序列号。
◎如果工作表包含采用文本格式的日期并且要对这些日期进行筛选、排序、设置日期格式或执行日期计算,则DATEVALUE 函数将十分有用。
③日期与时间函数——返回序列号(TIME )
◎返回给时间的序列号。
◎TIME (hour,minute,second )
hour-代表小时的整数,取值范围为0-23,可以是数值、单元格引用或者公式。
大于23将除以24,将余数视为小时。例如,TIME (27,0,0)=TIME(3,0,0) =.125或3:00AM。
minute-代表分的整数,取值范围为0-59,可以是数值、单元格引用或者公式。
大于59将被转换为小时和分钟。例如,TIME (0,750,0)=TIME(12,30,0)=.520833或12:30PM。
second-代表秒的整数,取值范围为0-59,可以是数值、单元格引用或者公式。
大于59将被转换为小时、分钟和秒。例如,TIME (0,0,2000)=TIME(0,33,22)=.023148或12:33:20 AM。
以上三个参数小于零都会返回错误#NUM!。
④日期与时间函数——返回序列号(TIMEVALUE )
◎返回给定文本日期的序列号。
◎TIMEVALUE (time_text)
Time_text-采用 Excel 时间格式的时间文本,或者是包含采用 Excel 时间格式文本的单元格引用。
如果省略参数date_text中的秒数据,系统会把空白秒视为0秒,但如果省略小时或者分钟数据,则会返回错误值 #VALUE!。
如果参数 time_text 给定的文本系统不能明确判断为时间,则也会返回错误值 #VALUE!。
如果参数 time_text 中还包括日期信息,则该信息将被忽略,仅返回时间序列号。
(2)日期与时间函数——提取给定日期、时间中的数值:YEAR 、MONTH 、DAY 、HOUR 、MINUTE 、SECOND
◎YEAR 返回给定日期对应的年份数值。YEAR (serial_number)
◎MONTH 返回给定日期对应的月份数值。MONTH (serial_number)
◎DAY 返回给定日期对应的日数值。DAY (serial_number)
Serial_number给定的日期,可以是日期或日期的序列号,单元格引用或结果是日期、日期序列号的函数或公式。
◎HOUR 返回给定时间对应的小时数值。HOUR (serial_number)
◎MINUTE 返回给定时间对应的分钟数值。MINUTE (serial_number)
◎SECOND 返回给定时间对应的秒数值。SECOND (serial_number)
Serial_number-给定的时间,可以是时间或时间的序列号,单元格引用或结果是时间、时间序列号的函数或公式。