Excel-2021版新增函數的使用

學校同仁大都還沒轉換至 Excel 2021,本篇列出 Excel 2021 新增的函數,先來初探其有何妙用之處。

本篇你將學到函數介紹:XLOOKUP、SORT、SORTBY 、FILTER 、UNIQUE 、SEQUENCE 、RANDARRAY 、XMATCH 、LET等函數

1. XLOOKUP 函數

你可能熟悉 VLOOKUP 函數的查詢功能,其主要是由第一欄中查詢符合的位置,再對照傳回指定欄位對應的儲存格內容,所以查詢內容必須位於第一欄。這個函數很好用,但也是帶來某種不方便。

Excel-2021版新增函數的使用

(1) 由第1欄查第2欄

儲存格G4:=XLOOKUP(F4,A4:A23,B4:B23)

利用 XLOOKUP 函數,查詢儲存格F4在A欄中的位置,再傳回B欄中對應的儲存格內容。

(2) 由第2欄查第4欄

儲存格G7:=XLOOKUP(F7,B4:B23,D4:D23)

利用 XLOOKUP 函數,查詢儲存格F7在B欄中的位置,再傳回D欄中對應的儲存格內容。

由此可知,XLOOKUP 函數將會是你常用的函數,因為更方便使用了。

 

2. SORT 函數

這也是用來查詢的函數,它帶來的方便性,完全讓你將公式簡化到極至。

這個函數是動態陣列公式,會傳回變數大小的陣列。

Excel-2021版新增函數的使用

(1) 將姓名欄位依筆劃遞增

儲存格F4:=SORT(B4:B23)

操作時,只要在儲存格F4中輸入 SORT 函數,按下 Enter 鍵時,系統會自動產生整欄的排序結果。(下圖中的藍色框線內容會自動產生)

這是 Excel 2021 的「溢出陣列」功能,所以只有陣列左上角(第一個儲存格)可以編輯。

Excel-2021版新增函數的使用

(2) 依姓名欄位查詢座號

儲存格E4:=XLOOKUP(F4,$B$4:$B$23,$A$4:$A$23)

(3) 依姓名欄位查詢成績

儲存格G4:=XLOOKUP(F4,$B$4:$B$23,$C$4:$C$23)

 

3. SORTBY 函數

這個函數太強大、太方便了,因為只要在第一個儲存格輸入公式,整個資料表都會排序。

Excel-2021版新增函數的使用

儲存格E4:=SORTBY(A4:C23,C4:C23,-1)

操作時,只要在儲存格E4中輸入 SORTBY 函數,按下 Enter 鍵時,系統會自動產生整個資料表的排序結果。(下圖中的藍色框線內容會自動產生)

Excel-2021版新增函數的使用

SORTBY(A4:C23,C4:C23,-1) 函數:

(1) 指定要排序的資料表儲存格A4:C23。

(2) 指定要排序的欄位儲存格C4:C23(成績欄)。

(3) 指定參數:1是遞增,-1是遞減。

利用這個函數,可以免去資料內容變動時,每次都要手動重新排序。

 

4. FILTER 函數

FILTER 函數是將進階篩選功能公式化,不用再因為資料來源變動而要重新手動操作。

Excel-2021版新增函數的使用

使用時要先準備篩選的條件,本例為儲存格G3,內容是指定的「地區」。

而且要先輸入好要篩選出來的欄位,本例將原始資料的四個欄位都篩選。

再輸入公式:

儲存格F6:=FILTER(A4:D23,C4:C23=G3)

在 FILTER 函數,指定:

(1) 資料來源為儲存格A4:D23

(2) 篩選欄位為儲存格C4:C23

(3) 篩選條件為「=G3」

因為函數只存在於一個儲存格,且會將結果溢出至鄰近儲存格,所以都不需要使用絶對參照。

 

5. SEQUENCE 函數

以往要產生數列,可以使用 ROW 函數和 COLUMN 函數,因其會傳回列號和欄數當成數列。

Excel-2021版新增函數的使用

(1) 使用 COLUMN 函數

儲存格B3:=COLUMN(A:A)

複製儲存格B3,貼至儲存格B3:G3。

因為 COLUMN(A:A) 會傳回1,公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。

(2) 使用 SEQUENCE 函數

儲存格B4:=SEQUENCE(1,6)

Excel-2021版新增函數的使用

只要在儲存格B4中輸入公式,因為指定參數1和6,表示會產生由1至6的數。

所以公式會自動溢出成儲存格B4:G4。

