搜索你感兴趣的问题
SUMPRODUCT函数的经典用法
鱼戏断桥边 2024-06-01 16:54:42
推荐回答
鱼戏断桥边 2024-06-07 10:14:21

SUMPRODUCT函数是excel07版本后新增的一个函数,功能多样且强大,是excel中的一颗璀璨明星,今天我们来看一下这个函数的用法。
语法:=SUMPRODUCT(array1,array2,array3, .),Array为数组。
意思为在给定的几组数组中,然后把数组间对应的元素相乘,最后返回乘积之和。
SUM意思是求和,PRODUCT意思是求积,组合在一起的意思是乘积之和。
函数的几个经典用法:

操作方法

1、与SUM函数用法比较例:下图中求销售数量总和,SUMPRODUCT(B2:B11)=SUM(B2:B11)求销售总金额:=SUMPRODUCT(B2:B11*C2:C11)回车或者=SUM(B2:B11*C2:C11)按Ctrl+Shife+Enter三键结束

SUMPRODUCT函数支持数组运算,不需要按Ctrl+Shife+Enter三键结束,它的运算原理是当SUMPRODUCT函数的参数为两个数组时,中间可以用乘号也可以是逗号,也可以写成=SUMRPODUCT(B2:B11,C2:C11)数组之间对应元素相乘,再求和,上图中=SUMPRODUCT(B2:B11*C2:C11)=SUMPRODUCT(B2:B11,C2:C11)=B2*C2+B3*C3+B4*C4+······+B11*C11用乘号和用逗号的区别在与,当有一个数组中有文本时,中间用乘号得出错误值,因为文本是无法参与计算的,此时中间只能用逗号隔开,然后文本将会被当成0来处理;

求销售总量,输入=SUMPRODUCT(B2:B11,C2:C11)

2、隔列求和例:求1、2、3、4月份的计划数量之和,在N3输入=SUMPUDUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3),下拉填充。

释义:COLUMN函数,返回所选择的单元格的列数,如输入=COLUMN(B5),则得到结果为2,意思是B5所在的单元格是第二列,输入=COLUMN(G17),得到结果为7,意思是G17所在的单元格是第7列,COLUMN(B3:M3),意思是B3到M3所在的列数,得到结果{2,3,4,5,6,7,8,9,10,11,12,13}MOD函数,用来求余数的函数,返回两数相除的余数,输入=MOD(5,2),得到结果为1,意思是5除以2得到的余数为1,输入=MOD(17,3),得到结果为2,意思是17除以3得到的余数是2,MOD(COLUMN(B3:M3),3)=2,意思是B3:M3所在的列数除以3,得到余数为2的单元格,得到的结果是{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE},此中TRUE为真,符合结果,FALSE为假,不符合结果。在计算中TRUE当成1计算,FALSE按0计算计算式:=SUMPRODUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3)=SUMPRODUCT({1,0,0,1,0,0,1,0,0,1,0,0}*{68,81,13,70,83,13,107,71,-36,85,118,33})=330两个数组之间元素一 一对应相乘再求和。此例中还可以用SUMIF函数,N3中输入=SUMIF($B$2:$M$2,$K$2,B3:M3)下拉填充,注意B2:M2的绝对引用;如果没有“计划、实际、差异”所在的行(删除第二行),则不能用SUMIF函数,用SUMPRDUCT函数较好

3、多条件求和公式用法:=SUMPRODUCT(条件1*条件2*条件3*······条件N) ,公式中多个条件相乘。例:下图中求2017年3月2日宏基21吋电脑销售金额,输入=SUMPRODUCT((A2:A18=--"2017/03/02")*(B2:B18="电脑")*(C2:C18="宏基21吋")*F2:F18)日期前的双负号“--”是对逻辑值进行转换的

求2017年3月3日小米5.5吋手机销售金额,输入=SUMPRODUCT((A2:A18=--"2017/3/3")*(B2:B18="手机")*(C2:C18="小米5.5吋")*F2:F18),日期前加双负号此时也可以用SUMIFS函数,输入=SUMIFS(F2:F18,A2:A18,"2017/3/3"B2:B18,"手机"C2:C18,"小米5.5吋")下图求东北和西北地区销售总数量,输入=SUMPRODUCT(((A2:A10="东北")+(A2:A10="西北")),B2:B10)

前两个条件相加,=SUMPRODUCT(((A2:A10="东北")+(A2:A10="西北")),B2:B10)=SUMPRODUCT((A2:A10="东北"),B2:B10)+SUMPRODUCT((A2:A10="西北"),B2:B10)4、多条件计数如图输入=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))

释义:第一个条件B2:B16>=80,当B2:B16区域有符合条件>=80时,显示1,否则显示0,另一条件同理=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))=SUMPRODUCT({0;1;1;0;1;1;0;0;0;0;0;0;1;1;0}*{1;1;1;1;1;1;0;0;0;0;0;1;0;1;0})两两对应相乘再求和此例还可以用COUNTIFS函数,输入=COUNTIFS(B2:B16,">=80"C2:C16,">=80")5、条件排名下图中,求排名,在C2输入=SUMPRODUCT(($B$2:$B$16>B2)*1)+1向下填充公式含义:在B2:B16区域中,乘以1,把它转化成数组才能参与运算,加1(+1)是看比B2(79)的成绩大的数量有几个,如果有6个,则B2的排名是7;

大家都关注
有问题 @爱问
Powered by iask.com