当前位置:首页 >> 电脑基础知识 >>

2003Excel技巧之排序与筛选


对于许多经常使用 Excel 排序功能的用户来说,“排序”对话框只允许一次 性设置 3 个关键字的限制实在难以满足需要。在如图 121-1 所示的工作表中,有 一个 5 列数据的表格, 如果需要按从左向右为关键字次序来排序,就成了一个难 题,当单击菜单“数据”→“排序”后,在如图 121-2 所示的“排序”对话框中, 无法设置“完成日期”和“责任人”字段。

图 121-1 包含 5 列的表格

图 121-2 只能设置 3 个关键字的“排序”对话框

事实上,Excel 的排序的关键字并不受上图中这个对话框的限制,是可以按 任意数量的列作为关键字来排序的。用户只需要把握一个原则,就可以实现对超 过 3 列的数据进行排序:在多列表格中,先被排序过的列,会在后面按其他列为 标准的排序过程中,尽量保持自己的序列。 所以,对多列进行排序时,要先排序较次要(或者称为排序优先级较低)的 列,后排序较重要(或者称为排序优先级最高)的列。

1

在本例中,因为列数并不多,甚至可以放弃使用“排序”对话框,而改用工 具栏上的“升序排列”按钮 ,依次对“责任人”、 “完成日期”、 “开始日期”、

“项目”、“类别”列进行排序。 另外,也可以通过使用两次“排序”对话框来完成排序:在第一次使用时, 将“完成日期”作为主关键字,将“责任人”作为次要关键字;在第二次使用时, 按图 121-2 所示进行设置。 最近完成的排序效果如图 121-3 所示。

图 121-3 多列排序最后效果

当把表格的数据按数字或字母顺序进行排序时,Excel 的排序功能能够很好 地工作,但是如果用户希望把某些数据按照自己的想法来排序,在默认情况下, Excel 是无法完成任务的。 在如图 122-1 所示的表格中,记录着公司职工的津贴数据,其中 B 列是所有 职工的职务,现在需要按职务大小来排序整张表格。

图 122-1 职工津贴表

2

此时,如果用户以 B 列为标准进行排序,无论是升序排列还是降序排列,都 无法得到令人满意的结果。 122-2 显示了对 B 列按升序排列的结果,从图中可 图 以看出,Excel 实际上是按照首个字的字母顺序来排序的。那么,如何才能让 E xcel 按照用户所希望的方式来排序呢? 首先,用户需要告诉 Excel 职务大小的顺序,方法是创建一个自定义序列。 有关自定义序列的更多技巧,请参阅技巧 51。在本例中,用户需要创建一个有 关职务大小的序列,如图 122-3 所示。然后,使用下面的方法,能够进行职务大 小排序。

3

图 122-2 默认情况下的排序结果

图 122-3 职务大小序列
单击数据区域中任意单元格,如 A2。 单击菜单“数据”→“排序”,出现“排序”对话框。

在“排序”对话框中,选择“主要关键字”为“职务”,排序方式为升序,如图 122-4 所示。

图 122-4 在“排序”对话框中设置 主要关键字为“职务”

4

单击“排序”对话框中的“选项”按钮,在出现的“排序 选项”对话框中,选择“自定义排序次序”列表框中的 职务序列,如图 122-5 所示,单击“确定”按钮。

图 122-5 设置自定义排序次序

单击“确定”按钮,关闭“排序”对话框。

如此操作后,表格中的数据就按照职务由大到小的顺序进行排列了,最后结 果如图 122-6 所示。

图 122-6 按职务大小排序的最后结果

注意
在使用自定义排列次序进行排序时,此次序将应用到“排序”对话框的 3 个关键字中,而无法 为每个关键字设置单独的自定义次序。如果表格中每列都需要使用不同的自定义排列次序, 则需要通过多次使用“排序”对话框,每次选择一种自定义排列次序。排序的顺序是先排序较 次要(或者称为排序优先级较低)的列,后排序较重要(或者称为排序优先级最高)的列。

