Excel OFFSET – 透過 OFFSET 函數幫你取出指定間隔的資料

OFFSET 函數可以根據你指定的起點與偏移量，動態定位到某個儲存格或一個範圍，是 Excel 裡處理間隔取值、動態加總與移動平均的核心工具。

跟固定的儲存格參照（例如 A1 或 B5:D10）不同，OFFSET 回傳的位置是由公式計算出來的。你告訴它「從哪裡出發、往哪個方向走幾步、要走多大範圍」，它就把對應位置的值或範圍帶回來。這表示你可以把偏移量綁定到其他儲存格的值，讓參照位置跟著資料自動變動。

OFFSET 最常見的三種用途：第一，從間隔排列的清單裡自動抽出特定項目（例如每隔 2 列取一筆）；第二，搭配 SUM 或 AVERAGE 建立自動擴展的計算範圍；第三，與 MATCH 組合實現 VLOOKUP 做不到的向左查詢。這篇文章會逐一拆解這些用法，每一段都附上可以直接複製的公式。

OFFSET 語法與五個參數

OFFSET 的完整語法只有一行：

=OFFSET(reference, rows, cols, [height], [width])

五個參數的意義如下：

• reference（必要）：起點，可以是一個儲存格或一個連續範圍，例如 A1 或 B2:D10。必須指向實際存在的位置，否則會回傳 #VALUE! 錯誤。
• rows（必要）：列方向的偏移量。正數往下，負數往上，0 不動。
• cols（必要）：欄方向的偏移量。正數往右，負數往左，0 不動。
• height（選填）：回傳範圍的列數。省略時自動採用 reference 的高度。必須是正整數。
• width（選填）：回傳範圍的欄數。省略時自動採用 reference 的寬度。必須是正整數。

如果只填前三個參數，OFFSET 回傳單一儲存格。加上 height 和 width，就能回傳一個可變大小的範圍，這是 OFFSET 最大的特色，也是 INDEX 函數做不到的事（參見微軟官方 OFFSET 函數說明文件）。

你可以把 OFFSET 想像成一個「相對導航」工具。一般參照像是在講絕對地址（「台北市信義路五段 7 號」），OFFSET 則像是在講相對方向（「從這裡往南走兩條街、左轉第三棟」）。當你的資料結構會變動，相對定位比絕對定位更有彈性。

基礎用法：單一儲存格取值的三種情況

正數偏移：往下往右找資料

假設資料表從 A1 開始，你想取得往下 2 列、往右 3 欄的位置：

=OFFSET(A1, 2, 3)

以 A1 為起點，往下走到第 3 列，往右走到 D 欄，結果等同於直接參照 D3。跟直接寫 D3 的差別在於：OFFSET 裡的 2 和 3 可以替換成其他儲存格的值或其他函數的計算結果，讓參照位置動態變化。

負數偏移：往上往左找資料

=OFFSET(D5, -2, -1)

以 D5 為起點，往上 2 列到第 3 列，往左 1 欄到 C 欄，結果等同於 C3。這在你已經知道資料終點、需要往回推算相對位置時很好用，例如從合計欄回推各月份的明細。

不偏移：動態切換資料來源

當 rows 和 cols 都設為 0 時，OFFSET 回傳 reference 本身。看似沒用，但在需要動態切換參照來源的場景中很常見，例如透過一個控制儲存格決定 reference 指向哪個範圍，再搭配其他函數做進一步處理。

進階應用一：每隔 N 列自動取值

這是 OFFSET 最經典的場景。當你的資料清單裡，不同類型的項目交替排列在同一欄，你想把其中一類單獨抽出來。

實際範例：奇數列與偶數列分離

假設 A 欄的資料長這樣：

儲存格	內容
A2	1（編號）
A3	王小明（姓名）
A4	2（編號）
A5	李小華（姓名）
A6	3（編號）
A7	張大偉（姓名）

你想把「編號」抽出來放到 D 欄。在 D2 輸入：

=OFFSET(A$2, (ROW(A1)-1)*2, 0)

公式邏輯拆解：

• A$2：起點鎖定在第 2 列，下拉時不會跑掉
• ROW(A1)：取得當前列號，第一筆是 1
• (ROW(A1)-1)*2：第一筆算出 (1-1)*2 = 0，OFFSET 不偏移，直接回傳 A2 的值「1」

下拉到 D3 時，ROW(A1) 變成 ROW(A2) = 2，偏移量變成 (2-1)*2 = 2，OFFSET 從 A2 往下 2 列到 A4，回傳「2」。以此類推，不管資料有幾百列都能一次搞定。

如果要抽出「姓名」，在 E2 輸入 =OFFSET(B$2, (ROW(B1)-1)*2, 0) 即可。

彈性調整間隔數

公式中的 *2 就是間隔數。每隔 3 列取一次就改成 3，每隔 4 列就改成 4。更好的做法是把間隔數放在獨立儲存格（例如 G1），公式改成：

=OFFSET(A$2, (ROW(A1)-1)*$G$1, 0)

