打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel多行、多列、多条件不能求和?两个方法任君选择

在OFFICE官方函数列表中,常用的求和函数有三个:SUM,SUMIF,SUMIFS。

他们每一个都各自专长:

SUM——单行、单列、多行、多列,无条件求和;

SUMIF——行、列,一个条件求和;

SUMIFS——行、列,多条件求和。

根据上表可知,常用的三个求和函数,均无法满足所有的求和要求。

是否有一个函数,可以满足所有的求和要求?

目前OFFICE最新版本为OFFICE 2019,尽管有新增函数,但并没有包含与求和有关的项目。

那么有没有函数组合,满足多条件、多行或多列求和呢?

曾经在《如何快速解决多条件汇总难题——Excel中的三个简单方法》中,提到过数学集合的概念,并将其应用于多条件求和。

既然集合可用于多条件,那么也将其用于多行或多列求和。

实际上多行或多列,在Excel中称之为"区域"。因此只要能够使函数组合识别到数据"区域",就可以进行计算。

与区域计算相关的函数常用的有两个,一个是SUMPRODUCT,一个是OFFSET。

下面就以统计最近一届奥运奖牌榜中国的金牌及银牌的总数为案例分别说明两个方法。

方法一:SUMPRODUCT数组计算实现区域多条件求和

观察SUMPRODUCT的参数是数据区域1、2……255。因此,可直接进行多区域计算,而计算条件,可作为区域参数即可,两者用符号"*"连接。

根据奖牌榜统计要求,包含一个求和区域及两个条件:

求和区域为"金牌"E、"银牌"F两列;

条件1是"最近一届",也就是在年度A列选择最大的年份,这里使用MAX函数即可找到;

条件2是国家为"中国"。

将以上条件分别填入函数SUMPRODUCT中,公式为:

SUMPRODUCT((E2:F10)*(A2:A10=MAX(A2:A10))*(D2:D10="中国"))=44

表哥TIPS:

SUMPRODUCT看似很好用,但由于参数个数的限制,最多不能超过255个,使用时需要注意。

方法二: OFFSET确定求和区域,加上SUMIFS多条件的特点。

根据Excel官方说明中,OFFSET既可以返回一个单元格,也可以返回一片数据区域。其中前三个参数是必需项目。

表哥Tips:

若第2、3两个参数为0,则可为空,但逗号不可省略。

同样以统计最近一届咱国家收获金牌和银牌数为例。这次我们使用

OFFSET来定义区域。

由于所选区域为E、F两行,所以要用到数学中集合的概念。公式为:

OFFSET(E2:E10,,{0,1})

公式中第三个参数的含义是,向右移动的列数。OFFSET中引入了集合{0,1},则可理解为此公式包含两个区域:OFFSET(E2:E10,,0)和OFFSET(E2:E10,,0)。

确定了求和数据区域后,

结合SUMIFS函数的多条件的特点,再公式最外层加上SUM以进行集合运算。组合公式为:

SUM(SUMIFS(OFFSET(E2:E10,,{0,1}),A2:A10,MAX(A2:A10),D2:D10,"中国"))

表哥Tips:

(1) 注意确认OFFSET返回区域是否在系统界内,超出系统极限值,会报错;

(2) SUMIFS的最多条件数是127,注意设置条件数在此范围内。

以上就是多行多列多条件求和的两种常用方法。

你学会了吗?

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Excel教程:3个excel条件求和公式
Excel | DGET、SUMIFS、SUMPRODUCT、LOOKUP、OFFSET、VLOOK...
Excel表格中常用的函数有哪些?有什么作用?
无意间想到的多条件求和公式,实在太好用了。。。
关于EXCEL,用SUMIF函数求和,同时满足两个条件可以吗?
EXCEL中多条件查找的15种方法探讨
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服