引言 #
在数据处理与分析领域，效率与智能化是永恒的追求。随着WPS Office的持续迭代，其表格组件（WPS表格）的功能已日益强大，尤其在函数与公式方面，不断引入先进特性以对标国际一流水平。其中，动态数组公式及相关新函数（如 FILTER、SORT、UNIQUE、SEQUENCE、XLOOKUP 等）的加入，彻底改变了传统公式的构建逻辑与数据输出模式。这些功能允许单个公式返回一个可自动扩展或收缩的结果区域，实现数据的动态溢出，从而让复杂的数据提取、排序、去重、序列生成等操作变得前所未有的简洁和高效。本文将深入剖析这些核心新功能的原理，并通过大量贴近实际办公场景的案例，手把手带您掌握其应用精髓，助您将WPS表格的数据处理能力提升至全新高度。
一、 动态数组公式：革命性的计算范式 #
1.1 什么是动态数组公式？ #
传统公式通常在一个单元格中返回一个结果。如果您需要处理一个数据区域，往往需要借助数组公式（按 Ctrl+Shift+Enter 输入），并将公式拖动填充至整个区域。这种方式不仅操作繁琐，而且在源数据变化时，结果区域的大小不会自动调整。
动态数组公式则打破了这一限制。只需在单个单元格中输入一个公式，该公式就能根据计算结果，自动“溢出”（Spill）到相邻的空白单元格中，形成一个动态的结果数组。这个结果区域的大小完全由公式逻辑和源数据决定，并会随源数据的增减而动态调整。
1.2 动态数组的核心优势 #
- 简化公式结构：无需再记忆复杂的
Ctrl+Shift+Enter三键输入，直接按 Enter 即可。一个公式解决一片区域的问题。 - 自动适应与更新：结果区域自动扩展或收缩，无需手动调整公式范围。当源数据增删时，结果即时、自动更新。
- 提升可读性与维护性：逻辑集中在一个单元格内，更容易理解和修改。公式栏中清晰显示为“溢出”范围。
- 支持“#”运算符引用：可以方便地引用整个动态数组结果，例如
A2#表示引用从A2单元格开始的整个溢出区域，这为构建更复杂的动态模型奠定了基础。
1.3 启用与兼容性说明 #
动态数组功能是WPS表格较新版本引入的特性。请确保您使用的是WPS Office 2019 专业增强版（版本号11929或更高）或WPS Office 2024及以后版本。您可以在“文件”->“帮助”->“关于WPS表格”中查看版本信息。
二、 核心新函数详解与实战案例 #
WPS表格为支持动态数组，引入了一系列强大的新函数。它们是构建动态报表的基石。
2.1 FILTER 函数：智能数据筛选器 #
FILTER 函数用于基于指定条件筛选出一个范围或数组中的数据。
- 语法：
=FILTER(要筛选的数组, 条件数组, [如果为空时返回的值]) - 参数解析：
数组：希望从中筛选数据的源区域。条件：一个布尔值（TRUE/FALSE）数组，其高度或宽度必须与“数组”参数相匹配。只有对应位置条件为 TRUE 的行（或列）会被返回。[如果为空]：可选。当所有条件都不满足时返回的值。省略则返回#CALC!错误。
实战案例1：多条件动态查询销售记录
假设您有一个销售数据表（A1:E100），包含“日期”、“销售员”、“产品”、“数量”、“金额”。您希望动态提取出“销售员”为“张三”且“产品”为“笔记本”的所有记录。
- 在空白区域（如G1单元格）输入公式：
=FILTER(A2:E100, (B2:B100="张三") * (C2:C100="笔记本"))A2:E100是源数据区域。(B2:B100="张三")生成一个TRUE/FALSE数组，标记“销售员”列为“张三”的行。(C2:C100="笔记本")生成另一个TRUE/FALSE数组，标记“产品”列为“笔记本”的行。- 两个条件数组相乘（
*），在数组运算中相当于逻辑“与”（AND），只有同时为TRUE的行，结果才为TRUE。
- 按下 Enter 键，公式会自动溢出，将符合条件的所有行完整地显示在G列及右侧的列中。当源数据增加新记录时，结果区域会自动包含新记录。
进阶技巧：您可以将条件单元格（如“销售员”和“产品”的查询值）放在单独的单元格（如 J1 和 J2），公式修改为 =FILTER(A2:E100, (B2:B100=J1) * (C2:C100=J2))，即可实现一个交互式的动态查询报表。
2.2 SORT 与 SORTBY 函数：灵活的数据排序 #
SORT 函数按指定列对范围或数组进行排序。SORTBY 则更为灵活，可以按另一个数组（不一定在源数据内）进行排序。
- SORT语法：
=SORT(数组, [排序依据索引], [排序顺序], [按列排序]) - SORTBY语法：
=SORTBY(数组, 排序依据数组1, [排序顺序1], ...)
实战案例2：生成按金额降序排列的销售Top N列表
继续使用销售数据。我们希望动态生成一个按“金额”从高到低排列的列表，并且只显示前10名。
- 首先，用
SORT对整个表排序：=SORT(A2:E100, 5, -1)A2:E100是源数据。5表示按第5列（“金额”）排序。-1表示降序排列（1为升序）。
- 要取前10名，可以结合
FILTER和SEQUENCE（见下文），或者更简单地，使用INDEX函数（虽然传统，但有效）。更优雅的动态方式是使用CHOOSEROWS（如果版本支持）或直接引用溢出区域的前10行。一个通用方法是：=TAKE(SORT(A2:E100, 5, -1), 10)TAKE函数可以返回数组的开头或结尾指定数量的行或列。这里取排序后数组的前10行。
实战案例3：SORTBY应用 - 按自定义顺序排序
假设您希望产品按“台式机”、“笔记本”、“平板”、“手机”这个特定顺序排序，而非字母顺序。
- 在一个辅助区域（如H1:H4）按顺序列出产品类别。
- 使用公式：
=SORTBY(FILTER(A2:E100, C2:C100<>""), MATCH(C2:C100, $H$1:$H$4, 0), 1)FILTER(...)先确保我们处理的是有产品记录的数据。MATCH(C2:C100, $H$1:$H$4, 0)为每个产品在自定义列表（$H$1:$H$4）中找到其位置序号，生成一个序号数组。SORTBY依据这个序号数组进行升序（1）排列，从而实现自定义顺序排序。
2.3 UNIQUE 函数：快速提取唯一值 #
UNIQUE 函数用于返回范围或数组中的唯一值列表，去除重复项。
- 语法：
=UNIQUE(数组, [按列/行], [仅出现一次]) - 参数解析：
[按列/行]：FALSE 或省略表示按行提取唯一值（默认）；TRUE 表示按列提取。[仅出现一次]：FALSE 或省略表示返回所有出现过的唯一值（包括重复出现的）；TRUE 表示仅返回只出现一次的值。
实战案例4：动态生成不重复的产品列表与销售员列表
这是数据透视表中“字段列表”的公式版实现，且能动态更新。
- 提取不重复产品列表：
输入公式后，WPS表格会自动列出所有出现过的产品名称。=UNIQUE(C2:C100) - 提取不重复销售员列表：
=UNIQUE(B2:B100) - 这两个列表可以作为数据验证（下拉列表）的来源，或者用于构建动态的汇总分析。
结合应用：统计每个销售员负责的不重复产品数量。
=COUNTIFS(C2:C100, UNIQUE(C2:C100), B2:B100, “特定销售员”)
但更高效的方式是结合 FILTER 和 UNIQUE：
=COUNTA(UNIQUE(FILTER(C2:C100, B2:B100=“特定销售员”)))
2.4 SEQUENCE 函数：生成数字序列的利器 #
SEQUENCE 函数生成一个指定行列数的数字序列数组。
- 语法：
=SEQUENCE(行数, [列数], [起始值], [步长]) - 参数解析：所有参数都非常直观，用于控制序列的维度、起点和增量。
实战案例5：动态生成日期序列或序号列
- 生成2024年1月的日期列：
=SEQUENCE(31, 1, DATE(2024,1,1), 1)- 生成31行、1列，从
2024/1/1开始，步长为1（天）的日期序列。
- 生成31行、1列，从
- 为动态筛选结果自动添加序号：
假设
G2#是FILTER函数返回的动态数组区域（表头在G1）。- 在
F2单元格输入公式：=SEQUENCE(ROWS(G2#)) - 该公式会生成一个与
FILTER结果行数相同的自然数序列（1,2,3…），作为序号列。当FILTER结果行数变化时，序号列长度自动同步变化。
- 在
2.5 XLOOKUP 函数：VLOOKUP/HLOOKUP的终极升级 #
虽然 XLOOKUP 本身不一定返回动态数组，但它与动态数组环境完美契合，是查找引用函数的革命。
- 语法：
=XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式], [搜索模式]) - 核心优势：
- 默认精确匹配：无需设置第四个参数为FALSE。
- 可以向左查找：
返回数组可以在查找数组的任意侧。 - 支持通配符和二进制搜索。
- 返回数组可以是动态数组：如果
返回数组是一个多列区域，XLOOKUP可以返回一个结果数组（需在支持动态数组的环境中）。
实战案例6：反向查找与多列信息一次性提取
传统上，用VLOOKUP根据员工姓名找工号（工号在姓名左侧）很麻烦。用 XLOOKUP 轻而易举。
- 反向查找：
在B列（姓名）中查找“张三”，返回同一行A列（工号）的值。=XLOOKUP(“张三”, B2:B100, A2:A100) - 一次性提取多列信息：
此公式会返回“张三”所对应的A到E列整行数据（一个1行5列的数组）。如果您将此公式输入在单个单元格并按下Enter，由于动态数组功能，它将溢出到右侧4个单元格，完整显示该员工的所有信息。=XLOOKUP(“张三”, B2:B100, A2:E100)
三、 函数组合：构建自动化动态报表系统 #
单个函数已经强大，组合使用更能释放无限可能。下面我们构建一个综合性的动态报表。
场景：创建一个动态销售仪表板，包含：
- 一个可选择销售员的下拉列表。
- 动态显示该销售员的所有交易记录（自动筛选、排序）。
- 动态计算该销售员的总销售额、平均单笔金额、销售产品种类数。
- 动态生成该销售员按产品分类的金额汇总。
步骤：
-
定义数据源与查询条件：
- 假设数据在
Sheet1!A2:E100。 - 在
Sheet2!A1单元格创建数据验证下拉列表，来源为=UNIQUE(Sheet1!B2:B100)，用于选择销售员。
- 假设数据在
-
动态交易明细表：
- 在
Sheet2!A3单元格输入公式，筛选并排序该销售员的记录（按金额降序）：=SORT(FILTER(Sheet1!$A$2:$E$100, Sheet1!$B$2:$B$100=$A$1), 5, -1) - 在
Sheet2!F3单元格为上述结果添加动态序号：
（=IF(A3#="", "", SEQUENCE(ROWS(A3#)))A3#即引用上述公式的整个溢出区域）
- 在
-
动态关键指标计算：
- 总销售额（
Sheet2!H1）：=SUM(FILTER(Sheet1!$E$2:$E$100, Sheet1!$B$2:$B$100=$A$1)) - 平均单笔金额（
Sheet2!H2）：=AVERAGE(FILTER(Sheet1!$E$2:$E$100, Sheet1!$B$2:$B$100=$A$1)) - 销售产品种类数（
Sheet2!H3）：=COUNTA(UNIQUE(FILTER(Sheet1!$C$2:$C$100, Sheet1!$B$2:$B$100=$A$1)))
- 总销售额（
-
动态产品分类汇总：
- 在
Sheet2!J1单元格生成该销售员销售过的不重复产品列表：=UNIQUE(FILTER(Sheet1!$C$2:$C$100, Sheet1!$B$2:$B$100=$A$1)) - 在
Sheet2!K1单元格，对应计算每个产品的销售总额：
（=SUMIFS(Sheet1!$E$2:$E$100, Sheet1!$B$2:$B$100, $A$1, Sheet1!$C$2:$C$100, J1#)J1#引用了上一步生成的动态产品列表）
- 在
至此，一个完全由公式驱动、无需任何手动刷新或调整范围的动态销售仪表板就完成了。只需在 A1 单元格选择不同销售员，所有表格和数据都会瞬间自动更新。
四、 进阶技巧与注意事项 #
4.1 处理“#SPILL!”错误 #
当公式的输出区域被非空单元格阻挡时，会出现“#SPILL!”错误。解决方法：
- 清除阻挡物：检查并清空公式预期溢出区域内的所有单元格。
- 使用“#”运算符：在设计引用动态数组结果的公式时，使用
A1#这种引用方式，可以避免手动确定范围。
4.2 性能考量 #
虽然动态数组功能强大，但过度复杂或引用整个数据列（如 A:A）的数组公式，在数据量极大时可能影响计算速度。建议尽量引用精确的数据范围（如 A2:A1000）。
4.3 与旧版本兼容性 #
如果您需要将包含动态数组公式的工作簿分享给使用旧版WPS或Microsoft Office（2019之前）的用户，动态数组将无法正常计算和显示。可以考虑：
- 使用“粘贴为值”的方式分享结果。
- 或者，提醒对方升级办公软件版本。
4.4 结合WPS AI提升效率 #
在掌握这些函数原理的基础上，您可以利用 WPS AI 来辅助生成或解释复杂公式。例如，您可以在WPS表格的AI助手框中输入：“用FILTER和SORT函数，提取销售部所有员工中，绩效大于90分的记录，并按绩效降序排列”。WPS AI可能会为您生成一个非常接近的公式草稿，您稍作调整即可使用。关于WPS AI的更多能力，可以参考我们的评测文章：《 WPS 智能助手（WPS AI）实战评测：如何用 AI 提升写作、制表与演示效率》。
五、 常见问题解答 (FAQ) #
Q1: 我的WPS表格版本好像不支持动态数组公式，输入后没有自动溢出，怎么办？ A1: 请确认您的WPS表格版本。需要WPS Office 2019专业增强版（11929+）或更新版本。您可以通过官网下载最新版。关于版本选择和安装，请参阅《 WPS Office 2024 官方正版免费下载与安装激活全平台指南》。
Q2: FILTER函数中，如何实现“或”(OR)条件，而不是“与”(AND)条件？
A2: 使用加号 + 代替乘号 *。例如，筛选销售员为“张三”或产品为“笔记本”的记录：=FILTER(A2:E100, (B2:B100="张三") + (C2:C100="笔记本"))。在数组运算中，加号 + 代表逻辑“或”。
Q3: 动态数组公式的结果，可以作为数据透视表的数据源吗？
A3: 是的，这是动态数组一个非常强大的应用。您可以将一个动态数组公式的结果定义为一个名称（如“动态数据源”），然后在创建数据透视表时，将数据源设置为这个名称（如 =动态数据源）。这样，数据透视表的数据源就会随着动态数组公式的结果自动更新范围。
Q4: 如何固定（转换为静态值）一个动态数组公式的结果？ A4: 选中整个动态数组的溢出区域，复制（Ctrl+C），然后右键单击，选择“粘贴为值”（或按 Ctrl+Shift+V）。这样公式就会被其计算结果替代。
Q5: SORT和SORTBY函数，能按多列排序吗？ A5: 可以。
SORT函数目前主要按单索引列排序。多列排序需嵌套或使用其他方法。SORTBY函数天生支持多列排序。语法为：=SORTBY(数组， 排序依据数组1， 顺序1， 排序依据数组2， 顺序2， ...)。例如，先按部门升序，再按工资降序：=SORTBY(员工数据， 部门列， 1， 工资列， -1)。
结语 #
动态数组公式及其相关新函数的引入，标志着WPS表格在数据处理智能化道路上迈出了关键一步。它们将用户从繁琐的公式填充和范围调整中解放出来，让开发者能够以更声明式、更聚焦逻辑的方式构建数据模型。从简单的数据提取、去重，到复杂的多条件筛选、动态排序，再到构建完整的、可交互的自动化报表仪表板，这些功能都提供了优雅而高效的解决方案。
掌握它们，意味着您拥有了处理现代数据工作流的利器。我们鼓励您将本文的案例在自己的数据上实践，并尝试组合创新。同时，WPS表格的其他高级功能，如 数据透视表、PowerPivot数据建模，与动态数组公式结合使用，能构建出更强大的商业智能分析系统。如果您想深入了解数据建模，推荐阅读《 WPS 表格数据建模与PowerPivot入门：构建商业智能分析基础》。不断探索与实践，您将真正体验到数据驱动决策的高效与乐趣。
本文由 WPS官网入口 站点提供，欢迎访问 WPS Office 下载 页面了解更多办公软件资讯。