SORT(UNIQUE函数嵌套后无法去重？

Question

在使用 Excel 或 Google Sheets 时，用户常遇到 `SORT(UNIQUE(range))` 嵌套函数无法完全去重的问题。典型表现为：原始数据存在重复项，但经 `UNIQUE` 处理后再用 `SORT` 排序，结果仍出现重复值。此问题通常源于数据类型不一致（如文本型数字与数值混杂）、前后空格未清除或不可见字符干扰，导致 `UNIQUE` 函数误判为不同条目。此外，在动态数组公式中，若引用区域包含错误数据或跨表链接未刷新，也可能使嵌套失效。需结合 `TRIM`、`CLEAN` 或 `VALUE` 等函数预处理数据，确保输入纯净，方可实现正确去重与排序。

Answer 1

1. 问题现象：为何 SORT(UNIQUE(range)) 仍出现重复值？

在使用 Excel 或 Google Sheets 的动态数组功能时，SORT(UNIQUE(A:A)) 被广泛用于对数据去重并排序。然而，许多用户发现即使应用了该嵌套函数，输出结果中依然存在看似相同的重复条目。

• 例如原始数据包含 "100"（文本）与 100（数值），UNIQUE 将其视为两个不同项。
• 字符串前后含有空格（如 "Apple " 与 "Apple"）也会导致误判。
• 不可见字符（如换行符、制表符）常潜藏于导入数据中，影响唯一性判断。
• 跨工作表引用未及时刷新或链接错误可能导致返回 #N/A 等错误值，干扰处理流程。

2. 根本原因分析：数据异构性与隐式类型差异

从底层机制看，UNIQUE 函数基于精确匹配进行比较，不执行隐式类型转换。以下表格列举常见干扰因素：

干扰类型	示例	函数识别结果	解决方案方向
文本型数字 vs 数值	"123" vs 123	视为不同	使用 VALUE()
前/后空格	"Data " vs "Data"	视为不同	TRIM()
换行符或制表符	"Item\n" vs "Item"	视为不同	CLEAN(), SUBSTITUTE()
全角/半角字符	"Ａpple" vs "Apple"	视为不同	统一编码规范
布尔值与文本	TRUE vs "TRUE"	视为不同	标准化输入
#N/A 或错误值	#N/A in range	中断数组输出	IFERROR() 过滤
日期格式混杂	45000 (serial) vs "2023/1/1"	可能不等价	Dates as numbers
公式返回空字符串	"" vs 空单元格	有时区分	条件过滤
区域链接未刷新	外部数据滞缓	旧值残留	手动刷新或自动化同步
大小写敏感性	"Test" vs "test"	默认视为不同（可配置）	LOWER()/UPPER()

3. 解决方案路径：构建鲁棒的数据预处理管道

为确保 SORT(UNIQUE(...)) 正确运行，必须建立标准化清洗流程。推荐采用如下函数组合：

=SORT(
    UNIQUE(
        ARRAYFORMULA(
            TRIM(
                CLEAN(
                    VALUE(
                        IF(ISNUMBER(A:A), A:A, SUBSTITUTE(A:A, CHAR(160), " "))
                    )
                )
            )
        )
    )
)
    

上述公式逐层处理：
- CHAR(160) 替换网页抓取中的非断空格
- VALUE() 强制转为数值类型
- CLEAN() 清除不可见控制字符
- TRIM() 去除首尾及多余空格
- ARRAYFORMULA 支持整列操作（Google Sheets）
- 外层 SORT 实现升序排列

4. 高级实践：构建可复用的清洗模块

对于企业级报表系统，建议封装通用清洗函数。以下 Mermaid 流程图展示数据净化逻辑：

graph TD
    A[原始数据输入] --> B{是否含错误值?}
    B -- 是 --> C[IFERROR 过滤]
    B -- 否 --> D[继续]
    C --> D
    D --> E{是否为混合类型?}
    E -- 是 --> F[尝试 VALUE 转换]
    E -- 否 --> G[保持原值]
    F --> H[TRIM 清理空格]
    G --> H
    H --> I[CLEAN 去除不可见字符]
    I --> J[UNIQUE 去重]
    J --> K[SORT 排序]
    K --> L[输出标准化列表]

5. 跨平台注意事项：Excel 与 Google Sheets 差异

虽然两者语法相似，但在实现细节上存在关键区别：

• Excel 使用 TEXTJOIN + FILTERXML 处理复杂清洗，而 Google Sheets 更依赖 REGEXREPLACE。
• Google Sheets 中 CLEAN 不处理 CHAR(160)，需额外替换。
• Excel 动态数组自动溢出，但旧版本需 Ctrl+Shift+Enter；Google Sheets 原生支持。
• 跨表引用时，Excel 的 INDIRECT 易失效，Google Sheets 的 IMPORTRANGE 需权限授权。
• 性能方面，超大数据集建议分步处理而非单一长公式链。