当前位置：首页 > news >正文

Excel 365/2021 动态数组函数实战：FILTER、XLOOKUP、UNIQUE 组合解决 5 类数据清洗难题

news 2026/7/11 2:26:13

Excel 365/2021 动态数组函数实战：FILTER、XLOOKUP、UNIQUE 组合解决 5 类数据清洗难题

数据清洗是每个Excel用户都会遇到的痛点。传统方法需要复杂的嵌套函数、辅助列和大量手动操作，而Excel 365/2021推出的动态数组函数彻底改变了这一局面。本文将带你用FILTER、XLOOKUP和UNIQUE这三个核心函数，解决实际工作中最常见的五类数据清洗难题。

1. 多条件筛选与数据提取

销售部门需要从全年订单中提取华东地区金额超过10万元的电子产品订单。传统方法需要结合INDEX-MATCH或复杂数组公式，现在只需一个FILTER函数：

=FILTER(订单表, (订单表[地区]="华东")* (订单表[产品类别]="电子产品")* (订单表[金额]>100000), "无符合条件记录")

关键技巧：

条件之间用乘号(*)表示AND关系
最后一个参数定义无结果时的返回内容
结果自动溢出到相邻单元格，形成动态数组

对比传统方法，这个公式减少了80%的输入量，且当源数据更新时会自动刷新结果。

注意：如果条件中包含OR逻辑，需要用加号(+)连接不同条件，并用括号分组

2. 跨表查找与数据合并

人力资源部需要将分散在5个分公司的员工信息表合并，并匹配最新的薪资等级。XLOOKUP的模糊匹配和数组功能可以一次性完成：

=XLOOKUP(员工编号, 分公司A[员工编号], HSTACK(分公司A[姓名], 分公司A[部门], 薪资表[等级]), "未找到", 0, 1)

参数解析：

参数	作用	本例设置
查找值	要查找的关键字段	员工编号
查找数组	被搜索的列	分公司A[员工编号]
返回数组	可以返回多列	HSTACK组合三列
未找到时	自定义错误提示	"未找到"
匹配模式	0=精确匹配	0
搜索模式	1=从上到下	1

这个公式的亮点在于：

用HSTACK同时返回多列信息
支持通配符匹配（将匹配模式设为2）
比VLOOKUP更灵活，查找列可以在任意位置

3. 数据去重与唯一值统计

市场部需要分析客户来源渠道，并统计每个渠道的唯一客户数。UNIQUE与COUNTA的组合能一键解决：

=LET( 唯一渠道, UNIQUE(客户表[来源渠道]), HSTACK( 唯一渠道, COUNTIFS(客户表[来源渠道], 唯一渠道) ) )

进阶技巧：

使用LET定义中间变量，提高公式可读性
结合SORT对结果排序：=SORT(UNIQUE(客户表[来源渠道]),1,1)
多列去重：=UNIQUE(客户表[[区域]:[渠道]])

下表对比了不同去重方法的效率：

方法	公式长度	计算速度	动态更新
数据透视表	需多次点击	快	需刷新
高级筛选	需设置条件区域	中等	否
UNIQUE函数	1个函数	最快	自动

4. 动态分类汇总

财务部门需要按月份和科目动态生成费用汇总表。传统方法需要手动创建数据透视表，现在用FILTER和SUMIFS组合实现自动化：

=LET( 月份列表, SORT(UNIQUE(费用表[月份])), 科目列表, TRANSPOSE(SORT(UNIQUE(费用表[科目]))), VSTACK( HSTACK("月份/科目", 科目列表), HSTACK( 月份列表, MAKEARRAY( COUNTA(月份列表), COUNTA(科目列表), LAMBDA(r,c, SUMIFS( 费用表[金额], 费用表[月份], INDEX(月份列表,r), 费用表[科目], INDEX(科目列表,c) ) ) ) ) ) )

这个复杂公式实现了：

自动提取唯一月份和科目作为行列标题
使用MAKEARRAY生成二维汇总矩阵
结果自动呈现为标准的交叉报表格式

当新增费用记录时，整个报表会自动扩展行列并更新数据。

5. 异常数据检测与清洗

质量部门需要从生产数据中筛选出超出3σ标准的异常值。FILTER结合统计函数可以智能标记问题数据：

=LET( avg, AVERAGE(生产数据[数值]), stdev, STDEV.P(生产数据[数值]), FILTER( 生产数据, ABS(生产数据[数值]-avg)>3*stdev, "无异常数据" ) )

配套的自动化处理方案：

用条件格式标记异常值：
```
=ABS(B2-AVERAGE(B:B))>3*STDEV.P(B:B)
```
使用XLOOKUP替换异常值为相邻正常值的平均值
用UNIQUE统计异常类型分布

实战案例：销售数据分析看板

将以上技巧整合到一个销售分析系统中：

=LET( // 数据准备 原始数据, 销售表[#全部], 有效数据, FILTER(原始数据, 原始数据[金额]>0), // 关键指标计算 总销售额, SUM(有效数据[金额]), 平均单价, AVERAGE(有效数据[单价]), 客户数, COUNTA(UNIQUE(有效数据[客户ID])), // 按产品分类统计 产品分类, SORT(UNIQUE(有效数据[产品类别])), 产品统计, HSTACK( 产品分类, COUNTIFS(有效数据[产品类别], 产品分类), SUMIFS(有效数据[金额], 有效数据[产品类别], 产品分类) ), // 最终输出 VSTACK( {"总销售额","平均单价","客户数";总销售额,平均单价,客户数}, {"","",""}, {"产品类别","订单数","销售额"}, 产品统计 ) )

这个综合案例展示了如何用动态数组函数构建一个自动化的数据分析系统，相比传统方法：