动态数组——新时代 Excel 的 AI 玩法

第14章：动态数组函数——Excel 365 的公式革命

2018年，微软悄悄在 Excel 365 中推出了动态数组引擎，彻底改变了公式的工作方式。以前一个公式只能输出一个结果，现在一个公式可以自动"溢出"填满整个区域。FILTER、SORT、UNIQUE、XLOOKUP、SEQUENCE……这些函数让过去需要复杂数组公式、高级筛选才能完成的事，变得简单到令人不敢相信。本章带你全面掌握这些新函数，并学会用 AI 快速把需求转化为公式。

动态数组革命：一个公式，多个结果

旧版 Excel vs Excel 365：公式范式的根本变化

在传统 Excel（2019及以前版本）里，一个公式只能产生一个结果，占据一个单元格。如果你想让公式输出多个值，必须：先选中要填充的整个区域，然后输入公式，最后按 Ctrl+Shift+Enter 以数组公式的方式提交——公式会被花括号 {} 包裹，表示它是数组公式。这种方式非常不直观，修改麻烦，普通用户几乎无法掌握。

Excel 365（包括 Excel 2021 和 Microsoft 365 订阅版）引入了动态数组引擎。现在，如果一个公式的计算结果是多个值，Excel 会自动将这些值"溢出"到相邻的空白单元格中——这就叫做溢出行为（Spill）。你不需要按任何特殊组合键，只需在一个单元格中输入公式，按 Enter，结果就自动填满对应区域。

旧版数组公式（需要 Ctrl+Shift+Enter）：
{=IF(B2:B10="销售部",C2:C10,"")}

Excel 365 动态数组（直接 Enter）：
=FILTER(C2:C10, B2:B10="销售部")

溢出区域和 # 号运算符

当公式产生溢出结果时，Excel 会用蓝色边框标记整个溢出区域，第一个单元格显示公式，其余单元格显示"幽灵"值（灰色显示，不可直接编辑）。

溢出区域有一个专属的引用方式：在公式单元格地址后面加 # 号。例如，如果 A1 中有一个产生溢出的公式，你可以用 A1# 来引用整个溢出区域。这非常有用，因为溢出区域的大小是动态变化的，A1# 始终指向当前全部溢出结果，不管它有多少行。

ℹ️ Note

**溢出错误 #SPILL!：**如果溢出区域中有其他内容（哪怕是一个空格），公式会显示 #SPILL! 错误。解决方法：清空溢出区域内的所有内容。

版本兼容性说明

本章所有函数（FILTER、SORT、SORTBY、UNIQUE、XLOOKUP、SEQUENCE、RANDARRAY、LET、LAMBDA）都是 Excel 365 专属功能。

⚠️ Warning

**如果你需要把文件分享给使用旧版 Excel 的同事：**动态数组函数的结果会变为静态值，公式会报错。建议在分享前将结果"粘贴为值"，或者为旧版用户提供备用方案。

FILTER——按条件提取数据

FILTER 是动态数组函数中最实用的一个，它彻底替代了传统的"高级筛选"和复杂的 IF+数组公式组合。核心思路是：我告诉你筛选哪个区域，满足什么条件，结果自动出现在指定位置。

语法

=FILTER(数组, 包含条件, [空值时返回])

参数说明：
• 数组      —— 要筛选的数据区域（可以是多列）
• 包含条件  —— 返回 TRUE/FALSE 的逻辑表达式（与数组行数相同）
• [空值时返回] —— 可选，当没有结果时显示什么（默认显示 #CALC! 错误）

案例1：提取某部门所有员工（单条件筛选）

假设 A 列是姓名，B 列是部门，C 列是薪资。现在要提取所有"销售部"员工的完整信息。

=FILTER(A2:C100, B2:B100="销售部", "无匹配结果")

说明：
• A2:C100 是要返回的数据（三列都返回）
• B2:B100="销售部" 是筛选条件，对每一行判断 B 列是否等于"销售部"
• "无匹配结果" 是当没有销售部员工时显示的文字

