《2003Excel技巧之排序与筛选》由会员分享，可在线阅读，更多相关《2003Excel技巧之排序与筛选（19页珍藏版）》请在金锄头文库上搜索。
1、1对于许多经常使用 Excel 排序功能的用户来说，“排序”对话框只允许一次性设置 3 个关键字的限制实在难以满足需要。在如图 121-1 所示的工作表中，有一个 5 列数据的表格，如果需要按从左向右为关键字次序来排序，就成了一个难题，当单击菜单“数据”“排序”后，在如图 121-2 所示的“排序”对话框中，无法设置“完成日期”和“责任人”字段。图 121-1 包含 5 列的表格图 121-2 只能设置 3 个关键字的“排序”对话框事实上，Excel 的排序的关键字并不受上图中这个对话框的限制，是可以按任意数量的列作为关键字来排序的。用户只需要把握一个原则，就可以实现对超过 3 列的数据进行排
2、序：在多列表格中，先被排序过的列，会在后面按其他列为标准的排序过程中，尽量保持自己的序列。所以，对多列进行排序时，要先排序较次要（或者称为排序优先级较低）的列，后排序较重要（或者称为排序优先级最高）的列。2在本例中，因为列数并不多，甚至可以放弃使用“排序”对话框，而改用工具栏上的“升序排列”按钮 ，依次对“责任人 ”、“完成日期”、“开始日期”、“项目”、“类别”列进行排序。另外，也可以通过使用两次“排序”对话框来完成排序：在第一次使用时，将“完成日期”作为主关键字，将“责任人”作为次要关键字；在第二次使用时，按图 121-2 所示进行设置。最近完成的排序效果如图 121-3 所示。图 121
3、-3 多列排序最后效果当把表格的数据按数字或字母顺序进行排序时，Excel 的排序功能能够很好地工作，但是如果用户希望把某些数据按照自己的想法来排序，在默认情况下，Excel 是无法完成任务的。在如图 122-1 所示的表格中，记录着公司职工的津贴数据，其中 B 列是所有职工的职务，现在需要按职务大小来排序整张表格。图 122-1 职工津贴表3此时，如果用户以 B 列为标准进行排序，无论是升序排列还是降序排列，都无法得到令人满意的结果。图 122-2 显示了对 B 列按升序排列的结果，从图中可以看出，Excel 实际上是按照首个字的字母顺序来排序的。那么，如何才能让 Excel 按照用户所希望
4、的方式来排序呢？首先，用户需要告诉 Excel 职务大小的顺序，方法是创建一个自定义序列。有关自定义序列的更多技巧，请参阅技巧 51。在本例中，用户需要创建一个有关职务大小的序列，如图 122-3 所示。然后，使用下面的方法，能够进行职务大小排序。4图 122-2 默认情况下的排序结果图 122-3 职务大小序列单击数据区域中任意单元格，如 A2。单击菜单“数据”“排序” ，出现“排序”对话框。在“排序 ”对话框中，选择 “主要关键字”为“ 职务”，排序方式为升序，如图 122-4 所示。图 122-4 在“排序” 对话框中设置主要关键字为“职务”5单击“ 排序 ”对话框中的 “选项”按钮，在
5、出现的 “排序选项” 对话框中，选择“ 自定义排序次序 ”列表框中的职务序列，如图 122-5 所示，单击 “确定”按钮。图 122-5 设置自定义排序次序单击“ 确定 ”按钮，关闭 “排序”对话框。如此操作后，表格中的数据就按照职务由大到小的顺序进行排列了，最后结果如图 122-6 所示。图 122-6 按职务大小排序的最后结果注意在使用自定义排列次序进行排序时，此次序将应用到“排序”对话框的 3 个关键字中，而无法为每个关键字设置单独的自定义次序。如果表格中每列都需要使用不同的自定义排列次序，则需要通过多次使用“排序”对话框，每次选择一种自定义排列次序。排序的顺序是先排序较次要（或者称为排
6、序优先级较低）的列，后排序较重要（或者称为排序优先级最高）的列。在默认情况下，Excel 对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在 26 个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图 123-1 中显示的表格包含了按字母顺序排列的姓名数据。6图 123-1 按字母顺序排列的姓名然而，在中国人的习惯中，常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同，则依次看名第二
7、、三字，规则同姓字。在 Excel 中，已经考虑到了这种需求。以上面的表格为例，使用姓氏笔划的顺序来排序的方法如下。单击数据区域中任意单元格，如 A2。单击菜单“数据”“排序” ，出现“排序”对话框。在“排序 ”对话框中，选择 “主要关键字”为“ 姓名”，排序方式为升序。单击“ 排序 ”对话框中的 “选项”按钮，在出现的 “排序选项”对话框中，单击方法区域中的“笔划排序” 单选按钮，如图 123-2 所示，单击“确定”按钮。图 123-2 设置按笔划排序单击“ 确定 ”按钮，关闭 “排序”对话框。最后的排序结果如图 123-3 所示。7图 123-3 按笔划排序的结果注意Excel 中的按笔划
8、排序并没有完全按照前文所提到的习惯来作为规则。对于相同笔划数的汉字，Excel 按照其内码顺序进行排列，而不是按照笔划顺序进行排列。对于简体中文版用户而言，相应的内码为代码页 936（ANSI/OEM - GBK）。许多用户都一直认为 Excel 只能按列进行排序，而实际上，Excel 不但能按列排序，也能够按行来排序。下面通过一个例子来介绍具体内容。在如图 124-1 所示的表格中，A 列是列标题，其他的数据是以行来组织的，现在需要依次按“类别”和“项目”来排序。对于这样的表格，按列来排序是没有意义的，必须按行来排序。图 124-1 以行来组织数据的表格选定单元格区域 B2:I5。单击菜单“
9、数据”“排序” ，出现“排序”对话框。8单击“ 排序 ”对话框中的 “选项”按钮，在出现的 “排序选项”对话框中，单击方向区域中的“按行排序” 单选按钮，如图124-2 所示，单击“ 确定”按钮。图 124-2 设置按行排序在“排序 ”对话框中，关键字列表框中的内容此时都发生了改变。选择“主要关键字”为“ 行 1”，排序方式为升序，再选择“次要关键字” 为“行 2”，排序方式为升序，如图 124-3 所示，单击 “确定”按钮。图 124-3 设置按行排序后，关键字的列表框内容发生了改变图 124-4 显示了按行排序的最后结果。图 124-4 按行排序的最后结果注意在使用按行排序时，不能像使用按
10、列排序时一样选定目标区域。因为 Excel 的排序功能中没有“标题列”的概念，所以如果选定全部数据区域再按行排序，标题列也会参与排序，出现意外的结果。因此，在本例的步骤 1 中，只选定标题列以外的数据区域。9在实际工作中，用户有时候需要按照字符的数量进行排序。例如在制作一份歌曲清单时，如图 125-1 所示，人们习惯按照歌曲名字的字数来把它们分门别类。图 125-1 歌曲清单但是，Excel 并不能直接按字数排序，如果要达到目的，需要先计算出每首歌曲名字的字数，然后再进行排序。在 C1 输入“ 字数” ，作为 C 列的列标题。在 C2 输入公式“=LEN(B2)”，然后把此公式复制到 C3:C
11、16 单元格区域。单击 C2，单击工具栏上的“升序排列”按钮 。这样，就完成了按字数排列歌名的任务，如图 125-2 所示。如果必要，可以删除 C 列。图 125-2 按字符数量排序后的歌曲清单10本技巧介绍的利用辅助列先计算现有数据，然后按辅助列的值进行排序的方法，是在需要按特殊属性排序时常用的一种解决方法，可以适用于多种类似的排序。在实际工作中，用户经常会通过为单元格设置背景色或者字体颜色来标注表格中较特殊的数据，但是却无法对有颜色的单元格做进一步操作，比如排序和筛选，因为在 Excel 中，无论使用菜单命令还是工作表函数都无法得到单元格的颜色信息。此时，需要借助 Excel 的宏表函数，
12、才能够得到单元格的颜色信息。有关宏表函数的更多知识，请参阅第 22 章宏表函数。在如图 126-1 所示的表格中，部分日期数据使用了单元格背景色，部分任务数据设置了字体颜色，下面以此表格为例，介绍如何按照颜色排序。图 126-1 使用了单元格背景色和字体颜色的表格分别在 C1、D1 中输入文字 Color1、Color2。选定 C2，按组合键，在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入 Color1，然后在引用位置输入：=GET.CELL(63,06!A2)+RAND()*0单击“ 确定 ”按钮关闭 “定义名称”对话框。11选定 D2，按组合键，在“定义名称”对话框的“在当前工
13、作簿中的名称”文本框中输入 Color2，然后在引用位置输入：=GET.CELL(24,06!B2)+RAND()*0如图 126-2 所示。单击“确定”按钮关闭“定义名称”对话框。图 126-2 使用宏表函数求颜色值在 C2 中输入“=Color1”，在 D2 中输入“=Color2”。选定 C2D2，往下拖曳到 C17D17，完成对公式的复制。单击 C2，单击工具栏上的“降序排列”按钮 ，就能够以日期列的单元格背景色来排序，结果如图 126-3 所示。图 126-3 按颜色值排序表格数据如果需要以任务列的字体颜色排序，则应以 D 列为标准进行排序。在本例中，使用了 Excel 宏表函数在
14、C 列和 D 列分别求出 A 列每个单元格的背景色对应的值和 B 列每个单元格的字体颜色对应的值，从而使按颜色排序成为可能。在使用 Excel 的筛选功能时，也可以按照单元格的背景色或字体颜色来筛选数据，原理相同，在此就不赘述了。注意12宏表函数只能计算手工设置的单元格背景色和字体颜色的值，而不能计算由条件格式功能产生的颜色。 注意用于计算颜色的宏表函数无法自动重算。因此，当单元格背景色或字体颜色发生改变后，用户需要按键执行手动重算，才能更新宏表函数的计算结果。在某些情况下，用户并不希望按照既定的规则来排序数据，而是希望数据能够“乱序”，也就是对数据进行随机排序。在如图 127-1 所示的表格
15、中，有一份歌曲清单，现在需要使用随机排序来改变它们的先后次序。图 127-1 按歌手姓名排序的歌曲清单在 C1 中输入“ 次序” 。在 C2 中输入公式“=RAND()” ，并拖曳到 C16 以完成对公式的复制。13单击 C2，单击工具栏上的“降序排列”按钮 ，就能够对歌曲清单进行随机排序，结果如图 127-2 所示。图 127-2 随机排序的歌曲清单事实上，因为 RAND 函数是易失性函数，所以每次排序都将改变其计算值，从而改变排序次序，实现每次排序都可能不一样的结果。有关 RAND 函数的更多内容，请参阅技巧 195。在日常工作中，用户的表格经常会包含由字母和数字混合的数据，在对这样的数据
16、排序时，结果总是令人无法满意，如图 128-1 所示。图 128-1 排序结果无法令人满意通常情况下，用户希望的规则是先比较字母的大小，再比较数字的大小，但Excel 是按照对字符进行逐位比较来排序的。因此在上图中，A7 排在第 5 位，而不是第 1 位。如果希望让 Excel 改变排序的规则，需要将数据做一些改变。14在 B1 中输入公式：=LEFT(A1,1) & RIGHT(000 & RIGHT(A1,LEN(A1) -1),3)将 B1 的公式复制到 B2:B10。单击 B2，单击工具栏上的“升序排列”按钮 。这样，A 列中的数据就按照用户所希望的那样完成了排序，如图 128-2 所示。图 128-2 正确的排序结果当用户反复对表格进行各种排序以后，表格的原有次序将被打乱，如果在排序后做了其他某些必要的操作，就不方便再使用 Excel 的撤消功能。这时，如果需要让表格返回到排
