sumifs sumproduct countif countifs
语法解释
-
条件区域:碰到数字型文本时,运用减负运算得出筛选结果 --left(“13759649670”,2)结果为13(把文本型变成数值型)
区域引用可结合indirect函数使用 -
条件:必需。用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数。例如,条件可以表示为 32、">32"、、B5、“32”、“苹果” 或 TODAY()或者()或者**">"&max(B2:B15)**(错误写法:">max(B2:B15)")
-
条件区域有时会用large和small函数
写法
-
条件求和:
- sumif(条件区域,条件,求和区域)
- sumifs(求和区域,条件区域1,条件1,条件区域2,条件2)
- sumproduct((条件区域1=条件1)(条件区域2=条件2)(条件区域3=条件3)*(求和区域或者计数区域))
sumif/sumifs有时可代替Vlookup函数进行查询,index也可,indirect也可,offset也可
,公式更加灵活,而且可以省略当员工不存在时的错误判断。应用条件:查询的区域中关键字不重复时(如员工姓名)
由于各个月份的销售明细表结构一致,而且数据连续存放,因此不必对各月份求和之后再进行汇总,而只需要使用一个公式即可完成计算。
=sumif(C3:I11,“B*”,D3:J11)
可以简写为 =sumif(C3:C11,“B*”,D3)(即只写左上角单元格)
-
条件计数:
- countif(条件区域,条件)
- countifs(条件区域1,条件1,条件区域2,条件2) 与数据透视表也可换用
应用:
- 判断满足指定条件的记录在表中是否存在
- 判定指定区域不重复数据的个数(单条件或者多条件)
- 统计指定条件的数据个数
判断员工是否重复(可将公式运用到数据有效性,避免录入重复)
筛选不重复记录-----运用下列公式后,筛选出结果为1的商品名称
3.条件平均
averageif
“>=”&average() 这是正确的
“>=average()” 这是错误的
sumproduct
原材料采购动态汇总示例
=SUMPRODUCT(SUMIF($B$10:$B$14,$A$3:$A$7,$C$10:$C$14)*($B$3:$J$7=$G10),$C$3:$K$7)
sumproduct条件需用left(A1,len(A1))=’‘李’’
但是sumifs就可以将条件设置为’’‘李’’&"*"
详见17.3 多条件统计员工工资表数据。可以自己再实践一次sumproduct