(3) 應用 COLUMN 函數產生月份名稱

儲存格B5:=TEXT(DATE(2022,COLUMN(A:A),1),"m月")

複製儲存格B5,貼至儲存格B5:G5。

(4) 應用 SEQUENCE 函數產生月份名稱

儲存格B6:=TEXT(DATE(2022,SEQUENCE(1,6),1),"m月")

公式輸入完成,按下 Enter 鍵,公式自動外溢至儲存格B6:G6。

 

6. UNIQUE 函數

這個函數也是使用者福音,因為其能依據資料清單以公式產生不重覆內容。

Excel-2021版新增函數的使用

儲存格E4:=UNIQUE(B4:B18)

當你在儲存格E4中輸入公式,按下 Enter 鍵,就能自動向下列出不重覆的儲存格內容。

Excel-2021版新增函數的使用

當你嘗試要刪除某一個儲存格時,會收到警告訊息。

Excel-2021版新增函數的使用

 

7. RANDARRAY 函數

以往,你是在一個儲存格 RAND 或是 RANDBETWEEN 函數來產生亂數,現在你可以使用陣列方式來產生亂數。

Excel-2021版新增函數的使用

儲存格E4:=RANDARRAY(8,7,20,70,TRUE)

公式的參數指定8列7欄的陣列,內容是介於20和70之間的亂數。

利用參數 TRUE,用以指定傳的亂數是整數,否則會傳回含有小數的數值。

因為公式是置於儲存格A3,所以檢視其陣列中的儲存格,會看到公式是以灰色顯示。

Excel-2021版新增函數的使用

 

8. XMATCH 函數

這個函數是 MATCH 函數的進化版,它可以指定在清單中搜尋的方向。

Excel-2021版新增函數的使用

(1) 由上往下搜尋並傳回第一個完全相符者的位置

儲存格E4:=XMATCH(E3,B4:B13,0,1)

參數1指定由上往下搜尋。

(2) 由下往上搜尋並傳回第一個完全相符者的位置

儲存格E7:=XMATCH(E6,B4:B13,0,-1)

參數-1指定由下往上搜尋。

(3) 由上往下搜尋並傳回第一個部分相符者(模糊搜尋)的位置

儲存格E10:=XMATCH(E9,B4:B13,2,1)

在儲存格E9中使用萬用字元:甲?,其中的「?」代表單一字元。

(4) 由下往上搜尋並傳回第一個部分相符者(模糊搜尋)的位置

儲存格E13:=XMATCH(E12,B4:B13,2,-1)

在儲存格E9中使用萬用字元:*忠,其中的「*」代表多個字元。

 

9. LET 函數

利用 LET 函數來定義變,再由變數組成的公式傳回運算結果,其讓公式更具可讀性。

image

(1) 定義半徑,計算圓面積。

儲存格A6:=LET(x,A4,x^2*PI())

定義x為半徑,x^2*PI()可以計算圓面積。

(2) 定義長度和寬度,計算矩形面積。

儲存格B8:=LET(x,B4,y,B6,x*y)

定義x為長度,y為寬度,x*y用以計算矩形面積。

(3) 定義奇數項的和、偶數項的和,再計算其差。

儲存格D8:=LET(x,SUM(C3,C5,C7),y,SUM(C4,C6,C8),y-x)

定義x為奇數項的和,y為偶數項的和,y-x用以計算兩者的差。

 

【參考資料】

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

【延伸閱讀】

  Excel-利用SORT和SORTBY函數進行排序

  Excel-FILTER函數與進階篩選

  Excel-篩選資料並轉置資料

  Excel-依指定欄位自動排序成績表

  Excel-利用XLOOKUP函數以欄位名稱和萬用字元查詢

  Excel-列出不重覆的姓氏並依筆劃由小至大排列

  Excel-利用FILTER函數模糊篩選

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

  Excel-單一欄位篩選與跨欄位篩選

  Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

  Excel-多欄轉換為單欄

  Excel-計算分組最大值

  Excel-由資料清單中篩選一組

  Excel-根據單條件和雙條件篩選資料(FILTER)

  Excel-下拉式清單選取月份列出該月日期

  Excel-FILTER和OFFSET的動態陣列

  Excel-從日期清單中區別平日和假日計算總和

  Excel-資料篩選的好幫手-FILTER 函數(2021版以上)

  Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)

  Excel-資料篩選的好幫手-UNIQUE函數(2021版以上)

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

    文章標籤

    Excel

    全站熱搜

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