SUMPRODUCT函数的应用 excel表格交换位置

SUMPRODUCT是什么?其实结合英语就能很好的理解SUMPRODUCT函数,sum是和,product是积,结合起来就是乘积之和。

Excel中SUMPRODUCT函数是一个数组类型的函数。很多时候可以用SUMPRODUCT函数取代SUM函数的数组公式,就不需要按三键结束。

SUMPRODUCT函数能够计算多个区域的数值相乘后之和。SUMPRODUCT函数的用法就是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

SUMPRODUCT函数的应用 excel表格交换位置
SUMPRODUCT函数的语法:SUMPRODUCT(array1,array2,array3, ...)

其中Array1, array2, array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

SUMPRODUCT函数使用需要注意三点:

第一,数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。

第二,函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

第三,如果是一个数组,那么就是对这个数组的求和。

我们先通过一个简单的工作表数据来认识SUMPRODUCT函数。



第一,输入公式:=SUMPRODUCT(A2:B4*C2:D4),就是将A2:B4和C2:D4两个区域的所有元素对应相乘,然后把乘积相加,即3*2+4*7+8*6+6*7+1*5+9*3,得到结果为156。

第二,输入公式:=SUMPRODUCT(A2:B4),得到结果为31。根据上面的要点介绍,如果是一个数组,那么就是对这个数组的求和,因此就是对{3,4;8,6;1,9}这个区域求和。

提示:两个数组相乘是同一行的对应两个数相乘。数组数据用大括号{}括起来,行数据之间用分号";"分隔,如果是同一行的数据,用逗号","分隔。

下图所示的是咱们IT部落窝[url=]6群(群号:92798512)的随机抽查的人员资料表。为了演示方便,只是截取了部分数据。



通过上面的数据,我们结合SUMPRODUCT函数的用法来完成以下应用案例。

SUMPRODUCT函数用于多条件计数,计算符合2个及以上条件的数据个数。有一个经典公式计数:SUMPRODUCT((条件1)*(条件2)*(条件3)*...)

第一,统计C列性别列中女性有几个人。

此题为单条件求和。首先要知道条件是什么,(C4:C33="女")区域中等于女的,这部分就是条件。

如果直接输入=SUMPRODUCT(C4:C33="女"),得到结果为0。第一部分用法介绍里介绍:函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理,C4:C33="女",按F9键得到执行结果是true、false形式的逻辑值,所以等于0。

那如何把逻辑值转换为数值呢,就要让逻辑值参加运算,可以用--,*1,+0等等。比如,=TRUE*1,结果为1。=FALSE*1,结果为0。因此在(C4:C33="女")外面加上--就可以了。

公式为:=SUMPRODUCT(--(C4:C33="女")),结果为9人。

第二,求E列潜水天数大于15天的男性有几人。

此题有两个条件:第一,大于15天,用E4:E33>15表示。第二,男性,用C4:C33="男"表示。

套用SUMPRODUCT((条件1)*(条件2)*(条件3)*...),得到公式:=SUMPRODUCT((E4:E33>15)*(C4:C33="男")),结果为8人。

第三,统计2月份发言的男性有几人。

D列最后发言时间有1月和2月的份。统计2月份,需要用到month函数来求月份。比如A1单元格:2011-2-25,A2单元格输入公式:=MONTH(A1),返回2。

还是套用SUMPRODUCT((条件1)*(条件2)*(条件3)*...),得到公式:=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33="男")),结果为16人。

第四,统计不包括笑看今朝的男性有多少人。

不包括,就是不等于,是<>表示。

公式为:=SUMPRODUCT((A4:A33<>"笑看今朝")*(C4:C33="男")),结果为20人。

第五,统计有几个人的潜水天数是不一样的。

比如,E列潜水天数为6的有6次,为5的有2次。

如何让每个数字只出现一次呢?计数用countif函数。如何让每个数只计算一次呢,可以使用1/countif。比如5出现两次,就是两个1/2,最后汇总就得到1。最后再套用公式sumproduct(1/countif(区域,区域))。

