复杂条件一网筛尽Excel高级筛选技巧.doc

　　复杂条件一网筛尽Excel高级筛选技巧

       Excel中的“自动筛选”功能大家也许并不陌生，对于条件简单的筛选操作，它基本可以应付。但是，最后符合条件的结果只能显示的在原有的数据表格中，不符合条件的将自动隐藏。若要筛选含有指定关键字的记录，并且将结果显示在两个表中进行数据比对或其他情况，“自动筛选”就有些捉襟见肘了。“傻瓜相机”毕竟功能有限，那么就让我们来试试“高级相机”吧！熟练运用“高级筛选”，无论条件多么复杂，都能一网筛尽。人力资源部的小李最近在做员工表格统计时，就尝到了甜头。

一、特定字符一步筛
　　现在在这份表格中，小李要查找姓“陈”的所有员工记录，他想了想，很快获得了结果。
　　如图1所示，在数据区域外的任一单元格（如B17）中输入被筛选的字段名称“姓名”，在紧靠其下方的B18单元格中输入筛选条件“陈*”。然后依次单击“数据→筛选→高级筛选”命令，在弹出的“高级筛选”对话框，选择筛选方式中的“将筛选结果复制到其他位置”单选按钮。将“列表区域”设置为“$A$1:$F$15”，“条件区域”设置为“$B$17:$B$18”，“复制到”设置为“$A$20:$F$20”，单击“确定”按钮，系统便自动将符合条件的记录筛选出来，并复制到指定的从A20开始的单元格区域中（如图2所示）。

　　小提示：如果在图1的B18单元格中输入筛选条件“*陈”，可筛选名字中含有“陈”字的员工记录（即“陈”字不一定是名字中的第一个字，该字可在名字中的任意位置）。

二、空白数据巧妙筛
　　接下来小李还需要查找没有职称员工的记录，如何进行呢？
　　如图3所示，他先在数据区域外的任一单元格（如E17）中输入被筛选的字段名称“职称”，然后在紧靠其下方的E18单元格中输入筛选条件“<>*”。

下一步，打开“高级筛选→将筛选结果复制到其他位置”，设置好“列表区域”、“条件区域”和“复制到”的位置，单击“确定”按钮，系统就自动将符合条件的记录筛选出来并复制到指定的单元格区域中（如图4所示）。

　　小提示：在本例中，如果要筛选的字段是数值型的，则需要将筛选条件更改为“=”（直接输入“=”号后按回车）。反过来，如果要筛选职称为非空的员工记录，只需将图3的筛选条件改为“*”即可。如果指定的筛选字段是数值型字段，则输入筛选条件“<>”。

三、满足多条件一起筛
　　更复杂的任务来了，这份表格需要查找满足多个条件的结果：男性、年龄不低于30岁、职称字段中含有“工程师”关键字的员工记录。这也难不倒小李。
　　如图5所示，在数据区域外的任一单元格区域（如A17:C17）中输入被筛选的字段名称“性别”、“年龄”和“职称”，在紧靠其下方的A18:C18单元格区域中分别输入筛选条件“男”、“>=30”和“*工程师”。然后进入“高级筛选→将筛选结果复制到其他位置”，设置好“列表区域”、“条件区域”和“复制到”的位置，确定，系统会自动将符合条件的记录筛选出来并复制到指定单元格区域中（如图6所示）。

　　小提示：如果将筛选条件输入在同一行中，筛选时系统会自动查找同时满足所有指定条件的记录并将其筛选出来。如果想查找所有字段值都是非空的员工记录，只需将指定的筛选条件文本型用“*”，数值型用“<>”，并将这些筛选条件输入在同一行中即可。
四、“多选一”也能筛
　　有时查找时，几个条件中如果满足一个即可，比如要查找年龄不低于30岁，或者职称为“高级工程师”的员工记录，又该如何筛选？
　　如图7所示，小李在数据区域外的任一单元格区域（如A17:B17）中输入被筛选的字段名称“年龄”和“职称”，在紧靠其下方的A18:B18单元格区域中分别输入筛选条件“>=30”和“高级工程师”。与以上方法类似，他选中“将筛选结果复制到其他位置”按钮后，设置好“列表区域”、“条件区域”和“复制到”的位置，确定，符合条件的记录就被筛选出来并复制到指定的单元格区域中了（如图8所示）。

　　小提示： 在以上所有的筛选操作中，如果想使筛选结果不重复，只需选中“高级筛选”对话框中的“选择不重复的记录”复选框再进行相应的筛选操作即可。

　　小试牛刀，工作效率大大提高。“Excel高级筛选就像一块智能吸铁石，正确充分地利用好，要找的资料就能很快到手！想筛就筛，我筛得漂亮……”小李笑了。

REPLACE(参数1,参数2,参数3,参数4)

