在Excel中，利用函數的嵌套可以實現很多功能，而offset函數就是比較難掌握的函數之一，這個函數因為參數比較多，可以有5個參數，所以與其他函數結合使用可以有非常多的用法，本文就給大家介紹一下offset函數的幾種用法，希望能夠對您提供幫助。

一：基本含義。

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

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

二：offset函數的基本運算

既然上圖中offset函數經過偏移後返回的是一個區域，所以我們可以對這個區域求和、平均值、計數、最大最小值等。比如對下面的返回的區域求和，那麼在offset函數前加上SUM函數，然後就可以計算出結果是36。

三：offset與一個match函數結合求和。

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

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

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

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

四：offset函數與多個match函數進行求和。

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

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

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

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

五：與count函數組合求最近幾個累計數

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

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

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

六，綜合運用：與match、countif、vlookup函數，定義名稱結合制作二級下拉菜單并動态查找數據

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

首先把A列的數據複制到E列中(此處為了能看清楚動圖，E列已隐藏)。然後選擇【數據】選項卡——【删除重複值】，把E列中的每個省份名稱隻留下一個值。

然後打開【公式】選項卡——【定義名稱】，輸入函數=5'!$E$2:$E$35，前面這個5'!是引用的工作表名稱。名稱輸入“省”。繼續定義名稱，輸入函數=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單元格中，添加【數據驗證】——【序列】——【來源】=省。在H2單元格中，【數據驗證】——【序列】——【來源】=市。

最後在I2單元格中輸入函數=VLOOKUP(H2,$B:$C,2,0)，就可以實現動态查找了。

這就是本文介紹的offset函數的應用，試着操作一下吧。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!