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

OffSet函數(shù)常與 Sum、Match、CountIf、If、Or、Row 等函數(shù)組合使用，例如 Sum + OffSet + Match + CountA函數(shù)組合實現(xiàn)動態(tài)求和、OffSet + Int + Row函數(shù)組合實現(xiàn)把一個重復(fù)多個、CountIf + OffSet + Match + CountIf函數(shù)組合實現(xiàn)分段統(tǒng)計。

一、Excel OffSet函數(shù)語法

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

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

2、說明：

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

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

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

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

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

二、Excel OffSet函數(shù)的使用方法及實例

（一）Rows 或 Cols 為 0 的實例

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

圖1

2、公式說明：

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

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

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

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

圖2

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

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

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

圖3

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

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

1、雙擊 A8 單元格，把公式 =OFFSET(A2:B3,2,2) 復(fù)制到 A8，按回車，返回值錯誤 #VALUE!；再次雙擊 A8，在公式后添加參數(shù)高度和寬度 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，因為返回的是一個數(shù)組，這種情況通常用于與其它函數(shù)組合，具體見下文的實例。公式 =OFFSET(A2:B3,2,2,1,1) 按回車能返回 5，因為只返回一個單元格。

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

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

圖5

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

三、Excel OffSet函數(shù)的擴展使用實例

（一）OffSet + Match函數(shù)組合

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

（二）Sum + OffSet + Match + CountA函數(shù)組合實現(xiàn)動態(tài)求和

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

圖6

2、公式說明：

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

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

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

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

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

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

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

圖7

（三）OffSet + Int + Row函數(shù)組合實現(xiàn)把一個重復(fù)多個

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

圖8

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

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

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

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

（四）CountIf + OffSet + Match + CountIf函數(shù)組合實現(xiàn)分段統(tǒng)計

1、假如要統(tǒng)計任意部門的“員工”個數(shù)。雙擊 G2 單元格，把公式 =COUNTIF(OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)),F2)復(fù)制到 G2，按回車，返回統(tǒng)計結(jié)果 2；雙擊 E2 單元格，把“行政”改為“銷售”，按回車，返回“銷售部員工”的統(tǒng)計結(jié)果 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 也省略了最后一個參數(shù)，默認取 0。

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

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

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

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

（五）If + Or + CountIf + OffSet + Row函數(shù)組合實現(xiàn)斷碼檢查

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

圖10

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

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

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

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

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