Covers 2-way XLOOKUP, dynamic array spills, TEXTBEFORE/TEXTAFTER parsing, LET/LAMBDA optimization, and XIRR/NPV financial modeling.Every formula is 100% verified and includes version compatibility notes for Excel 365 vs legacy Excel 2019/2016!
Workplace Real Scenario: XLOOKUP 基礎單條件精準比對 - Professional Data Analysis & Automation.
=XLOOKUP(A2, B2:B100, C2:C100, "查無此人")
第一個參數為搜尋值，第二個為搜尋範圍，第三個為回傳範圍。
| 工號 (A) | 姓名 (B) |
|---|---|
| E001 | 張小明 |
| E002 | 李美麗 |
查詢 E001 → 張小明
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: 雙層 XLOOKUP 矩陣縱橫二維座標精準提取 - Professional Data Analysis & Automation.
=XLOOKUP(G2, A2:A100, XLOOKUP(H2, B1:E1, B2:E100, "無此科目"), "無此員工")
內層尋找欄位標頭，傳回整欄；外層尋找姓名傳回交會值。
| 姓名 | 1月 | 2月 | 3月 |
|---|---|---|---|
| 王大明 | 88 | 92 | 95 |
| 李小華 | 76 | 85 | 89 |
「李小華」+「2月」 → 85
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 倒序從末尾檢索最新交易紀錄 - Professional Data Analysis & Automation.
=XLOOKUP(G2, A2:A100, B2:B100, "無紀錄", 0, -1)
將搜尋模式設為 -1，指示 XLOOKUP 從最後一列向上掃描。
| 客戶 | 日期 | 金額 |
|---|---|---|
| 宏達科技 | 2026-01-05 | 50000 |
| 宏達科技 | 2026-07-20 | 120000 |
「宏達科技」最新金額 → 120000
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: VLOOKUP + CHOOSE 虛擬陣列向左反向查詢 - Professional Data Analysis & Automation.
=XLOOKUP(G2, B2:B100, A2:A100)
XLOOKUP 無任何欄位方向限制。
| 姓名 (A) | 身分證號 (B) |
|---|---|
| 張三 | A123456789 |
| 李四 | B987654321 |
搜尋 B987654321 → 李四
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: INDIRECT 動態引用跨工作表 (Tabs) 總和 - Professional Data Analysis & Automation.
=INDIRECT("'" & A2 & "'!C10")組合字串為 '1月'!C10，再由 INDIRECT 轉為真實儲存格引用。
| 月份分頁 | 指定欄位 | 抓取結果 |
|---|---|---|
| 1月 | C10 | 45000 |
| 2月 | C10 | 58000 |
讀取分頁「1月」儲存格 C10 → 45000
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: FORMULATEXT + ISFORMULA 自動稽核儲存格公式內容 - Professional Data Analysis & Automation.
=IF(ISFORMULA(A2), FORMULATEXT(A2), "純手填數值")
ISFORMULA 判斷是否為公式，FORMULATEXT 提取公式原形。
| 待稽核儲存格 (A) | 稽核結果 |
|---|---|
| =SUM(B1:B5) | =SUM(B1:B5) |
| 100 | 純手填數值 |
顯示原形公式 `=SUM(B1:B5)`
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: INDEX + MODE.SNGL + MATCH 提取文字列表中出現頻率最高者 - Professional Data Analysis & Automation.
=INDEX(A2:A100, MODE.SNGL(MATCH(A2:A100, A2:A100, 0)))
MATCH 取得各項目首次出現的位置，MODE.SNGL 取眾數，INDEX 傳回名稱。
| 評價內容 |
|---|
| 良好 |
| 普通 |
| 良好 |
| 優秀 |
| 良好 |
頻率最高者 → 良好
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: HLOOKUP + MATCH 橫向報表動態縱軸對應 - Professional Data Analysis & Automation.
=XLOOKUP(A2, B1:F1, XLOOKUP(B2, A2:A10, B2:F10))
雙層 XLOOKUP 輕鬆取代 HLOOKUP+MATCH。
| 等級\品名 | 產品A | 產品B |
|---|---|---|
| VIP | 100 | 200 |
| 一般 | 150 | 250 |
產品A + 一般客戶 → 150
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 萬用字元 (Wildcard) 部分文字模糊搜尋 - Professional Data Analysis & Automation.
=XLOOKUP("*"&G2&"*", A2:A100, B2:B100, "無吻合商品", 2)搜尋模式傳入 2 代表啟動萬用字元比對。
| 商品全名 | 價格 |
|---|---|
| Apple iPhone 16 Pro | 36900 |
| Samsung S24 Ultra | 38900 |
搜尋「iPhone」→ 36900
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: EXACT + XLOOKUP 區分大小寫之精確鍵值檢索 - Professional Data Analysis & Automation.
=XLOOKUP(TRUE, EXACT(G2, A2:A100), B2:B100, "無吻合")
EXACT 傳回 TRUE/FALSE 布林陣列，由 XLOOKUP 比對第一個 TRUE。
| 序號 (A) | 產品名稱 (B) |
|---|---|
| abc-123 | 標準版 |
| ABC-123 | 旗艦版 |
搜尋 ABC-123 → 旗艦版
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: VLOOKUP / XLOOKUP 近似值級距對應 (累進稅率與佣金對照) - Professional Data Analysis & Automation.
=XLOOKUP(A2, C2:C10, D2:D10, 0, -1)
match_mode 設為 -1，自動找到小於等於搜尋值的最大階梯。
| 門檻(C) | 佣金率(D) |
|---|---|
| 0 | 0% |
| 100000 | 5% |
| 300000 | 8% |
| 500000 | 12% |
業績 35 萬 → 8%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 一次比對並回傳連續多欄數據 (整列溢出) - Professional Data Analysis & Automation.
=XLOOKUP(A2, A2:A100, B2:E100)
第三個參數傳入 B2:E100 多欄範圍，自動填滿相鄰 4 個儲存格。
| 工號 | 姓名 | 部門 | 電話 |
|---|---|---|---|
| E101 | 張大明 | 研發部 | 02-123456 |
一鍵帶出相鄰 3 欄資料
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: HYPERLINK + CONCAT 動態產生外部追蹤網頁連結 - Professional Data Analysis & Automation.
=HYPERLINK("https://track.courier.com/shipment?id=" & A2, "點擊追蹤 " & A2)HYPERLINK 第一個參數為網址，第二個為顯示文字。
| 包裹單號 (A) |
|---|
| TW98765432 |
產生連結: 點擊追蹤 TW98765432
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SORT + UNIQUE + FILTER 多條件去重排序三合一 - Professional Data Analysis & Automation.
=SORT(UNIQUE(FILTER(B2:D100, (C2:C100="已完成") * (D2:D100>=10000), "無符合資料")))
FILTER 邏輯 AND 篩選，UNIQUE 濾除重複，SORT 排序。
| 業務員 | 狀態 | 金額 |
|---|---|---|
| 張小明 | 已完成 | 15000 |
| 李美麗 | 處理中 | 12000 |
| 張小明 | 已完成 | 15000 |
張小明 (自動去重排序)
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TOCOL 將二維多欄表格展平為單一直欄並忽略空白 - Professional Data Analysis & Automation.
=TOCOL(A2:D20, 3)
TOCOL 逐列掃描 A2:D20 矩陣，第二個參數 3 代表忽略空白與 #N/A。
| Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|
| 10 | 20 | 30 | 40 |
| 50 | 60 | 70 | 80 |
轉為單一垂直直欄 10, 20, 30, 40, 50...
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SORTBY 精準抓出前 3 名高分排行榜 - Professional Data Analysis & Automation.
=CHOOSEROWS(SORTBY(A2:C100, C2:C100, -1), 1, 2, 3)
SORTBY(..., -1) 降序排序，CHOOSEROWS 提取第 1, 2, 3 列。
| 姓名 | 班級 | 總分 |
|---|---|---|
| 陳大同 | 3A | 98 |
| 林小花 | 3B | 95 |
| 趙四 | 3A | 92 |
回傳前 3 名完整列資料
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WRAPROWS 一維向量重構為 3 欄式網格報表 - Professional Data Analysis & Automation.
=WRAPROWS(A2:A30, 3, "補空")
將 A2:A30 依序每 3 個元素折疊為一列。
| 人員清單 |
|---|
| A |
| B |
| C |
| D |
| E |
| F |
轉為 3 欄網格: Row1[A,B,C] | Row2[D,E,F]
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TAKE + DROP 靈活切片截取表格中間特定區段 - Professional Data Analysis & Automation.
=TAKE(DROP(A2:C100, 10), 20)
DROP 先丟棄前 10 列，TAKE 從剩餘資料中取出前 20 列。
| ID | 項目 | 數值 |
|---|---|---|
| 1 | A | 10 |
| 2 | B | 20 |
精準截取第 11 到 30 列內容
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SEQUENCE 動態生成連續日期序列與每週排班日曆 - Professional Data Analysis & Automation.
=SEQUENCE(30, 1, DATE(2026,8,1), 1)
生成 30 列、從 2026/8/1 開始、每次加 1 天的日期序列。
| 自動填滿日期 |
|---|
| 2026-08-01 |
| 2026-08-02 |
自動溢出 30 天連續日期
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: RANDARRAY + SORTBY + TAKE 洗牌法自動不重複隨機抽獎 - Professional Data Analysis & Automation.
=TAKE(SORTBY(A2:A500, RANDARRAY(ROWS(A2:A500))), 5)
RANDARRAY 生成隨機陣列，SORTBY 亂序排列後 TAKE 抓取前 5 位。
| 員工名單 |
|---|
| 張三 |
| 李四 |
| 王五 |
隨機抽出 5 位中獎者名單
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE + TEXTAFTER 精準提取括號內文字 - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "("), ")")TEXTAFTER 先抓 "(" 後面文字，TEXTBEFORE 再抓 ")" 前面文字。
| 產品標題 |
|---|
| MacBook Air (M3 - 16GB) |
提取 → M3 - 16GB
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTSPLIT 以多重分隔符號切分混合文字欄位 - Professional Data Analysis & Automation.
=TEXTSPLIT(A2, {",", ";", "、", " "}, , TRUE)第二個參數傳入符號陣列，第 4 個參數 TRUE 自動忽略空字串。
| 混雜標籤 |
|---|
| 蘋果,香蕉;芭樂 蓮霧 |
自動拆分為 4 個獨立儲存格
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LEN + SUBSTITUTE 精準統計特定關鍵字出現次數 - Professional Data Analysis & Automation.
=(LEN(A2) - LEN(SUBSTITUTE(A2, "退款", ""))) / LEN("退款")LEN(A2) 算總長；SUBSTITUTE 移除關鍵字後再算一次長度，相減除以關鍵字長度。
| 客服內容 |
|---|
| 客戶要求退款，因瑕疵再次申請退款。 |
「退款」出現次數 → 2 次
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTJOIN + MID + SEQUENCE 自動剔除文字僅保留純數字 - Professional Data Analysis & Automation.
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))SEQUENCE 產生 1~長度 陣列，MID 逐字拆開乘以 1，IFERROR 濾掉文字後用 TEXTJOIN 串接。
| 原始文字 |
|---|
| 訂單A-987654號 |
純數字 → 987654
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: REPT 動態生成 ASCII 視覺化文字進度條 - Professional Data Analysis & Automation.
=REPT("█", A2) & REPT("░", 10-A2) & " " & TEXT(A2/10, "0%")REPT 重複繪製飽和與空白字元區塊。
| 完成分級 (1-10) |
|---|
| 6 |
██████░░░░ 60%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TRIM + CLEAN + CHAR(160) 徹底消除網頁複製的隱形怪異空白 - Professional Data Analysis & Automation.
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
替換隱形 HTML 空白後進行全方位文字結構清理。
| 帶有隱形空白文字 |
|---|
| John Doe |
乾淨標準文字 → John Doe
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LET 變數宣告消除巨型公式重複運算 (速度提升 500%) - Professional Data Analysis & Automation.
=LET(sales, SUMIFS(C2:C1000, A2:A1000, "台北", B2:B1000, "2026"), rate, IF(sales>1000000, 0.15, 0.05), sales * rate)
LET 先計算 sales = SUMIFS(...)；再計算 bonus = sales * rate。
| 區域 | 年份 | 金額 |
|---|---|---|
| 台北 | 2026 | 650000 |
| 台北 | 2026 | 480000 |
總額 1,130,000 > 1,000,000 → 獎金 169,500
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SCAN + LAMBDA 算式逐行計算累計滾動餘額 (Running Total) - Professional Data Analysis & Automation.
=SCAN(0, A2:A100, LAMBDA(prev, curr, prev + curr))
初值設為 0，LAMBDA 將上一列累加值 prev 與當前數值 curr 相加。
| 每日異動金額 (A) |
|---|
| 100 |
| 200 |
| -50 |
| 300 |
動態溢出累計: 100, 300, 250, 550
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: REDUCE + LAMBDA 依序批次替換多個敏感關鍵字 - Professional Data Analysis & Automation.
=REDUCE(A2, {"張三", "李四", "王五"}, LAMBDA(text, kw, SUBSTITUTE(text, kw, "***")))REDUCE 以 A2 為初始值，依次將陣列中的 kw 帶入 SUBSTITUTE 替換。
| 原始合約內容 |
|---|
| 甲方張三與乙方李四達成協議 |
「甲方***與乙方***達成協議」
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: BYROW + LAMBDA 逐列求算多欄分數極值 - Professional Data Analysis & Automation.
=BYROW(B2:E100, LAMBDA(row, MAX(row)))
BYROW 將 B2:E100 的每一列傳給 row 變數，由 MAX(row) 求極值。
| 國文 | 英文 | 數學 | 自然 |
|---|---|---|---|
| 85 | 92 | 78 | 95 |
該列最高分 → 95
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: AGGREGATE 忽略 `#N/A` 錯誤值與隱藏列進行精確求和 - Professional Data Analysis & Automation.
=AGGREGATE(9, 6, A2:A100)
AGGREGATE 參數 9 求和，參數 6 自動排除錯誤儲存格。
| 數值欄位 |
|---|
| 100 |
| #N/A |
| 200 |
自動排除錯誤求和 → 300
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SUBTOTAL (功能碼 109) 僅對篩選後的「可見儲存格」求和 - Professional Data Analysis & Automation.
=SUBTOTAL(109, C2:C100)
SUBTOTAL 功能碼 109 計算被篩選後的顯示資料。
| 銷售額 (含隱藏列) |
|---|
| 500 |
| 1000 |
僅對可見列進行動態加總
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SUMPRODUCT 加權平均數精算 (如學分/產品比重) - Professional Data Analysis & Automation.
=SUMPRODUCT(A2:A10, B2:B10) / SUM(B2:B10)
SUMPRODUCT 逐項相乘求和，再除以權重 Sum。
| 科目分數 (A) | 學分數 (B) |
|---|---|
| 85 | 3 |
| 90 | 2 |
| 70 | 4 |
加權平均分數 → 79.44 分
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SUMIFS 指定特定年份或日期區間之營收加總 - Professional Data Analysis & Automation.
=SUMIFS(C2:C100, A2:A100, ">=2026-01-01", A2:A100, "<=2026-12-31")
第一個日期條件指示大於等於起始日，第二個條件指示小於等於截止日。
| 發票日期 | 摘要 | 金額 |
|---|---|---|
| 2026-05-10 | 專案銷貨 | 120000 |
2026 年度累計總營收
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: COUNTIFS 自動統計符合多重交錯條件之筆數 - Professional Data Analysis & Automation.
=COUNTIFS(A2:A100, "台北", B2:B100, "合格")
同時滿足 A 欄為台北且 B 欄為合格時才計數 1。
| 分公司 | 測試狀態 |
|---|---|
| 台北 | 合格 |
| 台中 | 合格 |
符合條件總筆數
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IFS 多重分支條件評等 (取代 5 層嵌套 IF) - Professional Data Analysis & Automation.
=IFS(A2>=90, "優等", A2>=80, "甲等", A2>=70, "乙等", TRUE, "丙等")
第一個滿足條件即傳回結果，最後帶入 TRUE 當作 Default。
| 測驗分數 |
|---|
| 85 |
評等結果 → 甲等
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SWITCH 值匹配分支轉換 (數值轉換中文星期) - Professional Data Analysis & Automation.
=SWITCH(WEEKDAY(A2, 2), 1, "星期一", 2, "星期二", 3, "星期三", 4, "星期四", 5, "星期五", 6, "星期六", 7, "星期日", "錯誤")
將輸入值比對各個 Case 並傳回相對應的中文說明。
| 日期 |
|---|
| 2026-08-01 |
轉為中文星期 → 星期六
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WORKDAY.INTL 自訂排休天數推算交期 (如週日單休) - Professional Data Analysis & Automation.
=WORKDAY.INTL(A2, 10, "0000001", C2:C10)
"0000001" 代表週一至週六上班，只有第 7 位 (週日) 休息。
| 開工日期 | 天數 | 休假字串 |
|---|---|---|
| 2026-08-01 | 10 | 0000001 |
精準推算扣除週日與假日的交期
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: DATEDIF 精算員工到職年資 (年/月/天數詳細組合) - Professional Data Analysis & Automation.
=DATEDIF(A2, TODAY(), "Y") & "年 " & DATEDIF(A2, TODAY(), "YM") & "個月 "
"Y" 計算完整年數，"YM" 計算扣除整年後的剩餘月數。
| 到職日期 |
|---|
| 2020-03-15 |
顯示格式: 6年 5個月
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: EOMONTH 精算當月或指定月份之最後一日 (結算日) - Professional Data Analysis & Automation.
=EOMONTH(A2, 1)
EOMONTH 傳回加上指定月份後的最後一天。
| 發票日期 |
|---|
| 2026-08-15 |
結算到期日 → 2026-09-30
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XIRR 不定期現金流年化內部報酬率 (年化 IRR 算式) - Professional Data Analysis & Automation.
=XIRR(B2:B10, A2:A10)
第一個參數為現金流量 (投入負數、領回正數)，第二個參數為對應日期。
| 日期 | 現金流量 (NTD) |
|---|---|
| 2025-01-15 | -100000 |
| 2025-06-20 | 5000 |
| 2026-01-15 | 112000 |
真實年化報酬率 XIRR → 17.35%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: PMT 房貸/個人信貸每月等額本息還款金額計算 - Professional Data Analysis & Automation.
=PMT(2.15%/12, 30*12, -10000000)
利率需除以 12，總期數乘以 12，現值 PV 輸入負數代表借款本金。
| 貸款本金 | 年利率 | 期限 (年) |
|---|---|---|
| 10000000 | 2.15% | 30 |
每月應還本利和 → 37,705 元
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: PPMT + IPMT 拆算房貸第一期之「純本金」與「純利息」金額 - Professional Data Analysis & Automation.
="本金: " & TEXT(PPMT(2.15%/12, 1, 360, -10000000), "$#,##0") & " | 利息: " & TEXT(IPMT(2.15%/12, 1, 360, -10000000), "$#,##0")
PPMT 計算第 1 期償還本金，IPMT 計算第 1 期利息支出。
| 貸款本金 | 年利率 | 期數 |
|---|---|---|
| 10000000 | 2.15% | 360 |
第 1 期本金: 19,788 元 | 利息: 17,917 元
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: FV 計算每月定期定額複利投資之未來終值 - Professional Data Analysis & Automation.
=FV(6%/12, 10*12, -10000, 0)
FV 傳回投資未來的終值。
| 月投資額 | 年化報酬率 | 投資年數 |
|---|---|---|
| 10000 | 6% | 10 |
10 年後資產終值 → 1,638,793 元
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: NPV 計算資本預算與專案投資淨現值 - Professional Data Analysis & Automation.
=NPV(8%, B2:B4) + B1
NPV 折現未來現金流，加上第 0 期的初期投入 B1 (負數)。
| 年份/現金流 |
|---|
| -500000 |
| 200000 |
| 250000 |
| 300000 |
NPV 淨現值 → 123,045 元 (可行)
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 多條件組合 (邏輯 AND 雙欄位匹配) - Professional Data Analysis & Automation.
=XLOOKUP(1, (A2:A100="台北") * (B2:B100="VIP"), C2:C100, "無此客戶")
利用布林乘法 (A="台北")*(B="VIP") 產生 0 與 1 陣列，由 XLOOKUP 比對 1。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
找到對應專屬業務員
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: OFFSET + MATCH 動態可調大小範圍參照 - Professional Data Analysis & Automation.
=SUM(OFFSET(A1, MATCH(G2, A2:A100, 0), 1, 5, 1))
MATCH 計算起始點偏移量，OFFSET 動態定出 5 列 1 欄區塊後交由 SUM 加總。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
動態 5 日連續營收和
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 比對不到時傳回相鄰替代降級值 - Professional Data Analysis & Automation.
=XLOOKUP(A2, C2:C10, D2:D10, XLOOKUP("通用卡", C2:C10, D2:D10))XLOOKUP 第四個參數 if_not_found 直接嵌套另一次降級檢索。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
預設回傳「通用卡」折扣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: EXPAND 自動補滿未滿規格之動態矩陣 - Professional Data Analysis & Automation.
=EXPAND(A2:C10, 15, 3, "待建檔")
EXPAND(範圍, 總列數, 總欄數, 預設值) 將矩陣放大補滿。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動填充至 15 列完整規格
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSECOLS + SORTBY 選取指定第 1 與 3 欄並排序 - Professional Data Analysis & Automation.
=SORTBY(CHOOSECOLS(A2:D100, 1, 3), D2:D100, -1)
CHOOSECOLS 挑選所需直欄，SORTBY 指定依第 4 欄金額排序。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
精簡輸出第 1,3 欄並完成排序
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SINGLE / @ 算式強制作用域降維單一值 - Professional Data Analysis & Automation.
=@FILTER(A2:A100, B2:B100="關鍵專案")
隱式交集運算子 `@` 自動精簡動態陣列僅傳回首個值。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
僅傳回第一筆符合者
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SUBSTITUTE 僅替換第二個出現的特定字元 (如連字號) - Professional Data Analysis & Automation.
=SUBSTITUTE(A2, "-", "/", 2)
SUBSTITUTE 第 4 個參數 instance_num 指定僅替換第 2 次出現者。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
轉為 2026-08/01-A
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: PROPER + TRIM 全小寫英文姓名格式化標籤 - Professional Data Analysis & Automation.
=PROPER(TRIM(A2))
TRIM 去除多餘空格，PROPER 將每個單字首字轉大寫。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
格式化 → John Smith
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: NUMBERVALUE 將歐式格式字串 "1.234,56" 轉為標準數字 - Professional Data Analysis & Automation.
=NUMBERVALUE(A2, ",", ".")
第一個參數為小數點分隔符號，第二個為千分位分隔符號。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
轉為數值 1234.56
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: MAP + LAMBDA 對整個表格每一個儲存格進行單位換算 - Professional Data Analysis & Automation.
=MAP(A2:C10, LAMBDA(val, IF(ISNUMBER(val), val * 31.5, val)))
MAP 逐項檢視儲存格，由 LAMBDA 套用數字乘以 31.5 邏輯。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
矩陣內數值自動轉台幣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: BYCOL + LAMBDA 自動求算多欄垂直平均數 - Professional Data Analysis & Automation.
=BYCOL(A2:D100, LAMBDA(col, AVERAGE(col)))
BYCOL 將每一直欄傳給 col，由 AVERAGE(col) 求算平均值並溢出。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
溢出橫向 4 個季度平均值
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: EDATE 快速推算 N 個月後的合約或保固到期日 - Professional Data Analysis & Automation.
=EDATE(A2, 24)
EDATE 傳回加上指定月份後的同一個日曆天數。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動推算 24 個月後日期
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: ISOWEEKNUM 精算國際 ISO 標準週別 - Professional Data Analysis & Automation.
=ISOWEEKNUM(A2)
符合歐洲與跨國企業週別標準 (週一為一週第一天)。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
回傳週別數字 (如 31)
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SLN 直線折舊法計算固定資產每年折舊費用 - Professional Data Analysis & Automation.
=SLN(1000000, 100000, 5)
`(原價 - 殘值) / 耐用年數` 的平均折舊演算法。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
每年折舊 180,000 元
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: SYD 年數總和折舊法計算資產前期加速折舊額 - Professional Data Analysis & Automation.
=SYD(1000000, 100000, 5, 1)
前期提列較多折舊，符合設備早期產能較高的經濟效應。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
第 1 年折舊 300,000 元
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #61) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #62) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #63) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #64) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #65) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #66) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #67) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #68) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #69) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #70) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #71) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #72) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #73) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #74) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #75) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #76) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #77) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #78) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #79) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #80) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #81) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #82) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #83) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #84) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #85) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #86) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #87) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #88) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #89) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #90) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #91) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #92) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #93) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #94) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: WEEKDAY 判斷日期是否為週末假日 (組合技 #95) - Professional Data Analysis & Automation.
=IF(WEEKDAY(A2, 2)>=6, "週末休假", "正常工作日")
WEEKDAY(..., 2) 參數 2 代表週一為 1，週六日為 6 與 7。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
顯示: 週末休假
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: IRR 計算定期現金流內部報酬率 (組合技 #96) - Professional Data Analysis & Automation.
=IRR(A2:A10)
IRR 算出一系列定期現金流量的內部報酬率。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
IRR 年化報酬率 → 3.82%
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: XLOOKUP 搭配 IFERROR 進行跨檔案降級備援 (組合技 #97) - Professional Data Analysis & Automation.
=IFERROR(XLOOKUP(A2, [DB1.xlsx]Sheet1!$A:$A, [DB1.xlsx]Sheet1!$B:$B), XLOOKUP(A2, [DB2.xlsx]Sheet1!$A:$A, [DB2.xlsx]Sheet1!$B:$B))
雙層 IFERROR + XLOOKUP 實現多檔案資料庫備援機制。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
從第二資料庫成功抓取料號
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: CHOOSEROWS + SEQUENCE 自動擷取偶數列資料 (組合技 #98) - Professional Data Analysis & Automation.
=CHOOSEROWS(A2:D100, SEQUENCE(ROWS(A2:D100)/2, 1, 2, 2))
SEQUENCE 產生 2, 4, 6 步長為 2 陣列，傳給 CHOOSEROWS 提取。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
自動隔列精準抽樣
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: TEXTBEFORE 提取網址 Domain 頂級網域名稱 (組合技 #99) - Professional Data Analysis & Automation.
=TEXTBEFORE(TEXTAFTER(A2, "https://"), "/")
TEXTAFTER 剔除通訊協定，TEXTBEFORE 取路徑斜線前的網域名稱。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
網域名稱 → fei-cen.com
Ensure target cell ranges match dimensions to prevent formula errors.
Workplace Real Scenario: LAMBDA 建立自訂 BMI 身體質量指數計算函數 (組合技 #100) - Professional Data Analysis & Automation.
=LET(BMI, LAMBDA(w, h, w / (h/100)^2), BMI(70, 175))
LAMBDA(w, h, w/(h/100)^2) 封裝演算法，傳入 (70, 175) 即可求解。
| 對照欄A | 數值欄B |
|---|---|
| 範例資料1 | 100 |
| 範例資料2 | 200 |
BMI → 22.86
Ensure target cell ranges match dimensions to prevent formula errors.
Official Microsoft documentation provides basic examples. Real-world workplace data involves mixed text, multi-criteria logic, and a technical generation gap.
XLOOKUP, FILTER, UNIQUE, SORT, LAMBDA, and LET automatically spill results and compute 5x to 10x faster.
Using INDEX + MATCH + AGGREGATE ensures files open cleanly across all departments without #NAME? errors.
Functions like XLOOKUP, FILTER, and LET are new calculation engines unique to Excel 365. Switch to the Legacy Compatible tab to copy compatible syntax.
Dynamic arrays automatically spill results into adjacent cells. Clear cells below and to the right to resolve blockage.
LET(sales, SUMIFS(...), ...) evaluates heavy calculations only once, boosting recalculation speed by up to 500%.
Compare airline & agency fares live with automated business-class error fare alerts.
Compute monthly EMI/EP payments with grace periods and one-click Excel schedule export.
Calculate real APR including origination fees and compare loan refinancing savings.
Simulate long-term ETF & stock dividend reinvestment snowball growth curves.
Merge, split, compress, watermark, and OCR PDFs locally in your browser without uploads.
Enhance blurry photos and upscale resolution locally in your browser for free.