在默认情况下,Excel 对中文字的排序方式是按照“字母”顺序的,以中文 姓名为例,字母顺序即按姓的拼音的首字母在 26 个英文字母中出现的顺序进行 排列,如果同姓,则依次计算名的第二、第三字。图 123-1 中显示的表格包含了 按字母顺序排列的姓名数据。

5

图 123-1 按字母顺序排列的姓名

然而,在中国人的习惯中,常常是按照“笔划”的顺序来排列姓名的。这种 排序的规则是:按姓字的划数多少排列,同划数内的姓字按起笔顺序排列(横、 竖、撇、捺、折),划数和笔形都相同的字,按字形结构排列,先左右、再上下, 最后整体字。如果姓字相同,则依次看名第二、三字,规则同姓字。 在 Excel 中,已经考虑到了这种需求。以上面的表格为例,使用姓氏笔划的 顺序来排序的方法如下。
单击数据区域中任意单元格,如 A2。

单击菜单“数据”→“排序”,出现“排序”对话框。

在“排序”对话框中,选择“主要关键字”为“姓名”,排序方式为升序。

单击“排序”对话框中的“选项”按钮,在出现的“排序选项”对话框中,单击 方法区域中的“笔划排序”单选按钮,如图 123-2 所示,单击“确定”按钮。

图 123-2 设置按笔划排序

单击“确定”按钮,关闭“排序”对话框。

最后的排序结果如图 123-3 所示。
6

图 123-3 按笔划排序的结果

注意
Excel 中的按笔划排序并没有完全按照前文所提到的习惯来作为规则。对于相同笔划数的汉 字,Excel 按照其内码顺序进行排列,而不是按照笔划顺序进行排列。对于简体中文版用户 而言,相应的内码为代码页 936(ANSI/OEM - GBK)。

许多用户都一直认为 Excel 只能按列进行排序,而实际上,Excel 不但能按 列排序,也能够按行来排序。下面通过一个例子来介绍具体内容。 在如图 124-1 所示的表格中,A 列是列标题,其他的数据是以行来组织的, 现在需要依次按“类别”和“项目”来排序。对于这样的表格,按列来排序是没 有意义的,必须按行来排序。

图 124-1 以行来组织数据的表格
选定单元格区域 B2:I5。

单击菜单“数据”→“排序”,出现“排序”对话框。

7

单击“排序”对话框中的“选项”按钮,在出现的“排序选项”对 话框中, 单击方向区域中的“按行排序”单选按钮, 如图 124-2 所示,单击“确定”按钮。

图 124-2 设置按行排序 在“排序”对话框中,关键字列表框中的内容此时都发生了改变。选择“主 要关键字”为“行 1”,排序方式为升序,再选择“次要关键字”为“行 2”,排 序方式为升序,如图 124-3 所示,单击“确定”按钮。

图 124-3 设置按行排序后,关键字的 列表框内容发生了改变

图 124-4 显示了按行排序的最后结果。

图 124-4 按行排序的最后结果

注意
在使用按行排序时,不能像使用按列排序时一样选定目标区域。因为 Excel 的排序功能中没 有“标题列”的概念,所以如果选定全部数据区域再按行排序,标题列也会参与排序,出现意 外的结果。因此,在本例的步骤 1 中,只选定标题列以外的数据区域。

在实际工作中,用户有时候需要按照字符的数量进行排序。例如在制作一份 歌曲清单时, 如图 125-1 所示, 人们习惯按照歌曲名字的字数来把它们分门别类。
8

图 125-1 歌曲清单

但是,Excel 并不能直接按字数排序,如果要达到目的,需要先计算出每首 歌曲名字的字数,然后再进行排序。
在 C1 输入“字数”,作为 C 列的列标题。

在 C2 输入公式“=LEN(B2)”,然后把此公式复制到 C3:C16 单元格区域。 单击 C2,单击工具栏上的“升序排列”按钮 。

这样,就完成了按字数排列歌名的任务,如图 125-2 所示。如果必要,可以 删除 C 列。

图 125-2 按字符数量排序后的歌曲清单

