首页 Office&WPS正文

Excel的两种另类排序法

mydiannao Office&WPS 09-12 2869 0

  在Excel中利用数据的排序功能可以很轻松地进行排序,但这种排序会破坏原有的数据清单。笔者经过摸索,发现了两种可以利用公式自动排序且不破坏原始数据清单的方法。文章末尾提供.xls文件供大家下载参考。

  一、利用数组公式

  数组公式可以同时进行多重计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。数组公式的创建方法很简单,在单元格中输入公式后按 CTRL+SHIFT+ENTER 组合键即可生成数组公式。

  我们以下图中的Excel表中数据为例,现在我们想根据工资多少进行排序。

(图片较大,请拉动滚动条观看)

  为了便于输入,用Salary来代替$F$2:$F$31这个范围区域,用Name来代替$B$2:$B$31。其具体方法是利用菜单的“插入-名称-定义”功能进行操作。如下图所示,在文本框“当前工作簿中的名称”中输入代号,如“Age”,然后在引用位置中输入所要代替的范围(也可以单击右下角的红色箭头进行区域选择D2:D31单元格),最后单击“添加”即可。


  在单元格H2中输入"=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))",最后按CTRL+SHIFT+ENTER,自动在公式两端加上{}成为数组公式。

  下面我们将公式的作用详细说明如下。

  ROW(参数)函数的作用是得到“参数”所代表的单元格或单元格区域的行号,如果在数组公式中输入这个公式就得到一个行号数组。

  ROW(Salary)记录的是行号的信息, Salary+ROW(Salary)就是再原来工资的数目上再加上行号,这样是为了防止有相同的工资数目出现,避免因相同的工资数而出现错误的排序。

  ROW()-1则是给出一个从1到24的序数数组,便于从大到小对工资进行排序。LARGE(Salary+ROW(Salary),ROW()-1)是在Salary+ROW(Salary)的范围内找出一个ROW()-1大的数X(暂时用X来代替其返回值)。

  MATCH函数是返回在指定方式下与指定数值匹配的数组中元素的相应位置。MATCH(X,Salary+ROW(Salary),0)的作用是在Salary范围内查找X并且返回其所在的行号M(暂时用M代替返回的行号M)。

  INDEX(Name,M)是在Name范围内返回第M个元素的内容。

  这样就完成了从大到小的排序。

  为了便于与原数据进行比较,可在I2中输入“=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))”,然后再按组合键,这样就可以将工资数目从高至低排列出来。

  如果要从小到大排序则只需把LARGE()函数换成SMALL()函数即可。
  上述各函数更详细的用法可参阅Excel的帮助文件或《Excel常用函数的功能和使用方法》。

本站公告

【企业、公司、工作室、出租屋、家庭】
远程解决:网络异常,路由器配置,打印机设置,驱动安装,局域网文件共享,监控调试,电脑故障等电脑周边问题解决,欢迎咨询!
微信:1633694989