Excel数组公式从入门到实战：批量计算思维一次讲清

说实话，Excel 里最劝退人的一个词，就是“数组”。

很多人在 Excel 函数公式里一看到{}这个花括号，或者一搜教程出现“数组公式”“按 Ctrl+Shift+Enter”，第一反应就是：太麻烦了，不想学。

但如果你真的跨过这道坎，会发现数组几乎是最能批量提效的工具。它不玄乎，也不是编程高手的专利，它就是一种“批处理”的思维。你可以不学复杂宏、不用写 VBA，只要掌握数组的基本用法，就能让很多原本需要辅助列才能解决的问题，变成一个公式搞定。

这篇文章不讲理论书上的抽象定义，直接从你日常会遇到的表格场景出发，拆开三个关键点：数组是什么、数组公式怎么输入、数组怎么写进函数。

3 分钟不敢说让你成为高手，但一定能把“数组”这两个字从你的恐惧清单里划掉。

1. 这篇文章真正要解决的问题

先说痛点。你在工作中一定遇到过下面这些情况：

• 要求“某个月的销售总额”，但日期列是“2024/1/5”这种完整日期，不能直接用 SUMIF 匹配“1月”；
• 要求“部门 A 且职级是经理”的人数，SUMIF 只能单条件，COUNTIF 也搞不定多条件；
• 要求“产品价格为 10 到 20 元之间”的订单总金额，SUMIFS 倒是可以，但你的 Excel 版本比较老，SUMIFS 也能用，可你想顺便把条件区域里某列数据做一次加减运算后再求和；
• 求“A 列里包含某个关键字的一共几条”，通配符能解决一部分，但关键字不止一个，而且分散在多个条件里；
• 老板扔一张大表，里面有 2000 行数据，你不想拖辅助列，不想分 5 步操作，想一个单元格直接出结果。

这些问题靠基础函数不是不能做，而是要加辅助列、分步骤、容易出错。而数组公式最直接的价值就是：在同一套数据上完成“先计算、再判断、再汇总”的多步操作，只需要一个单元格。

如果你有以下任一情况，这篇内容值得读完：

• 知道 VLOOKUP、SUMIF、COUNTIF 这些基础函数，但看到数组就发怵；
• 已经在用 SUMIFS、INDEX+MATCH，但一直没搞懂它们和数组有什么关系；
• 想要形成“先做中间计算，再汇总”的思维，不局限于背函数。

我的判断是：数组不是高阶技巧，而是批量思维的基础。你可以一辈子不写复杂的数组公式，但至少要看得懂、能掌控 2 到 3 个最常用的数组套路。

2. 基础概念与核心原理

2.1 什么是 Excel 数组

从最朴素的角度说，数组就是一组数据的有序集合。

在 Excel 里，它不一定是代码里的int[]。你可以这样理解：

• 普通单元格里的内容，是一个“单值”，比如数字100、文本"销售部"；
• 一组连续排列的数据，比如A1:A10这 10 个单元格，就是一组数组；
• 一组手动输入的常量，比如{1,2,3,4,5}，也是数组；
• 一组函数计算返回的结果，比如用ROW(1:10)生成的{1;2;3;4;5;6;7;8;9;10}，也是数组。

关键点在于：Excel 里当你选中一个区域，或者在一个函数里引用了一个区域时，计算机内部其实就是按“数组”来处理这批数据的。

2.2 一维数组和二维数组

数组也分维度，这个听起来吓人，实际很好懂。

一维数组

一列多行或一行多列数据。比如A1:A5就是 5 行 1 列的一维数组。在数组常量里用逗号分隔同一行的数据，用分号分隔不同行的数据。

例如{1,2,3}表示一行三列，而{1;2;3}表示三行一列。

二维数组

多行多列数据。比如A1:C5就是 5 行 3 列的二维数组。

在 Excel 的日常操作中，大多数表格数据本身就是二维数组。你在函数里引用 $A$2:$D$100 这样的区域时，内部处理的往往就是二维数组。

