Excel制表技巧（14）自动筛选

        巧用Excel的“自动筛选”功能

        如图1所示的工作表（假设D列已填入数据）若要上报或打印，还必须对F列和G列做隐藏处理（若直接删除，填充到D列的数据将全部丢失）。我在实际工作中，摸索出了一种方法，即利用Excel“自动筛选”功能快速实现部门名称的录入，下面仍以“学校职工情况表”为例，说明其操作步骤。

    1. 按图1格式输入表格基本数据（F列、G列不需要录入）。

图一

    2. 单击“数据”菜单，选中“自动筛选”，则在每个字段右侧出现一个下箭头的按钮，单击“部门代码”右侧的按钮，打开一个下拉列表如图2所示。

图二

    3. 单击下拉列表中的任意一个“部门代号”，例：单击“1”，则“部门代码”为“1”的所有记录全部显示在屏幕上（如图3）。

图三

    4. 在图3中选中“所在部门”列的单元格区域，输入“教务科”，按下“Ctrl”键不松开，再按回车键，则“部门代码”为“1”的记录的“所在部门”列都填充上了“教务科”（如图4）。

图四

       5. 重复步骤3和步骤4可完成所有部门的填写操作。

      Excel多条件求和的三种方法

　　图1是一种典型的员工基本情况登记表，现在我们要求统计性别为“男”性、职称为“工程师”的员工的工资总和，可以用下面三种方法来实现。

　方法一、自动筛选法 

　　1、打开登记表，选中数据区域任意一个单元格，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态（此时，每个列标题右侧出现一个下拉按钮，参见图1）。 

　　2、选中J103（此处假定有100名员工）单元格，输入公式：=SUBTOTAL(9,J3:J102)，用于统计基本工资数据。 

　　3、先点击“性别”列右侧的下拉按钮，在随后弹出的下拉列表（如图2）中选择“男”；再点击“职称”列右侧的下拉按钮，在随后弹出的下拉列表（参见图2）中选择“工程师”。

　　符合条件的数据被筛选出来，工资之和出现的J103单元格中（如图3）。 

　　方法二、数组公式法 

　　打开登记表，选中保存统计结果数值的单元格（如J104），输入公式：=SUM((C3:C102="男")*(I3:I102="工程师")*(J3:J102))，输入完成后，按下“Ctrl+Shift+Enter”组合键确认公式即可。 

　　注意：这是一个数组公式，输入完成后，不能直接用“Enter”键进行确认，需要用“Ctrl+Shift+Enter”组合键进行确认，确认完成后，公式两端出现一对数组公式标志、一对大括号（{}，如图4）。 

       Excel高级筛选题

   学   号       姓名    数学  语文  英语 物理 
90220002 张成祥     97    94     93     93 
90220013 唐来云     80    73     69     87 
90213009 张  雷       85    71    67     77 
90213022 韩文歧     88     81    73    81 
90213003 郑俊霞     89     62    77    85 
90213013 马云燕     91    90     76    95 
90213024 王晓燕     86    79     80    93 
90213037 贾莉莉     93    73     78    88 
90220023 李广林      94   84     60    86 
90216034 马丽萍      55   59     98    76 
91214065 高云河      50   77     84    56 
91214045 王卓然      59   74     77    65 
条件为： 在表中筛选出符合“数学、语文、英语、物理四科成绩均大于90分”或“数学与物理均小于60分”条件的记录。

         一、用高级筛选完成。 
         1、下内容输入到H1至K3单元格： 
         数学    语文  英语 物理 
          >90    >90    >90   >90 
          <60                       <60 
         (注:两个<60分别在H3和K3单元格) 
         2、选定原数据范围(A1：F13)，点“数据”->“筛选”->“高级筛选”，“数据区域”已经自动填入内容为“$A$1:$F$13”，选中“将筛选结果复制到其他位置”，条件区域可用鼠标选定或直接输入“$H$1:$K$3”，“复制到”后面填入内容“$A$21:$F$33”，点“确定”。 

         二、利用函数完成。 
        1、在G2单元格输入公式: 
        =IF(AND(C2>90,D2>90,E2>90,F2>90),"四科成绩均高于90分",IF(AND(C2<60,F2<60),"数学物理均少于60分","")) 
