Excel 教學：OFFSET 函數
1. OFFSET 函數的用途
OFFSET 函數用來返回一個範圍，該範圍是基於給定的起始儲存格參照，並根據指定的行數和列數進行偏移。OFFSET 可以動態地返回範圍或儲存格，常用於創建動態範圍、圖表、數據分析等。
2. OFFSET 函數的語法
OFFSET(reference, rows, cols, [height], [width])
| 參數 | 說明 |
|---|---|
reference | 必填，起始儲存格的參照。 |
rows | 必填，指定偏移的行數。正值表示向下偏移，負值表示向上偏移。 |
cols | 必填，指定偏移的列數。正值表示向右偏移，負值表示向左偏移。 |
[height] | 選填，返回範圍的高度（行數）。如果省略，則默認為 1。 |
[width] | 選填，返回範圍的寬度（列數）。如果省略，則默認為 1。 |
3. OFFSET 函數範例
? 範例 1：基於起始儲存格偏移單一儲存格
假設 A1 儲存格的值為 10，你希望返回從 A1 向下偏移 2 行，向右偏移 1 列的儲存格（即 B3）。
? 使用 OFFSET 函數：
=OFFSET(A1, 2, 1)
? 結果：返回儲存格 B3 的值。
這裡，OFFSET(A1, 2, 1) 表示從 A1 向下偏移 2 行，向右偏移 1 列，結果就是 B3 儲存格的內容。
? 範例 2：返回一個範圍
假設 A1 儲存格開始，並且你希望返回 3 行 2 列的範圍。
? 使用 OFFSET 函數：
=OFFSET(A1, 0, 0, 3, 2)
? 結果：返回範圍 A1:B3。
這裡，OFFSET(A1, 0, 0, 3, 2) 表示從 A1 開始，返回高度為 3 行、寬度為 2 列的範圍，結果就是 A1:B3 範圍。
? 範例 3：動態範圍
假設你有一個動態資料範圍，並且想根據資料數量自動調整範圍高度。
假設 A1 開始的資料範圍，每一列包含一個數字，資料數量會變動，你希望根據 A1 開始的資料範圍高度來自動調整範圍。
? 使用 OFFSET 與 COUNTA 函數結合來創建動態範圍：
=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))
? 結果：返回從 A1 開始，到有資料為止的範圍的總和。
這裡，COUNTA(A:A) 用來計算 A 列的資料數量，而 OFFSET 根據資料數量動態選擇範圍，並將範圍內的值求和。
? 範例 4：使用 OFFSET 創建動態命名範圍
假設你的資料從 A1 開始，並且資料隨時會增加，你希望根據資料量創建動態命名範圍。
? 創建動態命名範圍：
- 點擊
公式->名稱管理員，選擇新建。 - 在
名稱欄中輸入範圍名稱，例如DynamicRange。 - 在
引用位置中輸入公式：
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
? 這樣，每當資料增加或減少時，DynamicRange 會自動調整範圍。
4. OFFSET 函數的應用
✅ 動態範圍：根據資料量動態調整範圍，避免手動更新範圍。
✅ 創建圖表範圍：使用 OFFSET 來動態生成圖表數據範圍。
✅ 與其他函數結合：與 SUM、AVERAGE、COUNTA 等函數結合使用，對動態範圍進行計算。
5. OFFSET 函數的限制
| 限制 | 說明 |
|---|---|
| 不支持空白儲存格 | 如果範圍中有空白儲存格，OFFSET 仍然會將空白儲存格包含在返回的範圍中。 |
| 性能問題 | 如果使用 OFFSET 創建的動態範圍非常大，可能會影響工作表性能，特別是在包含大量數據時。 |
| 不可跨工作表 | OFFSET 只能引用同一工作表中的範圍，無法跨工作表進行偏移操作。 |
6. 總結
OFFSET函數是非常強大的工具，能夠基於指定的起始儲存格參照，返回動態的範圍或儲存格。- 它非常適合用來處理動態資料範圍，並且與其他函數（如
SUM,AVERAGE,INDEX等）結合使用時，能夠達到強大的功能。 - 在使用時要注意其對性能的影響，特別是在大量數據和複雜範圍運算的情況下。