Excel中有不少万能函数,一个函数能顶多个函数,例如VLOOKUP、OFFSET、SUBTOTAL、AGGREGATE、SUMPRODUCT等。它们各有专长,功能都非常强大,且受人追捧,今天Excel办公小课堂来给大家介绍其中的SUMPRODUCT函数,朴实低调,不为大多数人所知,但却同样拥有超强的能力!一共整理了10种经典案例,非常全面,建议先收藏,不然到时候需要就找不到了哦~
SUMPRODUCT函数:返回相应的数据或区域乘积之和
功能:SUMPRODUCT(数组1,数组2,...)
目的:根据每个商品的数量和单价,计算所有商品的总计
公式:=SUMPRODUCT(C4:C12,D4:D12)
目的:根据每个商品的数量和单价,计算行政部使用各商品总计
公式:=SUMPRODUCT((C4:C12="行政部")*(E4:E12)*(F4:F12))
说明:SUMPRODUCT函数(C4:C12="行政部")表示在C4:C12区域查询“行政部”,如果此区域有包含行政部,则通过行政部中数量列和单价列的数据进行相乘,也就是(E4:E12)*(F4:F12),最后进行求和汇总。
目的:根据每个商品的数量和单价,计算行政部6月份使用各商品总计
公式:=SUMPRODUCT((C4:C12="行政部")*(D4:D12="6月")*(E4:E12)*(F4:F12))
说明:SUMPRODUCT函数(C4:C12="行政部")*(D4:D12="6月")表示在C4:C12区域查询“行政部”,D4:D12区域查询“6月”如果两个区域同时包含行政部和6月,则通过行政部和6月的数量列和单价列数据进行相乘,也就是(E4:E12)*(F4:F12),最后进行求和汇总。
目的:根据两个班级总人数,统计男同学人数
公式:=SUMPRODUCT(N(D4:D13="男"))
说明:SUMPRODUCT函数N(D4:D13="男")表示在D4:D13区域计算性别为“男”的个数,其中“性别”列是文本形式,则需嵌套N函数,表示返回转化为数值后的值,从而统计男同学的人数。
目的:根据两个班级总人数,统计80分以上女同学人数
公式:=SUMPRODUCT((D4:D13="女")*(E4:E13>80))
说明:SUMPRODUCT函数(D4:D13="女")表示在D4:D13区域查询性别为女的数据,(E4:E13>80)表示在E4:E13查询成绩大于80的数据,然后将两则数据进行相乘,从而统计80分以上女同学的人数。
目的:根据两个班级学生成绩进行排名
公式:=SUMPRODUCT(($E$4:$E$13>E4)/COUNTIF($E$4:$E$13,$E$4:$E$13))+1
说明:对于排名可能大家都会想到使用RANK函数来进行计算排名,但是RANK是国际的一种排名方式,当遇到相同名次时,下一个名次数字顺序就会间断,例如案例中有三位学生都是第5名,顺延下一位学生的名次就变成了第8名了,此时就间断了6和7名的名次,解决不间断名次(中国式排名方式)需要使用SUMPRODUCT函数。
SUMPRODUCT函数($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4成绩,按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE},这里的TRUE表示的是1,FALSE表示的是0。而COUNTIF($E$4:$E$13,$E$4:$E$13)表示数组中的元素就是对应成绩重复出现的次数,按F9可以看出{1;1;1;3;3;3;1;1;1;1},这里的3表示的是成绩78分出现3次,1表示没有重复的分数,也就是各分数出现1次。然后将两组数组进行相除,再通过SUMPRODUCT函数进行求和得到数字5,后面再加上1表示的是当前数字的本身排名情况。
如果对以上说明不理解,可以通过这种公式套路进行理解
套路:=SUMPRODUCT((条件区域>条件)/COUNTIF(要进行排名的区域))+1
目的:根据两个班级学生成绩,分别进行按班级排名
公式:=SUMPRODUCT(($B$4:$B$13=B4)*($E$4:$E$13>E4))+1
说明:SUMPRODUCT函数($B$4:$B$13=B4)表示在B4:B13班级区域列判断是否等于B4,按F9则返回一组数组{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE},($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4,按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}。这里的TRUE表示的是1,FALSE表示的是0,通过SUMPRODUCT函数将两组数组乘积求和汇总,再加上1本身成绩排名,从而计算出各班级学生排名情况。
如果对以上说明不理解,可以通过这种公式套路进行理解
套路:=SUMPRODUCT((条件区域1=条件1)*(要进行排名的区域))+1
目的:根据每个商品的数量和单价,计算笔销量总和
公式:=SUMPRODUCT(ISNUMBER(FIND("笔",B4:B12))*C4:C12*D4:D12)
说明:由于SUMPRODUCT函数的参数中不支持使用通配符,所以在计算带有关键字的问题,在这里需要截取ISNUMBER解决关键字的问题,首先使用FIND函数在B4:B12数据区域中查找关键字“笔”,如果包含关键字“笔”,就返回表示关键字位置的数值,否则就返回错误值。然后使用ISNUMBER函数判断FIND函数的数组结果是不是数值,如果是数值,说明是包含关键字的,如果不是数值,那就是不包含关键字了。ISNUMBER最终得到由逻辑值TRUE或FALSE构成的内存数组,然后再与后面的区域C4:C12和区域D4:D12依次相乘,最后由SUMPRODUCT返回乘积之和
目的:统计所有商品的总数量
公式:=SUMPRODUCT(C4:C12)
说明:公式SUMPRODUCT(C4:C12)直接对数量列中包含数字进行求和,忽略文本。当然在这里也可以直接用SUM函数进行汇总。
目的:根据每个商品的数量和单价,计算笔销量总和
公式:=SUMPRODUCT(--SUBSTITUTE(D4:D12,"元","")*C4:C12)
说明:由于单价列包含有单位“元”,此时先使用SUBSTITUTE函数将单位元进行替换掉,也就是--SUBSTITUTE(D4:D12,"元",""),这里两个减号--,表示的是负负得正,也就是强制地把文本型的数字转换成数值型的,最后通过SUMPRODUCT函数将数量列和单价列的数据进行乘积求和。
本文地址:http://sjzytwl.xhstdz.com/news/12921.html
物流园资讯网 http://sjzytwl.xhstdz.com/ , 查看更多
特别提示:本信息由相关用户自行提供,真实性未证实,仅供参考。请谨慎采用,风险自负。
更多>同类最新文章
0 条相关评论
文章列表
- Real Moto 2(真实摩托2游戏手机版)摩托手机「Real Moto 2(真实摩托2游戏手机版)」
- 家居“新物种”:行业新革命!叶盛明珠发热岩板颠覆家居采暖逻辑
- 如何连接usbusb怎么连接手机「如何连接usb」
- 售17.98万起! 北京BJ40增程版上市!双电机四驱+三把锁,硬派越野SUV的新扛把子?
- 新增20+项新功能 提供高阶智驾选装 领克EM-P超电双子星OTA 1.8.0推送更新
- 凯立德车载导航最新版本凯立德手机导航「凯立德车载导航最新版本」
- vivo Y300 Pro+/Y300t发布,更实惠的千元手机
- Canvalr手机版「Canva」
- Nothing 2025 放大招:Phone(3)手机领衔,里程碑式新机登场phone手机「Nothing 2025 放大招:Phone(3)手机领衔,里程碑式新机登场」
- 心有千千结 | 手机危害七宗罪&手机脸手机脸「心有千千结 | 手机危害七宗罪&手机脸」
相关文章
最新动态
- 全球首款三折叠手机亮相 华为抢占折叠屏手机高位三折叠手机「全球首款三折叠手机亮相 华为抢占折叠屏手机高位」
- 理导伴你行丨辅导员手机里面的故事手机故事「理导伴你行丨辅导员手机里面的故事」
- 手机探测门到底有多厉害?移除SIM卡和电池的手机也能检测到手机检测「手机探测门到底有多厉害?移除SIM卡和电池的手机也能检测到」
- 终极格斗冠军赛4手机版ufc游戏手机版「终极格斗冠军赛4手机版」
- 清明不只青团!还有乌稔饭、枣糕等地方特色美食
- 手机壳会致癌 被窝里玩更要命手机壳有异味有毒吗「手机壳会致癌 被窝里玩更要命」
- 火影忍者:当美女忍者们留长发,红豆长发飘飘,小樱很像玖辛奈
- 纽曼手机怎么开机纽曼手机「纽曼手机怎么开机」
- 新疆队悄悄准备,山东男篮瞄准青年队,9人能否过邱彪这关?
- 折叠屏手机 大降价!手机降价「折叠屏手机 大降价!」
推荐图文
最新文章
点击排行
- 1001苹果2025年将推出无边框iPhone,OLED屏幕新技术引领未来智能手机设计无边框手机「苹果2025年将推出无边框iPhone,OLED屏幕新技术引领未来智能手机设计」
- 992让移动硬盘的传输速度突破6000MB/s!雷电5解决方案首测
- 993每日一部手机小科普:Ericsson GF768爱立信手机「每日一部手机小科普:Ericsson GF768」
- 994手机换机搬家同步手机搬家下载「手机换机搬家同步」
- 995点击助手按键精灵最新按键精灵手机助手「点击助手按键精灵最新」
- 986上海市第二届职业技能大赛上午开幕,近千名选手同台竞技
- 987专访导师马树青 | 每个艺术家都会想着如何成为自己
- 988AI时代,科技期刊如何继续保持“可信”|中关村论坛观察
- 989落户重庆六大方式汇总!