Day 28
一、日期函数
1.常用日期函数
-
DATE(): 根据年、月、日三个数值返回其表示的日期
例:
函数:=DATE(2011,4,5)
显示效果:2011/4/5 -
DATEVALUE(): 返回由文本字符串表示的日期的十进制数字
例:
函数:=DATEVALUE(“2022年3月4日”)
显示效果:44624 -
TIME(): 根据时、分、秒三个数值返回其表示的时间的十进制格式
例:
函数:=TIME(10,30,52)
显示效果:10:30AM -
TIMEVALUE(): 返回由文本字符串表示的时间的十进制数字
例:
函数:=TIMEVALUE(“12:00:00”)
显示效果:0.5 -
DAY(): 返回序列数表示的某月的天数
例:
函数:=DAY(“2022/10/5”)
显示效果:5 -
MONTH(): 返回序列数表示的某年的月份
例:
函数:=MONTH(“2022/10/5”)
显示效果:10 -
YEAR(): 返回序列数表示的年份
例:
函数:YEAR(“2022/10/5”)
显示效果:2022 -
SECOND(): 返回时间值的秒数
例:
函数:=SECOND(13:32:10)
显示效果:10 -
MINUTE(): 返回时间值的分钟数
例:
函数:=MINUTE(13:32:10)
显示效果:32 -
HOUR(): 返回时间值的小时数
例:
函数:=HOUR((13:32:10)
显示效果:13 -
TODAY(): 返回当前日期
例:
函数:=TODAY()
显示效果:2022/8/24 -
NOW(): 返回当前日期和时间
例:
函数:=NOW()
显示效果:2022/8/24 19:29 -
EDATE(): 返回与某个日期相隔N个月的日期的序列数
例:
函数:=EDATE(“2022年8月24日”,1)
显示效果:2022/9/24 -
EOMONTH(): 返回与指定日期相隔N个月份数的月份的最后一天
例:
函数:=EOMONTH(“2022年3月4日”,1)
显示效果:2022/4/30 -
DATEDIF(): 计算两个日期之间的天数、月数、年数
例:
2.日期函数练习
二、逻辑函数
-
AND(): 用于测试所有条件是否均为TRUE
例:
函数:=AND(72>=60,25>=60)
显示效果:FALSE -
OR(): 用于测试是否有为TRUE的条件
例:
函数:=OR(72>=60,25>=60)
显示效果:TRUE -
NOT(): 将TRUE或者False的结果取反
例:
函数:=NOT(25>60)
显示效果:FALSE -
TRUE(): 返回TRUE
-
FALSE(): 返回FALSE
-
IF(): 判断单个条件是否为真,If(条件,条件成立对应的结果,条件不成立对应的结果)
例:
函数:=IF(72>=60,“及格”,“不及格”)
显示效果:及格 -
IFS(): 检查是否满足一个或多个条件,且返回第一个符合条件的值
-
IFS(条件1,结果1,条件2,结果2,条件3,结果3,…)
-
注意:后面的条件是在前面的条件不成立的时候判断的
例:
单元格j2 = 85
函数:=IFS(J2>=85,“优秀”,J2>=70,“良好”,J2>=60,“一般”,J2<60,“不及格”)
显示效果:优秀 -
IFERROR(): 使用 IFERROR 函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。
例:
函数:=IFERROR(FIND(“a”,“bbc”),“未找到”)
显示效果:未找到 -
SWITCH(): 根据值列表计算一个值,并返回与第一个匹配值对应的结果,如果不匹配,则可能返回可选默认值。
-
SWITCH(运算表达式,值1,结果1,值2,结果2,值3,结果3,…)
-
计算运算表达式,计算结果如果是值1,最后的结果就是结果1,如果计算结果是值2,最后结果就是结果2,…
单元格j2 = 85
函数:=SWITCH(INT(J10/10),9,“A”,8,“B”,7,“C”,6,“D”,“E”)
显示效果:B
2.相关练习
答案:
气费:=E12*$B
4
电费:
=
I
F
S
(
D
12
<
=
240
,
D
12
∗
4 电费:=IFS(D12<=240,D12*
4电费:=IFS(D12<=240,D12∗B
5
,
D
12
<
=
400
,
240
∗
5,D12<=400,240*
5,D12<=400,240∗B
5
+
(
D
12
−
240
)
∗
5+(D12-240)*
5+(D12−240)∗C
5
,
D
12
>
400
,
240
∗
5,D12>400,240*
5,D12>400,240∗B
5
+
160
∗
5+160*
5+160∗C
5
+
(
D
12
−
400
)
∗
5+(D12-400)*
5+(D12−400)∗D
5
)
水费
:
′
=
I
F
S
(
C
12
<
120
,
C
12
∗
5) 水费:'=IFS(C12<120,C12*
5)水费:′=IFS(C12<120,C12∗B
6
+
C
12
∗
6+C12*
6+C12∗E
6
+
C
12
∗
6+C12*
6+C12∗F
6
,
C
12
<
=
176
,
120
∗
6,C12<=176,120*
6,C12<=176,120∗B
6
+
(
C
12
−
120
)
∗
6+(C12-120)*
6+(C12−120)∗C
6
+
C
12
∗
6+C12*
6+C12∗E
6
+
C
12
∗
6+C12*
6+C12∗F
6
,
C
12
>
176
,
120
∗
6,C12>176,120*
6,C12>176,120∗B
6
+
56
∗
6+56*
6+56∗C
6
+
(
C
12
−
176
)
∗
6+(C12-176)*
6+(C12−176)∗D
6
+
C
12
∗
6+C12*
6+C12∗E
6
+
C
12
∗
6+C12*
6+C12∗F$6)
3.判断是否为闰年
三、查找与引用函数
1.常用函数
- COLUMN(): 返回与列号对应的数字
- ROW() :返回与行号对应的数字
UNIQUE(去重对象,行列去重方式,去重方式)
去重对象 - 提供需要去重的一行或者一列数
行列去重方式 - 去重对象是一列数据按行去重(FALSE-默认)、去重对象是一行数据按列去重(TRUE)
去重方式 - FALSE(默认),返回原数据去掉重复数据后的结果;TRUE,直接返回原数据中没有没有重复项的数据
- SORT() 对某个区域或数组的内容进行排序
- SORT(排序序列,排序依据对应的列号,排序方式)
1)排序序列 - 需要排序的对象
2)排序依据对应的列号 - 排序时比较大小对象在排序序列中的位置,如果不辅助,默认按照选中数据的第一列数据的大小排序
3)排序方式 - 1表示升序(默认) -1表示降序
也可连同学生姓名一起排序,输入第二参数为需要排序的两列或者2均可。
-
SORTBY() 对某个区域按照某列数据进行排序(默认升序)
-
SORTBY(排序序列,第一排序标准,排序方式,第二排序标准,排序方式,第三排序标准,排序方式3,…)
-
注意:排序标准是提供比较对象对应的一列数据
-
UNIQUE(去重对象,行列去重方式,去重方式)
-
去重对象 - 提供需要去重的一行或者一列数
-
行列去重方式 - 去重对象是一列数据按行去重(FALSE-默认)、去重对象是一行数据按列去重(TRUE)
-
去重方式 - FALSE(默认),返回原数据去掉重复数据后的结果;TRUE,直接返回原数据中没有没有重复项的数据
-
LOOKUP() 在一行或者一列中查找某个值并从另一行或者列中找到同位置的值
-
LOOKUP的用法:LOOKUP(查找对象,查找对象在原数据中所在的列,查找结果所在的列)
-
注意:使用LOOKU做数据查询的时候必须保证查询数据所在的列中,查找对象前面的数据必须是有序的(升序或者降序)
-
VLOOKUP() 按行查找表格或区域内容
-
VLOOKUP的用法:VLOOKUP(查找对象,同时包含查找对象和结果的序列,结果在前面给的序列中所在的列号,查找方式)
-
查找方式:
TRUE - 近似匹配,在查找对象所在的列本身有序的时候使用
FALSE - 精确匹配,在查找对象所在的列本身无序的时候使用
-
XLOOKUP() 按行查找表格或区域内容
-
XLOOKUP的用法
XLOOKUP(查找对象,查找数据所在的列,查找结果所在的列)
XLOOKUP(查找对象,查找数据所在的列,查找结果所在的列, 查找失败返回的默认值)
-
HLOOKUP() 在表格的首行或数值数组中搜索值,然后返回表格或数组中所在列的指定行中的值
-
注意:如果结果所在的列在查找对象所在的列表的前面,VLOOKUP无法查找