SUM 函数示例:对前N个值求和

概括

要计算某个范围内最大的 n 个值的总和，可以使用基于LARGE 函数的公式。在所示示例中，单元格 E5 中的公式为：

=SUM(LARGE(data,SEQUENCE(D5)))

其中data是命名区域B5:B16。结果为 190，即 70、65 和 55 之和。

通用配方

=SUM(LARGE(range,SEQUENCE(n)))

解释

在这个例子中，目标是对一组数据中最大的n个值求和，其中n是一个可以轻松更改的变量。为了方便起见，我们将 B5:B16 单元格区域命名为 “data”。从总体上看，解决方案可以分为两个步骤：（1）从数据集中提取最大的n个值；（2）对提取的值求和。根据 Excel 版本的不同，有几种方法可以解决这个问题。无论采用哪种方法，以下所有解决方案都依赖于 LARGE 函数。

大型功能

LARGE函数旨在返回指定范围内的第 n 个最大值。例如：

=LARGE(range,1) // 1st largest
=LARGE(range,2) // 2nd largest
=LARGE(range,3) // 3rd largest

通常情况下，LARGE 函数只返回一个值。但是，如果您将类似 {1,2,3} 的数组作为第二个参数k传递给 LARGE 函数，则 LARGE 函数将返回一个结果数组，而不是单个结果。例如，以下公式将返回 B5:B16 单元格区域中的 3 个最大值：

=LARGE(data,{1,2,3}) // returns {70;65;55}

请注意，LARGE 函数的结果是一个数组。通过将 LARGE 函数嵌套在 SUM 函数之后，我们可以得到数据中三个最大值的总和：

=SUM(LARGE(data,{1,2,3})) // returns 190

SUM 函数返回的结果为 190，即 70、65 和 55 的总和。

根据上述公式，接下来的挑战是如何创建所需的数组，以便从数据集中提取前n 个值。这取决于所使用的 Excel 版本。

当前 Excel

最新版本的 Excel 支持动态数组公式，并提供了一系列函数，使数组操作更加便捷。其中一个函数是SEQUENCE ，它旨在动态生成数组。在所示示例中，E5 单元格中的公式使用 SEQUENCE 函数，根据D5 单元格中n的值创建一个数值数组。

=SUM(LARGE(data,SEQUENCE(D5)))

从内到外，SEQUENCE 函数的rows 参数设置为单元格 D5。由于 D5 中的值为 3，SEQUENCE 函数会生成如下所示的顺序数组：

SEQUENCE(D5) // returns {1;2;3}

数值数组将直接返回给 LARGE 函数作为k参数：

=SUM(LARGE(data,{1;2;3}))

解决方案按上述步骤进行：

=SUM(LARGE(data,{1;2;3}))
=SUM({70;65;55})
=190

最终结果为 190，如上面的工作表所示。

传统 Excel

在不支持动态数组或 SEQUENCE 函数的旧版 Excel 中，我们需要采用不同的方法。一个简单的解决方案是将传递给 LARGE 函数的数值数组硬编码为数组常量，然后改用SUMPRODUCT 函数：

=SUMPRODUCT(LARGE(data,{1,2,3}))

这个公式在旧版本的 Excel 中运行良好，但由于数组是硬编码在 LARGE 函数中的，因此它并非动态解决方案，无法使用单元格 D5 中的n值。此外，随着n 值增大，输入像 {1,2,3,4,5,6,7,8,9} 等更长的数组常量会变得非常繁琐。为了解决这个问题，您可以使用下面这个更高级的公式。

动态 n

在旧版 Excel 中创建数值数组的经典方法是使用 ROW和INDIRECT函数。例如，要生成一个从 1 到 10 的数值数组，可以使用如下公式：

=ROW(INDIRECT("1:10")) // returns {1;2;3;4;5;6;7;8;9;10}

INDIRECT 函数将文本字符串“1:10”转换为范围1:10，并将转换后的范围返回给 ROW 函数。ROW 函数返回与 1:10 对应的 10 个行号，并以数组形式返回，如下所示：

{1;2;3;4;5;6;7;8;9;10}

请注意，这实际上是一个垂直数组，如分号 (;) 所示，但 LARGE 函数可以接受垂直或水平数组作为 k的值。为了在工作表中应用此方法，我们需要调整公式，将 n 的值与 INDIRECT 函数内部的字符串“1:”连接起来，如下所示：

=SUMPRODUCT(LARGE(data,ROW(INDIRECT("1:"&D5))))

这个公式现在是动态的。当n的值改变时，ROW 和 INDIRECT 函数会创建一个反映当前值的新数组，LARGE 函数会像以前一样提取前n 个值，而 SUMPRODUCT 函数会返回总和。

详情页