本篇文章帶大家了解一下人送外號(hào)“動(dòng)態(tài)統(tǒng)計(jì)之王”的offset函數(shù)！offset函數(shù)是一個(gè)非常實(shí)用的函數(shù)，它在下拉菜單、動(dòng)態(tài)圖表、動(dòng)態(tài)引用等操作中都具有不可替代的作用。毫不夸張的說excel表格的高效，有相當(dāng)一部分的功能來源于offset。

【前言】

OFFSET函數(shù)是判斷Excel函數(shù)使用者是否進(jìn)階的一個(gè)重要函數(shù)之一。在實(shí)際工作中，如果你需要對(duì)工作中的數(shù)據(jù)文件進(jìn)行系統(tǒng)化、自動(dòng)化的建模，那么勢(shì)必會(huì)使用這個(gè)函數(shù)。

【功能及語法】

OFFSET函數(shù)的功能是，以指定的引用為參照系，通過給定的偏移量返回新的引用。

語法：OFFSET(reference,rows,cols,[height],[width])

• reference   是原基礎(chǔ)點(diǎn)

• rows     是要偏移的行數(shù)，正數(shù)向下，負(fù)數(shù)向上，零不變。

• cols     是要偏移的列數(shù)，正數(shù)向右，負(fù)數(shù)向左，零不變。

• [height]    是基礎(chǔ)點(diǎn)偏移后，縱向擴(kuò)展幾行，正數(shù)向下擴(kuò)展，負(fù)數(shù)向上擴(kuò)展。

• [width]   是基礎(chǔ)點(diǎn)偏移后，橫向擴(kuò)展幾列，正數(shù)向右擴(kuò)展，負(fù)數(shù)向左擴(kuò)展。

如果不使用第四個(gè)和第五個(gè)參數(shù)（但不可以為零），則新引用的區(qū)域和原基礎(chǔ)點(diǎn)大小一致。

原基礎(chǔ)點(diǎn)可以是一個(gè)單元格，也可以是一個(gè)區(qū)域。

剛剛接觸OFFSET函數(shù)的同學(xué)，想要理解上面這些參數(shù)，可能存在一定的難度，那么我們用一個(gè)圖解的方式來給大家說明一下吧。

相信大家看這個(gè)圖都花費(fèi)了不少時(shí)間吧。我們可以先按照上圖的指引，將數(shù)據(jù)填入OFFSET函數(shù)中，實(shí)際操作一下，來看看是否和新區(qū)域的地址一致呢？

先來測(cè)試下第一個(gè)例子，看看正數(shù)為參量的運(yùn)行結(jié)果：

通過驗(yàn)算，對(duì)黃色 “新區(qū)域”中的值進(jìn)行求和，等于256，與單元格C15中的值一致，結(jié)果正確。如果同學(xué)們想模擬這個(gè)數(shù)據(jù)，也可以選中C15單元格，再通過工具欄中“公式——公式審核——公式求值”的功能，就能更加直觀的看到OFFSET的返回值。（在函數(shù)中使用F9也是可以的，選中公式中OFFSET的函數(shù)部分，再按F9即可，這里就不多講了。）

再來測(cè)試下第二個(gè)例子，看看負(fù)數(shù)為參量的運(yùn)行結(jié)果：

大家可以用“公式求值”的方式，自己測(cè)試一下，看看OFFSET函數(shù)區(qū)域的返回值。

那么知道了OFFSET的基本運(yùn)行原理之后，它在實(shí)際的工作中就可以幫助我們進(jìn)行很多的操作和運(yùn)算，而且有了這個(gè)函數(shù)的參與，可以實(shí)現(xiàn)excel中很多自動(dòng)化的效果。下面讓我們一起來看看OFFSET函數(shù)在實(shí)際操作中起到的強(qiáng)大作用！

一、初級(jí)常規(guī)用法

作為其他函數(shù)的區(qū)域引用，應(yīng)該是OFFSET函數(shù)最基礎(chǔ)的用途了。OFFSET函數(shù)并不是移動(dòng)了單元格區(qū)域，而是返回了一個(gè)偏移擴(kuò)展后的區(qū)域地址。因此所有將引用區(qū)域作為參數(shù)的函數(shù)，都可以利用OFFSET函數(shù)的返回值，例如我們上面的例子Sum（OFFSET（）），再比如下面這個(gè)例子：