本技巧介绍的利用辅助列先计算现有数据,然后按辅助列的值进行排序的方 法, 是在需要按特殊属性排序时常用的一种解决方法,可以适用于多种类似的排 序。

9

在实际工作中,用户经常会通过为单元格设置背景色或者字体颜色来标注表 格中较特殊的数据, 但是却无法对有颜色的单元格做进一步操作,比如排序和筛 选, 因为在 Excel 中,无论使用菜单命令还是工作表函数都无法得到单元格的颜 色信息。 此时,需要借助 Excel 的宏表函数,才能够得到单元格的颜色信息。有关宏 表函数的更多知识,请参阅第 22 章宏表函数。 在如图 126-1 所示的表格中,部分日期数据使用了单元格背景色,部分任务 数据设置了字体颜色,下面以此表格为例,介绍如何按照颜色排序。

图 126-1 使用了单元格背景色和字体颜色的表格
分别在 C1、D1 中输入文字 Color1、Color2。

选定 C2, 按<Ctrl+F3>组合键, 在“定义名称”对话框的“在当 前工作簿中的名称”文本框中输入 Color1,然后在引用位置输 入:
=GET.CELL(63,'06'!A2)+RAND()*0 单击“确定”按钮关闭“定义名称”对话框。

选定 D2, 按<Ctrl+F3>组合键, 在“定义名称”对话框的“在当 前工作簿中的名称”文本框中输入 Color2,然后在引用位置输 入:
=GET.CELL(24,'06'!B2)+RAND()*0

如图 126-2 所示。 单击“确定”按钮关闭“定义名称”对话 框。
10

图 126-2 使用宏表函数求颜色值

在 C2 中输入“=Color1”,在 D2 中输入“=Color2”。 选定 C2∶D2,往下拖曳到 C17∶D17,完成对公式的复制。 单击 C2,单击工具栏上的“降序排列”按钮 ,就能够以日期 列的单元格背景色来排序,结果如图 126-3 所示。

图 126-3 按颜色值排序表格数据

如果需要以任务列的字体颜色排序,则应以 D 列为标准进行排序。 在本例中,使用了 Excel 宏表函数在 C 列和 D 列分别求出 A 列每个单元格的 背景色对应的值和 B 列每个单元格的字体颜色对应的值, 从而使按颜色排序成为 可能。 在使用 Excel 的筛选功能时,也可以按照单元格的背景色或字体颜色来筛选 数据,原理相同,在此就不赘述了。

注意

宏表函数只能计算手工设置的单元格背景色和字体颜色的值, 而不能计算由条件格式功能产 生的颜色。

注意
11

用于计算颜色的宏表函数无法自动重算。因此,当单元格背景色或字体颜色发生改变后,用 户需要按<F9>键执行手动重算,才能更新宏表函数的计算结果。

在某些情况下,用户并不希望按照既定的规则来排序数据,而是希望数据能够“乱序”, 也就是对数据进行随机排序。

在如图 127-1 所示的表格中,有一份歌曲清单,现在需要使用随机排序来改 变它们的先后次序。

图 127-1 按歌手姓名排序的歌曲清单

在 C1 中输入“次序”。

在 C2 中输入公式“=RAND()”,并拖曳到 C16 以完成对公式的复制。

单击 C2,单击工具栏上的“降序排列”按钮 随机排序,结果如图 127-2 所示。

,就能够对歌曲清单进行

图 127-2 随机排序的歌曲清单

12

事实上,因为 RAND 函数是易失性函数,所以每次排序都将改变其计算值,从 而改变排序次序,实现每次排序都可能不一样的结果。有关 RAND 函数的更多内 容,请参阅技巧 195。

在日常工作中,用户的表格经常会包含由字母和数字混合的数据,在对这样 的数据排序时,结果总是令人无法满意,如图 128-1 所示。

图 128-1 排序结果无法令人满意

通常情况下,用户希望的规则是先比较字母的大小,再比较数字的大小,但 Excel 是按照对字符进行逐位比较来排序的。因此在上图中,A7 排在第 5 位,而 不是第 1 位。 如果希望让 Excel 改变排序的规则,需要将数据做一些改变。 在 B1 中输入公式:
=LEFT(A1,1) & RIGHT("000" & RIGHT(A1,LEN(A1) -1),3) 将 B1 的公式复制到 B2:B10。

