【Excel Function】FILTER 篩選；SORT、SORTBY 排序

---

一般提到「篩選」、「排序」，大家第一個想到的就是：工具列 ＞ 資料 ＞ 排序與篩選 ＞ 篩選，那個漏斗圖案的功能。

不過你知道嗎？其實篩選、排序也可以使用函數的方式操作，而這就是本次文章要介紹的主題－ FILTER 和 SORT 函數。

延伸閱讀：【Excel Function】FILTER 多條件篩選-集合(Set theory)：and、or、not、delta

Excel 範例檔案下載：Excel-FILTER-SORT-SORTBY.xlsx

---

篩選、排序

在介紹函數之前，先簡單介紹一般Excel的篩選、排序是如何使用。

首先選取要篩選的標題，並點擊篩選：工具列 ＞ 資料 ＞ 排序與篩選 ＞ 篩選（紅框處）

接著會發現標題列的每個儲存格(Cell)中多了一個向下箭頭。接下來就可以準備進行篩選啦！

篩選

假設我們要找出死亡率為 0% 的資料，點選［F1］這格的箭頭符號，選擇 0.00% 按下確定。

這邊可以看到被篩選出的資料，而［F1］這格的箭頭符號，變成了篩選的漏斗圖示。

數字篩選

如果資料為數值或時間，也可以使用簡單的條件去篩選。假設我們想整理出，死亡率小於 1% 的資料。首先點選［F1］這格的箭頭符號，選擇數字篩選 ＞ 小於。

這邊填入篩選條件：小於 0.01(1%)，按下確定就可以得到結果。此外，這邊也支援多條件判斷，大家可以自行試試不同的條件。

排序

排序主要有兩種，分別是升冪(Ascending)，降冪(Descending)。假設要將死亡率從小到大排列，點選［F1］這格的箭頭符號，選擇從最小到最大排序，就會得到以死亡率升冪排列的資料。

這邊可以同時使用篩選和排序，大家也可以多多利用這個功能操作！

---

FILTER、SORT、SORTBY

介紹完一般的篩選、排序的用法，接著直接進入今天的主題：使用 FILTER、SORT、SORTBY這三個函數，能夠不異動原始資料表，直接從中整理需要的資料。趕快來看看怎麼做吧！

FILTER 篩選

=FILTER(a,b,c)
a：資料範圍
b：篩選條件「多條件時：(條件一)*(條件二)*(條件三)……」
c：不符合條件時，回傳值

回傳：範圍內符合條件之資料（以篩選欄位升冪排序）

這裡我們使用 FILTER 找出：死亡率大於 1% 的資料。

=FILTER(資料範圍,篩選條件)
=FILTER(資料範圍,(條件一)*(條件二)*(條件三).....)

第一個參數資料範圍，這裡使用20220506_COVID19分頁(sheet)的資料：
「’20220506_COVID19′!A2:F199」

第二個參數篩選條件，20220506_COVID19分頁(sheet)的 F 欄大於1%(0.01)：
「’20220506_COVID19′!F2:F199>0.01」。

這邊提醒一下，直接在打公式的時候點選該分頁，就會自動帶入「’20220506_COVID19′!」

需要注意幾個小地方：

• 這兩個的資料行數(Row)要一樣，如果不一樣會顯示「#VALUE!」
  ERROR範例：=FILTER(A1:A2,B1:B3>0.01)
• 如果沒有符合條件的值，又沒有第三個參數時，就會顯示「#CALC!」

這樣就會拿到篩選後的資料，同時也不會動到原始資料。如果一份資料表需要呈現不同面相的資料，FILTER 就是非常實用的函數。

至於多條件篩選的部分，我在另一篇文章中會詳細介紹👇👇👇

延伸閱讀：【Excel Function】FILTER 多條件篩選-集合(Set theory)：and、or、not、delta

---

FILTER + 統計函數

使用 FILTER 篩選出來的資料為陣列、矩陣(Array)，而只要是陣列都可以使用基本的統計函數去運算，像是總和(SUM)、平均(AVERAGE)、標準差(STDEVA)…等。
如果需要將重複條件的資料進行整理運算，大家一定會想到「樞紐分析」(PivotTable)。當然樞紐是個相當強大的工具，操作起來也非常容易，可以用拖曳方式進行。不過這邊我們想進一步介紹如何使用 FILTER 達到相同的功能。