将公式向下复制到相应单元格。 
        2、选定全表按G列递减排序。

        利用 Excel高级筛选功能删除重复记录

        1、打开一份带有重复记录的Excel文档.如图一所示(注:本图已用photostop处理,其中彩色部分为重复记录)   

             

                                                                          图一

       2、选中图表中的所有记录(注意,此时应将每列的标题行也选择上,否则筛选完的数据表中将不再包含有该标题行) , 执行“数据”->“筛选”->“高级筛选”命令.

       3、选中“将筛选结果复制到其他位置”，并点击“复制到”后面的范围按钮来选择一块区域以存放筛选后的数据(注意,此处千万不要与原数据所处单元格相重合,否则数据表将会混乱),最后,勾选“选择不重复的记录”复选框后,点“确定”按钮即可.如图二所示:

             

                                           

                                                                       图二

        4、此时,Excel便会将所有重复记录自动删除掉,确认无误后,你就可以将新生成的数据清单拷贝到新的工作表中继续使用了.如图三所示

                      

             

         Excel自动、高级筛选实例剖析

        图1是一张普通的成绩登记表，如果需要将某（几）门学科成绩不及格的学生数据单独保存到另外一个工作表中，可以通过“自动筛选”和“高级筛选”来实现。

       下面我们详细介绍两个实例。文章末尾提供原文件供大家下载参考。 
                     

          

         实例一、将“语文”和“数学”成绩同时都不及格的学生数据保存到Sheet2工作表中 1、启动excel，打开相应的成绩表，选中数据表中任意一个单元条，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态（如图2）。 

        2、选中D1单元格（即“语文”标题字段所在的单元格），按其右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项（参见图2）。 3、此时，系统弹出“自定义自动筛选方式”对话框（如图3），单击左边方框中的下拉按钮，在随后弹出的下拉列表中，选择“小于”选项，然后在后面的方框中输入数值60（参见图3，此处，假定小于60分为不及格），确定返回。 

        4、仿照上面的操作，进一步设置一下“数学”的筛选条件，确定后，我们需要的数据即刻被筛选出来。 

        5、选中筛选后的数据区域，执行“编辑定位”命令，打开“定位”对话框，按其中的“定位条件”按钮，打开“定位条件”对话框（如图4），选中其中的“可见单元格”选项，确定返回。

        6、执行一下“复制”操作，然后切换到Sheet2工作表中，选中保存区域的第一个单元格（如A1），执行一下“粘贴”操作即可（如图5）。

       Excel姓名筛选

       在Excel中有一列学生姓名，凡需补考的学生姓名后都有一个“??”号，如何将补考学生姓名快速筛选出来呢？

    分析：用常规方法是无法将姓名后含“??”号的学生筛选出来的。我们可通过“替换”命令将姓名后的“??”号替换为“aaaaa”，再利用函数统计出每个单元格的字符数，最后按单元格字符数的多少进行“降序”排序，进而筛选出补考学生。

   难点：在Excel中，“??”作为通配符，当利用“替换”命令将“??”号替换为“aaaaa”时所有单元格中的内容都会被替换为“aaaaa”。

    解决方法：函数SUBSTITUTE可以帮助我们实现“替换”命令无法完成的任务。要筛选补考学生姓名问题只需以下四个步骤。

    1．替换“??”号为“ａａａａａ”。

    若A列中从A2起都是学生姓名，则在B2单元格中输入公式：“=SUBSTITUTE(A2,"??","aaaaa")”后回车，选中B2单元格，双击填充句柄，将该公式复制到最后一单元格中。这样我们就完成了在Excel中将通配符“??”号进行替换的任务。若是要将Excel中的“??”号进行删除，则只需将上述公式改为“=SUBSTITUTE(A2,"??",)”即可。

    2．统计替换后的单元格中的字符个数

    在C2单元格中输入公式“=LEN(B2)”后回车，并将该公式复制其他单元格。

    3． 按字符个数由大到小排序，筛选出补考学生名

    执行“数据排序”命令，按“字符数”关键字进行降序排列。由于学生姓名最少也有两个汉字，我们将“??”号又替换为5个字符，所以凡字符个数大于等于7的一定是补考学生。

    4． 删去筛选出姓名中的字母ａ

    在D2单元格中输入公式“=SUBSTITUTE(B2,"a","")”后回车，选中这一单元格，利用填充句柄将该公式进行复制，删去所有补考学生姓名后的字母“a”。

       使用高级条件筛选
