Google 試算表進階函式：QUERY、IMPORTRANGE 怎麼選

從 Excel 轉到 Google 試算表，常常會問：這邊有沒有什麼是原本做不到、現在一個函式就解決的？

有。這篇我依「想解決什麼問題」把 Sheets 內建的進階函式分成幾組，每組講一個函式在幹嘛、語法長怎樣，詳細的用法再連到站內別篇。要先說的是，這些函式不全是 Sheets 獨有：有幾個 Excel 365 近年也補了類似的（例如 IMAGE、TEXTSPLIT），只是名稱或參數排列不同，文末有一張 Excel 對照表。這篇的寫法一律以 Google 試算表為準。

範例資料用一張小小的茶飲銷量表，放在 A1:C6：

品項	杯數	單價
蘋果紅茶	12	45
青茶	7	38
烏龍	9	42
蘋果紅茶	5	45
檸檬綠	8	40

把別的檔案或網頁的資料拉進來

IMPORTRANGE

資料散在好幾個試算表的時候，複製貼上每天都得重做一次。IMPORTRANGE 直接把另一份試算表的範圍接進來，來源更新，這邊跟著更新。

Google 試算表公式匯入另一份檔案的「銷量」工作表 A1:C6

匯入另一份檔案的「銷量」工作表 A1:C6

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/你的檔案ID", "銷量!A1:C6")

第一次用會出現 #REF!，點一下儲存格，卡片上寫「你必須連結這些試算表。目標試算表初次從新的來源試算表擷取資料時，你必須授予權限。」按右邊的「允許存取」就好。授權的細節在 IMPORTRANGE 跨試算表同步，如果是總部收多家分店的檔案，看多門市銷售彙總。

我個人每天用到的函式裡，這個排前三。

IMPORTHTML 與 IMPORTXML

網頁上的表格或清單想抓下來，可以用 IMPORTHTML：

查詢：填 "table" 或 "list"。
索引：頁面上第幾個表格（或清單），從 1 開始。

Google 試算表公式抓網頁上的第 1 個表格，網址換成你要抓的頁面

抓網頁上的第 1 個表格，網址換成你要抓的頁面

=IMPORTHTML("https://example.com/prices", "table", 1)

要抓的不是表格，而是頁面上的某個標題或某一段文字，就換 IMPORTXML，用 XPath 指出位置：

第一次在檔案裡用 IMPORTHTML、IMPORTXML 這類抓外部資料的函式，儲存格會先是 #REF!，畫面上方出現橫幅「警告：某些公式正試圖傳送資料到外部或接收外部的資料。」按旁邊的「允許存取」才會載入。

兩個函式的完整教學和 XPath 的寫法，在 IMPORTXML：精確抓取網頁資料，另外 IMPORTDATA（匯入 .csv）和 IMPORTFEED（匯入 RSS）在網頁資料匯入函式。

用一條算式查資料

QUERY

QUERY 讓你用類似 SQL 的語法，一條算式完成篩選、分組、排序。把單價 40 以上的品項，依品項加總杯數，杯數多的排前面：

Google 試算表公式單價 40 以上，依品項加總杯數，由多到少排序

單價 40 以上，依品項加總杯數，由多到少排序

=QUERY(
  A1:C6,
  "select A, sum(B) where C >= 40 group by A order by sum(B) desc",
  1
)

來看看結果：

品項	sum 杯數
蘋果紅茶	17
烏龍	9
檸檬綠	8

第三個參數 1 是告訴它第一列是標題。查詢字串裡的欄位用 A、B、C 這種欄名，不是標題文字。語法一步步教在 QUERY 基礎。

一次算完整欄

ARRAYFORMULA

平常算金額，要在 D2 寫，再往下拉。ARRAYFORMULA 讓一條公式直接處理整個範圍：

Google 試算表公式D2 一格公式，算出每一列的金額，自動往下展開

D2 一格公式，算出每一列的金額，自動往下展開

=ARRAYFORMULA(B2:B6*C2:C6)

D2:D6 會是 540、266、378、225、320。新增資料要自動跟上的做法在 ARRAYFORMULA 入門，表單回覆表怎麼套用在表單回覆表自動填滿，想先建立「一次算一整欄」的觀念可以看陣列思維。

切開與擷取文字

SPLIT

一格裡塞了好幾段資料，用分隔符號切開，分別放進同一列的相鄰儲存格：

Google 試算表公式用斜線把一段文字切成三格

用斜線把一段文字切成三格

=SPLIT("蘋果紅茶/12/45", "/")

結果會分成三格：蘋果紅茶、12、45。

