超级过滤：FILTER，批量筛选酣畅淋漓|filter|拉框|数据源

FILTER函数是Excel 365版本才新增的，功能非常强大，根据给定的条件对数据表进行筛选过滤，相当于使用“筛选”或“高级筛选”功能筛选数据。但FILTER函数的优势是，不仅能使得数据源与筛选结果之间是联动的，而且既能按列筛选又能按行筛选。FILTER函数的语法结构是：

FILTER(数据源，条件判断，[找不到时取值])

¤ FILTER函数是动态数组函数，能动态调整输出区域的大小。

¤ 第1参数“数据源”，指的是待筛选过滤的单元格区域或数组。

¤ 第2参数“条件判断”，指的是根据给定的条件进行判断比较，返回值一般是布尔值数组，也就是若干逻辑值TRUE、FALSE组成的数组；返回值也可以是若干0与非0数字组成的数组。该参数是整个函数的核心，具有非常高的灵活性。

¤ “条件判断”返回的数组里凡是非0的数字，就是FILTER函数据此筛选的关键，这些非0数字在数组里所在的那些行或列，决定了在数据源（第1参数）里相同位置的行或列的数据被FILTER函数筛选出来。

¤ 第3参数“找不到时取值”，可选参数，指的是在数据源里找不到符合条件的数据时应当返回的值，这个值可根据实际需要去指定。

1. 单条件筛选过滤

FILTER函数单条件筛选过滤

首先在单元格区域G4:J4准备好标题，然后在单元格G5输入如下动态数组公式，回车后动态捕捉所需的输出区域并在该区域显示出筛选结果，如图所示：

=FILTER(B3:E14,D3:D14=H2,"")

¤ 第1参数“数据源”，这里选取了单元格区域B3:E14（不包含标题行）。该参数选择的区域有几列，就决定了筛选得到的结果有几列。

¤ 第2参数“条件判断”，是比较单元格区域D3:D14里每个商品名是否与单元格H2的商品名相等。提前在单元格H2里用有效性制作了下拉框，步骤是：单击“数据→数据验证→设置→允许：序列”，然后在“来源”选择框里输入这些内容“菠萝,哈密瓜,橙子,苹果,香蕉”（注意这里的“,”是英文半角的逗号）作为下拉列表的项目，最后单击“确定”按钮。

¤ 第3参数“找不到时取值”，通常取空值（用一对半角双引号表示空值）。

¤ 第1参数、第2参数里涉及到的区域，也可以选择整列区域。比如，上述公式可以改为“=FILTER(B:E,D:D=H2,"")”。

¤ 当单元格H2里选择不同的商品时，以单元格G5为起点的“溢出”区域里会动态显示筛选结果。比如，选择“哈密瓜”时，有3条筛选记录；选择“橙子”时，有0条筛选记录；选择“香蕉”时，有6条筛选记录。

¤ 用条件格式功能给“溢出”区域添加边框线及其他格式，让格式也能跟随上筛选结果的动态变化。条件格式的设置步骤是：假设筛选出的记录最多10条，先选中单元格区域G5:J14，再单击“开始→条件格式→新建规则→选择规则类型：使用公式确定要设置格式的单元格”，接着在“为符合此公式的值设置格式”下的选择框里输入公式“=$G5<>""”，继续单击“格式→边框→外边框→确定”（在这里还可以根据需要设置其他各种格式，比如字体的颜色、字形、填充颜色等），最后单击“确定”按钮。

2. 多组“与条件”筛选过滤

FILTER函数多组“与条件”筛选过滤

首先在单元格区域L4:O4准备好标题，然后在单元格L5输入如下动态数组公式，回车后立即在溢出区域里显示出筛选结果，如图所示：

=FILTER(B3:E14,(B3:B14=M2)*(D3:D14=M3),"")

¤ 上述公式还可以改为“=FILTER(B:E,(B:B=M2)*(D:D=M3),"")”。

¤ 提前分别用有效性为单元格M2与M3制作下拉框。单元格M2下拉框列表的来源为“北京,上海,深圳”。而单元格M3的下拉框与单元格H2是一样的，直接复制单元格H2粘贴到单元格M3就可以了。

¤ 这里FILTER函数的第2参数“条件判断”，使用了多组“与条件判断”，每组条件判断之间用乘号“*”连接，表示同时满足的意思。第1组条件判断，是比较单元格区域B3:B14里每个城市名是否与单元格M2的城市名相等，返回一个12行1列的布尔值数组“{FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}”。第2组条件判断，是比较单元格区域D3:D14里每个商品名是否与单元格M2的商品名相等，同样返回一个12行1列的布尔值数组“{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}”。大家可以在编辑栏里分别选中每组条件判断后按F9键去显示其真实值。

¤ 两组“与条件判断”的结果通过乘号“*”运算后，就返回一个12行1列的由0和1组成的数组“{0;0;1;0;0;0;1;1;0;0;1;1}”。若直接在编辑栏里选中两组条件判断再按F9键，就立即看到了真实结果。如图 4‑40所示。

图4‑40 编辑栏里选中两组“与条件判断”再按F9键显示最终结果

¤ 最终FILTER函数是根据数组“{0;0;1;0;0;0;1;1;0;0;1;1}”里所有的1去对数据源进行筛选的。

3. 或条件筛选过滤

首先在单元格区域Q4:T4准备好标题，然后在单元格Q5输入如下动态数组公式，回车后立即在溢出区域里显示出筛选结果，如图 4‑41所示：

=FILTER(B3:E14,(C3:C14=R2)+(D3:D14=R3),"")

¤ 上述公式还可以改为“=FILTER(B:E,(C:C=R2)+(D:D=R3),"")”。

¤ 先分别用有效性为单元格R2与R3制作下拉框。单元格R2下拉框列表的来源为“汪莉霞,唐欣怡,胡芝,蒋以娟,李城”。而单元格R3的下拉框与单元格M3是一样的，直接复制单元格M3粘贴到单元格R3就可以了。

¤ 这里FILTER函数的第2参数“条件判断”，使用了多组条件判断，每组条件判断之间用加号“+”连接，表示“或条件”。第1组条件判断，是比较单元格区域C3:C14里每个业务员姓名是否与单元格R2的姓名相等，返回一个12行1列的布尔值数组“{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}”。第2组条件判断，是比较单元格区域D3:D14里每个商品名是否与单元格R3的商品名相等，同样返回一个12行1列的布尔值数组“{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}”。在编辑栏里分别选中每组条件判断后按F9键去显示其真实值。