结果会自动溢出，显示所有销售部员工的姓名、部门、薪资。数据更新时，公式结果也自动更新——不需要重新筛选。

案例2：提取销售额大于10万的记录（数值条件）

假设 A 列是日期，B 列是销售员，C 列是销售额。提取销售额超过 100000 的所有记录：

=FILTER(A2:C100, C2:C100>100000, "暂无高额销售记录")

进阶用法——提取某个销售员本月销售额大于5万的订单：

=FILTER(A2:C100, (B2:B100="张三") * (C2:C100>50000))

说明：两个条件相乘（*）表示 AND 逻辑，两者都为 TRUE 才筛选出来

案例3：多条件 FILTER（AND / OR 组合）

在 FILTER 的条件参数中，用 ***** 连接多个条件表示 AND（同时满足），用 + 连接多个条件表示 OR（满足任意一个）。

/* AND 条件：部门是"市场部" 且 薪资大于 8000 */
=FILTER(A2:D100, (B2:B100="市场部") * (C2:C100>8000))

/* OR 条件：部门是"销售部" 或 部门是"市场部" */
=FILTER(A2:D100, (B2:B100="销售部") + (B2:B100="市场部"))

/* 复合条件：(销售部 或 市场部) 且 薪资 > 10000 */
=FILTER(A2:D100, ((B2:B100="销售部") + (B2:B100="市场部")) * (C2:C100>10000))

案例4：FILTER + SORT 组合（筛选后自动排序）

FILTER 的结果可以直接作为 SORT 的输入，实现"筛选并排序"的复合效果：

/* 筛选销售部员工，按薪资从高到低排序 */
=SORT(FILTER(A2:C100, B2:B100="销售部"), 3, -1)

说明：
• FILTER(A2:C100, B2:B100="销售部") 先筛选出销售部所有行
• SORT(..., 3, -1) 对结果按第3列（薪资）降序排列

案例5：AI 帮你把高级筛选需求转为 FILTER 公式

让 AI 直接生成 FILTER 公式 你有一张订单表，字段复杂，条件也复杂，不知道怎么写 FILTER 公式。

我有一张 Excel 订单表，数据在 A1:G500，列结构如下：
A列=订单编号, B列=下单日期, C列=客户名称, D列=产品类型, E列=数量, F列=单价, G列=销售员

我需要筛选出满足以下所有条件的订单：
1. 下单日期在 2024年1月1日 到 2024年3月31日之间
2. 产品类型是"A型"或"B型"
3. 单价大于500
4. 销售员是"李明"

请给我写一个 Excel 365 的 FILTER 公式，放在 I2 单元格，返回所有匹配行的完整数据。如果没有匹配结果，显示"无记录"。

SORT 和 SORTBY——动态排序

传统 Excel 的"排序"功能会直接改变原始数据的顺序，而且需要手动重新排序。SORT 和 SORTBY 函数会在另一个位置输出排序后的结果，原始数据不变，且结果随数据更新自动刷新。

SORT 语法

=SORT(数组, [排序依据列序号], [排序方向], [按列排序])

• 排序方向：1 = 升序（默认），-1 = 降序
• 按列排序：FALSE = 按行排序（默认），TRUE = 按列排序

SORTBY 语法

=SORTBY(数组, 排序依据1, 排序方向1, [排序依据2, 排序方向2], ...)

SORTBY 更灵活，排序依据可以是不在结果区域内的列

案例1：按销售额从高到低动态排序

/* 数据在 A2:C100，C列是销售额，按销售额降序 */
=SORT(A2:C100, 3, -1)

/* 只返回前10名（组合 TAKE 函数，Excel 365 新增）*/
=TAKE(SORT(A2:C100, 3, -1), 10)

案例2：SORTBY 按多列排序

先按部门升序，同部门内再按薪资降序——这是 SORT 做不到的，但 SORTBY 可以：

