你不会真的以为Excel 里的“数组”是程序员专属的东西吧每次看到公式里出现{SUM(IF(A2:A10手机,B2:B10))}这种带花括号的写法就想划走别急。这篇文章不绕弯子直接用最简单的例子把 Excel 数组讲清楚。你可以不懂编程、没写过一行代码只要会复制粘贴公式就能搞明白数组到底是什么、有什么用、怎么用。这次我们会从数组的本质开始依次走一遍数组公式的输入方式、最常见的三个实战场景再讲 Microsoft 365 里新增的动态数组函数比如 FILTER、UNIQUE、SORT最后给出排查思路和性能建议。全文不追求概念严谨到教科书级别只求你看完能上手用。先给一个结论数组不是 Excel 的高级功能而是 Excel 运算的基本方式。你平时写的A1*B1是单值运算当你把这一行公式拖到第 10 行其实就已经在用数组的逻辑了。只是你还不知道而已。1. 核心能力速览能力项说明适用版本Excel 2019 及更早版本需要按 CtrlShiftEnter 输入数组公式Microsoft 365 和 Excel 2021 起支持动态数组公式会自动溢出核心功能一次计算处理多个单元格数据、批量条件求和、多条件筛选、数组去重、内存数组中间结果复用常见操作输入数组公式、认识花括号{}、使用动态数组函数、用数组思维替代辅助列适合场景数据统计、业务报表、财务对账、文本提取、数据清洗、二级联动菜单难点理解“数组是一组元素组成的整体”以及旧版输入方式和动态数组的差异替代方案如果实在不想用数组公式可以用辅助列、透视表、Power Query 实现部分效果但公式会变长、文件会变大2. 数组到底是什么用一句话说清楚数组就是一组数据按顺序排在一起。Excel 里一个区域、一行、一列本质上就是数组。你可以把它理解成一个“批量处理的小组”普通公式一次只处理一个单元格数组公式一次处理一整组。举个例子。A 列是销售员B 列是销售额。你想求“销售员为张三的销售额合计”。普通思路先加辅助列在 C2 写IF(A2张三,B2,0)向下填充再对 C 列求和。数组思路直接用SUM(IF(A2:A10张三,B2:B10,0))一个公式完成判断和求和。这里的IF(A2:A10张三,...)就是逐行比较每一行的 A 列是否等于“张三”返回一组值为 0 或销售额的中间结果最后交给 SUM 汇总。全程只用了两个单元格区域没有辅助列。数组的难点不在“是什么”而在“能不能把一个区域当成一个变量来用”。一旦你接受“区域可以整体参与计算”很多公式的理解难度会立刻下降。3. 环境准备确认你的 Excel 版本支持哪种数组在开始之前先确认版本。不同的 Excel 版本数组公式的输入方式完全不同。版本数组公式输入方式是否支持动态数组Excel 2010 / 2013 / 2016 / 2019输入公式后按 CtrlShiftEnter公式两端会出现花括号{}不支持Microsoft 365Office 365直接按 Enter公式结果自动溢出到相邻单元格支持Excel 2021直接按 Enter自动溢出支持WPS 表格版本较新时支持动态数组但部分函数行为与 Excel 不完全一致不完全一致判断方法很简单在单元格输入SEQUENCE(3,1)后直接按 Enter。如果结果是自动生成 1、2、3 三个数字说明你的版本支持动态数组。如果显示#NAME?或#VALUE!说明你的版本不支持 SEQUENCE 函数需要走传统 CtrlShiftEnter 路线。这篇文章的示例会同时给出逻辑说明方便两套版本对照。如果你用的是 Microsoft 365体验最好几乎所有参数都可以自动扩展。4. 第一次真正使用数组公式求和、计数、平均新手学数组不必一上来就背函数列表。先把最常用的三个场景跑通感受数组“一次算一组”的威力。4.1 多条件求和需求统计 B 列中“电子产品”分类的销售额合计。数据假设A 列分类B 列销售额电子产品1200日用品300电子产品800普通公式SUMIF(A2:A4,电子产品,B2:B4)这个公式已经是“单条件数组逻辑”的代表作只是 SUMIF 内部帮你处理了数组判断。如果你需要多条件比如“销售员为张三且分类为电子产品”可以用 SUMIFSSUMIFS(B2:B4,A2:A4,电子产品,C2:C4,张三)只有在条件本身很复杂或者需要对数组中间层做处理时才需要自己构造数组公式SUM(IF((A2:A4电子产品)*(C2:C4张三),B2:B4,0))旧版按 CtrlShiftEnter新版直接按 Enter。这里使用*号连接两个条件相当于逻辑与。Excel 里 TRUE 乘以 TRUE 等于 1只要有一个为 FALSE结果就是 0。这个写法是数组公式做多条件判断时的核心技巧。4.2 条件计数统计“销售额大于 500”的订单数量COUNTIF(B2:B10,500)如果条件来自一个数组比如统计“负责人姓名在名单 E2:E5 中”的订单数量普通 COUNTIF 不方便可以这样SUMPRODUCT(COUNTIF(A2:A10,E2:E5))这个公式的本质是对名单里的每个负责人分别统计其在 A2:A10 中出现的次数得到一个数组再相加。你不需要辅助列不需要逐个写 COUNTIF 再求和。SUMPRODUCT 在这里替代了 CtrlShiftEnter让旧版也能正常处理数组。4.3 指定条件的平均值AVERAGE(IF(B2:B10500,B2:B10,))旧版按 CtrlShiftEnter。逻辑是只对满足条件的数字取平均不满足的返回空文本AVERAGE 忽略文本所以不会参与平均。用\或占位是数组公式处理条件平均的常见方式。5. 数组公式的核心操作区域运算与逐元素处理理解数组的关键在于Excel 会把你框选的区域拆成一个个元素分别做相同操作最后组合成结果数组。5.1 区域与单值逐元素相乘A1:A5*2的意思是把 A1 到 A5 的每个值都乘以 2结果是一个包含 5 个值的数组。在 Microsoft 365 里这个结果会自动放在公式所在单元格的下方。在旧版里你需要选择 5 个垂直单元格输入公式后按 CtrlShiftEnter 才会填满。5.2 区域与区域逐元素相加A1:A5B1:B5会生成一个数组依次为 A1B1、A2B2……直到 A5B5。区域大小不一致时Excel 会尽量对齐不能对齐的部分返回#N/A或#VALUE!具体取决于版本和数据结构。5.3 一维数组与二维数组一维数组可以理解为一行或一列比如 A1:E1 是水平一维数组A1:A5 是垂直一维数组。二维数组就是一个矩形区域比如 A1:C5。二维数组的典型场景是用数组公式计算多行多列的结果。例如统计“每个月的每个品类销售额是否达标”可以用矩阵运算一次生成多个结果而不是逐行写公式。这听起来抽象但如果你用过 SUMPRODUCT 做二维条件求和其实已经碰过二维数组了。SUMPRODUCT((A2:A10手机)*(B1:D1华东)*B2:D10)这个例子中A 列是产品名第 1 行是地区B2:D10 是销售额矩阵。第一个条件是对行方向筛选第二个条件是对列方向筛选最后将两个判断矩阵和原始数据逐元素相乘后求和。这就是二维数组的实战用法。如果只是做纯粹的二维条件汇总更推荐数据透视表。但数组公式的好处是不需要刷新、结果随源数据自动更新适合做报表模板。6. 动态数组函数Microsoft 365 用户必看如果你用的是 Microsoft 365 或 Excel 2021恭喜你可以直接使用动态数组函数。这些函数天然按数组思维设计不再需要 CtrlShiftEnter公式写好后会自动扩展到合理范围。6.1 FILTER动态筛选这是数组函数里最实用、最容易上手的一个。FILTER(A2:C100,B2:B100华东,无数据)含义从 A2:C100 区域中筛选出 B 列等于“华东”的所有行。如果结果为空返回“无数据”。FILTER 不需要按 CtrlShiftEnter函数名本身就会让 Excel 把结果“溢出”到下方单元格区域。你只需要在任意一个空白单元格输入公式即可。多条件筛选可以这样FILTER(A2:C100,(B2:B100华东)*(C2:C1001000),无数据)这个场景在实际工作中非常常见业务明细表是原始数据你想临时拉出某个区域、满足某个金额条件的订单。过去要么用高级筛选要么用透视表现在一个 FILTER 就能解决。FILTER 还可以搭配其他数组函数做进一步处理。比如只提取两列而不是整行FILTER(A2:A100,B2:B100华东)这相当于生成一个“符合条件的客户名单”。把这个结果再传给 UNIQUE 去重就是标准的“数组套数组”用法。6.2 UNIQUE数组去重UNIQUE(A2:A100)获取一个区域的不重复值列表。数据清洗场景里经常用到从一列混杂重复的值中提取所有唯一值过去需要“高级筛选”或“删除重复项”现在用函数动态生成源数据变化后结果也会自动更新。如果想去重后求和可以配合 SUM、SUMIF 实现SUM(SUMIF(A2:A100,UNIQUE(A2:A100),B2:B100))这个公式的逻辑是先取出不重复客户再分别计算每个客户的订单总额最后求和。看起来平常但它本质上是一个内存数组的中间过程完全不需要辅助列。6.3 SORT / SORTBY动态排序SORT(A2:C100,2,-1)将 A2:C100 区域按第 2 列的降序排列。第 3 个参数 -1 表示降序1 表示升序。SORT 的典型场景数据源更新后排序结果也需要同步更新。传统做法是重新执行排序操作用公式则可以在打开文件时自动得到最新排序结果。SORTBY 支持按照另一个列的条件排序而不是区域本身SORTBY(A2:C100,B2:B100,1)意思是A2:C100 的行顺序按照 B2:B100 的升序重排。如果业务上需要“按客户类型排序后再展示”SORTBY 更直观。6.4 SEQUENCE生成序列SEQUENCE(5,1,1,1)生成从 1 开始、步长为 1 的 5 个数字输出为 5 行 1 列。SEQUENCE 最常见的用法是生成序号、构建辅助数组、模拟日期序列。生成日期序列的示例SEQUENCE(10,1,DATE(2025,1,1),1)从 2025 年 1 月 1 日开始依次生成 10 天日期。结合 TEXT 函数可以快速生成一周的星期名称列表TEXT(SEQUENCE(7,1,DATE(2025,1,6),1),aaaa)这个公式会生成星期一到星期日的中文名称。用在排班表、值班表模板中非常方便。6.5 数组“堆叠”函数整理结构化数据VSTACK 可以把多个区域垂直拼接VSTACK(A2:C10,E2:G10)HSTACK 则水平拼接HSTACK(A2:A10,D2:D10)这两个函数适合合并多个工作表或拆分表的同构数据。过去要用 Power Query 或复制粘贴现在可以直接用公式生成一个合并后的区域。在此基础上再用 UNIQUE 和 FILTER 做二次处理几乎可以完成很多轻量级 ETL 工作。7. 常用数组应用场景这些实际业务可以直接套用很多人以为数组公式只适合“炫技”实际恰恰相反。下面这几个场景是业务数据中最常遇到的。7.1 按条件提取不重复的客户清单需求从订单表中提取“华东区域”的不重复客户。UNIQUE(FILTER(A2:A100,B2:B100华东))这个公式先筛选出符合条件的客户再去重。结果会自动对齐到相邻单元格。如果需要排序SORT(UNIQUE(FILTER(A2:A100,B2:B100华东)))三个函数嵌套在一行里实现了“筛选 去重 排序”这在传统公式里需要多个辅助列才能完成。7.2 多条件查找支持返回多个结果VLOOKUP 单条件查找大家都会。遇到“根据产品编码和批次号两个条件查找价格”传统写法是用辅助列把两列合并成唯一值再 VLOOKUP。有了数组和动态数组函数可以这样FILTER(D2:D100,(A2:A100P001)*(B2:B100202401),未找到)其中 A 列是产品编码B 列是批次号D 列是价格。这个公式返回所有同时满足两个条件的价格。如果结果唯一就是一个多条件查找如果有多个则自动返回列表。7.3 字符串拆分与重组合并使用 TEXTSPLIT 或 TRIM MID SEQUENCE 可以处理文本数组。Microsoft 365 中TEXTSPLIT(A1,,)把 A1 单元格里的逗号分隔文本拆成多列。如果按行拆TEXTSPLIT(A1,,,;)第二个分隔符是列分隔符第三个是行分隔符。旧版 Excel 里可以用TRIM(MID(SUBSTITUTE(A1,,,REPT( ,100)),SEQUENCE(1,3,1,100),100))这个公式的思路是先把所有逗号替换成 100 个空格然后用 MID 配合 SEQUENCE 按固定间隔提取最后用 TRIM 去掉多余空格。它体现了数组构造的核心能力在内存里生成一组位置再对每个位置做同样处理。新版直接用 TEXTSPLIT 可以替代但对理解数组原理很有帮助。7.4 合并单元格内容需求把一个区域的所有非空单元格合并成一句中文顿号分隔的文本。TEXTJOIN(、,TRUE,UNIQUE(FILTER(A2:A100,A2:A100)))TEXTJOIN 是字符串合并函数第二参数 TRUE 表示忽略空单元格。配合 FILTER 和 UNIQUE先筛选、再去重、最后合并。这在做标签汇总、关键词归类时很常见。7.5 二级联动菜单中的数据验证数据验证通常不叫数组公式但它的序列来源可以是动态数组。比如先定义“区域”列表然后根据选中的区域动态生成“城市”列表。使用数据验证的“序列”功能时可以直接引用FILTER(城市表[城市],城市表[区域]区域单元格)这样下拉菜单会根据上一级选择动态更新。旧版的经典做法是使用 INDIRECT 函数引用命名区域或者用偏移量构造动态引用原理也是数组思维只是方式更绕一些。8. 数组公式与外部工具联动Python、Excel VBA 和自定义接口Excel 数组不应该只看作表格内的公式问题。在真实业务中经常要把 Excel 数据交给 Python 处理或者把文件作为接口数据源。理解数组结构后你可以更顺畅地在 Excel 和编程语言之间切换思路。如果你用 Python 读取 Excel 文件常见库是 pandas。读取后数据会以二维数组的形态存在行和列都对应一个集合。比如import pandas as pd df pd.read_excel(销售数据.xlsx) filtered df[df[区域] 华东] print(filtered[[客户, 销售额]].to_string(indexFalse))这段代码的逻辑和 Excel 里的 FILTER 函数完全一致先读取一个二维表再按行条件筛选最后选择需要的列。学习数组公式有助于你理解 pandas 中的布尔索引和向量化操作因为两者都是“对整列做判断、得到一组布尔值、再根据布尔值选择行”。如果你在处理包含数组的接口数据思路同样相通。Excel 表格被导入程序后通常就是一个二维数组Python 中的 list 套 list、JSON 里的嵌套数组都是相同的“数据在内存中的组织形式”。在 Excel 里你看到的是单元格区域在程序里你看到的是数组、列表和字典但操作逻辑一致先选择行再选择列再做条件判断和聚合。如果你用 Excel VBA 处理数组可以先一次性把区域读入内存数组减少单元格访问次数提高宏的运行速度Dim arr As Variant arr Range(A2:C100).Value这段代码把单元格区域一次性读入内存数组后续操作只针对数组不频繁读写工作表速度会快很多。如果你要做批量处理尤其是几千行以上的数据这种“区域转数组”的写法非常实用。9. 资源占用、性能观察与降优化技巧数组公式看起来简单实际数据处理量上来后计算速度和文件大小会受影响。下面给出可操作的建议。9.1 观察点Excel 中观察计算性能主要看状态栏的“计算”提示以及修改单元格后公式重算的卡顿时间。如果你在公式中引用了整列比如A:A每次重算时 Excel 都要检查 100 万行速度会明显变慢。即便实际数据只有几千行引用整列也会导致无效计算。9.2 降低计算负担做法说明避免整列引用写A2:A1000而不是A:A少用易失函数OFFSET、INDIRECT、RAND、TODAY 会触发频繁重算限制动态数组范围动态数组结果自动扩展时尽量约束源区域不要整列取数计算选项设为手动数据量大时把公式计算设为“手动”改完数据再按 F9 重算用透视表代替复杂数组公式明细数据量大时透视表计算性能远高于数组公式9.3 动态数组的溢出范围控制动态数组公式会在一处输入后向相邻单元格扩展。如果扩展区域里有其他数据Excel 会返回#SPILL!错误。这时候要检查公式输出目标区域是否为空或者把公式移动到新的空白位置。这是 Microsoft 365 用户最常遇到的数组相关错误之一。9.4 什么时候不该用数组公式数据行数超过几万行、公式数量上千个时数组公式可能导致工作簿卡顿。这时候建议改用 Power Query 做数据清洗和聚合或者用透视表做统计再或者用 Python 脚本批量处理 Excel 文件。工具选择没有一定之规关键是先判断数据量和更新频率。10. 常见问题与排查方法问题现象可能原因排查方式解决方案公式两端自动出现花括号{}旧版 Excel 数组公式正常标志确认是否按了 CtrlShiftEnter旧版勿手工输入花括号按三键后会自动生成输入公式后报#VALUE!数组区域大小不一致或多个数组形状无法对齐检查区域行数/列数是否匹配调整区域范围或使用 SUMPRODUCT显示#SPILL!动态数组结果区域被其他内容占用找到溢出区域中的阻碍单元格清空目标区域或移动公式显示#NAME?函数在当前版本中不存在确认 Excel 版本和函数名称拼写升级 Microsoft 365或改用传统函数数组公式结果不自动扩展到整列当前是旧版 Excel检查是否开启动态数组功能使用 CtrlShiftEnter 固定返回值FILTER 返回空结果条件判断全为 FALSE检查条件区域和条件值是否匹配确认文本前后无空格使用*通配符时要注意是否适用于当前函数使用 UNIQUE 后结果顺序改变去重函数默认返回首次出现顺序若需排序再套一层 SORTSORT(UNIQUE(...))公式计算速度慢引用了整列或公式过多查看公式管理器、检查区域引用缩小区域设置手动计算或改用透视表跨工作簿引用动态数组报错外部引用目录包含不支持的变化打开源文件后刷新将数据复制到当前工作簿再处理接口数据导入后出现数组格式混乱原表包含合并单元格或空行先清理数据源用 Power Query 或 Python 脚本预处理11. 最佳实践与使用建议到这里你已经了解了 Excel 数组的核心概念、输入方式、实战场景和常见错误。最后给出一套实用建议避免在实际工作中踩坑。第一先把 Microsoft 365 的动态数组函数用熟再回头补旧版数组公式。动态数组的思维更符合自然习惯学起来不别扭。FILTER、UNIQUE、SORT 三个函数能覆盖大多数日常需求。第二遇到复杂统计不要只盯一个公式。先用辅助列把逻辑做通再压缩成数组公式。辅助列不是什么丢人的事情它反而是很好的调试工具。等公式稳定了再考虑是否去掉辅助列。第三数组公式里的条件判断尽量用*连接条件和连接“或”逻辑。多个条件相乘相当于“与”相加相当于“或”。如果条件本身是大于、小于等比较运算记得给每个比较逻辑加括号避免优先级错误。第四动态数组输出区域尽量放在空白区避免#SPILL!。如果要固定模板可以给结果区域预留足够空间。数据源变化导致结果行数增多时预留空间不足也会引发问题。第五接口数据传输和 Excel 数据处理的思维是一致的。你把 Excel 区域理解成二维数组之后看 Python 的 pandas、Java 的 POI、JavaScript 的 SheetJS思路都会更清晰。很多时候业务同事和开发之间的沟通障碍不是技术问题而是数据库表格和 Excel 表格的“行列视角”没有对齐。第六涉及重要数据时先用副本测试。数组公式特别是多条件、嵌套动态数组公式排错成本比普通公式高。在正式业务表里写复杂公式之前复制一份到测试文件里跑通确认数据结果无误后再移植。12. 总结与下一步回到开头的那个问题看到 Excel 数组就想划走现在你知道了数组公式并不比普通公式高级到哪里去它只是“用一组数据去计算另一组数据”。你需要记住的关键点只有三个一是区域本身可以被当作数组变量二是旧版输入要按 CtrlShiftEnter新版直接按 Enter三是 FILTER、UNIQUE、SORT 是学习动态数组最好的三个入门函数。下一步建议按这个顺序练习先跑一遍FILTER(A2:C100,B2:B100华东)确认动态数组的溢出效果。再写一个SUM(IF((区域1条件1)*(区域2条件2),求和区域,0))体验传统数组公式的逐元素计算。然后尝试把两个函数嵌套起来比如SORT(UNIQUE(FILTER(...)))把筛选、去重、排序一次完成。最后找一个工作里的真实报表尝试用数组公式替代辅助列对比公式数量和计算速度。数组这个知识点一旦跨过“区域即变量”这道坎后面再学 Excel 函数、VBA 宏、Python 处理表格都会顺畅很多。建议把这篇收藏起来下次遇到多条件求和、重复值提取、动态筛选时直接对照着改数据范围就能用。