当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel怎么获取满足多个条件的数值之和

酷网3周前 (10-06)Excel教程25

有一组数据,我们想要求出剔除某些数据后余下的数据之和,如下所示,要求数据区域中除代码FA、PD、SS之外的分数之和。

可以使用公式:

=SUM(B2:I2)-(SUMIF(B1:I1,”FA”,B2:I2)+SUMIF(B1:I1,”PD”,B2:I2)+SUMIF(B1:I1,”SS”,B2:I2))

然而,如果数据很多,这个公式会很长,很不简洁。此时,我们可以使用SUMPRODUCT函数的公式:

=SUM(B2:I2)-SUMPRODUCT(((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2))

下面对这个公式的运行原理进行解释。公式的主要部分:

SUMPRODUCT(((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2))

包含两部分:

((B1:I1)=({“FA”;”PD”;”SS”}))

(B2:I2)

其中,(B2:I2)被转换为由单元格区域中的数值组成的单行数组:{10,10,10,10,10,10,10,10}。这很好理解。

现在主要看看:

((B1:I1)=({“FA”;”PD”;”SS”}))

Excel将使用单元格区域B1:I1中的每个值依次与“FA”、“PD”、“SS”进行比较,生成一个3行8列的数组:

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE}

其第1行是B1:I1中的每个值与“FA”比较的结果,第2行是与“PD”比较的结果,第3行是与“SS”比较的结果。如下所示。

将上面生成的两个中间数组相乘:

((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2)

即:

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE}*{10,10,10,10,10,10,10,10}

第一个数组的每1行中的每个元素分别与第2个数组中对应的元素相乘,得到:

{0,10,0,0,0,0,0,0;0,0,0,0,10,0,0,0;0,0,0,0,0,0,10,0}

传递给SUMPRODUCT函数:

SUMPRODUCT(((B1:I1)=({“FA”;”PD”;”SS”}))*(B2:I2))

即:

SUMPRODUCT({0,10,0,0,0,0,0,0;0,0,0,0,10,0,0,0;0,0,0,0,0,0,10,0})

得到:

30

从而:

=SUM(B2:I2)-(SUMIF(B1:I1,”FA”,B2:I2)+SUMIF(B1:I1,”PD”,B2:I2)+SUMIF(B1:I1,”SS”,B2:I2))

=80-30

=50

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39438.html

分享给朋友:

相关文章

使用RANK函数进行多列数据统一排名

使用RANK函数进行多列数据统一排名

在Excel中,如何实现多列成绩统一排名 在设计成绩统计表时,有时候将一个班级的学生名单按组分成多列排列。如图 111‑1所示为初二(1)班期末考试成绩统计表,按学生座位号分成3组进行排列,现需要统计学生总分的班级排名,...

在Excel条件格式中使用公式来突出显示单元格

在Excel条件格式中使用公式来突出显示单元格

条件格式与公式相配合,往往能够发挥很大的威力,其中之一就是用来突出显示单元格。如下所示,在“新建格式规则”对话框中: 1. 选择“使用公式确定要设置格式的单元格” 2. 在“为符合此公式的值设置格式”框中输入适当的公式...

Excel必须学会函数之一,countif和SUMIF函数

Excel必须学会函数之一,countif和SUMIF函数

countif函数主要作用是,针对区域中符合条件的数据计数。 不只是单独计算使用,放在公式组里也是很常用的一个 一一一一一一一一一一一一一一一一一一一一一一一一一一一一一一一一一一 一一一一一一一一一一一一一一一一一一...

怎么使用Excel筛选功能?

怎么使用Excel筛选功能?

  我们使用Excel办公学习的时候经常会需要对数据进行筛选,但是仍有一些问题难倒了不少人,今天小编就就给大家带来针对筛选后的序号连续,筛选后取出筛选的内容,筛选后的复制粘贴这三个问题...

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

前几天了Excel RANK.EQ(RANK)总排名、SUMPRODUCT分类排名,有朋友说,RANK和RANK.EQ排出来的名次,如果有相同的数值会出现相同的排名,再继续排下去,会出现名次“间断”的情况,比如,两个第三...

Excel中合并单元格快捷键的方法

Excel中合并单元格快捷键的方法

  不管我们是在工作中还是日常生活中,经常会制作一些Excel表格,那么合并单元格就是家常便饭了。每天都要用鼠标操作实在是太麻烦了,有没有快捷键什么的。如果有快捷键的话,工作起来也比较...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。