单击 B2,单击工具栏上的“升序排列”按钮 。 这样, 列中的数据就按照用户所希望的那样完成了排序, A 如图 128-2 所示。

图 128-2 正确的排序结果 13

当用户反复对表格进行各种排序以后,表格的原有次序将被打乱,如果在排 序后做了其他某些必要的操作,就不方便再使用 Excel 的撤消功能。这时,如果 需要让表格返回到排序前的状态,就不那么容易了。 如果在排序前就预先知道可能需要保持表格在排序前的状态,可以在表格的 左侧或右侧插入一列空白列, 并填充一组连续的数字。在如图 129-1 所示的表格 中,A 列就是新插入的列,用于记录表格的现有次序。

图 129-1 使用辅助列记录表格的当前次序

现在, 无论对怎样的表格进行排序, 只要最后以 A 列为标准做一次升序排序, 就能够返回表格的原始次序。

130-1 没有正确选择数据区域,而自动选择的区域中包含有空格

如果需要排序的数据区域不是标准的数据列表,并且包含空格,那么若在排 序前没有手工先选定整个数据区域,而是只选定数据区域中的任意单元格,排序 结果将很可能不正确。因为在这种情况下,Excel 并不总是能为用户自动选择正 确的数据区域。

14

130-2 内存不足的情况

Excel 是一款桌面型的电子表格软件,当处理过于庞大的数据量时,其性能 会低于专业的数据库软件,并完全依赖于电脑的硬件配置。因此,当排序或筛选 的数据区域过大时,Excel 可能会提示用户“内存不足”。此时,可以采用以下 的解决方法。 1.增加电脑的物理内存。 2.优化电脑的性能,如关闭暂时不需要的其他程序,清理系统分区以保留足 够剩余空间,删除 Windows 临时文件等。 3.减小排序的数据区域。
130-3 数据区域中包含有格式化为文本的数字

当数据区域中包含有格式化为文本的数字时,排序结果将会错误。在如图 13 0-1 所示的表格中,A5∶A10 是文本型数字,此时按编号进行排序,则较小的编 号可能会排到较大的编号后面去。

图 130-1 排序错误的表格

要想使排序结果正确,必须先将文本型数字转换为数值型数字,方法如下。
单击工作表中任意空单元格,按<Ctrl+C>组合键。

选定 A5:A10,单击菜单“编辑”→“选择性粘贴”。

在“选择性粘贴”对话框中,选择“加”项,然后单击“确定”按钮。

130-4 Excel 提示“不同的单元格格式太多”

如果用户工作簿中存在着 4000 种以上的单元格格式组合, 那么在执行许多命 令时(包括排序),Excel 都会提示“不同的单元格格式太多”。

15

这里所谈到的单元格格式组合,指工作簿中任意单元格,如果所设置的单元 格格式与其他单元格有任何细微的差别,即成为一种单元格格式组合。比如,有 两个单元格,都设置单元格格式为红色宋体 12 号字,如果其中一个单元格的数 字格式使用 2 位小数, 而另一个单元格的数据格式不使用小数,则两个单元格各 使用一种单元格格式组合。 在一般情况下,4000 种的上限足够用户设置数据区域,但如果某个工作簿文 件经过多人之手,长年累月使用,并且有很多内容是从别的文件中 Copy 而来, 也可能最终导致超出限制。 解决的方法是简化工作簿的格式,使用统一的字体、图案与数字格式。有关 此问题的详细说明,请参阅 http://support.microsoft.com/?kbid=213904。
130-5 排序区域包含合并单元格

如果在排序的时候,Excel 提示“此操作要求合并单元格都具有相同大小”, 则说明数据区域中包含合并单元格,并且合并单元格的大小各不相同。例如图 1 30-2 所示的表格,A 列的数据是由合并单元格组成,而 B 列和 C 列都没有合并单 元格。此时如果对整个数据区域的排序操作将无法进行。

