Excel数组公式从入门到精通：高效数据计算技巧

引言 / 什么是数组公式

Excel数组公式能在单个单元格完成对多组数据的批量计算，特别适合处理复杂条件统计、矩阵运算等场景。传统数组公式需通过 Ctrl+Shift+Enter 触发，而Excel 365新增的动态数组公式可自动溢出结果，彻底解决了旧版公式需要预选区域的痛点。掌握该功能可显著提升百万级数据的处理效率。

准备工作

本文示例基于Excel 365（版本2026），动态数组功能需该版本及以上支持。传统数组公式兼容Excel 2007+，但部分函数如UNIQUE仅限动态数组环境使用。建议开启【公式】→【显示公式】功能辅助调试。

基础操作 / 核心用法

步骤一：理解数组公式特性

传统数组公式输入后会自动添加花括号{}，如计算A1:A10与B1:B10对应乘积和：

{=SUM(A1:A10*B1:B10)}

动态数组公式直接输入：

=SUM(A1:A10*B1:B10)

结果自动向下填充关联区域。

步骤二：用SUMPRODUCT替代SUMIFS

统计市场部且职级>3的员工数：

=SUMPRODUCT((A2:A100="市场部")*(B2:B100>3))

相比SUMIFS，该公式支持OR逻辑（如+连接不同条件）且无需绝对引用。

提示：数组运算时，TRUE/FALSE会自动转换为1/0参与计算。

进阶技巧

多维数据转置（TRANSPOSE）

将3行4列数据转为4行3列：

{=TRANSPOSE(A1:D3)}

动态数组环境可用：

=TRANSPOSE(A1:D3#)

#符号自动识别动态区域范围。

动态数组新函数组合

=UNIQUE(FILTER(A2:A100,B2:B100>3))  # 提取符合条件的唯一值
=SORT(A2:A100,,-1)                  # 降序排列

常见问题

Q：动态数组公式在旧版Excel打开会怎样？
A：自动转换为传统数组公式，可能产生#SPILL!错误，需手动调整区域引用。

Q：数组公式计算速度慢如何优化？
A：① 用FILTER替代多条件IF数组；② 避免整列引用（如A:A）；③ 开启迭代计算（文件→选项→公式）。

Q：SUMPRODUCT和SUMIFS有何区别？
A：SUMPRODUCT支持OR逻辑、数组运算，但计算速度略慢；SUMIFS条件设置更直观，但仅支持AND逻辑。

小结

数组公式通过批量数据运算替代循环计算，可解决常规公式难以处理的复杂场景。动态数组功能大幅降低了使用门槛，但需注意内存占用问题。建议优先尝试UNIQUE、FILTER等新函数组合，传统版本用户可使用INDEX+SMALL实现类似效果。实践时建议从简单案例入手，逐步掌握多维运算思维。