如何用公式对Excel数据去重并计数？

Question

如何在不使用高级筛选或VBA的情况下，仅通过Excel公式对一列数据进行去重并统计每个唯一值的出现次数？常见场景如处理销售记录、学生名单等重复数据。期望结果为：提取唯一值列表，并对应其出现频次。难点在于早期版本Excel缺乏FILTER、UNIQUE等动态数组函数，需结合IF、FREQUENCY、MATCH、COUNTIF等函数实现。例如，如何正确组合公式避免重复提取、空值干扰及性能瓶颈？该问题在数据分析预处理中尤为常见且具挑战性。

Answer 1

一、问题背景与核心需求分析

在日常数据分析中，处理重复数据是常见任务之一。例如销售记录中的客户名称、学生名单中的姓名、产品类别等字段常包含重复项。目标是提取出唯一的值列表，并统计每个唯一值的出现次数。然而，在Excel 2019及更早版本中，缺乏FILTER、UNIQUE等动态数组函数，无法直接使用现代公式实现去重统计。

因此，必须依赖传统函数组合：如IF、COUNTIF、MATCH、FREQUENCY、INDEX等，构建稳定且高效的公式逻辑。该方法不仅适用于老旧系统环境，也对理解Excel计算引擎底层机制有重要意义。

二、基础概念与函数解析

• COUNTIF(range, criteria)：统计满足条件的单元格数量，用于频次计算。
• MATCH(lookup_value, lookup_array, 0)：返回查找值在数组中的位置，用于判断是否首次出现。
• FREQUENCY(data_array, bins_array)：虽常用于直方图，但可辅助识别唯一值索引（配合MATCH）。
• INDEX(array, row_num)：按行号提取结果，用于构建最终输出列表。
• IF(logical_test, value_if_true, value_if_false)：控制流程，避免空值或重复提取。

这些函数的嵌套使用构成了非VBA环境下实现去重的核心技术栈。

三、解决方案设计流程图

graph TD
    A[原始数据列] --> B{是否存在重复？}
    B -- 是 --> C[使用MATCH+COUNTIF定位首次出现位置]
    B -- 否 --> D[直接列出]
    C --> E[通过FREQUENCY生成唯一标识索引]
    E --> F[用INDEX提取唯一值列表]
    F --> G[使用COUNTIF统计各唯一值频次]
    G --> H[输出：唯一值 + 出现次数]

四、具体实现步骤与公式详解

假设原始数据位于A2:A13，如下表所示：

序号	姓名
1	张伟
2	王芳
3	李强
4	张伟
5	刘洋
6	王芳
7	陈静
8	李强
9	赵敏
10	张伟
11	刘洋
12	陈静

步骤1：生成唯一值列表

在B2单元格输入以下数组公式（需按 Ctrl+Shift+Enter）：

=IFERROR(INDEX($A$2:$A$13,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$13)+IF(COUNTIF($A$2:$A$13,$A$2:$A$13)=0,1,0),0)),"")

向下填充至足够行数（如B13），即可得到不重复的姓名列表。

步骤2：统计每个唯一值的出现次数

在C2单元格输入普通公式：

=IF(B2="","",COUNTIF($A$2:$A$13,B2))

向下填充，即可获得对应频次。

五、优化策略与性能考量

上述方法存在潜在性能瓶颈，尤其当数据量超过千行时，频繁调用COUNTIF会导致计算延迟。为提升效率，可采用以下优化手段：

1. 将源数据转换为“表格”（Ctrl+T），启用结构化引用，提高公式可读性与维护性。
2. 使用辅助列标记首次出现位置，例如D列：=IF(MATCH(A2,A:A,0)=ROW(),1,0)，再筛选为1的行。
3. 结合SUMPRODUCT替代部分COUNTIF以减少范围扫描次数。
4. 避免整列引用（如A:A），应限定具体区域（如A2:A1000）以减少内存占用。

此外，对于含空值的数据集，应在公式中加入TRIM和LEN判断，防止空白字符干扰匹配结果。