图 130-2 包含合并单元格的数据区域

而在如图 130-3 所示的表格中,同行次的合并单元格的大小完全相同,因此 可以正常排序。

16

图 130-3 合并单元格大小相同的数据区域

对于图 130-2 所示的表格,需要用户取消合并所有已合并的单元格,然后才 能排序。

在实际工作中,用户常常需要把重要的工作表进行保护,以防止工作表内容 被意外更改。 如果在保护工作表的同时,又希望对工作表中的数据使用自动筛选 功能以便进行一些数据分析工作,则需要进行一些设置。以图 131-1 所示的工作 表为例。

图 131-1 原始表格

131-1 Excel 2002 及以上版本

从 Excel 2002 开始, 工作表保护功能中允许用户设置在保护工作表的同时所 允许进行的部分操作类型,其中就包括自动筛选。
选中 A1:C17 中的任意单元格。

17

单击菜单“数据”→“筛选”→“自动筛选”,使表格进入自动筛选模式。

单击菜单“工具”→“保护”→“保护工作表”,在 “保护工作表”对话框的“允许此工作表的所有 用户进行”列表框中,勾选“使用自动筛选”选 项,如图 131-2 所示。

图 131-2 在“保护工作表”对话框中 勾选“使用自动筛选”选项 如果需要,可以在“取消工作表保护时使用的密码”文本框中输入保护工 作表的密码,最后单击“确定”,关闭“保护工作表”对话框。

现在,虽然工作表处于受保护状态,不能对任何单元格进行修改,但仍然可 以使用“自动筛选”功能,如图 131-3 所示。

图 131-3 受保护的工作表仍然可以使用“自动筛选”功能

注意

步骤 2 与步骤 3 的操作顺序一定不能颠倒, 即用户必须在保护工作表以前就让数据区域或列 表进入自动筛选模式。 否则, 如果先执行保护工作表命令, 则无法开启或关闭自动筛选模式, “自动筛选”命令也将是灰色的。 131-2 Excel 2000 及更低版本

在 Excel 2000 或更低版本中, 保护工作表功能没有提供与自动筛选相关的设 置,但是用户仍然可以借助宏代码来进行设置。假设数据保存在 Sheet1 中。
18

选中 A1:C17 中的任意单元格。

单击菜单“数据”→“筛选”→“自动筛选”,使表格进入自动筛选模式。

按<Alt+F11>打开 VBA 编辑器窗口, 按<Ctrl+R>组合键显示工程 资源管理器窗口,双击其中的 ThisWorkbook,在右边的代码窗 口中输入以下代码:
Private Sub Workbook_Open() Worksheets("sheet1").Protect Password:="pwd", userInter Faceonly:=True Worksheets("sheet1").EnableAutoFilter = True End Sub

保存并关闭工作簿文件。 当再次打开工作簿文件时, 工作表 Sheet1 将被保护, 但是自动筛选功能仍然 可用。用户可以根据自己的需要,修改上述代码中的工作表名称与密码(pwd)。 与在 Excel 2002 及以上版本中相似,用户必须先让数据表进入自动筛选模 式,再运行宏代码执行工作表保护。

19


