編按：

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

【前言】

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

一、高階應(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è)缇吞崆爸谱髁四０澹?/span>至于什么時(shí)候給出報(bào)表，就隨我們的便了。切記，不要讓“中層領(lǐng)導(dǎo)”知道你的工作效率很高。

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

思路決定了我們制表的格局，這是一個(gè)簡(jiǎn)單的案例，當(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è)公式比較長(zhǎng)，列出如下：

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)槲覀?span>D列的唯一值，是用公式得到的，里面的“空單元格”不是名義上的“空”，而是由公式得到的空，所以不能直接通過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ì)你今后制表的過程，將大有裨益。

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

OFFSET函數(shù)（上篇）《Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）》

OFFSET制作動(dòng)態(tài)圖表《不拘一格OFFSET動(dòng)態(tài)表》

制作三級(jí)下拉菜單《還不會(huì)做Excel三級(jí)下拉菜單？其實(shí)它跟復(fù)制粘貼一樣簡(jiǎn)單》