在Excel中，利用函數(shù)的嵌套可以實現(xiàn)很多功能，而offset函數(shù)就是比較難掌握的函數(shù)之一，這個函數(shù)因為參數(shù)比較多，可以有5個參數(shù)，所以與其他函數(shù)結合使用可以有非常多的用法，本文就給大家介紹一下offset函數(shù)的幾種用法，希望能夠?qū)δ峁椭?/p>

一：基本含義。

offset函數(shù)有5個參數(shù)，即=offset（起始區(qū)域，向下偏移行數(shù)，向右偏移列數(shù)，返回的行數(shù)，返回的列數(shù)），如果最后2個參數(shù)省略，則返回的區(qū)域與第一個參數(shù)相同。而第一個參數(shù)表示的起始區(qū)域不僅可以是單元格，也可以表示從一個區(qū)域開始進行偏移。

如下圖所示，在F2單元格輸入函數(shù)=OFFSET(A1,5,3,4,2)，表示從A1單元格開始偏移，向下偏移5行，向右偏移3列，然后返回4行2列的區(qū)域，即下圖中D6到E9單元格的區(qū)域。因為此處返回的是一個區(qū)域，所以輸入函數(shù)直接按回車會返回錯誤值，按下Ctrl+shift+enter鍵會返回區(qū)域的第一個值。

二：offset函數(shù)的基本運算

既然上圖中offset函數(shù)經(jīng)過偏移后返回的是一個區(qū)域，所以我們可以對這個區(qū)域求和、平均值、計數(shù)、最大最小值等。比如對下面的返回的區(qū)域求和，那么在offset函數(shù)前加上sum函數(shù)，然后就可以計算出結果是36。

三：offset與一個match函數(shù)結合求和。

在下圖中，要計算1月到某月的銷量和，如果切換月份時，累計銷量也會變化。該如何操作呢？

首先，在F2單元格添加數(shù)據(jù)驗證。選擇【數(shù)據(jù)】——【數(shù)據(jù)驗證】——允許選擇【序列】——來源選擇A2到A13單元格的數(shù)據(jù)——【確定】。這時我們就在F2單元格建立好月份的下拉菜單選項。

然后在G2單元格輸入函數(shù)=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))，此時再F2單元格選擇相應的月份，G2單元格的累計銷量就會隨著選擇的月變化而變化。

這個函數(shù)有三個公式，最里面的MATCH(F2,A2:A13,0)，表示查找F2位于A2到A13單元格的第幾行，比如F2單元格是十月，十月在A2到A13單元格的第10行，所以match函數(shù)返回的結果是10。OFFSET(B2,0,0,10）中省略了第五個參數(shù)，返回的結果是B2到B11單元格，最后利用sum函數(shù)對B2到B11單元格進行求和。

四：offset函數(shù)與多個match函數(shù)進行求和。

仍然是上面一組數(shù)據(jù)，那么可不可以求任意兩個月份之間的累計銷量呢？我們考慮到既然match函數(shù)返回的值是所選單元格在區(qū)域中的位置，那么就可以利用match函數(shù)嵌套來編制公式。

我們在D2和F2單元格分別設置月份的下拉菜單選項，然后在G2單元格中輸入函數(shù)=SUM(OFFSET(B2,MATCH(D2,A2:A13,0)-1,0,MATCH(F2,A2:A13,0)-MATCH(D2,A2:A13,0)+1),0)。此時我們在D2和F2中選擇相應的月份，就可以求出兩個月份之間的累計銷量了。

這個函數(shù)看上去比較長，實際在編寫函數(shù)的時候比較容易想到，也容易理解，因為match函數(shù)可以返回行數(shù)，所以在以B2單元格為起始單元格的前提下，向下偏移的行數(shù)要根據(jù)D2單元格的變化而變化，第二個參數(shù)用了MATCH(D2,A2:A13,0)-1，表示如果D2單元格選擇九月，那么這個match函數(shù)返回的是8，即9月對應的銷量在B2開始數(shù)的第9-1=8行數(shù)據(jù)。第4個參數(shù)用到了兩個match函數(shù)相減，因為F2單元格所在的行數(shù)減D2單元格所在的行數(shù)，需要對計算結果加1進行調(diào)整。最后利用sum函數(shù)求和即可。

但是這種情況如果選擇時D2單元格大于F2單元格時，計算的就不是正確結果怎么辦？此時只要把offset函數(shù)第四個參數(shù)嵌套一個if函數(shù)，即如果F2的月份大于D2，那么match函數(shù)相減后加1，如果F2的月份小于D2，那么等于match函數(shù)相減后減1。這樣設置后無論如何選擇月份都會計算出正確結果。

五：與count函數(shù)組合求最近幾個累計數(shù)

如下圖所示，我想要計算最近3個月的累計銷量，當增加月份時，銷量也會變動，函數(shù)如何寫呢？

此時輸入的函數(shù)是=SUM(OFFSET(B1,COUNT(B:B),0,-3))。在下面繼續(xù)添加月份和銷售時，累計銷量始終是最近3個月的銷售累計。

這個函數(shù)offset函數(shù)第二個參數(shù)COUNT(B:B)表示對B列數(shù)據(jù)進行計算，因為count函數(shù)對文本、空白單元格都不會進行計數(shù)，所以B列有多少有數(shù)據(jù)的單元格，count函數(shù)就返回多少。而第四個參數(shù)-3，表示從B1單元格偏移到最后一個單元格后，往回折了3個單元格。所以可以表示計算最近三筆銷量之和。

六，綜合運用：與match、countif、vlookup函數(shù)，定義名稱結合制作二級下拉菜單并動態(tài)查找數(shù)據(jù)

下圖左側是我國34個省級行政區(qū)，300多個市級行政區(qū)及對應銷量，我們根據(jù)右側的下拉箭頭選擇省級行政區(qū)后，就可以在后面的下拉箭頭選擇當前省級行政區(qū)下的市及對應銷量。因為步驟比較多，此處不再對具體操作進行演示，簡單說一下操作步驟。以后在介紹動態(tài)圖表制作的時候會進行詳細介紹。

首先把A列的數(shù)據(jù)復制到E列中(此處為了能看清楚動圖，E列已隱藏)。然后選擇【數(shù)據(jù)】選項卡——【刪除重復值】，把E列中的每個省份名稱只留下一個值。

然后打開【公式】選項卡——【定義名稱】，輸入函數(shù)=5'!$E$2:$E$35，前面這個5'!是引用的工作表名稱。名稱輸入“省”。繼續(xù)定義名稱，輸入函數(shù)=OFFSET('5'!$B$1,MATCH('5'!$G$2,'5'!$A$2:$A$342,0),0,COUNTIF('5'!$A$2:$A$342,'5'!$G$2),1)，名稱輸入“市”。

接著在G2單元格中，添加【數(shù)據(jù)驗證】——【序列】——【來源】=省。在H2單元格中，【數(shù)據(jù)驗證】——【序列】——【來源】=市。

最后在I2單元格中輸入函數(shù)=VLOOKUP(H2,$B:$C,2,0)，就可以實現(xiàn)動態(tài)查找了。

這就是本文介紹的offset函數(shù)的應用，試著操作一下吧。