excel图表中的数组公式

Excel中有一种公式叫数组公式，比较复杂，很难理解，很多人都不鼓励。但是数组公式非常强大，可以完成很多不可思议的任务，绝对值得学习。

使用数组公式，可以判断数据是否与指定区域的数据匹配，统计单元格区域的非重复值个数，提取单元格区域的非重复数据，将列数据转换为行数据。本文简单介绍了数组公式，让感兴趣的朋友了解一下，为进一步的研究和应用打下基础。

为什么要学习和使用数组公式？

使用数组公式可以让Excel完成我们认为不可能完成的任务，或者只能由VBA完成的任务。在某些情况下，数组公式可能是一个非常有效的解决方案。当然，好奇心也驱使我们学习进阶来创造更高级的公式，在学习了数组公式的基本用法之后，再去创造更有效的数组公式。掌握了数组公式后，增加了一个新的武器来创建Excel解决方案。我想这可能是我想学数组公式的一些原因。

在某些情况下，除非使用VBA，否则使用数组公式可能是实现目标的唯一方法。在使用普通公式时，我们可能需要辅助列或一些中间步骤。但是，数组公式可以提供一次性解决方案，并节省中间步骤。当然，高级过滤、透视表以及一些新增加的数组函数可以解决使用数组公式实现的任务，但它们不能在输入发生变化时自动更新，或者有一些局限性。但是，数组公式可以立即更新，这是一个主要优势。

什么是数组公式？

在理解数组公式之前，让我们看一下为下面图1所示的工作表查找总销售额的过程。

图1

如图1所示，这四种水果的总销售额是必需的。首先，用公式找出每种水果的销量，然后相加。总共使用了四个公式。事实上，我们只能使用一个公式来计算总销售额，如图2所示。

图2。

在单元格C7中输入公式：

=SUM(B2:B5*C2:C5)

同时按Ctrl+Shift+Enter完成公式输入。

这个公式是一个数组公式，即它有一个运算符，对一组数据而不是单个数据进行运算，传输的结果也是一组数组，最终的结果可能是单个数据，也可能是一组数据。

请注意，当我们按下Ctrl Shift Enter键完成输入时，Excel会自动在公式两侧添加花括号{}，而无需手动输入。

公式原理

以上面显示的工作表为例，看看数组公式的计算过程，了解其运算原理。

首先，公式中的B2:B5和C2:C5分别被单元格数据替换为数组：

=SUM({ 5.8；1.2;1.1;3.5}*C2:C5)

=SUM({ 5.8；1.2;1.1;3.5}*{100;350;200;300})

然后，将两个数组的对应元素相乘，得到：

=SUM({ 580；420;220;300})

最后，阵列被求和作为求和函数的参数，并且获得最终结果2270。

可以看出，数组公式是处理一组或一系列数据而不是单个数据的公式。它可以返回单个值，如本示例所示，也可以返回一组数据，如以下示例所示。

选择一列中的任意9个单元格，并输入以下数组公式：

=ROW(1:9)

结果如下图3所示。

图3。

该公式生成一个数组{ 1；2;3;4;5;6;7;8;9}，然后依次输入单元格。

注意，输入公式后，记得按Ctrl+Shift+Enter。

在本文中，任何需要输入数组公式的人都是指输入公式内容后按Ctrl Shift Enter键完成数组公式的输入。

再看一个例子，理解数组公式的逻辑运算。

下图4所示的工作表记录了不同卖家销售的各种产品的数量。

图4

要计算张三或李四卖出的手机数量，可以使用数组公式：

=SUM((A2:A10=“手机”)*((B2:B10=“张三”)(B2:B10=“李四”))*(C2:C10))。

结果如下图5所示。

图5

该公式创建三个数组：

第一个数组是一系列TRUE或FALSE值，这是单元格区域A2:A10中的数据与“手机”进行比较的结果。请注意，Excel会将“手机”的数量扩展为与比较的单元格数量相同(下同)。结果是：{真；假；真；假；假；假；真；假；真} .

