編按：哈嘍，大家好！在上篇文章中，我們初步學習了OFFSET的基礎和進階用法，掌握了用OFFSET轉(zhuǎn)置數(shù)據(jù)、逆向查找數(shù)據(jù)、二維表轉(zhuǎn)一維表等操作，今天我們就來學習一些關于OFFSET的高階用法：制作動態(tài)下拉菜單和動態(tài)圖表。相信一定會對大家的工作和學習大有幫助。

＊＊＊＊＊＊＊＊＊

【前言】在上篇文章中，我們了解了OFFSET函數(shù)的運算原理和各個參數(shù)的作用，并且我們也通過一些OFFSET的案例，了解了它的用途。那么本篇我們繼續(xù)來看看，OFFSET函數(shù)在實際工作中所能起到的強大效果吧。

一、高階應用的思路

（動態(tài)報表模板的原型）

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

場景一：領導安排了工作，統(tǒng)計某季度的銷售數(shù)據(jù)，我們馬上行動，用函數(shù)快速的制作報表；

場景二：領導安排了工作，因為每季度都需要統(tǒng)計銷售數(shù)據(jù)，所以我們早就提前制作了模板，至于什么時候給出報表，就隨我們的便了。切記，不要讓“中層領導”知道你的工作效率很高。

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

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

二、典型用法舉例

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

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

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

這個公式比較長，列出如下：

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ù)也是可以達到這樣去重的效果。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

引用位置函數(shù)：

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

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

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

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

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

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

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

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

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

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

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

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

＊＊＊＊部落窩教育－OFFSET函數(shù)制作動態(tài)圖表＊＊＊＊

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