Excel cell+filter

Excel FILTER + CELL 函数 终极实战手册

（适配 Office 365 / 2021 动态数组，工作、台账、数据清洗专用）

一、FILTER 函数核心（万能筛选）

1. 函数语法

=FILTER(数据区域,筛选条件,[无结果显示内容])

2. 核心特点

- 自动溢出、自动批量显示结果

- 不需要下拉、不需要刷新

- 支持多条件、模糊匹配、正反筛选

- 比筛选、高级筛选快 100 倍

3. 最常用基础公式

单条件筛选

提取 A 列所有 “合格” 数据

=FILTER(A:A,A:A="合格","无数据")

模糊筛选（包含某文字）

提取包含 “矿山” 的所有内容

=FILTER(A:A,ISNUMBER(SEARCH("矿山",A:A)),"无")

多条件 且

A 列矿山 + B 列已审核

=FILTER(A:B,(A:A="矿山")*(B:B="已审核"),"无")

多条件 或

包含矿山 或 包含地块

=FILTER(A:A,ISNUMBER(SEARCH("矿山",A:A))+ISNUMBER(SEARCH("地块",A:A))>0,"无")

二、CELL 函数核心（提取单元格所有隐藏信息）

1. 函数语法

=CELL(信息类型,单元格)

2. CELL 能查的超级实用信息（工作最常用）

- CELL("contents",A1) → 提取单元格内容

- CELL("address",A1) → 提取单元格地址$$A$$1

- CELL("row",A1) → 获取行号

- CELL("col",A1) → 获取列号

- CELL("filename",A1) → 获取当前文件完整路径 + 文件名 + 工作表

三、FILTER + CELL 组合高阶用法（你之前重点学的）

1. 筛选并同时显示行号（超级台账神器）

=FILTER(CELL("row",A2:A100),A2:A100="已完成","无结果")

作用：筛选出符合条件的所在行号，方便定位错误数据

2. 筛选内容 + 自动带出单元格地址

=FILTER(CELL("address",A2:A100),ISNUMBER(SEARCH("核查",A2:A100)),"无")

3. 反向筛选（FILTER 排除某些内容）

排除包含 “作废” 的数据

=FILTER(A2:A100,NOT(ISNUMBER(SEARCH("作废",A2:A100))),"无")

四、日常工作 10 个万能固定公式（直接复制）

1. 模糊筛选包含关键词

=FILTER(A:A,ISNUMBER(SEARCH("关键词",A:A)),"无")

2. 排除关键词筛选

=FILTER(A:A,NOT(ISNUMBER(SEARCH("作废",A:A))),"无")

3. 双条件同时满足（且）

=FILTER(A:B,(A:A="完成")*(B:B="初审"),"无")

4. 双条件任意满足（或）

=FILTER(A:A,(ISNUMBER(SEARCH("甲",A:A))+ISNUMBER(SEARCH("乙",A:A))>0),"无")

5. 提取非空数据

=FILTER(A:A,A:A<>"","空白已过滤")

6. 筛选后返回行号

=FILTER(CELL("row",A2:A999),A2:A999<>"","无")

7. 筛选后返回单元格地址

=FILTER(CELL("address",A2:A999),A2:A999="有效","无")

8. 获取当前表格完整路径

=CELL("filename",A1)

9. 提取纯文件名（不含路径、工作表）

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

10. 动态序号（搭配 FILTER 自动排序）

=SEQUENCE(COUNTA(FILTER(A:A,A:A<>"")))

五、核心知识点总结（必记）

1. FILTER

- *且 用乘法 *

- *或 用加法 +

- *模糊 用 SEARCH+ISNUMBER

- *反向排除 用 NOT

2. CELL

- 专门拿单元格属性：行、列、地址、路径、内容

- 可以嵌入 FILTER 实现「筛选 + 定位」高阶台账

3. 两者组合是 Excel最强动态数据清洗组合

- 自动筛、自动排、自动定位行号地址

- 不用透视表、不用高级筛选、不用 VBA

六、常见报错解决

- #CALC!：没有匹配数据（正常，公式自带兜底 “无结果”）

- #SPILL!：右边有内容挡住溢出区域，清空即可

- 无法使用：说明你的 Excel 是旧版本（2019 及以下不支持）