excel图表中的数组公式( 二 ) _知识经验

如下图4所示的工作表，记录着各种产品由不同的销售人所售卖的数量。

文章插图
图4
想要计算张三或者李四所销售的手机数量，可以使用数组公式：
=SUM((A2:A10=”手机“)*((B2:B10=”张三“)+(B2:B10=”李四“))*(C2:C10))
结果如下图5所示。

文章插图
图5
这个公式创建了3个数组：
第一个数组是一系列的TRUE或FALSE值，是由单元格区域A2:A10中的数据与“手机”比较后的结果。注意，Excel会将“手机”的数量扩展到与所比较的单元格数量相同（下面的相同）。结果数组为：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE} 。
第二个数组由一系列的0或1组成，是由单元格B2:B10中的数据与“张三”和“李四”比较后的运算结果。其中，单元格区域B2:B10与“张三”比较生成一系列TRUE或FALSE值组成的数组：{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}，与“李四”比较也生成一系列TRUE或FALSE值组成的数组：{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE} 。将这两个数组相加，此时Excel将TRUE转换为1，FALSE转换为0，得到数组{1;0;1;1;1;1;1;1;0} 。
第三个数组由单元格C2:C10中的数值组成，即{1200;200;300;120;220;50;600;100;800} 。
然后，将这3个数组对应的元素相乘。与前面数组相加相同，在数组相乘时，Excel将TRUE和FALSE分别转换为1和0 。因此，3个数组相乘的公式为：
{1;0;1;0;0;0;1;0;1}*{1;0;1;1;1;1;1;1;0}*{1200;200;300;120;220;50;600;100;800}
这3个数组相乘的结果也是一个数组，其每个元素为这3个数组对应元素相乘的结果，即：
{1200;0;300;0;0;0;600;0;0}
该数组作为SUM函数的参数得到最终的结果为2100（=1200+300+600），即张三和李四销售的手机数量。
详细的运算过程如下图6所示。

文章插图
图6
可以看出：
数组公式中将乘法（*）用于逻辑与，即遵守与AND操作相同的规则；将加法（+）用于逻辑或，遵守与OR操作相同的规则。MOD运算模拟异或XOR操作。
在进行算术运算时，Excel会将TRUE和FALSE转换成数值1和0 。
数组公式示例
有很多数组公式应用示例可供参考学习。下面列举3个简单的示例，帮助读者进一步理解数组公式原理。
示例1：计算及格的学生成绩的平均值
在图7所示的工作表中，求及格的学生成绩的平均值，使用数组公式：
=AVERAGE(IF(B2:B9>60,B2:B9,FALSE))

文章插图
图7
这个数组公式使用IF函数来测试多个单元格。比较单元格区域B2:B9中的数值是否大于60，返回一个包含布尔值的数组{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}，然后IF函数根据该数组中的值，如果为TRUE则返回B2:B9中的值，为FALSE则返回FALSE 。扩展后的公式如下：
=AVERAGE(IF({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{89;92;78;56;88;58;95;55},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))
注意，在原公式最后的FALSE被扩展成一个与前面数组相匹配的适合大小的数组。
在IF函数测试完成后，得到下面的中间结果：
=AVERAGE({89;92;78;FALSE;88;FALSE;95;FALSE})
AVERAGE函数忽略布尔值（TRUE或FALSE），仅对数值求平均值。
示例2：计算排名前3的学生成绩之和
仍以图7所示的工作表为例，要求前3名的学生成绩之和。
可以使用数组公式：
=SUM(LARGE(B2:B9,ROW(1:3)))
其中，ROW(1:3)返回数组{1;2;3} 。LARGE函数依次取单元格区域B2:B9中的最大值、第2大及第3大值，返回数组{95;92;89}，然后将其作为SUM函数的参数求和。