Excel中有一种公式称为数组公式。 比较复杂，有点难以理解。 很多人都避之不及。 然而，数组公式非常强大，可以完成许多不可思议的任务。 这绝对值得学习。
使用数组公式，可以判断数据是否与指定范围内的数据匹配、统计单元格范围内唯一值的数量、提取单元格范围内唯一的数据、将列数据转换为行数据、...、本文仅简单介绍一下数组公式，以便感兴趣的朋友能够理解，为进一步研究和应用打下基础。
为什么你应该学习和使用数组公式
使用数组公式，Excel可以完成我们认为不可能完成的任务，或者只能使用VBA才能完成的任务，并且在某些情况下，数组公式可以是一个非常有效的解决方案。 当然，好奇心也会驱使我们在学习了数组公式的基本使用之后想要学习更多知识来创建更高级的公式，创建更有效的数组公式。 掌握数组公式后，我在创建 Excel 解决方案时有了新武器。 我想这可能是想要学习数组公式的一些原因。
在某些情况下，使用数组公式可能是实现目标的唯一方法，除非使用 VBA。 使用普通公式时，我们可能需要辅助列或一些中间步骤，但是数组公式可以提供一次性解决方案并消除中间步骤。 当然，高级过滤、数据透视表和一些新的数组函数可以解决使用数组公式实现的任务，但它们不能在输入变化时自动更新或有一定的限制。 然而，数组公式可以立即更新，这是它的一大优点。
什么是数组公式
在了解数组公式之前，我们先看一下查找工作表的总销售额的通常过程，如下图 1 所示。
图1
如图1所示，要求这四种水果的总销售额，首先使用公式求出每种水果的销售额，然后将它们相加。 总共使用了4个公式。 事实上，我们只需使用一个公式即可计算出总销售额，如图 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})
最后将数组作为SUM函数的参数进行求和，得到最终结果2270。
如您所见，数组公式是处理一组或一系列数据而不是单个数据的公式。 它可以返回单个值，如本例所示，也可以返回一组数据，如下面的示例所示。
选择一列中的任意 9 个单元格并输入以下数组公式：
=行(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
此公式创建 3 个数组：
第一个数组是一系列 TRUE 或 FALSE 值，它是将单元格范围 A2:A10 中的数据与“手机”进行比较的结果。 请注意，Excel 会将“电话”的数量扩展为与要比较的单元格数量相同（下同）。 结果数组为：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。
第二个数组由一系列0或1组成，这是将单元格B2:B10中的数据与“张三”和“李四”进行比较的结果。 其中，单元格区域B2:B10与“张三”进行比较，生成由一系列TRUE或FALSE值组成的数组：{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}，与“李四”比较还生成一个 TRUE 或 FALSE 值数组：{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;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 将 TRUE 和 FALSE 分别转换为 1 和 0。 因此，3个数组相乘的公式为：
{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 运算相同的规则； 使用加法 (+) 进行逻辑或时，遵循与或运算相同的规则。 MOD 运算模拟 XOR 运算。
在执行算术运算时，Excel 将 TRUE 和 FALSE 转换为数值 1 和 0。
数组公式示例
有很多数组公式应用实例可供参考和学习。 下面举三个简单的例子来帮助读者进一步理解数组公式的原理。
示例 1：计算及格学生的平均分
在图 7 所示的工作表中，要查找及格学生分数的平均值，请使用数组公式：
=(IF(B2:B9>60,B2:B9,假))
图7
此数组公式使用 IF 函数来测试多个单元格。 比较单元格区域B2:B9中的值是否大于60，返回一个包含布尔值的数组{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}，然后根据该值进行IF函数数组中，如果为TRUE，则返回B2:B9中的值，如果为FALSE，则返回FALSE。 展开后的公式如下：
=(IF({真;真;真;假;真;假;真;假},{89;92;78;56;88;58;95;55},{假;假;假;假;假;假;假;假}))
请注意，原始公式末尾的 FALSE 被扩展为与前一个数组匹配的适当大小的数组。
IF函数测试完成后，得到如下中间结果：
=({89;92;78;假;88;假;95;假})
该函数忽略布尔值（TRUE 或 FALSE），仅对数值求平均值。
例2：计算前3名学生的分数总和
仍以图7所示的工作表为例，要求将前三名学生的成绩相加。
可以使用数组公式：
=SUM(大(B2:B9,行(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,行(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应用开辟了新天地。 然而，熟练使用数组公式需要大量深入的练习，但这绝对值得学习。
