Skip to main content

Kutools for Office — 一套工具,五種功能。完成更多工作。

Excel OFFSET 函數

Author Siluvia Last modified

Excel 的 OFFSET 函數返回一個相對於特定單元格偏移指定行數和列數的單元格或單元格範圍的引用。

showing the usage of OFFSET function

語法

=OFFSET (參考, 行數, 列數, [高度], [寬度])


參數

參考必填):您將設置為起始點的單個單元格或一組相鄰單元格。
行數必填):從起始點向上(負數)或向下(正數)移動的行數。
列數必填):從起始點向左(負數)或向右(正數)移動的列數。
高度可選):您希望返回的行數。高度必須是正數。
寬度可選):您希望返回的列數。寬度必須是正數。


返回值

OFFSET 函數將返回一個基於給定起始點偏移的單元格引用。


函數注意事項

1. 當引用指向不連續的單元格範圍時,將返回 #VALUE! 錯誤值。
2. 當行和列的偏移引用超出工作表邊界時,將返回 #REF! 錯誤值。


示例

示例 1:OFFSET 函數的基本用法

使用以下公式返回單個單元格的引用:
=OFFSET(B2,3,1)

在此情況下,B2 是起始點,數字 3 和 1 表示從 B2 向下移動 3 行並向右移動 1 列,最終返回 C5 中的值。請參見截圖:

showing the usage of OFFSET function

使用以下公式返回單元格範圍的引用:
=OFFSET(B2,3,1,2,2)

在此情況下,您將獲得位於 B2 下方 3 行、右側 1 列的 2 x 2 範圍的結果。

showing the usage of OFFSET function

注意:當您僅選擇一個單元格來應用 OFFSET 函數以返回單元格範圍時,將出現 #VALUE! 錯誤。您需要選擇一個 2 x 2 的範圍(例如 4 個空白單元格),輸入公式並按下 Ctrl + Shift + Enter 鍵以獲取結果。

示例 2:使用 OFFSET 函數求單元格範圍的總和

正如我們上面提到的,如果您嘗試在單個單元格中單獨使用 OFFSET 函數,例如 =OFFSET(B2,3,1,2,2),它將返回 #VALUE! 錯誤。但是,如果將 SUM 和 OFFSET 函數結合使用,如下截圖所示,它將直接返回範圍 C5:D6 中的值的總和。

1. 選擇一個空白單元格,將以下公式複製到其中並按下 Enter 鍵以獲取結果。
=SUM(OFFSET(B2,3,1,2,2)))

showing the usage of OFFSET function
示例 3:根據特定條件對列求和

如下截圖所示,如何獲取從星期日到星期二的芒果總銷售額?請按照以下步驟操作。

showing the usage of OFFSET function

1. 選擇一個空白單元格,將以下公式複製到其中並按下 Enter 鍵以獲取結果。
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

showing the usage of OFFSET function

注意

1. 在上述公式中,MATCH(G4,B3:B8) 用於查找芒果並返回其在範圍 B3:B8 中的位置。讓我們看看,芒果位於 B2 下方 5 行,因此返回數字 5;
2. 由於 MATCH 的結果是 5,最後一個逗號代表 0,此時 OFFSET 函數顯示為 OFFSET(C2:E2,5,0),這意味著偏移從範圍 C2:E2 開始,向下移動 5 行並向右移動 0 列以獲取範圍 C7:E7 的引用;
3. 然後 SUM 函數顯示為 SUM(C7:E7),最終返回範圍 C7:E7 中值的總和。


更多示例

如何在 Excel 中每隔 n 行求和?

如何在 Excel 中創建動態命名範圍?

如何在 Excel 中每五行或第 n 行返回單元格值?


最強大的辦公生產力工具

Kutools for Excel - 幫助您脫穎而出

🤖 KUTOOLS AI 助手:基於智能執行生成代碼創建自訂公式分析數據並生成圖表調用 Kutools 函數等進行數據分析革命
熱門功能查找、標記或識別重複值  |  刪除空行  |  合併列或儲存格而不丟失數據  |  無需公式即可四捨五入 ...
超級 VLookup多條件  |  多值  |  跨多工作表  |  模糊查找...
高級下拉列表簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  比較列以 選擇相同和不同的儲存格 ...
特色功能網格聚焦  |  設計檢視  |  增強編輯欄  |  工作簿與工作表管理器 | 資源庫(自動文字)  |  日期提取器  |  合併資料  |  加密/解密儲存格  |  按列表發送電子郵件  |  超級篩選  |  特殊篩選(篩選粗體/斜體/刪除線...) ...
前 15 大工具集12 文本工具添加文本刪除特定字符 ...)  |  50+ 圖表 類型甘特圖 ...)  |  40+ 個實用公式基於生日計算年齡 ...)  |  19 插入工具插入QR碼從路徑插入圖片 ...)  |  12 轉換工具金額轉大寫匯率轉換 ...)  |  7 個合併與分割工具高級合併行拆分Excel儲存格 ...)  |  ... 及更多
使用您偏好的語言來操作 Kutools - 支持英文、西班牙文、德文、法文、中文及其他 40 多種語言!

Kutools for Excel 擁有超過 300 種功能,確保您需要的功能只需輕點一下即可實現...


Office Tab - 在 Microsoft Office(包括 Excel)中啟用標籤式閱讀和編輯

  • 一秒鐘內在數十個打開的文件之間切換!
  • 每天減少數百次鼠標點擊,告別滑鼠手。
  • 當查看和編輯多個文檔時,您的效率提高了 50%。
  • 為 Office(包括 Excel)帶來高效的標籤,就像 Chrome、Edge 和 Firefox 一樣。