用過 Excel 表格的人，轉到 Google 試算表第一個會手癢的就是在表格裡寫 。這個寫法在 Sheets 目前不支援，官方說明寫得很直接：#This Row 還不支援。
這篇整理表格參照會踩到的幾個坑。表格怎麼建立在入門篇，實際用在庫存清單在實戰篇，這邊假設你已經有一個名為「表_1」的表格。
先看資料
沿用實戰篇的庫存表，A1:E9，標題列在第 1 列，資料 8 列：
| 品項 | 類別 | 庫存 | 單價 | 狀態 |
|---|---|---|---|---|
| 紅茶茶葉 | 茶葉 | 37 | 280 | 充足 |
| 綠茶茶葉 | 茶葉 | 12 | 260 | 充足 |
| 珍珠 | 配料 | 8 | 95 | 待補貨 |
| 椰果 | 配料 | 21 | 70 | 充足 |
| 杯子 | 包材 | 112 | 3 | 充足 |
| 吸管 | 包材 | 64 | 2 | 充足 |
| 封口膜 | 包材 | 5 | 410 | 待補貨 |
| 鮮奶 | 配料 | 9 | 62 | 已叫貨 |
語法一覽
依官方說明中心「Use table references in Google Sheets」，支援的寫法有：
| 寫法 | 取到的範圍 |
|---|---|
表_1 或 表_1[#DATA] | 只有資料，沒有標題和頁尾 |
表_1[庫存] | 「庫存」欄的資料 |
表_1[#ALL] | 標題、資料、頁尾全部 |
表_1[#HEADERS] | 標題列 |
表_1[#TOTALS] | 頁尾 |
表_1[[#HEADERS],[#DATA]] | 標題加資料 |
表_1[[#ALL],[庫存]:[單價]] | 多欄，欄名之間用冒號 |
另外還有 表_1[[#DATA],[#TOTALS]]（資料加頁尾）和 表_1[[#ALL],[庫存]]（單欄含標題頁尾）。#HEADERS、#TOTALS 這些是 2024 年 11 月 19 日起逐步開放的，來源是 Workspace Updates。
#ALL 和預設寫法差一列
這是我覺得最容易搞混的。表_1[品項] 只有資料，表_1[[#ALL],[品項]] 連標題一起。用 COUNTA 就看得出來：
只算資料的品項數
=COUNTA(表_1[品項])結果是 8。換成含標題的：
含標題列的品項數
=COUNTA(表_1[[#ALL],[品項]])結果是 9，多出來的 1 是標題「品項」。#HEADERS 則是 ，得到 5，表示 5 個欄名。
要加總多欄就用冒號：
庫存與單價兩欄的數字合計
=SUM(表_1[[#ALL],[庫存]:[單價]])庫存加起來是 268，單價加起來是 1182，合計 1450。標題是文字，SUM 會略過，所以含不含標題不影響這題。
只想加庫存的話 就是 268。
表格裡寫不出 [@欄名]
Excel 在表格裡算每一列的庫存金額，會寫：
[@欄名] 是 [#This Row],[欄名] 的簡寫，而 Sheets 的 #This Row 官方說明明講不支援，所以這個寫法照抄會出問題。
Sheets 的做法有兩種。一種是退回一般的儲存格參照，在 F2 寫：
另一種是在表格外面用一條公式整欄算出來：
一條公式算出每個品項的庫存金額
=ARRAYFORMULA(表_1[庫存]*表_1[單價])放在 H2，會往下展開 8 個值：10360、3120、760、1470、336、128、2050、558。官方說明有提到可以用 ARRAYFORMULA 來取整欄或整個表格。這種寫法在表格長大時，結果也會多出一列，這就帶出下一個坑。
溢出會被擋
上面那條公式在 H2 展開成 H2:H9。如果 H10 剛好有一個備註，目前 8 列剛好放得下。
表格新增一列之後，結果需要 H2:H10，卻碰到 H10 的備註，這時候 H2 會變成 #REF!，提示是「系統未展開陣列結果，以免覆蓋 H10 中的資料。」。
Sheets 的溢出被擋是 #REF!，不是 Excel 的 #SPILL!。我自己的習慣是，會往下長的表格參照結果，下面整欄都留空，或是乾脆放在表格右邊的獨立欄位。
表格新增資料很安靜，公式昨天還好好的，今天因為多一列就被擋，不會有人通知你。有溢出公式的工作表，新增資料之後要看一眼結果有沒有變成 #REF!。
圖表、條件式格式、樞紐分析表不能用
官方說明列了一個限制：在這幾個功能裡選範圍時，目前不能用表格參照。
- 條件式格式
- 圖表
- 樞紐分析表
也就是說，這三個地方的「資料範圍」要用一般的 A1:E9，不能填 表_1。表格新增一列之後這些範圍會不會跟著延伸，官方說明沒有寫，我個人是新增資料後就點進去確認一遍。
改名字會怎麼樣
這是大家最想知道的，但官方說明沒有講改名之後公式會怎樣。改表格名稱：表格選單的「重新命名表格」。
以下沿用實戰篇新增糖漿、加上「金額」欄之後的表格（六欄、九筆資料，配料的金額加總是 4020）。把「金額」欄改成「庫存金額」後，既有 SUMIFS 的欄參照跟著更新；再把表格「表_1」改成「庫存測試」，同一條公式仍回傳 4020。這是結構化參照的改名，不代表公式字串裡手寫的文字也會自動改；改完仍點進相依公式核對。
改名時要遵守官方列的規則，表格名稱不能：
- 是 TRUE 或 FALSE。
- 長得像
A1或R1C1的儲存格位址。 - 以數字開頭。
- 超過 255 個字元。
- 含特殊字元，只有底線可以。
名稱裡的空格在公式裡會變成底線，例如表格叫「Sales Tracker」，欄叫「Sales Amount」，公式是 Sales_Tracker[Sales Amount]，欄名的空格維持原樣。
表格搬位置，QUERY 用相對欄序
庫存實戰篇的查詢改用 Col2（類別）與 Col6（金額）。把新增糖漿、加上金額欄之後的完整六欄表格從 A1 剪下，貼到 S12：結構化 SUMIFS 仍為 4020，Col2/Col6 查詢仍得到茶葉 13480、配料 4020、包材 2514；寫死 B/F 的查詢則出現 #VALUE!。因此，表格參照不等於查詢字串裡的欄字母也會跟著搬家。Col 是輸入表內的順序，重新排列欄位仍須調整。
另以一份五筆資料、已開啟頁尾的表格為例：#DATA 有 5 列、#ALL 有 7 列（含標題及頁尾）；在表格裡插入再刪除一列時，結構化加總由 530 變 610，再回到 530。
跨檔案用 IMPORTRANGE
官方範例裡有一個，把表格參照放進 IMPORTRANGE 的第二個參數，當字串：
IMPORTRANGE 搭配表格參照（官方示意網址）
=IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/abcd123abcd123",
"DeptSales[Sales Amount]"
)網址是官方的示意字串。授權怎麼處理，之前 IMPORTRANGE 實戰講過。
和 Excel 結構化參照的差異
這不是完整比對，只列有官方說明依據的：
| 項目 | Excel | Google 試算表 |
|---|---|---|
| 這一列 | [@庫存]、[#This Row] | 不支援 |
| 標題和頁尾 | #Headers、#Totals | #HEADERS、#TOTALS |
| 預設表格名稱 | Table1 | 繁體中文介面是「表_1」 |
| 轉換為表格的快捷鍵 | Ctrl+T | 選單項目旁會顯示（Mac 是 ⌘+選項+T） |
在 Sheets 裡用 Chrome 單按 Ctrl+T 會開新分頁。
官方沒寫的限制
這幾件事，我只能說官方說明沒有交代，沒有證據不寫結論：
- 表格裡能不能有合併儲存格。我個人不會在表格裡合併。
- 表格裡的欄位放溢出公式，會不會和新增列打架。
- 能建立多少表格、兩個表格可以不可以相鄰。
下次新增一列之後，把工作表上的公式掃一眼，是目前最保險的做法。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章留言討論
共 0 則留言