首頁 > 文章 > 專題 > Excel函數學習之聊聊動態統計之王OFFSET()

Excel函數學習之聊聊動態統計之王OFFSET()

青灯夜游轉載: 2023-04-10 18:37:002935瀏覽

這篇文章帶大家了解人送外號「動態統計之王」的OFFSET函數！ OFFSET函數是一個非常實用的函數，它在下拉式選單、動態圖表、動態參考等操作中都具有不可替代的作用。毫不誇張的說Excel表格的高效，有相當一部分的功能來自OFFSET。

【前言】

#OFFSET函數是判斷Excel函數使用者是否進階的重要函數之一。在實際工作中，如果你需要對工作中的資料檔案進行系統化、自動化的建模，那麼勢必會使用這個函數。

【功能及語法】

OFFSET函數的功能是，以指定的引用為參考系，透過給定的偏移量傳回新的引用。

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

reference 是原始基礎點
rows 是偏移的行數，正數向下，負數向上，且零不變。
cols 則是偏移的列數，正數向右，負數向左，且零不變。
[height] 是基礎點偏移後，縱向擴展幾行，正數向下擴展，負數向上擴展。
[width] 是基礎點偏移後，橫向擴展數列，正數向右擴展，負數向左擴展。

如果不使用第四個和第五個參數（但不可以為零），則新引用的區域和原始基礎點大小一致。

原始基礎點可以是一個儲存格，也可以是一個區域。

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

Excel函數學習之聊聊動態統計之王OFFSET()

相信大家看這張圖都花了不少時間吧。我們可以先依照上圖的指引，將資料填入OFFSET函數中，實際操作一下，來看看是否和新區域的位址一致呢？

先測試下第一個例子，看看正數為參量的運行結果：

Excel函數學習之聊聊動態統計之王OFFSET()

#透過驗算，對黃色「新區域」中的值進行求和，等於256，與儲存格C15中的值一致，結果正確。如果同學想模擬這個數據，也可以選取C15單元格，再透過工具列中「公式——公式審核——公式求值」的功能，就能更直覺的看到OFFSET的回傳值。（函數中使用F9也是可以的，選取公式中OFFSET的函數部分，再按F9即可，這裡就不多講了。）

Excel函數學習之聊聊動態統計之王OFFSET()

再來測試下第二個例子，看看負數為參量的運行結果：

Excel函數學習之聊聊動態統計之王OFFSET()

大家可以用「公式求值」的方式，自己測試一下，看看OFFSET函數區域的返回值。

那麼知道了OFFSET的基本運作原理之後，它在實際的工作中就可以幫助我們進行很多的操作和運算，而且有了這個函數的參與，可以實現excel中很多自動化的效果。下面讓我們一起來看看OFFSET函數在實際操作中所扮演的強大角色！

一、初級常規用法

作為其他函數的區域引用，應該是OFFSET函數最基礎的用途了。 OFFSET函數並不是移動了單元格區域，而是傳回了一個偏移擴展後的區域位址。因此所有將引用區域作為參數的函數，都可以利用OFFSET函數的回傳值，例如我們上面的例子Sum（OFFSET（）），再例如下面這個例子：

Excel函數學習之聊聊動態統計之王OFFSET()

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

二、進階常規用法

#絕技①：模擬轉置TRANSPOSE函數

Excel函數學習之聊聊動態統計之王OFFSET()

我們在使用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函數的反向查詢功能

Excel函數學習之聊聊動態統計之王OFFSET()

#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單元格啦！

#絕技③：資料重設升級版－重排資料結構

Excel函數學習之聊聊動態統計之王OFFSET()

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

函數解析：

第一步：得到連續出現的名稱

F2單元格函數：

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

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

Excel函數學習之聊聊動態統計之王OFFSET()

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

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

G2單元格函數：

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

因為我們F列中的每個名字都出現了三次，這就決定了語文、數學、英語這三個科目需要順序、循環地羅列出來，同第一步的思路一樣，用「除數求餘」的數學思維來達到效果。

