Excel公式懶人包：33 個報表常用函數，附語法範例與常見錯誤

把 Excel 的每個儲存格想成一個抽屜，手打進去的數字是放在抽屜裡的東西，而 Excel公式就是貼在抽屜外面的那張取用單，寫著去哪幾格拿數字、用什麼方式算、結果擺在哪裡。數字一改，取用單照舊，結果自己跟著更新，報表才能做一次就反覆使用。

只是 Excel函數的種類相當多，報表上天天用到的其實集中在加總、計數、查找、判斷、文字與日期這幾類，也有幾個地方特別容易混淆。SUMIF 與 SUMIFS 的引數順序剛好相反，XLOOKUP 在 Excel 2016 與 2019 又不能用，公式往下複製後參照跑掉，更是常見的狀況。每個函數都附上語法、對著同一張示範表算出的結果，以及最常見的誤用，複製後換成自己的欄位就能用。

Excel公式是什麼？

Excel公式是以等號（=）開頭、寫在儲存格裡的一條算式。Excel 看到開頭的等號，才會把後面的內容當成要計算的東西；沒有等號，輸入的就只是一般文字。至於 Excel函數，則是 Excel 預先寫好的公式零件，使用時只要依序填入引數。換句話說，函數不是和公式並列的另一種東西，而是公式裡最常被呼叫的一種積木。

Microsoft 的說明文件指出，公式可以包含四種元素：函數、參照、運算子與常數。它舉的範例是 =PI()*A2^2，其中 PI() 是函數，會傳回圓周率 3.142…；A2 是參照，會傳回儲存格 A2 的值；2 是常數，也就是不經計算、保持不變的值；^ 是乘冪運算子，* 則是乘法運算子。

常數有個容易忽略的特性。把數字直接寫進公式，例如 =30+70+110，結果只有在修改公式本身時才會變。比較好的做法是把這些數字各自放進獨立的儲存格，再讓公式去參照它們，之後只要改儲存格裡的數字，所有用到它的公式就會一起更新。

後續的範例都沿用同一張銷售紀錄。它放在 A1:E7，A 欄是客戶、B 欄是品項、C 欄是金額、D 欄是成交日、E 欄是訂單編號，第 1 列是欄位名稱，資料在第 2 到 7 列：

另外有一張價目表放在 G1:H4，G 欄是品項、H 欄是單價，查找類函數會用到：

表裡的客戶名稱與金額都是示範用的假設資料，範例寫出的結果都以這兩張表為準。

輸入公式的基本規則，引數、引號與範圍的寫法

讀懂函數的語法列，是看懂所有公式的前提。以 SUM(number1,[number2],...) 為例，括號裡的引數用逗號分隔，用方括號包住的是選填，沒有方括號的就是必填。左括號和右括號一定要成對，像 =IF(40>50,SUM(G2:G5),0) 有兩對括號，少一個就會出錯。

文字與數字的寫法不同。公式裡出現的文字要用雙引號括住，例如 "名片"，數字則不用；邏輯值 TRUE 與 FALSE 看起來像文字，卻同樣不加引號。乘號要用 *，不能用英文字母 X。

範圍的寫法是用冒號連接起點與終點，A2:A7 代表欄 A 的第 2 列到第 7 列。Excel 預設的 A1 表示法以字母指欄、以數字指列，欄從 A 到 XFD，共 16,384 欄，列從 1 到 1,048,576；B2 就是欄 B 與列 2 相交的那一格。要參照同一個活頁簿裡其他工作表的資料，寫法是在範圍前面加上工作表名稱與驚嘆號，例如 =AVERAGE(行銷!B1:B10)。工作表名稱含有空格或數字時，前後要加單引號，例如 ='1 月營收'!A1。

相對、絕對與混合參照決定公式複製後的位置

報表公式往下拉就壞掉，多半是參照的型別沒選對。參照有三種寫法，差別在複製或填滿時會不會跟著位置變動。相對參照像 A1，會跟著位置調整，新輸入的公式預設都是相對參照，所以 B2 的 =A1 填到 B3，會自動變成 =A2。絕對參照像 $A$1，複製或填滿都不變，所以 B2 的 =$A$1 填到 B3，仍然是 =$A$1。

