☰

数组公式语法

tag-icon • tag-icon microsoft-excel worksheet-function
数组公式语法

我想知道如何在 Excel 表中为以下长公式给出简洁的公式：

= (A1*A6)+(B1*B6)+(C1*C6)...

我是否应该使用 sum 函数，或者是否有其他函数可以实现此目的？我认为使用带SUM函数的冒号应该会有所帮助，但我不知道如何使用它。

答案1

您正在寻找乘积和功能。

=SUMPRODUCT(A1:C1,A6:C6)

这将返回两个（或更多）范围内相应项的乘积之和。

在此处输入图片描述

正如您从我链接到的 Microsoft 文档中看到的，范围不需要是单行或单列（尽管它们必须具有相同的尺寸）。

SUMPRODUCT 可以将最多 255 个不同范围内的值相乘。例如，=SUMPRODUCT(A1:C1,A6:C6,A11:C11)与 相同=A1*A6*A11+B1*B6*B11+C1*C6*C11。

答案2

SUM函数不起作用，因为它只是添加元素。您需要将值相乘前传递给 SUM 类似=SUM(A1*A6, B1*B6, C1*C6, D1*D6, E1*E6)

当然，你也可以使用，=A1*A6 + B1*B6 + C1*C6 + D1*D6 + E1*E6其输入量与SUM

有很多更好的解决方案。其中一个已经由 Blackwood 提出。另一种替代方法是使用数组公式。您可以看到一个与您的 Microsoft 完全相同的示例：

数组公式语法

一般来说，数组公式使用标准公式语法。它们都以等号 (=) 开头，您可以在数组公式中使用大多数内置 Excel 函数。主要区别在于，使用数组公式时，您可以按Ctrl+ Shift+Enter输入公式。执行此操作时，Excel 会用括号将数组公式括起来 - 如果您手动输入括号，公式将转换为文本字符串，并且无法正常工作。

数组函数是构建复杂公式的一种非常有效的方法。数组公式=SUM(C2:C11*D2:D11)与以下相同：

=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

要在您的案例中使用数组公式，您可以输入（当然，您需要相应地更改数组的最后一个元素）

=SUM(A1:E1*A6:E6)

然后按Ctrl+ Shift+Enter

一旦你掌握了数组公式的概念，它就可以应用于大多数其他公式，你甚至可以忘记SUMPRODUCT

更新：

较新的 Excel 版本在许多情况下甚至会自动使用数组公式

从 2018 年 9 月 Office 365 更新开始，任何可以返回多个结果的公式都会自动将它们向下溢出或溢出到相邻单元格中。这种行为变化还伴随着几个新的动态数组函数。动态数组公式，无论是使用现有函数还是动态数组函数，都只需输入到单个单元格中，然后按 Enter 确认。以前，旧式数组公式需要先选择整个输出范围，然后使用Ctrl+ Shift+确认公式Enter。它们通常被称为 CSE 公式。

数组公式指南和示例

数组公式是一个非常强大的工具。但是要小心使用。每次需要编辑时，一定不要忘记按Ctrl++ShiftEnter

为什么要使用数组公式？

如果您有在 Excel 中使用公式的经验，那么您就会知道您可以执行一些相当复杂的操作。例如，您可以计算任意给定年数内的贷款总成本。您可以使用数组公式执行复杂的任务，例如：

  • 计算单元格区域所包含的字符数。

  • 仅对满足特定条件的数字求和，例如某个范围内的最低值或介于上限和下限之间的数字。

  • 对一系列值中的每个第 n 个值求和。

数组公式还具有以下优点：

  • 一致性：如果您单击从 E2 向下的任何单元格，您都会看到相同的公式。这种一致性有助于确保更高的准确性。

  • 安全：您无法覆盖多单元格数组公式的组件。例如，单击单元格 E3 并按 Delete。您必须选择整个单元格范围（E2 到 E11）并更改整个数组的公式，或者保留数组原样。作为一项额外的安全措施，您必须按Ctrl+ Shift+Enter确认对公式的更改。

  • 更小的文件大小：您通常可以使用单个数组公式，而不是多个中间公式。例如，工作簿使用一个数组公式来计算 E 列中的结果。如果您使用标准公式（例如 =C2*D2、C3*D3、C4*D4…），则需要使用 11 个不同的公式来计算相同的结果。

由于访问模式已知，因此速度也更快。现在，无需单独进行 11 种不同的计算，而是可以进行矢量化并并行执行，从而利用 CPU 中的多个内核和 SIMD 单元

答案3

另一种方法是将表达式 =A1*A6 放在 A7 中，然后根据需要复制，然后对第 $7$ 行求和以获得最终答案。它不会像您希望的那样在一个单元格中完成，但有时获得中间乘积会很方便。我使用过这两个版本。这个版本对我来说更像 Excel，但您的口味可能有所不同。

答案4

如果第 1 行和第 6 行中没有其他内容SUMPRODUCT()，那么您可以使用这个想法在此评论中提到. 根据您的问题概述，您可以使用=SUMPRODUCT(1:1,6:6)

相关内容