在 Excel 中，OffSet函數用于返回以某個單元格為基準且距離該單元格指定行數和列數區域的引用。引用的單元格可以在基準單元格的上下或左右，這取絕于OffSet函數的參數 Rows 和 Cols 的正負；引用單元格的區域大小取絕于參數 Height 和 Width 數值。

OffSet函數常與 Sum、Match、CountIf、If、Or、Row 等函數組合使用，例如 Sum + OffSet + Match + CountA函數組合實現動態求和、OffSet + Int + Row函數組合實現把一個重復多個、CountIf + OffSet + Match + CountIf函數組合實現分段統計。

一、Excel OffSet函數語法

1、表達示：OFFSET(Reference, Rows, Cols, [Height], [Width])

中文表達示：OFFSET(引用單元格, 行數, 列數, [返回引用高度], [返回引用寬度])

2、說明：

A、Reference 必須是對單元格或相鄰單元格區域的引用，否則會返回值錯誤 #VALUE!；返回單元格以 Reference 為基準。

B、Rows 指距 Reference 左上角向上或向下的行數。例如 Reference 為 B3，若 Row 為 2，則是指 B3 下 2 行；若 Row 為 -2，則是指B3 上 2 行。即 Row 為正數時，是指基準單元格的下面；Row 為負數時，是指基準單元格的上面。

C、Cols 指距 Reference 左上角從左到右的列數。例如 Reference 為 B3，若 Cols 為 1，是指 B3 右邊 1 列（即 C 列）；若 Cols -1，是指 B3 左邊 1 列（即 A 列）。即 Cols 為正，指基準單元格右邊；Cols 為負，指基準單元格左邊。

D、Height 和 Width 必須為正數；如果省略 Height 或 Width，則默認它們與 Reference 相同。

E、如果 Rows 和 Cols 超出工作表邊緣，OffSet函數將返回引用錯誤值 #REF!。

二、Excel OffSet函數的使用方法及實例

（一）Rows 或 Cols 為 0 的實例

1、雙擊 A10 單元格，把公式 =OFFSET(A1,3,) 復制到 A10，按回車，返回“黃子辛”；再次雙擊 A10，把公式改為 =OFFSET(A1,,2)，按回車，返回“職務”；操作過程步驟，如圖1所示：

圖1

2、公式說明：

A、公式 =OFFSET(A1,3,) 中，A1 為基準引用單元格，3 為距離 A1 的行數，省略了 Cols ，默認取 0；省略了 Height 和 Width，默認它們與 A1 的高度和寬度相同。公式的意思是：返回 A1 下 3 行 0 列的單元格引用，即返回 A4 中的“黃子辛”。

B、公式 =OFFSET(A1,,2) 省略了 Rows，默認取 0；省略的 Height 和 Width 取值與上面相同；公式的意思是：返回 A1 右邊 0 行 2 列的單元格引用 C1，即返回“職務”。

（二）返回距基準單元格左上角指定行數的上下引用實例

1、雙擊 A9 單元格，把公式 =OFFSET(A2:B3,1,1,1,1) 復制到 A9，按回車，返回“瓜類”；再次雙擊 A9 單元格，把 OffSet 第二個參數 1 改為 -1，按回車，返回“分類”；操作過程步驟，如圖2所示：

圖2

2、公式 =OFFSET(A2:B3,1,1,1,1) 中，A2:B3 為引用單元格區域，意思是：返回 A2:B3 左上角的單元格（即 A2）下 1 行、右 1 列且高度與寬度都為 1 的單元格引用，即返回 B3 中的“瓜類”。把第二個參數改為 -1 后，返回 A2:B3 左上角單元格上 1 行、右 1 列且高度與寬度都為 1 的單元格引用，即返回 B1 中的“分類”。

（三）返回距基準單元格左上角指定列數的左右引用實例

1、雙擊 C10 單元格，把公式 =OFFSET(C4:D5,3,2,1,1) 復制到 C10，按回車，返回 892；再次雙擊 C10，把 2 改為 -2，按回車，返回“6月”；操作過程步驟，如圖3所示：

圖3

2、公式 =OFFSET(C4:D5,3,2,1,1) 中，C4:D5 為引用單元格區域，意思是：返回 C4:D5 左上角單元格（即 C4）下 3 行、右 2 列且高度與寬度都為 1 的單元格引用，即返回 E7 中的 892；把 2 改為 -2 后，變為返回 C4 下 3 行、左 2 列的單元格引用，即返回 A7 中的“6月”。

（四）返回多單元格引用實例

