Office中国论坛/Access中国论坛

标题: 行号跟着数据跑—excel之自动排序 [打印本页]

作者: admin 时间: 2007-8-31 11:10
标题: 行号跟着数据跑—excel之自动排序
在Excel中利用数据的排序功能可以很轻松地进行排序，但这种排序会破坏原有的数据清单。笔者经过摸索，发现了两种可以利用公式自动排序且不破坏原始数据清单的方法。　　一、利用数组公式　　数组公式可以同时进行多重计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。数组公式的创建方法很简单，在单元格中输入公式后按 CTRL+SHIFT+ENTER 组合键即可生成数组公式。我们以下图中的Excel表中数据为例，现在我们想根据工资多少进行排序。　　为了便于输入，用Salary来代替$F$2F$31这个范围区域，用Name来代替$B$2B$31。　　在单元格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()函数即可。二、利用普通公式进行排序　　在K2单元格中输入公式"=IF(B2=0,0,INT(CONCATENATE(INT(F2),200-ROW(B1))))"，将该公式下拉到K31（“下拉”指将鼠标移动到公式所在单元格的右下角，当鼠标变成一个小十字符号的时候，按住鼠标左键向下拉动，则此列的单元格中会自动加上相应的公式，下同）。　　该公式的作用是将工资与所在的行号信息进行整合。　　公式中的ROW(B1)就是B1单元格所在的行号。　　CONCATENATE函数是一个整合函数，本处是把F列的数据和它所在行数整合为一个数据，这样在对它进行排序后就包含了它所在的行的信息。　　用200减去ROW(B1)是为了使CONCATENATE的第2个参数保持3位数，保证整合后的数据的位数一致（当然本处用100来减也可以）。　　INT函数是为了把原来的文本内容变为数字。　　在L2单元格中输入"=LARGE(K:K,ROW(B1))"，并下拉至L31，对K列的数据进行排序。　　在N2中输入"=IF(L2=0,0,200-RIGHT(L2,3))"，并下拉至N31。该公式取得数据的最初行数信息。RIGHT(L2,3)的返回值是L2单元格数据的后3位数，用200来减去此数就是该数据所在的行数。　　在M2单元格中输入"=IF(N2=0,0,INDEX(Name,N2,))"，再下拉至M31，即可完成排序。该公式是根据行号来取得所对应的Name值，其实在N列中就已经完成了排序。INDEX(Name,N2)就是根据N2单元格中所代表的行号来返回其在name区域中所代表的单元格的内容。使用IF()函数是一种安全策略，防止出现0值。　　两种方法各有优劣，数组公式法较简单，但不太好理解，而普通公式法则正相反，容易理解但操作较繁琐。

作者: hm6364 时间: 2007-9-14 09:25
谢谢!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

作者: coolking 时间: 2007-9-24 16:31

第一种方式仍然可能存在问题
比如表中F20 木嫆清工资为3265，F21苗人凤工资若为3264，
二人的工资数+行号后，恰好相等，则在排序后的数据中，出现二次木嫆清，而没有苗人凤
第二种方式正在研究中……

作者: coolking 时间: 2007-9-25 12:44
第一种方法，把公式改为：
=INDEX(Salary,MATCH(LARGE(Salary+0.001*ROW(Salary),ROW()-1),Salary+0.001*ROW(Salary),0))
问题解决。
想问一下搂主，代替$F$2F$31这个范围区域的salary在excel中是怎么定义的？

作者: admin 时间: 2007-9-25 14:44
谢谢这网友的关注...代替$F$2F$31这个范围区域的salary在excel中是怎么定义的？你选定F2&F31这写列然后点:插入--名称--定义将那里的名字定义salary或其他的都可以

作者: twins 时间: 2007-12-9 03:02
我也要来学习学习！！

作者: fzwjz 时间: 2008-6-15 09:05
我也要来学习学习！！

作者: fzwjz 时间: 2008-6-15 09:08
我也要来学习学习！！

作者: fzwjz 时间: 2008-6-16 21:47
我也要来学习学习！！

作者: runty 时间: 2008-7-8 21:55
看一下附檔比較容易理解

作者: linag516 时间: 2008-7-29 16:52
[:27]

作者: caijiuhua 时间: 2008-9-4 22:11
来看看

作者: ren6399 时间: 2008-9-25 06:56
学习学习哦自己太菜了哦

作者: rongrong257 时间: 2008-10-10 14:21
我也要来学习学习！！

作者: 刘志文 时间: 2008-12-15 21:16
学习学习[:50] [:50]

作者: zzgx123 时间: 2009-1-14 15:33
我也要来学习学习！！

作者: wanghx8866 时间: 2009-1-23 16:54
我也要来学习学习！！

作者: tgu 时间: 2009-1-30 16:21
学习，学习一下

作者: yanwei82123300 时间: 2009-3-18 12:12
我也来学习

作者: sxzr 时间: 2009-4-17 21:44
谢谢！我也来学习了

作者: wanderingcat 时间: 2009-4-22 12:18
辛苦啦

作者: yuhj 时间: 2009-10-29 16:36
hao

作者: 刘青梅 时间: 2016-12-5 12:29
我也要来学习

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)