高级筛选.docx

高级筛选.docx

《高级筛选.docx》由会员分享，可在线阅读，更多相关《高级筛选.docx（13页珍藏版）》请在冰点文库上搜索。

高级筛选

高级筛选

Excel中的高级筛选比较复杂，且与自动筛选有很大不同。

现以下图中的数据为例进行说明。

说明：

上图中只所以要空出前4行，是为了填写条件区域的数据。

尽管Excel允许将条件区域写在源数据旁边，但在筛选中，条件区域可能会被隐藏，为了防止这种事情的发生，最好将条件区域放在源数据区域的上方或下方。

但要注意，条件区域与源数据区域之间至少要保留一个空行。

例1，简单文本筛选：

筛选姓张的人员。

A1：

姓名，或=A5

A2：

张

运行数据菜单→筛选→高级筛选命令，在弹出的高级筛选对话框中，按下表输入数据。

说明：

①筛选中，条件区域标题名要与被筛选的数据列标题完全一致。

②如果勾选“将筛选结果复制到其他位置”，则当前列表区域不符合条件的不隐藏，而是将符合条件的区域复制到指定的区域。

例2，单标题OR筛选：

筛选姓张和姓王的人员

A1：

姓名

A2：

张

A3：

王

条件区域：

$A$1:

$A$3

说明：

将筛选条件放在不同行中，即表示按“OR”来筛选

例3，两标题AND筛选：

筛选出生地为北京的男性人员

A1：

出生地

A2：

北京

B1：

性别

B2：

男

条件区域：

$A$1:

$B$2

说明：

将判断条件放在同一行中，就表示AND筛选

例4，两标题OR筛选：

筛选出生地为北京或女性人员

A1：

出生地

A2：

北京

B1：

性别

B3：

女

条件区域：

$A$1:

$B$3

说明：

条件区域允许有空单元格，但不允许有空行

例5，精确文本筛选：

筛选姓名为张飞的人员信息

A1：

姓名

A2：

="=张飞"

条件区域：

$A$1:

$A$2

说明：

注意条件书写格式，仅填入“张飞”的话，可能会筛选出形如“张飞龙”、“张飞虎”等人的信息。

例6，按公式结果筛选：

筛选1984年出生的人员

A1：

""，A1可以为任意非源数据标题字符

A2：

=year（C6）=1984

条件区域：

$A$1:

$A$2

说明：

按公式计算结果筛选时，条件标题不能与已有标题重复，可以为空，条件区域引用时，要包含条件标题单元格（A1）

例7，用通配符筛选：

筛选姓名为张X（只有两个字）的人员（例5补充）

A1：

姓名

A2：

="=张?

"

条件区域：

$A$1:

$A$2

说明：

最常用的通配符有?

和*。

?

表示一个字符，如果只筛选三个字的张姓人员，A2：

="=张?

?

"。

*表示任意字符

例8，日期型数据筛选：

例6补充

A1：

NO1

A2：

=C6>=A$3

A3：

1984-1-1

B1：

NO2

B2：

=C6<=B$3

B3：

1984-12-31

条件区域：

$A$1:

$B$2

说明：

日期型数据与具体的日期比较往往容易出错，因此在比较时，应将比较的日期值输入到一个单元格中，这里分别将两个具体日期值输入到了A3和B3。

注意A2与A3中对这个具体日期值的引用，必须加绝对引用符号，因为出生日期这列数据都要与该单元格中的数据进行比较。

Excel有一个小小的缺陷，那就是无法自动识别重复的记录。

为了清除这些重复记录，有的朋友是一个一个手工删，既费时又费力。

　　虽说Excel中并没有提供给我们清除重复记录这样的功能，但我们还可以利用它的高级筛选功能来达到相同的目的。

今天，笔者就来向大家介绍一个如何利用Excel的“高级筛选”巧妙删除重复记录的小技巧。

　　（注:

本文所述技巧已于微软Excel2003环境下测试通过）

　　具体操作步骤如下:

　　1.打开一份带有重复记录的Excel文档。

如图1所示（注:

本图已用Photoshop处理，其中彩色部分为重复的记录）

图1

　　2.选中图表中的所有记录（注意，此时应将每列的标题行也选择上，否则筛选完的数据表中将不再包含有该标题行），执行“数据”菜单→“筛选→高级筛选”命令

　　3.在弹出的“高级筛选”对话框中选择“将筛选结果复制到其他位置”一项，并点击“复制到”选择框后面的范围按钮来选择一块区域以存放筛选后的数据（注意:

此处千万不要与原数据所处的单元格相重合，否则数据表将会混乱），最后，勾选“选择不重复的记录”复选框后，点击“确定”按钮即可。

如图2所示

图2

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

如图3所示

图3

Excel数据许多单元格字符间（如以两个字命名的姓名间）常常会有空格，这样既影响美观，也不便于操作。

如何删除字符间的空格呢？

具体方法如下：

执行“工具→宏→VisulBasic编辑器”命令或按下Alt+F11组合键，在VisulBasic编辑器中选择“插入→添加模块”，在代码窗口输入以下代码：

Sub删除空格（）

Dimi%

Fori=1To10

Cells.ReplaceWhat:

="",Replacement:

="",LookAt:

=xlPart,SearchOrder:

=_

xlByRows,MatchCase:

=False,MatchByte:

=False

Nexti

EndSub

