Excel 进阶技巧：数组的运算和应用场景
在《又快又准地引用数据，Excel 进阶技巧 VLOOKUP | 使用详解》这篇文章中，介绍了各种 VLOOKUP 查询的使用技巧，尤其是最后的「配合 IF 实现换列查询」和「实现多个条件查询」这两个技巧，击中了非常大的痛点：
- 换列查询：在使用 VLOOKUP 时，由于这个函数的特性，是查找区域内的第一列值是否匹配，从而决定是否引用后几列的值。但实际情况中，可能要匹配的那个值在第二列或更后面的列，这时候想用 VLOOKUP，往往不得不自行剪切粘贴，调换列之间的顺序。但有些表格我们不能轻易地调整结构，这时候换列查询就派上用场了：在不改变表格列顺序的基础上，照样使用 VLOOKUP。
- 多条件查询：VLOOKUP 默认只能按一个条件查询。如果想要实现多条件查询，的确有不少方法。但这些方法大多需要用到 MATCH、CHOOSE、INDEX 等我们可能不那么熟悉的函数，而且公式理解起来比较复杂，不像 VLOOKUP 那么清晰易懂。有没有可能扩展 VLOOKUP 函数，使它能胜任多条件查询？其实稍加处理，也是可以做到的。
细心的你可能发现了，以上这两个技巧的公式中，不约而同地出现了一个元素：IF({1,0}, , )。受限于篇幅原因，沨沄极客老师当时没有具体展开介绍，许多人按着文章自行操作了一遍，但并不一定真的理解这个公式背后的涵义。
公式中出现了不常见的 IF({1,0}, , )
没错，我们在日常接触 Excel 的过程中，的确很少会看到公式中出现大括号 {}。它们究竟代表了什么，为什么配合 VLOOKUP 函数使用，能实现换列查询、多条件查询这样的进阶技巧呢？
其实，这些大括号在 Excel 中代表着数组。这篇文章，我们就来聊一聊 Excel 中的数组：它们是什么，有什么特点，以及在哪些场景下适合使用。本文中提到的一些案例和截图，可以下载这个对应的示例表格 📊 Excel 数组.xlsx，搭配本文一起阅读，效果更佳。
Excel 中的数组是什么？
如果抛开 Excel，单纯讲数组，许多人对这个概念也许并不陌生。在数学上，一个数字是数字，多个数字就是数组嘛。比如 1 是一个数字，{1,2,3,4……} 就组成了一个数组，不过这个数组相当简单，它只有一行，我们也称之为一维数组。如果你内心还留有对高等数学的阴影，那么想必二维数组也不陌生，它就是一个 x 行 y 列的数字矩阵。
一个二维数组
对应到 Excel 中，最直观的理解，一个单元格对应着一个数字，一片区域就对应着一个数组。例如，A1 单元格对应着 1 这个数字，A1:D1 区域对应着 {1,2,3,4} 这个一维数组，而 A1:D2 这个区域，则对应了第一排是「1,2,3,4」、第二排是「5,6,7,8」的二维数组。
一块区域就组成了一个数组
讲到这里，你可能都觉得稀松平常，没什么特别的。那么，接下来就可能是你不知道的了：一个单元格内，不仅可以有数字，还可以塞下一个数组，这是什么意思呢？
我们都知道，一个单元格，可以引用另一个单元格的数值。例如，在下图的 B5 单元格中输入 = A1，自然会显示结果 1，这个毫无问题。但是，如果我们在 B6 单元格中，输入 = A1:D2，也就是说，一个单元格，如果去引用一个区域，也就是引用一组数字，会怎么样呢？这时候，Excel 就给出了错误的提示，显示 #VALUE!。
在一个单元格中塞下一个数组
显然，一个单元格等于一块区域，这个在感觉上也是违反常理的，然而，如果你换一种输入方式，就会发现结果大不一样。例如，在 B7 单元格中，如果你的鼠标选中它，会发现这个单元格的公式是 {=A1:D2}，而对应的引用值就显示成了 1。
B7单元格的公式加上了大括号，就引用到了 1
更神奇的是，如果你尝试编辑 B7 中的公式，你会发现公式栏一旦进入编辑状态，大括号就自动不见了，直接显示成了 = A1:D2，和你尝试编辑 B6 单元格是一模一样的。
B6 和 B7 的公式栏，进入编辑态时，是一模一样的
而如果你自己尝试直接在单元格中输入一开始看到的 {=A1:D2}，你会发现 Excel 并没有显示出来 1，而是直接把这个单元格当成了文本处理。
手动输入 {=A1:D2} 会被 Excel 当作文本处理
看到这里，如果你之前没有接触过数组，肯定已经有点迷糊了，觉得这个 Excel 不是你曾经认识的 Excel 了。其实没那么复杂，我们一点一点厘清这个问题。
首先，数组有着自己特定的输入方式。在进行数组运算时，包在最外面的 {} 这对大括号，并不是靠你在公式栏手打出来的，而是通过使用快捷键 Ctrl+Shift+Enter（简称 CSE 组合键，在 macOS 中是 Control+Shift+Enter ），告诉 Excel 这是一个数组运算公式，Excel 会自动加上这对大括号的显示。也就是说，B6 和 B7 单元格，虽然你在编辑态时两者看上去一模一样，都是 = A1:D2。但在最后临门一脚上，B6 是直接敲 Enter 键表示公式输入完成的，而 B7 则是通过 CSE 组合键完成公式编辑的，此时在公式外面 Excel 会自动加上大括号，并且显示引用值 1。
这样，你就搞清楚了包在公式最外面的 {} 大括号的来源，是通过 CSE 快捷键生成的，而不是靠手工输入的。
其次，为什么 B7 显示 1，而 B6 则是 #VALUE! 呢？我们更进一步地来深入了解这个问题：
- B6 公式 = A1:D2 - 从本质上来说，一个单元格，的确是无法等于一个数组的，所以 Excel 报错了。
- B7 公式 {=A1:D2} - 通过在 B7 中以 CSE 快捷键的方式，相当于涵义变成了，针对 B7 这个单元格，去引用 A1:D2 这个区域的首行首列单元格。相当于是指定了 B7 单元格，对应到另一个区域数组中的某一个单元格。
为了更深刻地理解这一点，选中 B10:E11 这个区域后，然后在公式编辑栏中再次输入 = A1:D2，并以 CSE 快捷键结束编辑。这时候你会发现，B10:E11 显示的内容，和 A1:D2 区域完全一样。这是因为，数组公式运算的引用，让 B10:E11 这个区域的每个单元格，一一对应到了 A1:D2 这个区域的单元格。本质上，就相当于在 8 个单元格内，分别输入了 B10 = A1，C10 = B2，D10 = C3……
选中一片区域后，输入= A1:D2，并以 CSE 快捷键结束
刚刚提到的，其实都是直接引用了区域数组。在 Excel 中，也可以直接输入常量数组。例如，选中 B12:E12 区域，在公式编辑栏中输入 = {1,2,3,4}，并以 CSE 快捷键结束，这时候四个单元格内就填入了 1,2,3,4。如果你想填充一个 x 行 y 列的常量数组，只需要以 ; 来表示换行。例如，在 B13:E14 区域中，输入 = {1,2,3,4;5,6,7,8}，就会 1,2,3,4 成一行，5,6,7,8 成一行。总结一下，就是对于常量数组，用逗号来分隔行，用分号来分隔列。
在 Excel 中输入常量数组
数组的运算方式
在了解了数组的基本内涵和输入方式后，我们来进入下一个环节：数组的运算方式。其实，在刚刚上一节中，我们已经进行了 Excel 中最简单的一种运算：值引用。在一个单元格中，用 = 去等于另一个单元格，就是最简单的值引用运算。
那么，现在我们就引入更复杂的一些运算，例如基本的运算符号，以及 Excel 中的各种函数。
数组的区域运算
在我们的常规理解中，Excel 中的运算，是以单元格为单位的，在一个格子中输入公式，给出一个格子的运算结果。而对于数组来说，则可以进行区域运算，即针对一个区域，运用数组公式计算后，直接给出一个区域的结果。
为了理解这一点，让我们来看一个例子。例如，在计算不同产品的成交额时，我们会使用单价 * 销量。在传统的方式里，我们会在 D3 单元格中输入 = B3 * C3，然后下拉填充这个公式，使这一列的每个单元格，都是左边两个单元格的乘积。
传统方式：每个单元格有一个公式运算
这样做当然没有问题，那么，如果运用数组的方式来计算，是怎么样的呢？选中 I3:I7 区域，在公式编辑栏中输入 = G3:G7*H3:H7，然后以 CSE 快捷键结束。这时候，我们也得到了一样的结果。
数组的区域运算
那么，通过数组区域运算的方式，这样做有什么好处呢？在此我们先按下不表，我们快速地了解完数组运算的特性，在下一节应用场景中，我们具体来谈一谈这个问题。数组除了区域运算之外，还可以结合函数，进行单元格运算。
数组的单元格运算
除了数组的区域运算外，结合各种函数，还可以实现数组的单元格运算。例如，当我们想核算总成交额时，最常见的方法，就是在 D8 单元格中输入 = SUM(D3:D7)。你会发现，如果使用这种常规方法计算总成交额，必须依赖前一步计算得到的 D3:D7 区域的各产品的成交额。
传统方式计算总成交额
那么，如果运用数组计算的方式呢？只需要在 I8 单元格中，输入 = SUM(G3:G7*H3:H7)，并以 CSE 快捷键结果就行，它不依赖于 I3:I7 区域的计算结果，把这块区域删除了，也一样能得到结果。
使用数组进行单元格计算
为了更深刻的理解单元格运算的本质，我们可以在公式编辑栏中，使用 F9 查看引用区域的绝对值。实际上，公式 = SUM(G3:G7*H3:H7) 等价于公式 = SUM({125;658;223;99;341}*{12;3;5;23;19})。我们前面提到了，; 分号表示数组的换行，实际上相当于 Excel 先计算了 125*12, 658*3, 223*5, 99*23, 341*19 这五个中间值，然后通过 SUM 函数把这五个计算结果求和汇总，得到了最终的 13345 的结果。
数组单元格计算的实际过程
在对数组的区域运算和单元格运算有了一个基础的认识后，我们就来说说，为什么会存在 Excel 数组这种东西，它的应用场景到底是什么？我们刚才提到的许多案例，明明也有许多常规的方法可以实现计算，在哪些情况下，用数组会更合适？
数组的应用场景
使用数组的好处有许多，例如，在 Office 的官方文档中，就提到了文件大小的优势。尤其是对于数组区域运算来说，可以把多个单元格中单独存在的公式，变成一个整体的区域数组公式，当文件存在的计算行数较多时，使用数组运算的 Excel 文件大小会更占优势。
当然，如果说最核心的优势，在我看来主要有两点：其一，是由于数组公式的编辑操作特性，使得公式的一致性与可理解性得到了更强的保证。其二，是可以隐藏表格的复杂性，使用数组运算可以减少表格中的辅助列。
这么说来可能有些抽象，我们就以实际的案例，来谈一谈这两大优势的应用场景。
1. 公式的一致性与可理解性
通过前面的操作你可能也发现了，数组公式的编辑与操作，似乎没那么自由。你必须先选定一个区域，然后在公式编辑栏中输入数组公式，并且以 CSE 快捷键结束。同时，如果你想调整区域内的某一个单元格，或者增删行列，Excel 都会报错，告诉你不得单独编辑一个数组区域内的某个单元格。
这正是数组公式的特点，总结来说，数组公式有四大不能：
- 不能单独修改数组区域内的某一部分单元格公式
- 不能单独移动数组区域内的某一部分单元格
- 不能单独删除数组区域内的某一部分单元格
- 不能在数组区域内直接插入新单元格
也就是说，任何针对一个数组区域的编辑，都必须牵一发而动全身，要变全体一起变，不能搞区域内的特立独行。这样的特点，在平常看起来很恼人，但在特定的情境下却非常有用，例如制作预算表。
在许多公司内，往往会按年度或季度，下发要求各部门填写预算表。这样的表格，往往财务部门已经预先设定好了公式，只需要业务部门填入相关的预测部分。
然而，在实际操作中，业务部门改着改着，可能就不小心改到了财务部门定好的公式区域内，误删或错改了某些公式，导致财务部门最后汇总各业务部门的预算表时，徒增工作量。同时，对于业务部门来说，可能也想了解财务部门的核算逻辑与规则，但当预算表比较复杂时，很难清晰直接地理解各个区域之间的运算与钩稽关系。这时候，财务部门制作预算表时，如果能合理地运用数组公式，就能派上大用场了。
我们以一个非常简单的产品销量收入与利润预算表来说明举例。表格中黄色的部分，是下发到各业务部门需要填写的预测，而其它的总收入、总成本、利润等，财务已经预先设置好了公式，会自动计算。
黄色区域是业务部门填写的，其它区域不应该编辑
这时候，如果将总收入、总成本、利润这几行的公式，使用数组的区域运算公式，填表人如果一不小心改到了这几行的公式，Excel 会马上报错提示无法更改部分数组。
当对数组区域的部份内容修改时，Excel 会报错
同样的场景，你可以试想下，如果你发出去一个上千行的销量统计表格，比如类似于前文的销量*单价=成交额，如果成交额一列是使用传统的公式填写的，其中要是有一个单元格的公式被人为修改了，你怎么能上千行中找到那一个错误？而使用数组公式就没有这个烦恼，因为只要它们是一个区域，其计算公式的一致性就有保障。
此外，数组公式还提供了更好的可理解性。除了使用 B3:G3 这样的方式表示一个数组区域外，你还可以人为给其命名，例如 B3:G3 区域命名为「销量」，B4:G4 区域命名为「客单价」。只需要选中这个区域，然后在左上角的名称框中改名即可。
选中区域，在名称框中为其命名
一旦给区域完成命名，在数组公式引用时，你可以直接使用区域的名称，这样看到的公式就不再是枯燥无意义的字母，而是活生生的文字描述了。例如，在命名了第 3 行和第 4 行分别为「销量」和「客单价」后，第 5 行总收入的数组运算公式，就变成了 = 销量*客单价。这样一来，别人在阅读理解表格时，自然会更加清楚。
给区域数组命名为，公式更加直观好理解
2. 保持简洁，减少辅助列
数组的使用还可以使表格尽可能地保持简洁，减少辅助列的引入，不破坏表格的原有结构。像前文中举到的「总成交额」的例子，你不必先在表格中引入一列「成交额」，再做汇总求和计算，通过数组运算，可以直接一步到位得到总成交额。
此外，更鲜活的例子，也可以参考文初提到的，VLOOKUP 一文中提到的「配合 IF 实现换列查询」和「实现多个条件查询」两个技巧，都使用了 IF({1,0}, , )这个数组运算。现在，通过本文对数组的介绍，再来理解一下，这两个公式的背后，到底发生了什么吧。
IF({1,0},B:B,A:A)可以实现交换 A 列和 B 列的位置。我们重点关注下 E1 和 F1 两个单元格：
- E1 内的数组公式，实际上等价于 = IF(1,B1, A1)，由于 1 代表 True，取第一个值 B1，即姓名；
- F1 内的数组公式，实际上等价于 = IF(0,B1, A1)，由于 0 代表 False，取第二个值 A1，即工号；
以此类推，下面的每一行，实际上都是通过对 IF 内 0/1 的真假判断，来输出了一个交换了 A/B 列的新区域。
使用数组公式交换列
同样的道理，=IF({1,0},A:A&B:B,C:C) 是如何实现多条件查询的，也就很容易理解了。本质上，是通过数组公式，输出了一个新的区域，这个区域中，原先的工号和姓名两列被拼接在了一起。我们重点关注 E1 和 F1 两个单元格：
- E1 内的数组公式，实际上等价于 = IF(1,A1&B1, C1)，由于 1 代表 True，取第一个合并值 A1&B1，即「工号姓名」；
- F1 内的数组公式，实际上等价于 = IF(0,A1&B1, C1)，由于 0 代表 False，取第二个值 C1，即「工资金额」；
多条件查询依赖于合并列
通过上面这张截图，VLOOKUP 结合数组公式，之所以能够实现多条件查询，实际上是巧妙结合了 {1,0} 的数组，将原来一个三列的数组区域，重新输出成了两列数据。如果不使用数组公式，你就需要先在 Excel 表格中插入这样的辅助列，手工拼接上 A 和 B 列，破坏了原有的表结构。而通过数组公式，则可以把这些重新生成的区域巧妙地隐藏在数组公式内，避免了在 Excel 中增添辅助列，维持了表格的简洁。
以上，就是关于数组公式介绍、运算和应用场景。如果你还有更多关于数组运算的小技巧，欢迎在评论中和大家分享。
没有评论:
发表评论