统计函数
1.计数函数(COUNT)
返回A1-A5的数量
1.1条件计数:COUNTIF
1.1.1按照条件计数
用法:COUNTIF(范围,条件)
1.1.2判断异同
用法:COUNTIF(列1,列2)
用于判断两列对应位置数据是否相同
1.1.3按照指定条件计数(和其他函数结合)
=COUNTIF(B1:B6,">"&AVERAGE(B1,B6))
用法:COUNTIF(范围,条件&其他函数)
一定要用&将其他函数连接否则无效
1.1.4和通配符配合使用
1.2多条件计数:COUNTIFS
注:多条件同时满足计数
=COUNTIFS(C1:C6,"*苹果*",B1:B6,"<=200")
用法:COUNTIFS(区域,条件,区域,条件)
1.2.1和通配符配合使用
=COUNTIFS(C1:C6,"??",B1:B6,">"&AVERAGE(B1:B6))
"??":两个字的搜索条件
1.3计算非空值数量:COUNTA
1.4计算空值数量:COUNTBLANK
2.平均值函数(AVERAGE)
2.1AVERAGE使用注意事项
=AVERAGE(G1:G6)
注:AVERAGE函数会自动忽略文本
2.2单条件求平均值:AVERAGEIF
=AVERAGEIF(D2:D7,D2,E2:E7)
用法:AVERAGEIF(条件范围,条件,数值范围 )
2.3多条件求平均值:AVERAGEIFS
=AVERAGEIFS(E2:E7,D2:D7,D2,E2:E7,">"&F2)
用法:=AVERAGEIFS(数值范围,条件范围1,条件,条件范围2,条件)
2.4计算返回给定参数(包括数字、文本和逻辑值)的平均值:AVERAGEA
注:AVERAGEA不会计算空值
3.最大值 LARGE&MAX
区别:MAX只能返回最大值,LARGE可以返回某一个最大值
用法:MAX(区域)
LARGE(区域,名次)
例:返回第二个最大值
4.最小值 SMALL&MIN
用法与最大值相同
4.1带文本和逻辑值的最小值 :MINA
MINA与MAXA用法相同:可以对文本值求最大最小值
MIN与MAX用法相同
5. 中位数MEDIAN
6.众数MODE
用法:MODE(区域)
查找和引用函数
1.查找列号COLUMN
用法:column(位置)
位置如果不填默认当前位置
1.1查找一片区域的位置
提示:使用这种方法设置的序号会随着删除而更新
2.查找列号ROW
3.匹配MATCH
用法:MATCH(查找值,查找范围)
4.LOOKUP函数
使用要求:行和列的内容必须是有序的,lookup_vector必须是一个单行或单列的连续区域,并且其中的值必须按升序或降序排列。
用法:LOOKUP(查找内容,查找区域,结果区域)
小贴士:下拉列表方法
数据--数据验证--序列--选中区域
5.VLOOKUP函数
纵向查找,以行为单位一行一行寻找
重点:查询值所在列必须在查询结果所在列的前面
用法:VLOOKUP(查找值,查找范围,相对查找值列的列号,查找方式)
- range_lookup:查找方式
- FALSE或者0:精确匹配,匹配的时候查找的时候和查找值一样的值。(使用精确匹配的时候查找值所在的列中的数据可以无序)。
- TRUE或者1:近似匹配(模糊匹配),匹配的时候如果找不到相同的值,就找所有比查找值小的所有值中的最大的那个。(使用模糊匹配的时候,查找值所在的列中的数据必须从小到到排序)。
6.HLOOKUP函数
横向查找:按照一列一列进行查找
用法:HLOOKUP(查找值,查找范围,相对查找值行的行号,查找方式)
小贴士:绝对引用和相对引用
相对引用是指,当把公式复制到其它单元格中时,行号和列号会根据实际的单元格相应的改变。
相对引用 :行号或者列号不发生改变