OFFICE 365的两个筛选函数,颠覆了我对Excel的认知③

最近推送的五篇文章:

在前二篇介绍OFFICE 365的文章中,第一篇是介绍动态数组的强大:

OFFICE 365的这些功能,颠覆了我对Excel的认知①

第二篇带我们领略了逆天的筛选函数:

OFFICE 365的FILTER函数,颠覆了我对Excel的认知②

有了Filter函数,VLOOKUP+COUNTIF、LOOKUP+COUNTIF+OFFSET、INDEX+SMALL+ROW三支组合表示非常紧张,人心惶惶,因为它们很快要失业了。

今天我们再来看排序函数SORT,看它是如何抢排序功能的饭碗的。

一、SORT函数语法介绍
单词释义

sort

英[sɔ:t]   美[sɔ:rt]

n.分类,类别; 品质,本性; 方法; 一群;

vt.分类; 整顿,整理; 适合;

vt.挑选; 把…分类; 将…排顺序;

函数语法

=SORT(数据区域,按第几列排序,[按升序还是降序],按行还是按列排序)

第三个参数为1按升序(默认),为-1按降序;

第四参数为1按列排序,为0按行排序(默认)。

另外,还有一个SORTBY函数,它可指定多个排序关键字。语法:

=SORTBY(数据区域,排序区域1,排序规则1,排序区域2,排序规则2......)

二、SORT函数的基本应用

1、按一个关键字排序

按第5列“金额”降序排列,公式:

=SORT(A3:E22,5,-1)

2、按列升序排列

公式:

=SORT(B2:E14,1,1,1)

3、按权重排序

使用SORTBY按权重的排序公式:

=SORTBY(A4:E15,B4:B15*$B$1+C4:C15*$C$1+D4:D15*D1+E4:E15*E1,-1)

权重指标比较多的话,按上面这样写比较费劲,可以用妹妹头函数MMULT将其完善一下:

=SORTBY(A4:E15,MMULT(B4:E15,TRANSPOSE($B$1:$E$1)),-1)
4、按双指标排序
先按办事处升序,办事处内再按金额降序,公式:
=SORTBY(A3:E22,C3:C22,1,E3:E22,-1)
实际上我们也可用SORT函数结合常量数组来实现双指标排序:
=SORT(A3:E22,{3,5},{1,-1})
三、SORT函数的拓展应用

1、按各办事处的合计金额降序排序

如果不用分类汇总来排序的话,一般是用辅助列来实现按某字段合计金额排序,现在有了SORTBY函数,用它结合SUMIF轻松搞定,公式:

=SORTBY(A3:E22,SUMIF(C3:C22,C3:C22,E3:E22),-1)

2、按各办事处的合计金额降序排序,办事处内部再按金额降序

公式:

=SORTBY(A3:E22,SUMIF(C3:C22,C3:C22,E3:E22),-1,E3:E22,-1)

3、筛选出各办事处前二名,并按办事处名称升序排列

公式:

=SORT(FILTER(A3:E22,COUNTIFS(C3:C22,C3:C22,E3:E22,">="&E3:E22)<3),3,1)

4、筛选出各办事处前二名,并按办事处名称升序,办事处内按金额降序排列

公式:

=SORT(FILTER(A3:E22,COUNTIFS(C3:C22,C3:C22,E3:E22,">="&E3:E22)<3),{3,5},{1,-1})

5、筛选出每个商品的最新单价,并按时间升序排列

这是一个很常见的需求,公式:

OFFICE 365还有一些实用的函数,如UNIQUE、SEQUENCE,这些函数相对比较简单,就不介绍了。相信要不了多久,这些强大实用的函数,很快就会添加到正式版中,不再只是测试功能。

(0)

相关推荐