公式为:=SUMPRODUCT(1/COUNTIF(E4:E33,E4:E33)) ,结果为14人。

用函数SUMPRODUCT求和,函数需要的参数一个是进行判断的条件,另一个是用来求和的数据区域。

SUMPRODUCT函数求和应用有一个经典的套用格式:SUMPRODUCT((条件1)*(条件2)*(条件3)*…*求和区域)

第一,女性潜水总天数计算。

套用格式,得到公式:=SUMPRODUCT((C4:C33="女")*E4:E33),结果为134。

第二,潜水时间大于15天的男性的潜水天数计算。

套用格式,得到公式:=SUMPRODUCT((E4:E33>15)*(C4:C33="男")*E4:E33),结果为242。

第三,2月份发言的男性的潜水天数计算。

套用格式,得到公式:=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33="男")*E4:E33),结果为159。

第四,QQ号首位是8的人的潜水天数计算。

首先用left函数提取首位是8的,因为left提取的是文本,加""就成了文本。然后套用格式,得到公式:=SUMPRODUCT((LEFT(B4:B33)="8")*E4:E33),结果为77。

第五,姓名字符数为2,不包括“月亮”的人的潜水天数计算。

公式为:=SUMPRODUCT((LEN(A4:A33)=2)*(A4:A33<>"月亮")*E4:E33),得到结果:92。

第六,“笑看今朝”和 “冷逸”的潜水天数计算。

公式为:=SUMPRODUCT((A4:A33={"笑看今朝","冷逸"})*E4:E33),得到结果13。

公式中两个条件,可以这样写:{"笑看今朝","冷逸"}。

如下图所示,需要求出姓名列的潜水天数已经排名情况。



在以前的讲座详细介绍了vlookup函数实现查找的方法,在本讲座中用sumproduct函数取代。利用单条件求和的特点来查找。根据前面的介绍,套用格式=SUMPRODUCT((姓名=I26)*天数),得到公式:=SUMPRODUCT(($A$4:$A$33=I26)*$E$4:$E$33),然后下拉即可完成。

在以往排名我们使用rank函数,不过我们也可使用SUMPRODUCT函数来完成。在K26输入公式:=SUMPRODUCT(--($J$26:$J$31>J26))+1,下拉。思路就是如果区域中有几个大于本身,目标值就是大于的所有数+1。

  

爱华网本文地址 » http://www.413yy.cn/a/25101013/158403.html

更多阅读

Excel常用函数 excel表格的函数应用

Excel 是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择,使用Microsoft Excel可以执行计算,分析信息并管

excel表格的35招必学秘技 excel库存管理表

excel表格的35招必学秘技——简介也许你已经在Excel中完成过上百张财务报表,也许你已利用Excel函数实现过上千次的复杂运算,也许你认为Excel也不过如此,甚至了无新意。但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技

excel表格的基本操作教程 电脑工作表格基本操作

excel表格的基本操作教程——简介 excel表格功能非常强大,我们可以利用它收集记录数据,也可以进行简单的统计计算,还有很多函数功能可以利用,比如可以求和,求平均数,排序等,现在我简单的说一下它的基础操作。excel表格的基本操作教程——

Excel中COLUMN函数的使用 excel函数的使用方法

Excel中COLUMN函数的使用——简介COLUMN函数是一个简单的辅助函数,在一些复杂的函数计算中会使用到COLUMN函数,但是一般都不会对COLUMN函数作解析,很多朋友对公式函数中突然插入“=COLUMN()”表示理解。这里,为大家介绍COLUMN函数的使用。

ROW函数的用途 excel中的row函数

ROW函数为返回引用单元格的行号或单元格区域。语法:ROW(reference),举个例子:=row(a15),返回值为15;=row(),返回值为公式所在单元格的行号;还可以引用数组。 =row(a1) 返回a1的行号,=1 =row(1:1) 返回第一行的行号,=1 =row(a:a) 返

声明:《SUMPRODUCT函数的应用 excel表格交换位置》为网友不小心钟情分享!如侵犯到您的合法权益请联系我们删除