混合參照則只鎖一半。$A1 鎖住欄、列可以變，A$1 鎖住列、欄可以變。舉例來說，A2 的 =A$1 複製到 B3，會變成 =B$1，欄位跟著往右移了一格，列號 1 則被鎖住。這三種寫法不必手打 $，編輯公式時選取其中的參照再按 F4，就會循環切換。從 A1 開始，第一次按變成 $A$1，第二次變成 A$1，第三次變成 $A1，再按一次就回到 A1。

鎖定的用處，在算金額時最明顯。假設另一張工作表的 F1 放著固定的單價，B 欄是數量，要在 C 欄算出金額。C2 寫成 =B2*$F$1，往下填滿後 C3 會變成 =B3*$F$1，數量跟著列往下走，單價則始終指向 F1。如果單價沒有鎖定而寫成 F1，填到 C3 就會變成 F2，指向一格空白儲存格，金額會算成 0。後面 VLOOKUP 要把查詢表的範圍鎖起來，道理也一樣。

公式常見錯誤值代表的意思

儲存格出現 # 開頭的錯誤值時，通常代表公式出了某一類問題，認得名稱就能少走冤枉路。報表裡最常遇到的有六種：

錯誤值只指出大致的方向，真正排錯時還是要回頭看公式本身。另外有兩個多半和動態陣列有關的錯誤，#SPILL! 是結果要展開的範圍被其他內容擋住，#CALC! 常見於 FILTER 篩不出任何資料又沒有設定替代內容。

新版 Excel 才有的函數與支援版本

同一個檔案在不同版本的 Excel 裡，能用的函數並不一樣。公式明明照著教學輸入卻算不出來，原因不一定是打錯，也可能是這個函數在自己的版本裡還沒有。下表依 Microsoft「Excel函數（依類別）」頁面的版本標示，整理報表常用的幾個新函數：

XLOOKUP 的說明頁明確寫著不適用於 Excel 2016 和 Excel 2019；用這兩個版本的人，仍然可能收到別人用較新版本建立、公式裡含有 XLOOKUP 的活頁簿。IFS、CONCAT 與 TEXTJOIN 的說明頁寫的則是需要 Office 2019 或 Microsoft 365 訂閱。

SUM、IF、VLOOKUP、INDEX、MATCH、SUMIF、COUNTIF 這些長年存在的函數，則不受版本限制。SUMIFS、COUNTIFS 與 AVERAGEIFS 雖然在分類頁上也帶有版本標示，各自的說明頁卻都把 Excel 2016 列為適用版本，一般常見的版本都能使用。另外，Microsoft 也說明部分舊函數已被新函數取代，只是為了相容舊檔案而保留下來，所以 Excel函數裡常見新舊兩種寫法並存，例如 CONCATENATE 與 CONCAT。

報表最先用到的加總、計數與平均類函數

報表上大部分的格子，做的都是加總、計數或算平均。這類函數每個都不難，容易出錯的是引數順序與準則寫法這些小地方。

SUM 加總數值並自動略過文字

SUM 是最基本的加總，語法是 SUM(number1,[number2],...)，最多可以放 255 個數字，個別的值、儲存格參照與範圍都可以混用。對示範表的金額欄，=SUM(C2:C7) 得 35,900。

不直接寫成 =C2+C3+C4 的原因在於，用加號相加時，只要其中一格是文字就會得到 #VALUE!，刪掉其中一列又會得到 #REF!；SUM 則會略過文字、只加總數值，範圍內插入或刪除列時也會自動更新。

分成多段範圍時，=SUM(A1:A3,B1:B3) 比 =SUM(A1,A2,A3,B1,B2,B3) 穩，因為後者在範圍中間插入或刪除列時比較容易出錯。如果只想加總看得見的儲存格，例如篩選過後的結果，要改用 SUBTOTAL。

SUMIF 與 SUMIFS 的準則寫法與引數順序

SUMIF 是按條件加總，語法是 SUMIF(range,criteria,[sum_range])，意思是在 range 裡找出符合 criteria 的格子，再加總 sum_range 對應的數字。要算示範表裡所有名片的金額，寫成 =SUMIF(B2:B7,"名片",C2:C7)，得 5,400。

準則的寫法有幾個規則。準則含有比較符號時，整個準則要用雙引號括住，例如 ">5"，單純的數字準則則不用；準則裡可以用萬用字元，? 代表單一字元，* 代表任意字元。文字準則一定要加引號，沒加引號常常會得到 0。另外，SUMIF 比對超過 255 個字元的字串時，結果會不正確，這是少數會踩到的限制。

