熟练运用excel函数公式是提高办公效率、增强业务解决能力的重要方法,也是提高职场竞争力的有效途径。本号精选62个excel常用函数,结合示例详细解析功能和用法,助力提高函数应用水平,欢迎关注收藏。
第二篇:SUMPRODUCT、AND、OR、IF函数
SUMPRODUCT函数
功能:返回数组或区域乘积的和。
语法:SUMPRODUCT(arr1,arr2,arr3, ...)
说明: 数组参数arr1,arr2,arr3, ...必须具有相同的维数,比如arr1为A1:A10,维数是10,arr2为B1:B8,维数是8,维数不同,则返回结果为错误值 #VALUE!。函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
示例:
1、实现分类汇总透视表功能
上图采购登记表需要按物品进行分类汇总,excel的透视表能很方便的实现,但其有个不足之处是源数据更改后,结果不自动更新,需右键刷新,或者用VBA代码解决,不太方便。其实SUMPRODUCT函数就能实现和透视表一样的分类汇总功能,而且结果还能自动更新。下面是两种实现效果对比:
其中H列物品1数量求和公式为:=SUMPRODUCT(($C$3:$C$10=G5)*$D$3:$D$10)
I列物品1数量求和公式为:=SUMPRODUCT(($C$3:$C$10=G5)*$F$3:$F$10)
加入日期条件,如要求得到2019-2-2日的分类汇总结果,见下图:
H5单元格公式:=SUMPRODUCT(($B$3:$B$10=$H$1)*($C$3:$C$10=G5)*$D$3:$D$10)
I5单元格公式:=SUMPRODUCT(($B$3:$B$10=$H$1)*($C$3:$C$10=G5)*$F$3:$F$10)
2、统计次数和分类编号功能
要求对采购登记信息按物品名称统计各自发生的采购次数,和按采购物品品名编分类序号,如下图:
G列为按品名分别编分类序号,G3单元格公式:=SUMPRODUCT((C$3:C3=C3)*1)
I列为按品名统计发生的采购次数,I5单元格公式:=SUMPRODUCT(($C$3:$C$10=H5)*1)
不过这个功能用COUNTIF函数更简单,用COUNTIF时
G3单元格公式:=COUNTIF(C$3:C3,C3)
I5单元格公式:=COUNTIF($C$3:$C$10,H5)。
COUNTIF函数以后再详细讲解,这里提到只为开拓思路,让大家知道,很多函数功能是可以相互替代实现的,遇到问题时某个对应的函数一时想不起来,可以试着考虑其他方法,不要死磕。
AND函数
功能:返回参数运算结果,当所有参数的逻辑值为真时,返回TRUE;只要有一个参数的逻辑值为假,即返回FALSE。
语法:AND(log1,log2, ...)
说明:如果参数中包含文本或空白单元格,则这些值将被忽略。如果参数内包括非逻辑值,则 返回错误值 #VALUE!。
示例:
根据学生成绩表,标记出各科成绩都合格的名单。
F3单元格公式:=IF(AND(B3>=60,C3>=60,D3>=60),"都及格