我有一份月報，每個月一張工作表，首頁想用一個儲存格切換月份。公式卻只能寫死成 ，要看九月就得回來改公式。
今天用的是 INDIRECT，它能把一段文字當成位址來讀。這篇也會帶到同一類的 OFFSET 和 ADDRESS，一個用位移找格子，一個把列號、欄號組成位址文字。$ 怎麼放的基本功在 參照的藝術，這篇不重講。
INDIRECT
INDIRECT 會把一段文字當成位址，去讀那個位址的內容。
語法
INDIRECT 的語法
=INDIRECT(位址文字, [是否為A1表示法])- 位址文字：像
"B3"或"B2:B4"這樣的文字，也可以是裡面存著這種文字的儲存格。 - 是否為
A1表示法：選填，預設TRUE。填FALSE的話，位址文字要照R1C1的寫法，我個人沒用過。
範例
先準備一張小表，A1:B5 是這樣：
| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 37 |
| 3 | 青茶 | 112 |
| 4 | 烏龍奶茶 | 8 |
| 5 | 鮮奶茶 | 64 |
D1 打上文字 B3，D2 打上數字 4。要讀 D1 寫的那一格，就是：
D1 裡面是文字 B3，所以讀 B3
=INDIRECT(D1)D1 的 B3 是一段文字，INDIRECT 把它當位址，所以你應該會得到 112。把 D1 改成 B4，同一條公式就變成 8。
位址也可以用 & 現場組。D2 是列號 4，要讀 B 欄那一列：
用 & 把欄名和列號接成位址文字
=INDIRECT("B"&D2)"B"&D2 先變成 "B4"，結果是 8。範圍也可以，從 B2 加總到第 D2 列：
位址文字是範圍，交給 SUM 加總
=SUM(INDIRECT("B2:B"&D2))"B2:B"&D2 是 "B2:B4"，37 + 112 + 8 等於 157。來看看這三條的結果：
| 公式 | 結果 |
|---|---|
| 112 | |
| 8 | |
| 157 |
跨工作表切換月份
回到開頭的月報。假設有「9月」「10月」兩張工作表，各自的 B2 放當月總杯數，9月是 412，10月是 387。首頁 B1 打工作表名稱，公式這樣寫：
B1 是工作表名稱，組成 '10月'!B2 之後再讀取
=INDIRECT("'"&B1&"'!B2")B1 是 10月 的時候，組出來的文字是 '10月'!B2，結果 387；改成 9月 就是 412。工作表名稱以數字開頭或有空格，位址裡要用單引號包起來，我乾脆一律都包。
B1 如果做成下拉選單，首頁就變成月份切換器，設定方式在 資料驗證實戰。要注意的是，如果想用 INDIRECT 做「選了縣市，第二個下拉選單才出現該縣市的區」這種相依下拉，在 Google 試算表的資料驗證裡行不通：把 這類字串範圍填進「下拉式選單 (來自某範圍)」的來源欄，會顯示「請輸入有效的範圍」；用 INDIRECT(A2) 去讀具名範圍也一樣不能用。來源欄要的是真正的儲存格範圍，不是公式的結果，所以要改用輔助欄先算好清單，下拉選單再讀取那個真正的範圍。這點跟 Excel 常見的 INDIRECT 做法不同，細節見 資料驗證進階。
OFFSET
OFFSET 不接文字，它從一個起點出發，往下、往右位移幾格，再取一格或一塊範圍。
語法
OFFSET 的語法
=OFFSET(起點, 列位移, 欄位移, [高度], [寬度])- 起點：從哪一格出發。
- 列位移：往下幾列，負數是往上。
- 欄位移：往右幾欄，負數是往左。
- 高度、寬度：選填，要取幾列、幾欄。沒填就是一格。
範例
同樣那張表，從 A1 往下 2 列、往右 1 欄：
A1 往下 2 列、往右 1 欄就是 B3
=OFFSET(A1,2,1)來看看結果：112。加上高度和寬度就能取一整塊，從 B1 往下 1 列，取 3 列 1 欄，也就是 B2:B4：
取 B2:B4 再加總
=SUM(OFFSET(B1,1,0,3,1))結果是 157。
只加總最後 3 筆
OFFSET 最常被拿來做「永遠算最新幾筆」。用 COUNTA 數出現在有幾筆資料，再往下位移：
B 欄有幾筆資料，就從第 N-2 筆開始取 3 筆
=SUM(OFFSET(B1,COUNTA(B2:B)-2,0,3,1))目前 COUNTA(B2:B) 是 4，起點 B1 往下位移 4 - 2 = 2 列，是 B3，取 3 列就是 B3:B5，112 + 8 + 64 等於 184。
在第 6 列新增一筆「青茶 15」，COUNTA(B2:B) 變 5，公式自動改成取 B4:B6，8 + 64 + 15 等於 87，公式不用改。
這個寫法有個前提，B 欄中間不能有空白格。只要中間空了一格，COUNTA 數出來的筆數就比實際列數少，位移會停在不對的地方，而且不會有錯誤訊息。
ADDRESS
ADDRESS 的方向跟 INDIRECT 相反，給它列號和欄號，它回傳一段位址文字。
語法
ADDRESS 的語法
=ADDRESS(列, 欄, [絕對或相對], [是否為A1表示法], [工作表名稱])- 列、欄：都是數字，欄也用數字，B 欄是 2。
- 絕對或相對：選填，1 是
$B$3（預設），2 是B$3，3 是$B3，4 是B3。 - 後兩個選填，一個決定用
A1還是R1C1，一個可以把工作表名稱接在前面。
範例
第 3 列第 2 欄：
| 公式 | 結果 |
|---|---|
$B$3 | |
B3 |
結果是文字，不是 B3 的內容。要讀內容，再交給 INDIRECT：
ADDRESS 組位址，INDIRECT 去讀
=INDIRECT(ADDRESS(3,2))結果是 112。這兩個本身就能直接寫成 =B3，我也不會這樣用。比較有用的場合是位置要靠計算決定，例如「杯數最多的是哪一格」：
MATCH 找出最大值在第幾筆，+1 補上標題列，欄固定 2
=ADDRESS(MATCH(MAX(B2:B5),B2:B5,0)+1,2,4)MAX(B2:B5) 是 112，MATCH 找到它是第 2 筆，加上標題列等於第 3 列，答案是 B3。
會踩到的地方
插入列，文字位址不會跟著搬。 =B3 是真的參照，在第 3 列上面插入一列，它會自動改成 =B4，還是讀到青茶。 的 "B3" 只是一段文字，Sheets 不會去改它，插入後它讀的是新插入的那一列。OFFSET 的起點是真的參照，會跟著搬，位移的數字就不會。
改工作表名稱，公式就斷。 上面月報的例子，名稱是從 B1 的文字組出來的。如果有人把「10月」改名成「十月」，B1 還寫 10月 的話，INDIRECT 找不到那張表，會回 #REF!。
公式裡看不出它在讀哪裡。 一般的 =B3，編輯時 Sheets 會把被引用的格子框起來；INDIRECT 引用的是文字。接手的人要自己推算，位址文字到底組出什麼。
快不快，這篇沒有結論。 INDIRECT、OFFSET 算不算揮發性函式，常有人討論。官方說明（support.google.com/docs/answer/58515）舉的例子只有 NOW、TODAY、RAND、RANDBETWEEN，沒提到它們，這篇不下結論。重算原理在 試算表為什麼越來越慢。
什麼時候改用別的
我的順序是這樣：能直接寫位址就直接寫；位置要靠數字決定，先用 INDEX；真的是工作表名稱要隨儲存格換，才用 INDIRECT。
INDEX 取第 3 列：
INDEX 取 B2:B5 的第 2 筆，結果同 B3
=INDEX(B2:B5,2)結果是 112。它寫的是真正的範圍，插入列時範圍會跟著調整，編輯時也看得到框。INDEX 的用法在 INDEX 與 MATCH 入門。
月報這種每個月一張表的做法，可以改成一張總表，加一欄「月份」，再用 SUMIFS 或 FILTER 取月份。這樣不用組工作表名稱，新增一個月也不用多開一張表。範圍會一直長大的寫法，接著看 資料會長大時，範圍要怎麼寫。
用 OFFSET 加總最後 2 筆
資料同上，A1:B5 共 4 筆。請用 OFFSET 寫一條公式，加總 B 欄最後 2 筆，而且之後新增資料時公式不用改。
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
這篇在技能樹的 Lv.5「系統設計與最佳實踐」，模組「動態參照的謹慎使用指南」（第 1／1 篇）。讀完這篇，路線上的下一個模組是「LAMBDA」。
回到技能樹相關文章
查看所有文章參照的藝術：公式往下拖時，哪些要動、哪些要釘住
從折扣率拖到一半變成 0 的小錯誤出發，用圖看相對、絕對、混合參照在複製時怎麼變，再談範圍的兩端、跨工作表與命名範圍，判斷 $ 該放在哪裡。
資料會長大時，範圍要怎麼寫：開放式範圍、整欄、表格與 FILTER
同一份銷量表新增三筆資料，對照固定範圍、開放式範圍 B2:B、整欄 B:B、表格參照與 FILTER 各自算出什麼、會在哪裡出錯，並說明 COUNTBLANK、COUNTA、總計格放哪裡這幾個常踩的地方。
動態資料設計：資料變多，公式不用跟著改
從每個月要把 A2:A100 改成 A2:A135 的硬編碼問題出發，對照寫死範圍與開放式範圍、把折扣率這類數字抽到設定儲存格，並說明什麼時候不需要這麼動態。
留言討論
共 0 則留言