學不完．教不停．用不盡

本篇要以 Excel 2021 的新增函數進行篩選、查詢、排序之綜合練習。會用到的函數：

(1) 篩選：FILTER

(2) 排序：SORT

(3) 查詢：XLOOKUP

(4) 取唯一值：UNIQUE

(5) 計算個數：COUNTA

(6) 計算乘積和：SUMPRODUCT

【設計與解析】

首先，選取儲存格A4:C100，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：序號、姓名、居住地。

接著，定義名稱：清單。並指定參照：清單=FILTER(姓名,LEFT(姓名,1)=E4)。

以 LEFT 函數取出姓名的第1個字，再以 FILTER 函數指定篩選符合條件的姓名。

1. 計算篩選出姓林者的數量

儲存格F4：

=COUNTA(清單)

=COUNTA(FILTER(姓名,LEFT(姓名,1)=$E$4))

以FILTER 函數傳回一個陣列，再以 COUNTA 函數針對該陣列計算個數。

2. 將篩選出姓林者陣列依筆劃排序

儲存格E7：

=SORT(清單)

=SORT(FILTER(姓名,LEFT(姓名,1)=$E$4))

以FILTER 函數傳回一個陣列，再以 SORT 函數針對該陣列排序(在此由小至大排序)。

3. 在將篩選出姓林者陣列指定儲存格H4的指定第N個內容

儲存格G7：

=INDEX(SORT(清單),H4)

=INDEX(SORT(FILTER(姓名,LEFT(姓名,1)=E4)),H4)

將定義好的名稱：清單，以 INDEX 函數傳回儲格H4指定的第N個內容。

4. 查詢儲存格G7指定姓名對應的居住地、序號

儲存格H7：=XLOOKUP(G7,姓名,居住地)

在 XLOOKUP 函數中依儲存格G7的內容在姓名陣列中的位置傳回對應的居住地。

儲存格I7：=XLOOKUP(G7,姓名,序號)

在 XLOOKUP 函數中依儲存格G7的內容在姓名陣列中的位置傳回對應的序號。

5. 取出居住地內容的唯一值

儲存格G10：=UNIQUE(居住地)

以 UNIQUE 函數取出居住地欄位內容的唯一值

6. 計算每個居住地的人數

儲存格H10：=SUMPRODUCT((居住地=G10)*1)

符合「居住地=G10」條件者，傳回 TRUE／FALSE，再「*1」，轉換為 1／0。

再透過 SUMRODUCT 函數予以加總。

【參考資料】

 UNIQUE 函數參考微軟提供的說明網頁：UNIQUE 函數

 FILTER 函數參考微軟提供的說明網頁：FILTER 函數

 XLOOKUP 函數參考微軟提供的說明網頁：XLOOKUP 函數

 SORT 函數參考微軟提供的說明網頁：SORT 函數

 SORTBY 函數參考微軟提供的說明網頁：SORTBY 函數

 XMATCH 函數參考微軟提供的說明網頁：MATCH 函數