本文目录一览:
- 1、如何用excel函数给数据排序
- 2、如何在excel中使用rank函数排序
- 3、excel有哪些排序函数?如何使用?
- 4、Excel中如何利用函数按行排序?
- 5、excel 排列组合公式?
- 6、excel中用函数进行排位的方法步骤
如何用excel函数给数据排序
如何用Excel函数排序与筛选:
Execl本身具有很方便的排序与筛选功能,下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足,首先无论排序或筛选都改变了原清单的原貌,特别是清单的数据从其它工作表链接来而源数据发生变化时,或清单录入新记录时必须从新进行排序或筛选。其次还有局限,例如排序只能最多对三个关键字(三列数据)排序,筛选对同一列数据可用“与”、或“或”条件筛选,但对不同列数据只能用“与”条件筛选。
例如对某张职工花名册工作簿,要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的,如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁,男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用,即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单,其中有的职工已经退休,对在职职工的年龄进行排序时无法剔除已退休职工的数据。
本文试图用Execl的函数来解决上述问题。
一、用函数实现排序
题目:如有一张工资表,A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序,且在职工总收入相同时再按工资从多到少排序,在职工总收入和工资相同时再按奖金从多到少排序,在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。
方法:G1单元格填入公式
“=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))”,
CONCATENATE 是一个拼合函数,可以把30个以下的单元的数据拼合成一个数据,这些被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减,是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数,CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录,在本例中指收入为零的记录。(在上文提到的职工年龄排序,则公式改为“if(f2="退休", 10^100,.....)”,即剔除了退休职工。)
第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标,见到黑十时双击鼠标就完成了G1到G500的填充)。
第三步在在H2单元填入公式“=MATCH(SMALL(G:G,ROW(A1)),G:G,0)”与第二步一样拖放到H501单元格。此公式实际上是把三列公式合成一列公式,ROW(A1)即为A1的行数是1,随着向下拖放依次为2、3、4...,SMALL(G:G,ROW(A1))为 G列中最小的数随着向下拖放依次为第2、第3、..小的数,MATCH(SMALL(G:G,ROW(A1)),G:G,0)即为G列各行的数据中最小、第2、第3小等的数据在第几行。
第四步把A1至F1单元格的表头复制到I1至N1单元格,在I2单元格输入公式“=INDEX($A$2:$F$501,$H2, COLUMN(A$1))”INDEX函数是一个引用函数,即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1)列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格,点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。
以上叙述看似繁杂实际非常简单,只要把A1至F1的表头复制到I1至N1单元格,再分别在G1、H2、I2单元格输入公式然后向下拖放,即使对EXCEL应用不熟练的同志一分锺内便能完成。
对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的,如H列的函数中的SMALL改为LARGE,上面例子数据就从小到大排列了。如H2单元格的公式改为“=IF(O1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(LARGE(G: G,ROW(A1)),G:G,0))”并把H2单元格的公式向下拖放。这样在O1单元格输入1上面例子数据是从大到小排列的,O1单元格输入1以外的数上面例子数据就从小到大排列了。
如在H列前插入若干列,如插入一列,则现在的H列输入类似G列的公式,例如“=if(F2=0,10^100,d2)”,现在的I列的公式改为“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(SMALL(H:H,ROW(A1)),H:H,0)))”即在P单元格输入1以外的值就实现了按奖金大小排序.这样只要通过改变P1(原来的O1单元格)单元格内容的改变就能立即得到按不同要求的排序。
如何在excel中使用rank函数排序
rank是T_sql
函数,rank()函数返回结果集分区内指定字段的值的排名,指定字段的值的排名是相关行之前的排名加一。具体使用步骤如下:
1、在excel中编辑好需要排序的内容。
2、在C2单元格输入公式“=RANK(B2,$B$2:$B$4,0)”。
3、点击键盘“Enter”。
4、双击图示符号,向下填充公式。
5、公式填充后,排名结果就出来了。
注意事项:
1、操作第二步,在C2单元格输入公式“=RANK(B2,$B$2:$B$4,0)”时,引用单元格时必须是绝对引用“$B$2:$B$4”,否则在向下填充公式时会出现错误。
2、操作第二步,在C2单元格输入公式“=RANK(B2,$B$2:$B$4,0)”时,排位方式输入“0”是降序排列,若需要升序排列输入“1”即可。
扩展资料:
语法:
RANK()
OVER([partiton_by_clause]order
by
clause)
partition_by_clause
将from子句生成的结果集划分为应用到RANK函数的分区。
Order_by_clause确定将RANK值应用到分区中的行时所使用的顺序。
以下用一个成绩表作示例:
table架构:
s_score(s_class(班级),s_id(学号),s_score(分数))。
SQL
statement1:
Select
rank()
over(order
by
s_score
desc)
as
名次,s_class,s_id,s_score
from
s_score。
参考资料来源:百度百科-rank
excel有哪些排序函数?如何使用?
在日常工作中,经常使用excel软件来办公,那么排序公式excel怎么设置,一起来看看吧。
1、首先打开或新建一个表格,如下图所示。
2、然后输入函数公式=rank(),如下图所示。
3、接着点击选择一个成绩。选择所有成绩,如下图所示。
4、最后点击回车键即可永排序公式计算出排名,如下图所示就完成了。
Excel中如何利用函数按行排序?
步骤1:首先进入Excel软件中,找到即将使用ROW函数进行排序的数据表格,然后找到对应的单元格,具体如图所示。
请点击输入图片描述
步骤2:找到单元格后,按照如图所示,在单元格中输入ROW函数,其函数格式为“=ROW(”。
请点击输入图片描述
步骤3:此时,完善ROW函数,如图所示,ROW函数的输入格式为“=ROW()”。
请点击输入图片描述
步骤4:如图所示,由于考生成绩单和序号那两行不占行数,故此这里需要减去两行,故此最终利用ROW函数排序的函数格式为"=ROW()-2"。
请点击输入图片描述
步骤5:ROW函数输入完毕后,点击回车键,如图所示,可看到第一个序号排出来了,点击单元格十字下拉复制,可见整个表格序号利用ROW函数排出来了。
请点击输入图片描述
请点击输入图片描述
END
总结:
1
1.首先进入Excel软件中,找到即将使用ROW函数进行排序的数据表格,然后找到对应的单元格。
2.找到单元格后,在单元格中输入ROW函数,其函数格式为“=ROW(”。
3.此时,完善ROW函数,故此ROW函数的输入格式为“=ROW()”。
4.由于考生成绩单和序号那两行不占行数,故此这里需要减去两行,故此最终利用ROW函数排序的函数格式为"=ROW()-2"。
5.ROW函数输入完毕后,点击回车键,可看到第一个序号排出来了,点击单元格十字下拉复制,可见整个表格序号利用ROW函数排出来了。
excel 排列组合公式?
Excel有排列组合公式,PERMUT为排列函数,COMBIN为组合函数。
1、电脑打开Excel表格,输入组合函数=COMBIN(50,3)。
2、回车就会得到19600。
3、输入排列公式=PERMUT(50,3)/PERMUT(3,3)。
4、回车就可以了。
excel中用函数进行排位的方法步骤
Excel 中经常需要使用到函数进行对数据的一个排位操作,排位操作具体该如何用函数来完成呢?下面是我带来的关于excel中用函数进行排位的 方法 ,希望阅读过后对你有所启发!
excel中用函数进行排位的方法
用函数进行排位步骤1:平常生活中,我们想将一列数字排序,大家最易想到的是用排序按钮,然后下一列用自动填充功能填充,但是当遇到两个数据相同的时候,这样填充出来的数据就不能很好的显示排名情况。如下图所示,两个105相同的分数,但是自动填充显示的名次却不一样。我们还得对这些相同分数的排名进行手动修改。
用函数进行排位步骤2:如何让EXCEL帮助我们,不用排序快捷键,而且能去除重名次呢。首先我们不用将数据排序,回到最初数据形式。如下图所示:
用函数进行排位步骤3:下面我们需要运用RANK函数。在B2单元格中输入以下内容:=RANK(A2,$A$2:$A$24)。其中A2是需要确定位次的数据,$A$2:$A$24表示数据范围,括号里的内容即表示A2单元格数据在A2:A24这个数据区域的排名情况,公式计算结果将被显示在B2单元格里。下图1为公式,下图2为计算结果的显示。
用函数进行排位步骤4:然后选中B2单元格,将鼠标放在右下角,鼠标变成小十字的时候,按下鼠标左键不放,往下拉到B24单元格,这样所有分数的排名就显示出来了。如下图所示:
rank函数在excel使用的方法一:正排名
此例中,我们在B2单元格求20这个数值在 A1:A5 区域内的排名情况,我们并没有输入order参数,不输入order参数的情况下,默认order值为0,也就是从高到低排序。此例中20在 A1:A5 区域内的正排序是1,所以显示的结果是1。
rank函数在excel使用的方法二:倒排名
此例中,我们在上面示例的情况下,将order值输入为1,发现结果大变,因为order值为1,意思是求倒数的排名,20在A1:A5 区域内的倒数排名就是4。
rank函数在excel使用的方法三:求一列数的排名