2.3 数组公式和普通公式的区别

普通公式处理的是一个或多个单值，返回一个结果。

数组公式可以同时处理一组或多组数据，返回一个结果，也可以返回多个结果。

举一个最经典的例子。

假设 A 列是商品名称，B 列是销量。现在要求“商品名称为‘鼠标’的总销量”。

普通做法：

=SUMIF(A2:A100,"鼠标",B2:B100)

这是基础函数，没问题。

但如果你想稍微变通一点，比如“销量大于 50 的鼠标的销量总和”，你可以写成：

=SUM((A2:A100="鼠标")*(B2:B100>50)*B2:B100)

这就是一个数组公式。整个公式的含义是：

1. 判断 A2:A100 是否等于“鼠标”，返回一组逻辑值；
2. 判断 B2:B100 是否大于 50，返回一组逻辑值；
3. 两个逻辑值相乘，相当于“且”的关系；
4. 再乘对应行的销量；
5. 最后 SUM 求和。

如果你用的是 Excel 365 或 Excel 2021，直接按回车就行。如果用旧版 Excel，输入完需要按Ctrl+Shift+Enter。

这才是数组公式的威力：你不是在描述一个单元格怎么算，而是在描述一整列数据怎么批量算。

2.4 数组常量：手动写的花括号数据

在公式里，你可以直接手写一组数据，这叫数组常量。例如：

=SUM({1,2,3,4,5})

返回结果是 15。这组{1,2,3,4,5}就是数组常量。

需要注意：数组常量里不能包含单元格引用、公式、函数，只能是数字、文本、逻辑值。文本还需要用英文引号括起来，比如{"A","B","C"}。

2.5 动态数组：新版 Excel 的“自然溢出”

从 Excel 365 开始，微软引入了动态数组引擎。当你写一个公式，计算结果是一组值时，它会自动把结果“溢出”到相邻的单元格。

比如你在 A1 输入：

=ROW(1:5)

在新版 Excel 中不需要按任何特殊键，A1 会得到 1，A2 到 A5 会自动得到 2、3、4、5，同时 A1 周围出现蓝色边框，表示这是动态数组溢出区域。

这对于写数组公式来说是一个重大变化。新版 Excel 中，大多数数组公式不再需要 Ctrl+Shift+Enter，可以直接回车。

旧版 Excel（2019 及更早版本）里，你必须按Ctrl+Shift+Enter，公式会自动加上花括号{}。

3. 环境准备与前置条件

在动手写数组公式前，先确认一下你的 Excel 环境。

3.1 Excel 版本影响操作方式

如果你用的是企业批量安装的 Office 2016，请务必记住：数组公式要按 Ctrl+Shift+Enter，否则结果会错误，或者只返回第一个值。

但如果你用的是 Microsoft 365 家庭版，体验会好很多，直接回车就行，动态数组也支持。为了方便，本文示例会同时标注两种情况的输入方式。

3.2 准备示例数据

为方便验证，建议新建一个 Excel 文件，Sheet1 中准备如下测试数据：

如果不想手工输入，可以直接把下面这段粘贴到 A1：

商品 品类 销量 单价 鼠标 外设 80 39 键盘 外设 45 99 显示器 显示设备 12 899 U盘 存储 150 59 手机支架 配件 200 19 显示器 显示设备 8 1099

3.3 快捷键准备

• Ctrl+Shift+Enter：旧版 Excel 数组公式确认键；
• F9：在公式编辑栏选中某段表达式后按 F9，可以查看这段计算出来的数组内容；
• Esc：按 F9 之后退出预览状态。

这三个键是数组调试阶段最重要的工具。

4. 核心流程拆解：从普通公式到数组公式

很多新手并不是不会写公式，而是不知道“什么时候该用数组、写出来之后怎么验证”。这一节用拆解的方式，把数组公式的操作路径讲清楚。