函數(shù)原理和上面的用法相同，我們就不再贅述了，依然是利用OFFSET函數(shù)返回的區(qū)域作為MAX函數(shù)的參數(shù)。

二、進(jìn)階常規(guī)用法

絕技①：模擬轉(zhuǎn)置TRANSPOSE函數(shù)

我們?cè)谑褂肨RANSPOSE函數(shù)前，需要先選擇相應(yīng)大小的轉(zhuǎn)置區(qū)域，而且還需用Ctrl+Shift+Enter三鍵結(jié)束公式，比較繁瑣。

這里我們可以使用OFFSET函數(shù)來模擬這個(gè)轉(zhuǎn)置的效果，如上圖所示。

A11單元格函數(shù)：

=OFFSET($A$1,COLUMN()-1,ROW()-11)

函數(shù)解析：

轉(zhuǎn)置數(shù)據(jù)其實(shí)就是一個(gè)“行轉(zhuǎn)列”、“列轉(zhuǎn)行”的過程，再說具體點(diǎn)就是行號(hào)與列號(hào)互換的問題。在原數(shù)據(jù)中的第一列“姓名”列，轉(zhuǎn)置后變成了新區(qū)域中的第一行。同理“姓名”列中每行的行號(hào)，就成為了轉(zhuǎn)置后的列號(hào)。使用OFFSET的原理，就是偏移取值的時(shí)候，調(diào)換行列號(hào)的引值范圍。

比如A11單元格，COLUMN()=1，1-1=0，那么OFFSET的第二參數(shù)為0，說明原基礎(chǔ)點(diǎn)的行數(shù)不偏移（OFFSET的第二參數(shù)表示行偏移量，不熟悉的話看看前面的內(nèi)容喲?。?。ROW()=11，11-11=0，OFFSET的第三參數(shù)為0，說明列數(shù)也不偏移，所以引用的是原基礎(chǔ)點(diǎn)A1單元格的值。

把函數(shù)向右拉動(dòng)填充，B11單元格，COLUMN()=2，2-1=1，那么OFFSET的第二參數(shù)為1，說明原基礎(chǔ)點(diǎn)的行數(shù)向下偏移一個(gè)位置。ROW()=11，11-11=0，OFFSET的第三參數(shù)為0，說明列數(shù)不偏移，所以B11單元格引用的是基礎(chǔ)點(diǎn)A1向下偏移后的A2單元格的值。

把A11單元格的函數(shù)向下拉動(dòng)填充，A12單元格，COLUMN()=1，1-1=0，行數(shù)不偏移。ROW()=12，12-11=1，OFFSET的第三參數(shù)為1，說明列數(shù)從基礎(chǔ)點(diǎn)A1向右偏移一個(gè)位置，引用的是B1單元格的值（我們公式中的A1之所以使用絕對(duì)引用，是因?yàn)槲覀兯械膯卧穸际且訟1為基礎(chǔ)點(diǎn)）。

以此類推，當(dāng)我們使用鼠標(biāo)下拉右拉填充公式之后，借助COLUMN和ROW函數(shù)幫我們定位出各個(gè)單元格的偏移量，由此達(dá)到了轉(zhuǎn)置的效果。

絕技②：模擬Vlookup函數(shù)的反向查詢功能

VLOOKUP函數(shù)的反向查詢大多是借助數(shù)組完成的，但因?yàn)閿?shù)組的原因，在數(shù)據(jù)量較多的情況下，函數(shù)可能會(huì)卡頓，所以很多同學(xué)也會(huì)使用INDEX函數(shù)來代替。那么今天就再豐富一下大家的知識(shí)量，我們用OFFSET函數(shù)來處理這類問題。

C12單元格函數(shù)：

=OFFSET($A$1,MATCH(“D2568”,$B$2:$B$7,0),)

函數(shù)解析：

