動態陣列參照函數(5) - SORT

Excel 365新增動態參照函數SORT,取得排序結果的利器。

資料排序是處理資料過程中的常態需求,對Excel而言,排序也是一種基本操作,使用者必須直接針對原始資料進行排序的操作,排序後的原始資料順序當然也就有了變化,而且,既然是屬於操作層面,若有不同的排序需求,就得再操作一次,原始資料的資料順序與原始的資料順序可能有就大大的不同了。不過,有了SORT函數後就不一樣了!使用者不用直接針對原始資料進行排序的操作,而是透過函數的參照即可傳回排序結果,根本就不必異動到原始資料。SORT所傳回的排序結是一種動態陣列,當原始資料記錄有所增減或內容有所更新,猶如原始資料複本般的動態陣列也會自動更新最新的排序結果。

語法:

=SORT (array, [sort_index], [sort_order], [by_col])

參數: 

  • array – 要進行排序的資料範圍或陣列。
  • [sort_index] – 作為排序關鍵的欄索引,也就是要以哪一個欄位作為排序的依據,若未表明,此參數的預設值為1。
  • [sort_order] – 排序的順序依據,1代表升冪;-1代表降冪。若未表明,此參數的預設值為1,進行升冪排序。
  • [by_col] - 設定排序的運作是將欲排序的資料範圍或陣列內容,依據循欄(By Column)或是循列(By Row)的方向進行順序的對調與調整排序。這是一可省略的參數設定,若沒設定此參數,則預設為FALSE,採用循列(By Row)方向進行排序作業,若是設定為TRUE,則採用循欄(By Column)方向進行排序作業。

範例:

以下的實作範例是一個名為「交易清單」的資料表,內含五個資料欄位,分別名為「交易編號」、「交易日期」、「地區」、「交易金額」及「經手人」,若要針對首欄「交易編號」進行由小到大的排序時,在空白的目的地儲存格,例如:H2,輸入以下的SORT函數:

=SORT(交易清單)

括弧裡僅需描述欲進行排序的原始資料所在處,一個參數的提供就立刻達到所要的排序需求。

如果要根據原始資料來源裡的「交易日期」欄位,進行由小到大的排序,則再加上所需的參數也是輕鬆完成排序喔!例如:

=SORT(交易清單,2,1)

其中,第二個參數[sort_index]設定為「2」,表示要以資料來源的第二個欄位作為排序的依據,此例為「交易日期」欄位。第三個參數[sort_order]設定為「1」,即表示排序的順序為升冪排序,也就是日期是由最舊到最新進行升冪排序。

如果要根據原始資料來源裡的「交易金額」欄位,進行由大到小的降冪排序,則可以輸入以下的SORT函數:

=SORT(交易清單,4,-1)

因為「交易金額」欄是原是資料來源的第4個欄位,因此,第二個參數[sort_index]要設定為「4」,而降冪排序的需求就必須將函數裡的第三個參數[sort_order]設定為「-1」,即表示排序的順序為由大到小的排序。

傳統的資料表架構,縱向是資料欄位、橫向是一筆筆的資料記錄,如剛剛練習的「交易清單」資料表。通常對資料表進行排序時,是針對橫向整列(Row)進行上、下順序的調整,例如:「金額」欄位進行降冪排序時,整列的資料便是依據金額欄位裡的數據而上、下對調,以達到由大到小的整列順序調整。吾人稱之為Sort by row,這也是SORT函數的預設值。不過,若是想要排序的原始資料並非傳統的資料表架構,而是如下圖所示橫向是資料欄位(「經手人」、「業績」、「銷售量」)、縱向是一筆筆的資料記錄(位於C到M欄的11筆資料)的原始資料範圍,而此範例已事先命名為「銷售統計」。那麼排序的需求應該就是Sort by column的性質,例如:若要根據首列「經手人」姓名筆畫順序由小到大(由左至右)的進行排序,則可輸入以下SORT函數:

=SORT(銷售統計,,,TRUE)

其中,第二個參數[sort_index]省略便表示會將首列視為排序依據;第二參數[sort_order]也省略則表示排序的順序依據是由小到大,而最後一個參數
[sort_order] 設定為TRUE即表示要sort by column進行排序。

如果想要根據業績高低由大到小進行排序,則可撰寫以下的SORT函數: 

=SORT(銷售統計,2,-1,TRUE)

第一個參數array在此自然指的就是名為「銷售統計」的資料範圍;而第二個參數[sort_index]設定為「2」指的便是設定第2列的「業績」為排序依據;至於第三個參數[sort_order]設定為「-1」則表示要進行的排列順序是降冪排序(由大到小);最後一個參數[by_col]設定為TRUE,正表示此次的排序是採用循欄(By Column)方向進行各欄左右對調的排序作業。

註:此Excel文章內文實作活頁簿檔案下載