4.1 第一步：先用普通公式实现单条件

很多人面对一个多条件问题，第一反应是“函数不够用”。其实并非如此，有些问题先尝试用普通函数解决，至少你会清楚函数本身的行为。

以“计算‘外设’品类总销量”为例。

先用 SUMIF：

=SUMIF(B2:B100,"外设",C2:C100)

这个公式很好理解：在 B 列找“外设”，对应 C 列相加。

如果你想要的结果，是把每一行“是否外设”的判断结果先展示出来，再看总和，可以用辅助列。

在 E2 输入：

=IF(B2="外设",C2,0)

然后向下填充到 E7，最后对 E 列求和。

这就是“中间计算+汇总”的思维。但数组公式更高效的点在于：这个 IF 判断不必放在单元格里，而是直接写在内存中运算。

4.2 第二步：把辅助列运算“装进”内存

同样的需求，用数组公式可以写成：

=SUM(IF(B2:B7="外设",C2:C7,0))

在旧版 Excel 中，输入完按Ctrl+Shift+Enter，公式会自动变成：

{=SUM(IF(B2:B7="外设",C2:C7,0))}

这个公式是怎么运算的？

1. B2:B7="外设"会生成一组逻辑值：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}；
2. IF 对每个逻辑值判断，TRUE 则取 C 列对应值，FALSE 则取 0，生成一组数组：{80;0;12;0;0;0}；
3. SUM 对这组数组求和，得到 92。

整个过程相当于：中间生成了一些“看不见的辅助列”，但运算完就丢弃，不占单元格空间。

4.3 第三步：用乘号代替 IF 实现多条件

IF 写法比较直观，但多条件时会变得冗长。更常见的是用乘号连接多个条件。

“外设品类且销量大于 50 的总销量”：

=SUM((B2:B7="外设")*(C2:C7>50)*C2:C7)

这里的逻辑是：

1. (B2:B7="外设")生成{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}；
2. (C2:C7>50)生成{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}；
3. 两组逻辑值相乘，TRUE 相当于 1，FALSE 相当于 0，所以只有在两个条件都为 TRUE 时，乘积才为 1；
4. 再乘 C 列销量，其他行都变成 0；
5. SUM 求和。

这种写法的核心好处是：多个条件之间用乘号连接，就是“并且”关系；用加号连接，就是“或者”关系。逻辑清晰，不受函数限制。

4.4 第四步：验证数组公式是否正确的通用方法

写数组公式最容易遇到的情况是：公式返回结果不对，或者只返回一个值，但你又不知道哪里错了。

此时最简单的方法是使用 F9 逐步调试。

在公式编辑栏中，用鼠标选中(B2:B7="外设")这段，按 F9，Excel 会显示它计算出来的数组内容：

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

按 Esc 退出预览，再选中另一个条件段继续按 F9 查看，一步步检查每个中间结果是否符合预期。

这个方法几乎可以排查 80% 的数组公式写作错误。

5. 完整示例与代码实现

下面提供 4 个可直接复制的实战示例，分别覆盖求和、计数、查找和动态数组场景。每个示例都会标注输入方式，建议先复制到示例表格里试一次，再改造成自己的数据。

5.1 示例一：多条件求和

需求：计算“品类为外设”且“销量大于等于 50”的总销量。

方法一，使用 SUMIFS（不涉及数组思维，但作为对照组）：

=SUMIFS(C2:C7,B2:B7,"外设",C2:C7,">=50")

方法二，使用数组公式：

=SUM((B2:B7="外设")*(C2:C7>=50)*C2:C7)

方法三，使用 SUMPRODUCT（不需要按三键，内部就是数组运算）：

=SUMPRODUCT((B2:B7="外设")*(C2:C7>=50)*C2:C7)

解释：

• 方法二与方法三本质相同，区别只在于输入方式。
• 新版 Excel 和 WPS 较新版本可以直接回车。
• 旧版 Excel 下方法二需要按 Ctrl+Shift+Enter，方法三不需要。

