officeba > 单独文章


你见过的最齐全的EXCEL排列排序方法N种

OFFICEBA为大家收集了最齐全的EXCEL排列排序方法,多达N种!下面就为大家数数家珍。
1.自定义序列排列
在Excel20007中,可以单击office按钮,再单击“Excel选项”,在“常用”选卡里,单击“编辑自定义列表”按钮,来添加自定义序列。自定义序列,除了可以方便用来填充之外,还可以以这个序列为基础排序数据。使用自定义排序的方法是,是在“排序选项”对话框中,选择“自定义排序次序”列表框里选择自定义的序列,其它操作以上面类同。下面详细说明
如果您要求Excel按照“金工车间”、“铸造车间”和“维修车间”的特定顺序重排工作表数据,前面介绍的几种方法就无能为力了。这类问题可以用定义排序规则的方法解决:首先单击Excel“工具”菜单下的“选项”命令,打开“选项”对话框中的“自定义序列”选项卡(图3)。选中左边“自定义序列”下的“新序列”,光标就会在右边的“输入序列”框内闪动,您就可以输入“金工车间”、 “铸造车间”等自定义序列了,输入的每个序列之

  间要用英文逗号分隔,或者每输入一个序列就敲回车。如果序列已经存在于工作表中,可以选中序列所在的单元格区域单击“导入”,这些序列就会被自动加入“输入序列”框。无论采用以上哪种方法,单击“添加”按钮即可将序列放入“自定义序列”中备用(图3)。

  使用排序规则排序的具体方法与笔划排序很相似,只是您要打开“排序选项”对话框中的“自定义排序次序”下拉列表,选中前面定义的排序规则,其他选项保持不动。回到“排序”对话框后根据需要选择“升序”或“降序”,“确定”后即可完成数据的自定义排序。

  需要说明的是: 显示在“自定义序列”选项卡中的序列(如一、二、三等),均可按以上方法参与排序,请读者注意Excel提供的自定义序列类型。