我們以單元格A1作為原基礎(chǔ)點(diǎn)，需要返回的值與原基礎(chǔ)點(diǎn)在同一列，所以我們只需要考慮OFFSET函數(shù)的行偏移量，不用考慮列偏移量。因?yàn)閱T工編號(hào)一般都是具有唯一性的值，所以我們采用MATCH函數(shù)得到編號(hào)“D2568”在區(qū)域B2:B7中的序號(hào)，返回值4作為OFFSET函數(shù)的行偏移量，帶入到OFFSET函數(shù)中，=OFFSET($A$1,4,)。列偏移省略默認(rèn)為0，擴(kuò)展寬度和擴(kuò)展高度省略默認(rèn)為1 （即一個(gè)單元格），是不是就是A5單元格啦！

絕技③：數(shù)據(jù)重置升級(jí)版——重排數(shù)據(jù)結(jié)構(gòu)

在F2:H2區(qū)域輸入公式后，下拉填充數(shù)據(jù)，就得到了右面的一維數(shù)據(jù)表。這種重排數(shù)據(jù)的問題，在實(shí)際工作中應(yīng)該不少見吧！那么同學(xué)們會(huì)選擇什么方法解決呢？作者反而覺得OFFSET函數(shù)的思路更加的簡潔清晰。

函數(shù)解析：

第一步：得到連續(xù)出現(xiàn)的姓名

F2單元格函數(shù)：

=OFFSET($A$1,INT((ROW(F1)-1)/3)+1,)

因?yàn)榭颇恳还灿腥齻€(gè)，所以可以確定同一個(gè)姓名需要出現(xiàn)三次，那么當(dāng)我們下拉F2單元格填充函數(shù)的時(shí)候，就要保證OFFSET函數(shù)的行偏移量每3個(gè)單元格的參數(shù)值都是一樣的。這里就需要有一個(gè)“除數(shù)取整”的數(shù)學(xué)思維了，我們列個(gè)圖來輔助說明：

從圖中我們可以看出一組序號(hào)，通過INT((序號(hào)-1)/3)+1的轉(zhuǎn)換后，就可以得到右側(cè)的序列（如果有4個(gè)科目，那就把3改成4，依此類推）。將這個(gè)序列號(hào)放入OFFSET函數(shù)的第二參數(shù)，作為行偏移的標(biāo)準(zhǔn)，就可以得到我們姓名列的效果了。

第二步：給同一個(gè)人分配不同的科目

G2單元格函數(shù)：

=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)

因?yàn)槲覀僃列中的每個(gè)姓名都出現(xiàn)了三次，這就決定了語文、數(shù)學(xué)、英語這三個(gè)科目需要順序、循環(huán)地羅列出來，同第一步的思路一樣，用“除數(shù)求余”的數(shù)學(xué)思維來達(dá)到效果。

如上圖所示，序號(hào)通過MOD函數(shù)的轉(zhuǎn)換，得到一個(gè)順序、循環(huán)羅列的序號(hào)。將該序號(hào)作為OFFSET函數(shù)的第三參數(shù)列偏移量，就可以順序、循環(huán)的引出原數(shù)據(jù)的科目內(nèi)容。

第三步：通過姓名和科目，模擬INDEX函數(shù)，在原數(shù)據(jù)中引出成績

H2單元格函數(shù)：

=OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

分別用MATCH函數(shù)，得到數(shù)據(jù)在相關(guān)區(qū)域中所對(duì)應(yīng)的序號(hào)，作為OFFSET的偏移量，分別放入第二、三參數(shù)中。從基準(zhǔn)點(diǎn)A1單元格偏移后的單元格，就是我們需要的成績值。

通過上面的內(nèi)容，我們不難發(fā)現(xiàn)OFFSET函數(shù)，往往都是和MATCH函數(shù)連用。因?yàn)镸atch函數(shù)可以找到關(guān)鍵字在一個(gè)數(shù)列中的序號(hào)，所以我們經(jīng)常利用這個(gè)函數(shù)來確定OFFSET函數(shù)的偏移量。

三、高階應(yīng)用的思路

（動(dòng)態(tài)報(bào)表模板的原型）

我們使用Excel是為了快速地統(tǒng)計(jì)分析數(shù)據(jù)，快速地提取出我們需要的內(nèi)容?，F(xiàn)在假設(shè)以下兩個(gè)場(chǎng)景：

場(chǎng)景一：

領(lǐng)導(dǎo)安排了工作，統(tǒng)計(jì)某季度的銷售數(shù)據(jù)，我們馬上行動(dòng)，用函數(shù)快速的制作報(bào)表；