第二个数组由一系列0或1组成，这是将单元格B2:B10中的数据与“张三”和“李四”进行比较的结果。其中，将单元格区域B2:B10与“张三”进行比较，生成由一系列TRUE或FALSE值组成的数组：{ TRUE假；假；假；假；真；真；真；FALSE}，与“李四”相比，它还会生成一个由一系列TRUE或FALSE值组成的数组：{ FALSE假；真；真；真；假；假；假；FALSE} .将两个数组相加，Excel会将TRUE转换为1，将FALSE转换为0，得到数组{ 1；0;1;1;1;1;1;1;0}。

第三个数组由单元格C2:C10中的值组成，即{ 1200；200;300;120;220;50;600;100;800}。

然后，将这三个数组对应的元素相乘。与前面的数组加法一样，Excel在对数组进行乘法运算时，会将“真”和“假”分别转换为1和0。因此，三个数组相乘的公式是：

{1;0;1;0;0;0;1;0;1}*{1;0;1;1;1;1;1;1;0}*{1200;200;300;120;220;50;600;100;800}

这三个数组的乘法结果也是一个数组，每个元素都是这三个数组对应元素的乘法结果，即：

{1200;0;300;0;0;0;600;0;0}

数组作为SUM函数的参数，最终的结果是2100(=1200 300 600)，也就是张三和李四卖出的手机数量。

详细操作过程如下图6所示。

图6

你可以看到：

乘法(*)用于公式中的逻辑AND，即遵循与AND运算相同的规则；使用加法()进行逻辑“或”，并遵循与“或”运算相同的规则。模运算模拟异或运算。

执行算术运算时，Excel将“真”和“假”转换为值1和0。

公式示例

数组公式的应用实例有很多，可供参考。下面举三个简单的例子，帮助读者进一步理解数组公式的原理。

例1:计算及格学生的平均成绩。

在图7所示的工作表中，将及格学生的分数平均，并使用数组公式：

=平均值(IF(B2:B960，B2:B9，FALSE))

图7

这个数组公式使用IF函数来测试多个单元格。比较单元格区域B2:B9中的值是否大于60，返回包含布尔值{ TRUE真；真；假；真；假；真；FALSE}，然后IF函数根据数组中的值返回B2:B9中的值，如果为TRUE，则返回FALSE。扩展公式如下：

=平均值(如果({真；真；真；假；真；假；真；FALSE}，{ 89；92;78;56;88;58;95;55}，{ FALSE假；假；假；假；假；假；FALSE}))

请注意，原始公式末尾的FALSE被扩展为与前一个数组匹配的合适大小的数组。

中频功能测试完成后，获得以下中间结果：

=AVERAGE({ 89；92;78;假；88;假；95;FALSE})

AVERAGE函数忽略布尔值(真或假)，只取数值的平均值。

例2:计算前3名学生的分数总和。

以图7所示的工作表为例，要求前3名学生的成绩总和。

您可以使用数组公式：

=SUM(LARGE(B2:B9，ROW(1:3)))

ROW(1:3)返回数组{ 1；2;3}。LARGE函数依次取单元格区域B2:B9中的最大值、第二大值和第三大值，返回数组{ 95；92;89}，然后将它们作为SUM函数的参数进行求和。

示例3:计算数字单元格中数字的总和。

单元格A1中的值是12345，1 2 3 4 5的值应该通过公式得到。

您可以使用数组公式：

=SUM(1*MID(A1，ROW(间接(“1:”LEN(A1))，1))

LEN函数计算单元格A1中字符的长度，因此上面的公式变为：

=SUM(1*MID(A1，ROW(间接(“1:6”))，1))

间接函数返回对第1行到第6行的引用：

=SUM(1*MID(A1，ROW(1:6)，1))

然后扩展到：

=SUM(1*MID(A1，{ 1；2;3;4;5;6},1))

MID函数依次接受单元格A1中的字符，一次一个：

=SUM(1 * {“1”；"2";"3";"4";"5";"6"})

将1与数字文本相乘，将其转换为数字：

=SUM({ 1；2;3;4;5;6})

最后，SUM函数对数字数组求和。

标签

数组极大地扩展了公式的能力，为Excel的应用开辟了新的领域。但是，想要掌握并熟练运用数组公式，需要大量深入的练习，但绝对值得学习。