运行结果预期：外设品类中，鼠标销量 80 符合条件，键盘 45 不符合（小于 50），所以结果为 80。

5.2 示例二：多条件计数

需求：统计“品类为外设”或“销量大于等于 100”的商品个数。注意这里是“或”的关系。

数组公式写法：

=SUM(((B2:B7="外设")+(C2:C7>=100)>0)*1)

拆解：

• (B2:B7="外设")生成{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}；
• (C2:C7>=100)生成{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}；
• 两者相加，得到{1;0;1;1;1;0}；
• >0的作用是避免出现“两个条件都满足时等于 2”的情况；
• 最后乘 1，将逻辑值转为数字，SUM 求和。

结果预期：鼠标（外设）、显示器（显示设备销量 12）、U盘（销量 150）、手机支架（销量 200），共 4 个。

5.3 示例三：数组公式用于查找

需求：根据商品名称，同时返回销量和单价。用 VLOOKUP 需要写两次，用数组公式可以一次搞定。

选中 E2:F2 两个单元格，输入：

=VLOOKUP("显示器",A2:D7,{3,4},0)

在新版 Excel 中，直接回车，E2 和 F2 会自动分别显示销量和单价。

在旧版 Excel 中，需要先同时选中 E2:F2，输入公式后按 Ctrl+Shift+Enter。

这里{3,4}是一个数组常量，告诉 VLOOKUP：同时返回第 3 列和第 4 列。

如果你不想用 VLOOKUP，也可以用 INDEX+MATCH 组合实现双列返回：

=INDEX(C2:D7,MATCH("显示器",A2:A7,0),{1,2})

这个公式的含义是：在 C 列到 D 列这个二维区域中，找到“显示器”所在行，返回第 1 列和第 2 列。

5.4 示例四：动态数组的自动溢出

在 Excel 365 或 Excel 2021 中，验证动态数组最简单的方法是使用 UNIQUE 函数。

需求：提取 B 列中所有不重复的品类。

在 E2 输入：

=UNIQUE(B2:B7)

直接回车，Excel 会自动在 E2、E3、E4 返回“外设”“显示设备”“存储”“配件”四个结果。

在传统思维里，要去重必须用“高级筛选”或者写复杂的 INDEX+MATCH 数组公式。而动态数组改变了交互模式：公式计算出来的结果是一组数据，它就自然占据一组单元格，不需要预先选择输出区域。

如果版本不支持 UNIQUE，也可以用传统的数组去重公式，但复杂度较高，这里不做展开。

6. 运行结果与效果验证

公式写完不是终点，你需要验证结果是否正确。尤其是数组公式，出错后的表现和普通公式不一样。

6.1 如何判断数组公式是否生效

在旧版 Excel 中，判断标准是：公式编辑栏里是否出现了花括号{}。但要注意，这个花括号不是你自己输入的，而是按 Ctrl+Shift+Enter 后系统自动加的。

在新版 Excel 中，如果一个数组公式返回多值，单元格会显示蓝色边框，并且在公式编辑栏中可以看到@符号和溢出区域提示。

6.2 单值返回和区域返回的区别

• 如果返回单值，比如SUM(IF(...))，结果会正常显示在一个单元格里；
• 如果返回多值，比如VLOOKUP同时返回两列，你必须先在输出区域选中对应范围，否则可能会报#VALUE!错误，或者只返回第一个结果。

6.3 常见验证方法

1. 先算一遍手动结果；
2. 用 F9 逐步检查中间数组；
3. 用 SUMIFS / COUNTIFS 等普通函数交叉验证；
4. 如果公式结果错误，优先检查条件区域和数据区域是否行数一致。

例如，公式中引用B2:B7，但另一个条件引用C2:C100，区域长度不一致时，容易产生#VALUE!错误。这是数组公式最常见的低级错误之一。

6.4 新旧版本结果一致性验证

