excel怎么把数据从大到小排列(并按照数值由大到小排序)
从数据区域提取符合条件的数据,并按照由大到小排序,可以先筛选出符合条件的数据,然后将筛选出的数据粘贴到目标区域,最后再进行排序。使用这种方法比较简单,不过缺点是,当数据更新时,需要重复执行筛选排序的操作。
本文接下来介绍用函数法和power query法,从数据源提取符合条件的数据,并自动排序。使用这两种方法的优点是,当数据更新时,不用重复操作就可以更新结果。
本文案例演示使用的是Excel2016版本,使用的函数为SUMPRODUCT、MATCH、INDEX函数。如果使用的是OFFICE365版本,可以使用FILTER SORT函数。
1
案例描述
如下图所示,A1:B8为成绩表。要求提取成绩大于等于80的记录,且提取的记录按成绩由高到低排序,结果如E1:F5所示。
2
函数法
1、在C列构建辅助列,并在C2单元格输入以下公式:
=IF(B2>=80,SUMPRODUCT(--($B$2:$B$8>B2)) COUNTIF($B$2:B2,B2),"")
拖动C2单元格填充柄,向下复制公式。
公式解析:
(1)SUMPRODUCT(--($B$2:$B$8>B2)),计算B2:B8区域中大于B2单元格中数值的个数。
(2)COUNTIF($B$2:B2,B2),计算B2单元格的值在$B$2:B2中出现的次数。B2单元格数值为“88”,该数值第1次出现,因此COUNTIF($B$2:B2,B2)=1。B4单元格数值也为“88”,但是第2次出现,因此因此COUNTIF($B$2:B4,B4)=2。
(3)SUMPRODUCT COUNTIF函数得到的结果,实际是B2单元格的数值在B2:B8中的排序,如下图所示。B6单元格的数值为“91”,数值最大,因此排序为“1”;B2单元格的数值为“88”,仅次于B6单元格的值,因此排序为“2”;而B4单元格的数值和B2单元格相同,也为“88”,但是由于是第2次出现,因此排序为“3”。
(4)IF函数的作用是,当B列中的数值小于80时,在C列中不显示排序号,显示为空文本。
2、在E2单元格输入以下公式:
=IFERROR(INDEX(A$2:A$8,MATCH(ROW()-1,$C$2:$C$8,0)),"")
拖动E2单元格填充柄,将公式复制到F2单元格,并拖动填充柄,将E2、F2单元格公式向下复制。
E2、F2单元格的公式是INDEX MATCH函数组合用于查找的经典用法,不再赘述。想要了解更多INDEX MATCH函数组合,可以阅读文章:INDEX MATCH函数用于查询的6种典型用法
3、按快捷键【Ctrl T】,将数据源A1:B8转为超级表。
当数据源中新增记录时,函数返回的结果会自动更新,如下图所示。
3
power query方法
使用power query法非常简单。
1、选中A1:B8任意单元格,单击【数据】-【从表格】。
在弹出的【创建表】对话框中,单击确定。
2、在打开的power query编辑器中,单击【成绩】列的筛选按钮,选择【数字筛选器】-【大于或等于】。
在打开的【筛选行】对话框中,输入筛选条件“80”。然后单击确定。
3、再次单击【成绩】列的筛选按钮,选择【降序排序】。
得到的结果如下图所示:
4、单击【关闭并上载】,将表格加载到工作表中。
得到的结果如下图所示:
5、如果数据源更新,单击【数据】-【刷新】,即可更新结果。如下图所示:
- 纯结是什么意思啊(当前切除是否过激)
- vivo手机怎么样打开后台运行设置(你还不知道就很遗憾啦)
- 创造与魔法怎么免费买坐骑(创造与魔法星彩墟鲲坐骑获取攻略)
- 征集志愿填报的技巧和原则(了解征集志愿)
- 文案短句干净治愈阳光励志学生(文案短句干净治愈阳光励志)
- 水动力吸脂后效果如何(做完居然变成了这样·····)
- 爱心早餐的做法和图片(好吃)
- 大陆漂移学说谁提出来的知到(学习强国四人赛题库第二十辑(1901~2000))
- 推饼怎么玩(生日宴上的豪赌竟是精心设计的陷阱)
- 巴黎协定是几年(美国为何“欲走还留”)
- 儿童简易树叶剪纸图片(一张纸剪出好看的枫叶)
- 这6种人不适合佩戴貔貅(让老司机来告诉你)
- 如何巧妙的回答离婚原因(或不小心暴露离婚三个真正原因)
- 眉弓囊肿手术后恢复图片(几个月内突然“野蛮生长”)
- 高考倒计时怎么考前准备(高三教师教你这样备考)
- 手机版荒野行动怎么降落(要么吃鸡)
- 蒲公英泡水喝什么时间喝最佳(1个月后)
- 张亮麻辣烫是哪年开的(管不住5000加盟商)