Question

EXCEL中关于SUM函数的数组函数应用问题？

在A1:A10中有一个单元格是丙，在A1:D1中有一个单元格是甲，在A1:D10的单元格里，除了前面的丙和甲，其他单元格都为数字，现在求，有甲的那一列和有丙的那一行交叉形成的那一个单元格内的数字。要求用SUM数组函数解决。
=SUM((A2:A10="丙")*(B1:D1="甲")*B2:D10) =0
{=SUM(B2:D10*(A2:A10="丙")*(B1:D1="甲"))} =0
都不对

Answer 1

比如公式 =SUM(A1:A100-B1:B100)
这个公式的意思是A1至A100单元格区域分别减去B1至B100单元格区域的值的总和，
即： =A1-B1+A2-B2+A3-B3+A4-B4........A100-B100 。
这样就是数组计算了,必须要加按SHIFT+CTRL+ENTER三键,只简单的按下回车键是得不出正确值的。
这个公式的计算目的也可以用公式 =SUM(A1:A100)-SUM(B1:B100) 得到结果 但这个公式就不是数组公式了,所以又不必用数组三键了。

能用普通公式进行计算的尽量用普通公式进行设置,这样可以减少内存的占用。

Answer 2

结果单元格输入：
=SUM((B2:D10)*(A2:A10="丙")*(B1:D1="甲"))
然后：Ctrl+Shift+Enter，同时按下。
或者用公式：=sumproduct((B2:D10)*(A2:A10="丙")*(B1:D1="甲"))
sumproduct就是sum的数组函数

追问

为什么上面的答案B2:D10没有括号，你的有括号，能不能写出让我看得懂的计算过程

追答

这就是数组公式，有括号看的比较清楚，已经是最基本的计算公式了，如果看不懂最好我也没办法了。

Answer 3

=SUM((A2:A10="丙")*(B1:D1="甲")*B2:D10)

追问

为什么下面的答案B2:D10在括号里面，你的没有括号？能不能写出计算过程？

追答

B2:D10加括号是多余的，有没有都可以。
假如A5有“丙”字，则A2:A10="丙"得到一个九行一列的数组
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
假如C1有“甲”字，则B1:D1="甲"得到一个一行三列的数组
{FALSE,TRUE,FALSE}
两个数组相乘(B1:D1="甲")*(B1:D1="甲")，得到一个九行三列的数组。
0 0 0
0 0 0
0 0 0
0 1 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
注意，此时TRUE和FALSE经过相乘，都变成0和1的值了。
在EXCEL里，TRUE实际是数值1，FALSE实际是数值0。
而B2:D10正好是九行三列，用上面的数组与这个区域按位相乘，是1的保留了原数值，是0的结果也为0，最后求和就成了。

追问

那如果我把公式改成下面这样的，问号部分怎么写才行
=SUM((A1:A10="丙")*(A1:D1="甲")* ？)

追答

？就是B2:D10呀！对应A2:A10的2到10行，对应B1:D1的B到D列。

追问

不对讶，前面是四行十列，后面B2:D10只有三行十列

追答

哦，没注意看都是从A1开始的。这样不行，公式不能包含A列和第一行，所以必须从B列和第二行开始。

Answer 4

{=SUM(B2:D10*(A2:A10="丙")*(B1:D1="甲"))}

{}是用ctrl+shift+enter组合键加上的吗？
假定在A11求这个值，A11输入
=SUM(B2:D10*(A2:A10="丙")*(B1:D1="甲"))
按F2或将编辑栏激活，ctrl+shift+enter。
肯定没有错，要么就是你的题出错了？

追问

为什么有的B2:D10外面有个括号，有的没有，能不能写出计算过程。

追答

这和运算优先顺序有关。
可以把B2:D10看作是9行3列的矩阵，A2:A10="丙"返回9行1列的矩阵且每个元素等于TRUE或FALSE，B1:D1="甲"返回1行3列的矩阵且每个元素等于TRUE或FALSE，在EXCEL中，逻辑值参加计算时，TRUE=1，FALSE=0，这三个矩阵相乘（注意不是数学意义上的矩阵乘法）得出结果。给你个过程：
B2:D10
数字1 数字2 数字3
数字4 数字5 数字6
数字7 数字8 数字9
数字10 数字11 数字12
数字13 数字14 数字15
数字16 数字17 数字18
数字19 数字20 数字21
数字22 数字23 数字24
数字25 数字26 数字27
A2:A10="丙"
逻辑值11
逻辑值12
逻辑值13
逻辑值14
逻辑值15
逻辑值16
逻辑值17
逻辑值18
逻辑值19
B1:D1="甲"
逻辑值21 逻辑值22 逻辑值23
三个矩阵相乘后
数字1逻辑值11逻辑值21 数字2逻辑值11逻辑值22 数字3逻辑值11逻辑值23
数字4逻辑值12逻辑值21 数字5逻辑值12逻辑值22 数字6逻辑值12逻辑值23
数字7逻辑值13逻辑值21 数字8逻辑值13逻辑值22 数字9逻辑值13逻辑值23
数字10逻辑值14逻辑值21 数字11逻辑值14逻辑值22 数字12逻辑值14逻辑值23
数字13逻辑值15逻辑值21 数字14逻辑值15逻辑值22 数字15逻辑值15逻辑值23
数字16逻辑值16逻辑值21 数字17逻辑值16逻辑值22 数字18逻辑值16逻辑值23
数字19逻辑值17逻辑值21 数字20逻辑值17逻辑值22 数字21逻辑值17逻辑值23
数字22逻辑值18逻辑值21 数字23逻辑值18逻辑值22 数字24逻辑值18逻辑值23
数字25逻辑值19逻辑值21 数字26逻辑值19逻辑值22 数字27逻辑值19逻辑值23
只有"甲""丙"交叉的地方得1，其余都得0，1乘以任何数还得那个数，0乘以任何数得0，最后把27个数加起来就是"甲""丙"交叉的数。明白了？

Answer 5

明白了吧？