條件不只一個時，改用 SUMIFS，語法是 SUMIFS(sum_range,criteria_range1,criteria1,...)，最多可以放 127 組條件。最容易搞混的地方是，SUMIFS 的加總範圍放在第一個引數，剛好與 SUMIF 相反。要算龐果設計的型錄訂單金額，寫成 =SUMIFS(C2:C7,A2:A7,"龐果設計",B2:B7,"型錄")，得 12,000。

COUNT 只算數字，COUNTA 連文字也算

COUNT 與 COUNTA 都是算筆數，差別在算不算文字。COUNT(value1,[value2],...) 只計算含數字的儲存格，數字和日期都算，範圍內的文字、邏輯值、空白與錯誤值都不算。對金額欄，=COUNT(C2:C7) 得 6。

COUNTA(value1,[value2],...) 計算所有不空白的儲存格，不論裡面是什麼資料，連錯誤值與公式傳回的空字串 "" 也會算進去。對客戶欄，=COUNTA(A2:A7) 得 6。

兩者的分工很好記，要算有幾筆文字資料，用 COUNTA，要算有幾筆金額，用 COUNT。拿 COUNT 去算客戶欄，=COUNT(A2:A7) 會得 0，因為那一欄全是文字。

COUNTIF 與 COUNTIFS 依條件計算筆數

COUNTIF 只能放一個條件，語法是 COUNTIF(range,criteria)。要算品項是名片的訂單有幾筆，寫成 =COUNTIF(B2:B7,"名片")，得 2。

條件有兩個以上，就用 COUNTIFS，語法是 COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...)，最多 127 組，所有條件都符合才算 1 筆。各組範圍的欄數與列數要相同，但不必相鄰。要算龐果設計且金額大於 5000 的訂單，寫成 =COUNTIFS(A2:A7,"龐果設計",C2:C7,">5000")，得 2。

準則的引號、比較符號與萬用字元，寫法都和 SUMIF 相同。COUNTIF 同樣有字串長度的限制，比對長度超過 255 個字元的字串時，會傳回不正確的結果。

AVERAGE 與 AVERAGEIF 算出整體與條件平均

AVERAGE 傳回引數的算術平均值，語法是 AVERAGE(number1,[number2],...)。對金額欄，=AVERAGE(C2:C7) 得約 5,983.33。範圍或參照裡的文字、邏輯值與空白儲存格會被忽略，但值為 0 的儲存格會算進去，這點會直接影響平均值。

要算符合條件的平均，用 AVERAGEIF(range,criteria,[average_range])。龐果協會三筆訂單的平均金額，寫成 =AVERAGEIF(A2:A7,"龐果協會",C2:C7)，得約 5,233.33。如果範圍內沒有任何儲存格符合準則，結果會是 #DIV/0!。

條件有兩個以上時，可以改用 AVERAGEIFS，寫法與 SUMIFS 類似。

查找類函數依關鍵欄位取回對應資料

報表最頭痛的，是把兩張表對起來，例如訂單表要去價目表抓單價。同一個查詢有三種寫法，都是用 B2 的品項「名片」，到價目表 G2:H4 找出對應的單價，結果也都是 600。它們各有適用的條件與限制，重點是看自己的 Excel 版本與資料排列，選能用的那一種。

VLOOKUP 只能由左往右查找

VLOOKUP 是最廣為人知、各版都能用的查找函數，語法是 VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])。範例寫成 =VLOOKUP(B2,$G$2:$H$4,2,FALSE)，得 600。查詢表的範圍用了絕對參照，向下填滿時才不會位移，這就是前面講過的參照規則。

使用上有幾個條件要注意。查詢值必須位在範圍的第一欄，所以 VLOOKUP 只能由左往右找。最後一個引數省略時，預設是 TRUE，也就是近似符合，而且第一欄必須先排序；要精確比對，一律寫成 FALSE。

出錯的情況也有固定的幾種。欄號超出查詢範圍的欄數，會得到 #REF!；查不到資料會得到 #N/A。比對失敗的原因，常常是數字或日期被存成文字，或是資料前後多了空格；空格與無法列印的字元，可以用 TRIM 或 CLEAN 先清理。

XLOOKUP 能往左查找並指定查不到時的文字

XLOOKUP 是較新的查找函數，語法是 XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])。同一個查詢寫成 =XLOOKUP(B2,G2:G4,H2:H4,"查無品項")，得 600。版本上，它不適用於 Excel 2016 和 2019，Excel 2021 或 Microsoft 365 才有。

