在 Excel 中使用高级筛选功能

Excel  是存放大量数据的地方，但它又不是个专业的数据库。然而Excel 用户对数据的操作要求却越来越靠近数据库。比如筛选这件事吧，Excel 的 AutoFilter 功能大多数人都会用，也许还会自定义一下，但是条件一多，AutoFilter 功能就应付不过来了。例如，AutoFilter 的自定义只支持两个判断式的Or 或And关系，当我们在员工表里查找“张三”和“李四”的信息时，还能发挥作用，再加一个“王五”就不行了。

在数据库里，我们对数据进行筛选的要求可以通过极其复杂的SQL 语句来实现，但是Excel 不像Access，并没有专业的查询模块，不过Excel 提供的 Advanced Filter 功能倒是和SQL 语句指定的准则“差可拟”。根据我在工作中的观察，使用这个功能还是需要培训一下的，要真正理解Advanced Filter 功能的内涵，才能在处理数据时得心应手，享受它的好处。

Advanced Filter 在筛选条件比较复杂时就可以显示身手了。使用Advanced Filter 功能时，准则不是在对话框里输入的，而是在单元格里，这样准则就可多可少，大大增加了灵活性。我们输入准则的单元格被称为“Criteria range”， Advanced Filter 功能只要知道“Criteria range”在哪里就行了。

Advanced Filter 功能要配置的参数有5项。  

1. List Range，也就是原始数据。这块数据要有列头，类似于数据库表的字段名。原始数据在工作表里的位置没有什么特别规定，不需要从第一行第一列开始，只要这块数据看上去像个表的样子，有列头(字段名)和数据。
2. Criteria Range，准则区域。准则区域也需要列头，表示对哪列，也就是对哪个字段按准则进行筛选。准则区域的列数不需要和原始数据的列数一样，只要把作为准则的列放在这里就行了。比如，原始数据有ID，客户，金额，销售人员，时间五列，我们的任务是把某些销售员在某段时间的销售记录显示出来，我们的准则区域就只需要“销售人员”和“时间”两列。如果对SQL 语句很了解，就知道准则区域的作用相当于

   where  (销售人员=value1 OR 销售人员=value2) AND (时间=value3)

   准则区域在工作表上放置的位置并没有特别要求，只要不和原始数据覆盖就好，我比较喜欢和原始数据隔开几列左右并排放。切记，准则的列名一定要和原始数据的列名一致，多个空格都不行，否则就是两个字段名了。有时候，这是 Advanced Filter 功能失败的原因。
3. 就地过滤还是复制到新的地方。如果在VBA代码里调用 AdvancedFilter 方法，这里的值就是Action:=xlFilterCopy 或 Action:=xlFilterInPlace
4. 如果选择了复制到新地方，那么还要提供希望复制到的区域。
5. 过滤时是否只保留唯一的记录。 Unique:=True 或 False。 

微软有个网页Examples of complex criteria 详细讲了复杂准则的设置。其实使用Advanced Filter 功能的关键就是如何写准则区域。

我这里借用一下微软的示例数据，稍作改动，我说了我喜欢原始数据和准则数据左右并排放。(原始的示例数据是上下并排放的。)

	A	B	C	D	E	F	G
1	Type	Salesperson	Sales		Type	Salesperson	Sales
2	Beverages	Suyama	$5122
3	Meat	Davolio	$450
4	produce	Buchanan	$6328
5	Produce	Davolio	$6544

一、一个列里上下写多个数据

这里的列指的是准则区域的列。在同一个列头下，出现在不同行的数据表示OR 的关系。比如在F2 填上“Davolio”，在F3 填上“Buchanan”，并指定准则区域为 (F1:F3)，表示把包含“Davolio”或“Buchanan”的行找出来。 相当于SQL 语句的 where Salesperson = "Davolio" OR Salesperson = "Buchanan"。

注意：空白的单元格表示全部记录。比如，上例中F4 是空白的，指定准则区域为(F1:F4) 会返回所有记录。

二、条件来自多个列并且所有条件要为真

做法就是在同一行不同的列里输入条件。出现在同一行的数据之间是AND 的关系，出现在同一列的数据之间是OR 的关系。

例如，要找类型为“Produce”，并且金额大于$1000的记录，就在E2 输入“Produce”，在G2 输入“>1000”，并指定准则区域为(E1:G2)。相当于SQL 语句的 where  Type = "Produce" AND Sales > 1000。

我们这里的条件出现了“>1000”，如果没有指定比较运算符，默认是等于的关系(=)。如果要明确用等于号来指定相等比较，在单元格里输入 =''=entry''。不管是文本还是数值比较，都用这种字符串方式表示。entry 是要比较的文本或数值。之所以用这种形式，是因为在Excel 里等于号(=)用来表示后面输入的内容要当公式处理。

例如，我们输入="=Davolio"，Excel 显示的是 =Davolio。输入="=3000"，显示 =3000。

三、条件来自多个列任意条件为真

Boolean logic:   (Type = "Produce" OR Salesperson = "Davolio")

