Excel高级筛选技巧.docx
《Excel高级筛选技巧.docx》由会员分享，可在线阅读，更多相关《Excel高级筛选技巧.docx（24页珍藏版）》请在冰豆网上搜索。
Excel高级筛选技巧
Excel高级筛选技巧
Excel中的自动筛选”功能大家也许并不陌生，对于条件简单的筛选操作，它基本可以应付。
但是，最后符合条件的结果只能显示的在原有的数据表格中，不符
合条件的将自动隐藏。
若要筛选含有指定关键字的记录，并且将结果显示在两个表中进行数据比对或其他情况，自动筛选”就有些捉襟见肘了。
傻瓜相机”毕竟
功能有限，那么就让我们来试试高级相机”吧！
熟练运用高级筛选”，无论条件多么复杂，都能一网筛尽。
人力资源部的小李最近在做员工表格统计时，就尝到
了甜头。
设置筛选条件区
高级筛选的前提是在数据表的空白处设置一个带有标题的条件区域，这个
条件区有3个注意要点：
条件的标题要与数据表的原有标题完全一致；
多字段间的条件若为与"关系，则写在一行；
多字段间的条件若为或"关系，则写在下一行。
写条件时要遵守的规则是：
1、要在条件区域的第一行写上条件中用到的字段名，比如要筛选数据清单中年龄”在30岁以上，学历”为本科的职员，其中年龄”和学历”是数据清单中对应列的列名，称作字段名，那么在条件区域的第一行一定是写这两个列的名称
（字段名），即年龄”和学历”，而且字段名的一定要写在同一行。
2、在字段名行的下方书写筛选条件，条件的数据要和相应的字段在同一列，比如上例中年龄为30岁，则“3脸个数据要写在条件区域中年龄”所在列，同时本科”要写在条件区域中学历”所在的列。
在具体写条件时，我们要分析好条件之间是与关系还是或关系，如果是与关系，这些条件要写到同一行中，如是是或关系，这些条件要写到不同的行中，也就是说不同行的条件表示或关系，同行的条件表示与关系。
一、特定字符一步筛
现在在这份表格中，小李要查找姓陈”的所有员工记录，他想了想，很快获得了结果。
如图1所示，在数据区域外的任一单元格（如B17）中输入被筛选的字段名称姓名”，在紧靠其下方的B18单元格中输入筛选条件陈*”。
然后依次单击数据-筛选-高级筛选”命令，在弹出的高级筛选”对话框，选择筛选方式中的将筛选结果复制到其他位置”单选按钮。
将列表区域”设置为“$A$1:
$F$15；条件区域”设置为“$B$17:
$B$1&复制到”设置为“$A$20:
$F$20；单击确定”按钮，系统便自动将符合条件的记录筛选出来，并复制到指定的从A20开始的单元格
区域中（如图2所示）。
7
1007
陈XX
男
34
Tj日
8
1008
朱XX
女
40
高级工程师
二车间
9
1011
石X
男
47
高级工程师
二年回
1C
1012
陈XX
男
35
工程师
三帮日
11
1014
TX
女
33
高级工程师
三车间
12
1016
陆XX
更
41
二牛区
13
1017
刘X
女
22
助工
f日
14
1024
陈XX
男
26
会计师
财务科
15
1025
田XX
男
28
助工
16
17
姓名
13
陈*
19
20
工号
姓名
性别
年龄
职称
■■■1一-冈位
21
1007
陈XX
男
34
肘丁
■-一/
一车间_
22
1012
「XX
男
35
二里送
_23_
1024
陈XX
男
26
令/{■师cfah・苗品走h
小提示：
如果在图1的B18单元格中输入筛选条件“陈”，可筛选名字中含有陈”字的员工记录（即陈"字不一定是名字中的第一个字，该字可在名字中的任意位置）。
二、空白数据巧妙筛
接下来小李还需要查找没有职称员工的记录，如何进行呢？
如图3所示，他先在数据区域外的任一单元格（如E17）中输入被筛选的字段名称职称”，然后在紧靠其下方的E18单元格中输入筛选条件“<>*'：
下一步，打开高级筛选一将筛选结果复制到其他位置”，设置好列表区域”、条件区域”和复制到”的位置，单击确定”按钮，系统就自动将符合条件的记录筛
选出来并复制到指定的单元格区域中（如图4所示）。
9
1011
石X
男
47
高级工程师|
二车】
3
10
1012
男
35
工程师
三荆
11
1014
Tx
女
33
高级工程师
三车口
3
12
1016
陆XX
男
41
三车】
3
13
1017
刘X
女
22
助工
一车1
14
1024
陈XX
男
26
会计师
财务不
斗
17
16
1025
田XX
男
28
助工
三车日
17
职称
18
<>*
19
20
工号
姓名
性别
年龄
职称
21
1007
陈XX
男
34
—cfan,CDim?
cnl
22
1016
陆XX
男
41
小提示：
在本例中，如果要筛选的字段是数值型的，则需要将筛选条件更改为“=”（直接输入“二号后接回车）。
反过来，如果要筛选职称为非空的员工记录，只需将图3的筛选条件改为"即可。
如果指定的筛选字段是数值型字段，则输入筛选条件“<>'；
三、满足多条件一起筛
更复杂的任务来了，这份表格需要查找满足多个条件的结果：
男性、年龄不
低于30岁、职称字段中含有工程师”关键字的员工记录。
这也难不倒小李。
如图5所示，在数据区域外的任一单元格区域（如A17:
C17）中输入被筛选的字段名称性别“、年龄”和职称”，在紧靠其下方的A18:
C18单元格区域中分别输入筛选条件男"、">=3价口“札程师”。
然后进入高级筛选一将筛选结果复制到其他位置”，设置好列表区域”、条件区域”和复制到”的位置，确定，系统会自动将符合条件的记录筛选出来并复制到指定单元格区域中（如图6所示）。
A
B
C
D
E
F
1：
工号
姓名
性别
年龄
职称
■Ji■.口_
网位
21
1001
郑X
女
23
助工
二至同
3：
1002
王XX
男
34
高级工程师
一车间
4
1003
杨XX
男
26
工程师
三车间
5
1005
张XX
男
29
工程师
一车间
6
1006
徐XX
54
高级工程师
二车间
r1
1007
陈XX
34
一车间
8.
1008
朱XX
40
高级工程师
二军间
9
1011
石X
男
47
高级工程师
二车间
10
1012
除XX
男
35
工程师
二七司
11
1014
Tx
女
33
高级工程师
二军司
12
1016
陆XX
勇
41
二年司
13
1017
刘X
女一
22
助工
1车间
14
1024
陈XX
男
26
会计师
财务科
15
1025
田XX
男
2S
助工
三车间
16
17
性别
年龄
职称
IS
男
>=30
*工程师
cfan*cnm*cn
8
1008
柒XX
妥
40
高级工程如
二军同
9|
1011
石X
男
4?
高级工程见
二车间
10
1012
陈XX
男
35
工程师
二生间
11
1014
Tx
女
33
高级工程师
三车同
12
1016
陆XX
男
41
二生同
13
1017
刘X
女
22
助工
一车间
14
1024
陈XX
男
26
会计师
财务科
15
1025
田XX
男
28
助工
16
17
性别
年龄
职称
18
男
>=30
水工程师
19
20
工号
姓名
性别
年龄
职称
LJ-*「\.
冈位
21
1002
王XX
男二
34
声吸工程师
一车间
22
1011
石X
男
47
石我
23
1012
f^xx
男
35
小提示：
如果将筛选条件输入在同一行中，筛选时系统会自动查找同时满足所有指定条件的记录并将其筛选出来。
如果想查找所有字段值都是非空的员工记录，只需将指定的筛选条件文本型用数值型用“<>；并将这些筛选条件输入在同一行中即可。
四、多选一”也能筛
有时查找时，几个条件中如果满足一个即可，比如要查找年龄不低于30岁,或者职称为高级工程师”的员工记录，又该如何筛选？
如图7所示，小李在数据区域外白^任一单元格区域（如A17:
B17）中输入被筛选的字段名称年龄”和职称”，在紧靠其下方的A18:
B18单元格区域中分别输入筛选条件">=3价口高级工程师”。
与以上方法类似，他选中将筛选结果复制到其他位置”按钮后，设置好列表区域”、条件区域”和复制到”的位置，确定，符合条件的记录就被筛选出来并复制到指定的单元格区域中了（如图8所示）。
h
B
C
D
F
1
工号
姓名
性别
年静
职称
冈住
2
3
1001
一郑X
女
23
助工
二至同
1002
土XX
男
34
高级工程师
一邨
■1
5
1003
1005
福XX
张XX
更
男
26
29
工程师工程师
二年恬一车上
7
S
式。
县.1007
除XX陈XX
*
男
54
34
高圾工程师
二利
_郭
1008
朱XX
女
40
高级工程师
二军僧
9
1011
石X
禺
47
高级工程师
二奉,
10
1012
陈XX
男
35
工程师
三车伊
111
13
1014
TX
女
33
高级工程师
三车间
1016
陆XX
男
41
三夺百
1017
刘X
女
22
助工
一车间
14
1024
味XX
男
26
会计师
财务不
15
1025
田XX
男
28
助工
三年至
16
:
哥有
in.c口E.cn
17
年龄
职称
18
19
>=30
高级工程用
16
17
年龄
职称
13
19
>=30
高级工程师
20
21
工号
姓名
性别
年龄
职称
lJj11*
冈便
22
23
1002
1006
王XX徐XX
男女
34
54
高级工程师高级工程师
二纲
可a|
24
1007
陈XX
男
34
I]
25
1008
朱XX
.女
40
高级工程师
二车,
a]
26
1011
石X
男
47
高级工程师
二帮
可
27
2S
1012
陈XX
男
35
工:
呵一
—il
4
1014
TX
女
33
工及
29
1016
陆XX
*
41
k£om；c1i
小提示：
在以上所有的筛选操作中，如果想使筛选结果不重复，只需选中高级筛选”对话框中的选择不重复的记录”复选框再进行相应的筛选操作即可。
充分地利用好，要找的资料就能很快到手！
想筛就筛，我筛得漂亮……”小李笑
了。
Excel提供的筛选”命令，可让我们方便地找到工作表中的记录。
筛选”包
括了自动筛选”和高级筛选”。
自动筛选”简便易用，但条件设置缺乏灵活性。
当自动筛选"不能满足我们的需要时，可以考虑使用高级筛选"，下面我们以图表1为例，一起来看看它的用法吧！
单列上具有多个条件
当我们要筛选姓名为张三”或田八”的学生。
首先在工作表中数据区域的下方输入筛选条件如图表2,然后依次单击数据”菜单、筛选"、高级筛选”，出现图表3对话框。
在方式”中选择将筛选结果复制到其他位置单击列表区域”右侧按钮，在工作表上选择图表1的数据区域，单击条件区域”右侧按钮，在工作表上选择图表2的区域，单击复制到"右侧按钮，在工作表中选择将筛选结果复制到的位置。
单击确定”后即可看到筛选结果。
当然，也可在方式”中选择在原有区域显示筛选结果”，则确定”后，可在原来数据区域的地方看到筛选后的结果，如果要恢复到原来的数据，单击菜单数据“、筛选”、全部显示”。
单行上具有多个条件
当我们要筛选班级为幺”并且成绩＞90”的学生。
在工作表中数据区域的下方输入筛选条件如图表4。
其他操作同上。
多行多列具有多个条件
当我们要筛选班级为幺”或成绩＜60”的学生。
在工作表中数据区域的下方输入筛选条件如图表5。
其他操作同上。
当我们要筛选一班的成绩大于90分或者小于60分的学生，二班的成绩大于90或小于60的学生。
在工作表中数据区域的下方输入筛选条件如图表6。
其他操作同上。
当我们要筛选一班和二班的成绩大于60且小于90的学生。
在工作表中数据区域的下方输入筛选条件如图表7。
其他操作同上。
经过上面的条件设置我们大概也能看出其中的规律了，当我们把条件放在同一行时，表示同时要满足的条件，即与"关系；当我们把条件放在不同行时，表示这些条件满足其一即可，即或”关系。
（严清秀）
小技巧
我们可以尝试把筛选条件写成姓名“张”，则经过上述操作后，我们看到的筛选结果有张三“、张七”，即把有所有姓名以张”起始的都能被找出来。
怎么实现精确筛选呢？
只需把筛选条件姓名”的下方输入“张三”。
为了防止系统认为我们输入的是个公式，可在编辑栏中输入“张三”，输完按回车后，单元格中显示的是张三”。
EXCE四子表格中四个常用函数的用法
现在介绍四个常用函数的用法：
COUNT（用于计算单元格区域中数字值的个数卜
COUNTA（用于计算单元格区域中非空白单元格的个数卜COUNTBLANK（用于计算单元格
区域中空白单元格的个数卜COUNTIF（用于计算符合一定条件的COUNTBLANK单元格个
数）。
结合例子将具体介绍：
如何利用函数COUNTA统计本班应考人数（总人数）、利用函数
COUNT统计实际参加考试人数、利用函数COUNTBLANK统计各科缺考人数、利用函数
COUNTIF统计各科各分数段的人数。
首先，在上期最后形成的表格的最后添加一些字段名和合并一些单元格，见图1。
一、利用函数COUNTA统计本班的应考人数（总人数）
因为函数COUNTA可以计算出非空单元格的个数，所以我们在利用此函数时，选取本班学生名字所在单元格区域（B3〜B12）作为统计对象，就可计算出本班的应考人数（总人数）。
1.选取存放本班总人数的单元格，此单元格是一个经过合并后的大单元格（C18〜G18）;
2.选取函数；单击菜单插入/函数”或工具栏中的函数按钮f*,打开粘贴函数”对话
框，在函数分类”列表中选择函数类别统计”，然后在函数名”列表中选择需要的函数“COUNTA,按确定”按钮退出粘贴函数”对话框。
3.选取需要统计的单元格区域；在打开的函数向导”对话框中，选取需要计算的单元
格区域B3〜B13,按下回车键以确认选取；函数向导”对话框图再次出现在屏幕上，按下确
定”按钮，就可以看到计算出来本班的应考人数（总人数）了。
二、利用COUNT、COUNTBLANK和COUNTIF函数分别统计各科参加考试的人数、统计各科缺考人数、统计各科各分数段的人数
我们在输入成绩时，一般情况下，缺考的人相应的科目的单元格为空就可以了，是0
分的都输入0。
（一）统计语文科的参加考试人数、缺考人数、各分数段的人数。
1.用函数COUNT统计语文科的参加考试人数。
单击存放参加语文科考试人数的单元格C19,然后按照前面的操作步骤，首先在函数分类”列表中选择函数类别统计”，在函
数名”列表中选择需要的函数“COUNT;其次按照上面〜、3”选取单元格区域的操作方法，
选取需要统计的单元格区域（C3〜C12）,然后回车确认，单击函数向导”对话框确定"按钮，就可以看到计算出来的结果
。
2.用函数COUNTBLANK统计语文科的缺考人数。
单击存放语文科缺考人数的单元格C20,然后按照上面的操作方法，在统计”类别中选取函数COUNTBLANK,并进行需要统计单元格区域（C3〜C12）的选取，直到得出结果。
3.用函数COUNTIF分别统计出语文科各分数段的人数。
（1）统计90分（包才90分）以上的人数（表中为“9吩以上”：
）单击存放此统计人数的单元格C21,然后选取函数，即选取统计”类别中的函数“COUNTIF,然后单击函数向导”对话框中的“Ragan钻侧的按钮，以选取统计单元格的区域（C3〜C12）后，回到函数向导”对
话框中，再输入统计的条件：
“$#@62;=90如图2。
单击确定”按钮，就可以计算出结果了。
（2）统计大于或等于80分而小于90分的人数（表中为“8089分”：
）双击单元格C21进入编辑状态，可以看到统计90分以上的分数段的人数的公式如图3所示是：
=COUNTIF（C3:
C12,”$#@62;=90；）
要统计本分数段人数，我们只要双击C22,在其中输入计算公式：
=COUNTIF（C3:
C12；$#@62;=80）—COUNTIF（C3:
C12；$#@62;=90）回车后，即可计算出此分数段的人数。
（3）用同样方法，只要在C23、C24、C25三个单元格中，分别输入公式（可以通过复制粘贴后，修改数字快速完成）：
=COUNTIF（C3:
C12；$#@62;=7。
）—COUNTIF（C3:
C12；$#@62;=80）
=COUNTIF（C3:
C12；$#@62;=60）—COUNTIF（C3:
C12；$#@62;=7。
）
=COUNTIF（C3:
C12；$#@60;6。
）
输入完毕后，注意一定要以回车确定，即可分别统计出大于或等于70分而小于80
分”表中为“7079分”、）大于或等于60分而小于70分”表中为“6669分”、）小于60分”表中为不及格”）这三个分数段的各自的人数。
（二）统计其余各科的参加考试人数、缺考人数、各分数段的人数。
如前一期所述，用复制公式的方法，可以快速计算出其余各科的有关数据。
以上已经计
算出语文科的应考人数、缺考人数及各分数段的人数，选取范围（C19〜C25）,把鼠标指向刚
才选取的单元格区域的右下方（即填充句柄），待光标变为小黑十字时，按下鼠标左键，并向
右拖动，至G25松开鼠标，各科要统计的结果都出来了。
前两期对班级成绩，分别作了总分、平均分、最高分、最低分、应考人数、缺考人数、分数段等数据统计，这些数据可以用来衡量这个班的成绩的情况。
这一期，将首先介绍用函数
“RANK以最快的速度把本班的名次排出来，作为衡量学生个人在本班的学习情况；另外再介绍用“MEDIAN、"MODE、"STDEVP函数分别统计出各科成绩的中位数”、粉数”、标
准差”，以此衡量各科试题的质量（如试题的难易程度、离散程度等）情况。
一、用函数“RANK对总分排名次
（一）单元格区域的命名
先打开上期制作的表格（如图1）,如果用总分”来排名次，首先选取所有总分”数据
的单元格区域（H3〜H12）,然后单击菜单中的插入/名称/定义”，在弹出的定义名称”对话框中，在当前工作簿的名称”中输入或修改名称为总分”；在用用位置”栏中显示的就是刚
才选取的单元格区域（H3〜H12）,当然也可以通过单击其右侧的按钮重新选取单元格区域。
如果只定义一个名称，则可按确定”按钮退出；如果还要添加其它区域名称，可单击添加”
按钮，待命名完毕后，再按确定”按钮结束单元格区域的命名。
在此我们可以比较一下前
两期用选取”和现在用命名”区域的不同方法及用途：
利用选取”确定区域，预选区域不是
固定的，如果需要相对固定的区域，可以利用命名”，则以后的操作会比较简便，如果对某
个区域一旦命名，利用函数的时候，就可以按以下的方法确定单元格的区域，无须再去选取
区域了。
（二）选取函数确定排名
1.在图1的平均分”右边的单元格（J2）中输入名次”。
2.单击选取单元格J3,再选择统计”类的“RANK函数，则在弹出的粘贴函数”对话框中，一切设置如图2（图中的“H3是存放第一个学生总分的单元格，总分”则是刚才命名
的单元格区域名称。
此时不能在粘贴函数”对话框中，单击图2中“Ref右边的按钮去选取
单元格区域，否则后面利用复制方法统计其余各人的名次时，单元格的区域会发生变化；利
用命名的单元格区域，复制时其区域不会发生变化；如果只看其中一个人的名次，则可以利
用选取”的方法），单击确定”按钮，即可得出第一个学生的成绩排名。
然后选取单元格J3,
拖动其填充句柄至最后一名学生，马上得出全班的成绩排名。
而且名次是可以动态变化的，如果某人的某科成绩发生变化，所有排名也会随数据的变化而变化。
如果想把名次按从低到高的顺序进行排列，只要先选取范围（J3〜J12），然后利用菜单中的数据/排序”命令，对名次”进行递增”排序即可。
二、用“MEDIAN、"MODE、"STDEVP函数分别计算各科成绩的中位数"、软数”、标
准差”
中位数"、粉数"、标准差”是三个统计类”的函数，也是统计学中三个十分常用的概念，它是分析数据的分布、离散程度等标志的重要依据，下面通过对学生成绩的分布情况，分析每科试题的有关情况。
1.计算语文”的中位数”
先选取存放数据的单元格026,然后在统计”类函数中选取函数“MEDIAN,在弹出的粘贴函数”对话框中，单击“Nuberl右边的按钮，选取需计算的单元格区域（03-012）,然
后单击粘贴函数”对话框中的确定”按钮，就可计算出语文科的中位数（在选取的数据中，
中位数是它们的平均数）。
2.计算语文”的飨:
数"、标准差”
利用“MODE、"STDEVP两个函数，按照上述的方法，即可计算出语文”的粉数”、标准差”。
3.计算其余各科的中位数"、粉数"、标准差”
按照前两期介绍的复制方法，相信各位可以熟练地操作，计算出其余各科的中位数”、
粉数"、标准差”了，最后结果如图3。
三、数据分析
学会使用Excel中的一些常用函数，是为了使我们的工作更科学、更迅速、更轻松。
那么上述对成绩统计出来的数据，如何体现为教学服务呢？
下面简单介绍一下Excel中图表”
的应用，以便我们对各科的试题进行分析：
在Excel中图表”是反映表格数据的直观表现，通过图表可以非常迅速直观地对数据产
生总体上的认识，这正是统计学中，最常用的对数据分布的表现方式。
（一）使用图表向导"建立语文”分数的分布图表
1.选取图表类型：
可以先选取表格中某个空白的单元格，单击菜单中的插入图表”，
在弹出的图表向导一步骤1”对话框中，选择一种图表类型和子图表类型，如我们选择折
线图”。
单击确定”按钮后，进入图表向导—步骤2”。
2.选择图表源数据：
在弹出的步骤2对话框中，单击数据区域右侧的按钮，对话框消
失，按前面介绍过的方法选取数据区域（b21〜c25），回车确定选取后，单击下一步”进入图
表向导一步骤3”。
3.设置图表选项：
在弹出的步
升级会员 