後面兩個選填參數要留意預設值：「依每個字元分割」預設是 TRUE，分隔符號裡的每一個字元都算切點，所以會得到 A、B、C 三格，連 B-C 中間的單一 - 也被切開。想把 -- 當成一整段分隔符號，第三個參數要設成 FALSE。另外，切出來的數字字串會自動轉成數字，"0912" 會變成 912，開頭的 0 會不見。

REGEXMATCH、REGEXEXTRACT、REGEXREPLACE

分隔符號不固定的時候，換規則運算式（regex）上場。三個函式分工：

REGEXMATCH(文字, 規則)：符合規則嗎，回 TRUE 或 FALSE。
REGEXEXTRACT(文字, 規則)：把符合的那一段取出來。
REGEXREPLACE(文字, 規則, 取代文字)：把符合的部分換掉。

Google 試算表公式從訂單訊息取出編號

從訂單訊息取出編號

=REGEXEXTRACT("訂單 A-1024 已出貨", "[A-Z]-[0-9]+")

結果是 A-1024。同一個規則丟給 REGEXMATCH 是 TRUE。

Google 試算表公式只留下數字，其他符號都拿掉

只留下數字，其他符號都拿掉

=REGEXREPLACE("0912-345-678", "[^0-9]", "")

結果是 0912345678，連字號、空格、括號這類非數字都會被拿掉。Sheets 用的是 RE2 語法，常見的寫法和限制整理在從混亂文字中提取資訊。

連到 Google 的服務

GOOGLEFINANCE

股價、匯率、歷史收盤價，直接從 Google 財經抓進儲存格。

代碼：美股直接寫 "AAPL"，台股要加交易所前綴，像 "TPE:2330"。匯率用 "CURRENCY:USDTWD" 這種格式。
屬性：不填就是 "price"。常用的有 "price"（報價，有延遲）、"changepct"（漲跌百分比）、"high52" 和 "low52"（52 週高低）、"volume"（成交量）、"marketcap"（市值）、"datadelay"（資料延遲分鐘數）。
起始日、結束日、間隔：有填的話就是歷史資料，屬性可用 "open"、"close"、"high"、"low"、"volume"；間隔填 "DAILY" 或 "WEEKLY"。

Google 試算表公式查台積電報價

查台積電報價

=GOOGLEFINANCE("TPE:2330", "price")

Google 試算表公式蘋果過去 30 天的每日收盤價，結果往下展開成兩欄

蘋果過去 30 天的每日收盤價，結果往下展開成兩欄

=GOOGLEFINANCE("AAPL", "close", TODAY()-30, TODAY(), "DAILY")

這是延遲報價，最多會延遲 20 分鐘（可以用 "datadelay" 查延遲分鐘數），價格也一直在變，這邊就不放數字了，貼上去你自己的畫面就會看到。這個函式有機會回錯誤或空白，重要的數字別全靠它。台股代碼、匯率、歷史資料的完整寫法在 GOOGLEFINANCE 抓股價與匯率。

GOOGLETRANSLATE 與 DETECTLANGUAGE

產品名稱、客戶留言這類要翻譯的文字，不用複製到翻譯網站。下面兩個例子假設 A2 是一段外文的商品名稱或客戶留言（上面茶飲表的 A2 是中文品名，翻成繁體中文不會有變化，請換成你自己的資料）：

Google 試算表公式把 A2 自動偵測語言並翻成繁體中文

把 A2 自動偵測語言並翻成繁體中文

=GOOGLETRANSLATE(A2, "auto", "zh-TW")

Google 試算表公式英文翻成日文

英文翻成日文

=GOOGLETRANSLATE("Hello", "en", "ja")

第二個會得到「こんにちは」。常用語言代碼有 "zh-TW"（繁中）、"zh-CN"（簡中）、"en"、"ja"、"ko"。

只想知道這段文字是哪國語言，用 DETECTLANGUAGE：

Google 試算表公式偵測英文句子的語言代碼

偵測英文句子的語言代碼

=DETECTLANGUAGE("Hello, how are you?")

會回 en。語言代碼表和兩個函式搭配的做法在 GOOGLETRANSLATE 與 DETECTLANGUAGE。翻完之後我習慣複製、用 Ctrl + Shift + V（Mac：Cmd + Shift + V，在 Chrome）貼成值，這樣之後不會每次開檔都重新查一遍。

在儲存格裡放圖

IMAGE

模式 1（預設）：保持比例，縮到放得進儲存格。
模式 2：拉伸填滿，比例可能會變形。
模式 3：原始大小。
模式 4：搭配高度、寬度自訂大小。

