Excel FILTER 函数：多条件筛选、AND 与 OR

=FILTER(A2:C7,B2:B7="North") 返回 A2:C7 中 B 列地区为 North 的每一行。你只需在一个单元格中输入它，匹配的行会溢出到下方和右侧的单元格中。把 B 列中某个地区改成 North，或者把某个 North 改成 South，列表都会随之更新。

公式重置

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

只有 E2 中有公式。E:G 中其他有内容的单元格都是它的溢出结果：点击 F3，你会看到它属于 E2 中的公式。如果在这片区域里输入了内容，FILTER 就会显示 #SPILL! 而不是这些行（见 #SPILL! 错误）。

FILTER 语法

=FILTER(array, include, [if_empty])

• array 是你想要返回的内容：一列、几列或整张表。
• include 是一个条件，对 array 的每一行给出一个 TRUE 或 FALSE，例如 B2:B7="North"。它的行数必须与 array 完全相同。（如果要筛选列，就为每一列给出一个值。）
• if_empty 是没有任何行匹配时要显示的内容。不写它的话，空结果就是 #CALC! 错误。

FILTER 需要 Excel 2021、Excel 2024 或 Microsoft 365。在 Excel 2019 及更早版本中它会显示 #NAME?，在这些版本中筛选要用“数据”选项卡上的“筛选”按钮。Google 表格也有 FILTER，而且每个条件还可以作为单独的参数给出。

文本比较不区分大小写：B2:B7="north" 也能匹配 North。FILTER 会保持行原来的顺序；对结果排序是另一步，后面会讲到。

按单元格的值筛选

把“North”直接写死在公式里，每次都得去改公式。把值放在一个单元格里，改为与这个单元格比较。在 F1 中选择另一个地区，结果就会跟着变：

公式重置

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

West 没有任何行，所以选择它时会显示 if_empty 的文本 No sales。

数字也一样。F1 中为 100 时，C2:C7>=F1 保留销售额至少为 100 的每一行，C2:C7>F1 则是严格大于。

FILTER 多条件筛选（AND）

只在两个条件同时成立时才保留某一行，就把它们相乘。下面返回销售额大于 100 的 North 行：

公式重置

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

Ann（120）和 Cara（200）符合条件。Finn 虽然是 North，但他的 60 没有超过 100，所以被排除了。

为什么用乘法：每个条件都是一列 TRUE 和 FALSE，在算术运算中 TRUE 算作 1，FALSE 算作 0。只有每个因子都是 1 时，这一行的结果才是 1，所以 * 起到了 AND 的作用。每个条件都要加上自己的括号，而且想串联多少个都可以：(B2:B7="North")*(C2:C7>100)*(C2:C7<500)。

这里不能用 AND()。AND(B2:B7="North",C2:C7>100) 会把整个区域归结为一个 TRUE 或 FALSE，而不是每行一个，于是 FILTER 拿到的形状就不对了。

FILTER 的 OR 条件

只要至少一个条件成立就保留某一行，就把条件相加。下面返回 North 和 East 的行：

公式重置

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

同时满足两个条件的行相加得 2，而 FILTER 会保留结果不为 0 的任何行，所以加法起到了 OR 的作用。两者可以混用：((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) 表示（North 或 East）并且大于 100。在这里它返回 Ann、Cara 和 Dan。

没有匹配时 FILTER 返回 #CALC!

没有任何行符合条件时，FILTER 就没有东西可以返回。没有第三个参数时，结果是 #CALC! 错误；有第三个参数时，会显示你自己写的文本：

公式重置

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! 计算没有结果，比如 FILTER 没有匹配到任何内容。

E2 显示 #CALC!，F2 显示 No match。把 B3 从 South 改成 West，两个公式都会返回 Ben。如果什么都不想显示，就用空字符串：=FILTER(A2:A7,B2:B7="West","")。

这个表格也演示了只筛选一列：array 是 A2:A7，所以只返回姓名。要取表格中的部分列而不是全部，用 CHOOSECOLS 包住结果：=CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) 返回姓名和销售额，不包含地区。CHOOSECOLS 需要 Microsoft 365 或 Excel 2024。

对 FILTER 的结果排序

FILTER 按行在表格中出现的顺序返回结果。用 SORT 包住它就能给结果排序：这里把 North 行按销售额从高到低排列。3 是结果中用来排序的列，-1 表示降序。

公式重置

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

Cara（200）排在第一，然后是 Ann（120）和 Finn（60）。如果只想返回前几行，再用 TAKE 包一层：=TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) 保留前两行（TAKE 需要 Microsoft 365 或 Excel 2024）。其他排序选项见 SORT 和 SORTBY。

筛选包含某段文本的行

FILTER 不支持通配符，所以 B2:B7="*th*" 查找的是字面文本 *th*。要保留名字中包含某段文本的行，就用 SEARCH 检查每个单元格（找到文本时返回位置，找不到时返回错误），再用 ISNUMBER 包住它：

公式重置

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

结果返回 Ann 和 Dan：SEARCH 不区分大小写，所以“an”也能匹配 Ann 中的 An。需要区分大小写时，用 FIND 代替 SEARCH。

练习：两个条件的 FILTER

公式重置

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 E2 中返回销售额大于 85 的 South 销售员所在的行（全部三列）。

检查显示答案

练习：按单元格筛选，并设置备用值

公式重置

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 F3 中列出 F1 中所填地区的销售员姓名（只要 A 列）。如果没有，显示 None。

检查显示答案

FILTER 的常见错误

• 区域高度不一致。 =FILTER(A2:C7,B2:B6="North") 用 5 行去检查一张 6 行的表，Excel 会返回 #VALUE!。让 include 的起止行与 array 相同。
• 来源为空的地方出现 0。 array 中的空单元格会被 FILTER 返回为 0。筛选前先把空白换成空文本：=FILTER(IF(A2:C7="","",A2:C7),B2:B7="North")。
• 引用整列。 =FILTER(A:C,B:B="North") 可以用，但如果公式本身就在 A 到 C 列中，它就会引用自己。把结果放在表格旁边，或者使用 A2:C1000 这样的固定区域。
• 给数字加了引号。 C2:C7>"100" 是在拿数字和文本比较，什么都不会保留。应写成 C2:C7>100。
• 以为它和“筛选”按钮一样。 FILTER 把匹配的行复制到新位置，不会改动原表格。要在表格本身中隐藏行，请使用“数据 > 筛选”。