場(chǎng)景二：

領(lǐng)導(dǎo)安排了工作，因?yàn)槊考径榷夹枰y(tǒng)計(jì)銷售數(shù)據(jù)，所以我們?cè)缇吞崆爸谱髁四０澹劣谑裁磿r(shí)候給出報(bào)表，就隨我們的便了。切記，不要讓“中層領(lǐng)導(dǎo)”知道你的工作效率很高。

兩個(gè)場(chǎng)景，你會(huì)選擇哪種處理方式呢？作者希望是第二個(gè)。

思路決定了我們制表的格局，這是一個(gè)簡單的案例，當(dāng)數(shù)據(jù)源被修改后，相對(duì)應(yīng)的季度數(shù)據(jù)也會(huì)自動(dòng)做出調(diào)整。在復(fù)雜的模版中并不是所有的位置都會(huì)使用OFFSET函數(shù)，但對(duì)于動(dòng)態(tài)引用數(shù)據(jù)區(qū)域的需求，用OFFSET函數(shù)來處理是絕對(duì)不會(huì)錯(cuò)的。

四、典型用法舉例

絕技4：制作動(dòng)態(tài)下拉菜單

在數(shù)據(jù)建模的過程中，我們經(jīng)常會(huì)使用到下拉菜單（或者是組合框控件）。為了確保下拉內(nèi)容的唯一性，我們會(huì)使用INDEX+SMALL+IF+ROW的“萬金油”函數(shù)來去重提取數(shù)列中的數(shù)據(jù)。還記得我們?cè)谏掀v到的OFFSET函數(shù)替代INDEX函數(shù)的例子嗎？所以說，如果OFFSET函數(shù)可以代替Index函數(shù)使用的話，那么OFFSET函數(shù)同樣也可以實(shí)現(xiàn)“萬金油”的過程。下面我們就一起來看看復(fù)雜的“下拉菜單”的制作過程。

步驟一：使用OFFSET函數(shù)去重提取唯一值的 “萬金油”公式

這個(gè)公式比較長，列出如下：

D2單元格函數(shù)：

=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函數(shù)也是可以達(dá)到這樣去重的效果。

步驟二：在名稱管理器中使用OFFSET函數(shù)，建立數(shù)據(jù)源

我們可以用Ctrl+F3組合鍵，打開名稱管理器窗口，然后新建名稱，名稱設(shè)置為“區(qū)域”，引用位置為“D2:D15”，如下圖所示：

然后選擇G1單元格，按Alt+D+L組合鍵可以打開數(shù)據(jù)驗(yàn)證設(shè)置框，在允許中選擇“序列”，在來源中輸入“=區(qū)域”，如下圖所示：

點(diǎn)擊確定按鈕，那么我們G1單元格的下拉菜單就建立好了。但是問題也來了，我們會(huì)發(fā)現(xiàn)有好多的空選項(xiàng)，這不是我們需要的。

有的同學(xué)會(huì)說，名稱管理器中選擇D2:D5就可以了。是的，但是如果我們A列的區(qū)域中出現(xiàn)了新的數(shù)據(jù)，那下拉菜單中的數(shù)據(jù)可就少了，所以此時(shí)我們依然使用OFFSET函數(shù)來處理這個(gè)問題。

更改名稱管理器中，“區(qū)域”的引用位置：

=OFFSET(動(dòng)態(tài)下拉菜單!$D$1,1,,COUNTA(動(dòng)態(tài)下拉菜單!$D$2:$D$15)-COUNTBLANK(動(dòng)態(tài)下拉菜單!$D$2:$D$15),1)

因?yàn)槲覀僁列的唯一值，是用公式得到的，里面的“空單元格”不是名義上的“空”，而是由公式得到的空，所以不能直接通過COUNTIF(D2:D15,””)的方式得到有值的單元格個(gè)數(shù)。因此我們先使用了COUNTBLANK函數(shù)（空值單元格計(jì)數(shù)），統(tǒng)計(jì)空值單元格的數(shù)量，再用COUNTA函數(shù)統(tǒng)計(jì)非空單元格的數(shù)量，最后二者相減就得到了有值單元格的個(gè)數(shù)。將得到的結(jié)果作為OFFSET函數(shù)的第四參數(shù)（新區(qū)域的擴(kuò)展行數(shù)）使用，就實(shí)現(xiàn)了動(dòng)態(tài)引用有效數(shù)據(jù)的效果。如下圖所示：

