日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

OFFSET是Excel中的函數，在Excel中，OFFSET函數的功能為以指定的引用為參照系，通過給定偏移量得到新的引用。

它的語法結構為：

=OFFSET（基準位置，向下或上偏移幾行，向右或左偏移幾列，引用區域的高度，引用區域的寬度）

公式里面的第1個參數可以是單元格，也可以是單元格區域，第2和第3個參數可為正數，也可以是負數，如果是正數，表示向下和向右偏移，如果是負數，則表示向上和向左偏移，第4和第5個參數如果省略不寫，則默認為和第1個參數大小一致。

返回的引用可以為一個單元格或單元格區域。并可以指定返回的行數或列數。Reference 作為偏移量參照系的引用區域。Reference 必須為對單元格或相連單元格區域的引用；否則，函數 OFFSET 返回錯誤值#VALUE!。

函數說明

如果行數和列數偏移量超出工作表邊緣，函數 OFFSET 返回錯誤值 #REF!。

如果省略 height 或width，則假設其高度或寬度與 reference 相同。

函數 OFFSET 實際上并不移動任何單元格或更改選定區域，它只是返回一個引用。函數 OFFSET可用于任何需要將引用作為參數的函數。

例如，公式 SUM(OFFSET(C2,1,2,3,1)) 將計算比單元格C2 靠下 1 行并靠右 2 列的 3 行 1 列的區域的總值。

函數示例

將示例復制到空白工作表中，你可能會更易于理解該示例。

具體應用：

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。這樣設置后無論如何選擇月份都會計算出正確結果。

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

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

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

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

綜合運用：OFFSET與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函數的應用，試著操作一下吧。