参数1 是要替换其部分字符的文本。
参数2 是要用参数4替换的参数1中字符的起始位置.
参数3 是希望REPLACE用参数4替换参数1中从参数2开始算起的字符个数。
参数4 是要用于替换参数1中字符的文本。

如:
=REPLACE(A3,3,2,"yueliang") 用 yueliang替换A3的第3位算起的2个字符.
Excel中巧用文本函数REPLACE
在Excel中完成如教师资格证书号码、学生学号或考号的排序工作时经常会遇到一些实际问题难以解决，由于工作关系对此有些研究，现将有关具体解决方法介绍如下：
REPLACE函数简介
    REPLACE 使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。
    语法
    REPLACE(old_text,start_num,num_chars,new_text)
    old_text  是要替换其部分字符的文本。
    start_num  是要用new_text 替换的old_text中字符的位置。
    num_chars  是希望REPLACE使用new_text 替换old_text中字符的个数。
    new_text  是要用于替换old_text中字符的文本。
    示例
    公式                      说明（结果）
=REPLACE("12345",1,3,"*")  用@替换前三个字符（*45）
=REPLACE("2008",3,2,"20") 用20替换2008的最后两位（2020）
目标任务
    通过Excel工作表函数公式的运用，完成如下学生考号的排序。
    例如：某班学生有这样一组考号：
       2004081001
       2004080002
       2004080003
       2004081004
    其中学生考号由年限（2004）、县区代码（08）、性别代码（“0”为男生，“1”为女生）及尾数序号组成。由此可看出学生的考号第7位（即性别代码区分）是各自不同的。像这类数据我们在统计时，如果对考号想以末尾后三位数字为依据进行升序或降序方式进行排序，要是单纯单击升、降序排序的话，是达不到我们的目的要求的，那么诸如此类数据我们该怎样完成排序呢？
方法与步骤
    （一） 新建工作簿
1、 启动 “Microsoft Excel”窗口，建立如下一张工作表。（如图1）

    2、 将当前工作簿文件“Book1”保存为“学生情况统计.xls”。
    （二） 具体操作方法
    1、 为了方便直观操作，我将学生考号输入到该统计表的最后一列（E列）。
    2、 在学生考号右侧F列中进行计算，首先鼠标先选定F2单元格，确定数据结果显示的起始位置。
2、 单击常用工具栏上粘贴函数按钮fx，弹出“粘贴函数”对话框，粘贴文本函数REPLACE，然后鼠标单击“确定”按钮。如图2所示：

    4、 在数据栏中输入要添加到公式中各元素的内容。
    old_text（旧文本）：折叠按钮可选定“E2”单元格内容。
    start_num（起始数值）：填写要替换的起始位置。如“7”，指的是从第7位起替换。
    num_chars（替换字符的个数）如“1”。
    new_text（新文本）如“0”，即将指定的旧文本中的字符替换为0。
    5、 单击“确定”按钮，计算结果就会显示在工作表中了。利用Excel的填充功能，我们选定F2单元格向下拖拽填充柄，完成其他学生考号的替换。
    以上操作步骤，用户还可以在公式编辑栏中直接输入公式“= REPLACE(E2,7,1,0)”即可。
    6、最后，我们选定F列任意某单元格，鼠标单击常用工具栏上的升序排列按钮，操作完成。
excel一次性删除一列中最后一个字
　　在excel中，一列中，有些单元格字数多,有些字数少。如何一次性删除一列中的最后一个字，也就是将最后一个文字统一去掉？
　　我们可以在过渡列进行以下操作。比如我们要将E列的所有单元格最后一个字删除去掉，可以在F1单元格输入公式：=REPLACE(E1,LEN(E1),1," ")，回车，确定，然后双击F1单元格的填充柄，进行F列的自动填充。
　　附：excel中replace函数和LEN函数的用法

　　excel replace函数
　　EXCEL中REPLACE函数的使用方法如下介绍：
　　REPLACE(参数1,参数2,参数3,参数4)
　　参数1 是要替换其部分字符的文本。
　　参数2 是要用参数4替换的参数1中字符的起始位置.
　　参数3 是希望REPLACE用参数4替换参数1中从参数2开始算起的字符个数。
　　参数4 是要用于替换参数1中字符的文本。
　　excel replace函数举例
　　A1单元格内容为：世纪在线，在B1单元格输入公式：=REPLACE(A1,3,2,"ittribalwo")
公式的意思是：用字符”ittribalwo”替换A1单元格第3位算起的2个字符，结果为：世纪ittribalwo。
　　excel len 函数
　　excel中LEN函数是返回文本字符串中的字符数。
　　比如：A1单元格内容为：栏目名称，在A2单元格输入公式：=len(a1),返回A1单元格中的字符数。注：文本格式中一个空格也为一个字符。