=SORTBY(A2:D100, B2:B100, 1, C2:C100, -1)

说明：
• 先按 B 列（部门）升序排列
• 相同部门内，再按 C 列（薪资）降序排列
• 返回 A:D 四列的完整数据

案例3：随机打乱顺序（SORTBY + RANDARRAY）

这是一个有趣的技巧：用 RANDARRAY 生成一列随机数作为排序依据，配合 SORTBY 就能随机打乱数据顺序。

=SORTBY(A2:C50, RANDARRAY(49))

说明：
• RANDARRAY(49) 生成49个随机数（对应49行数据）
• SORTBY 按这些随机数排序，效果等于随机打乱

应用场景：随机分配任务、随机排班、抽奖时随机排序参与者名单
注意：每次工作表计算时，随机数会刷新，顺序会变化。
如需固定结果，复制后粘贴为值。

案例4：排名榜单自动更新

做销售排行榜时，希望数据更新后排名自动变化：

/* 在空白区域放置自动排名榜单 */
=SORT(A2:C20, 3, -1)

/* 只显示前5名销售员 */
=TAKE(SORT(A2:C20, 3, -1), 5)

/* 自动添加排名序号（配合 SEQUENCE） */
=LET(
  sorted, SORT(A2:C20, 3, -1),
  rank,   SEQUENCE(ROWS(sorted)),
  HSTACK(rank, sorted)
)

UNIQUE——动态去除重复值

以前要提取不重复的列表，需要用"删除重复项"功能（会修改原数据），或者用复杂的数组公式。UNIQUE 函数让这件事变得极其简单：一个公式，动态输出去重后的唯一值列表。

语法

=UNIQUE(数组, [按列去重], [只出现一次])

• 按列去重：FALSE = 按行去重（默认），TRUE = 按列去重
• 只出现一次：FALSE = 返回所有唯一值（默认），TRUE = 只返回在原数据中只出现一次的值

案例1：提取不重复的产品列表

/* A列是产品名称（含重复），提取唯一产品列表 */
=UNIQUE(A2:A500)

/* 提取唯一产品列表并排序（更实用） */
=SORT(UNIQUE(A2:A500))

案例2：UNIQUE + COUNTA 统计不重复数量

在 UNIQUE 外面套一个 COUNTA，就可以统计有多少个不重复的值——相当于不重复计数：

/* 统计有多少个不同的客户 */
=COUNTA(UNIQUE(B2:B500))

/* 统计有多少个不同的产品类型（排除空值） */
=COUNTA(UNIQUE(FILTER(D2:D500, D2:D500<>"")))

案例3：多列组合去重

有时候需要按多列组合去重，例如"同一个客户的同一个产品"只保留一条：

/* 对 B列（客户）和 D列（产品）的组合去重 */
=UNIQUE(CHOOSE({1,2}, B2:B500, D2:D500))

/* 更简洁的写法（直接选取两列） */
=UNIQUE(B2:C500)
注意：这会对两列的整体组合去重，两列值完全相同才算重复

XLOOKUP——新一代查找函数

XLOOKUP 是 Excel 365 中对 VLOOKUP 的全面升级替代品。如果你在用 Excel 365，以后写查找公式直接用 XLOOKUP，不需要再用 VLOOKUP 了。

XLOOKUP vs VLOOKUP 全面对比

XLOOKUP 语法

=XLOOKUP(查找值, 查找区域, 返回区域, [找不到时], [匹配模式], [搜索模式])

• 匹配模式：0=精确匹配（默认）, -1=小于等于, 1=大于等于, 2=通配符
• 搜索模式：1=从第一个开始（默认）, -1=从最后一个开始

案例1：双向查找（行列同时匹配）

在一张矩阵表中，同时按行标题和列标题查找交叉值：

/* 矩阵表：A1是空，B1:E1是季度标题，A2:A10是产品名称，B2:E10是销售数据 */
/* 查找"产品B"在"Q3"的销售额 */
=XLOOKUP("产品B", A2:A10, XLOOKUP("Q3", B1:E1, B2:E10))

