標籤：

OFFICE 365的兩個排序函數，顛覆了我對Excel的認知③

03-06

最近推送的五篇文章：

  • OFFICE 365的FILTER函數，顛覆了我對Excel的認知②
  • OFFICE 365的這些功能，顛覆了我對Excel的認知①
  • 你真的理解了相對引用？這三點95%的人都不會，你呢？
  • SUBTOTAL函數：統計篩選出的數據，就用它
  • 【一本不正經系列】我把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、篩選出每個商品的最新單價,並按時間升序排列

這是一個很常見的需求，公式：

=SORT(FILTER(A3:C14,A3:A14=MAXIFS(A3:A14,B3:B14,B3:B14)),1,1)

另外，說明一下，上面這些函數，如果用低版本Excel打開，還是可以得到計算結果，只是會心數組形式體現，會在函數名前會加上xlfn等前綴。比如在Excel 2016打開本文的示例，會顯示成下面這樣：

本文第一個示例的公式：

{=_xlfn._xlws.SORT(A3:E22,5,-1)}

本文最後一個示例的公式：

{=_xlfn._xlws.SORT(_xlfn._xlws.FILTER(A3:C14,A3:A14=_xlfn.MAXIFS(A3:A14,B3:B14,B3:B14)),1,1)}

OFFICE 365還有一些實用的函數，如UNIQUE、SEQUENCE，這些函數相對比較簡單，就不介紹了。相信要不了多信，這些強大實用的函數，很快就會添加到正式版中，不再只是測試功能。

擴展閱讀：

Excel 2019 新增了哪些實用功能？（附正式版下載地址）

本公眾號（Excel偷懶的技術）不同於其他號，一篇文章不會重複推送，要閱讀歷史文章，請在本公眾號主頁發送關鍵詞「目錄」，也可發送其他關鍵詞閱讀相應文章或下載相應資料。

目錄：本公眾號已發表的文章，按類別編寫的目錄導航

禮包：《「偷懶」的技術：打造財務Excel達人》示例文件和贈送禮包

答疑：《「偷懶」的技術：打造財務Excel達人》常見問題答疑。

練習：根據《偷懶的技術》讀者群提問改編的練習題，來源於工作，實用！

整理類：介紹如何應用常見的數據整理技巧，將不規範的數據整理為規範的數據

篩選類：來源於實戰的自動篩選、高級篩選文章

儀錶盤：回複本關鍵詞下載《豪華儀錶盤》的示例文件

圖表模板：下載《財務分析經典圖表模板》，財務分析時簡單套用就可以了

財務圖表1：下載《財務分析經典圖表及製作方法（第1季）》示例文件

財務圖表2：下載《財務分析經典圖表及製作方法（第2季）》示例文件

中秋：用Excel製作的海上明月圖

七夕：一些有趣好玩的熱點文章，主要為自定義格式、條件格式方面的

《「偷懶」的技術：打造財務Excel達人（有趣、有料，財務總監助你「菜鳥」變「達人」！中國十大優秀CFO向志鵬作序力薦，五位上市公司財務總監、暢銷書作者秋葉傾情推薦）》(羅惠民)【簡介_書評_在線閱讀】 - 噹噹圖書

如果本文對你有幫助，走時別忘了點一下文章底部的廣告和右下角的大拇指


推薦閱讀：

TAG:MicrosoftExcel | Excel函數 | Office365 |