和 VLOOKUP 相比，XLOOKUP 改用查詢陣列與傳回陣列取代單一範圍加欄號，所以傳回欄在查詢欄的左邊也可以；預設就是完全符合，不必另外寫 FALSE；if_not_found 可以直接指定找不到時顯示的文字，省略的話，找不到就傳回 #N/A；而且一個公式可以同時傳回多欄資料。

match_mode 用來設定比對方式，例如完全符合、找下一個較小或較大的項目，或是使用萬用字元；search_mode 用來設定搜尋方向與是否使用二進位搜尋。這兩個引數都有預設值，平常的精確查詢不必動它們。

INDEX 與 MATCH 組合的查找寫法

INDEX 與 MATCH 是兩個函數搭配起來的寫法，各版都能用，也不受查詢欄必須在最左邊的限制。INDEX(array,row_num,[column_num]) 依列號與欄號取出交集儲存格的值；MATCH(lookup_value,lookup_array,[match_type]) 傳回項目在範圍裡的位置，而不是值本身。

兩者組合起來，就是 =INDEX(H2:H4,MATCH(B2,G2:G4,0))，得 600。MATCH 先找出 B2 的「名片」在 G2:G4 裡排第幾個，再把這個位置交給 INDEX，去 H2:H4 取同一個位置的值。

MATCH 的第三個引數要寫 0，才是完全符合；省略時預設是 1，會要求查詢範圍遞增排序，沒排序就容易得到錯的結果。MATCH 比對文字時不分大小寫，找不到會得到 #N/A。INDEX 另有一種「參照形式」的用法，一般查表用的是這裡示範的陣列形式。

邏輯類函數讓公式能依條件給不同結果

從「如果成立就怎樣」到「同時滿足幾個條件」，報表裡的分級、標註與容錯，靠的都是這一類函數。IF 是核心，其他幾個是它的延伸。

IF 依條件成立與否傳回兩種結果

IF 在條件成立時傳回一個結果，不成立時傳回另一個，語法是 IF(logical_test,value_if_true,[value_if_false])。範例是 =IF(C2>=5000,"大單","一般")，C2 是 3000，條件不成立，所以得「一般」。「大單」「一般」這類文字結果都要用雙引號括住。

IF 可以一層包一層，處理多重條件。Microsoft 的說明也提醒，不論用 IF 還是 IFS，條件一多就得確保每個條件都依正確順序排列，公式也會變得難以建立、測試與更新。條件只有幾個時，改用 IFS 能省掉層層括號，讀起來清楚得多；分級一多，比較好維護的做法是把分級標準另外整理成一張對照表，再用查找函數去查。

使用 IF 時有兩個常見的狀況。儲存格出現 0，多半是 value_if_true 或 value_if_false 的位置沒有填內容；出現 #NAME?，通常是公式拼錯了。

IFS 依序判斷多個條件

IFS 讓公式依序判斷多個條件，不必再包好幾層 IF。語法是 IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],...)，最多可以測試 127 組條件。版本上，它需要 Office 2019 或 Microsoft 365 訂閱。

範例是 =IFS(C2>=10000,"A 級",C2>=5000,"B 級",TRUE,"C 級")，C2 是 3000，前兩個條件都不成立，所以得「C 級」。最後一組用 TRUE 當條件，是替所有條件都不成立的情況指定預設結果。

出錯的原因有兩種。所有條件都不是 TRUE，而且沒有預設結果時，會得到 #N/A；給了條件卻沒有給對應的結果，Excel 會跳出引數太少的提示。

IFERROR 會一併遮掉所有類型的錯誤

IFERROR 在公式出錯時，改顯示指定的內容，報表交出去之前，常拿來遮掉 #N/A 與 #DIV/0!。語法是 IFERROR(value,value_if_error)。範例是 =IFERROR(C2/D2,"待確認")。假設另有一張表，C 欄是總額、D 欄是數量，D 欄是空白或 0 的時候，公式會顯示「待確認」，而不是 #DIV/0!。

它能處理的錯誤類型有 #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 與 #NULL!。

也正因為它會把所有類型的錯誤一起遮掉，公式本身寫錯的時候，儲存格上也看不到任何異狀。比較穩當的順序，是先確認公式本身沒問題，再用 IFERROR 包起來。如果只想處理 #N/A，可以改用 IFNA，它從 Excel 2013 起提供。