全部显示
全部隐藏
在可用作条件 （条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。）区域的区域上方插入至少三个空白行。条件区域必须具有列标签。请确保在条件值与区域之间至少留了一个空白行。
外观示例

类型 销售人员 销售

类型 销售人员 销售
饮料 Suyama 5122
肉类 Davolio 450
农产品 Buchanan 6328
农产品 Davolio 6544

在列标志下面的一行中，键入所要匹配的条件。
条件示例

单列上具有多个条件

如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如，下面的条件区域显示"销售人员"列中包含"Davolio"、"Buchanan"或"Suyama"的行。

销售人员
Davolio
Buchanan
Suyama

多列上具有单个条件

若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面的条件区域将显示所有在"类型"列中包含"农产品"、在"销售人员"列中包含"Davolio"且"销售额"大于 $1,000 的数据行。

类型 销售人员 销售
农产品 Davolio >1000

某一列或另一列上具有单个条件

若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，下面的条件区域将显示所有在"类型"列中包含"农产品"、在"销售人员"列中包含"Davolio"或销售额大于 $1,000 的行。

类型 销售人员 销售
农产品
Davolio
>1000

两列上具有两组条件之一

若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在"销售人员"列中包含"Davolio"且销售额大于 $3,000 的行，同时也显示"Buchanan"销售商的销售额大于 $1,500 的行。

销售人员 销售
Davolio >3000
Buchanan >1500

一列有两组以上条件

若要找到满足两组以上条件的行，请用相同的列标包括多列。例如，下面条件区域显示介于 5,000 和 8,000 之间以及少于 500 的销售额。

销售 销售
>5000 <8000
<500

将公式结果用作条件

可以将公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）的计算结果作为条件使用。用公式创建条件时，不要将列标签作为条件标签使用；应该将条件标签置空，或者使用区域中的非列标签。例如，下面的条件区域显示在列 C 中，其值大于单元格区域 C7:C10 平均值的行。

=C7>AVERAGE($C$7:$C$10)

注释

用作条件的公式必须使用相对引用 （相对单元格引用：在公式中，基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。如果复制公式，相对引用将自动调整。相对引用采用 A1 样式。）来引用列标签（例如，"销售"），或者引用第一个记录的对应字段。公式中的其他所有引用都必须为绝对引用 （绝对单元格引用：公式中单元格的精确地址，与包含公式的单元格的位置无关。绝对引用采用的形式为 $A$1。），并且公式的计算结果必须为 TRUE 或 FALSE。在本公式示例中，"C7"引用区域中第一个记录（行 7）的字段（列 C）。
可在公式中使用列标签来代替相对单元格引用或区域名称。当 Microsoft Excel 在包含条件的单元格中显示错误值 #NAME? 或 #VALUE! 时，可忽略这些错误，因为它们不影响区域的筛选。
Microsoft Excel 在计算数据时不区分大小写。
所有以该文本开始的项都将被筛选。例如，如果您键入文本"Dav"作为条件，Microsoft Excel 将查找"Davolio"、"David"和"Davis"。如果只匹配指定的文本，可键入下面的公式，其中"text"是需要查找的文本。 =&apos&apos=text&apos&apos