1、雙擊 A8 單元格，把公式 =OFFSET(A2:B3,2,2) 復制到 A8，按回車，返回值錯誤 #VALUE!；再次雙擊 A8，在公式后添加參數高度和寬度 1，按回車，返回 5；再雙擊 A8，把后面兩個 1 都改為 2，按 Ctrl + Shift + 回車，也返回 5；操作過程步驟，如圖4所示：

圖4

2、公式 =OFFSET(A2:B3,2,2) 與 =OFFSET(A2:B3,2,2,2,2) 是一樣的，因為省略的高度與寬度默認用 A2:B3 的高度和寬度，按回車時它們返回值錯誤 #VALUE!，而按 Ctrl + Shift + 回車，卻能返回 5，因為返回的是一個數組，這種情況通常用于與其它函數組合，具體見下文的實例。公式 =OFFSET(A2:B3,2,2,1,1) 按回車能返回 5，因為只返回一個單元格。

（五）返回 #REF! 錯誤的實例

1、雙擊 A9 單元格，把公式 =OFFSET(A2:B3,-2,2,1,1) 復制到 A9，按回車，返回引用錯誤 #REF!；操作過程步驟，如圖5所示：

圖5

2、公式 =OFFSET(A2:B3,-2,2,1,1) 的 -2 表示 A2 上面的兩行，而 A2 上只有一行，所以返回引用錯誤。

三、Excel OffSet函數的擴展使用實例

（一）OffSet + Match函數組合

OffSet + Match函數組合的詳細解析，在《Excel Match函數的使用方法的8個實例，含與Sum、OffSet、Indirect組合實現提取數據和對變化區域動態跨表求和》一文中已經介紹，查看請點擊書名號中的文章標題。

（二）Sum + OffSet + Match + CountA函數組合實現動態求和

1、假如要求任意月份的各類服裝銷量之和。雙擊 B11 單元格，把公式 =SUM(OFFSET(A1,MATCH(A11,A2:A9,),1,1,4)) 復制到 B11，按回車，返回“2月”的各類服裝銷量之和 1601；雙擊 B13，把公式 =SUM(OFFSET(A$1,MATCH(A13,A$2:A$9,),1,1,4)) 復制到 B13，按回車，返回“1月”的各類服裝銷量之和 1614；選中 B13，把鼠標移到 B13 右下角的單元格填充柄上，鼠標變為加號后，按住左鍵，往下拖，則求出“4月和7月”的各類服裝銷量之和；操作過程步驟，如圖6所示：

圖6

2、公式說明：

兩個公式是一樣的，只不過后一個公式要往下拉，把 A1、A2 和 A9 的行加 $ 變為絕對引用，下面只以后一個公式 =SUM(OFFSET(A$1,MATCH(A13,A$2:A$9,),1,1,4)) 為例說明。

A、A$2:A$9 以數組形式返回 A2 至 A9 中的所有月份，往下拖，A2 和 A9 都不會自動加 1，如 A2 不會變為 A3、A4 等。

B、MATCH(A13,A$2:A$9,) 用于返回 A13（即“1月”）在 A2 至 A9 中的位置 1；Match函數省略了最后一個參數，默認取 0 表示精確匹配。

C、則 OFFSET(A$1,MATCH(A13,A$2:A$9,),1,1,4) 變為 OFFSET(A$1,1,1,1,4)，接著以 A1 為基準，返回 A1 下 1 行、1 列且高度為 1、寬度為 4 的單元格引用，即返回 B2:E2。

D、則公式變為 =SUM(B2:E2)，最后對 B2:E2 求和，所以返回 1614。

提示：如果列數比較多，公式中的最后一個參數 4 可以用 CountA(B$1:E$1) 或 COUNTA($1:$1)-1 代替，即用CountA函數統計列數，則公式變為 =SUM(OFFSET(A$1,MATCH(A13,A$2:A$9,),1,1,CountA(B$1:E$1)))。$1:$1 表示引用第 1 行。

3、如果要動態求任意列的和，應用此公式 =SUM(OFFSET(A$1,1,MATCH(A11,B$1:E$1,),COUNTA(A$2:A$9),1))，演示如圖7所示：

圖7

（三）OffSet + Int + Row函數組合實現把一個重復多個

1、假如要把 A 列的每個水果名稱重復三個。雙擊 A9 單元格，把公式 =OFFSET(A$2,INT((ROW(A1)-1)/3),0) 復制到 A9，按回車，返回“龍眼”；選中 A9，用往下拖的方法把剩余名稱重復三次；操作過程步驟，如圖8所示：

圖8

2、公式 =OFFSET(A$2,INT((ROW(A1)-1)/3),0) 說明：

A、ROW(A1) 用于返回 A1 的行號 1，當往下拖時，A1 會變為 A2、A3 等，再用 Row 函數返回它們的行號。

