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

Question

如题所述

Answer 1

有一组数据，我们想要求出剔除某些数据后余下的数据之和，如下所示，要求数据区域中除代码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