相关文章:
Excel2003中,根据文字颜色来筛选或排序.doc
Excel2003中,根据文字颜色来筛选排序_计算机软件及应用_IT/计算机_专业资料。Excel2003根据文字颜色排序之简单方法Excel2003 中,根据文字颜色来筛选排序Excel...
EXCEL排序与数据筛选_图文.ppt
EXCEL排序与数据筛选 - excel03排序与自动筛选... excel03排序与自动筛选 任务十二 统计“成绩册” ...? 掌握数据筛选方法。 ? 掌握数据分类汇总的方法。 ?...
Excel中的数据排序与筛选教案.doc
Excel 中的数据排序和筛选本次课题所讲授的内容是 Excel2003 的强大的数据处理...观察过程,思考高级筛选的意义 高级筛选 指出条件区的意义和建立方法(重点)。 ...
Office Excel 2003数据排序方法及技巧.doc
Excel 做数据排序的常用方法与技巧 在用 Excel 制作相关的数据表格时,我们...文档贡献者 whl5987 贡献于2010-12-03 1/2 相关文档推荐 课题_Excel筛选复制...
Excel2003教程(完整版)_图文.ppt
Excel2003教程(完整版)_政史地_初中教育_教育专区...图表的制作、掌握数据的排序筛选、分类汇总方法 ?...
Excel2003练习题及答案.txt
必须?◇不应对数据库排序◇使用数据记录单◇应 对数据库的分类字段进行排序◇设置筛选条件 答案C 48.在EXCEL2003中打印时?要打印出工作表的行号和列标?应该设置...
Excel中的数据排序与筛选教案.doc
中的数据排序和筛选 授教课师 时间 2012 年 6 月 20 日 教学 本次课题所讲授的内容是 Excel2003 的强大的数据处理能力, 与学生的学习和 设想 工作实际关系...
Excel中的数据排序与筛选教案(1).doc
Excel 中的数据排序和筛选》教案一、教学设计课 题 Excel 中的数据排序和筛选 授教课师 孙志红 时间 2014-04-06 教学 本次课题所讲授的内容是 Excel2003 的...
EXCEL技巧数据管理与透视分析_图文.ppt
EXCEL技巧数据管理与透视分析 - 本章学习目标 1、了解Excel2003的列表功能 2、掌握记录单的应用方法 3、掌握数据排序的方法 4、掌握数据筛选的方法 5、掌握工作表...
excel和WPS筛选重复项的技巧_图文.doc
具体操作步骤: 点击开始菜单-排序和筛选-筛选-按颜色排序-后单独显示重复数据。 您也可以根 据个人需要,删除或者继续其他编辑。 excel 2003方法/步骤打开 ...
EXCEL2003数据透视表使用方法_图文.doc
EXCEL2003数据透视表使用方法_计算机软件及应用_IT/计算机_专业资料。EXCEL2003...新课 一、创建数据透视表 “数据透视表”功能能够将筛选排序和分类汇总等操作...
03.查找和排序.ppt
2003Excel技巧之排序与筛选... 19页 2财富值 工资表 4页 免费
Excel学案-数据排序、筛选.doc
Excel学案-数据排序筛选 - Excel2003的学案,很实用,适合计算机
第10章 数据排序、筛选和汇总.ppt
第10章 数据排序筛选和汇总_IT/计算机_专业资料。计算机基础,Word文档的使用...Excel 2003提供了多种对数据清单进行排序方法,既可以按升序,降序的方法,也...
Excel2003数据处理_图文.ppt
第7讲 Excel2003 的使用数据的管理与分析 主讲:田卫红 制作者: 教学目标...? ? 能力目标:掌握数据排序 ? ? 掌握分类汇总的方法掌握数据筛选方法 制作...
EXCEL表多级分类方法.doc
Excel 2003 中,单击菜单“数据→排序”。弹出“排序”对话框,其中主要关键...如果是 Excel 2007,在“数据”选项卡的“排序和筛选”组中单击“排序”命令,...
Excel 2003技巧培训_图文.ppt
排序与筛选 应用图表 OFFICE办公系统之 办公系统之Excel 办公系统之 认识Excel 认识Excel 2003 2003工作窗口 Excel 2003工作窗口 OFFICE办公系统之 办公系统之Excel ...
数据排序._图文.ppt
Excel 2003提供了多种对数据清单进行排序方法,既可以按升序、降序的方法,也可以按用 户自定义的排序方法。 ? 对数据清单排序 ? 创建自定义排序 数据筛选在...
excel数据清单.ppt
根据一列的数据对数据行排序(简单排序)想快速根据一...Excel 2003提供了两种 筛选数据的方法,分别为自动...
EXCEL删除重复数据的多种方法.doc
EXCEL 删除重复数据的多种方法 EXCEL 删除重复数据在新旧版本中操作方法有所不同。 先来说说 EXCEL2003 吧。 这里就要用到“高级筛选”功能,不多说了。大家可以...