说明：
• 内层 XLOOKUP("Q3", B1:E1, B2:E10) 找到 Q3 对应的那一列数据
• 外层 XLOOKUP("产品B", A2:A10, ...) 在那列数据中找产品B的行

案例2：反向查找（查找值在返回列的右边）

/* 传统 VLOOKUP 无法向左查找，XLOOKUP 可以 */
/* 已知工号（D列），查对应的姓名（B列，在工号左边） */
=XLOOKUP(G2, D2:D100, B2:B100)

G2 = 要查的工号
D2:D100 = 查找区域（工号列）
B2:B100 = 返回区域（姓名列，在左边也没关系）

案例3：找不到时返回指定内容

/* 找不到时显示"新客户"而不是错误 */
=XLOOKUP(A2, 客户数据库!A:A, 客户数据库!B:B, "新客户")

/* 找不到时显示空白（避免 #N/A 影响其他计算） */
=XLOOKUP(A2, 客户数据库!A:A, 客户数据库!B:B, "")

案例4：返回多列结果

/* VLOOKUP 每次只能返回一列，需要写多个公式 */
/* XLOOKUP 一次返回多列——公式放在 H2，结果溢出到 H2:J2 */
=XLOOKUP(G2, A2:A100, B2:D100)

G2 = 查找的员工工号
A2:A100 = 查找区域
B2:D100 = 返回区域（姓名、部门、薪资三列同时返回）

SEQUENCE——生成数字序列

SEQUENCE 可以自动生成一个数字序列——等差数列、日期序列、编号序列……这在创建日历、生成测试数据、自动编号时极为有用。

语法

=SEQUENCE(行数, [列数], [起始值], [步长])

• 行数：生成多少行
• 列数：生成多少列（默认1）
• 起始值：从哪个数开始（默认1）
• 步长：每步增加多少（默认1，可以是负数或小数）

案例1：生成日历月份格

/* 生成2024年1月的日历（6行7列，从1月1日星期一开始） */
/* 先在 A1 放公式，结果自动填充6×7的区域 */
=SEQUENCE(6, 7, DATE(2024,1,1), 1)
然后把单元格格式设为 "d"，只显示日期中的"日"部分

/* 生成本月所有工作日的日期序列 */
=WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 0),
  SEQUENCE(NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),
           EOMONTH(TODAY(),0))))

案例2：自动生成编号

/* 生成 001, 002, 003... 格式的编号（配合 TEXT） */
=TEXT(SEQUENCE(100), "000")

/* 生成带前缀的编号：ORD-001, ORD-002... */
="ORD-" & TEXT(SEQUENCE(100), "000")

/* 自动编号（随数据行数变化，配合 COUNTA） */
=SEQUENCE(COUNTA(A2:A1000))

案例3：生成测试数据

/* 快速生成100个随机数（用于测试） */
=RANDARRAY(100, 1, 1, 1000, TRUE)
说明：生成100行1列，范围1-1000的随机整数

/* 生成5×3的随机数矩阵，范围0-100 */
=RANDARRAY(5, 3, 0, 100)

/* 配合 SEQUENCE 生成连续日期 */
=SEQUENCE(30, 1, TODAY(), 1)
再设置日期格式即可得到未来30天的日期列表

动态数组函数速查表

✅ Tip

本章核心要点：

动态数组是 Excel 365 最重要的升级之一。FILTER 替代高级筛选，SORT/SORTBY 替代手动排序，UNIQUE 替代删除重复项，XLOOKUP 替代 VLOOKUP。这些函数的结果都是动态的——数据源更新，结果自动更新。配合 AI，你可以把任何复杂的筛选、查找需求直接转化为公式，无需手动推导语法。

上一章 ← 第13章：图表与数据可视化 下一章 第15章：VBA 入门——让 Excel 帮你干重复的事 →