如果A列中增加了新的區(qū)域名稱，那么G1的下拉菜單也會(huì)增加新的選項(xiàng)，讓我們來一起看一下效果，我相信這是你需要的。

絕技5：OFFSET函數(shù)在圖表中的使用

上面的這張圖表，相信大家都不陌生吧。參加工作的同學(xué)們都會(huì)有制作圖表的經(jīng)歷，在上圖中選擇A1:B10區(qū)域，在工具欄——“插入”——柱形圖，就完成了我們圖例的內(nèi)容。

如果我們刪掉一行數(shù)據(jù)，那么柱形圖中的系列圖例就會(huì)少一個(gè)，可是如果增加一行數(shù)據(jù)的話，就需要更改圖表數(shù)據(jù)源的范圍，才能顯示正確的圖表。但總不能每次都更改呀，那就失去了我們使用Excel 高效快速的初衷。

此時(shí)，我們依然可以借鑒OFFSET函數(shù)來解決：

步驟一：使用OFFSET函數(shù)分別對(duì)“日期列”和“數(shù)量列”，制作自定義名稱

名稱管理器，我們上面有介紹，就不多說了。選中“日期列”，設(shè)置如下：

引用位置函數(shù)：

=OFFSET(圖表系列!$A$1,1,0,COUNTA(圖表系列!$A$2:$A$1000),1)

因?yàn)樵瓟?shù)據(jù)中并不存在公式得到的空單元格，所以這里不需要使用Countblank函數(shù)，直接用CountA函數(shù)統(tǒng)計(jì)出非空單元格的個(gè)數(shù)，作為OFFSET函數(shù)的第四參數(shù)（新區(qū)域的行數(shù)）即可。這里的A2:A1000，表示一個(gè)絕對(duì)大的區(qū)域，保證新輸入的內(nèi)容在這個(gè)范圍內(nèi)。

選中“數(shù)量列”，同理制作出數(shù)量的自定義名稱，如下：

步驟二：在圖表區(qū)域使用名稱

這是OFFSET動(dòng)態(tài)圖表的關(guān)鍵所在，添加名稱的位置是很重要的。

在繪圖區(qū)，選擇任意一個(gè)柱形，在編輯欄中你可以看到圖標(biāo)的函數(shù)寫法（是不是第一次知道圖表也有函數(shù)）。我們就在這里修改引用的范圍。

我們只需改動(dòng)區(qū)域的部分。

• 圖表系列!$A$2:$A$10

• 圖表系列!$B$2:$B$10

用自定義名稱替換這兩個(gè)紅色的部分即可，切不可以將“圖表系列!$A$2:$A$10”整體替換！

替換后按回車，函數(shù)就會(huì)像上圖這樣顯示，OFFSET.xlsx是我們的工作薄名稱。效果如下：

其他的圖表類型也是可以的，大家可以試著操作一下，加深印象。

【編后語】

OFFSET函數(shù)的五個(gè)參數(shù)，如果理解了意義，就不難記住。它的返回值可以作為其他函數(shù)的引用，同理其他“返回值是數(shù)值格式”的函數(shù)也可以作為OFFSET函數(shù)的參數(shù)，讓我們的數(shù)據(jù)可以自己動(dòng)起來。

這個(gè)函數(shù)在Excel函數(shù)中起著不可或缺的作用，尤其是我們需要使用Excel建模的時(shí)候，動(dòng)態(tài)區(qū)域的引用、自動(dòng)化處理數(shù)據(jù)，往往都會(huì)使用到這個(gè)函數(shù)，強(qiáng)烈建議同學(xué)們，能多花一些時(shí)間來學(xué)習(xí)一下，對(duì)你今后制表的過程，將大有裨益。

相關(guān)學(xué)習(xí)推薦：

以上就是Excel函數(shù)學(xué)習(xí)之聊聊動(dòng)態(tài)統(tǒng)計(jì)之王OFFSET()的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注有卡有網(wǎng)