无错杀蓝公式excel数组公式从入门到精通

2020年9月24日发(作者：谭绍隆)
数组公式从入门到精通

入门篇

本主题包含三部分：入门篇、提高篇、应用篇（分中级和高级）

对于刚接触Excel数组公式的人来说，总是会感觉到它的一份神秘。又Excel的Online Help 中只有很
少关于它的主题，所以这种神秘感就更强了。不要紧，只要跟着我的思路走，你很快就会看清数 组公式的
真面目！

数组概念
对于数组概念，大家都会很熟悉，其就 是一个具有维度的集合。比如：一维数组、二维数组、多维数
组。数组的表示一般为“{}”所包括（一 维和二维数组）。Excel中也不例外，如果你想直接表示一个数组，
也必须用“{}”括起来。

数组与数组公式
在Excel中，凡是以半角符号“=”开始的单元格内容都被E xcel认为是公式，其只能返回一个结果。
而数组公式可以返回一个或者是多个结果，而返回的结果又 可以是一维或二维的，换句话说，Excel中的
数组公式返回的是一个一维或二维的数组集合。
在Excel中需要按下 “Ctrl+Shift+Enter”组合键结束数组公式的输入。

为什么要用数组公式？
如果你的需要满足以下条件之一，那么采用数组公式技术可能会是你很好的选择方案。
你的运算结果会返回一个集合吗？
你是否希望用户不会有意或无意的破坏某一相关公式集合的完整性？
你的运算中是否存在着一些只有通过复杂的中间运算过程才会等到结果的运算？
看到这些另人费解的问题，你可能会摸不着头绪。不要紧，看了以下内容你也许就会明白了。

什么情况下会返回一个集合？
看一个简单的例子，选中C1:E3，输入“={
20 }”，按“Ctrl+Enter”组合键。

