Excel｜数组公式与多条件、求和、sumproduct函数
我的图书馆
Excel｜数组公式与多条件、求和、sumproduct函数
1 数组公式与多条件判断and函数数组公式可以认为是Excel对公式和数组的一种扩充，换一句话说，是Excel公式在以数组为参数时的一种应用。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。如有以下数据：姓名面试官1面试官2面试官3是否被录取求职人员1合格合格不合格FALSE求职人员2合格不合格合格FALSE求职人员3合格不合格不合格FALSE求职人员4合格合格合格TRUE求职人员5合格合格合格TRUE对于求职人员需要由三个面试官判断全部合格，才可以录用，可以应用数组公式和多条件判断。“是否被录取”一列下面的单元格输入公式如下：=AND(B2:D2='合格')在单元格编辑状态下（也就是光标点击进入单元格的情况下（单元格按F2时可进入编辑状态）），按ctrl+Shift+Enter，即可以将上述公式框在{}内，如下所示：{=AND(B2:D2='合格')}此时的公式即是数组公式。数组公式会对数组（或引用的区域）中的每一个元素的值逐一参与计算，如果有n个元素，则会形成n重循环的n个值；如果是1个数组（或引用的区域），则是对这1个数组（或引用的区域）的每一个元素的值逐一参与计算；如果是m个数组（或引用的区域），则是对这m个数组（或引用的区域）的每一个元素的值逐一参与计算，每个数组（或引用的区域）先用第一个元素的值参与计算、然后是每个数组（或引用的区域）的第2个、第3个...；多条件判断的函数除了and()以外，还有or()。2 逻辑值的数值取值当单元格逻辑值是'true'时，应用公式时可以转换为数字'1'，当单元格逻辑值是'false'时，应用公式时可以转换为数字'0'。如以下数据：员工姓名所属部门业绩工龄是否发放奖金发放资金金额员工1销售部144003FALSE0员工2人力部180009FALSE0员工3人力部2520008TRUE500员工4销售部3240005FALSE0员工5工程部32400010TRUE500员工6工程部360004FALSE0员工7人力部37200011TRUE500员工8销售部4320005FALSE0员工9销售部144002FALSE0员工10人力部168008FALSE0员工11工程部360004FALSE0同时满足业绩超过30000元以及工龄在5年以上两个条件即可发放500的资金。“是否发放奖金”一列便可以利用公式：=AND(C2>3)“发放资金金额”列便可以利用公式：=E2*5003 数组公式与多条件判断函数、求和函数如有以下数据：日期规格金额惠普2654爱普生2780佳能2432爱普生3223爱普生3564佳能1432惠普1987惠普3465爱普生2683佳能2154按时间段统计每种产品销售金额：时段间惠普爱普生佳能1987678714328106122506018便可在“惠普”列的下一个单元格应用以下公式：=SUM(($A$2:$A$11<$A15)*($B$2:$B$11=B$14)*($C$2:$C$11))4 数组公式与sumproduct()函数sumproduct()函数是指在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。与数组公式有功能相似之处。如有以下数据：产品名称销售数量销售单价金额A3打印纸（箱）551206600迷你文件柜681198092鼠标70996930A4打印纸（箱）45115517526797总销售额26797在“总销售额”下的单元格中可以应用以下公式：=SUMPRODUCT(B2:B5*C2:C5)如有以下一个月（从1号-31号）的收进、发出的数据，便可以应用SUMPRODUCT()进行一个月的收进、发出的汇总。整月收进汇总整月发出汇总结存数量1号2号……收进发出收进发出……583524553332上述“58”的单元格便是应用以下公式：=SUMPRODUCT((MOD(COLUMN(I5:BR5),2)=1)*I5:BR5)得出的值。上述(COLUMN(I5:BR5))用于返回某一引用的列号；(MOD(COLUMN(I5:BR5),2)=1)形成一个逻辑值，逻辑值的'true'对应数值'1'，逻辑值的'false'对应数值'0'。这样与区域I5:BR5的每一个单元格的列号相对应，由(MOD(COLUMN(I5:BR5),2)=1)形成一个逻辑值数组，或一个0、1序列的数组，用这种间接的方式，可以对每天收进的数值或行汇总。
喜欢该文的人也喜欢？多条件求和，不要用数组【excel吧】_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0成为超级会员，使用一键签到本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：168,402贴子：
？多条件求和，不要用数组收藏
科目&&&&&& 余额
1001现金&&&&&& 4,079,000
1002银行存款&&&&&& 7,319,000
1002银行存款&&&&&& 5,702,000
1002银行存款&&&&&& -9,866,000
1002银行存款&&&&&& 2,189,000
1002银行存款&&&&&& 2,621,000
1002银行存款&&&&&& -8,146,000
1002银行存款&&&&&& 1,222,000
1002银行存款&&&&&& 8,940,000
1002银行存款&&&&&& -5,510,000
1111应收票据&&&&&& 4,653,000
1133其他应收款&&&&&& 2,488,000
1133其他应收款&&&&&& 1,869,000
1133其他应收款&&&&&& 4,389,000
1133其他应收款&&&&&& 2,289,000
1133其他应收款&&&&&& 577,000
1133其他应收款&&&&&& 7,003,000
求科目列始于"100"的,余额列大于0的合计
=SUMPRODUCT((LEFT(A2:A18,3)="100")*(B2:B18&0)*B2:B18)
=SUMPRODUCT((LEFT(A:A,3)="100")*(B:B&0)*B:B) 整列引用不行吗？显示值为#NUM!，还有数组公式也不能引用整列 可以有引用整列的公式吗？
如果你用07或更高版本,可以试试sumifs
回复：4楼用的是03班的试过：A列前几行有空行，=SUMPRODUCT((LEFT(A5:A200,3)="100")*(B5:B200&0)*B2:B200)会出现#VALUE!错误
登录百度帐号Excel多条件引用，你会几种方法？
我的图书馆
Excel多条件引用，你会几种方法？
本文由网友 YJBoer&授权原创首发了解如何投稿请点击→&在工作中，有许多数据处理需要多条件引用，下面介绍几种多条件引用的方法，需要收藏和分享的友友自便吧。大牛绕道勿喷，谢谢！1、INDEX+MATCH&首先，我们先写出MATCH函数：多个条件和区域我们用&连接，精准查找。（如图）由于数组，我们要打上大括号（不是手工输入，而是ctrl+shift+enter组合键出来的）。我们得到的是2，说明“1班、叶一、语文”的分数是在条件区域的第2行。我们再用INDEX函数，引用出相应条件的分数。我们看下数据区域第2行的分数是多少？=INDEX(D4：D16，2)，得出是98分。&我们再把这两个函数组合一下{=INDEX(D4:D16,MATCH(F6&G6&H6,A4:A16&B4:B16&C4:C16,0))}，当然别忘记MATCH函数中用应用到数组，需要大括号括起来（ctrl+shift+enter组合键）。&2、VLOOKUP&很多朋友认为VLOOKUP只能单一的往右引用，其实VLOOKUP不仅可以往左引用，也是可以多条件查找的。我们用if（{1,0}）重组数组的方法，就可以完成以上要求。我们用 {=VLOOKUP(F7&G7&H7,IF({1,0},A4:A16&B4:B16&C4:C16,D4:D16),2,0)}，多个条件用和区域&连接（大括号输入方法同上）&3、OFFSET+MATCH利用不同的组合函数可以得到相同的结果，前面我们用了INDEX+MATCH的组合函数完成多条件引用，OFFSET+MATCH的组合也是可以的，只要了解每一个函数的用法，万变不离开其中。{=OFFSET(D4,MATCH(F8&G8&H8,A4:A16&B4:B16&C4:C16,0)-1,0)} ，多个条件用和区域&连接（大括号输入方法同上）。&4、LOOKUPLOOKUP是一个非常强大的函数，运用方法变化多种，具体用法可以度娘一下。=LOOKUP(1,0/((A5:A16=F9)*(B5:B16=G9)*(C5:C16=H9)),D5:D16)&5、SUM借用多条件求和引用函数{=SUM((A5:A16=F10)*(B5:B16=G10)*(C5:C16=H10))*D5:D16}&6、SUMIFS借用多条件求和引用函数=SUMIFS(D5:D16,A5:A16,F11,B5:B16,G11,C5:C16,H11)&7、SUMPRODUCT借用多条件求和引用数值=SUMPRODUCT((A5:A16=F12)*(B5:B16=G12)*(C5:C16=H12)*D5:D16)&8、DSUM借用多条件求和引用数值=DSUM(A4:D16,4,F17:H18)&多条件求和是可以用作多条件引用，不过被引用的单元格一定要是数值，如果是文本格式就会报错了，这个是局限性。不过，倘若掌握前面四种方法，我们也足以应付工作中的引用需求了。&●本文编号368，以后想阅读这篇文章直接输入368即可●输入m可以获取到全部文章目录●输入c可以获取到全部动画下载地址
[转]&[转]&[转]&
喜欢该文的人也喜欢查看: 429|回复: 7
数组多条件求和，有附件
阅读权限20
在线时间 小时
本帖最后由 yehesuifeng 于
22:19 编辑
不同区域不同产品，提成比例也不同，如何实现计算各个区域的提成数额?
捕获.PNG (14.67 KB, 下载次数: 0)
22:18 上传
(8 KB, 下载次数: 4)
22:18 上传
点击文件名下载附件
阅读权限70
在线时间 小时
& & & & & & & &
K3:=SUMPRODUCT((E3:J3*N3:S3))复制代码
是这样的吗？下拉。
阅读权限50
在线时间 小时
=SUM(E3:J3*N3:S3)复制代码
这个意思？
阅读权限20
在线时间 小时
是这样的吗？下拉。
我举得例子比较特殊，已更新，希望根据查找实现。希望你再帮忙看看。
阅读权限20
在线时间 小时
这个意思？
多谢回复，我举得例子可能过于特殊，我已经更新，希望通过在M列中查找A列的值，然后相乘求和。
麻烦在帮忙看看
阅读权限50
在线时间 小时
多谢回复，我举得例子可能过于特殊，我已经更新，希望通过在M列中查找A列的值，然后相乘求和。
麻烦在帮 ...=SUMPRODUCT(E3:J3*OFFSET(N$1:S$1,MATCH(D3,M:M,)-1,))复制代码
阅读权限20
在线时间 小时
非常正确，多谢。
阅读权限70
在线时间 小时
本帖最后由 excel我最爱 于
11:16 编辑
多谢回复，我举得例子可能过于特殊，我已经更新，希望通过在M列中查找A列的值，然后相乘求和。
麻烦在帮 ...
=SUMPRODUCT(OFFSET($O$2:$T$2,MATCH(E3,$N$3:$N$5,),,)*F3:K3)复制代码
(4.32 KB, 下载次数: 1)
11:15 上传
点击文件名下载附件
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师病毒安全知识,电脑网络技术,手工杀毒方法,答疑解决笔记
Excel多条件求和数组公式中的或运算
　　前文中的数组公式通过如　{=SUM((A2:A100=&销售部&)*(C2:C100=&男性&)*(F2:F100))}　这样的形式实现对A列和C列中分别为&销售部&和&男性&的员工工资（F列）数的求和（假设工资表有100行，A列为&部门&，C列为&性别&，F列为&工资&）。不过后来遇到一个问题，再以前面的这个例子说明，要求求A列为&销售部&和&信息部&，C列中为&男性&的员工工资（F列）的总额，那么就要在数组公式中用到一个或运算，就不能只用&*&连接了，试了下原来是用&+&来连接就可以了，该数组公式如下：　　{=SUM(((A2:A100=&销售部&)+((A2:A100=&信息部&))*(C2:C100=&男性&)*(F2:F100))}当然输入时，不用输入大括号{}，但等号别忘了，输入完，按ctrl+shift+Enter确认该数组公式，别直接按回车，那就不是数组，会出错的。　　例不多述，举一反三。
>> 除非说明均为原创，如转载请注明来源于
] 相关的文章:
打开速度太慢了。 是不是空间问题？流风33 于
21:18:07 回复还行。什么网络？电信、网通、铁通？
楼上的说的没错，我想应该是你空间的问题。流风33 于
10:27:45 回复至少我用电信上的还行
狼人过来看看，博客不错！！！
(window.slotbydup=window.slotbydup || []).push({
id: '127222',
container: s,
size: '160,600',
display: 'inlay-fix'}