从上面两个例子看到，Advanced Filter 功能同一行的准则之间是AND 的关系，同一列的准则之间是OR 的关系。其实，只要是出现在不同行，准则之间就是OR 的关系。如果我们要以多个字段为条件，任意条件成立就可以，只要在对应的列名下，在不同行输入条件就可以。

例如，我们要查找类型是“Produce”或销售人员是“Davolio”的记录。就在E2 输入“Produce”，在F3 输入“Davolio”，并指定准则区域为(E1:F3)。相当于SQL 语句的 where Type = "Produce" OR Salesperson = "Davolio"。

四、多套准则，每套准则都包含了多列为条件

直接看要求吧，找出Davolio 大于3000 美元的销售记录，或者找出Buchanan 大于1500 美元的销售记录。用SQL 语句表达要求就是 where  (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500)。

“Davolio 大于3000 美元的销售记录”算是一套准则，“Buchanan 大于1500 美元的销售记录”是另一套准则。两套准则之间是OR 的关系，那么两套准则是在不同行输入的。再看每套准则内部，这两套准则的要求是一样的，我们只要分析一套就可以了。“Davolio 大于3000 美元的销售记录”用到两个字段，两者是AND 的关系，也就是说“=Davolio”和“>3000” 要出现在同一行。我们在F2  输入“Davolio”，在G2 输入“>3000”。同理，在F3  输入“Buchanan”，在G3 输入“>1500”。最后，指定准则区域为 (E1:G3)。

五、多套准则，每套准则都以同一列为条件

这里的要求是，找出销售额大于6000 并且小于6500 的记录，或者找出销售额小于500 的记录。用SQL 语句表达要求就是 where  (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500)。

对于“销售额大于6000 并且小于6500”这种一个字段同时要满足两个条件的筛选要求，我们对于这个字段就要再加一个同样的列头了。前面二的例子是两个不同的字段同时满足条件，这个例子是同一个字段同时满足两个条件。

	A	B	C	D	E	F	G	H
1	Type	Salesperson	Sales		Type	Salesperson	Sales	Sales
2	Beverages	Suyama	$5122				>6000	<6500
3	Meat	Davolio	$450				<500
4	produce	Buchanan	$6328
5	Produce	Davolio	$6544

条件表示方法如上表所示，并指定准则区域为(G1:H3)。

六、文本中部分字符匹配，即字符串文本中做模糊查询

输入准则时不要用等于号 (=)，直接输入要查找的部分字符，Excel 会列出以这些字符开头的所有记录。比如，输入 Dav 作为准则，Excel 会找出“Davolio”，“David”和“Davis”等这样以“Dav”开头记录。

还有种方法是用通配符* 或者?，这样字符出现在什么地方都可以。

小技巧：~ (tilde)符号用来找* 、?，或者~。例如，fy91~? 找出 "fy91?"。

七、以公式的返回值作为准则

在准则里也可以利用公式返回值，但要记住以下重要的几点：

• 公式的返回值必须是 TRUE 或 FALSE。
• 按正常方式输入公式。
• 准则使用公式的列不要用原始数据中的列头(字段名)，或者保持列名为空白，或者定义一个非原始数据中列名的新列名。
• 作为准则的公式必须使用单元格的相对引用来指向数据区第一行中对应的单元格。
• 公式里的所有其它引用必须使用绝对引用，即用$符号，比如$A$1。

看两个例子:

1、找出金额大于平均值的记录

	A	B	C	D	E	F	G	H
1	Type	Salesperson	Sales		Type	Salesperson	Sales	Average
2	Beverages	Suyama	$5122					=C2>AVERAGE($C$2:$C$5)
3	Meat	Davolio	$450
4	produce	Buchanan	$6328
5	Produce	Davolio	$6544

H1 是新定义的一个列名“Average”，C2 是相对引用，表示要过滤的“Sales”栏，即C 栏中第一行数据所在单元格，AVERAGE($C$2:$C$5) 里的参数都是绝对引用。指定准则区域为 (H1:H2)。

2、字符串筛选时大小写敏感

Advanced Filter 功能在筛选时，字母大小写是不分的。比如在前面第二例中，“Produce”为条件，会找出类型为“Produce”和“produce”的记录。如果我们强制要求大小写敏感，只找出类型为“Produce”的记录，就要利用公式帮忙了。

	A	B	C	D	E	F	G	H
1	Type	Salesperson	Sales		Type	Salesperson	Sales	ExactMatch
2	Beverages	Suyama	$5122					=EXACT(A2, "Produce")
3	Meat	Davolio	$450
4	produce	Buchanan	$6328
5	Produce	Davolio	$6544

H1 是新定义的一个列名“ExactMatch”，A2 是相对引用，表示要过滤的“Type”栏，即A 栏中数据区第一行的单元格，指定准则区域为 (H1:H2)。

附录：

把准则区域命名为 Criteria，原始数据区域命名为 Database，要复制到的区域命名为Extract，这样Advanced Filter 功能对话框的几个参数会自动填上。

如果要把过滤后的结果集复制到新的地方，可以只复制指定的列。先把需要的列名复制到准备放数据的区域的第一行，在Advanced Filter 功能对话框的“Copy to”输入框，选择你复制的列名，这样结果集将只包括你需要的列。