Excel 2007高级筛选功能的应用

陈 瑛郑 毅

(1.武汉冶金管理干部学院信息部 湖北 武汉:430080;2.武汉钢铁工程技术集团自动化有限责任公司 湖北 武汉:430080)

Excel 2007高级筛选功能的应用

陈 瑛1郑 毅2

(1.武汉冶金管理干部学院信息部 湖北 武汉:430080;2.武汉钢铁工程技术集团自动化有限责任公司 湖北 武汉:430080)

通过对Excel 2007“数据”菜单中“高级筛选”命令的分析,设置高级筛选中条件区域的构造方法,能够弥补目前Excel 2007教材中对高级筛选功能过于简单的不足。介绍如何掌握高级筛选的应用技巧,便于有效地解决一些实际问题。

Excel 2007;高级筛选;条件区域

Excel 2007是一款功能强大的电子表格编辑制作软件,是个人及办公事物处理的理想工具,其筛选功能可以帮助我们从已有的复杂数据表中得到所需数据。

数据筛选是将工作表中所有不满足条件的数据暂时隐藏起来,只显示那些符合条件的数据。Excel提供了二种不同的筛选方式:自动筛选和高级筛选。其中“自动筛选”只能用于条件简单的筛选操作,不能实现字段之间包含“或”关系的操作;“高级筛选”则能够完成比较复杂的多条件查询,并能将筛选结果复制到其它位置。目前市场上的Excel 2007教材对高级筛选功能介绍过于简单,不利于使用者解决实际问题。

1 高级筛选的操作过程

高级筛选的操作过程分两步进行。

步骤一:输入高级筛选条件,打开“高级筛选”对话框。

(1)在要筛选的工作表的空白位置处,输入所要筛选的条件;

(2)在“数据”主菜单下的“排序和筛选”工具栏中(见图1),单击“高级”按钮。

步骤二:设置筛选方式,筛选出满足条件的结果。

(1)单击上述“高级”按钮后,打开“高级筛选”对话框(见图2)。

图1 排序和筛选

图2 高级筛选

(2)在“方式”下,选中“将筛选结果复制到其它位置”的单选按钮;

(3)单击“列表区域”右侧的拾取器按钮,进行单元格区域选取;

(4)单击“条件区域”右侧的拾取器按钮,选取输入的筛选条件单元格区域;

(5)单击“复制到”右侧的拾取器按钮,设置显示筛选结果的单元格区域;

(6)单击“确定”按钮。系统会自动将符合条件的记录筛选出来并复制到指定的单元格区域。

注意:若要通过隐藏不符合条件的行来筛选区域,请单击“在原有区域显示筛选结果”,系统会自动将符合条件的记录筛选出来并复制到指定的单元区域。

2 应用实例

Excel 2007高级筛选的关键之处在于正确地设置筛选条件,即建立条件区域。条件区域可以是通配符、文本、数值、计算公式和比较式。

在Excel中,条件区域构造的规则是:同一列中的条件表示“或”;同一行中的条件表示“与”。即AND只有所有条件都成立,整个条件都成立。还有“或”、“与”的复合条件,用公式创建条件等。为使读者有直观的认识,下面通过实例具体进行说明。

2.1 根据工作表某一列构造一个条件,条件都必须为真

使用通配符“＊”进行高级筛选。“＊”代表若干个字符。以《学生基本信息表》为例,查找姓“李”的所有学生的记录(见表1)。

操作步骤:

(1)建立条件区域:在数据区域外的任一单元格中输入被筛选的字段名称“姓名”,在其下方的单元格中输入筛选条件“李＊”(见表2)。

表1 学生基本信息表

表2 添加筛选区域

(2)使用“数据”选项卡上“排序和筛选”组中的“高级”命令,弹出“高级筛选”对话框,选择筛选方式中的“将筛选结果复制到其它位置”单选按钮(见图3)。

图3 高级筛选

(3)分别将“列表区域”设置为“＄A＄2:＄G＄13”;“条件区域”设置为“＄B＄15:＄B＄16”;“复制到”设置为“＄A＄18”。

(4)单击“确定”按钮,系统会自动将符合条件的记录筛选出来并复制到指定的从A18开始的单元格区域中(见表3)。

2.2 根据工作表某一列可构造区间查询条件,条件都必须为真

再以《学生基本信息表》为例。筛选年龄在1981/3/15至1983/7/4之间出生的学生的记录。

操作步骤:其它操作步骤同上,条件区域设置如下(见表4)。

表3 筛选结果

表4 条件区域

单击“确定”按钮,系统会自动将符合条件的记录筛选出来(见表5)。

表5 筛选结果

2.3 根据工作表多列构造单一条件,其中所有条件都必须为真

如果将筛选条件输入在同一行中,筛选时系统会自动将同时满足所有指定条件的记录筛选出来。即条件之间的关系为“与”关系。在《学生基本信息表》中,查找“性别”为男性、“专业”为机械工程,“入学成绩”在580分以上且是“籍贯”是江苏的学生记录。

操作步骤:其它操作步骤同上,在条件区域设置如下(见表6):

表6 条件区域