图1-1 (ArrayFormula_)
结果在C1:E3中看到的结果全是“Name”，而实际真正返回的结果应该是一个包含三行三列的二 维数
组，如何办？答案就是用数组公式。选中C1:E3，输入“={
”，按“Ctrl+Sh ift+Enter”组合键。

图1-2 (ArrayFormula_)
可能你又会问，这有何用？为何不在单元格中直接输入内容，反而要这么麻烦？
这仅仅是一个 例子，说明的是如何通过数组公式返回一个结果集。给你个问题，如果存在这样一个工
作表：包含字段{ ，如何将“Sex”为“Female”的记录抽取出来 (为了打印报
表，抽取的记录需要连续存放) ？这个问题将在“应用篇”里进行解答。

什么情况下会用到相关公式完整性？
什 么是相关公式完整性？这仅仅是我给出的一个定义，请再回到“图1-2”，请选择C1:E3中任意一单
元格，然后做随意的修改(哪怕和原先的公式一样)，按“Enter”键结束输入。结果如何？修改未成功！ 提
示“不能更改数组的某一部分”。

图1-3 (ArrayFormula_)
为什么会是这样呢？因为你正企图破坏相关公式的完整性。由于C1:E3中公式的数据源均为“{”，而C1:E3共用的一个公式(这与每个单元格都有相
同的公式是有区别的，因为这仅仅是C1 :E3拥有9个相同的公式，而不是一个!)，因此，当你要单独更改
其中一个单元格时，系统会认为你 正在更改部分单元格的数据源，如此会导致数据源不一致的现象，从而
导致与其它相关单元格脱离关系， 这样数组公式就失去作用，所以系统不又允许你更改数组公式的部分内
容。这样的好处是可以维护数据的 完整性，做到与数据源总是有一致的对应关系。

你的公式复杂吗？
如果有如下数 据，在D6单元格中求出对所购物品需要付多少费用。你会如何做？在D6中输入
“=(C2*D2+C 3*D3+C4*D4)”？结果正确，如果中间某个单元格地址输入错误你的结果会正确吗？如果记
录 不只3条，而是成千上万条，你是否会感觉到力不从心(如果不考虑单元格内字符数的限制)？如果用“图
1-5”中的方法，你的感觉又会如何？（在D6中输入“=SUM(C2:C4*D2:D4)”，按“Ct rl+Shift+Enter”键
结束输入。其中涉及到的技巧会在“提高篇”中讨论。）

图1-4 (ArrayFormula_)


图1-5 (ArrayFormula_)

怎么样？是否了解了数组公式？是否学会了如何使用数组公式？是否感觉到了它的一点点威力？
请继续关注“数组公式从入门到精通”之“提高篇”，让我们继续深入数组公式！
数组公式从入门到精通

提高篇

本主题包含三部分：入门篇、提高篇、应用篇（分中级和高级）


相信你在“入门 篇”中已经学会了如何建立数组公式，同时也大致了解在什么情况下适合使用数组公
式解决问题。需要说 明的是，在“入门篇”中提到的使用数组公式的三种情况并不是绝对的，要视具体情
况而定。
在接下来的讨论中，你将会了解数组公式的一些工作原理。
在进行正式讨论之前，先跟着我做一些准备工作。
Excel的主要功能就是数据的分析和处 理，我们现在只关心的是数据处理中的数据抽取。所谓数据抽
取就是对源数据按照一定的条件筛选后所得 到的结果。如何定制条件筛选呢？方法很多，这里介绍“IF()”
函数和模拟AND、OR的原理和用 法。

模拟AND、OR
让我们先来看看为什么要模拟AND、OR，而不用Excel的工作表函数AND()、OR()？
建立如下图的工作表，分别在D11、D12中输入“=SUM(IF(AND(C2:C7=D9,D 2:D7=D10),E2:E7))”、
“=SUM(IF((C2:C7=D9)*(D2:D7= D10),E2:E7))”，并分别按“Ctrl+Shift+Enter”结束公式输入。

图2-1 (ArrayFormula_)

之所以创建以上公式，是因为我想对满足“Product ID”为D9，“City”为D10的记 录进行汇总，很明
显，从上面的返回结果表明D11中的结果是正确的，而D10中的结果是错误的。为 什么会是这样呢？
在接下来的演示中通过讲述AND()和OR()函数的工作原理来解释为什么D1 0中的公式返回了错误的
结果，以及演示为什么D11中的公式可以神奇般的得到结果。
选中 在上面工作表的G2:G7，输入“=OR(C2:C7=D9,D2:D7=D10)”，按“Ctrl+Sh ift+Enter”；选中H2:H7,
输入“=AND(C2:C7=D9,D2:D7=D10) ”，按“Ctrl+Shift+Enter”。

图2-2 (ArrayFormula_)


图2-3 (ArrayFormula_)

怎么G2:G7都是TRUE；而H2:H7都是FALS E？实际我们想要的是“图2-3”中的结果。
为了节省篇幅，我直接把答案告诉你，G2:G7中的 公式相当于
“=OR(C2=D9,C3=D9,C4=D9,C5=D9,C6=D9,C7=D9, D2=D10,D3=D10,D4=D10,D5=D10,D6=D10,D7=D10”，
这回知 道原因了吧？“=OR(C2:C7=D9,D2:D7=D10)”返回的结果只有一个，而不是七个！同理， AND()
函数类似。不信，你可以更改数据表中的一些数据来进行验证。
现在你该知道D1 0返回错误值的原因了吧？那为什么D11能够返回正确的结果？这正是我们要解决
AND()和OR( )函数在数组公式中存在问题的出发点。先看看下面这个说法：“*”相当于AND，“+”相当
于OR 。这是一些论坛中常见的回答，我到如今为止也这样解答了不少朋友的疑问。结论正确么？难道Excel
中的“*”和“+”有两层含义？――严格的说，这是不正确的！因此，我已经误导了很多朋友，如果你曾经在某论坛中得到过我这样的解答，我在这里说声抱歉！为什么“*”和“+”可以模拟AND和OR呢？< br>就像“图2-1”中D12的公式“=SUM(IF((C2:C7=D9)*(D2:D7=D10), E2:E7))”。
要了解其原理，就要揭开FALSE和TRUE的面纱。在一新工作表的C2中输 入“=TRUE+0”，按回车
键；在D2中输入“=FALSE+0”，按回车键。

图2-4 (ArrayFormula_)

“图2-4”中的结果说明：将TRUE和FALSE转换为整型后的值分别为1和0。
建立 如下图中的工作表，选中D2:E3，输入“=D$$1*$$C2”，按“Ctrl+Enter”；同样选中D6 :E7，输入
“=D$$5+$$C6”，按“Ctrl+Enter”。

图2-5 (ArrayFormula_)

从上图中很容易看出，对于“乘”操作，只有TRUE*T RUE才会返回1（TRUE），因此“*”模拟了
AND的效果；对于“加”操作，只有FALSE+ FALSE才会返回0（FALSE），因此“+”模拟了OR的效果。
技术说明：
1) Excel中的IF()工作表函数对条件真假的判断是这样，当条件的值为0时，认为是假；否则，全部认为是 真。
条件的数据类型一定是数值。比如“=IF(-3,1,0)”返回1。因此“+”的操作做到了模 拟OR的效果。

理解IF()
IF()还用理解？Excel Online Help中不是已经表达的很清楚了吗？也许你会这样问。
我并非是想文字充数，请看下图：

图2-6 (ArrayFormula_)

C5中的公式为“=IF( C2:C3=”（为数组公式），你知道它的值为什么是FALSE而
不是三么？
聪明的你可 能已经想到这种类型的数组公式返回的是一个结果集，这个结果集的大小与操作对象的大
小是一致的，在 这里操作对象为C2:C3和D2:D3，因此返回值为两个元素。
就是这样，由于C2=”John ”，不满足条件，因此应该返回IF()函数的第三个参数值，但这里无第三个
参数，所以系统返回FA LSE；由于C3=”Mary”，满足条件，因此返回第二个参数值，即ROW(D2:D3)，而
C 3对应的是D3，所以返回值应该为3。为了验证结果，请选择C5:C6，输入
“=IF(C2:C3 =”，按“Ctrl+Shift+Enter”。结果如何？

图2-7 (ArrayFormula_)


聪明的Excel
先看看这个，知道 “=MIN(FALSE,3)”的返回值么？结果返回0，从上面论述的知识不难理解，因为
FALS E转换为整型的值为0。我们已经知道“图2-7”中“=IF(C2:C3=”的结果集为
“{FAL SE,3}”，那么，请选择“图2-7”中的D5，输入“=MIN(IF(C2:C3=”，按
“C trl+Shift+Enter”，看结果。

图2-8 (ArrayFormula_)

结果竟然是3，而不是0！这就是Excel聪明之处！为 什么说聪明呢？因为在绝大部分情况下我们想要
的结果是满足条件的部分，而舍弃非满足条件的部分。这 对筛选数据非常有帮助！如果你坚持要将非满足
条件的部分包含进来，最简单的方法可以将公式变形为“ =MIN(IF(C2:C3=”，简简
单单的一个逗号“,”，结果却截然不同。对于如何对筛选有帮 助，将在“应用篇”中给予实例解答。

模拟IF()
再来看看“图2-1”中D 12的公式“=SUM(IF((C2:C7=D9)*(D2:D7=D10),E2:E7))”，让我们换 种形式。
在E12中输入“”，按“Ctrl+Shift+Enter”。

图2-9 (ArrayFormula_)

结果也是30！所以“*”可以模拟I F()！由于我们已经揭开了TRUE和FALSE的面纱，因此不难理解，
对于“*”操作，只有TR UE*TRUE才会返回1，所以结果相当于“=SUM(0*12.34, 0*13.34, 1*30, 0*29,
0*103.05, 0*113.05)”，当然结果为30了。
注意：并非所有情况下“*”与IF()效果都相同，要视具体情况而定，这就需要你灵活掌握了。

引用大小制约
此主题并非重要，不过为了使你更加深入数组公式，还是在这里介绍一下。
这里的引用大小制约指的是数组公式中各相关引用之间的大小制约或引用大小对结果集大小的制约。
1) 主关键区域决定数组函数返回值的大小（关键区域是指决定数组公式返回结果集大小的区域）

看“图2-7”中的公式“=IF(C2:C3=”，这里的主关键区域为C2:C3，那
么该公式的结 果集大小为2（即有两个元素）。
2) 有互依赖关系的引用之间大小要一致
互依赖就是共同决定某个结果。
看“图2-9”的D12“=SUM(IF((C2:C7= D9)*(D2:D7=D10),E2:E7))”，其中C2:C7与D2:D7就
是互依赖的引用 ，它们共同决定IF()函数第一个参数的值，所以它们的大小必须一致，否则返回错
误值。
对于没有互依赖关系的引用大小有无限制？这就需要视具体情况而定了，再看“图2-7”中的
公式“= IF(C2:C3=”，其中的两个引用之间就没有大小的限制；而公式
“=MIN(IF(C2:C3 =”就不一样了，其中第一个引用的大小必须大于第二个引
用的大小。

到现在为止 ，你可以说已经掌握了Excel中的数组公式的工作原理以及一些中级技巧。学到现在你已
经足以可以 应付一些工作中的需要了，当然前提是你真正已经掌握了这些技术。

不知道你现在的感觉如 何？可能你还有一头雾水的感觉，这是正常的，Excel中的数组公式确实不是
很好理解。不要紧！只 要多做练习，将实践与理论结合，很快你就会体会到它的强大威力！

为了使你的工作效率更 高，我又写了“应用篇”，内容主要是平时能够遇到问题的一些解决方案（包
括自己心得和网友一些问题 的解答）。

因此请您继续关注“数组公式从入门到精通”之“应用篇”，让我们展示数组公式的威力！

Excel数组公式从入门到精通之精通篇
上次写了篇《Excel数组 公式从入门到精通之入门篇》，不觉已十多天过去了，今天补上“精通篇”。
当然说“精通”可能有点过 了，但是希望大家通过这两篇博文能够真正认识“数组公式”，并且在工作中使用
数组公式帮我们解决实 际问题。
Excel数组公式从入门到精通之精通篇
一、课程回忆
什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里 重点提醒
的一点就是，如果要使用数组公式，在编辑栏输入完公式以后一定要按下“Ctrl+Shif t+Enter”组合键，使
编辑栏的公式处在“{}”之中。
二、数组公式继续深入
印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例 子真是太实用了。具体细节记不太
清楚了，大致意思就是使用函数计算1到100的和。这里同样以此为 例。
1.求1到100的和
在往下看之前，大家想一下 ，如果让你来处理该如何来处理呢？只用一个函数解决1到100的和，
当然也可以是1000、100 00甚至更多。讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进
一步的了解数组公式的 用法。
解答：{=SUM(ROW(1:100))}
问题分析：求1到100的和，答案是5050（小学生都知道^-^），但Excel必须是你告诉了它正确< br>的方法，它才能知道。计算从1到100的和，实际上就是计算1+2+3+4+……+98+99+10 0，好了，答案出
来了，在编辑栏中输入“＝sum(1+2+3+4+……+98+99+100)” 。相信聪明的一定对此答案不满意，虽然能得
到正确的结果，但很明显是“错误”的方法。
要得到1到100的正确数列，最简单的方法就是使用Row()或是Column()函数，由于个人习惯，< br>我比较习惯于Row()，所以这里以Row()函数为例。
熟悉Row()函 数：在A1单元格中输入“＝Row()”，使用填充柄填充至A5，看到什么结果？是不
是每一个单元 格中值就是其对应的行数。


惊喜：Row()表示单前行，如 果使用Row(1:100)就表示一个数组，其中包含的便是第一到第一百
行的行号，即1、2、3、 ……、98、99、100这些数值，现在我们就把这个数组应用到公式中。
在工作 表的任意一个单元格中输入“=sum(Row(1:100))”，然后按Ctrl+Shift+Enter 组合键，你会惊
喜的发现，我们要的结果出现了。

2.在Excel2003中享受“SUMIFS”
SUMIF函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Office 2007
开始，引入了SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Office 2003怎么办呢？其
实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。
如下图所示，某教师有一张任教的几个班级的学生成绩表。

任务：统计出“一班”、“二班”共计多少人？
此题要如何解决？SUMIF用两次 ？或是COUNT用两次？这里还是演示数组公式的用法，所以
先用SUM和IF组合的形式。
在任一单元格中输入“=SUM(IF((A2:A12=一班二班公式按下回车键， 是不
是发现结果是“#VALUE！”，再次进入编辑栏然后按下“Ctrl+Shift+Enter ”快捷键，是不是发现正确的结果出
来了？

这里再次解释一下这 个公式“{=SUM(IF((A2:A12=一班二班，外侧SUM没
什么好用的了，就是求（）内各 数的和。中间的“IF((A2:A12=一班二班的运算过程是
这样的，判断A2:A12区域内单元 格的值是否是“一班”，如果是则结果为1，则此公式计算的结果依次是“1、
0、0、1、0、0、1 、0、0、1、0”，因为第一个条件为真，第二个条件肯定就不为真了，因为一个单元格
不可能同时等 于“一班”和“二班”，所以第一个数组就是“1、0、0、1、0、0、1、0、0、1、0”。这时再判断< br>A2:A12区域内单元格的值是否是“二班”，如果是结果则为1，否则为0，所以这个数组条件计算的 结果就
是“0、1、0、0、0、1、0、1、0、0、0”，中间的加号就是将这两个数组相加，也就 是说最终的数组为“1、
1、0、1、0、1、1、1、0、1、0”，然后使用SUM求和，结果就为 7了。

从上面的图中标注可以看出，所以的公式我全部使用了数组（A2 :A12这就是一个数组），并且上
图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替 代，之所以全部列出，还是希望大家
更好的理解一下数组。
在Excel中 ，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格
所对应的一个值（数 组中的一个元素），放在数组公式中使用时，通常整个数组元素都会参与运算。
三、数组公式精通
这里是一个实际工作中的例子，只是我稍微变化了一下，还是SUM应用的例子。
需 求：如下图所示，现在要统计员工张三在1号加工所有机器的“实绩”，也就是说在右侧的数据
中先过滤 日期为1，然后再过滤人员为张三的数据，最后统计实绩的结果。如果使用一个公式完成这一需
求，你能 想到吗？当然SUMIFS是除外的，因为SUMIFS是Office 2007以后的产物。

答案：在上图所示的C2单元格中输入“=SUM((E2:E21=A2)*(G2 :G21=B2)*(H2:H21))”公式，然后
按下“Ctrl+Shift+Enter”组合 键，你会发现想要的结果已经出现了。数组公式就是这么简单，解决问题也是
这么简单。
这次用的公式可以看出，比之前用的公式还要简单，连IF都不要了，实际上这里的“＝”符号就
是起到 了一个类似IF的效果。
这里再说明一下公式的执行过程，公式中E2:E21表示 数组区域，这个相信已经不需要再说明了，
放到数组公式中就是依次取数组中的各个数值，也就是依次取 日期中的值。E2:E21=A2，实际上就是拿日
期中的每一个值依次与A2中的日期进行比对，如果 相等则结果为True，即1，如果不相等则为False即为
0。到了这里也许你有一点明白了，如果 第一不相等，则后面的无需再继续下去了，因为公式里用的全部
是“*”乘积符号，任何数乘0等于0。 如果此项符合再继续判断G2:G21区域，也就是用姓名依次比对，如
果和B2中的姓名相同，则为T ure，即1，如果为False，即0，继续下一个回合。如果此项也为Ture，很
明显前面两项的 结果为1*1＝1，再乘以H2:H21数组中对应的数字，即符合条件的“实绩”，以第一个符合
条件 的第一条记录为例，在数组公式运行的第一个回合为SUM(1*1*234)，结果当然为234了，然后再依
次完成整个数组的运算，我们最终的目的就达到了。

数组公式 非常有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。但大家记住数
组中的数据是一一 对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。
希望大家能够慢慢的理解、贯通。