如果要查找某些字符相同但其他字符不一定相同的文本值，则可使用通配符。

通配符

以下通配符可作为筛选以及查找和替换内容时的比较条件 （条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。）。

请使用 若要查找
（问号） 任何单个字符
例如，sm?th 查找"smith"和"smyth"
*（星号） 任何字符数
例如，*east 查找"Northeast"和"Southeast"
~（波形符）后跟 ?、* 或 ~ 问号、星号或波形符
例如，"fy91~?"将会查找"fy91?"

单击区域中的单元格。
在"数据"菜单上，指向"筛选"，再单击"高级筛选"。
若要通过隐藏不符合条件的数据行来筛选区域，请单击"在原有区域显示筛选结果"。
若要通过将符合条件的数据行复制到工作表的其他位置来筛选区域，请单击"将筛选结果复制到其他位置"，然后在"复制到"编辑框中单击鼠标左键，再单击要在该处粘贴行的区域的左上角。

在"条件区域"编辑框中，输入条件区域的引用，并包括条件标志。
如果要在选择条件区域时暂时将"高级筛选"对话框移走，请单击"压缩对话框" 。

若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。

提示

       您可以将某个区域命名为"Criteria"，此时"条件区域"框中就会自动出现对该区域的引用。您也可以将要筛选的数据区域命名为"Database"，并将要粘贴行的区域命名为"Extract"，这样，这些区域就会相应地自动出现在"数据区域"和"复制到"框中。
