學校同仁大都還沒轉換至 Excel 2021，本篇列出 Excel 2021 新增的函數，先來初探其有何妙用之處。
本篇你將學到函數介紹：XLOOKUP、SORT、SORTBY 、FILTER 、UNIQUE 、SEQUENCE 、RANDARRAY 、XMATCH 、LET等函數
1. XLOOKUP 函數
你可能熟悉 VLOOKUP 函數的查詢功能，其主要是由第一欄中查詢符合的位置，再對照傳回指定欄位對應的儲存格內容，所以查詢內容必須位於第一欄。這個函數很好用，但也是帶來某種不方便。
(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 函數
這也是用來查詢的函數，它帶來的方便性，完全讓你將公式簡化到極至。
這個函數是動態陣列公式，會傳回變數大小的陣列。
(1) 將姓名欄位依筆劃遞增
儲存格F4：=SORT(B4:B23)
操作時，只要在儲存格F4中輸入 SORT 函數，按下 Enter 鍵時，系統會自動產生整欄的排序結果。(下圖中的藍色框線內容會自動產生)
這是 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 函數
這個函數太強大、太方便了，因為只要在第一個儲存格輸入公式，整個資料表都會排序。
儲存格E4：=SORTBY(A4:C23,C4:C23,-1)
操作時，只要在儲存格E4中輸入 SORTBY 函數，按下 Enter 鍵時，系統會自動產生整個資料表的排序結果。(下圖中的藍色框線內容會自動產生)
SORTBY(A4:C23,C4:C23,-1) 函數：
(1) 指定要排序的資料表儲存格A4:C23。
(2) 指定要排序的欄位儲存格C4:C23(成績欄)。
(3) 指定參數：1是遞增，-1是遞減。
利用這個函數，可以免去資料內容變動時，每次都要手動重新排序。
4. FILTER 函數
FILTER 函數是將進階篩選功能公式化，不用再因為資料來源變動而要重新手動操作。
使用時要先準備篩選的條件，本例為儲存格G3，內容是指定的「地區」。
而且要先輸入好要篩選出來的欄位，本例將原始資料的四個欄位都篩選。
再輸入公式：
儲存格F6：=FILTER(A4:D23,C4:C23=G3)
在 FILTER 函數，指定：
(1) 資料來源為儲存格A4:D23
(2) 篩選欄位為儲存格C4:C23
(3) 篩選條件為「=G3」
因為函數只存在於一個儲存格，且會將結果溢出至鄰近儲存格，所以都不需要使用絶對參照。
5. SEQUENCE 函數
以往要產生數列，可以使用 ROW 函數和 COLUMN 函數，因其會傳回列號和欄數當成數列。
(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)
只要在儲存格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 函數
這個函數也是使用者福音，因為其能依據資料清單以公式產生不重覆內容。
儲存格E4：=UNIQUE(B4:B18)
當你在儲存格E4中輸入公式，按下 Enter 鍵，就能自動向下列出不重覆的儲存格內容。
當你嘗試要刪除某一個儲存格時，會收到警告訊息。
7. RANDARRAY 函數
以往，你是在一個儲存格 RAND 或是 RANDBETWEEN 函數來產生亂數，現在你可以使用陣列方式來產生亂數。
儲存格E4：=RANDARRAY(8,7,20,70,TRUE)
公式的參數指定8列7欄的陣列，內容是介於20和70之間的亂數。
利用參數 TRUE，用以指定傳的亂數是整數，否則會傳回含有小數的數值。
因為公式是置於儲存格A3，所以檢視其陣列中的儲存格，會看到公式是以灰色顯示。
8. XMATCH 函數
這個函數是 MATCH 函數的進化版，它可以指定在清單中搜尋的方向。
(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 函數來定義變，再由變數組成的公式傳回運算結果，其讓公式更具可讀性。
(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用以計算兩者的差。
【參考資料】
【延伸閱讀】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
Excel-資料篩選的好幫手-FILTER 函數(2021版以上)
Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)
Excel-資料篩選的好幫手-UNIQUE函數(2021版以上)
留言列表