第 14 ~ 15 章:QUERY 類 SQL 查詢與 FILTER / UNIQUE / SORTN

一般人做報表要拉樞紐分析表或寫十幾行 SUMIFS,高手只用一格 QUERY 函式!記住子句的嚴格執行順序:

1. SELECT選欄位/計算 → 2. WHERE文字用'單引號' → 3. GROUP BY分組彙總 → 4. PIVOT交叉展開 → 5. ORDER BYDESC 降冪 → 6. LIMIT / LABEL抓前N筆 / 改標題
⚡ 互動沙盒 QUERY & SORTN 一格產出整張動態報表(點選下方情境切換)
動態陣列四劍客速記: FILTER(* 代表 AND、+ 代表 OR)、SORT(UNIQUE(...))(抽出並排序不重複清單)、SORTN(範圍, 3, 0, 欄, FALSE)(直接抓前 N 名)。

第 16 章:一格管整欄 ARRAYFORMULA、LET 與 BYROW+LAMBDA

寫在 M1 一格:={"實付金額"; ARRAYFORMULA(IF(A2:A="", "", H2:H*I2:I*(1-J2:J)))} M2~M31 自動溢位計算 第 32 列新表單進來自動算 空白列維持乾淨不顯示 0

LET:為重複運算取變數名稱

=LET(
  實付, H2*I2*(1-J2),
  IFS(實付>=50000, "S級", 實付>=20000, "A級", TRUE, "B級")
)

BYROW + LAMBDA:解決 MAX/AND 陣列死穴

ARRAYFORMULA 無法逐列跑 MAX 或 AND,改用 BYROW 一列一列處理:

=BYROW(B2:D10, LAMBDA(row, MAX(row)))

第 17 章:正則表達式三兄弟 (REGEX) 與 TEXTJOIN

在上方或下方輸入自己的正則規則,即時在 dirty_contacts.csv 資料上測試 REGEXMATCH、REGEXEXTRACT 與 REGEXREPLACE 的效果:

⚡ REGEX 實驗室 正則表達式即時測試器(修改正則樣式會即時重算下表)
客戶來源文字函式輸出結果

TEXTJOIN 合併神器:=TEXTJOIN("、", TRUE, SORT(UNIQUE(FILTER('銷售紀錄'!G2:G31, '銷售紀錄'!C2:C31="北區")))) — 用頓號串接不重複清單並自動略過空白格。

第 18 ~ 19 章:多表堆疊 VSTACK / INDIRECT 與雲端獨享外掛函式

多表整合與動態切換

  • VSTACK:=VSTACK('1月'!A2:M, '2月'!A2:M, '3月'!A2:M) 垂直疊合多張分頁(也可用大括號 {'1月'!A2:M; '2月'!A2:M})。
  • CHOOSECOLS:=CHOOSECOLS(A1:M31, 1, 5, 7, 13) 挑選並重排特定欄。
  • INDIRECT:=SUM(INDIRECT("'" & B1 & "'!M2:M31")) 根據 B1 下拉選單動態切換工作表(大型檔案慎用以免重算變慢)。

Google Sheets 獨享雲端函式

  • GOOGLEFINANCE:=GOOGLEFINANCE("CURRENCY:USDTWD") 即時美金匯率,或 =GOOGLEFINANCE("TPE:2330", "price") 抓台積電股價。
  • GOOGLETRANSLATE:=GOOGLETRANSLATE(G2, "zh-TW", "en") 一鍵產生雙語商品目錄。
  • IMPORTHTML:=IMPORTHTML("公開網址", "table", 1) 免寫爬蟲直接抓取靜態網頁表格。

📝 隱藏神技篇隨堂實作作業(共 4 題,滿分 100 分)

實作 1:QUERY 一格產出業務員業績排行 0 / 25 分

使用單一 QUERY 查詢 '銷售紀錄'!A1:M31,選出業務員 (E) 與實付金額加總 (SUM(M)),依 E 分組並按 SUM(M) 由大到小 (DESC) 排序,標題列數為 1:

參考解答:=QUERY('銷售紀錄'!A1:M31, "SELECT E, SUM(M) GROUP BY E ORDER BY SUM(M) DESC", 1)
實作 2:SORTN 抓出全公司 Top 3 大單 0 / 25 分

請用 Google Sheets 專屬的 SORTN 函式,從 '銷售紀錄'!A2:M31 中嚴格取出第 13 欄(實付金額)最高的前 3 筆訂單(同分顯示模式填 0、由大到小填 FALSE):

參考解答:=SORTN('銷售紀錄'!A2:M31, 3, 0, 13, FALSE)
實作 3:標題列 ARRAYFORMULA 一格管整欄 0 / 25 分

請寫出能直接放在 M1 一格內的陣列公式:第 1 列顯示標題 "實付金額",下方若 A2:A 為空白就留白 "",否則計算整欄 H2:H*I2:I*(1-J2:J):

參考解答:={"實付金額"; ARRAYFORMULA(IF(A2:A="", "", H2:H*I2:I*(1-J2:J)))}
實作 4:REGEXREPLACE 一秒統一純數字電話 0 / 25 分

在 待清理名單 中,D2 電話格式有的含空白、有的含 -。請用 REGEXREPLACE 把 D2 裡面所有非數字字元替換成空字串 "":

參考解答:=REGEXREPLACE(D2, "[^\d]", "")
← 上一單元:進階篇 前往:速查手冊與總成績單 →