umproduct函数应用实战docx.docx
- 文档编号:7739414
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:12
- 大小:203.78KB
umproduct函数应用实战docx.docx
《umproduct函数应用实战docx.docx》由会员分享,可在线阅读,更多相关《umproduct函数应用实战docx.docx(12页珍藏版)》请在冰豆网上搜索。
umproduct函数应用实战docx
SUMPRODUCT的应用及其强大功能
SUMPRODUCT是应用非常广泛的一个有强大能力的求和公式,它是
2007版本后增加的一个功能多样实用性强大的公式。
函数定义:
SUMPRODUCT…返回相应的数组或区域乘积的和,即不同数组对应区域乘积Z和。
注意是乘积的和。
),
语法:
SUMPRODUCT(数组区域1,数组区域2,数组区域3,最多可以有255个这样的数组。
1、语法说明:
下图中要示购买所有商品的总金额,一般做法就是B列
单价与C列数量相乘,最后把乘积相加就得到总金额。
现在利用
sumproduct函数,一步就可以算出来。
SUMPRODUCT函数就是令B
列与C列对应数字相乘,然后相加,即B2*C2,B3*C3……等。
函数最经典也是最简单最好理解的形式,结果为10548o
wrc•I■•・Irm
厂Efe复制・
占5・,bIy-
隽贴板G
字仿
»A▼孙=
■0▼
•o———rrzzCE3
对幵力式
LOOKUP▼:
X✓A
=SUMPRODUCT(B2:
B9,C2:
C9)
A
B
C
D
E
F1
IG
1
商品单价数量应付总和
2
■
2.5
6
=SUMPRODUCT(B2:
B9,C2C9)
3
3.5
一10
SUMPRODUCT(arrayl[array2],[array3]f[arr
4’
6
50
5
10
24
6.
50
25
7
6.5
52
8
15.6
75
9
10
七巧板(
72
100.
111
12
13
在这里,也可以将两数组或者区域直接用⑷相乘,结果是一样。
如下图:
=SUMPRODUCT(|B2:
B9)*(C2:
C9))
0O七"
霑复制.一1
I彳:
BIU-田■0・A
C2
2
3|
4.
5
1
▼
x✓A
=SUMPRODUCT((B2:
B9)*(C2:
C9))
AB
CD
E
F
C
商品单价一
数量J
应付总和
豆芽2.5
6
10548
EftSfig字体
对幵方工
8
9
10
11
12
13
14
3.5
10
=SUMPRODUCT((B2B'
?
HC2:
C9)j
6
50
10
24
50
25
6.5
52
15.6
75
.72.
・100.
I.—
板菜片椒鱼鱼鱼巧油嶠花就鎚带七
下图是用传统的方法求得的总金额,可以看岀结果与用sumproducti+算结果一样的。
D
商品
单价数量
应付总和
10548
10548
2
3
4
5
6
7
8
9
L0
L1
L2
L3
=SUM(D2:
D9)
1
D10
七
2.5
6
15
3.5
10
35
6
50
300
10
24
240
50
25
1250
6.5
52
338
15.6
75
1170
72
100
7200
10S4R1
板
2、某部门在2月3月4月给部分员工的工资流水,现在要求在这段时间内张三共领了多少工资?
显然,如果用sumif或者sumifs也可以求出,如下图,用sumif可以求出:
=SUMIF(C2:
C18,,,^H,,,D2:
E18)或者用sumifs也可以求出:
=SUMIFS(D2:
D18,C2:
C18,”张三)也可以用此函数sumproduct求出:
=SUMPRODUCT((C2:
C18=n张三T(D2:
D18)),从这一点看,这三个函数都可以达到目的。
JL6▼
张三共领了多少工资
▲AB
时间部门姓名工资
J2018/2/16经管部
I2018/2/18市场部
李四
6000
2018/2/26经管部
2018/2/27市场部
2018/3/6政教部
四子一李妻王
OOCOCO
452315
5500
L2018/3/25经管部—郑七|660
1
2018/4/2市场部
2
2018/4/6经管部
3
2018/4/10政教部
4
2018/4/11市场部
5
2018/4/12政教部
6
2018/4/13市场部
7
2018/4/14经管部
8
2018/4/14市场部
9
COOOOOCO
4月份张三多少工资
4月份市场部发了多少
02018/3/30政教部
>2018/2/6市场部张三5000
2
2018/4/6
经管部
郑七
5000
4月份市场部发了多少i
3
2018/4/10政教部
王八
4930
1
2018/4/11市场部
卢子_
5200
■
D
2018/4/12政教部
王二
800
D
2018/4/13
市场部
张三
4500
7
■
2018/4/14经管部
郑七一
—r
7900
■■■
=SUMPRODUCT((C2:
C18=H5KH,er(D2:
D18))
但是如果当条件这成月分时,如果用sumif或者sumifs的话,就变得非常麻烦,使简单的问题复杂化了。
此吋还有从A列里提取月份。
此吋用sumif,sumifs就非常不方便。
3、如图示问题,4月份张三共领了多少工资,如果用sumif,sumifs就非常不方便。
提取月份我们用函数:
month函数。
这里我们要从A列提取月份出来,找到是4月份的:
month(A2:
A18)=4,从sumproduct函数的逻辑来看:
month(A2:
A18)=4就是一组包含4月份的数组(不是4月份的我们可以把它的逻辑理解为0,是4月份逻辑数值为1)oC2:
C18「张三”,是另一包含张三的数组(其逻辑数值同上),其对应数组D2:
D18,其逻辑关系如下图,
三个数组对应的值相乘再加,就是我们要的结果(注意观察图中红色与加粗红色部分,加粗红色部分就是符合条件的值,红色工资栏就是张三的工资)。
所以用sumproduct函数。
这里因为提取月份后,用sumif,sumifs计算会造成内在逻辑变得复杂,所以不推荐用sumif,sumifsc如下图,所以在单元格内输入公式:
=SUMPRODUCT((MONTH(A2:
A18)=4)*(C2:
C18=H张三罗(D2:
D18)):
XIh==|-^-=-=|ts=i口|屮/V•|5
1对齐方式rj
=SUMPRODUCT((MONTH(A2:
A18)=4)*(C2:
C18=n张三W)*(D2:
D18))
G
H1
1I|
J
I张三共领了多少工资
'■—■
■■■]■■■■
这里只能把三个数组用町目乘。
并且输入时注意符号。
三种不同颜色标注三个数组。
用同样的方法求4月份市场部一共发了多少工资,公式如下:
=sumproduct((month(A2:
A18)=4)*(B2:
B18=”市场部H)*(D2:
D18))
fx=sumproduct((month(A2:
A18)=4)*(B2:
B18="市场部H)*(D2:
D18)
|_DJ
af
G
1H1
1
J
1K
工资
张三共领了多少工资
5000
29500
6500
6000
29500
4500
2300
1500
11000
—5500
660
7500
4月份张三多少工资
10000
14500
5000
4月份市场部发了多少工资I
4930
=sumproduct((month(A2:
A18=4)*(B2:
B18=*市场部}(D2:
D18)
4、求加权数:
如学生成绩统计时,按各科比例,算岀最后的加权成绩:
意思就是各科取对应比例算出成绩,最后相加。
在R4单元格内输入:
=SUMPRODUCT(M4:
Q4,M2:
Q2),注意这里的
加权比例项因为每个学生算成绩时比例是固定的,在下拉复制公式时应
锁定,所以必须锁定:
=SUMPRODUCT(M4:
Q4,$M$2:
$Q$2)o最后下拉复制公式就可以求出所有学生的加权成绩。
L
1M1
N
0
P
Q4
R
S
K
求学生的各科加权成绩
加权比俛30%40%30%50%50%
名三四一七八子子姓张李王郑王卢建
数学语文英语文综理综实际成绩
135120135226288|1
_120112126215300
_105109135180247_
150116134196165
95匚86106204249匚[
_115109136256―226—.
105116118289223
对齐方式gI
=SUMPRODUCT(M4:
Q4,$M$2:
$Q$2)
NOPQRSTU
手科加权成绩
J0%
40%
30%
50%
50%|
语文
英语
文综
瑾综一实际成绩
L35
120
135
226
2881=SUMPRODUCT(M4:
O4.SMS2:
SQfc2)
L20
112
SUMPRODUCT(arraylt(array2]#[array3]
•[array4)t...)
L05
109
135
180
247
L50
116
I134
196
165□
1HA
904
字体
Ql_对齐方式G
;厂―7A
=SUMPRODUCT(M4:
Q4,$M$2:
$Q$2)
LM
N0P0RS
求学生的各科加权成绩
加权比仅
30*
40%
30%
50H
50%
数学
语文
英语
文综
逞综
实际成绩
135
120
135
226
288
386
120
112
126
215
300
376.1
105
109
135
180
247
329.1
150
116
134
196
165
312.1
95
86
106
204
249
321.2
115
109
136
256
226
359.9
105
116
_I*
289
223
3693.
■
sumproduct函数功能强大,在实际中应用很多,需要在实际中多多体会练习。
在后续的文章中,我们还要对此函数做进一步的讨论。
弄清函数的逻辑关系,其实excel也不难学。
本文为创作品,支持原创,请点赞,请关注本公众号:
excel不难学。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- umproduct 函数 应用 实战 docx