同一套逻辑，旧版数组公式和新版动态数组在结果上应该一致。如果新版直接回车结果正常，但旧版按 Ctrl+Shift+Enter 后结果不同，大概率是“隐式交集”的问题。

比如旧版中：

=SUM((A1:A10>10)*(B1:B10))

如果 A1:A10 和 B1:B10 的形状不一致，旧版会按交集处理，导致结果不对。解决办法是：确保所有区域的行数、列数完全一致。

7. 常见问题与排查思路

数组公式看似简单，但在实际操作中会遇到各种奇怪现象。下面整理最常见的 6 类问题，每一条都有具体排查方向。

7.1 细节问题一：什么时候必须用 Ctrl+Shift+Enter

简单判断方法：

• 如果你用的是 Excel 365 或 Excel 2021，大多数情况下直接回车即可；
• 如果你用的是 Excel 2019 或更早版本，凡是公式中使用区域与区域之间的运算（如(A1:A10="是")*B1:B10），都需要按 Ctrl+Shift+Enter。

还有一种情况容易被忽略：公式中使用了数组常量{...}，但普通函数如 SUMPRODUCT 或 LOOKUP 内部已经支持数组运算，这时不需要三键。

7.2 细节问题二：为什么公式被系统自动加了“@”符号

这是新版 Excel 的“隐式交集”机制。

当公式返回一个数组，但只有一个单元格可以显示时，新版 Excel 会自动在函数前加@，表示“只取交集结果”。例如：

=@SUM((A1:A10)*(B1:B10))

出现这种情况，通常是因为你选中了一个单元格，而非多个单元格区域。如果你希望在旧版默认行为中看到数组公式效果，要么使用支持数组的函数（如 SUMPRODUCT），要么将公式输入到足够大的区域中。

7.3 细节问题三：数组公式导致文件计算缓慢

如果你在一张 10 万行的表格里使用数组公式，每次修改任意数据都会重新计算一整个数组，速度必然会慢。

排查方法：查看状态栏是否显示“计算中”，在“公式”选项卡中把计算方式改为“手动计算”，排查是否是大范围数组公式导致。

解决方案：

1. 优先使用 SUMIFS / COUNTIFS / MAXIFS 等原生聚合函数；
2. 尽量避免对整列引用，缩小数据范围；
3. 使用 Excel 表格对象（Ctrl+T 插入表格）配合结构化引用；
4. 如果逻辑复杂，考虑用辅助列分段计算，而不是一段超长数组公式。

8. 最佳实践与工程建议

数组公式不是越复杂越好。在实际工作中，需要根据场景选合适的方案。

8.1 能用原生函数就别硬写数组公式

SUMIFS、COUNTIFS、AVERAGEIFS、MAXIFS、MINIFS 这些函数本身针对多条件场景做了优化，性能远超“数组相乘再 SUM”的写法。假设你只是做“多条件求和”，优先使用 SUMIFS，它是普通函数，不需要三键，计算速度更快，团队协作时别人也更容易读懂。

只有当条件区域需要做运算时，才考虑数组公式。比如“对 A 列月份字符串取前 3 位后等于‘1月’”这类需求，SUMIFS 无法处理，数组公式才派上用场。

8.2 用 Excel 表格结构化引用降低维护成本

把数据区域转换为 Excel 表格（快捷键 Ctrl+T），然后使用结构化引用，数组公式的可读性会大幅提升。

例如，原公式：

=SUM((B2:B100="外设")*C2:C100)

如果 A1 已经是一个命名为“销售表”的表格，公式可以写成：

=SUM((销售表[品类]="外设")*销售表[销量])

这样新增数据行时，表格范围自动扩展，公式无需手动修改引用区域。这个习惯特别适合在团队协作中推广。表格结构在后续使用 VLOOKUP、数据透视表时也非常友好。

8.3 复杂数组逻辑用 LET 函数命名中间变量