将筛选所得的行复制到其他位置时，可以指定要复制的列。在筛选前，请将所需列的列标复制到粘贴区域的首行。而当筛选时，请在"复制到"框中输入对被复制列标的引用。这样，复制的行中将只包含已复制过列标的列。

        Excel 筛 选 数 据   

       筛选数据清单可以使我们快速寻找和使用数据清单中的数据子集。筛选功能可以使Excel只显示出符合我们设定筛选条件的某一值或符合一组条件的行，而隐藏其他行。 在Excel中提供了“自动筛选”和“高级筛选”命令来筛选数据。一般情况下，“自动筛选”就能够满足大部分的需要。不过，当我们需要利用复杂的条件来筛选数据清单时，就必须使用“高级筛选”才可以。
       对于数据清单，我们可以在条件区域中使用两类条件。一是：对于单一的列，可以使用多重的比较条件来指定多于两个的比较条件；例如，显示欠款大于一万、十万或一百万的客户。二是：当条件是计算的结果或需要比较时，可以使用计算条件。例如，若只要显示其销售额大于单元格 H2 中的季度平均值的行，请键入“=销售额> $H $2”（美元符号表示单元格引用为绝对单元格引用）。
     12.4.1 使用“自动筛选”来筛选数据
    如果要执行自动筛选操作，在数据清单中必须有列标记。其操作步骤如下：
    (1) 在要筛选的数据清单中选定单元格。
    (2) 执行“数据”菜单中的“筛选”命令，然后选择子菜单中的“自动筛选”命令。
    (3) 在数据清单中每一个列标记的旁边插入下拉箭头，如图12-9所示。

    (4) 单击包含想显示的数据列中的箭头，我们就可以看到一个下拉列表，如图12-10所示。

    (5) 选定要显示的项，在工作表我们就可以看到筛选后的结果，如图12-11所示。

    12.4.2 建立自定义“自动筛选”
    对于上一节中的筛选，我们还可以通过使用“自定义”功能来实现条件筛选所需要的数据。
    如果要符合一个条件，可以按照下列步骤执行：
    (1) 在要筛选的数据清单中选定单元格。
    (2) 执行“数据”菜单中的“筛选”命令，然后选择子菜单中的“自动筛选”命令。
    (3) 在数据清单中每一个列标记的旁边插入下拉箭头。单击包含我们想显示的数据列中的箭头，就可以看到一个下拉列表。
    (4) 选定“自定义”选项，出现一个自定义对话框，如图12-12所示。

    (5) 单击第一个框旁边的箭头，然后选定我们要使用的比较运算符。单击第二个框旁边的箭头，然后选定我们要使用的数值。在本例中设定的条件为，所有“销售数量<20”的记录。 单击“确定”按钮，就可以看到如图12-13的筛选结果。

    如果要符合两个条件，可以按照下列步骤执行：
    (1) 在要筛选的数据清单中选定单元格。执行“数据”菜单中的“筛选”命令，然后选择子菜单中的“自动筛选”命令。
    (2) 在数据清单中每一个列标记的旁边插入下拉箭头。单击包含想显示的数据列中的箭头，就可以看到一个下拉列表。
    (3) 选定“自定义”选项，出现一个自定义对话框。单击第一个框旁边的箭头，然后选定我们要使用的比较运算符。 在第二个框中，键入想和比较运算符一起利用的数。选定“与”选项按钮或“或”选项按钮。如果要显示同时符合两个条件的行，选定“与”选项按钮；若要显示满足条件之一的行，选定“或”选项按钮。再在第二个框中指定第二个条件，如图12-14所示。
    (4) 最后按下“确定”按钮，就可以看到图12-15的显示。


    12.4.3 移去数据清单的筛选
    对于不再需要的筛选数据，我们可以采用下列方法之一将之移去。
    方法一：移去列的筛选，单击设定条件列旁边的箭头，然后从下拉式数据列表中选定“全部”，如图   12-16所示。

    方法二：重新显示筛选数据清单中的所有行，执行“数据”菜单上的“筛选”菜单中的“全部显示”命令。
    12.4.4 使用高级筛选
    使用自动筛选命令寻找合乎准则的记录，且方便又快速，但该命令的寻找条件不能太复杂；如果要执行较复杂的寻找，就必须使用高级筛选命令。执行高级筛选的操作步骤如下：
    (1) 在数据清单的前方插入几个空行，并建立条件区域，如图12-17所示。在本例中我们设定的条件是“销售数量>=10”的地区。

    (2) 在数据清单中选定单元格。执行“数据”菜单的“筛选”菜单中的“高级筛选”命令，出现一个如图12-18所示的对话框。

    (3) 在“方式”框中选定“在原有区域显示筛选结果”选项按钮。在“数据区域”框中，指定数据区域。在“条件区域”框中，指定条件区域，包括条件标记，结果如图12-19所示。若要从结果中排除相同的行，可以选定“选择不重复的记录”选择框。

    （4） 最后按下“确定”按钮即可，之后我们就会看到如图12-20的显示结果。

    12.4.5 设定条件区域
    在使用“高级筛选”命令前，我们必须为之指定一个条件区域，以便显示出符合条件的行。我们可以定义几个条件 (称为多重条件) 来选定符合所有条件的行，或显示符合一组或另一组条件的行。
    对于设定条件区域的基本步骤，无论是输入比较条件还是使用计算条件都是相同的。我们可以使用以下的步骤来建立条件区域：
    (1) 在数据清单的前方插入几个空行。
    (2) 在首行选定单元格中输入字段名称，例如“销售数量”。
    (3) 在其下方的单元格中输入条件，例“ >=10”。
    (4) 重复步骤 (2) 和 (3) 指定其它条件。
    注意：当利用比较条件时，条件标记必须和我们想评价的列标记相同。在列标记下面的行中，键入需要的条件，利用“数据”菜单上的“筛选”菜单中的“高级筛选”命令，来显示符合指定条件的行。
    如果要显示符合所有条件的行
    如果要对不同的列指定多重条件，请在条件区域的同一行中输入所有的条件。如果要对相同的列指定一个以上的条件，或某一值域，我们可以通过多次输入列标记来实现。例如，条件区域如图12-21所示，结果如图12-22所示。


    如果要显示符合一个或另一个条件的行
    如果要相同的列指定不同的条件，请把条件输入在不同的行上，如图12-23中的设定。根据所设定的条件，它将显示每一个特定的行，即指满足“销售数量 <10”或者“销售数量 >18”的所有记录。该操作也就是满足逻辑“或”，即符合指定条件中的任一个即可，结果如图12-24所示。



     12.4.6 设定准则的一些注意事项
     前面在用数字当做准则时，用过＞20。在做比较时,可以用的比较运算符号如下所示:
     = 等于
     > 大于
     >= 大于或等于
    < 小于
    < = 小于或等于
    < > 不等于
    例如:要找大于或等于60000的记录，用> =60000；不等于70000,用<>70000。
