说实话Excel 里最劝退人的一个词就是“数组”。很多人在 Excel 函数公式里一看到{}这个花括号或者一搜教程出现“数组公式”“按 CtrlShiftEnter”第一反应就是太麻烦了不想学。但如果你真的跨过这道坎会发现数组几乎是最能批量提效的工具。它不玄乎也不是编程高手的专利它就是一种“批处理”的思维。你可以不学复杂宏、不用写 VBA只要掌握数组的基本用法就能让很多原本需要辅助列才能解决的问题变成一个公式搞定。这篇文章不讲理论书上的抽象定义直接从你日常会遇到的表格场景出发拆开三个关键点数组是什么、数组公式怎么输入、数组怎么写进函数。3 分钟不敢说让你成为高手但一定能把“数组”这两个字从你的恐惧清单里划掉。1. 这篇文章真正要解决的问题先说痛点。你在工作中一定遇到过下面这些情况要求“某个月的销售总额”但日期列是“2024/1/5”这种完整日期不能直接用 SUMIF 匹配“1月”要求“部门 A 且职级是经理”的人数SUMIF 只能单条件COUNTIF 也搞不定多条件要求“产品价格为 10 到 20 元之间”的订单总金额SUMIFS 倒是可以但你的 Excel 版本比较老SUMIFS 也能用可你想顺便把条件区域里某列数据做一次加减运算后再求和求“A 列里包含某个关键字的一共几条”通配符能解决一部分但关键字不止一个而且分散在多个条件里老板扔一张大表里面有 2000 行数据你不想拖辅助列不想分 5 步操作想一个单元格直接出结果。这些问题靠基础函数不是不能做而是要加辅助列、分步骤、容易出错。而数组公式最直接的价值就是在同一套数据上完成“先计算、再判断、再汇总”的多步操作只需要一个单元格。如果你有以下任一情况这篇内容值得读完知道 VLOOKUP、SUMIF、COUNTIF 这些基础函数但看到数组就发怵已经在用 SUMIFS、INDEXMATCH但一直没搞懂它们和数组有什么关系想要形成“先做中间计算再汇总”的思维不局限于背函数。我的判断是数组不是高阶技巧而是批量思维的基础。你可以一辈子不写复杂的数组公式但至少要看得懂、能掌控 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:B10050)*B2:B100)这就是一个数组公式。整个公式的含义是判断 A2:A100 是否等于“鼠标”返回一组逻辑值判断 B2:B100 是否大于 50返回一组逻辑值两个逻辑值相乘相当于“且”的关系再乘对应行的销量最后 SUM 求和。如果你用的是 Excel 365 或 Excel 2021直接按回车就行。如果用旧版 Excel输入完需要按CtrlShiftEnter。这才是数组公式的威力你不是在描述一个单元格怎么算而是在描述一整列数据怎么批量算。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 会得到 1A2 到 A5 会自动得到 2、3、4、5同时 A1 周围出现蓝色边框表示这是动态数组溢出区域。这对于写数组公式来说是一个重大变化。新版 Excel 中大多数数组公式不再需要 CtrlShiftEnter可以直接回车。旧版 Excel2019 及更早版本里你必须按CtrlShiftEnter公式会自动加上花括号{}。3. 环境准备与前置条件在动手写数组公式前先确认一下你的 Excel 环境。3.1 Excel 版本影响操作方式版本数组公式输入方式动态数组是否支持Excel 365直接回车支持自动溢出Excel 2021直接回车支持自动溢出Excel 2019CtrlShiftEnter不支持Excel 2016 及更早CtrlShiftEnter不支持WPS 表格较新版本直接回车部分支持如果你用的是企业批量安装的 Office 2016请务必记住数组公式要按 CtrlShiftEnter否则结果会错误或者只返回第一个值。但如果你用的是 Microsoft 365 家庭版体验会好很多直接回车就行动态数组也支持。为了方便本文示例会同时标注两种情况的输入方式。3.2 准备示例数据为方便验证建议新建一个 Excel 文件Sheet1 中准备如下测试数据ABCD商品品类销量单价鼠标外设8039键盘外设4599显示器显示设备12899U盘存储15059手机支架配件20019显示器显示设备81099如果不想手工输入可以直接把下面这段粘贴到 A1商品 品类 销量 单价 鼠标 外设 80 39 键盘 外设 45 99 显示器 显示设备 12 899 U盘 存储 150 59 手机支架 配件 200 19 显示器 显示设备 8 10993.3 快捷键准备CtrlShiftEnter旧版 Excel 数组公式确认键F9在公式编辑栏选中某段表达式后按 F9可以查看这段计算出来的数组内容Esc按 F9 之后退出预览状态。这三个键是数组调试阶段最重要的工具。4. 核心流程拆解从普通公式到数组公式很多新手并不是不会写公式而是不知道“什么时候该用数组、写出来之后怎么验证”。这一节用拆解的方式把数组公式的操作路径讲清楚。4.1 第一步先用普通公式实现单条件很多人面对一个多条件问题第一反应是“函数不够用”。其实并非如此有些问题先尝试用普通函数解决至少你会清楚函数本身的行为。以“计算‘外设’品类总销量”为例。先用 SUMIFSUMIF(B2:B100,外设,C2:C100)这个公式很好理解在 B 列找“外设”对应 C 列相加。如果你想要的结果是把每一行“是否外设”的判断结果先展示出来再看总和可以用辅助列。在 E2 输入IF(B2外设,C2,0)然后向下填充到 E7最后对 E 列求和。这就是“中间计算汇总”的思维。但数组公式更高效的点在于这个 IF 判断不必放在单元格里而是直接写在内存中运算。4.2 第二步把辅助列运算“装进”内存同样的需求用数组公式可以写成SUM(IF(B2:B7外设,C2:C7,0))在旧版 Excel 中输入完按CtrlShiftEnter公式会自动变成{SUM(IF(B2:B7外设,C2:C7,0))}这个公式是怎么运算的B2:B7外设会生成一组逻辑值{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}IF 对每个逻辑值判断TRUE 则取 C 列对应值FALSE 则取 0生成一组数组{80;0;12;0;0;0}SUM 对这组数组求和得到 92。整个过程相当于中间生成了一些“看不见的辅助列”但运算完就丢弃不占单元格空间。4.3 第三步用乘号代替 IF 实现多条件IF 写法比较直观但多条件时会变得冗长。更常见的是用乘号连接多个条件。“外设品类且销量大于 50 的总销量”SUM((B2:B7外设)*(C2:C750)*C2:C7)这里的逻辑是(B2:B7外设)生成{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}(C2:C750)生成{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}两组逻辑值相乘TRUE 相当于 1FALSE 相当于 0所以只有在两个条件都为 TRUE 时乘积才为 1再乘 C 列销量其他行都变成 0SUM 求和。这种写法的核心好处是多个条件之间用乘号连接就是“并且”关系用加号连接就是“或者”关系。逻辑清晰不受函数限制。4.4 第四步验证数组公式是否正确的通用方法写数组公式最容易遇到的情况是公式返回结果不对或者只返回一个值但你又不知道哪里错了。此时最简单的方法是使用 F9 逐步调试。在公式编辑栏中用鼠标选中(B2:B7外设)这段按 F9Excel 会显示它计算出来的数组内容{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:C750)*C2:C7)方法三使用 SUMPRODUCT不需要按三键内部就是数组运算SUMPRODUCT((B2:B7外设)*(C2:C750)*C2:C7)解释方法二与方法三本质相同区别只在于输入方式。新版 Excel 和 WPS 较新版本可以直接回车。旧版 Excel 下方法二需要按 CtrlShiftEnter方法三不需要。运行结果预期外设品类中鼠标销量 80 符合条件键盘 45 不符合小于 50所以结果为 80。5.2 示例二多条件计数需求统计“品类为外设”或“销量大于等于 100”的商品个数。注意这里是“或”的关系。数组公式写法SUM(((B2:B7外设)(C2:C7100)0)*1)拆解(B2:B7外设)生成{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}(C2:C7100)生成{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输入公式后按 CtrlShiftEnter。这里{3,4}是一个数组常量告诉 VLOOKUP同时返回第 3 列和第 4 列。如果你不想用 VLOOKUP也可以用 INDEXMATCH 组合实现双列返回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 返回“外设”“显示设备”“存储”“配件”四个结果。在传统思维里要去重必须用“高级筛选”或者写复杂的 INDEXMATCH 数组公式。而动态数组改变了交互模式公式计算出来的结果是一组数据它就自然占据一组单元格不需要预先选择输出区域。如果版本不支持 UNIQUE也可以用传统的数组去重公式但复杂度较高这里不做展开。6. 运行结果与效果验证公式写完不是终点你需要验证结果是否正确。尤其是数组公式出错后的表现和普通公式不一样。6.1 如何判断数组公式是否生效在旧版 Excel 中判断标准是公式编辑栏里是否出现了花括号{}。但要注意这个花括号不是你自己输入的而是按 CtrlShiftEnter 后系统自动加的。在新版 Excel 中如果一个数组公式返回多值单元格会显示蓝色边框并且在公式编辑栏中可以看到符号和溢出区域提示。6.2 单值返回和区域返回的区别如果返回单值比如SUM(IF(...))结果会正常显示在一个单元格里如果返回多值比如VLOOKUP同时返回两列你必须先在输出区域选中对应范围否则可能会报#VALUE!错误或者只返回第一个结果。6.3 常见验证方法先算一遍手动结果用 F9 逐步检查中间数组用 SUMIFS / COUNTIFS 等普通函数交叉验证如果公式结果错误优先检查条件区域和数据区域是否行数一致。例如公式中引用B2:B7但另一个条件引用C2:C100区域长度不一致时容易产生#VALUE!错误。这是数组公式最常见的低级错误之一。6.4 新旧版本结果一致性验证同一套逻辑旧版数组公式和新版动态数组在结果上应该一致。如果新版直接回车结果正常但旧版按 CtrlShiftEnter 后结果不同大概率是“隐式交集”的问题。比如旧版中SUM((A1:A1010)*(B1:B10))如果 A1:A10 和 B1:B10 的形状不一致旧版会按交集处理导致结果不对。解决办法是确保所有区域的行数、列数完全一致。7. 常见问题与排查思路数组公式看似简单但在实际操作中会遇到各种奇怪现象。下面整理最常见的 6 类问题每一条都有具体排查方向。问题现象可能原因排查方式解决方案公式结果只返回第一个值未按 CtrlShiftEnter 确认查看公式编辑栏是否缺少花括号旧版选中单元格后按三键确认公式返回 #VALUE! 错误数组区域长度不一致或文本未加引号使用 F9 分段检查中间数组统一区域行数列数检查文本常量引号结果和预期不一致但公式没报错条件区域引用错误或乘号/加号逻辑混淆逐段按 F9 查看逻辑值和乘法结果在一个单元格先单独验证条件判断结果新版 Excel 公式报“溢出”错误公式返回多值但目标区域有其他内容阻挡查看蓝色边框是否有遮挡清除输出区域周围非空单元格或将公式移动到空白区域按 CtrlShiftEnter 后花括号出现但结果还是错中间某一步的数组运算方向不对检查逗号和分号是否用错逗号表示横向分号表示纵向确认数组形状公式没问题但表格特别卡引用了整列区域如 A:A 或 B:B查看公式计算区域范围尽量限制为有效数据区域例如 A2:A10007.1 细节问题一什么时候必须用 CtrlShiftEnter简单判断方法如果你用的是 Excel 365 或 Excel 2021大多数情况下直接回车即可如果你用的是 Excel 2019 或更早版本凡是公式中使用区域与区域之间的运算如(A1:A10是)*B1:B10都需要按 CtrlShiftEnter。还有一种情况容易被忽略公式中使用了数组常量{...}但普通函数如 SUMPRODUCT 或 LOOKUP 内部已经支持数组运算这时不需要三键。7.2 细节问题二为什么公式被系统自动加了“”符号这是新版 Excel 的“隐式交集”机制。当公式返回一个数组但只有一个单元格可以显示时新版 Excel 会自动在函数前加表示“只取交集结果”。例如SUM((A1:A10)*(B1:B10))出现这种情况通常是因为你选中了一个单元格而非多个单元格区域。如果你希望在旧版默认行为中看到数组公式效果要么使用支持数组的函数如 SUMPRODUCT要么将公式输入到足够大的区域中。7.3 细节问题三数组公式导致文件计算缓慢如果你在一张 10 万行的表格里使用数组公式每次修改任意数据都会重新计算一整个数组速度必然会慢。排查方法查看状态栏是否显示“计算中”在“公式”选项卡中把计算方式改为“手动计算”排查是否是大范围数组公式导致。解决方案优先使用 SUMIFS / COUNTIFS / MAXIFS 等原生聚合函数尽量避免对整列引用缩小数据范围使用 Excel 表格对象CtrlT 插入表格配合结构化引用如果逻辑复杂考虑用辅助列分段计算而不是一段超长数组公式。8. 最佳实践与工程建议数组公式不是越复杂越好。在实际工作中需要根据场景选合适的方案。8.1 能用原生函数就别硬写数组公式SUMIFS、COUNTIFS、AVERAGEIFS、MAXIFS、MINIFS 这些函数本身针对多条件场景做了优化性能远超“数组相乘再 SUM”的写法。假设你只是做“多条件求和”优先使用 SUMIFS它是普通函数不需要三键计算速度更快团队协作时别人也更容易读懂。只有当条件区域需要做运算时才考虑数组公式。比如“对 A 列月份字符串取前 3 位后等于‘1月’”这类需求SUMIFS 无法处理数组公式才派上用场。8.2 用 Excel 表格结构化引用降低维护成本把数据区域转换为 Excel 表格快捷键 CtrlT然后使用结构化引用数组公式的可读性会大幅提升。例如原公式SUM((B2:B100外设)*C2:C100)如果 A1 已经是一个命名为“销售表”的表格公式可以写成SUM((销售表[品类]外设)*销售表[销量])这样新增数据行时表格范围自动扩展公式无需手动修改引用区域。这个习惯特别适合在团队协作中推广。表格结构在后续使用 VLOOKUP、数据透视表时也非常友好。8.3 复杂数组逻辑用 LET 函数命名中间变量Excel 365 用户可以使用 LET 函数给中间计算结果命名既提高可读性又避免重复计算。以下公式计算“外设品类中销量高于平均销量”的商品销量总和LET( 品类列, 销售表[品类], 销量列, 销售表[销量], 平均销量, AVERAGE(销量列), SUM((品类列外设)*(销量列平均销量)*销量列) )这里先定义“品类列”“销量列”“平均销量”三个名称再写最终计算逻辑。虽然还是一个数组公式但逻辑一目了然也方便后续修改条件。8.4 不要在公式里写成“一行宇宙”很多新手喜欢把所有条件拼在一个公式里看起来非常“厉害”但维护起来很难受。一个 300 字符的数组公式三个月后你自己都看不懂。建议做法一个公式只负责一类计算多条件存储区域用命名区域或表格引用中间步骤较多时先在辅助列验证每一步结果确认无误后再合并在公式所在单元格添加批注说明数据来源、条件含义如果条件会变动把条件放在单元格里用公式引用单元格而不是写入文本常量。8.5 兼容性策略给团队用别只给自己用如果你的 Excel 表格需要发给同事而他们用的是旧版 Excel建议遵循以下规则数组公式尽量少用优先 SUMPRODUCT因为它不需要三键如果必须使用返回多值的动态数组函数如 UNIQUE、FILTER、SORT先确认对方是否使用 Office 365 或 Excel 2021如果在 WPS 中运行请注意 WPS 的动态数组支持情况与 Excel 365 不完全一致建议测试后再交付添加一个“版本要求”说明页或者在表格底部写清楚哪些公式需要 Excel 365 支持。8.6 安全与权限提醒如果公式涉及引用其他 Excel 文件的数据或者公式计算结果会被其他系统读取建议先确认数据权限和文件路径稳定性不要直接引用带空格的临时路径文件不要用数组公式跨表读取大型生产数据文件可能导致打开缓慢如果数据来自自动化导出建议先导入本工作簿再执行数组计算避免外部链接失效。9. 总结与后续学习方向这篇文章想帮你建立的不是背诵多少个数组公式而是一种“批量处理”的思维。回顾一下核心要点数组就是一组数据的有序集合Excel 中引用一行或一列区域本质就是在处理数组数组公式可以同时完成“判断—计算—汇总”多个步骤不需要辅助列旧版 Excel 中按 CtrlShiftEnter 确认数组公式新版 Excel 中直接回车乘号表示并且关系加号表示或者关系这是数组条件判断的基石优先用 SUMIFS / COUNTIFS 等原生函数复杂条件用 SUMPRODUCT 或数组公式最后才考虑动态数组函数无论什么场景都要注意区域行数一致、引用范围不要太大、公式可读性优先。接下来你可以按这个顺序继续实践先把文中的 4 个示例在表格里跑通然后尝试把一个日常工作问题用“辅助列方案”改为“数组公式方案”再进一步学习 SUMPRODUCT 的常见用法最后如果你的 Excel 版本支持研究 FILTER、SORT、UNIQUE、SEQUENCE 这些动态数组函数它们会彻底改变你对数组的认知。数组公式在 Excel 中并不算“新东西”但它的理解深度会直接影响你学习 Power Query、VBA 甚至 Python 处理表格时的思维模型。把今天这些基本逻辑吃透后续再看任何数组相关的内容都会轻松很多。建议先收藏这篇文章等你不小心遇到“旧版 Excel 按了三键却没反应”之类的问题时回头翻一翻第七节的排查表大概率能找到答案。