Excel 365 用户可以使用 LET 函数给中间计算结果命名，既提高可读性，又避免重复计算。

以下公式计算“外设品类中销量高于平均销量”的商品销量总和：

=LET( 品类列, 销售表[品类], 销量列, 销售表[销量], 平均销量, AVERAGE(销量列), SUM((品类列="外设")*(销量列>平均销量)*销量列) )

这里先定义“品类列”“销量列”“平均销量”三个名称，再写最终计算逻辑。虽然还是一个数组公式，但逻辑一目了然，也方便后续修改条件。

8.4 不要在公式里写成“一行宇宙”

很多新手喜欢把所有条件拼在一个公式里，看起来非常“厉害”，但维护起来很难受。一个 300 字符的数组公式，三个月后你自己都看不懂。

建议做法：

1. 一个公式只负责一类计算；
2. 多条件存储区域用命名区域或表格引用；
3. 中间步骤较多时，先在辅助列验证每一步结果，确认无误后再合并；
4. 在公式所在单元格添加批注，说明数据来源、条件含义；
5. 如果条件会变动，把条件放在单元格里，用公式引用单元格，而不是写入文本常量。

8.5 兼容性策略：给团队用，别只给自己用

如果你的 Excel 表格需要发给同事，而他们用的是旧版 Excel，建议遵循以下规则：

• 数组公式尽量少用，优先 SUMPRODUCT，因为它不需要三键；
• 如果必须使用返回多值的动态数组函数（如 UNIQUE、FILTER、SORT），先确认对方是否使用 Office 365 或 Excel 2021；
• 如果在 WPS 中运行，请注意 WPS 的动态数组支持情况与 Excel 365 不完全一致，建议测试后再交付；
• 添加一个“版本要求”说明页，或者在表格底部写清楚哪些公式需要 Excel 365 支持。

8.6 安全与权限提醒

如果公式涉及引用其他 Excel 文件的数据，或者公式计算结果会被其他系统读取，建议先确认数据权限和文件路径稳定性：

• 不要直接引用带空格的临时路径文件；
• 不要用数组公式跨表读取大型生产数据文件，可能导致打开缓慢；
• 如果数据来自自动化导出，建议先导入本工作簿，再执行数组计算，避免外部链接失效。

9. 总结与后续学习方向

这篇文章想帮你建立的，不是背诵多少个数组公式，而是一种“批量处理”的思维。

回顾一下核心要点：

1. 数组就是一组数据的有序集合，Excel 中引用一行或一列区域，本质就是在处理数组；
2. 数组公式可以同时完成“判断—计算—汇总”多个步骤，不需要辅助列；
3. 旧版 Excel 中按 Ctrl+Shift+Enter 确认数组公式，新版 Excel 中直接回车；
4. 乘号表示并且关系，加号表示或者关系，这是数组条件判断的基石；
5. 优先用 SUMIFS / COUNTIFS 等原生函数，复杂条件用 SUMPRODUCT 或数组公式，最后才考虑动态数组函数；
6. 无论什么场景，都要注意区域行数一致、引用范围不要太大、公式可读性优先。

接下来你可以按这个顺序继续实践：

• 先把文中的 4 个示例在表格里跑通；
• 然后尝试把一个日常工作问题用“辅助列方案”改为“数组公式方案”；
• 再进一步学习 SUMPRODUCT 的常见用法；
• 最后，如果你的 Excel 版本支持，研究 FILTER、SORT、UNIQUE、SEQUENCE 这些动态数组函数，它们会彻底改变你对数组的认知。

数组公式在 Excel 中并不算“新东西”，但它的理解深度会直接影响你学习 Power Query、VBA 甚至 Python 处理表格时的思维模型。把今天这些基本逻辑吃透，后续再看任何数组相关的内容都会轻松很多。

建议先收藏这篇文章，等你不小心遇到“旧版 Excel 按了三键却没反应”之类的问题时，回头翻一翻第七节的排查表，大概率能找到答案。