AND 與 OR 把多個條件合成一個真假值

AND 與 OR 把多個條件合成一個真假值，通常包在 IF 裡面當條件。AND(logical1,[logical2],...) 要所有條件都是 TRUE，才傳回 TRUE；OR(logical1,[logical2],...) 只要有一個條件是 TRUE，就傳回 TRUE。兩者最多都可以放 255 個條件。

先看 AND 的範例。示範表第 4 列是海報、5200，寫成 =IF(AND(C4>=5000,B4="海報"),"重點案件","一般")，兩個條件都成立，所以得「重點案件」。OR 的範例是 =IF(OR(B2="名片",B2="型錄"),"印刷品","其他")，B2 是名片，兩個條件有一個成立，所以得「印刷品」。

引數必須是邏輯值，如果指定的範圍裡不含任何邏輯值，就會得到 #VALUE!。另外，AND 與 OR 單獨使用時，只會傳回 TRUE 或 FALSE，要讓結果變成有意義的文字，還是要搭配 IF。

文字類函數處理編號、姓名與格式

報表資料常常是一格塞好幾種資訊，像是訂單編號、姓名，需要拆開、接起來，或統一格式。最常用的是三組 Excel函數，拆字的範例用示範表 E 欄的訂單編號，例如 PG2609-001。

LEFT、RIGHT 與 MID 拆出訂單編號的片段

這三個函數分別從左邊、右邊，或從指定的位置取出幾個字元。LEFT(text,[num_chars]) 與 RIGHT(text,[num_chars]) 的 num_chars 省略時，預設是 1；數字大於字串長度時，會傳回全部的文字。MID(text,start_num,num_chars) 的三個引數都要填，start_num 從 1 起算。

對 E2 的 PG2609-001，=LEFT(E2,6) 得 PG2609，也就是年月代碼；=MID(E2,8,3) 得 001，從第 8 個字元起取 3 個；=RIGHT(E2,3) 同樣得 001。

MID 有兩個邊界要留意。start_num 大於字串長度時，傳回的是空文字；start_num 小於 1，或 num_chars 是負數，則會得到 #VALUE!。

TEXT 把數字與日期轉成指定格式的文字

TEXT 依格式代碼，把數字或日期轉成文字，常用在報表標題與編號補零，語法是 TEXT(value,format_text)，格式代碼要放在雙引號裡。=TEXT(C2,"#,##0") 得 3,000，是加上千分位；=TEXT(0.285,"0.0%") 得 28.5%；=TEXT(1234,"0000000") 得 0001234，是補前置零。

TEXT 的結果是文字，之後要拿去計算會很不方便。Microsoft 的說明建議，原始的數值留在一個儲存格，另外用一個儲存格放 TEXT 的結果，之後其他公式一律參照原始值。

日期的格式代碼不分大小寫，月、日、年分別用 M、D、Y。容易出錯的是 "mm"，單獨使用時代表月份，要跟 "HH" 或 "SS" 一起用，才會變成分鐘。如果只是想改顯示的樣子，不需要公式，按 Ctrl+1 開啟儲存格格式也能做到。

CONCAT 與 TEXTJOIN 把多格文字接在一起

CONCAT 與 TEXTJOIN 把多格文字接在一起，兩個都需要 Excel 2019 或 Microsoft 365。CONCAT(text1,[text2],...) 取代了舊的 CONCATENATE，舊函數為了相容仍然可用；它沒有分隔符號的引數，也不能略過空白，但可以直接放入整個範圍。範例是 =CONCAT(A2,"-",B2)，得「龐果設計-名片」。

TEXTJOIN 多了分隔符號與略過空白的功能，語法是 TEXTJOIN(delimiter,ignore_empty,text1,[text2],...)。ignore_empty 設為 TRUE 時，會略過空白的儲存格。範例是 =TEXTJOIN("/",TRUE,A2:A3)，得「龐果設計/龐果協會」。

兩個函數有同一個上限，接出來的結果如果超過 32,767 個字元（也就是一格儲存格能放的字元數上限），就會得到 #VALUE!。

日期類函數，算期限、年資與工作日

報表常常要算到期日、成交幾年，或是工期有幾個工作天。Excel 把日期存成連續的序列值，預設 1900 年 1 月 1 日是序列值 1，所以日期可以直接拿來加減，這也是日期類函數能運作的基礎。