单击“确定”按钮,系统会自动将符合条件的记录筛选出来(见表7)。

表7 筛选结果

2.4 根据工作表多列构造复合条件,其中所有条件都必须为真

如果要表示“或”关系的条件,则要求在字段下方的不同行输入条件,筛选结果只满足其中任意一个条件。在《学生信息表》中,查找“性别”为男性、“专业”为机械工程,“入学成绩”在580分以上,“籍贯”不一定是江苏的学生记录等。

操作步骤:其它操作步骤同上,在条件区域设置如下(见表8)。

单击“确定”按钮,系统会自动将符合条件的记录筛选出来(见表9)。

表8 条件区域

2.5 根据公式的计算结果构造筛选条件,筛选出满足条件的学生纪录

公式的计算结果可以作为筛选条件,在《学生基本信息表》中,需要找出进入本校所有学生入学成绩的平均分,并筛选出高于平均分的全体同学的记录。

操作步骤:

建立条件区域:列标题D16和E16不写任何内容,但在选择“条件区域”时一定要选择。在E17中输入计算平均值的公式“=AVERA GE(F3:F13)”,回车后计算结果604直接显示在E17单元格中。在D17中输入筛选条件“=F3>=＄E＄17”,回车后D17显示为“FLASE”。(因为F3的数值601<604,见图4)

图4 条件区域

使用“数据”选项卡上“排序和筛选”组中的“高级”命令,选择筛选方式中的“将筛选结果复制到其它位置”单选按钮(见图5)。

单击“确定”。高于平均分的全体同学记录显示出来(见表10)。

将公式的计算结果作为筛选条件时务必注意如下两点:

(1)构造条件区域时:要求标题行不能和数据表中标题行相同,既可空白又可书写内容。上例条件区域标题D16和E16原是空白。现把列标题补上,D16写“满足”E16写“平均分”标题不同于《学生基本信息表》中的标题,其筛选结果和表12完全一致(见表11)。

图5 高级筛选

(2)若构造条件区域时,标题行和数据表中标题行相同,计算机则告知“引用无效”。

上例中找出进入本校所有学生入学成绩的平均分。用作条件的公式必须使用“相对引用”来引用列标。如“=AVERAGE(F3:F13)”。公式中的其他所有引用必须为“绝对引用”,如“=F3>=＄E＄17”中的“＄E＄17”。并且公式的计算结果以“TRUE”或“FALSE”形式出现。

表9 筛选结果

表10 筛选结果

2.6 根据比较式构造筛选条件,筛选出符合条件的记录

比较式“<>”是不等于的意思。用它进行高级筛选,可以解决很多实际问题。如每年收发教材会遇到如下情况,正常情况是应收数量和实收数量相等,但假如出现应收数量和实收数量不同,如何查漏补缺。用比较式很容易解决这个问题。

操作步骤:以《教材信息表》为例,见表12。

(1)建立条件区域:C16为条件区域的列标题,空白,在C17中输入“=C4<>D4”,单击确定。因为C4单元格数字不等于D4单元格的数字,C17单元格就显示为“TRUE”。

表11 筛选结果

表12 教材信息表

(2)使用“数据”选项卡上“排序和筛选”工具中的“高级”命令,“列表区域”为＄A＄3:＄d＄13,“条件区域”为＄C＄16:＄C＄17,“复制到”为＄A＄18区域(见表13)。

表13 添加筛选区域

(3)通过筛选,查找出应收数量和实收数量不符合的记录(见表14)。

注意:其中C16中可以不录入任何数据。构造条件区域时:要求标题行不能和数据表中标题行相同,既可空白又可书写非标题内容,但在选择“条件区域”时一定要选择。

3 结束语

利用Excel 2007高级筛选功能,关键在于巧妙构造条件区域。

(1)非公式筛选,条件区域的标题与数据区域标题保持一致。高级筛选前应将筛选条件输入在工作表的空白区域,且条件区域的位置要与原数据区域保持至少空一行或一列的位置。

(2)用公式创建条件时,必须使用非数据表标题,公式正确,就能计算出相应正确的结果。

(3)用比较式完成筛选应在条件区域的相应单元格中键入“=条目”,否则将不能对数据做出正确筛选。

[1] 博学工作室.EXCEL2007数据处理与分析范例精讲[M].北京:机械工业出版社,2008.

[2] 飞师教育.EXCEL2007中文版范例学[M].北京:电子工业出版社,2008.

[3] 沈 浩.EXCEL高级应用与数据分析[M].北京:电子工业出版社,2008.

Application of Excel 2007 Advanced Filter Function

CHEN Ying ZHENG Yi

Through analysis of"advanced filter"order in Excel 2007"data"menu,criteria range creating method is set up in advanced filter,with which the drawback that advanced filter function was too simple in Excel 2007 textbooks can be compensated.Meanwhile,some practical skills in the application of advanced filter are introduced so as to solve some practical problems.

Excel 2007;advanced filter;criteria range

TP315

A

1671-3524(2010)03-0042-05

(责任编辑:栗 晓)

2010-06-01

2010-06-25

陈瑛(1962～),女,教师.E-mail:chyingo@163.com