1.排位函数RANK():
  在H2单元格输入公式:=RANK(G2,G:G),接着把鼠标指针指向H2单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动将公式向下复制到H13单格后放手(如图6)。



  RANK函数是返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小(如果数据清单已经排过序了,则数值的排位就是它当前的位置)。其语法为:RANK(number,ref,order)其中:number为需要找到排位的数,ref为包含一组数的数组或引用(ref中的非数值型数据将被忽略),order为一指明排位方式的数,order为0或省略时按降序排位,order不为0时按升序排位。

  这个公式中number是指G2单元格中的431分;ref指的是G列这些分数;order省略没有。也就是说公式的最终意思是按照降序计算G2单元格中的431分在G列这组分数中所排的位置。

  如果RANK 函数中ref 所引用的区域中有重复数,则函数返回相同的排位数,但重复数的存在将影响后续数值的排位。也就是说如果分数相同名次相同,随后的名次将空缺。如这里400出现两次,其排位为4,而395分的排位为6(没有排位为5的数值)。

  2.条件函数IF():

  在H2单元格直接输入1,在H3单元格输入公式: =IF(G3=G2,H2,H2+1) 接着把鼠标指针指向H3单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动将公式向下复制到H13单格后放手(如图7)。



  IF函数用于执行真假值判断后,根据逻辑测试的真假值返回不同的结果,因此`函数也称之为条件函数。可以使用函数 IF 对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,value_if_false)。

  其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。此参数可使用任何比较运算符。Value_if_true显示在logical_test 为 TRUE 时返回的值,Value_if_true 也可以是其他公式。Value_if_false 显示在logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。也就是说如果第一个参数logical_test返回的结果为真的话,则执行第二个参数Value_if_true的结果,否则执行第三个参数Value_if_false的结果。

  IF函数可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。这个公式中logical_test是G3=G2, value_if_true是H2,value_if_false是H2+1。意思是G3单元格中的分数与G2单元格中的分数是否相同,如果相同,则显示H2单元格值,也就是我们填入的1,如果不同,则显示H2+1,也就是2。

  用这种方法排的名次不会出现空缺,相同分数的排名仍然相同。
在有些情况下,对数据的排序顺序可能非常特殊,既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序,而是按照指定的特殊次序,如对总公司的各个分公司按照要求的顺序进行排序,按产品的种类或规格排序等等,这时就需要自定义排序。

利用自定义排序方法进行排序,首先应建立自定义序列,其方法可参阅第1章的有关内容。建立好自定义序列后,即可对数据进行排序,方法是:单击数据区中要进行排序的任意单元格,单击【数据】菜单,选择【排序】项,在弹出的【排序】对话框中单击【选项】按钮,系统弹出【排序选项】对话框,如图2-36所示,在【自定义排序次序】的下拉列表中,选择前面建立的自定义序列,然后单击【确定】按钮,即可对数据进行自定义排序。
2、数据排序的规则
Excel允许对字符、数字等数据按大小顺序进行升序或降序排列,要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下:

数值:按数值的大小。

字母:按字母先后顺序。

日期:按日期的先后。

汉字:按汉语拼音的顺序或按笔画顺序。

逻辑值:升序时FALSE排在TRUE前面,降序时相反。

空格:总是排在最后。

数据排序步骤
(1)单击数据区中要进行排序的任意单元格。

(2)单击【数据】菜单,选择【排序】项,系统将弹出【排序】对话框,如图2-35所示。



图2-35 【排序】对话框

(3)在【排序】对话框中用下拉列表框选择要排序的关键字,关键字有“主要关键字”、“次要关键字”和“第三关键字”,根据需要分别选择不同的关键字;

(4)单击【确定】按钮,数据就按要求进行了排序。

当只有一个关键字时,可以单击工具栏上的升序按钮或降序按钮,进行自动排序。
3、数值排序


  1.RANK函数

  RANK函数是Excel计算序数的主要工具,它的语法为:RANK (number,ref,order),其中number为参与计算的数字或含有数字的单元格,ref是对参与计算的数字单元格区域的绝对引用,order是用来说明排序方式的数字(如果order为零或省略,则以降序方式给出结果,反之按升序方式)。

  例如图1中E2、E3、E4单元格存放一季度的总产值,计算各车间产值排名的方法是:在F2单元格内输入公式“=RANK(E2,$E$2: $E$4)”,敲回车即可计算出铸造车间的产值排名是2。再将F2中的公式复制到剪贴板,选中F3、 F4单元格按Ctrl+V,就能计算出其余两个车间的产值排名为3和1。如果B1单元格中输入的公式为“=RANK(E2,$E$2:$E$4,1)”,则计算出的序数按升序方式排列,即2、1和3。

  需要注意的是:相同数值用RANK函数计算得到的序数(名次)相同,但会导致后续数字的序数空缺。假如上例中F2单元格存放的数值与 F3相同,则按本法计算出的排名分别是3、3和1(降序时)。

  2.COUNTIF函数

  COUNTIF函数可以统计某一区域中符合条件的单元格数目,它的语法为COUNTIF(range,criteria)。其中range为参与统计的单元格区域,criteria是以数字、表达式或文本形式定义的条件。其中数字可以直接写入,表达式和文本必须加引号。

  仍以图1为例,F2单元格内输入的公式为“=COUNTIF($E$2:$E$4, ">"&E2)+1”。计算各车间产值排名的方法同上,结果也完全相同,2、 1和3。

  此公式的计算过程是这样的:首先根据E2单元格内的数值,在连接符&的作用下产生一个逻辑表达式,即“>176.7”、“>167.3”等。 COUNTIF函数计算出引用区域内符合条件的单元格数量,该结果加一即可得到该数值的名次。很显然,利用上述方法得到的是降序排列的名次,对重复数据计算得到的结果与RANK函数相同。

3.IF函数


  Excel自身带有排序功能,可使数据以降序或升序方式重新排列。如果将它与IF函数结合,可以计算出没有空缺的排名。以图1中E2、E3、 E4单元格的产值排序为例,具体做法是:选中E2单元格,根据排序需要,单击Excel工具栏中的“降序排序”或“升序排序”按钮,即可使工作表中的所有数据按要求重新排列。


  假如数据是按产值由大到小(降序)排列的,而您又想赋予每个车间从1到n(n为自然数)的排名。可以在G2单元格中输入1,然后在G3单元格中输入公式“=IF(E3=E2, G3,G3+1)”,只要将公式复制到G4等单元格,就可以计算出其他车间的产值排名。

按笔划排序。通过上面的操作,我们还可以看到“排序选项”对话框中还有一个方法区域,单击里面的“按笔划排序”就按照汉字的笔划来排序。在中国人的习惯中,常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是:按姓字的划数多少排列,同划数内的姓字按起笔顺序排列(横、竖、撇、捺、折),划数和笔形都相同的字,按字形结构排列,先左右、再上下,最后整体字。如果姓字相同,则依次看名第二、三字,规则同姓字。
文本排序


  选举等场合需要按姓氏笔划为文本排序, Excel提供了比较好的解决办法。如果您要将图1数据表按车间名称的笔划排序,可以使用以下方法: 选中排序关键字所在列(或行)的首个单元格(如图1中的A1),单击Excel“数据”菜单下的“排序”命令,再单击其中的“选项”按钮。选中“排序选项”对话框“方法”下的“笔画排序”,再根据数据排列方向选择“按行排序”或“按列排序”,“确定”后回到“排序”对话框(图2)。如果您的数据带有标题行(如图1中的“单位”之类),则应选中“有标题行”(反之不选),然后打开“主要关键字”下拉列表,选择其中的“单位”,选中排序方式(“升序”或“降序”)后“确定”,表中的所有数据就会据此重新排列。

按颜色排序。同样,在Excel2007里,来完成此操作不算难事,而在Excel2003中就要借助Excel的宏表函数GET.CELL生成的辅助列来完成,关于GET.CELL的参数,在https://www.excelba.com/Art/Html/41.html 中有具体的说明。
现在设要对A列的单元格颜色和字体颜色进行排序,操作方法如下:
1)首先选择B1格,然后按<Ctrl+F3>组合键,在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入Color1,然后在引用位置输入: =GET.CELL($A1)+RAND()*0,单击添加,然后同样文本框中输入Color2,然后在引用位置输入: =GET.CELL($A1)+RAND()*0,后单击添加,最后单击确定完成自定义名称的添加。
2)在B1中输入公式“=Color1”,在C1中输入“=Color2”,然后向下填充。
3)最后就可以根据B,C两列的数据,进行排序。
公式法

  在H2单元格直接输入1,在H3单元格输入公式:=H2+(G3<G2)接着把鼠标指针指向H3单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动将公式向下复制到H13单格后放手(如图8)。



  公式“=H2+(G3<G2)”的作用是:如果该单元格中的分数小于它上面单元格中的分数,则其名次比上一个单元格的值增加1;如果该单元格中的分数等于上面单元格中的分数,则其名次等于上一个单元格的名次。

  公式中“=”为赋值命令,其作用是将计算结果填在这个单元格内,“H2”是我们先手工输入的1;“+”为加法运算;(G3<G2)为逻辑运算语句,其作用是用来计算G列中G3和G2两个单元格中的分数的大小直接进行比较的运算结果,如果G3单元格中的分数比G2单元格中的分数小,运算结果为“1”,否则为“0”。这样在按照递减顺序进行排序的分数序列中,当G3单元格中的分数比G2单元格中的分数小时,H3单元格的值为H2单元格中的值再加上1,即为上一个名次的值加1;而当G3单元格中的时间和G2单元格中的时间相等时,H3单元格的值为H2单元格中的值再加上0,即这2个名次相同。

  这种直接使用公式法同使用IF函数一样,名次不会出现空缺,相同分数的排名仍然相同。
按行排序。很多人可能都不知道Excel除了默认的按列排序之外,还可以按行来排序,因为都没有注意排序的选项功能。在选择排序的区域后,在“数据”中的“排序”项,调出“排序”对话框,单击“排序”对话框中的“选项”按钮,在出现的“排序选项”对话框中,单击方向区域中的“按行排序”单选按钮,后单击“确定”按钮,就可以按行来排序了。
对超过3列的数据排序。在Excel2007里,就不会有多大困难,而在Excel2003中,超过3列的数据进行排序时需要把握一个原则,就是要先排序较次要(或者称为排序优先级较低)的列,后排序较重要(或者称为排序优先级最高)的列。
其它借助辅助列排序。利用辅助列,我们还可以来完成很多排序操作,比如:
1)还原排序前的顺序。在排序之前,可以在表格的左侧或右侧插入一列空白列,并填充一组连续的数字,等排序完成之后,可以利用此列的数据为标准做一次升序排序,就能够返回表格的原始次序。
2)随机排序。如果我们想把数据随机打乱,就可以插入一列包含公式“=RAND()”的数据,然后按此列数据排序即可。
3)按字符的数量排序。如果我们想按单元格中包含的字符数排序,这时可以利用Len函数来获得一列字符的数据,然后根据此排序。

借助辅助列还可以完成各种各样的排序,而关键就是如何来升成这个辅助列,其中如果利用函数公式等是这个方法的关键,所以也就不再多举例子了。

声明:欢迎各大网站转载本站文章,还请保留一条能直接指向本站的超级链接,谢谢!

时间:2009-03-11 01:24:24,点击:65824


【OfficeBa论坛】:阅读本文时遇到了什么问题,可以到论坛进行交流!Excel专家邮件:342327115@qq.com(大家在Excel使用中遇到什么问题,可以咨询此邮箱)。

【声明】:以上文章或资料除注明为Office自创或编辑整理外,均为各方收集或网友推荐所得。其中摘录的内容以共享、研究为目的,不存在任何商业考虑。如有任何异议,请与本站联系,本站确认后将立即撤下。谢谢您的支持与理解!


相关评论

我要评论

评论内容