輸入日期有個要注意的地方。EDATE、EOMONTH 與 NETWORKDAYS 的說明頁都提醒，日期要用 DATE 函數輸入，或是來自其他公式、函數的結果；直接以文字格式輸入日期，可能會出問題。

TODAY 傳回當天日期並隨重新計算更新

TODAY() 沒有引數，傳回目前日期的序列值。如果儲存格在輸入函數前是通用格式，Excel 會自動把格式改成日期。=TODAY()+5 就是五天後的日期，=MONTH(TODAY()) 則取出目前的月份。需要連時間一起顯示，要改用 NOW。

TODAY 每次重新計算就會更新。如果發現日期沒有照預期更新，可以到「檔案、選項、公式」，確認計算選項選的是「自動」。

DATEDIF 計算年資的用法與限制

DATEDIF 計算兩個日期之間的整年、整月或天數，常拿來算年資與帳齡，語法是 DATEDIF(start_date,end_date,unit)。unit 填 "Y" 是整年，"M" 是整月，"D" 是天數，另外還有 "MD"、"YM" 與 "YD"。範例是 =DATEDIF(D2,TODAY(),"Y")，算的是從 D2 的成交日到今天，滿了幾年。

DATEDIF 有幾個限制要知道。Microsoft 的說明指出，這個函數是為了支援 Lotus 1-2-3 的舊活頁簿才保留，特定情況下可能算出錯誤的結果，其中 "MD" 有已知的問題，不建議使用。另外，start_date 如果大於 end_date，就會得到 #NUM!。

如果只想知道相差幾天，不必用 DATEDIF，直接用較晚的日期減去較早的日期就可以。

EDATE、EOMONTH 與 NETWORKDAYS 推算帳期與工作日

這三個函數的語法都很短，分別處理往後推幾個月、月底，以及工作日的計算，對帳期與交期特別實用。

EDATE(start_date,months) 傳回幾個月之前或之後的同一天，正數往後、負數往前，月數不是整數時只取整數部分。=EDATE(D2,1) 得 2026/10/1。EOMONTH(start_date,months) 傳回往前或往後推指定月數後，那個月的最後一天，常用來算月底的結帳日。=EOMONTH(D2,0) 得 2026/9/30。

NETWORKDAYS(start_date,end_date,[holidays]) 傳回兩個日期之間的工作日數，不含週末與指定的假日，起訖兩天都計入。=NETWORKDAYS(D2,D3) 得 6，也就是 2026/9/1 到 9/8 之間的 6 個工作日。要排除假日的話，把假日清單放在另一個範圍，當作第三個引數。如果週末不是週六、週日，可以改用 NETWORKDAYS.INTL 自訂哪幾天是週末。

動態陣列函數，一條公式輸出整張結果

FILTER、UNIQUE 與 SORT 這類函數，一條公式就會傳回多個值，並自動展開到旁邊的儲存格，這個行為叫做溢出，傳統函數做不到。對報表來說，篩選、去重與排序可以一次完成。這類函數都要 Excel 2021 或 Microsoft 365 才有。

溢出的運作方式很單純，公式的結果是陣列時，按下 Enter，Excel 會動態建立大小剛好的範圍。資料如果放在 Excel 表格裡，而且公式用的是結構化參照，來源資料增減時，結果的範圍也會自動調整。

溢出範圍被擋住時出現的 #SPILL!，不只會發生在新函數上。Microsoft 的 VLOOKUP 說明頁舉過一個例子，=VLOOKUP(A:A,A:C,2,FALSE) 用整欄當查詢值，又依賴隱含交集取單一值，就會出現 #SPILL!，解法是用 @ 運算子錨定，寫成 =VLOOKUP(@A:A,A:C,2,FALSE)，或是改用單一儲存格參照。另外，動態陣列對活頁簿之間的支援有限，來源活頁簿關閉後重新整理，連結的動態陣列公式會傳回 #REF!。

FILTER 依條件篩出整列資料

FILTER 依條件篩出符合的整列資料，語法是 FILTER(array,include,[if_empty])。範例是 =FILTER(A2:C7,B2:B7="名片","無資料")，會傳回兩筆名片訂單，也就是示範表的第 2 列與第 5 列。include 的高度或寬度要和 array 相同。

第三個引數 if_empty 最好養成習慣填上。沒有符合條件的資料、又沒有填這個引數時，會得到 #CALC!，因為 Excel 目前不支援空白的陣列。

