很多人在 Excel 表格里看到别人写的公式长成这样{SUM(A2:A10*B2:B10)}第一反应往往是“这什么写法看不懂先划走”。这种反应不奇怪因为数组公式的显示方式、输入方式和普通公式完全不同尤其是老版本 Excel 里还要按住 CtrlShiftEnter 三个键稍不注意就报错。但数组公式本身并不难。它解决的是一类真实需求让公式一次性处理一组数据而不是一个单元格。日常办公里“单价乘数量求总金额”“统计某个部门符合条件的订单数”“把一列姓名合并成一行”这些场景都能用数组思维快速解决。这篇文章会先讲清楚数组在 Excel 里到底是什么再区分新版和旧版 Excel 的不同操作方式然后通过一个最小案例带你把数组公式亲手输入一遍并用 F9 调试公式的中间结果。最后给出多条件统计、不重复计数、文本合并等高频率场景公式以及一版可以直接对照排查的报错表。读完以后你再看到带花括号的公式至少能判断它到底做了什么而不是直接划走。1. 先搞懂 Excel 里的“数组”到底指什么1.1 数组一组数据被当成一个整体处理Excel 里普通公式的对象是一个单元格。比如A2*B2它只处理 A2 和 B2 这两个单元格。数组不同数组是一组数据打包在一起公式对这组数据同时进行相同的运算。举个例子。下面这张表商品单价数量键盘1992鼠标895显示器10991摄像头1293如果按普通思路计算总金额需要先加一列“金额”在 E2 写C2*D2下拉到 E5最后再SUM(E2:E5)。整个过程需要引入一个辅助列。数组公式的思路是跳过辅助列直接让“单价区域”和“数量区域”按位置一对一相乘然后交给 SUM 求和SUM(C2:C5*D2:D5)这个公式里C2:C5*D2:D5会生成一组临时结果398、445、1099、387。这组结果只存在于 Excel 内存中没有写在任何单元格里所以被称为“内存数组”。SUM 再把这一组结果累加得到 2329。这里的核心转变是你把“两列数据相乘”当成一个整体操作交给了公式而不是手工逐行计算。数组公式的威力就是来自这种“批量处理”。1.2 老版本为什么要按 CtrlShiftEnter在老版本 ExcelExcel 2019 及更早版本中公式要返回多个值或进行数组运算时Excel 无法自动判断这是一个数组公式所以要求用户用 CtrlShiftEnter 三个键结束输入。输完以后编辑栏和单元格里会出现花括号{SUM(C2:C5*D2:D5)}注意一个关键点花括号是 Excel 自动加上的不是手输进去的。如果你手打花括号Excel 会把整个内容当成文本处理公式不生效。这也是数组公式劝退很多人最大的原因普通公式输入完按回车就行数组公式却要按三个键修改公式后还要再按一次三键否则结果不变。这个交互习惯和普通公式差异太大导致很多人觉得“数组公式很复杂”。以上是老版本的情况。从 Excel 2021 和 Microsoft 365 开始情况发生了变化。1.3 动态数组新版 Excel 的第二个阶段新版 Excel 引入了动态数组机制。公式返回多个结果时会自动“溢出”到相邻单元格不再需要选中区域也不需要按 CtrlShiftEnter。比如在新版 Excel 中直接在 D2 输入C2:C5*D2:D5按回车后D2、D3、D4、D5 会同时出现结果。这种自动填充到多个单元格的效果就是“溢出”。动态数组下拉带来两个实用变化输入数组公式像输入普通公式一样简单回车即可。新增了 FILTER、UNIQUE、SORT、SEQUENCE 等专为数组设计的函数很多原本很复杂的“提取”“去重”“排序”问题一个函数就能完成。这也是为什么后面会反复强调版本问题。同样的思路在新版 Excel 和旧版 Excel 中的写法完全不同。2. 动笔前先确认自己用的 Excel 版本2.1 不同版本对数组公式的支持差异写数组公式之前先确认自己用的 Excel 版本否则会出现“我明明按教程写了公式为什么结果不对”的问题。不同版本对数组公式和处理方式差异很大能力Excel 2021 / Microsoft 365Excel 2019 / 2016WPS 表格动态数组自动溢出支持不支持部分支持以实际版本为准FILTER、UNIQUE、SORT、SEQUENCE支持不支持部分支持TEXTJOIN 文本合并支持Excel 2019 支持2016 不支持部分支持传统 CSE 数组公式支持支持支持判断方法很简单随便找一个空单元格输入A1:A3如果回车后多个单元格同时出现 A1、A2、A3 的内容说明你的版本支持动态数组如果只显示 A1 的内容说明当前版本是旧版行为。这个判断很关键直接决定你后面是按回车还是按三键。2.2 老版本环境下的兼容写法如果你用的是 Excel 2019 或更早版本或者需要给这类同事发送工作簿建议优先使用 SUMPRODUCT 函数。SUMPRODUCT 天生就支持数组运算不需要按 CtrlShiftEnter。比如统计“销售一部”的订单数老版本数组公式是{SUM((A2:A10销售一部)*1)}但更稳妥的写法是SUMPRODUCT((A2:A10销售一部)*1)效果完全一样却省去了三键输入。同理多条件求和中SUMPRODUCT 也比传统数组公式更容易维护。这里给一个经验判断如果你的工作场景要求表格在多个版本的 Excel 中兼容尽量用 SUMPRODUCT 代替需要按三键的 SUM 数组公式。这样同事打开文件时不会因为忘按三键而算出错误结果。2.3 新版本里容易踩的 和溢出问题新版 Excel 虽然方便但有两个新坑需要注意。第一个坑是 隐式交集。当你在新版 Excel 中打开一个旧版数组公式时Excel 可能会自动在公式前面加上 把原来的数组运算变成“只取第一个值”。例如旧版公式SUM(C2:C5*D2:D5)在新版中显示时可能变成SUM(C2:C5*D2:D5)结果就只计算了第一行数据导致金额变小。遇到这种情况要检查公式里是否被自动加了 如果有删掉 再回车。第二个坑是 #SPILL! 错误。新版公式返回多个结果时如果目标单元格旁边的区域已经有内容Excel 会报 #SPILL!意思是结果溢出的地方被占用了。清空目标区域即可或者用 INDEX 只取结果中的某一部分。这两种报错都是在旧版 Excel 中不存在的新问题。这也是为什么说明版本非常重要用新版的思路去操作旧版会报错用旧版的习惯写新版公式也可能出错。3. 最小上手案例一次算完“单价乘数量”3.1 数据准备一张最简单的商品表为了把数组公式跑通先准备一张最简单的表。打开 Excel在 A1:D5 区域输入商品单价数量金额键盘1992鼠标895显示器10991摄像头1293这张表的目的是计算每种商品的金额以及总计金额。先不要手工在 D 列填写公式后面用数组公式一次性完成。3.2 数组求和把中间步骤交给内存在 D2 单元格输入下面这个公式完成“单价乘数量再求和”SUM(C2:C5*D2:D5)注意这里 C 列是单价D 列是数量但我在前面表格里把 D 列预留给“金额”列了。为了不产生歧义把公式写清楚SUM(B2:B5*C2:C5)其中 B2:B5 是单价C2:C5 是数量。输入完成后根据版本选择结束方式新版 Excel 或支持动态数组的版本直接按回车。旧版 Excel按 CtrlShiftEnter。如果看到公式两边出现花括号{SUM(B2:B5*C2:C5)}说明数组公式输入成功。这里要解释一下执行过程。Excel 会先计算B2:B5*C2:C5也就是199 * 2 39889 * 5 4451099 * 1 1099129 * 3 387然后 SUM 把 398、445、1099、387 相加得到 2329。如果你在 D2 输入公式后返回 2329说明数组公式已经成功运行。3.3 理解中间结果一组数据经过运算变成一组新数据这一步是理解数组公式的关键。B2:B5*C2:C5不是一个值而是一组值。它就像一条流水线左边进来一列单价右边进来一列数量中间逐个相乘出口出来一列新的数字。可以在一个空白区域验证这个说法。选择 F2:F5输入B2:B5*C2:C5新版 Excel直接回车F2 到 F5 自动填充结果 398、445、1099、387。老版 Excel先选中 F2:F5输入公式后按 CtrlShiftEnter同样会出现四个结果。这组临时结果就是“内存数组”。数组公式的本质就是这种“一组数据按位置一一对应运算”的机制。理解了这一步就理解了 80% 的数组公式。4. 用 F9 把数组公式拆开逐段看结果4.1 调试操作在编辑栏选中公式片段再按 F9很多数组公式写出来结果不对但肉眼很难发现问题。这时候最有效的调试方法就是 F9。操作步骤很简单选中包含公式的单元格。进入编辑栏用鼠标选中公式中的某段比如B2:B5*C2:C5。按键盘上的 F9 键。Excel 会显示这一段计算出来的结果。例如选中B2:B5*C2:C5后按 F9会看到{398;445;1099;387}这表示这一段公式已经正确计算出了四个结果。如果看到这个结果说明问题不在乘法而是在外层函数或区域引用上。同样的方式也可以检查条件判断。比如公式里有一句A2:A6销售一部按 F9 后会看到{TRUE;FALSE;TRUE;FALSE;TRUE}这些 TRUE 和 FALSE 就是数组中每个元素进行判断的结果。4.2 排查顺序从中间值倒推问题位置F9 调试法有一个固定的排查顺序建议按照从内到外、从条件到结果的顺序进行先检查条件区域是否返回了正确的 TRUE/FALSE 数组。再检查运算区域是否返回了正确的数值数组。然后检查两个数组相乘后是否得到了 0、1 或错误值。最后看外层 SUM 或 SUMPRODUCT 是否把中间结果正确汇总。如果中间值出现这些情况要能判断病因F9 结果可能的含义{#VALUE!;0;1}区域大小不一致或文本型数字参与运算{FALSE;TRUE;FALSE}条件判断本身没问题但还需要参与数值运算{0;0;0}条件全部不满足或区域引用错位{#N/A;1;2}公式中存在查找类函数某些值找不到{1;1;0}条件匹配成功但需要确认是不是多个条件逻辑正确F9 是数组公式最好的“透视镜”。它能让你看到公式在每一个位置上的临时结果而不是只有一个抽象的总数。4.3 常见调试坑F9 之后不要直接回车F9 调试有一个非常容易踩的坑查看完中间结果后如果按了回车公式会被临时结果替换掉。举例来说你选中B2:B5*C2:C5后按 F9如果这时直接回车公式可能变成SUM({398;445;1099;387})原来的区域引用全部消失了换成了硬编码的数字。以后原始数据一改这个公式不会跟着更新。正确做法是查看完 F9 的结果后按 Esc 退出编辑状态这样临时结果只是显示出来供你观察不会写入公式。注意F9 会把选中片段的计算结果直接写进公式。查看完中间结果后一定要按 Esc 退出编辑状态不要按回车。5. 三个高频场景直接拿去用5.1 多条件统计用 * 代替 AND这是数组公式最实用的场景之一。比如要根据“部门”和“金额”两个条件统计订单数。数据结构如下部门订单金额销售一部800销售二部1200销售一部1500销售二部600销售一部2000统计“销售一部”且“订单金额大于 1000”的订单笔数SUM((A2:A6销售一部)*(B2:B61000))新版直接回车旧版按 CtrlShiftEnter或者直接用 SUMPRODUCT 写法SUMPRODUCT((A2:A6销售一部)*(B2:B61000))运算过程是这样的第一段A2:A6销售一部得到{TRUE;FALSE;TRUE;FALSE;TRUE}第二段B2:B61000得到{FALSE;TRUE;TRUE;FALSE;TRUE}两个数组相乘后得到{0;0;1;0;1}SUM 累加得到 2这里最容易掉的坑是用 AND 代替 *SUM(AND(A2:A6销售一部,B2:B61000))这样写大概率会返回错误结果。原因在于 AND 函数会把整个数组当成一个整体来判断只返回一个 TRUE 或 FALSE而不是逐行返回一组 TRUE/FALSE。在数组公式中需要逐条判断的场景必须用*连接条件。5.2 不重复计数新旧版本两种写法统计“不重复客户数量”是另一个高频需求。数据如下客户张三李四张三王五李四新版 Excel 直接使用 UNIQUE 配合 COUNTACOUNTA(UNIQUE(A2:A6))UNIQUE 会返回不重复客户列表{张三;李四;王五}COUNTA 统计非空数量得到 3。旧版 Excel 没有 UNIQUE只能使用经典数组公式SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))这个公式的原理是利用了“倒数求和”的思路每个客户出现 n 次COUNTIF 就返回 n1/n 这组数据加起来正好是 1。比如“张三”出现 2 次贡献 1/21/21“李四”出现 2 次也是贡献 1三个客户合计 3。这个公式巧妙但不好记忆而且当数据量很大时计算会变慢。如果你的版本支持 UNIQUE优先用 UNIQUE它们解决的问题是一样的计算不重复项数量。5.3 一列数据按逗号合并TEXTJOIN 与数组思维日常办公中经常要把一列姓名、编号或邮箱合并到一行用逗号隔开。Excel 2019 及以上版本可用 TEXTJOINTEXTJOIN(,,TRUE,A2:A10)第一个参数是分隔符第二个参数 TRUE 表示忽略空白单元格第三个参数是要合并的区域。这里没有用到 CtrlShiftEnter但它体现的仍然是“把区域当成整体处理”的数组思维。Excel 遍历 A2:A10 的每一个单元格将它们按逗号拼接成一个文本。如果是 Excel 2016 老版本没有 TEXTJOIN 函数可以用 VBA 或者把数据转置后合并但操作明显更繁琐。这个例子也说明新函数出现后很多原本需要复杂数组公式解决的问题已经变成了一个函数调用。6. 常见报错与排查路径6.1 现象速查表数组公式的报错种类不多但每种都很容易让人迷惑。把常见现象整理成表格直接对照排错问题现象常见原因检查方式处理建议公式结果不对且没有出现花括号老版本未按 CtrlShiftEnter查看编辑栏公式是否被花括号包裹重新进入编辑状态并按下三键出现 #VALUE! 错误数组区域大小不一致或文本型数字参与运算用 F9 查看中间结果统一区域范围将文本数字转成数值出现 #SPILL! 错误新版公式结果要溢出但目标区域被占用检查目标单元格旁边是否有内容清空溢出区域或使用 INDEX 限制结果范围多条件统计结果全部为 0条件区域与统计区域错位检查区域引用是否指向正确列确保多个数组的行数完全一致用 AND 连接条件后结果不对AND 不会逐行计算而是返回单个结果把公式拆开按 F9 查看用*代替 AND结果只有第一行数据新版 Excel 自动加了 隐式交集检查公式里是否多出 符号删除 确认使用动态数组功能6.2 为什么数组区域大小必须一致数组公式的运算规则要求参与运算的多个数组必须具有相同的行数和列数。比如C2:C5*D2:D5是两个 4 行 1 列的数组可以按位置相乘。但如果写成C2:C5*D2:D6一个是 4 行一个是 5 行Excel 无法确定 C2 是和 D2 对齐还是和 D6 对齐就会返回 #VALUE!。排查这类问题的方法还是 F9。选中出错公式中的某个区域按 F9 查看它返回了几个值。如果两个区域返回的数组长度不一致基本可以确定是区域错位。6.3 为什么条件区域和统计区域会错位多条件统计中还有一个隐藏问题两个条件明明都成立但结果却是 0。原因通常是两个条件引用的区域没有对齐。例如统计“销售一部”且“金额大于 1000”的订单数却写成SUMPRODUCT((A2:A6销售一部)*(B3:B71000))A 列从第 2 行开始B 列从第 3 行开始两边的判断对象错开了一行。Excel 不会报错但计算结果没有任何意义。数组公式要求所有关联条件必须基于同一行进行判断。写公式时先检查每个区域的行号是否一致。这是数组公式最容易排查、也最容易忽略的问题。7. 最佳实践让数组公式稳定、可维护、兼容7.1 学习环境与工作文件的差异学习数组公式和正式写进工作文件要求完全不同。学习阶段可以大胆尝试新建一个空白工作簿随便造几行数据使用新版动态数组的 FILTER、UNIQUE、SORT 函数用 F9 反复查看中间结果。这时候不用太在意性能重点是理解内存数组和运算规则。但正式工作文件就要谨慎得多确认团队其他人使用的 Excel 版本避免写出只有你电脑能显示的公式。涉及关键统计的数组公式建议在旁边加一个普通公式或辅助列做交叉验证。正式文件中的数据量可能达到几万行数组公式数量较多时表格会明显变慢要评估是否值得。注意数组公式不是万能的。如果工作簿需要在旧版 Excel 中打开并持续使用优先考虑 SUMPRODUCT 或辅助列再考虑数组公式。7.2 什么时候不要用数组公式数组公式虽然强大但并非所有场景都适合使用。以下几种情况建议换用其他方案第一数据量极大时。几万行数据参与数组运算每次重新计算都需要遍历整个区域表格会变得异常卡顿。此时应该尽量使用透视表或数据库而不是在单元格里硬算。第二需要频繁修改公式时。数组公式修改后容易忘记按三键容易在团队协作中埋下隐患。如果条件经常变化辅助列加普通函数是更稳定的选择。第三涉及“几个数相加凑成一个数”这类组合搜索问题。有些用户想在 Excel 里实现“从一堆数字中找出哪些相加等于某个值”数组公式虽然可以做两两组合但数字一多计算量会指数增长而且公式极难维护。这类问题用规划求解或 Python 脚本更合适而不是硬写数组公式。7.3 数组公式使用前检查清单写数组公式前可以用下面这份清单过一遍确认当前 Excel 版本是否支持动态数组。确认所有参与运算的数组区域行数和列数一致。确认文本型数字已经转为数值类型。确认条件区域和统计区域从同一行开始。确认多个条件使用*连接而不是 AND。确认旧版环境下是否应该改用 SUMPRODUCT。确认新版公式没有多余的 符号。确认公式返回多个结果时目标溢出区域没有其他内容。用 F9 检查过关键片段的中间结果。正式文件里用辅助列或普通公式交叉验证一次结果。这套清单可以当成个人笔记也可以贴在表格旁边当公式审查标准。7.4 建议的学习路径数组公式入门不需要一次性掌握所有函数建议按下面的顺序练习先用SUM(B2:B5*C2:C5)理解“内存数组”概念。再用SUM((条件区域条件)*1)练习条件计数。用 SUMPRODUCT 做多条件求和体会兼容写法。新版用户直接学习 FILTER、UNIQUE、SORT、SEQUENCE 这一组动态数组函数。最后再看 INDEXSMALLIF 这类老版本复杂写法理解它们的原理即可不必作为主要工具。数组公式真正的门槛不在数学而在输入习惯和对内存数组的理解。把最小案例亲手输入一遍再用 F9 看一次中间结果Excel 数组就不再是“看到就想划走”的知识点。日常办公里 80% 的统计和提取需求都可以用这套思维解决。