單元 3 · 第 10 ~ 13 章
進階篇:查詢函式、樞紐分析與自動化
精通 VLOOKUP / XLOOKUP 跨表查詢、資料驗證與條件格式防呆、可點選 Slicer 樞紐儀表板與表單/巨集自動化。
本單元作業得分
0 / 100 分
第 10 章:跨表查詢神器 —— VLOOKUP、XLOOKUP 與 INDEX+MATCH
利用「已命名範圍」(資料 > 已命名範圍) 替 '商品目錄'!$A$2:$E$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 欄變色!
第 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 列標題列!