多條件的寫法是把條件相乘或相加。兩個條件都要成立，用乘號 * 連接，例如 (A2:A7="龐果設計")*(B2:B7="型錄")；只要其中一個成立，用加號 + 連接。

UNIQUE 取出不重複的項目

UNIQUE 從清單或範圍裡取出不重複的值，語法是 UNIQUE(array,[by_col],[exactly_once])。範例是 =UNIQUE(B2:B7)，會傳回名片、型錄、海報三個項目。

exactly_once 設為 TRUE 時，只傳回恰好出現一次的項目。Microsoft 的說明頁舉的例子，就是找出只使用過一次服務的客戶。

在報表裡，UNIQUE 的結果很適合當下拉清單的來源，也可以當彙總表的項目欄，後面再接 SUMIF 之類的函數，算出每個項目的合計。

SORT 與 SORTBY 的排序依據差異

SORT 與 SORTBY 都是排序，SORT 依欄位索引，SORTBY 依另一組範圍。SORT(array,[sort_index],[sort_order],[by_col]) 的 sort_order 填 1 是遞增，也是預設值，填 -1 是遞減。範例是 =SORT(A2:C7,3,-1)，依第 3 欄的金額由大到小排序，金額最高的 12,000 會排在最上面。

SORTBY(array,by_array1,[sort_order1],...) 依對應範圍的值排序，參照的是範圍，而不是欄位索引號，所以新增或刪除欄位時比較不容易出錯。Microsoft 的說明建議，排序格線裡的資料時使用 SORTBY，因為它更有彈性。

SORTBY 的 by_array 必須是單列或單欄，所有引數的大小也要相同。排序順序的引數如果不是 1 或 -1，就會得到 #VALUE!。

報表裡常見的函數搭配寫法

單一函數會用之後，報表真正省時間的，是把兩個函數疊在一起，讓一條公式同時完成查找與容錯、篩選與排序，或是文字與日期的組合。

用 IFERROR 包住查找函數來處理查不到的情況

價目表查不到品項時，與其顯示 #N/A，不如顯示「查無品項」。各版 Excel 都能用的寫法是 =IFERROR(VLOOKUP(B2,$G$2:$H$4,2,FALSE),"查無品項")，B2 的名片查得到，所以得 600；品項不在價目表裡時，就會顯示「查無品項」。

使用新版 Excel 的話，XLOOKUP 的 if_not_found 引數可以達到同樣的效果，寫成 =XLOOKUP(B2,G2:G4,H2:H4,"查無品項")，不必另外包一層函數。兩種寫法二選一，取決於自己的 Excel 版本。

SORT 搭配 FILTER，篩出符合條件的資料再排序

同一條公式先篩選、再排序，是動態陣列最能展現價值的用法。=SORT(FILTER(A2:C7,A2:A7="龐果設計"),3,-1) 先取出龐果設計的三筆訂單，再依第 3 欄的金額由大到小排序，金額依序是 12,000、5,200、3,000。

這組公式的版本門檻和動態陣列函數相同，需要 Excel 2021 或 Microsoft 365。

Microsoft 的說明頁也提到，動態陣列公式只寫在一個儲存格裡，結果再溢出到旁邊的儲存格，所以不需要使用絕對參照。

把 TEXT 與 TODAY 組成帶日期的報表標題

讓報表標題自動帶出當天日期，是 TEXT 很常見的用途。公式寫成 ="龐果設計銷售報表 更新日期 "&TEXT(TODAY(),"yyyy/mm/dd")，用 & 把文字和 TEXT 的結果接起來，顯示的是開啟檔案當天的日期。

要經過 TEXT，是因為日期如果直接用 & 接在文字後面，顯示出來的會是序列值的數字；用 TEXT 指定格式，才會顯示成日期的樣子。前面提過格式代碼不分大小寫，所以 Microsoft 說明頁範例裡的 "MM/DD/YY"，寫成 "yyyy/mm/dd" 同樣有效。

想留存某一天的報表，可以把這格公式複製後貼成值，日期就不會再隨重新計算變動。

Excel公式不必一次背完，真正需要的是遇到一個重複的手動動作時，知道它屬於加總、查找、判斷、文字還是日期這幾類，再回頭挑對應的函數。從報表裡最常手動重做的那一件事開始，用一條 Excel公式取代它，之後每次更新數字，結果都會自己算好。