您的位置首页 >生活百科 >

excel 排序函数 动态排序公式详解

Excel 排序函数核心就是新版 365 支持的 SORT 动态数组函数,可以直接对选定区域按指定列升序或者降序自动输出排序后的完整结果,原始数据不会被改动,属于动态溢出公式,输入一次公式就会自动填充多行多列,除了 SORT 之外,老版本 Excel 没有原生排序函数,一般需要搭配 INDEX MATCH LARGE SMALL RANK 组合公式实现等效排序,很多用户容易混淆,SORT 是直接输出排序后的整组数据,RANK 只是生成名次编号,并不会把数据重新排列,两者功能不一样,SORT 还支持多关键字排序,也就是先按第一列排序,第一列相同的时候再按第二列继续排序,这个能力是传统组合公式很难实现的,理解动态数组溢出特性,就能明白为什么 SORT 公式只需要在一个单元格录入,结果会自动铺满下方和右侧区域,同时也要知道旧版 Excel 2019 以及更早版本不支持 SORT 函数,遇到这种环境就必须改用老式组合排序函数方案。

SORT 函数基础用法

这个是微软 365 和 2021 版本自带原生排序函数

公式

=SORT (A2:B10,2,-1)

参数拆解

A2:B10 要参与排序的整个数据区域

2 代表按照区域里面第 2 列作为排序关键字

-1 代表降序,数值大的排在前面,如果填写 1 就是升序,从小到大排列

操作步骤

1 选中想要输出排序结果的起始单元格

2 在单元格输入上面 SORT 排序函数公式

3 按下回车,动态数组自动溢出,自动生成排序后的全部行

4 如果原始源数据发生修改,排序结果会自动刷新,属于实时动态更新

补充要点 如果省略第二个和第三个参数,默认就是按第 1 列升序排序,写法为 = SORT (A2:B10)

SORT 多关键字排序函数

业务表格经常需要多条件排序,比如先按部门排序,部门相同再按分数降序

公式

=SORT (A2:C10,{1,-1;2,-1})

参数说明

{1,-1;2,-1} 代表第一个关键字是第 1 列,排序方向降序,第二个关键字是第 2 列,排序方向降序

原理 第一列相同的数据,自动继续按照第二列做二次排序,适合人员台账成绩表这类复杂排序场景

注意 这个多参数数组写法,在低版本不支持,仅支持动态数组版本 Excel

SORTBY 排序函数

和 SORT 属于同类动态排序函数,区别是 SORTBY 可以用另外独立区域作为排序依据,不需要排序关键字必须在原数据区域内部

公式

=SORTBY (A2:B10,B2:B10,-1)

参数拆解

A2:B10 要输出排序的数据源区域

B2:B10 独立的排序依据区域

-1 降序

适用场景 原始数据和排序依据不在同一个连续区域,SORT 就不好处理,此时优先选用 SORTBY 排序函数,灵活度更高

老版本兼容 组合排序函数方案

Excel2019 以及更早版本没有 SORT 动态数组函数,使用 LARGE SMALL INDEX MATCH 实现排序

降序排序提取数值公式

=INDEX ($A$2:$A$10,MATCH (LARGE ($B$2:$B$10,ROW (A1)),$B$2:$B$10,0))

函数拆解

LARGE 提取区域里面第 N 大数值

ROW (A1) 下拉的时候自动变成 1 2 3,依次获取第一名第二名第三名

MATCH 找到这个数值在原区域对应的位置

INDEX 根据位置取出对应的姓名

升序版本把 LARGE 替换成 SMALL 即可

注意缺陷 如果出现同分,MATCH 只会匹配第一个位置,会出现重复结果,需要额外增加辅助列处理同分,公式复杂度明显高于 SORT

排序函数和菜单排序对比

项目SORT 动态排序函数菜单排序功能
原始数据保持不变直接修改原表行顺序
更新方式源数据变动自动刷新不会自动更新,需要重新点排序
版本要求365 2021 才支持所有 Excel 版本都有
多关键字原生支持数组多关键字界面可视化设置更直观

高频踩坑要点

第一 SORT 溢出区域如果有其他单元格已经存在内容,会出现 SPILL 报错,这个是动态数组最常见报错,需要清空溢出目标区域单元格

第二 排序函数识别文本数字的时候排序顺序会异常,文本型数字和真数值排序规则不一样,提前统一成数值格式

第三 SORTBY 的排序依据区域行数必须和数据源行数保持一致,行数不匹配公式直接报错

第四 老版本组合排序函数遇到同分重复值,结果会重复,正式报表使用前要测试同分情况

【常见问题】

问题 1 excel 排序函数 SORT 提示 SPILL 错误是什么原因

回答 1 excel 排序函数 SORT 出现 SPILL 报错,是因为公式需要溢出的区域里面已经有原有内容,清空目标区域单元格之后,excel 排序函数就可以正常生成动态排序结果。

问题 2 excel 排序函数旧版 Excel 能不能使用 SORT

回答 2 excel 排序函数 SORT 属于动态数组函数,Excel2019 以及更早版本不支持,想要实现 excel 排序函数效果,需要改用 INDEX MATCH LARGE 组合老式排序公式。

问题 3 excel 排序函数 SORT 和 SORTBY 有什么区别

回答 3 excel 排序函数 SORT 是用数据源内部的列作为排序关键字,SORTBY 排序函数可以使用独立外部区域作为排序依据,当排序依据不在原数据区域里面,优先选择 SORTBY 排序函数。

问题 4 excel 排序函数能不能实现多关键字二次排序

回答 4 excel 排序函数 SORT 支持多关键字二次排序,在第二个参数写入数组,依次设置每一列关键字和升降序,第一列数值相同的数据会自动按照第二列继续排序。

【免责申明】

本文内容基于微软 Excel 动态数组版本编写,WPS 新版表格兼容部分排序函数,不同版本支持情况存在差异,老式组合排序公式存在同分缺陷,仅用于表格数据处理学习参考,正式业务使用前建议测试数据边界情况。

今日大家都在搜的词: