編按：哈嘍，大家好！今天是部落窩函數(shù)課堂的第6課，我們將認識人送外號“動態(tài)統(tǒng)計之王”的OFFSET函數(shù)！OFFSET函數(shù)是一個非常實用的函數(shù)，它在下拉菜單、動態(tài)圖表、動態(tài)引用等操作中都具有不可替代的作用。毫不夸張的說Excel表格的高效，有相當一部分的功能來源于OFFSET。今天就跟著小編一起來認識一下它吧?。ㄓ捎诮坛唐^長，將分為上下兩篇，本篇為上篇。）

＊＊＊＊＊＊＊＊＊

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

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

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

reference：是原基礎(chǔ)點

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

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

[height]：是基礎(chǔ)點偏移后，縱向擴展幾行，正數(shù)向下擴展，負數(shù)向上擴展。

[width]：是基礎(chǔ)點偏移后，橫向擴展幾列，正數(shù)向右擴展，負數(shù)向左擴展。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

我們在使用TRANSPOSE函數(shù)前，需要先選擇相應大小的轉(zhuǎn)置區(qū)域，而且還需用CTRL+SHIFT+ENTER三鍵結(jié)束公式，比較繁瑣。

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

A11單元格函數(shù)：

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

函數(shù)解析：

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

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

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

★★★?把A11單元格的函數(shù)向下拉動填充，A12單元格，COLUMN()=1，1-1=0，行數(shù)不偏移。ROW()=12，12-11=1，OFFSET的第三參數(shù)為1，說明列數(shù)從基礎(chǔ)點A1向右偏移一個位置，引用的是B1單元格的值（我們公式中的A1之所以使用絕對引用，是因為我們所有的單元格都是以A1為基礎(chǔ)點）。

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

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

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

C12單元格函數(shù)：

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

函數(shù)解析：

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

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

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

函數(shù)解析：

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

F2單元格函數(shù)：

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

因為科目一共有三個，所以可以確定同一個姓名需要出現(xiàn)三次，那么當我們下拉F2單元格填充函數(shù)的時候，就要保證OFFSET函數(shù)的行偏移量每3個單元格的參數(shù)值都是一樣的。這里就需要有一個“除數(shù)取整”的數(shù)學思維了，我們列個圖來輔助說明：

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

第二步：給同一個人分配不同的科目

G2單元格函數(shù)：

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

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

如上圖所示，序號通過MOD函數(shù)的轉(zhuǎn)換，得到一個順序、循環(huán)羅列的序號。將該序號作為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ū)域中所對應的序號，作為OFFSET的偏移量，分別放入第二、三參數(shù)中。從基準點A1單元格偏移后的單元格，就是我們需要的成績值。

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

【編后語】如果你是剛學習OFFSET函數(shù)的同學，我相信這個函數(shù)對于你來說應該不好理解，可我還是建議你，一定要多練習，要學會它。不要怕出錯，從錯誤中可以發(fā)現(xiàn)很多的問題，也可以鞏固你對一個函數(shù)的認知。

＊＊＊＊＊＊＊＊＊

下一篇OFFSET函數(shù)的文章我們將進行一些高級的用法說明，絕對是你工作中會使用到的，所以如果你今天沒有看懂，不要急，再看再體會，當然也可以來找老師，我們一起努力學會它。

＊＊＊＊部落窩教育－excel函數(shù)OFFSET應用＊＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng