目录
7. Excel的函数-动态函数
7.1 FILTER
【FILTER】函数=FILTER(数据区域,筛选条件,[无满足条件的记录时返回的值])
7.1.1 单条件筛选
获得湖北区的相关数据
=FILTER(A2:G30,C2:C30=“湖北区”)
wps会产生数据溢出的情况
7.1.2 返回选定的列
如果需要返回其中的几行,只需要将数据区域修改为自己需要的区域即可
现在需要知道湖北区的最大销售金额是多少
=MAX(FILTER(F2:F30,C2:C30=“湖北区”))
7.1.3 多条件筛选
FILTER函数中的多条件筛选,使用+
来表示或,使用*
表示且
例如,需要计算湖北区、湖南区大最大销售金额,那么函数公式为
=MAX(FILTER(F2:F30,(C2:C30=“湖北区”)+(C2:C30=“湖南区”)))
例如,需要计算湖北区商品2的最大销售金额,那么函数公式为
=MAX(FILTER(F2:F30,(C2:C30="湖北区")
*(D2:D30="商品2")))
7.2 SUMIF函数
7.2.1 案例1
现在有几名同学的考试成绩,现在想统计数学成绩在90分以上的成绩之和
第一个参数为条件区域,因为要判断数学成绩,所以这里选择B列,当然这里也可以修改为实际的区域B2:B12
第二个参数为判定条件,判定条件为大于等于90
第三个参数为实际求和区,这是选填参数
7.2.2 案例2
现在想统计小米系列的产品销售额是多少?
7.2.3 案例3
7.2.4 案例4
现在需要统计语文成绩在80分以上,90分以下同学的数学成绩之和
【SUMIFS函数】=SUMIFS(实际求和区域,条件区域1,条件1,条件区域2,条件2)
7.3 SUBTOTAL函数
Excel中有数据筛选非常好用,那么,如果想统计筛选后的结果应该怎么弄?例如想知道筛选后的销售总额
使用筛选选择不同的区,而SUM求和并没有变化
【SUBTOTAL函数】=SUBTOTAL(功能函数,选择区域)
数字 1-11 或 101-111,用于指定要为分类汇总使用的函数。 如果使用 1-11,将包括手动隐藏的行,如果使用 101-111,则排除手动隐藏的行;始终排除已筛选掉的单元格。
=SUBTOTAL(9,F2:F21)
index+match
=INDEX(E3:E9,MATCH(H3,B3:B9,0))。
提取出生年月
text+mid
或者ctrl+e
计算年龄
datedif
对成绩进行排名
公式的前半部分(C$3:C$9>C3)返回的是一个数组,区域C$3:C$9中大于C3的单元格个数。后半部分COUNTIF(C$3:C$9,C$3:C$9)可以理解为:*1/COUNTIF(C$3:C$9,C$3:C$9),公式COUNTIF(C$3:C$9,C$3:C$9)返回的值为1,只是用于辅助计算。*所以上述公式也可以简化为:=SUMPRODUCT((C$3:C$9>C3)1)+1。
if函数
https://github.com/datawhalechina/free-excel