Excel公式技巧29： 获取满足多个条件的数值之和

excelperfect

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

图1

可以使用公式：

=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”比较的结果。如下图2所示。

图2

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

((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