第 10 章:跨表查詢神器 —— VLOOKUP、XLOOKUP 與 INDEX+MATCH

利用「已命名範圍」(資料 > 已命名範圍) 替 '商品目錄'!$A$2:$E$11 命名為 商品對照表 後,即可輕鬆將商品成本帶入訂單表計算毛利。

VLOOKUP(固定框整個矩陣+數第幾欄) =VLOOKUP(G2, $A$2:$E$11, 4, FALSE) • 搜尋欄必須在最左邊,只能向右找 • 中間插入新欄位時,第 4 欄會抓錯! • 沒寫 FALSE 會變模糊比對回傳錯價格 XLOOKUP(分開指定搜尋欄與結果欄) =XLOOKUP(G2, $A$2:$A$11, $D$2:$D$11, "無") ✓ 支援向左查詢,不限欄位方向 ✓ 插入新欄時,結果欄自動追蹤不錯位 ✓ 預設就是精確比對,還自帶找不到提示
⚡ 動態展示 XLOOKUP / VLOOKUP 跨表帶入成本與毛利透視鏡
列 A (商品名稱) B (商品類別) C (標準售價) D (第4欄: 成本價) E (供應商)

第 11 章:資料驗證下拉選單 & 自訂公式「整列標示」

資料驗證 (Data Validation)

  • 選取範圍 → 資料 > 資料驗證 > 新增規則。
  • 選擇 下拉式選單(來自範圍) 指向 ='商品目錄'!$A$2:$A$11,新品上架時選單自動更新。
  • 進階選項勾選「拒絕輸入」,防止同事亂打字造成統計失真。

條件式格式:自訂公式整列上色

選取範圍 A2:O31,設定「自訂公式為」:

=$L2="VIP"

關鍵:鎖欄 $L、不鎖列 2,才會讓 A~O 整列同時依據該列 L 欄變色!

A 訂單編號 E 業務員 L 客戶等級(判斷欄 $L) M 實付金額 ORD-2026-001 陳志明 VIP (=$L2="VIP" → TRUE!) $60,800 ORD-2026-002 林佳穎 一般 (=$L3="VIP" → FALSE) $6,000

第 12 章:樞紐分析表與交叉分析篩選器 (Slicers) 互動儀表板

在 Google Sheets 中,透過 插入 > 樞紐分析表 可在 10 秒內依日期(右鍵 > 建立樞紐分析日期群組 > 年-月)或類別彙整報表;再搭配 資料 > 新增交叉分析篩選器 (Slicer) 就能做出可即時點選切換的儀表板!請直接試玩下方模擬器:

⚡ 互動儀表板 樞紐分析表 + Slicer 交叉篩選模擬器(結合商品成本計算毛利率)
篩選後實付總額
$0
篩選後總毛利
$0
平均毛利率 (計算欄位)
0%
列標籤訂單數實付金額佔總計 %毛利毛利率
樞紐圖即時長條比較

第 13 章:保護機制、Google 表單串接、巨集與 AI

  • 保護範圍與公式:選取公式欄 → 資料 > 保護工作表和範圍,鎖定為「只限您自己」避免同事誤刪。
  • 串接 Google Forms:工具 > 建立新表單,新回覆會以「插入新列」寫入 表單回應 1,因此彙整公式務必另開分頁或寫在第 1 列標題列。
  • 錄製巨集 (Macros):擴充功能 > 巨集 > 錄製巨集,可把每天重複的排版動作錄下來並綁定快捷鍵一鍵重播(底層自動生成 Apps Script)。
  • Smart Fill 智慧填滿:手動輸入 1~2 筆拆分示範後按 Ctrl + Shift + Y,AI 自動推斷規律填滿整欄。

📝 進階篇隨堂實作作業(共 4 題,滿分 100 分)

實作 1:新世代查詢 XLOOKUP 帶入成本價 0 / 25 分

在 銷售紀錄 的 N2,請使用 XLOOKUP 以商品名稱 G2 到 '商品目錄'!$A$2:$A$11 搜尋,並帶回成本價 '商品目錄'!$D$2:$D$11,若找不到商品請顯示 "查無商品"(請記得鎖定 $):

參考解答:=XLOOKUP(G2, '商品目錄'!$A$2:$A$11, '商品目錄'!$D$2:$D$11, "查無商品")
實作 2:經典 VLOOKUP 精確查詢成本價 0 / 25 分

若改用傳統 VLOOKUP 在 銷售紀錄 N2 查詢 G2,查詢範圍為 '商品目錄'!$A$2:$E$11(成本價位於第 4 欄),請寫出包含精確比對的完整公式:

參考解答:=VLOOKUP(G2, '商品目錄'!$A$2:$E$11, 4, FALSE)(若已命名範圍也可寫 商品對照表)
實作 3:條件式格式自訂公式「整列標示 VIP」 0 / 25 分

設定條件式格式套用範圍為 A2:O31,希望凡是 L 欄客戶等級為 "VIP" 的訂單整列(A 到 O 欄)自動變色,請問「自訂公式為」應填寫什麼?

參考解答:=$L2="VIP"
實作 4:情境題 — 串接 Google Forms 的公式陷阱 0 / 25 分

將 Google 表單串接到試算表後,為什麼不建議直接在 表單回應 1 工作表的第 2 列往下手拉複製計算公式?

參考解答:選 B。正確做法是另開分析分頁或將 ARRAYFORMULA 寫在第 1 列標題列!
← 上一單元:中階篇 下一單元:隱藏神技篇(QUERY 與正則)→