B、公式中的 3 表示重復次數；INT((ROW(A1)-1)/3) 用于把 (ROW(A1)-1)/3 的結果取整，只保留整數部分且不四舍五入；例如：(ROW(A1)-1)/3 結果為 0/3，則 INT(0) 返回 0；(ROW(A4)-1)/3 結果為 4/3，則 INT(4/3) 返回 1。

C、則公式變為 =OFFSET(A$2,0,0)，最后用 OffSet 返回 A2 下面 0 行 0 列的單元格引用，即 A2 中的“龍眼”。若 A1 變為 A4，則公式變為 =OFFSET(A$2,1,0)，最后用 OffSet 返回 A2 下 1 行 0 列的單元格引用，即 A3 中的“青皮西瓜”。

（四）CountIf + OffSet + Match + CountIf函數組合實現分段統計

1、假如要統計任意部門的“員工”個數。雙擊 G2 單元格，把公式 =COUNTIF(OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)),F2)復制到 G2，按回車，返回統計結果 2；雙擊 E2 單元格，把“行政”改為“銷售”，按回車，返回“銷售部員工”的統計結果 3；操作過程步驟，如圖9所示：

圖9

2、公式 =COUNTIF(OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)),F2) 說明：

A、MATCH(E2,B2:B10,) 用于返回 E2（即“行政部”）在 B2 至 B10 中的位置 3，Match 也省略了最后一個參數，默認取 0。

B、COUNTIF(B2:B10,E2) 用于在 B2 至 B10 中統計 E2（行政部）的個數，結果為 3，B2:B10 是統計范圍，E2 是條件。

C、則 OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)) 變為 OFFSET(C1,3,,3)，意思是以 C1 為基準，返回 C1 下 3 行 0 列且高度為 3 的單元格引用，即返回 C4:C6。

D、則公式 =COUNTIF(C4:C6,F2)，最后在 C4 至 C6 中統計 F2（員工）的個數，行政部的員工只有兩個，因此返回結果 2 正確。

提示：要統計的列（如“部門”）必須排序。

（五）If + Or + CountIf + OffSet + Row函數組合實現斷碼檢查

1、假如要判斷服裝是否斷碼，假定連續四個尺碼不為 0 為齊碼，否則為斷碼。雙擊 H2 單元格，把公式 =IF(OR(COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0")>=4),"否","是") 復制到 H2，按回車，返回“是”；選中 H2，用于單擊單元格填充柄的方法返回剩余服裝是否斷碼；操作過程步驟，如圖10所示：

圖10

2、公式 =IF(OR(COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0")>=4),"否","是") 說明：

A、A$1 在上面的實現動態求和已經解釋過；ROW(A1) 用于返回 A1 的行號 1；ROW($1:$3) 用于返回 1 到 3 的數組，即 {1;2;3}；4 為OffSet函數的寬度參數，用于取 4 個連續的單元格數值。

B、為什么要返回 1 到 3 的數組？經過上面運算，OFFSET(A$1,ROW(A1),ROW($1:$3),,4) 變為 OFFSET(A$1,1,{1;2;3},,4)，接著以 A1為基準，返回 A1 下 1 行，由于 Cols 參數是一個數組，第一次只能從數組中取出第一個元素 1，因此 OffSet 第一次返回 A1 下 1 行 1 列且寬度為 4 的單元格引用，即 B2:E2；第二次從數組中取第二個元素 2， OffSet 第二次返回 A1 下 1 行 2 列且寬度為 4 的單元格引用，即 C2:F2；同理，OffSet 第三次返回 D2:G2；取 1 到 3 的數組恰好到表格尺碼的最后一列（即 G 列）；OffSet 最后返回 {#VALUE!;#VALUE!;#VALUE!}，數組中的 #VALUE! 依次代表 B2:E2、C2:F2 和 D2:G2。

C、則 COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0") 變為 COUNTIF({#VALUE!;#VALUE!;#VALUE!},">0")，接著，以 ">0" 為條件對每個引用區域進行統計，B2:E2 有一個 0，因此統計結果 3；C2:F2 和 D2:G2 都有一個 0，統計結果也都為 3。

D、則公式變為 =IF(OR({3;3;3}>=4),"否","是")，接著從數組中取出第一個元素 3，并與 4 比較，由于不成立，因此返回 False；繼續取第二、三個元素，它們都不成立，也返回 False；則公式變為 =IF(OR{FALSE;FALSE;FALSE},"否","是")，由于數組中全為假，所以 Or 返回FALSE，則公式變為 =IF(FALSE,"否","是")，If 的條件為假，所以返回“是”。