Google 試算表公式假設 A 欄放商品代碼（例如 tea01），組出圖片網址

假設 A 欄放商品代碼（例如 tea01），組出圖片網址

=IMAGE("https://example.com/products/" & A2 & ".jpg", 1)

圖片網址要公開看得到，需要登入才能看的圖，儲存格裡不會顯示。四種模式的差別，以及選單「插入」>「圖片」>「將圖片插入儲存格內」和 IMAGE 怎麼選，看 IMAGE 函式與儲存格內圖片。

SPARKLINE

一格裡畫一張迷你圖，放在每一列後面看趨勢特別好用。資料要是依時間排列的一列（或一欄）數字，下面兩個例子假設 B2:G2 是某個品項 1 到 6 月的杯數。上面茶飲表的 B2:B6 是五個不同品項，不是時間序列，畫出來的線沒有意義。

Google 試算表公式B2:G2（六個月的杯數）的折線迷你圖

B2:G2（六個月的杯數）的折線迷你圖

=SPARKLINE(B2:G2)

選項用 {"名稱","值"; "名稱","值"} 的格式，不同選項之間用分號隔開。圖表類型 "charttype" 可選 "line"、"column"、"bar"、"winloss"：

Google 試算表公式柱狀圖迷你圖，指定顏色

柱狀圖迷你圖，指定顏色

=SPARKLINE(B2:G2, {"charttype","column"; "color","#5B7A52"})

列高拉高一點，圖才看得清楚。勝敗圖、進度條和選項的完整整理在 SPARKLINE 迷你圖。

用 AI 處理一欄文字

AI

AI 函式可以請 Gemini 在儲存格裡產生文字、摘要或分類。

Google 試算表公式假設 A2 是一則客戶留言，依內容分類

假設 A2 是一則客戶留言，依內容分類

=AI("把這則留言分成：詢問、抱怨、讚美", A2)

這個函式屬於 Google Workspace Experiments 的功能，要看你的 Workspace 方案和管理員有沒有開放，不是每個帳號都用得到。公式要按「Generate and Insert」才會產生結果，一次最多處理 350 格，也不支援巢狀使用。寫提示詞的技巧在用 AI 幫你寫公式，拿它整理凌亂資料的例子在讓 AI 幫你清理資料。

Excel 有沒有對應

不少人從 Excel 轉過來會想知道哪些觀念可以直接帶過來。整理如下，版本以 Microsoft 官方說明為準：

Google 試算表	Excel 對應
`QUERY`	沒有同名函式
`IMPORTRANGE`	沒有同名函式
`IMPORTHTML`、`IMPORTXML`、`IMPORTDATA`、`IMPORTFEED`	沒有同名函式
`ARRAYFORMULA`	函式本身沒有。Excel 的動態陣列公式會自動展開，不用另外包（動態陣列在 Excel 365、2021 起多數有）
`SPLIT`	`TEXTSPLIT`（Excel 365、2024 有，2021 沒有）
`REGEXMATCH`、`REGEXEXTRACT`、`REGEXREPLACE`	`REGEXTEST`、`REGEXEXTRACT`、`REGEXREPLACE`（僅 Microsoft 365）。Excel 用 PCRE2，Sheets 用 RE2，語法細節有差
`GOOGLEFINANCE`	`STOCKHISTORY` 與股票資料類型（付費 Microsoft 365）
`GOOGLETRANSLATE`、`DETECTLANGUAGE`	`TRANSLATE`、`DETECTLANGUAGE`（Microsoft 365）
`IMAGE`	`IMAGE`（Microsoft 365），參數排列不同
`SPARKLINE`	「走勢圖」，是功能而不是函式
`AI`	`COPILOT`（受限釋出）

所以真正 Excel 沒有同名函式的，是 QUERY、IMPORTRANGE 和 IMPORT 系列，加上 ARRAYFORMULA 這個函式本身。

要先記得的事

這幾個函式大多要連網，資料也都不在檔案裡，所以：

IMPORT 系列、GOOGLEFINANCE、GOOGLETRANSLATE 都是開檔後才去抓資料，來源慢或暫時連不上，你這邊就會先看到載入中或錯誤。
對方網站改版、來源檔案刪掉或權限收回，公式會直接壞掉。
不需要即時更新的結果，貼成值再用。

先挑最貼近你工作的一個試試，我建議從 IMPORTRANGE 或 QUERY 開始。

我是喜特先生，Mr. Sheet，我們下個教學見！

Google 試算表常用進階函式導覽：QUERY、IMPORTRANGE、GOOGLEFINANCE、SPARKLINE 怎麼選，Excel 有沒有