看到Excel里“数组”两个字就想划走？很多人第一反应是“这是编程才有的东西，跟我没关系”。这个认知其实会错过Excel里效率最高的一类操作：数组公式和动态数组。本质上，数组就是“一批数据放在一起”，而Excel数组公式就是让你用一条公式同时处理这一批数据。以前它需要用Ctrl+Shift+Enter输入，显示成花括号，很多教程又讲得抽象，所以劝退了不少人。现在Office 365和Excel 2021已经支持动态数组，大部分情况直接回车就能出结果，门槛已经大幅降低。
这篇文章不打算堆概念，直接从三个问题切入：数组是什么、能解决什么问题、实战中怎么用。我先给一张能力速览表，再讲一维二维数组的底层逻辑，接着演示动态数组和传统数组公式的真实案例，最后补充VBA和Python批量处理Excel的扩展思路，以及性能优化和常见报错排查。
谁适合读？天天用Excel做统计、做报表，被“多条件筛选”“两列相乘求和”“一列数据合并成一行”这类需求折磨的人；给旧版本Excel维护模板，还在用CSE数组公式的人；以及想用Python快速批量处理Excel表格的数据工作者。
1. Excel数组核心能力速览
| 能力项 | 说明 |
|---|---|
| 核心思想 | 一条公式同时处理一批数据，而不是一格一格复制函数 |
| 一维数组 | 按一行或一列排列的数据集合，例如{1,2,3} |
| 二维数组 | 多行多列的数据块，例如{1,2;3,4} |
| 旧版输入方式 | 选中公式后按Ctrl+Shift+Enter，公式两端会出现花括号{} |
| 新版动态数组 | Excel 365 / Excel 2021 直接回车，结果自动溢出到相邻单元格 |
| 高频数组函数 | FILTER、UNIQUE、SORT、SEQUENCE、TRANSPOSE、TEXTJOIN |
| 典型场景 | 多条件筛选、去重、排序、二维转置、批量计算、数据拆分 |
| 性能注意 | 整列引用和大量嵌套数组公式会导致计算变慢，需按实际数据量评估 |
| 学习门槛 | 比普通函数高一档，但掌握F9调试和动态数组后很容易上手 |
| 适合人群 | 日常做数据处理、报表、财务统计、运营分析的用户 |
这张表看完，先记住两个关键点：旧版Excel中数组公式要按三键确认，新版Excel中动态数组直接回车；如果你用的是Excel 2016或更早版本，后面的FILTER、UNIQUE、SORT用不了，但基础数组公式仍然有效。
2. 适用场景与使用边界
数组公式和动态数组最适合解决下面几类问题：
- 多条件汇总。例如求“华东区、A类产品、2024年”的销售额合计，虽然可以用
SUMIFS，但数组写法能让你更直观地理解条件匹配原理。 - 批量计算后再汇总。例如先计算每个商品的“单价×数量”，再一次性求和，传统做法需要加辅助列，数组公式可以一条公式完成。
- 筛选、去重、排序。新版Excel里
FILTER能按条件抽取数据，UNIQUE能一键去重，SORT能动态排序，这些函数的返回值本身就是数组。 - 数据重构。一列数据变成一行，或者一行数据变成一列，用
TRANSPOSE就能完成。 - 序列生成。用
SEQUENCE自动生成连续的日期、序号或随机数。
边界也要说清楚。数组公式不适合这几类场景：
- 超大表格。在10万行数据上写整列引用或大量数组公式，Excel会明显卡顿，这种情况建议改用Power Query、Access或Python处理。
- 需要逐格修改结果的场景。动态数组的结果是整体溢出，你无法单独修改结果区域里的某一个单元格，这是设计如此，不是故障。
- 旧版本兼容要求严格的工作环境。如果你的文件要发给只用Excel 2013/2016的同事，动态数组函数落地时会被提示
#NAME?，需要改写或转为静态值。
还需要注意数据合规。处理客户名单、员工工资、个人隐私数据时，先做脱敏再演示和分享；用Python批量读取Excel时，不要把含敏感信息的文件随意提交到第三方在线服务。
3. Excel数组环境准备
数组公式不需要安装任何额外组件，它就在Excel里。你要确认的其实是版本是否支持动态数组。
- Excel 365（Microsoft 365）：完整支持动态数组，推荐。
- Excel 2021：完整支持动态数组。
- Excel 2019：部分支持，动态数组不一定完整。
- Excel 2016及更早：不支持动态数组，传统数组公式用
Ctrl+Shift+Enter输入。 - WPS Office：新版部分支持动态数组，具体以你当前安装版本为准，老版本可能只支持传统数组公式。
查看版本的方式：打开Excel，点击“文件”->“账户”，在“关于Excel”中可以看到版本号；也可以直接测试=UNIQUE(A1:A5)，如果能正常返回去重结果，说明支持动态数组。
文件准备方面，建议先在空白工作簿里练习。准备一个销售明细表，至少包含“区域、产品、数量、单价”四列，10到50行数据即可。用这个小表把后面的案例全部跑通，再套用到你的真实表格。
4. 数组的本质：一维数组与二维数组
数组在Excel里就是一组数据的集合。用花括号直接输入时，逗号代表列分隔符，分号代表行分隔符。
{1,2,3} {1,2;3,4}第一个是1行3列的一维横向数组，第二个是2行2列的二维数组。你可以把它想象成Excel里的一个小单元格范围：A1:C1对应{1,2,3}，A1:B2对应{1,2;3,4}。
如何在单元格里看数组具体内容？选中公式编辑栏中的区域，按F9，Excel会显示计算后的数组。这是排查数组公式最重要的调试手段。例如在单元格输入：
=A1:A5按F9后可以看到={值1;值2;值3;值4;值5}这样的结果。注意按完F9要按Esc退出，不要直接回车，否则会把公式替换成显示出来的数组。
传统数组公式的核心规则是：如果你对区域数组进行运算，必须注意返回方向与区域大小一致。例如：
=SUM(A1:A10*B1:B10)这个公式的含义是：先逐个计算A1*B1、A2*B2直到A10*B10，得到一个10个元素的数组，然后用SUM把这些乘积加起来。在旧版Excel中，需要选中公式单元格后按Ctrl+Shift+Enter确认，公式两端会出现花括号；如果你只按回车，很有可能只返回A1*B1对应的结果，也就是数组的第一个元素。
二维数组的典型操作是转置。TRANSPOSE函数可以把一个区域的行列互换。使用方法如下：
=TRANSPOSE(A1:C3)在旧版中，需要先选中一个3行3列的目标区域，输入公式后按Ctrl+Shift+Enter；新版中直接回车，结果会溢出到对应区域。执行之后，原来A1:C3这个3行3列区域，会被转置成3行3列的新区域，两边的行数与列数正好互换。看到这个结果，你就能直观理解二维数组的行列关系了。
5. 动态数组：溢出与三个高频函数
新版Excel最大的变化是动态数组。动态数组公式输入时不需要三键，直接在目标单元格输入公式后回车，计算结果会自动“溢出”到右侧或下方的空白单元格。这个自动扩展的区域叫做溢出区域。
如果溢出区域被其他内容占据，Excel会返回#SPILL!错误。比如你在B1输入=A1:A5，但B2、B3、B4或B5中已有数据，溢出就会中断，Excel会提示“溢出区域非空”。
下面是最值得掌握的三组高频函数。
5.1 FILTER 多条件筛选
FILTER可以根据条件返回整个数组，适合替代高级筛选。基本语法：
=FILTER(数据区域, 条件区域=条件, 无结果时返回内容)要筛选“销售明细表”中“华东区”的所有记录：
=FILTER(A2:D101, B2:B101="华东区", "无数据")多个条件同时满足时，用乘号连接多个判断：
=FILTER(A2:D101, (B2:B101="华东区")*(C2:C101>100), "无数据")这里B2:B101="华东区"会生成一个TRUE/FALSE数组，C2:C101>100会生成另一个TRUE/FALSE数组，二者相乘后，只有两个条件都满足的位置才会得到1，其余为0，从而实现AND逻辑。FILTER返回的是动态数组，会自动列出所有符合条件的数据。
5.2 UNIQUE 数组去重
UNIQUE可以从列表中提取不重复值，替代“删除重复项”步骤。基本语法：
=UNIQUE(A2:A101)如果要返回每个值出现的次数，可以配合COUNTIF，例如返回两列：一列是不重复产品名，一列是出现次数：
=HSTACK(UNIQUE(A2:A101), COUNTIF(A2:A101, UNIQUE(A2:A101)))不过HSTACK仅在较新版本中可用，旧版本建议分两列写公式。UNIQUE对后续做透视表、下拉列表数据源非常有用，而且当源数据变化时，它会自动更新。
5.3 SORT 与 SEQUENCE
SORT可以对区域或数组排序。默认升序：
=SORT(A2:D101, 4, -1)其中第二个参数是排序依据的列号，第三个参数-1代表降序。若希望先按“区域”排序，再按“数量”排序，可以写成：
=SORT(A2:D101, {2,3}, {1,1})SEQUENCE用于生成连续序列。生成10行1列的序号：
=SEQUENCE(10)生成5行2列的序列：
=SEQUENCE(5, 2, 1, 1)SEQUENCE常与INDEX配合，生成指定区间的日期序列，例如从2024年1月1日开始连续生成30天：
=SEQUENCE(30, 1, DATE(2024,1,1), 1)不过这里返回的是日期序列值，显示时需要把单元格格式设为日期。
6. 数组公式实战案例
理论讲完，下面给几个可以直接套用的实战案例。建议在销售明细表上逐条验证。
6.1 一列数据用逗号合并成一行
需求：把A列的产品名合并到一个单元格里，逗号分隔。旧版没有TEXTJOIN时，要用复杂的数组公式；新版直接写：
=TEXTJOIN(",", TRUE, A2:A101)第二个参数TRUE表示忽略空值。TEXTJOIN会遍历A2:A101中的每个单元格，把它们拼成字符串。这个函数接受区域参数，本身并不要求按三键，但底层逻辑仍然是按数组遍历处理。如果有大量重复数据，需要先用UNIQUE去重再合并：
=TEXTJOIN(",", TRUE, UNIQUE(A2:A101))这样就能得到“苹果,香蕉,梨”这样的唯一值列表。
6.2 两列相乘后求和
需求：计算“数量×单价”的总销售额，但不加辅助列。这是最经典的数组公式案例。
=SUM(A2:A101*C2:C101)在旧版中按Ctrl+Shift+Enter，新版直接回车。它的计算过程是先得到“数量×单价”的中间数组，再做求和。
如果你担心整列引用导致卡顿，请把区域写成具体的范围，例如A2:A101而不是A:A。如果表格行数会经常变化，建议把源数据区插入为“表格”（Ctrl+T），然后用结构化引用。
6.3 按条件返回二维数组并转置展示
需求：把“区域”作为行、“产品类型”作为列，生成一个交叉统计表。虽然数据透视表是更专业的工具，但在不改动原表的前提下，用数组函数也能快速实现。先用UNIQUE提取不重复的区域和产品类型，再用SUMIFS配合动态数组生成统计矩阵。
例如E2单元格有区域列表，F1到H1有产品类型列表：
=SUMIFS(C:C, A:A, $E2, B:B, F$1)这是一个普通公式，配合动态数组区域后可以下拉填充。如果你确实想一步生成整个二维矩阵，使用MAKEARRAY需要较新版本，公式也更复杂，日常更推荐“辅助区域+SUMIFS”的组合，既直观又好排错。
6.4 找哪几个数相加等于目标值
这是一个高频需求：给一堆金额，想找出哪些数相加等于某个总数。要说明的是，这不是数组公式的主场，应该用“数据”选项卡里的“规划求解”。
操作思路是：给每个数值旁边加一列“是否使用”辅助列，然后在“规划求解”中设置目标单元格等于目标总数，通过改变辅助列单元格，并添加辅助列为二进制的约束条件来求解。规划求解无法保证在指数级组合里快速找到所有答案，但能帮你找到一组可行解。
如果你只是想判断两个数加起来是否等于目标值，可以用FILTER配合MATCH实现，但这个只适用于“两两配对”的场景。真正的组合求和，还是用规划求解更稳妥。
6.5 多维数组的Index取值
INDEX本身返回数组中的指定元素，但它也可以返回整个行或列。格式：
=INDEX(A2:D101, 0, 3)这个公式返回A2:D101区域第3列的所有行，实际上是返回一整列数组。在动态数组环境下，它会把所有内容溢出到下方单元格。通过INDEX加SEQUENCE，你还可以实现按指定顺序抽取数据行，比如把第1、5、10行抽出来重新组合。
7. VBA数组与Python pandas批量处理
如果Excel公式处理的数据量已经明显变慢，或者需要循环处理几十个工作簿，就可以进入“代码阶段”。这里不是让大家抛弃Excel公式，而是让数组这个概念升级到编程语言中。
7.1 VBA：把区域读入数组再写回
VBA中处理单元格最忌讳逐格读写。正确的做法是先把整个区域一次性读入数组，在内存中计算，最后一次性写回。
Sub BulkCalculate() Dim arr As Variant Dim i As Long arr = Range("A2:D101").Value For i = 1 To UBound(arr, 1) arr(i, 4) = arr(i, 2) * arr(i, 3) Next i Range("F2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr End Sub这个例子把“数量×单价”的结果写入D列。VBA数组从区域读取时，下标通常从1开始，比如arr(i, 4)表示第i行的第4列。批量处理时，先清空结果区域再写回，避免旧数据残留。
7.2 Python pandas：读Excel、数组计算、批量导出
Python的pandas对Excel数组类操作支持很成熟。需要安装pandas和openpyxl：
pip install pandas openpyxl读取Excel，批量计算并导出：
import pandas as pd df = pd.read_excel("data.xlsx", sheet_name="Sheet1") df["销售额"] = df["数量"] * df["单价"] # 按区域分组汇总，类似于追加一个透视表 summary = df.groupby("区域", as_index=False)["销售额"].sum() with pd.ExcelWriter("output.xlsx") as writer: df.to_excel(writer, sheet_name="明细", index=False) summary.to_excel(writer, sheet_name="汇总", index=False)pandas里最像Excel数组函数的是apply和向量化运算。例如对每一行做判断：
df["是否达标"] = df["销售额"].apply(lambda x: "是" if x > 10000 else "否")用Python处理Excel有几个注意点：
- 原文件备份：
to_excel会把整个工作表重写，容易覆盖格式。 - 大文件内存占用：读取超大Excel时会占用较多内存，建议先用
read_excel的usecols参数只读需要的列。 - 敏感数据：不要把含个人信息的数据上传到在线接口，批量脚本要在本地运行。
8. 性能观察：数组公式会不会卡
关于数组公式“卡不卡”，要分开看。
普通数组公式在几千行数据内通常感觉不到明显延迟，比如=SUM(A2:A1001*B2:B1001)一步算完，比加辅助列还快。但如果把公式写到整列，比如=SUM(A:A*B:B)，Excel需要处理上百万个单元格，即使很多是空值也会产生大量计算，文件会明显变慢。
动态数组同样要避免大范围溢出。=FILTER(A:A,B:B="华东区","")这种写法，会让公式在数百万行的区域上逐行判断，计算量和内存占用都很高。更合理的做法是给表格区域限定范围，或者使用Excel表格对象。
建议从这几个方向观察和优化：
- 在“公式”->“计算选项”里把工作簿设为手动计算，排查公式性能问题时可以逐次按F9触发计算。
- 使用
LET函数给中间结果命名，减少重复计算。例如：
=LET(区域, A2:A101, 条件, B2:B101, SUM((条件="华东区")*区域))LET在Excel 365和Excel 2021中可用。它可以避免同一个区域被重复引用、重复计算。
- 用“表格”对象替代普通区域。选中数据后按
Ctrl+T创建Excel表格，公式里的引用会变成表1[数量]这种结构化引用。它只统计实际有数据的行，不会扩展到整列，性能更好。 - 如果计算越来越慢，用“删除重复项”或
UNIQUE生成的缓存值替代原区域，也是一个思路。 - 在任务管理器里观察Excel进程的内存占用，如果持续走高，优先检查是否有整列引用或大量动态数组同时刷新。
9. 常见问题与排查方法
下面是Excel数组使用中最常见的几个报错和排查思路。
| 问题现象 | 可能原因 | 排查方式 | 解决方案 |
|---|---|---|---|
| 按下公式只显示第一个值 | 旧版Excel数组公式没有按三键，或公式本身返回多值但放在单个单元格 | 检查公式两端是否有花括号；确认Excel版本 | 选中公式单元格后按Ctrl+Shift+Enter，或换用支持动态数组的Excel版本 |
出现#SPILL! | 动态数组溢出区域被其他内容占用 | 查看Excel提示，定位阻塞单元格 | 清空阻挡的单元格，或把公式移动到空白区域 |
出现#VALUE! | 两个数组区域维度不一致，无法计算 | 用F9检查每个区域的形状和大小 | 调整区域范围，保证相乘或者比较的数组行数一致 |
出现#NAME? | 公式中函数在当前版本中不存在，例如旧版用FILTER | 检查Excel产品版本 | 改用IF数组公式、辅助列，或升级到支持动态数组的版本 |
| 公式结果不自动扩展 | 当前版本不支持动态数组，或公式被放在合并单元格内 | 检查是否合并单元格；确认版本 | 取消合并单元格；使用旧版三键输入 |
UNIQUE/SORT返回为空 | 原区域为空或引用范围不对 | 检查数据区域是否有内容；用F9查看 | 调整数据区域，或确认数据前后没有多余空格 |
| 批量处理时文件越来越大 | 动态数组溢出到了大量空白行或整列引用过多 | 观察公式引用范围，查看溢出区域大小 | 收敛区域，使用表格结构化引用，必要时将结果粘贴为静态值 |
| 发送给同事后公式全部变成错误值 | 接收方Excel版本不兼容 | 确认对方版本 | 另存为兼容格式，或把公式结果复制成值再发送 |
排错时，最常用的是F9。选中公式中某一段，按F9，就能看到这一段计算出的数组长什么样。看完按Esc退出，不要直接回车。这个动作能解决90%的数组公式调试问题。
10. 最佳实践与使用建议
数组公式最有价值的用法，是让计算过程保持“动态”和“可维护”。从工程化使用的角度，建议遵守下面几条原则。
第一，先小范围验证。不要在十万行的真实数据上直接测试新公式。先在空白工作表模拟20行数据，确认结果符合预期，再替换成真实区域。这样可以避免公式写错后Excel长时间无响应。
第二，把公式区域命名或者结构化。直接写=SUM(明细[数量]*明细[单价])比写=SUM(Sheet1!A2:A9999*Sheet1!B2:B9999)更好读，也更容易维护。给区域命名后，数组公式的适用范围更清晰。
第三，尽量少用整列引用。A:A这种写法虽然方便，但会让Excel计算大量空行。尤其在数组公式里，整列引用的代价会成倍放大。要么用具体区域，要么用Excel表格。
第四，动态数组结果不要手工修改。溢出区域是锁定的，如果试图修改其中某个单元格，会提示“不能更改数组的一部分”。遇到这种情况不要奇怪，这是新版Excel的规则。想固定结果就复制后粘贴成值。
第五，复杂公式能做中间列就做中间列。为了单纯炫技，把10层数组嵌套写成一个公式，会让排查成本变得很高。数组公式的优势是减少辅助列，但并不是说辅助列完全不能用。在可读性和性能之间做平衡，才是工程化使用的方法。
第六，发布或发给别人之前，把需要保护的计算逻辑另存为一份“公式版”，把要对外输出的版本另存为“值版”。这样既保留了可追溯性，也避免别人不小心破坏公式结构。
11. 总结与下一步
Excel数组的核心价值，是把“一格一格处理”升级成“整批处理”。旧版数组公式用Ctrl+Shift+Enter输入，花括号吓退了一大批人；新版动态数组直接回车，FILTER、UNIQUE、SORT、SEQUENCE让数组公式变得非常容易上手。这篇内容不是让你背语法，而是让你建立“数组就是一批值”这个底层概念，然后按实际需求去套公式。
最先要验证的三个功能建议是：用SUM(A2:A10*B2:B10)理解传统数组公式的批处理原理；用FILTER写一个多条件筛选；用UNIQUE生成去重列表。这三个功能分别对应数组计算、条件筛选、去重，覆盖了大多数日常场景。
最容易踩的坑集中在三处：用了旧版Excel却硬写动态数组函数；写了整列引用导致卡顿；动态数组遇到合并单元格或已有数据后报#SPILL!。把这三个坑提前避开，数组公式用起来会顺畅很多。
后续扩展方向也比较明确：如果你想继续往“自动化处理Excel”走，学VBA数组和Power Query；如果想处理更大数据量，直接用Python pandas做批量读取、清洗和输出；如果只是日常分析，可以继续研究LET、XLOOKUP与动态数组的组合用法。建议把这篇文章收藏起来，下次遇到多条件筛选、去重、合并单元格数据时，直接翻出来抄公式。
