本篇文章帶大家了解一下人送外號“動態統計之王”的offset函數！offset函數是一個非常實用的函數，它在下拉菜單、動態圖表、動態引用等操作中都具有不可替代的作用。毫不夸張的說excel表格的高效，有相當一部分的功能來源于offset。
?
【前言】
OFFSET函數是判斷Excel函數使用者是否進階的一個重要函數之一。在實際工作中，如果你需要對工作中的數據文件進行系統化、自動化的建模，那么勢必會使用這個函數。
【功能及語法】
OFFSET函數的功能是，以指定的引用為參照系，通過給定的偏移量返回新的引用。
語法：OFFSET(reference,rows,cols,[height],[width])
-
reference? ?是原基礎點
-
rows?????是要偏移的行數，正數向下，負數向上，零不變。
-
cols?????是要偏移的列數，正數向右，負數向左，零不變。
-
[height]? ? 是基礎點偏移后，縱向擴展幾行，正數向下擴展，負數向上擴展。
-
[width]???是基礎點偏移后，橫向擴展幾列，正數向右擴展，負數向左擴展。
如果不使用第四個和第五個參數（但不可以為零），則新引用的區域和原基礎點大小一致。
原基礎點可以是一個單元格，也可以是一個區域。
剛剛接觸OFFSET函數的同學，想要理解上面這些參數，可能存在一定的難度，那么我們用一個圖解的方式來給大家說明一下吧。
相信大家看這個圖都花費了不少時間吧。我們可以先按照上圖的指引，將數據填入OFFSET函數中，實際操作一下，來看看是否和新區域的地址一致呢？
先來測試下第一個例子，看看正數為參量的運行結果：
通過驗算，對黃色 “新區域”中的值進行求和，等于256，與單元格C15中的值一致，結果正確。如果同學們想模擬這個數據，也可以選中C15單元格，再通過工具欄中“公式——公式審核——公式求值”的功能，就能更加直觀的看到OFFSET的返回值。（在函數中使用F9也是可以的，選中公式中OFFSET的函數部分，再按F9即可，這里就不多講了。）
再來測試下第二個例子，看看負數為參量的運行結果：
大家可以用“公式求值”的方式，自己測試一下，看看OFFSET函數區域的返回值。
那么知道了OFFSET的基本運行原理之后，它在實際的工作中就可以幫助我們進行很多的操作和運算，而且有了這個函數的參與，可以實現excel中很多自動化的效果。下面讓我們一起來看看OFFSET函數在實際操作中起到的強大作用！
一、初級常規用法
作為其他函數的區域引用，應該是OFFSET函數最基礎的用途了。OFFSET函數并不是移動了單元格區域，而是返回了一個偏移擴展后的區域地址。因此所有將引用區域作為參數的函數，都可以利用OFFSET函數的返回值，例如我們上面的例子Sum（OFFSET（）），再比如下面這個例子：
函數原理和上面的用法相同，我們就不再贅述了，依然是利用OFFSET函數返回的區域作為MAX函數的參數。
二、進階常規用法
絕技①：模擬轉置TRANSPOSE函數
我們在使用TRANSPOSE函數前，需要先選擇相應大小的轉置區域，而且還需用Ctrl+Shift+Enter三鍵結束公式，比較繁瑣。
這里我們可以使用OFFSET函數來模擬這個轉置的效果，如上圖所示。
A11單元格函數：
=OFFSET($A$1,COLUMN()-1,ROW()-11)
函數解析：
轉置數據其實就是一個“行轉列”、“列轉行”的過程，再說具體點就是行號與列號互換的問題。在原數據中的第一列“姓名”列，轉置后變成了新區域中的第一行。同理“姓名”列中每行的行號，就成為了轉置后的列號。使用OFFSET的原理，就是偏移取值的時候，調換行列號的引值范圍。
★ 比如A11單元格，COLUMN()=1，1-1=0，那么OFFSET的第二參數為0，說明原基礎點的行數不偏移（OFFSET的第二參數表示行偏移量，不熟悉的話看看前面的內容喲！）。ROW()=11，11-11=0，OFFSET的第三參數為0，說明列數也不偏移，所以引用的是原基礎點A1單元格的值。
★★ 把函數向右拉動填充，B11單元格，COLUMN()=2，2-1=1，那么OFFSET的第二參數為1，說明原基礎點的行數向下偏移一個位置。ROW()=11，11-11=0，OFFSET的第三參數為0，說明列數不偏移，所以B11單元格引用的是基礎點A1向下偏移后的A2單元格的值。
★★★ 把A11單元格的函數向下拉動填充，A12單元格，COLUMN()=1，1-1=0，行數不偏移。ROW()=12，12-11=1，OFFSET的第三參數為1，說明列數從基礎點A1向右偏移一個位置，引用的是B1單元格的值（我們公式中的A1之所以使用絕對引用，是因為我們所有的單元格都是以A1為基礎點）。
以此類推，當我們使用鼠標下拉右拉填充公式之后，借助COLUMN和ROW函數幫我們定位出各個單元格的偏移量，由此達到了轉置的效果。
絕技②：模擬Vlookup函數的反向查詢功能
VLOOKUP函數的反向查詢大多是借助數組完成的，但因為數組的原因，在數據量較多的情況下，函數可能會卡頓，所以很多同學也會使用INDEX函數來代替。那么今天就再豐富一下大家的知識量，我們用OFFSET函數來處理這類問題。
C12單元格函數：
=OFFSET($A$1,MATCH(“D2568”,$B$2:$B$7,0),)
函數解析：
我們以單元格A1作為原基礎點，需要返回的值與原基礎點在同一列，所以我們只需要考慮OFFSET函數的行偏移量，不用考慮列偏移量。因為員工編號一般都是具有唯一性的值，所以我們采用MATCH函數得到編號“D2568”在區域B2:B7中的序號，返回值4作為OFFSET函數的行偏移量，帶入到OFFSET函數中，=OFFSET($A$1,4,)。列偏移省略默認為0，擴展寬度和擴展高度省略默認為1 （即一個單元格），是不是就是A5單元格啦！
絕技③：數據重置升級版——重排數據結構
在F2:H2區域輸入公式后，下拉填充數據，就得到了右面的一維數據表。這種重排數據的問題，在實際工作中應該不少見吧！那么同學們會選擇什么方法解決呢？作者反而覺得OFFSET函數的思路更加的簡潔清晰。
函數解析：
第一步：得到連續出現的姓名
F2單元格函數：
=OFFSET($A$1,INT((ROW(F1)-1)/3)+1,)
因為科目一共有三個，所以可以確定同一個姓名需要出現三次，那么當我們下拉F2單元格填充函數的時候，就要保證OFFSET函數的行偏移量每3個單元格的參數值都是一樣的。這里就需要有一個“除數取整”的數學思維了，我們列個圖來輔助說明：
從圖中我們可以看出一組序號，通過INT((序號-1)/3)+1的轉換后，就可以得到右側的序列（如果有4個科目，那就把3改成4，依此類推）。將這個序列號放入OFFSET函數的第二參數，作為行偏移的標準，就可以得到我們姓名列的效果了。
第二步：給同一個人分配不同的科目
G2單元格函數：
=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)
因為我們F列中的每個姓名都出現了三次，這就決定了語文、數學、英語這三個科目需要順序、循環地羅列出來，同第一步的思路一樣，用“除數求余”的數學思維來達到效果。
如上圖所示，序號通過MOD函數的轉換，得到一個順序、循環羅列的序號。將該序號作為OFFSET函數的第三參數列偏移量，就可以順序、循環的引出原數據的科目內容。
第三步：通過姓名和科目，模擬INDEX函數，在原數據中引出成績
H2單元格函數：
=OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))
分別用MATCH函數，得到數據在相關區域中所對應的序號，作為OFFSET的偏移量，分別放入第二、三參數中。從基準點A1單元格偏移后的單元格，就是我們需要的成績值。
通過上面的內容，我們不難發現OFFSET函數，往往都是和MATCH函數連用。因為Match函數可以找到關鍵字在一個數列中的序號，所以我們經常利用這個函數來確定OFFSET函數的偏移量。
三、高階應用的思路
（動態報表模板的原型）
我們使用Excel是為了快速地統計分析數據，快速地提取出我們需要的內容。現在假設以下兩個場景：
場景一：
領導安排了工作，統計某季度的銷售數據，我們馬上行動，用函數快速的制作報表；
場景二：
領導安排了工作，因為每季度都需要統計銷售數據，所以我們早就提前制作了模板，至于什么時候給出報表，就隨我們的便了。切記，不要讓“中層領導”知道你的工作效率很高。
兩個場景，你會選擇哪種處理方式呢？作者希望是第二個。
思路決定了我們制表的格局，這是一個簡單的案例，當數據源被修改后，相對應的季度數據也會自動做出調整。在復雜的模版中并不是所有的位置都會使用OFFSET函數，但對于動態引用數據區域的需求，用OFFSET函數來處理是絕對不會錯的。
四、典型用法舉例
絕技4：制作動態下拉菜單
在數據建模的過程中，我們經常會使用到下拉菜單（或者是組合框控件）。為了確保下拉內容的唯一性，我們會使用INDEX+SMALL+IF+ROW的“萬金油”函數來去重提取數列中的數據。還記得我們在上篇講到的OFFSET函數替代INDEX函數的例子嗎？所以說，如果OFFSET函數可以代替Index函數使用的話，那么OFFSET函數同樣也可以實現“萬金油”的過程。下面我們就一起來看看復雜的“下拉菜單”的制作過程。
步驟一：使用OFFSET函數去重提取唯一值的 “萬金油”公式
這個公式比較長，列出如下：
D2單元格函數：
=IFERROR(OFFSET($A$1,SMALL(IF(ROW($A$2:$A$27)-1=MATCH($A$2:$A$27,$A$2:$A$27,0),ROW($1:$20),9^9),
ROW(D1)),),””)
萬金油公式不是我們今天要講的主題，就不展開講了。重要就是為了讓大家知道OFFSET函數也是可以達到這樣去重的效果。
步驟二：在名稱管理器中使用OFFSET函數，建立數據源
我們可以用Ctrl+F3組合鍵，打開名稱管理器窗口，然后新建名稱，名稱設置為“區域”，引用位置為“D2:D15”，如下圖所示：
然后選擇G1單元格，按Alt+D+L組合鍵可以打開數據驗證設置框，在允許中選擇“序列”，在來源中輸入“=區域”，如下圖所示：
點擊確定按鈕，那么我們G1單元格的下拉菜單就建立好了。但是問題也來了，我們會發現有好多的空選項，這不是我們需要的。
有的同學會說，名稱管理器中選擇D2:D5就可以了。是的，但是如果我們A列的區域中出現了新的數據，那下拉菜單中的數據可就少了，所以此時我們依然使用OFFSET函數來處理這個問題。
更改名稱管理器中，“區域”的引用位置：
=OFFSET(動態下拉菜單!$D$1,1,,COUNTA(動態下拉菜單!$D$2:$D$15)-COUNTBLANK(動態下拉菜單!$D$2:$D$15),1)
因為我們D列的唯一值，是用公式得到的，里面的“空單元格”不是名義上的“空”，而是由公式得到的空，所以不能直接通過COUNTIF(D2:D15,””)的方式得到有值的單元格個數。因此我們先使用了COUNTBLANK函數（空值單元格計數），統計空值單元格的數量，再用COUNTA函數統計非空單元格的數量，最后二者相減就得到了有值單元格的個數。將得到的結果作為OFFSET函數的第四參數（新區域的擴展行數）使用，就實現了動態引用有效數據的效果。如下圖所示：
如果A列中增加了新的區域名稱，那么G1的下拉菜單也會增加新的選項，讓我們來一起看一下效果，我相信這是你需要的。
絕技5：OFFSET函數在圖表中的使用
上面的這張圖表，相信大家都不陌生吧。參加工作的同學們都會有制作圖表的經歷，在上圖中選擇A1:B10區域，在工具欄——“插入”——柱形圖，就完成了我們圖例的內容。
如果我們刪掉一行數據，那么柱形圖中的系列圖例就會少一個，可是如果增加一行數據的話，就需要更改圖表數據源的范圍，才能顯示正確的圖表。但總不能每次都更改呀，那就失去了我們使用Excel 高效快速的初衷。
此時，我們依然可以借鑒OFFSET函數來解決：
步驟一：使用OFFSET函數分別對“日期列”和“數量列”，制作自定義名稱
名稱管理器，我們上面有介紹，就不多說了。選中“日期列”，設置如下：
引用位置函數：
=OFFSET(圖表系列!$A$1,1,0,COUNTA(圖表系列!$A$2:$A$1000),1)
因為原數據中并不存在公式得到的空單元格，所以這里不需要使用Countblank函數，直接用CountA函數統計出非空單元格的個數，作為OFFSET函數的第四參數（新區域的行數）即可。這里的A2:A1000，表示一個絕對大的區域，保證新輸入的內容在這個范圍內。
選中“數量列”，同理制作出數量的自定義名稱，如下：
步驟二：在圖表區域使用名稱
這是OFFSET動態圖表的關鍵所在，添加名稱的位置是很重要的。
在繪圖區，選擇任意一個柱形，在編輯欄中你可以看到圖標的函數寫法（是不是第一次知道圖表也有函數）。我們就在這里修改引用的范圍。
我們只需改動區域的部分。
-
圖表系列!$A$2:$A$10
-
圖表系列!$B$2:$B$10
用自定義名稱替換這兩個紅色的部分即可，切不可以將“圖表系列!$A$2:$A$10”整體替換！
替換后按回車，函數就會像上圖這樣顯示，OFFSET.xlsx是我們的工作薄名稱。效果如下：
其他的圖表類型也是可以的，大家可以試著操作一下，加深印象。
【編后語】
OFFSET函數的五個參數，如果理解了意義，就不難記住。它的返回值可以作為其他函數的引用，同理其他“返回值是數值格式”的函數也可以作為OFFSET函數的參數，讓我們的數據可以自己動起來。
這個函數在Excel函數中起著不可或缺的作用，尤其是我們需要使用Excel建模的時候，動態區域的引用、自動化處理數據，往往都會使用到這個函數，強烈建議同學們，能多花一些時間來學習一下，對你今后制表的過程，將大有裨益。
相關學習推薦：excel教程
