Excel 数组公式

数组公式是 Excel 中一个功能极其强大但难度较高的公式。一个数组公式可以一次执行多个计算。基本上，它是一种允许用户对多个值而不是单个值执行任何操作的公式。数组公式返回的值可以是一个数组项或一组数组项。

通常，我们对单个值或值范围执行操作。数组公式应被花括号 ({}) 包裹。在本章中，您将学习 Excel 中的数组公式以及如何在 Excel 数据上执行它。

什么是数组？

数组是多个值的集合，或者可以说数组包含一个以上的值。数组值在 Excel 中位于花括号 ({}) 内部。每当您想对数组执行任何操作时，请将其放入花括号中。即使对于数组公式，您也需要将公式放入花括号中。

在 Excel 数据上执行数组公式并非易事。您必须学习如何执行此操作。

如何输入数组公式？

输入一个简单的公式，然后使用 Ctrl+Shift+Enter 键而不是 Enter 键来获取结果。 Ctrl+Shift+Enter 键会在结果生成时将简单公式转换为数组公式。

基本上，它将简单公式评估为数组公式并相应地返回结果。手动将数组公式输入到单元格中不起作用。手动输入数组公式不会返回任何结果。

数组的表示

数组看起来像 {10; 20; 30} 或 {"UP", "MP", "UK"}。在这里，您会注意到 - 我们使用分号 (;) 和逗号 (,) 来分隔数组内部的值。它们在使用中具有不同的含义。

由分号分隔的数组值表示 Excel 工作表中的垂直数据范围。而对于水平范围，则使用逗号分隔数组内部的值。这两个都是一维数组的示例。二维数组在 Excel 工作表中同时使用分号和逗号来表示水平和垂直范围。

垂直范围 ({10; 20; 30})

垂直范围表示列的值。基本上，如果您想将列的值表示为数组，请在 Excel 工作表中使用垂直范围。

例如，您可以将以下数组值 {10; 20; 30} 理解为可能放置在 A1、A2 和 A3 中的 A 列的值。

水平范围 ({"UP", "MP", "UK"})

水平范围表示行数据。水平范围指 Excel 工作表中行内出现的值。例如，您可以将以下数组值 {"UP", "MP", "UK"} 理解为第 1 行中的值，即 A1、B1 和 C1。

示例 1：数组公式实现

没有数组公式

如果没有数组公式，像这样 (A2:A9) 的多个值只是一个单元格范围，其中包含从 A2 到 A9 选定单元格中的值。

看一个简单数据集的简单场景，我们将向您展示简单公式和数组公式的区别。我们已经选择了一些产品及其价格和订购数量。

步骤 1： 我们有每个产品的总价。现在我们将找到所有产品的总价。

步骤 2： 我们可以通过使用数组公式将这个冗长的过程最小化为一个。写入以下公式以查找所有过程的总和，然后按 Ctrl+Shift+Enter 键。

=SUM(B2:B5*C2:C5)

目前，这个公式就像简单的公式，但是当你按下 Ctrl+Shift+Enter 键时，这个写入的公式将自动成为数组公式 {=SUM(B2:B5*C2:C5)。

"记住 - 在单元格中输入公式后，不要按 Enter 键。"

步骤 3： 请看下面的截图，您可以看到我们得到了与通过冗长过程找到的相同结果。

步骤 4： 如果您按 Enter 键而不是 Ctrl+Shift+Enter，您可能会得到 #VALUE! 错误或不正确的值。

我们没有得到正确的结果值（错误），因为公式没有更改为数组公式。

示例 1

我们举了一个下面的例子，您可以看到 (B2:B13) 中的数据是每个月一年的销售额。(C2:C13) 中的数据是每年一月到十二月的费用。D 列包含每个月的损益，其总计放置在末尾（在单元格 D14 中）。

为了计算年度损益，我们必须首先找到每月的收入，然后计算出的收入总和。对于大量数据来说，这可能是一个漫长的过程。我们可以通过使用数组公式将此过程最小化并使其变得容易。数组公式使此过程变得简短。

多单元格数组公式

多单元格数组公式减少了冗长的计算。通过使用数组公式，您可以一次获得最终结果（年度收入）。您只需要一个数组公式而不是这种冗长的计算。

如果您尝试不使用数组公式，则必须使用以下公式计算年度收入，然后按 Enter 键。

=SUM(B2:B13*C2:C13)

但这将返回不正确的值或 #VALUE! 错误。所以，不要使用这样的公式。如果您想执行像此公式这样的操作，请按 Ctrl+Shift+Enter 键将普通公式转换为数组公式。

数组公式

上述公式的数组公式如下。

{=SUM(B2:B13*C2:C13)}

数组公式被 {}（花括号）包裹。您不能直接在单元格中写入数组公式以获取结果。它将返回空，既没有错误也没有不正确的结果。所以，您需要遵循以下步骤。

步骤 1： 写入简单公式，然后按 Ctrl+Shift+Enter 键以获取作为数组公式的结果。

Ctrl+Shift+Enter 键将简单公式转换为数组公式。

步骤 2： 这一次查看数组公式的结果，它与我们通过冗长计算获得的结果相同。

"您也可以在普通公式后按 Ctrl+Shift+Enter 键而不是 Enter 键来获取数组公式的结果。"

为什么要使用 Excel 中的数组公式？

借助 Excel 公式，用户可以在 Excel 数据上执行计算以完成复杂的任务。它通过使用数组公式减少了用户执行冗长计算以获得最终结果的精力。单个数组公式取代了各种不寻常的公式。

您可以将数组公式用于以下任务，这对它们很有用

• 例如，对满足某些条件的范围内的 N 个最大或最小的值求和。
• 根据每个月的销售和费用数据计算整个年份的收入。在这种情况下，数组公式会忽略计算的利润和损失，然后对总计求和，并用一个公式提供结果。

Excel 数组常量

数组常量是一组静态值。由于数组常量具有静态值，当您将公式复制到其他单元格时，它们永远不会改变。在 MS Excel 中，有三种类型的数组常量。

1. 水平数组常量
2. 垂直数组常量
3. 二维数组常量

水平数组常量

水平数组常量指行中的值。要创建水平数组常量，请在花括号 ({}) 中键入由逗号分隔的值。

例如, {1, 2, 3, 4}

要创建水平数组常量，请在行中选择相应数量的单元格，并在公式栏中输入以下数组。

={1,2,3,4}

现在，按 Ctrl+Shift+Enter 键，看看水平数组常量是什么样子。

垂直数组常量

垂直数组常量位于 Excel 列中。这意味着垂直数组常量的值指的是列中的值。要创建垂直数组常量，请在花括号 ({}) 中键入由分号分隔的值。

例如, {11; 22; 33; 44}

要创建水平数组常量，请在行中选择相应数量的单元格，并在公式栏中输入以下数组。

={10; 20; 30; 40}

现在，按 Ctrl+Shift+Enter 键，看看水平数组常量是什么样子。

二维数组常量

二维数组常量是垂直和水平数组常量的组合。由于它是水平和垂直常量的组合，它同时使用分号和逗号来表示行和列值。

例如，二维数组常量使用分号分隔每个行值，使用逗号分隔每个列值，即 {"a", "b", "c"; 1, 2, 3}。

要创建二维数组常量，请在行和列中选择相应数量的单元格。然后，在公式栏中输入以下数组。

={"a", "b", "c"; 1, 2, 3}

现在，按 Ctrl+Shift+Enter 键，看看水平数组常量是什么样子。