Excel高级筛选技巧复杂条件一网筛尽Excel高级筛选技巧
Excel中的“自动筛选”功能大家也许并不陌生,对于条件简单的筛选操作,它基本可以应付。但是,最后符合条件的结果只能显示的在原有的数据表格中,不符合条件的将自动隐藏。若要筛选含有指定关键字的记录,并且将结果显示在两个表中进行数据比对或其他情况,“自动筛选”就有些捉襟见肘了。“傻瓜相机”毕竟功能有限,那么就让我们来试试“高级相机”吧!熟练运用“高级筛选”,无论条件多么复杂,都能一网筛尽。人力资源部的小李最近在做员工表格统计时,就尝到了甜头。
一、特定字符一步筛
现在在这份表格中,...
复杂条件一网筛尽Excel高级筛选技巧
Excel中的“自动筛选”功能大家也许并不陌生,对于条件简单的筛选操作,它基本可以应付。但是,最后符合条件的结果只能显示的在原有的数据表格中,不符合条件的将自动隐藏。若要筛选含有指定关键字的记录,并且将结果显示在两个表中进行数据比对或其他情况,“自动筛选”就有些捉襟见肘了。“傻瓜相机”毕竟功能有限,那么就让我们来试试“高级相机”吧!熟练运用“高级筛选”,无论条件多么复杂,都能一网筛尽。人力资源部的小李最近在做员工表格统计时,就尝到了甜头。
一、特定字符一步筛
现在在这份表格中,小李要查找姓“陈”的所有员工记录,他想了想,很快获得了结果。
如图1所示,在数据区域外的任一单元格(如B17)中输入被筛选的字段名称“姓名”,在紧靠其下方的B18单元格中输入筛选条件“陈*”。然后依次单击“数据→筛选→高级筛选”命令,在弹出的“高级筛选”对话框,选择筛选方式中的“将筛选结果复制到其他位置”单选按钮。将“列表区域”设置为“$A$1:$F$15”,“条件区域”设置为“$B$17:$B$18”,“复制到”设置为“$A$20:$F$20”,单击“确定”按钮,系统便自动将符合条件的记录筛选出来,并复制到指定的从A20开始的单元格区域中(如图2所示)。
小提示:如果在图1的B18单元格中输入筛选条件“*陈”,可筛选名字中含有“陈”字的员工记录(即“陈”字不一定是名字中的第一个字,该字可在名字中的任意位置)。
二、空白数据巧妙筛
接下来小李还需要查找没有职称员工的记录,如何进行呢?
如图3所示,他先在数据区域外的任一单元格(如E17)中输入被筛选的字段名称“职称”,然后在紧靠其下方的E18单元格中输入筛选条件“<>*”。
下一步,打开“高级筛选→将筛选结果复制到其他位置”,设置好“列表区域”、“条件区域”和“复制到”的位置,单击“确定”按钮,系统就自动将符合条件的记录筛选出来并复制到指定的单元格区域中(如图4所示)。
小提示:在本例中,如果要筛选的字段是数值型的,则需要将筛选条件更改为“=”(直接输入“=”号后按回车)。反过来,如果要筛选职称为非空的员工记录,只需将图3的筛选条件改为“*”即可。如果指定的筛选字段是数值型字段,则输入筛选条件“<>”。
三、满足多条件一起筛
更复杂的任务来了,这份表格需要查找满足多个条件的结果:男性、年龄不低于30岁、职称字段中含有“工程师”关键字的员工记录。这也难不倒小李。
如图5所示,在数据区域外的任一单元格区域(如A17:C17)中输入被筛选的字段名称“性别”、“年龄”和“职称”,在紧靠其下方的A18:C18单元格区域中分别输入筛选条件“男”、“>=30”和“*工程师”。然后进入“高级筛选→将筛选结果复制到其他位置”,设置好“列表区域”、“条件区域”和“复制到”的位置,确定,系统会自动将符合条件的记录筛选出来并复制到指定单元格区域中(如图6所示)。
小提示:如果将筛选条件输入在同一行中,筛选时系统会自动查找同时满足所有指定条件的记录并将其筛选出来。如果想查找所有字段值都是非空的员工记录,只需将指定的筛选条件文本型用“*”,数值型用“<>”,并将这些筛选条件输入在同一行中即可
四、“多选一”也能筛
有时查找时,几个条件中如果满足一个即可,比如要查找年龄不低于30岁,或者职称为“高级工程师”的员工记录,又该如何筛选?
如图7所示,小李在数据区域外的任一单元格区域(如A17:B17)中输入被筛选的字段名称“年龄”和“职称”,在紧靠其下方的A18:B18单元格区域中分别输入筛选条件“>=30”和“高级工程师”。与以上方法类似,他选中“将筛选结果复制到其他位置”按钮后,设置好“列表区域”、“条件区域”和“复制到”的位置,确定,符合条件的记录就被筛选出来并复制到指定的单元格区域中了(如图8所示)。
小提示: 在以上所有的筛选操作中,如果想使筛选结果不重复,只需选中“高级筛选”对话框中的“选择不重复的记录”复选框再进行相应的筛选操作即可。
小试牛刀,工作效率大大提高。“Excel高级筛选就像一块智能吸铁石,正确充分地利用好,要找的资料就能很快到手!想筛就筛,我筛得漂亮……”小李笑了
在创建高级筛选或使用数据库和列表管理
数(如 DSUM)时,要用到复杂条件 (条件:所指定的限制查询或筛选的结果集中包含哪些记录的条件。)。
要点
由于在单元格中键入文本或值时等号用来表示一个公式,因此 Microsoft Excel 会评估您键入的内容;不过,这可能会产生意外的筛选结果。为了表示文本或值的相等比较运算符,应在条件区域的相应单元格中键入作为字符串表达式的条件:
=''=条目''
其中“条目”是要查找的文本或值。例如:
在单元格中键入的内容
Excel 评估和显示的内容
="=李小明"
=李小明
="=3000"
=3000
Excel 在筛选文本数据时不区分大小写。不过,您可以使用公式来执行区分大小写的搜索。有关示例,请参见 使用区分大小写的搜索筛选文本。
以下各节提供了复杂条件的示例。
一列中有多个条件
多列中有多个条件,其中所有条件都必须为真
多列中有多个条件,其中所有条件都必须为真
多个条件集,其中每个集包括用于多个列的条件
多个条件集,其中每个集包括用于一个列的条件
查找共享某些字符而非其他字符的文本值的条件
将公式结果用作条件
筛选大于数据区域中所有值的平均值的值
使用区分大小写的搜索筛选文本
一列中有多个条件
布尔逻辑: (销售人员 = "李小明" OR 销售人员 = "林丹")
要查找满足“一列中有多个条件”的行,请直接在条件区域的单独行中依次键入条件。
在下面的数据区域 (A6:C10) 中,条件区域 (B1:B3) 显示“销售人员”列 (A8:C10) 中包含“李小明”或“林丹”的行。
A
B
C
1
类型
销售人员
销售额
2
=李小明
3
=林丹
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
多列中有多个条件,其中所有条件都必须为真
布尔逻辑: (类型 = "特制品" AND 销售额 > 1000)
要查找满足“多列中有多个条件”的行,请在条件区域的同一行中键入所有条件。
在下面的数据区域 (A6:C10) 中,条件区域 (A1:C2) 显示“类型”列中包含“特制品”和“销售额”列 (A9:C10) 中值大于 ¥1,000 的所有行。
A
B
C
1
类型
销售人员
销售额
2
=特制品
>1000
3
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
多列中有多个条件,其中所有条件都必须为真
布尔逻辑: (类型 = "特制器" OR 销售人员 = "李小明")
要查找满足“多列中有多个条件,其中所有条件都必须为真”的行,请在条件区域的不同行中键入条件。
在下面的数据区域 (A6:C10) 中,条件区域 (A1:B3) 显示“类型”列中包含“特制品”或“销售人员”列 (A8:C10) 中包含“李小明”的所有行。
A
B
C
1
类型
销售人员
销售额
2
=特制品
3
=李小明
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
多个条件集,其中每个集包括用于多个列的条件
布尔逻辑: ( (销售人员 = "李小明" AND 销售额 >3000) OR (销售人员 = "林丹" AND 销售额 > 1500) )
要查找满足“多个条件集,其中每个集包括用于多个列的条件”的行,请在单独的行中键入每个条件集。
在下面的数据区域 (A6:C10) 中,条件区域 (B1:C3) 显示“销售人员”列中包含“李小明”并且“销售额”列中值大于 ¥3,000 的行,或者显示“销售人员”列中包含“林丹”并且“销售额”列 (A9:C10) 中值大于 ¥1,500 的行。
A
B
C
1
类型
销售人员
销售额
2
=李小明
>3000
3
=林丹
>1500
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
多个条件集,其中每个集包括用于一个列的条件
布尔逻辑: ( (销售额 > 6000 AND 销售额 < 6500 ) OR (销售额 < 500) )
要查找满足“多个条件集,其中每个集包括用于一个列的条件”的行,请在多个列中包括同一个列标题。
在下面的数据区域 (A6:C10) 中,条件区域 (C1:D3) 显示“销售额”列 (A8:C10) 中值在 5,000 和 8,000 之间以及值小于 500 的行。
A
B
C
D
1
类型
销售人员
销售额
销售额
2
>6000
<6500
3
<500
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
查找共享某些字符而非其他字符的文本值的条件
要查找共享某些字符而非其他字符的文本值,请执行下面一项或多项操作:
键入一个或多个不带等号 (=) 的字符,以查找列中文本值以这些字符开头的行。例如,如果键入文本“Dav”作为条件,则 Excel 将找到“Davolio”、“David”和“Davis”。
使用通配符
操作方法
可以使用下面的通配符作为比较条件。
使用
查找
?(问号)
任何单字符
例如,sm?th 可找到“smith”和“smyth”
*(星号)
任何数量的字符
例如,*east 可找到“Northeast”和“Southeast”
~(波形符)后跟 ?、* 或 ~
问号、星号或波形符
例如,fy91~? 可找到“fy91?”
在下面的数据区域 (A6:C10) 中,条件区域 (A1:B3) 显示“类型”列中开头字符为“Me”的行,或者“销售人员”列 (A7:C9) 中第二个字符为“u”的行。
A
B
C
1
类型
销售人员
销售额
2
Me
3
=?u*
4
5
6
类型
销售人员
销售额
7
Beverages
Suyama
¥5122
8
Meat
Davolio
¥450
9
produce
Buchanan
¥6328
10
Produce
Davolio
¥6544
将公式结果用作条件
可以将公式 (公式:单元格中的一系列值、单元格引用、名称或运算符的组合,可生成新的值。公式总是以等号 (=) 开始。)的计算结果作为条件使用。请记住以下要点:
公式必须能够评估为 TRUE 或 FALSE。
由于是使用公式,请按通常方式输入公式,不要按下面的方式键入表达式:
=''=条目''
对于条件标志请不要使用列标志;要么保持条件标志为空,要么使用不是区域中列标志的标志(在下面的示例中,为“计算的平均值”和“精确匹配”)。
如果在公式中使用列标志而不使用相对单元格引用或区域名称,Excel 将在包含条件的单元格中显示错误值(如 #NAME? 或 #VALUE!),您可以忽略这些错误,因为它不影响区域的筛选方式。
所使用的条件公式必须使用相对引用 (相对单元格引用:在公式中,基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。如果复制公式,相对引用将自动调整。相对引用采用 A1 样式。)来引用第一行中的相应单元格(在下例中为 C7 和 A7)。
公式中的所有其他引用必须是绝对引用 (绝对单元格引用:公式中单元格的精确地址,与包含公式的单元格的位置无关。绝对引用采用的形式为 $A$1。)。
下面的小节提供了将公式结果作为条件的一些特定示例。
筛选大于数据区域中所有值的平均值的值
在下面的数据区域 (A6:D10) 中,条件区域 (D1:D2) 显示“销售额”列中值大于所有“销售额”值 (C7:C10) 的平均值的行。在公式中,“C7”引用数据区域 (7) 中筛选列 (C) 的第一行。
A
B
C
D
1
类型
销售人员
销售额
计算的平均值
2
=C7>AVERAGE($C$7:$C$10)
3
4
5
6
类型¥
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
使用区分大小写的搜索筛选文本
在数据区域 (A6:D10) 中,通过使用 EXACT 函数执行区分大小写的搜索 (A10:C10),条件区域 (D1:D2) 将显示“类型”列中包含“特制品”的行。在公式中,“A7”引用数据区域 (7) 中筛选列 (A) 的第一行。
A
B
C
D
1
类型
销售人员
销售额
精确匹配
2
=EXACT(A7, "特制品")
3
4
5
6
类型
销售人员
销售额
7
饮料
苏术平
¥5122
8
肉
李小明
¥450
9
特制品
林丹
¥6328
10
特制品
李小明
¥6544
本文档为【Excel高级筛选技巧】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑,
图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。