SUMPRODUCT函数详解(2)
时间:2022-01-24 13:19:57
工作表有一类最基本的函数是基于某条件统计或汇总结果,例如COUNTIF函数或SUMIF函数,都是相当有用和灵活的函数,但仅局限于单条件。而本文将着重探讨基于多条件获得结果的函数——SUMPRODUCT函数。
SUMPRODUCT函数是Excel中最“多才多艺”的函数之一。其基本功能是,数组中相应的项目相乘并返回结果之和。下面将介绍SUMPRODUCT函数经典的用法。
SUMPRODUCT函数的标准用法
SUMPRODUCT函数的基本用法是:一个数组中的每个值与另一个数组中相应的值相乘,返回乘积的和。
示例1:如果单元格区域A9:A11中分别包含值1、2、3,单元格区域B9:B11中分别包含值10、20、30,那么
=SUMPRODUCT(A9:A11,B9:B11)
返回结果140,或者(1*10)+(2*20)+(3*30)=10+40+90=140。
SUMPRODUCT函数的“进化”用法
在Excel中,有两个非常有用的支持条件统计和求和的函数,分别是COUNTIF函数和SUMIF函数,但仅支持单个条件测试。
举个简单的双条件测试例子。例如,公式=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)将得到在单元格区域A1:A10中介于10和20之间有多少项。然而,这种摸拟双条件测试的方法是有局限的,它不能处理不同的单元格区域或者更多的条件。
为了理解SUMPRODUCT函数的使用,先举个例子。示例数据如下:
下面的公式很容易得出“Ford”的数量:
=COUNTIF(A1:A10,”Ford”)
返回的结果为4。
同样,下面的公式直接获得“Ford”销售价格之和:
=SUMIF(A1:A10,”Ford”,C1:C10)
返回的结果为33873。
然而,假设想要统计6月份“Ford”的销售数量,使用下面的数组公式:
=SUM(IF(A1:A10=”Ford”,IF(B1:B10=”June”,1,0),0))
输入后同时按下Ctrl+Shift+Enter键,返回结果3。
同样,要统计6月份“Ford”销售价格之和,使用下面的数组公式:
=SUM(IF(A1:A10=”Ford”,IF(B1:B10=”June”,C1:C10,0),0))
返回结果27000。
下面我们看看使用SUMPRODUCT函数来获取结果的情形。下面的公式:
=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”))
得到6月份“Ford”的销售数量,结果为3。
下面的公式:
=SUMPRODUCT((A2:A10=”Ford”)*(B2:B10=”June”)*(C2:C10))
得到6月份“Ford”销售价格之和。
“*”号用作AND(与)运算符,公式的其含义是单元格区域A2:A10中的值为“Ford”并且B2:B10中的值为“June”所对应的C2:C10中的值相加。
然而,如果我们想在公式中使用OR(或)运算符呢?使用“+”号来代表OR运算符。例如,下面的公式:
=SUMPRODUCT((A1:A10=”Ford”)+(A1:A10=”Renault”))
计算单元格区域A1:A10中为“Ford”或“Renault”的数量,结果为6。
SUMPRODUCT函数的优点
正如上文所描述的,多条件测试是SUMPRODUCT函数的主要优点。然而,SUMPRODUCT函数还有其它两个优点:一个是它能够处理关闭的工作簿中的数据,不受源工作簿是否关闭的影响;另一个是能够处理文本值以满足需求。
例如,如果想在另一个工作簿中计算“Sumproduct函数.xlsm”工作簿中的数据,那么使用下面的公式计算该工作簿中大于15000的值的和。
=SUMIF([Sumproduct函数.xlsm]Sheet1!$C$2:$C$10,”>15000″)
此时,如果“Sumproduct函数.xlsm”工作簿关闭,再重新计算工作表是时该公式会返回#VALUE。同样,如果“Sumproduct函数.xlsm”工作簿已经关闭后,再在其它工作簿中使用上面的公式,会返回#VALUE。
然而,使用SUMPRODUCT函数就不会存在上面的问题。无论“Sumproduct函数.xlsm”工作簿是否关闭,将下面的公式输入到其它工作簿中,都会得到正确的结果。
=SUMPRODUCT(–(‘[Sumproduct函数.xlsm]Sheet1’!$C$2:$C$10>15000),–(‘[Sumproduct函数.xlsm]Sheet1’!$C$2:$C$10))
第二个主要的优点是SUMPRODUCT函数能够处理具有不同数据类型文本的列。例子数据如下图所示,在B列的第一行中是文字文本值作为标题,其余行为数值。
如果使用下面的公式:
=SUMPRODUCT((A1:A4=”x”)*(B1:B4))
则会返回错误#VALUE。
要忽略错误,则要使用双一元操作符(两个减号):
=SUMPRODUCT(–(A1:A4=”x”),(B1:B4))
返回正确的结果,本例中是4。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
word怎样修改所有尾注的样式
excel 设A列超链接为B列 把A列、B列合并到C列
在Word 2007中如何删除自定义库
![](https://img.aspxhome.com/file/2023/2/29572_0s.jpg)
在Excel中如何使用Sum函数?
![](https://img.aspxhome.com/file/2023/3/42283_0s.jpg)
word打印预览不全
如何美化word表格
![](https://img.aspxhome.com/file/2023/5/34645_0s.gif)
Excel里ctrl+h有什么作用?Excel中ctrl+h组合快捷键的几大功能介绍
![](https://img.aspxhome.com/file/2023/8/40248_0s.gif)
硬盘使用DiskGenius分区工具4K对齐
![](https://img.aspxhome.com/file/2023/0/48340_0s.png)
Word2016首字母下沉怎么操作?Word2016首字母下沉操作教学
![](https://img.aspxhome.com/file/2023/1/31161_0s.png)
word如何合并邮件呢?
![](https://img.aspxhome.com/file/2023/1/24131_0s.jpg)
Word怎么让表格每页都带有表头
![](https://img.aspxhome.com/file/2023/4/34344_0s.gif)
最全的Word知识点,抓紧时间收藏起来
word 如何分别或同时调整自动编号和后面文本的缩进距离
![](https://img.aspxhome.com/file/2023/6/34136_0s.png)
win10下载软件被阻止安装怎么办?win10下载软件被阻止安装的解决方法
![](https://img.aspxhome.com/file/2023/5/47795_0s.png)
Apple ID 已被暂时禁止获取免费 App 怎么办?
![](https://img.aspxhome.com/file/2023/7/a194707_0s.png)
Excel数据透视表怎么布局并设置排序方式?
![](https://img.aspxhome.com/file/2023/0/41230_0s.jpg)
excel折线图如何变成想要的曲线?
![](https://img.aspxhome.com/file/2023/2/41792_0s.jpg)
word如何恢复未保存文档
word如何设置行距行高使页面更美观?
![](https://img.aspxhome.com/file/2023/5/23705_0s.jpg)
如何在Word中制作空心字教程
![](https://img.aspxhome.com/file/2023/3/29233_0s.png)