Excel多条件求和&SUMPRODUCT函数用法详解
更新时间:2024-05-17 13:45:01 阅读量: 综合文库 文档下载
Excel多条件求和 & SUMPRODUCT函数用法详解
日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本),方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了,与SUMPRODUCT函数的作用相比实在不匹配,为了更好地掌握该函数,特将其整理如下。
一、 基本用法
在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
语法:
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
公式:=SUMPRODUCT(A2:B4, C2:D4)
A B C D
1 Array 1 Array 1 Array 2 Array 2 2 3 4 2 7 3 8 6 6 7 4 1 9 5 3
公式解释:两个数组的所有元素对应相乘,然后把乘积相加,即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为156
二、 扩展用法
1、 使用SUMPRODUCT进行多条件计数
语法:
=SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
作用:
统计同时满足条件1、条件2到条件n的记录的个数。
实例:
=SUMPRODUCT((A2:A10=\男\中级职称\
公式解释:
统计性别为男性且职称为中级职称的职工的人数
2、 使用SUMPRODUCT进行多条件求和
语法:
=SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*某区域)
作用:
汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。
实例:
=SUMPRODUCT((A2:A10=\男\中级职称\
公式解释:
统计性别为男性且职称为中级职称的职工的工资总和(假设C列为工资)
三、 注意事项
1、数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。
2、SUMPRODUCT函数将非数值型的数组元素作为 0 处理。
3、在SUMPRODUCT中,2003及以下版本不支持整列(行)引用,必须指明范围,不可在SUMPRODUCT函数使用A:A、B:B,Excel2007及以上版本可以整列(列)引用,但并不建议如此使用,公式计算速度慢。
4、SUMPRODUCT函数不支持“*”和“?”通配符
SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“?”等通配符,要实现此功能可以用变通的方法,如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(S
EARCH())等函数来实现通配符的功能。如:
=SUMPRODUCT((A2:A10=\男\中级职称\龙\
=SUMPRODUCT((A2:A10=\男\中级职称\龙逸凡\10)))*C2:C10))
注:以上公式假设D列为职工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是实现“*”的通配功能,只是前者区分大小写,后者不区分大小写。
5、SUMPRODUCT函数多条件求和时使用“,”和“*”的区别:当拟求和的区域中无文本时两者无区别,当有文本时,使用“*”时会出错,返回错误值 #VALUE!,而使用“,”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理,故不会报错。 也就是说:
公式1:=SUMPRODUCT((A2:A10=\男\中级职称\
公式2:=SUMPRODUCT((A2:A10=\男\中级职称\,C2:C10)
当C2:C10中全为数值时,两者计算结果一样,当C2:C10中有文本时公式1会返回错误值 #VALUE!,而公式2会返回忽略文本以后的结果。
四、 网友们的精彩实例
1、求指定区域的奇数列的数值之和
=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)
2、求指定区域的偶数行的数值之和
=SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)
3、求指定行中列号能被4整除的列的数值之和
=SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)
4、.求某数值列前三名分数之和
=SUMPRODUCT(LARGE(B1:B16,ROW(1:3)))
5、统计指定区域不重复记录的个数
=SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15))
正在阅读:
Excel多条件求和&SUMPRODUCT函数用法详解05-17
南京市规划建设展览馆参观认识实习报告04-24
恒大冰泉营销计划方案(1)01-19
争吵的艺术——读《绳》与《白象似的群山》05-31
商务函电常用词汇综合练习05-15
玉米浸泡水提取植酸钙镁04-14
- 多层物业服务方案
- (审判实务)习惯法与少数民族地区民间纠纷解决问题(孙 潋)
- 人教版新课标六年级下册语文全册教案
- 词语打卡
- photoshop实习报告
- 钢结构设计原理综合测试2
- 2014年期末练习题
- 高中数学中的逆向思维解题方法探讨
- 名师原创 全国通用2014-2015学年高二寒假作业 政治(一)Word版
- 北航《建筑结构检测鉴定与加固》在线作业三
- XX县卫生监督所工程建设项目可行性研究报告
- 小学四年级观察作文经典评语
- 浅谈110KV变电站电气一次设计-程泉焱(1)
- 安全员考试题库
- 国家电网公司变电运维管理规定(试行)
- 义务教育课程标准稿征求意见提纲
- 教学秘书面试技巧
- 钢结构工程施工组织设计
- 水利工程概论论文
- 09届九年级数学第四次模拟试卷
- 求和
- 用法
- 详解
- SUMPRODUCT
- 函数
- 条件
- Excel
- amp
- Agilent 7890 B 7000C 气相色谱质谱联用仪操作规程
- AF价体系调查问卷(基于层次分析法)2
- 轨道衡控制室要求
- 物理化学上册复习
- 2012年高考真题 - 语文(江西卷)解析版
- 2001~2010成考数学试题(文史类)分类题解
- 2017秋三年级语文上册第28课南极,一片净土教学设计语文S版(1)
- 中学教材全解八年级英语(下)
- 高考一轮:第一章 第4讲 物质的量浓度
- 高中奥林匹克物理竞赛解题方法 十三 降维法 上
- 消费经济学
- 造纸厂实习报告
- 钢结构厂房屋面反吊顶板施工方案.doc1
- 高层办公楼设计任务书
- 一年级经典诵读教案
- 集体备课管理制度
- 2019年建设工程防汛防台专项应急预案2
- 贸大金融学考研历程
- 小学一年级语文下册各单元测评题
- 3牛顿运动定律