延伸閱讀：【Excel Function】條件加總：SUMIF、SUMIFS

接下來會使用：澳洲各州確診人數總和，為大家簡單示範總和的使用。

=SUM(FILTER(a,b))
a：資料範圍
b：篩選條件

首先我們知道 FILTER 會回傳一個矩陣(Array)

=FILTER(資料範圍,篩選條件)

第一個參數資料範圍，要回傳人數，所以選擇「C:C」，它就會回傳篩選條件所對應 C 欄的值。

第二個參數篩選條件，只要回傳澳洲的人數，因此是 B 欄要等於［E2］：「B:B=E2」

可以看到， FILTER 回傳了一個陣列(Array)。

接著只要用 SUM 函數把前面的 FILTER 公式包起來，就可以計算矩陣內數值總和。

=SUM(FILTER(資料範圍,篩選條件))

---

SORT 排序

=SORT(a,b,c,[d])
a：資料範圍
b：排序欄位
c：排序規則（1 預設遞增；-1 遞減）
d：資料方向（FALSE 預設列表方向向下；TRUE 列表方向向右）

這邊我們以國文成績排序，由成績高的至成績低的，來進行範例。

=SORT(資料範圍,排序欄位,排序規則)

第一個參數資料範圍，資料表範圍從第三列小莫開始到最後一列小序的英文成績：「A3:D12」

第二個參數排序欄位，第一欄為名字，第二欄為國文成績，這邊為第二欄：「2」

第三個參數排序規則，排序由高分至低分，高至低為遞減，這邊填入：「-1」

第四個參數資料方向，因為每筆資料皆為一列一列往下排，與預設相同，所以可直接不填。

這樣公式就會回傳成績列表，從國文分數高的至分數低的排列，如上圖所示。

---

SORTBY 多欄位排序

=SORTBY(a,b,c,[d,e])
a：資料範圍
b：排序資料₁
c：排序規則₁（1 預設遞增；-1 遞減）
d：排序資料₂
e：排序規則₂（1 預設遞增；-1 遞減）

SORTBY 和 SORT 用法很相近，不一樣的地方有三點：

• SORTBY 可以多資料排序（不一定只能用2組）
• SORTBY 能直接選取排序資料，不用第幾欄會第幾列（index值）
• SORTBY 以排序資料為資料方向，不須使用資料方向參數

接下來，我們來練習以兩組排序，英文和數學成績排序，先以英文成績高至低排序，若英文同分則以數學成績低到高排序。

=SORTBY(資料範圍,排序資料1,排序規則1,排序資料2,排序規則2)

第一個參數資料範圍，資料表範圍從第三列小莫開始到最後一列小序的英文成績：「A3:D12」

第二個參數排序資料₁，先以英文排序，所以選擇英文的儲存格：「D3:D12」

第三個參數排序規則₁，由英文高分至低分排序，高至低為遞減，這邊填入：「-1」

第四個參數排序資料₂，第二組為序為數學，所以選擇數學的儲存格：「C3:C12」

第五個參數排序規則₂，數學排序由低分至低高，高至低為遞增，這邊填入：「1」

這樣就完成先以英文成績高至低排序，再以數學成績低到高排序。可以看到［I8］到［I11］，英文成績皆為60分，因此再以數學成績排序：上至下為低到高。

---

FILTER + SORT

這邊大家不妨可以練習看看，資料來源為分頁「20220506_COVID19」，先篩選符合條件的資料出來，再把資料做排序：全球新冠確診資料，死亡率(Death Rate)大於 1% 的國家，以確診率(Confirmed Rate)低到高作為排列。

=SORT(FILTER(a,b),c,d)
a：資料範圍
b：篩選條件
c：排序欄位
d：排序規則（1 預設遞增；-1 遞減）

=SORT(FILTER('20220506_COVID19'!A2:F199,'20220506_COVID19'!F2:F199>0.01),5,1)

這裡我們直接提供函數寫法，當然大家有甚麼問題都可以直接於下方的留言區提問，也歡迎私訊粉專詢問喔！

---

COVID-19 資料來源：JHU CSSE

Excel 範例檔案下載：Excel-FILTER-SORT-SORTBY.xlsx

---

更多 Excel 文章請至 文章導覽 尋找，也可利用上方「搜尋(Search...)」功能

有任何莫非工具的問題，歡迎至「表單」提問

---

Excel 是不是很容易
和墨菲一起用 Excel 把工作變簡單