Excel函數學習之聊聊動態統計之王OFFSET()

如上圖所示，序號透過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是為了快速地統計分析數據，快速地提取出我們需要的內容。現在假設以下兩個場景：

場景一：

領導者安排了工作，統計某季度的銷售數據，我們馬上行動，用函數快速的製作報表;

場景二：

領導安排了工作，因為每季都需要統計銷售數據，所以我們早就提前製作了模板，至於什麼時候給出報表，就隨我們的了。切記，不要讓「中階領導」知道你的工作效率很高。

兩個場景，你會選擇哪一種處理方式呢？作者希望是第二個。

Excel函數學習之聊聊動態統計之王OFFSET()

想法決定了我們製表的格局，這是一個簡單的案例，當資料來源被修改後，相對應的季度資料也會自動做出調整。在複雜的模版中並不是所有的位置都會使用OFFSET函數，但對於動態引用資料區域的需求，用OFFSET函數來處理是絕對不會錯的。

四、典型用法範例

#特技4：製作動態下拉選單

#在數據在建模的過程中，我們經常會使用到下拉式選單（或是組合框控制項）。為了確保下拉內容的唯一性，我們會使用INDEX SMALL IF ROW的「萬金油」函數來去重提取數列中的資料。還記得我們在上篇講到的OFFSET函數取代INDEX函數的例子嗎？所以說，如果OFFSET函數可以取代Index函數使用的話，那麼OFFSET函數同樣也可以實現「萬金油」的過程。下面我們就一起來看看複雜的「下拉式選單」的製作過程。

步驟一：使用OFFSET函數去重提取唯一值的「萬金油」公式

Excel函數學習之聊聊動態統計之王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),<br>ROW(D1)),),"")

#萬金油公式不是我們今天要講的主題，就不展開講了。重要是為了讓大家知道OFFSET函數也是可以達到這樣去重的效果。

步驟二：在名稱管理器中使用OFFSET函數，建立資料來源

我們可以用Ctrl F3組合鍵，開啟名稱管理器窗口，然後新名稱，名稱設定為「區域”，引用位置為“D2:D15”，如下圖所示：

Excel函數學習之聊聊動態統計之王OFFSET()

#然後選擇G1單元格，按Alt D L組合鍵可以打開資料驗證設定框，在允許中選擇“序列”，在來源中輸入“=區域”，如下圖所示：

Excel函數學習之聊聊動態統計之王OFFSET()

#點擊確定按鈕，那麼我們G1單元格的下拉式選單就建立好了。但問題也來了，我們會發現有好多的空選項，這不是我們需要的。

Excel函數學習之聊聊動態統計之王OFFSET()

有的同學會說，名稱管理器中選擇D2:D5就可以了。是的，但是如果我們A列的區域中出現了新的數據，那麼下拉選單中的數據可就少了，所以此時我們依然使用OFFSET函數來處理這個問題。

更改名稱管理器中，「區域」的參考位置：

Excel函數學習之聊聊動態統計之王OFFSET()

#=OFFSET(動態下拉選單!$D$1,1, ,COUNTA(動態下拉選單!$D$2:$D$15)-COUNTBLANK(動態下拉選單!$D$2:$D$15),1)

因為我們D列的唯一值，是用公式得到的，裡面的“空單元格”不是名義上的“空”，而是由公式得到的空，所以不能直接通過COUNTIF(D2:D15, "")的方式得到有值的儲存格個數。因此我們先使用了COUNTBLANK函數（空值單元格計數），統計空值單元格的數量，再用COUNTA函數統計非空單元格的數量，最後二者相減就得到了有值單元格的個數。將所得的結果作為OFFSET函數的第四參數（新區域的擴展行數）使用，就實現了動態引用有效資料的效果。如下圖所示：

Excel函數學習之聊聊動態統計之王OFFSET()