這樣只要改 G1 的值，所有公式的取值間隔就同步更新。做報表篩選或資料分析的時候，這種彈性設計可以省下大量重複修改公式的時間。如果你常常需要把不同格式的文件轉成 Excel 來做這類資料處理，PDNob PDF Online 可以把 PDF 轉成可編輯格式，處理完的報表再用 AvePDF 把 Excel 轉回 PDF 方便分享。

進階應用二：搭配 SUM 與 AVERAGE 計算動態範圍

OFFSET 的 height 和 width 參數讓它能回傳一個可變大小的範圍。把這個動態範圍交給 SUM 或 AVERAGE，就能做到「資料新增多少，計算範圍就自動擴展多少」。

動態加總：自動計算整欄非空資料的總和

=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))

拆解：

• A1：起點
• 0, 0：不偏移，從 A1 開始
• COUNTA(A:A)：計算 A 欄非空儲存格數量，作為 height
• 1：width 為 1，只取一欄

不管你在 A 欄新增多少筆資料，SUM 的範圍都會自動包含所有非空儲存格。做月報或季報時特別好用，不需要每個月手動修改 SUM 的範圍。如果你需要把這類報表放在網站上供團隊查閱，可以參考我們整理的 WordPress 虛擬主機推薦懶人包，選擇適合的主機來架設內部系統。

移動平均：計算最近 N 筆資料的平均值

=AVERAGE(OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1))

這個公式從 A 欄最後一筆資料往上數 5 筆，計算這 5 筆的平均值。新增第 6 筆資料時，計算範圍自動變成第 2 到第 6 筆，永遠保持「最近 5 筆」。把 5 改成任何數字就能調整移動平均的期數。這在財務分析與品質管理中是很常見的需求。

進階應用三：OFFSET 搭配 MATCH 實現雙向查詢

VLOOKUP 最大的限制是只能向右查詢。如果你的目標值在查詢欄的左邊，VLOOKUP 就無能為力。OFFSET 加上 MATCH 可以突破這個限制。

向右查詢

=OFFSET(A1, MATCH(“王小明”, B:B, 0)-1, 3)

MATCH 找到「王小明」在 B 欄的位置（假設是第 5 列），減 1 是因為 OFFSET 的偏移從 0 起算而 MATCH 從 1 起算，最後的 3 表示往右偏移 3 欄。整個效果等同於 VLOOKUP 的向右查詢，但彈性更大。

向左查詢

=OFFSET(D1, MATCH(“王小明”, C:C, 0)-1, -2)

在 C 欄找到「王小明」後，從 D 欄往左偏移 2 欄到 A 欄，取得對應的編號。這是 VLOOKUP 完全做不到的操作。在設計資料表結構時，你不必再為了遷就 VLOOKUP 而把查詢欄硬放在最左邊。如果你對其他 Excel 技巧有興趣，Udemy 免費課程懶人包 裡有不少優質的試算表課程，ChatGPT 也能幫你解釋任何看不懂的公式邏輯。

OFFSET 與 INDEX 的選擇建議

很多使用者在學會 OFFSET 之後會發現 INDEX 似乎也能做類似的事。關鍵差異只有一個：OFFSET 能回傳動態大小的範圍，INDEX 不能。

需求場景	推薦函數	原因
需要動態範圍（height/width）	OFFSET	INDEX 無法回傳變動大小的範圍
單純定位取值	INDEX+MATCH	效能較好，非 volatile
搭配 SUM/AVERAGE 計算動態區間	OFFSET	需要動態範圍參數
大型試算表（效能敏感）	INDEX+MATCH	避免 volatile 函數拖慢速度

OFFSET 屬於 volatile（易變）函數，只要試算表中有任何儲存格的值被修改，所有包含 OFFSET 的公式都會重新計算。在幾百列的小型檔案裡幾乎感覺不到，但在幾萬列、幾百個公式的大型檔案裡會明顯拖慢速度。如果你的需求只是「定位取值」而不需要動態範圍，INDEX+MATCH 在效能上是更好的選擇。

常見錯誤與除錯方法

#REF! 錯誤：偏移超出邊界

OFFSET 最常見的錯誤。當偏移後的位置超出工作表範圍（例如以 A1 為起點但 rows 為負數，或以 A 欄為起點但 cols 為負數），Excel 就回傳 #REF!。解決方法：逐一檢查 rows 和 cols 的計算結果，確認最終位置在有效範圍內。如果 rows 是負數，確保「reference 的列號 + rows >= 1」；如果 cols 是負數，確保「reference 的欄號 + cols >= 1」。

#VALUE! 錯誤：reference 無效

當 reference 指向的不是有效的儲存格或範圍時出現。常見原因：reference 引用了已被刪除的範圍、reference 是一個常數而非儲存格參照、或 reference 引用了另一個出錯的公式結果。

Height 或 Width 為零或負數

這兩個參數必須是正整數。如果你用其他函數的結果作為 height 或 width，可能在邊界條件下產生 0 或負數。用 MAX 函數確保最小值為 1：

