私家车能做什么生意:行号跟着数据跑—excel之自动排序
来源:百度文库 编辑:中财网 时间:2024/04/29 05:48:27
行号跟着数据跑—excel之自动排序 在Excel中利用数据的排序功能可以很轻松地进行排序,但这种排序会破坏原有的数据清单。笔者经过摸索,发现了两种可以利用公式自动排序且不破坏原始数据清单的方法。
一、利用数组公式
数组公式可以同时进行多重计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。数组公式的创建方法很简单,在单元格中输入公式后按 CTRL+SHIFT+ENTER 组合键即可生成数组公式。我们以下图中的Excel表中数据为例,现在我们想根据工资多少进行排序。
为了便于输入,用Salary来代替$F$2:$F$31这个范围区域,用Name来代替$B$2:$B$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值。
两种方法各有优劣,数组公式法较简单,但不太好理解,而普通公式法则正相反,容易理解但操作较繁琐。
一、利用数组公式
数组公式可以同时进行多重计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。数组公式的创建方法很简单,在单元格中输入公式后按 CTRL+SHIFT+ENTER 组合键即可生成数组公式。我们以下图中的Excel表中数据为例,现在我们想根据工资多少进行排序。
为了便于输入,用Salary来代替$F$2:$F$31这个范围区域,用Name来代替$B$2:$B$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值。
两种方法各有优劣,数组公式法较简单,但不太好理解,而普通公式法则正相反,容易理解但操作较繁琐。
excel排序时,怎样让每一行的数据跟着都变化呢
excel怎样自动排序
Excel自动排序怎么了?
在 excel 中如何对自动求和得出 的数据进行排序
excel自动生成数据
excel对应数据自动录入
请问EXCEL中的自动排序(阿拉伯数字自动递增)怎么设置?
在excel中,筛选完数据后,为什么不让排序,
[[excel]]通过调用公式得到的数据,能[排序???
这样的数据怎样在EXCEL中排序
EXCEL排序
excel排序
数据排序
excel中总分出来后如何自动排序
EXCEL自动如何生成1.2.3.4.5.6这样的排序
Excel自动排序问题,怎么让带字母的数字正常排序?
EXCEL中,数据不能自动求和是怎么回事?
EXCEL中,数据不能自动求和是怎么回事?
excel中数据修改好自动变红
用EXCEL实现自动核对填充数据
excel自动找数据的问题
EXCEL文件里数据自动增加
在Excel 2003中对某一列数据进行排序后,如何取消排序结果.(已保存过,不能撤消)
在使用excel排序功能时,表格中有合并的单元格,需要对数据进行排序怎么办?