取
在 Excel 中，OFFSET 是一個功能非常強大但常讓初學者感到困惑的函數。它不僅僅能做到簡單的儲存格位移，更強大的地方在於能夠建立「動態範圍」，這在製作自動更新的圖表或報表時尤其有用。
先前我們在製作「自訂圖表範圍」的教學中曾使用到它，有網友對這個函數特別感興趣，因此今天我們就來深入解析 OFFSET 的用法與常見問題。
範例檔案下載： offset範例.xlsx
OFFSET 函數的基本語法
首先，我們來看看 OFFSET 函數的完整結構：
=OFFSET(reference, rows, cols, [height], [width])
reference：起始儲存格，也就是計算位移的基準點。
rows：垂直位移的列數。正數代表向下移動，負數代表向上。
cols：水平位移的欄數。正數代表向右移動，負數代表向左。
[height]：（選用）回傳範圍的高度（列數）。
[width]：（選用）回傳範圍的寬度（欄數）。
核心觀念：
當只使用前三個參數 (reference, rows, cols) 時，OFFSET 主要功能是移動到指定的儲存格並回傳其值。
當加入了後兩個選用參數 (height, width) 時，OFFSET 的功能就昇華為回傳一個指定大小的範圍。
應用一：移動儲存格
這是 OFFSET 最基本的用法。假設我們想從 B2 儲存格開始，向下移動 1 列，水平不移動（0 欄），公式如下：
=OFFSET(B2, 1, 0)
這個公式會從 B2 開始，向下移動 1 列到達 B3，並回傳 B3 的值 2。
應用二：回傳動態範圍與 #VALUE! 錯誤解析
OFFSET 的精髓在於建立動態範圍。當我們指定了 height 或 width 參數，它回傳的就不再是單一值，而是一個儲存格範圍（陣列）。
為什麼會出現 #VALUE! 錯誤？
在範例檔案的 F2 儲存格中，我們輸入了：
=OFFSET(B2,,,4)
這個公式的意思是：
從 B2 開始。
垂直移動 0 列，水平移動 0 欄（因為 rows 和 cols 參數被省略，預設為 0）。
回傳一個高度為 4 列、寬度為 1 欄（預設）的範圍。
這個範圍實際上是 B2:B5，其內容是 {1; 2; 3; 4} 這樣一個陣列。然而，單一儲存格無法顯示一個多值的陣列，因此 Excel 就會顯示 #VALUE! 錯誤。
實用技巧：F9 偵錯
當你不確定公式某個部分的回傳結果時，可以在編輯模式下將該部分反白，然後按下 F9 鍵。Excel 會立即計算並顯示該部分的結果。看完後記得按 Esc 鍵取消，否則結果會被寫死在公式裡。
如何正確處理 OFFSET 回傳的範圍？
有兩種主要方式可以處理 OFFSET 回傳的範圍陣列：
1. 使用陣列公式（較少用）
如果你確實想在儲存格中顯示陣列，可以選取對應大小的範圍（例如 F2:F5），輸入公式後按下 Ctrl+Shift+Enter。這樣公式前後會自動加上大括號 {}，並將陣列結果依序填入選取的儲存格中。
2. 搭配其他函數使用（最常用）
這是最實用也最常見的方法。將 OFFSET 函數放在另一個可以處理「範圍」的函數中，例如 SUM、AVERAGE、COUNT 等。
例如，在 H2 儲存格中，我們想加總 B2:B5 這個動態範圍：
=SUM(OFFSET(B2,,,4))
這樣一來，OFFSET 會將 B2:B5 這個範圍傳遞給 SUM 函數，SUM 接收到範圍後進行加總，得到正確結果 28，完全避免了 #VALUE! 錯誤，也不需要使用 Ctrl+Shift+Enter。
再看一個例子，在 G4 儲存格中：
=SUM(OFFSET(B2,0,0,2,3))
OFFSET(B2,0,0,2,3) 會回傳一個從 B2 開始、高 2 列、寬 3 欄的範圍，也就是 B2:D3。
SUM 函數會將 B2:D3 範圍內的所有數字（1, 5, 9, 2, 6, 10）相加，得到 33。
重點整理
OFFSET 可用於移動儲存格或定義範圍。差別在於是否使用 [height] 和 [width] 參數。
當 OFFSET 用於定義範圍時，它回傳的是一個陣列。若直接將其放入單一儲存格，會因無法顯示多個值而出現 #VALUE! 錯誤。
要解決 #VALUE! 問題，最常見的方法是將 OFFSET 巢狀包裝在其他如 SUM, COUNT, AVERAGE 等能處理範圍的函數內。
善用 F9 鍵可以幫助你理解公式中 OFFSET 實際回傳的範圍或陣列內容，是偵錯的絕佳工具。
掌握了 OFFSET，你就能在 Excel 中建立更具彈性與自動化的報表了！
沒有留言:
張貼留言
注意：只有此網誌的成員可以留言。