=OFFSET(A1, 0, 0, MAX(1, COUNTA(A:A)), 1)

兩個除錯技巧

第一，善用 Excel 內建的「評估值公式」功能（在公式標籤頁裡），它會一步一步展示公式的計算過程，幫你快速找出哪個部分算錯了。第二，用 IFERROR 包裹整個 OFFSET 公式：

=IFERROR(OFFSET(A$2, (ROW(A1)-1)*2, 0), “”)

這樣出錯時儲存格會顯示空白而非錯誤代碼。建議先確認公式邏輯正確再使用 IFERROR，否則會把所有錯誤都藏起來，反而難以除錯。

OFFSET 在 Google Sheets 中的差異

OFFSET 函數在 Google Sheets 中完全支援，語法也一模一樣，你在 Excel 中寫的公式可以直接貼過去用。主要的差異在效能面：Google Sheets 採用雲端運算架構，volatile 函數的影響相對沒那麼明顯，但如果資料量到數萬列以上，過多 OFFSET 還是會影響操作流暢度。

Google Sheets 有一些特有函數可以在特定場景下取代 OFFSET。FILTER 函數可以根據條件篩選資料，QUERY 函數提供類似 SQL 的查詢能力。如果你的需求是「根據條件取得資料」而非「根據位置偏移」，這些函數可能比 OFFSET 更適合。

跨平台移轉時要注意三件事：OFFSET 搭配 IMPORTRANGE 等外部資料來源時行為可能不同；Google Sheets 的陣列公式處理方式跟 Excel 不一樣，可能需要加上 ARRAYFORMULA；部分參數的預設行為可能有細微差異。在團隊協作上，Dropbox Transfer 支援最高 100 GB 的檔案快傳，奶牛快傳 則提供 4 GB 免費空間的快速分享。

OFFSET 適合誰、不適合誰

適合使用 OFFSET 的情境：你需要動態範圍（例如自動擴展的下拉選單、移動平均、動態圖表資料來源），或者你需要根據偏移量從間隔排列的資料中自動取值。如果你的檔案規模在數千列以內，OFFSET 的 volatile 特性不會造成明顯影響。

不適合使用 OFFSET 的情境：你的試算表資料量很大（數萬列以上）且公式很多，效能已經是首要考量；或者你只需要靜態的定位取值，不需要動態範圍，這時候 INDEX+MATCH 是更輕量的選擇。

三個下一步

1. 在一個空白工作表中實際輸入本文的基礎範例（=OFFSET(A1, 2, 3)），確認你理解偏移方向的對應關係。如果結果跟預期不同，用「評估值公式」功能逐步檢查。

2. 找一份你目前正在處理的間隔資料清單，套用「每隔 N 列取值」的公式，把間隔數放在獨立儲存格中方便調整。確認公式下拉後每個位置的回傳值都正確。

3. 如果你需要動態加總或移動平均，試著把本文的 SUM+OFFSET 和 AVERAGE+OFFSET 公式套用到你的報表中。先用小範圍測試，確認結果無誤後再擴展到完整資料範圍。

OFFSET 函數常見問題 FAQ

OFFSET 會不會讓 Excel 變慢？

會。OFFSET 是 volatile 函數，任何儲存格的值變動都會觸發所有包含 OFFSET 的公式重新計算。小檔案影響可忽略，大檔案（幾萬列、幾百個公式）會明顯變慢。如果不需要動態範圍，改用 INDEX+MATCH。

OFFSET 可以跨工作表參照嗎？

可以。把 reference 指向其他工作表即可，例如 =OFFSET(Sheet2!A1, 2, 3)。也可以跨活頁簿參照，只要目標活頁簿是開啟狀態。跨工作表的參照會讓公式更複雜，建議搭配「評估值公式」功能除錯。

OFFSET 和 INDIRECT 有什麼不同？

OFFSET 基於「相對位置偏移」來定位，需要起點和偏移量。INDIRECT 則是把文字字串轉成儲存格參照，例如 =INDIRECT("A"&B1) 把 B1 的值跟 “A” 組合成位址。兩者都是 volatile 函數，選擇的重點在於你的資料結構適合用哪種邏輯來描述。

OFFSET 可以用在資料驗證的下拉清單嗎？

可以，這是 OFFSET 很受歡迎的應用之一。在資料驗證的來源欄位輸入類似 =OFFSET(A1,0,0,COUNTA(A:A),1) 的公式，當 A 欄新增項目時，下拉清單會自動包含新的選項，不需要手動修改驗證範圍。搭配命名範圍使用效果更好。

如何用 OFFSET 建立動態圖表的資料來源？

在「公式」標籤頁的「名稱管理員」中新增一個名稱，參照到輸入 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)，然後在圖表的資料來源中引用這個名稱。資料新增時圖表會自動更新，適合需要定期更新的儀表板。如果你正在架設資料展示網站，Bluehost 是 WordPress 官方推薦的主機之一，Kinsta 則主打高效能代管，適合流量較大的站點。