输入完成后，关闭VBA窗口，返回到工作表编辑窗口，在Excel文档中执行宏即可。

为了操作更加方便，可以将宏命令添加到常用工具栏上，实现方法如下：

执行“工具→自定义”命令，在弹出的“自定义”对话框中，单击“命令”选项卡，在“类别”命令中选择“新菜单”，再将“命令”中的“新菜单”拖放到菜单栏中。

右击“新菜单”，从弹出的快捷菜单中选择“命名”，将新菜单命名为“删除空格”，并指定刚建立的宏，如图3。

要去除文件中的空格，只要点击菜单栏上的[删除空格]按钮就可以了。

也可以将宏代码导出到指定文件夹，以后如果其他Excel文档需要进行转换，将文件导入即可。

步骤如下：

在VBA编辑窗口，选中模块（代码），单击“文件→导出文件”，在导出文件窗口，选择文件名及保存路径，保存即可。

如果其他Excel表文件需进行删除空格，将该文件导入执行宏就可以了。

在EXCEL处理数据中,我们经常遇到一大堆数据中包含一些重复的行,或是部分字段值重复,而这些有时恰恰不想要的.希望把重复的值去掉、把重复的单独列出来、用不同的颜色标出来方便处理。

下面将介绍三种方式实现以上要求：

1.运用高级筛选（数据－筛选－高级筛选）如图：

在“选择不重复的记录”前将其选中，确定后表中便是没有重复的数据了，如果想把重复记录去掉（每个重复的记录只保留一条），把所得结果复制到别的表中，保存即可。

这是最简单的，相信大多数人都会，但有时我的需求并不是这样简单，这时我就可以考虑高级筛选加公式的方法，如下面的列子：

把A列中姓名重复的记录提取出来（可以照着图做一遍加深印象）

2.利用条件格式

利用条件格式（格式－条条格式）可以把重复的记录用不同的颜色标出来，方便处理，如下面的例子：

3.运用公式实现要求，好好理解的例子，特别是公式含义无论是对这个问题还是今后别的问题都很有帮助：

（公式：

在C13中输入“=INDEX（A:

A,SMALL（IF（MATCH（$A$1:

$A$7&$B$1:

$B$7&$C$1:

$C$7,$A$1:

$A$7&$B$1:

$B$7&$C$1:

$C$7,）=ROW（$1:

$7）,ROW（$1:

$7））,ROW（1:

1）））”然后再按住鼠标拖动复制公式至E19，在G13中输入“=INDEX（A:

A,SMALL（IF（MATCH（$A$1:

$A$7&$B$1:

$B$7,$A$1:

$A$7&$B$1:

$B$7,）=ROW（$1:

$7）,ROW（$1:

$7））,ROW（1:

1）））”然后再按住鼠标拖动复制公式至I19）其中公式为数组公式（大括号是数组公式的标识，不能用手输，输完{}中的公式后同时按Ctrl+Shift+Enter便可出现大括号。

如图：

现将用的内置函数功能简单地介绍一下（具体请见EXCEL帮助）：

INDEX

返回表或区域中的值或值的引用。

函数INDEX（）有两种形式：

数组和引用。

数组形式通常返回数值或数值数组；引用形式通常返回引用。

INDEX（array,row_num,column_num）返回数组中指定单元格或单元格数组的数值。

INDEX（reference,row_num,column_num,area_num）返回引用中指定单元格区域的引用。

SMALL

返回数据集中第k个最小值。

使用此函数可以返回数据集中特定位置上的数值。

语法

SMALL（array,k）

Array   为需要找到第k个最小值的数组或数字型数据区域。

K   为返回的数据在数组或数据区域里的位置（从小到大）。

IF

执行真假值判断，根据逻辑计算的真假值，返回不同结果。

MATCH

返回在指定方式下与指定数值匹配的数组中元素的相应位置。

如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。

ROW

返回引用的行号。

excel删除相同的数据

1、打开有重复数据的EXCEL

2、Alt+F11打开宏编辑器

3、左边双击：

ThisWorkBook

4、贴入以下代码并运行即可：

Sub删除重复数据（）

'删除col列的重复数据

'本例是删除标题为sheet1的EXCEL表中A列（从A2单元格开始）的重复数据

Application.ScreenUpdating=False

'可根据实际情况修改下面三行的结尾值

DimsheetsCaptionAsString:

sheetsCaption="Sheet1"

DimColAsString:

Col="A"

DimStartRowAsInteger:

StartRow=2

'以下不需要修改

DimEndRowAsInteger:

EndRow=Sheets（sheetsCaption）.Range（Col&"65536"）.End（xlUp）.Row

DimCount_1AsInteger:

Count_1=0

Dimcount_2AsInteger:

count_2=0

DimiAsInteger:

i=StartRow

WithSheets（sheetsCaption）

Do

Count_1=Count_1+1

Forj=StartRowToi-1

If.Range（Col&i）=.Range（Col&j）Then

Count_1=Count_1-1

.Range（Col&i）.EntireRow.Delete

EndRow=Sheets（sheetsCaption）.Range（Col&"65536"）.End（xlUp）.Row

i=i-1

count_2=count_2+1

ExitFor

EndIf

Next

i=i+1

LoopWhilei

EndWith

MsgBox"共有"&Count_1&"条不重复的数据"

MsgBox"删除"&count_2&"条重复的数据"

Application.ScreenUpdating=True

EndSub

5、按F5键运行即可