Excel: 提取唯一值的进阶玩法：兼容共享工作簿的 Excel 数组公式技巧

背景介绍

在 Excel 中，经常需要从一列数据中提取不重复的唯一值列表，以便分析或生成下拉列表。Office 365/Excel 2021 引入了动态数组函数，可以轻松实现这一需求。例如，常用公式：

=UNIQUE(FILTER(A2:A500, A2:A500<>""))

即可返回 A2:A500 区域去除空值后的唯一值列表，并自动溢出填充相邻单元格。在微软官方文档中，FILTER 函数会返回一个数组，并在作为公式结果时自动溢出；UNIQUE 函数同样返回数组，并在结果单元格自动溢出。也就是说，这些新函数会动态创建所需大小的结果区域，随源数据变化自动更新。

然而，当启用“共享工作簿(旧版)”功能时，Excel 会强制使用早期兼容模式，将动态数组公式视为传统的 Ctrl+Shift+Enter (CSE) 旧版数组公式。此时，原本可以自动扩展的溢出行为失效，公式会被 Excel 自动加上大括号 {}。结果出现以下问题：

• 清空源列数据时出错：由于结果区域已“锁定”长度，源列被清空后，目标单元格会出现 #REF! 或错误，无法正确显示空结果。
• 新增数据时无法自动更新：因为溢出区域不再动态扩展，新加入的数据不会触发公式重算和区域扩张，导致唯一值列表不完整。

以上问题是由于旧版共享模式下动态数组功能受限所致。要兼顾共享工作簿的需求，又实现动态唯一值提取，需要采用兼容的公式方案。

解决方案

针对上述兼容性问题，可以使用如下公式来提取唯一值并支持向下填充更新：

=IFERROR(@INDEX(UNIQUE(FILTER(A2:A500, A2:A500<>"")), ROWS(A

使用方法：在结果区域的首个单元格（如 B2）中输入该公式，然后向下拖动填充列（B3、B4……），直到看到空白。该公式会依次提取唯一值列表中的第 1、第 2、第 3…… 项，超过部分显示空字符串，避免报错。

公式拆解说明

该公式的各部分功能如下：

• FILTER(A2:A500, A2:A500<>"")：筛选出 A2:A500 区域中非空的单元格，生成一个数组。
• UNIQUE(...)：对筛选结果提取不重复项，返回一个唯一值数组。
• INDEX(..., ROWS(A1:A1))：从上述唯一值数组中按行号依次取值。ROWS(A1:A1) 在第一行返回 1，第二行返回 2，以此类推，实现“第 n 行的唯一值”提取。
• @（隐式交叉引用运算符）：在公式前加 @，强制公式返回单一值，而不产生溢出区域。这是为了兼容旧模式下的数组行为，避免公式尝试一次输出多值而出错。
• IFERROR(..., "")：当 INDEX 无法返回更多值时会报错（如 #REF!），IFERROR 捕捉该错误并返回空字符串，确保下方单元格显示为空而非报错。

实际示例

这样，即使在“共享工作簿(旧版)”模式下，新增或修改 A 列数据后，只需重新拖动或复制公式区域，结果列即可动态更新。

总结

该方法利用 UNIQUE、FILTER 等动态数组函数结合 INDEX/ROWS 的传统技巧，在旧版共享工作簿模式下实现了相对动态的唯一值提取。它兼顾了兼容性与稳定性：在支持动态数组的 Excel 版本中效果一致，同时即使启用旧版共享模式，公式也能逐行提取唯一值，避免了溢出冲突。