OFFSET函數的用法

OFFSET是Excel中非常重要的引用函數之一，很多高手喜歡利用這個函數進行數據引用，以此來解決一些棘手的問題。今天小五就帶大家來詳細認識一下這個函數。

OFFSET函數主要用來返回對單元格或單元格區域中指定行數和列數的區域的引用。返回的結果可以是單個單元格，也可以是單元格區域。它的語法結構為：

=OFFSET(基點,偏移的行數,偏移的列數,引用的行數,引用的列數)

注意：第2個參數為正數表示向下偏移，負數則向上偏移，第3個參數為正數表示向右偏移，負數則向左偏移；第4和第5個參數需為正數，如果省略的話，則表示引用的區域大小和基點一致。

1、單個單元格引用

如下圖表格，輸入公式：=OFFSET(A1,2,1)，返回結果是瀋陽。

公式說明：以A1為基點，向下偏移2行，向右偏移1列，省略第4和第5個參數，表示引用區域大小和基點一致，即單個單元格，結果是瀋陽。

2、單元格區域引用

選中表格外任意4個空白單元格區域，輸入公式：=OFFSET(A1,3,1,2,2)，按Ctrl+Shift+Enter三鍵輸入公式，返回杭州、成都、福州和長春這四個城市。

公式說明：以A1為基點，向下偏移3行，向左偏移1列，引用2行和2列的數據區域。

公式也可以寫成：=OFFSET(A1:B2,3,1,)，也就是以單元格區域為基點進行移動，第4和第5個參數省略，引用區域大小和基點一致。

3、單條件查找

利用OFFSET函數對數據的引用原理，我們可以根據指定條件進行數據查找，如下圖表格，要查找劉麗麗的業績，輸入公式：=OFFSET(B1,MATCH(D2,A2:A12,0),)

公式說明：先利用MATCH函數找出劉麗麗具體位置，再用OFFSET函數進行引用，OFFSET函數公式中的第3個參數也省略了，表示在當列上下移動,但要保留逗號進行占位。

4、多條件查找

也可以利用OFFSET函數實現多條件查找，如下圖表格，要查找劉麗麗2月份的業績，輸入公式：=OFFSET(A1,MATCH(F2,A2:A12,0),MATCH(G2,B1:D1,0))

5、指定條件求和

如果要計算劉麗麗3個月的總業績，輸入公式：

=SUM(OFFSET(A1,MATCH(F7,A2:A12,0),1,1,3))

或=SUM(OFFSET(B1:D1,MATCH(F2,A2:A12,0),))

6、指定條件計算平均值：

也可以計算指定月份的平均業績，比如這裡需要計算出2月份所有人員的平均業績，輸入公式：=AVERAGE(OFFSET(A1,1,MATCH(F3,B1:D1,0),11,1))

或=AVERAGE(OFFSET(A2:A12,,MATCH(F2,B1:D1,0)))

7、多行多列轉換為一列

利用OFFSET函數還可以把多行多列數據轉換為一列，如下圖表格，輸入公式：=OFFSET($A$1,INT((ROW(A1)-1)/3),MOD((ROW(A1)-1),3))&""

公式說明：INT((ROW(A1)-1)/3)向下填充時，生成000111222333444序列號；MOD((ROW(A1)-1),3)向下填充時，生成012012012012……序列號；最後用連字符加

上""，使得單元格數據引用完畢後顯示為空。

8、動態獲取最後數據記錄

如下圖表格，記錄每天的銷量數據，如何動態獲取最後的數據記錄？

獲取最後日期：=OFFSET(A$1,COUNTA(A$1:A15)-1,0)

獲取最後銷量：=OFFSET(B$1,COUNTA(B$1:B15)-1,0)

公式說明：先用COUNTA函數計算非空單元格數量，再用OFFSET函數進行數據引用。