Skip to content

Productivity › 試算表 › 進階函數

Excel 函數 (Excel Functions) ​

依用途整理常用函數:查找、條件聚合、邏輯、文字、日期、動態參照、資料庫函數。其他筆記提到函數時都連到這裡。

選哪個函數 ​

想做的事首選替代 / 舊版
依 key 帶回另一欄的值XLOOKUPINDEX + MATCH、VLOOKUP
交叉表 (2D) 查值INDEX + 兩個 MATCHXLOOKUP 巢狀
多個 AND 條件的加總 / 計數 / 平均SUMIFS / COUNTIFS / AVERAGEIFS—
條件像一張規則表(含 OR)DSUM / DCOUNT / DAVERAGE多個 COUNTIFS 相加
多層分類IFS、SWITCH巢狀 IF;規則多時改用查找表
依使用者選擇移動範圍OFFSET + MATCHINDEX 回傳範圍(非易變)
篩出符合條件的多列FILTER、UNIQUE、SORT(動態陣列,Microsoft 365)進階篩選

查找 (Lookup) ​

VLOOKUP 假設查找欄在最左、只能往右取值、用欄號定位,插入欄位就會取錯值。新版 Excel 優先用 XLOOKUP。

函數語法重點
XLOOKUPXLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])查找欄與回傳欄分開指定,可往左查;內建找不到時的預設值;預設精確比對
VLOOKUPVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])第 4 參數省略 = 近似比對,幾乎都要寫 FALSE
HLOOKUPHLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])橫向版的 VLOOKUP
MATCHMATCH(lookup_value, lookup_array, 0)回傳位置;0 = 精確比對
INDEXINDEX(array, row_num, [column_num])依位置取值
excel
=XLOOKUP(A2, Products[ID], Products[Name], "not found")
=VLOOKUP(A2, $A$1:$D$10, 2, FALSE)
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))
=INDEX(matrix, MATCH(row_key, row_labels, 0), MATCH(col_key, col_labels, 0))

找不到值回傳 #N/A 時,先檢查型別(數字 vs 文字的 "123")與前後空白(TRIM),而不是急著包 IFERROR;IFERROR 會把真正的錯誤也一起藏起來。

條件聚合 ​

公式意義
=COUNTIFS(B:B, "Yes", E:E, "Florida")B 欄為 Yes 且 E 欄為 Florida 的列數
=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Date], ">="&DATE(2024,1,1))North 區 2024 年起的金額加總;比較運算子要用字串串接
=AVERAGEIFS(Scores[Score], Scores[Class], G2)條件取自儲存格

*IFS 的條件之間是 AND;要 OR 就得把多個 COUNTIFS 相加,條件一多就該考慮下面的資料庫函數或 Power Query。

資料庫函數 (Database Functions) ​

DSUM、DCOUNT、DAVERAGE、DMIN、DMAX 共用 =DFUNCTION(database, field, criteria):

參數意義
database含標題列的資料範圍
field要彙總的欄(欄名、欄序號或標題儲存格)
criteria另一塊條件表:標題與資料表欄名相同;同一列 = AND,不同列 = OR;可用 >、<> 與萬用字元 *、?

例:=DSUM(A1:F6, "Sales", H1:J2) 的條件表是標題列 + 一列條件(一組 AND);範圍改成 H1:J3 就多一組 OR 條件。

情境選擇
條件簡單的日常報表SUMIFS 系列
條件像一張可讓人維護的規則表資料庫函數
邏輯複雜、資料量大Power Query、SQL、Python

邏輯與分類 ​

函數語法用途
IFIF(test, value_if_true, value_if_false)二元判斷、建立旗標 (flag)
AND / OR / NOTAND(cond1, cond2, ...)組合條件,常放在 IF 裡
IFSIFS(test1, result1, test2, result2, ..., TRUE, fallback)回傳第一個成立的結果;沒有內建 else,全部不成立時回傳 #N/A,所以最後補 TRUE, fallback
SWITCHSWITCH(expr, value1, result1, value2, result2, default)等值對應:代碼 → 標籤、狀態轉換
IFERRORIFERROR(value, value_if_error)錯誤時的替代值(小心掩蓋問題)
excel
=IF(AND(C2>=1000, D2="VIP"), "high", "normal")
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")
=SWITCH(E2, "TW", "Taiwan", "JP", "Japan", "Other")

巢狀 IF 超過三層就難以維護;分類規則多時,做一張「門檻 → 標籤」的查找表,再用 XLOOKUP(..., match_mode = -1)(找小於等於的最大值)取代。

文字 ​

函數範例結果 / 用途
LEFT / RIGHT / MID=LEFT("DataCamp", 4)Data;RIGHT → Camp
LEN=LEN(A2)字元數:檢查 ID、電話長度是否一致
TRIM / CLEAN=TRIM(CLEAN(A2))去前後與多餘空白 / 不可列印字元
UPPER / LOWER / PROPER=PROPER("jOHN")John
SUBSTITUTE=SUBSTITUTE(A2, "-", "")取代字串
CONCAT / &=A2 & "-" & B2串接;CONCAT 不會自動加分隔符
TEXTJOIN=TEXTJOIN(", ", TRUE, A2:C2)自動插分隔符並可略過空白
TEXT=TEXT(A2, "yyyy-mm-dd")數字 / 日期轉成指定格式的文字
TEXTSPLIT、TEXTBEFORE、TEXTAFTER=TEXTBEFORE(A2, "@")Microsoft 365 新增,比 LEFT + FIND 好讀

日期 ​

函數範例用途
TODAY / NOW=TODAY()今天日期,每次重算都會變(易變函數)
DATE=DATE(2024, 3, 1)由年月日組成日期,比在公式裡寫字串可靠
YEAR / MONTH / DAY=YEAR(A2)拆出年月日
WEEKDAY=WEEKDAY(A2, 2)星期幾;2 = 週一為 1
WORKDAY=WORKDAY(A2, 10, Holidays)加 10 個工作日(排除週末與假日表)
NETWORKDAYS=NETWORKDAYS(A2, B2, Holidays)兩日期間的工作日數
EDATE / EOMONTH=EOMONTH(A2, 0)加減月份 / 當月最後一天
DATEDIF=DATEDIF(A2, TODAY(), "y")年齡、年資(未列在函數清單但可用)

Excel 日期本質是序號(1900-01-01 = 1),所以可以直接相減得到天數。看起來像日期、卻靠左對齊的值其實是文字,要先轉型。

動態參照:OFFSET ​

OFFSET(reference, rows, cols, [height], [width]) 從起點往下 / 往右移動後回傳新的範圍,適合「依使用者選擇的週次取值」、動態圖表範圍。

公式意義
=OFFSET(A5, 1, MATCH($B$1, Weeks, 0))MATCH 算出 B1 選的週次在第幾欄,OFFSET 從 A5 移過去取值
=SUM(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1))從 B2 起、高度隨資料列數自動延伸的範圍(扣掉標題列)
注意說明
易變函數 (volatile)任何儲存格變動都重算,大型活頁簿會變慢
可讀性差搭配已命名範圍;只是查值時改用 XLOOKUP / INDEX
新版替代資料放進 Table 後範圍自動延伸,多數動態範圍需求就不需要 OFFSET

金融函數 ​

PMT、IPMT、PPMT、NPV、IRR 等見 貸款攤還表。