SUM 函数示例:计数或求和方差

概括

要计算或汇总差异，可以使用基于SUMPRODUCT 函数和ABS 函数的公式。在所示示例中，F6 中的公式对绝对差异求和：

=SUMPRODUCT(ABS(variance))

其中，variance指的是指定区域D5:D15。换句话说，结果是将 D5:D15 中的值转换为绝对值后的总和。有关此示例中出现的其他公式的详细信息，请参见下文。

在这个例子中，目标是以不同的方式对一组方差进行求和或计数。方差列在 D5:D15 单元格区域，该区域也被称为 “方差”区域。F5 单元格中的第一个公式使用SUM 函数对所有方差求和。

=SUM(variance) // returns -175

F6 中的公式使用ABS 函数和SUMPRODUCT 函数计算绝对方差之和：

=SUMPRODUCT(ABS(variance)) // returns 975

在这个公式中，ABS 将 SUMPRODUCT 函数返回的方差的绝对值存储在一个数组中：

=SUMPRODUCT({25;150;200;225;50;100;25;75;0;75;50})

SUMPRODUCT 函数返回总和，即 975。

注意：这里我们使用 SUMPRODUCT 函数而不是 SUM 函数，是因为 SUMPRODUCT 函数本身就能处理许多数组运算，无需特殊输入公式。这意味着它可以在任何版本的 Excel 中正常运行，无需特殊处理。更多信息，请参阅“为什么使用 SUMPRODUCT”。

F7 中的公式计算绝对值大于零 (0) 的方差个数：

=SUMPRODUCT(--(ABS(variance)>0)) // returns 10

该公式中，ABS 函数返回数组中所有方差的绝对值，如上所述：

{25;150;200;225;50;100;25;75;0;75;50}

使用逻辑表达式来检查方差是否大于零：

{25;150;200;225;50;100;25;75;0;75;50}>0

这将返回一个包含 TRUE 和 FALSE 值的数组：

--{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

双重否定（--）将 TRUE 和 FALSE 值转换为 1 和 0，并将结果直接传递给 SUMPRODUCT 函数：

=SUMPRODUCT({1;1;1;1;1;1;1;1;0;1;1}) // returns 10

最终结果为 10。

F8 中的公式用于计算正方差的数量：

=SUMPRODUCT(--(variance>0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}>0))
=SUMPRODUCT({1;0;1;0;0;1;0;1;0;0;0})
=4

F9 中的公式用于计算负方差：

=SUMPRODUCT(--(variance<0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}<0))
=SUMPRODUCT({0;1;0;1;1;0;1;0;0;1;1})
=6

最后，F10 中的公式统计绝对方差大于 100 的个数：

=SUMPRODUCT(--(ABS(variance)>100))
=SUMPRODUCT(--({25;150;200;225;50;100;25;75;0;75;50}>100))
=SUMPRODUCT({0;1;1;1;0;0;0;0;0;0;0})
=3

在所示示例中，D 列中的方差用作辅助列。但是，如果需要，您也可以直接在数组运算中计算方差，结果相同。例如，要统计正方差，F8 中的公式为：

=SUMPRODUCT(--(variance>0)) // returns 4

这个公式可以改写成这样，用于在内部计算方差：

=SUMPRODUCT(--(C5:C15-B5:B15>0)) // returns 4

以上所有公式中的命名范围方差都可以替换为 C5:C15-B5:B15。