Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

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

(1) 篩選:FILTER

(2) 排序:SORT

(3) 查詢:XLOOKUP

(4) 取唯一值:UNIQUE

(5) 計算個數:COUNTA

(6) 計算乘積和:SUMPRODUCT

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

【設計與解析】

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

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

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

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

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

儲存格F4:

=COUNTA(清單)

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

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

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

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 函數予以加總。

【參考資料】

image UNIQUE 函數參考微軟提供的說明網頁:
image FILTER 函數參考微軟提供的說明網頁:
image XLOOKUP 函數參考微軟提供的說明網頁:
image SORT 函數參考微軟提供的說明網頁:
image SORTBY 函數參考微軟提供的說明網頁:
image XMATCH 函數參考微軟提供的說明網頁:
 

學不完.教不停.用不盡文章列表

    vincent 發表在 痞客邦 留言(1) 人氣()