12.4.7 复制符合复杂条件的筛选数据
    将符合复杂条件的筛选数据复制到一个新的位置是一个十分重要的功能。利用该功能，我们可以整理出各种类型的报告。例如，我们可以从数据库中整理出一份关于华邦POS销售大于30套的地区。其操作步骤如下：
    （1）建立条件区域。
    （2）执行“数据”菜单中“筛选”菜单的“高级筛选”命令，出现一个高级筛选对话框。
    （3）在“方式”框中选定“将筛选结果复制到其它位置”单元项。在“数据区域”框中，指定数据区域。在“条件区域”框中，指定条件区域，包括条件标记。在“复制到”框中，指定我们要将筛选过的行复制到的位置。若要从结果中排除相同的行，可以选定“选择不重复的记录”复选框。设置好的对话框如图12-25所示。

    （4）按下“确定”按钮即可，之后我们就会看到如图12-26的显示。

    注意：利用这种方法，我们只可以将筛选过的数据复制到当前的工作表中。如果要将筛选过的数据粘贴到不同的工作表中，必须先切换到新的工作表，然后在那里执行“高级筛选”操作。

       Excel数据巧妙分离

    当你看到某人在浪费宝贵的时间时，是否感觉有些气恼？但自己不能帮上忙更是痛苦的事。最近这种事就发生在我的一个同事身上，在Excel中有些数据需要分离，本来一个字符串函数就可以很快解决的问题，他却去一个一个的处理，这里就让我们来看看其简单的方法，并希望大家举一反三，提高工作效率。 

　　首先我们来看看原始数据，这是一组产品代号，其中包括三个部分（如下图所示）。第一部分是“K+数字”为产品编码，第二部分是“B+数字”为价格等级，第三部分的三个数字代表顾客。现在为了统计方便，需要将所有代号的三个部分分离出来，置于不同的三列中。

　　其实使用一个字符串函数就可以简单地完成这个工作，首先分离前三个字符，在B2单元格中输入“=Left(A2,3) ”，选中B2单元格，鼠标移至右下角，当光标变成“十”字形状时，拖动鼠标到最下面的单元格，复制函数到第一列的所有单元格中，这样结果就显示出来了（如下图所示）。

　　然后分离中间两个字符，这就需要使用Mid函数，起形式为“=Mid(source_string,start_position,length)”，其意思是比较容易理解的，第一个参数是原始字符串，第二个参数是提取字符的起始位置，最后一个参数是提取字符的的个数。在C2单元格中输入函数“=Mid(A2,4,2) ”，利用和上面同样的方法把函数复制到C列的其它单元格中就可以了。

　　最后提取后面的三个字符，这里就要用到Right函数，其形式为“=Right(source_string,number_of_characters)”，在D2单元格中输入函数“=Right(A2,3) ”，利用和前面同样的方法把函数复制到D列的其它单元格中就可以了。

　　最后我们感觉这其实是比较简单的操作，但是我们并不能只看到它的表面，应该把这种方法举一反三，学习更多的简便方法，在数据处理过程中提高工作效率。