|
編按：哈嘍，大家好！在上篇文章中，我們初步學(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)圖表。相信一定會對大家的工作和學(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)容。現(xiàn)在假設(shè)以下兩個(gè)場景： 場景一：領(lǐng)導(dǎo)安排了工作，統(tǒng)計(jì)某季度的銷售數(shù)據(jù)，我們馬上行動(dòng)，用函數(shù)快速的制作報(bào)表； 場景二：領(lǐng)導(dǎo)安排了工作，因?yàn)槊考径榷夹枰y(tǒng)計(jì)銷售數(shù)據(jù)，所以我們早就提前制作了模板，至于什么時(shí)候給出報(bào)表，就隨我們的便了。切記，不要讓“中層領(lǐng)導(dǎo)”知道你的工作效率很高。 兩個(gè)場景，你會選擇哪種處理方式呢？作者希望是第二個(gè)。
思路決定了我們制表的格局，這是一個(gè)簡單的案例，當(dāng)數(shù)據(jù)源被修改后，相對應(yīng)的季度數(shù)據(jù)也會自動(dòng)做出調(diào)整。在復(fù)雜的模版中并不是所有的位置都會使用OFFSET函數(shù)，但對于動(dòng)態(tài)引用數(shù)據(jù)區(qū)域的需求，用OFFSET函數(shù)來處理是絕對不會錯(cuò)的。 二、典型用法舉例 絕技4：制作動(dòng)態(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ù)同樣也可以實(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單元格的下拉菜單就建立好了。但是問題也來了，我們會發(fā)現(xiàn)有好多的空選項(xiàng)，這不是我們需要的。
有的同學(xué)會說，名稱管理器中選擇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的下拉菜單也會增加新的選項(xiàng)，讓我們來一起看一下效果，我相信這是你需要的。
絕技5：OFFSET函數(shù)在圖表中的使用
上面的這張圖表，相信大家都不陌生吧。參加工作的同學(xué)們都會有制作圖表的經(jīng)歷，在上圖中選擇A1:B10區(qū)域，在工具欄——“插入”——柱形圖，就完成了我們圖例的內(nèi)容。 如果我們刪掉一行數(shù)據(jù)，那么柱形圖中的系列圖例就會少一個(gè)，可是如果增加一行數(shù)據(jù)的話，就需要更改圖表數(shù)據(jù)源的范圍，才能顯示正確的圖表。但總不能每次都更改呀，那就失去了我們使用Excel 高效快速的初衷。 此時(shí)，我們依然可以借鑒OFFSET函數(shù)來解決： 步驟一：使用OFFSET函數(shù)分別對“日期列”和“數(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è)絕對大的區(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ù)就會像上圖這樣顯示，OFFSET.xlsx是我們的工作薄名稱。效果如下：
其他的圖表類型也是可以的，大家可以試著操作一下，加深印象。 ＊＊＊＊部落窩教育－OFFSET函數(shù)制作動(dòng)態(tài)圖表＊＊＊＊ 原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載） |
|
|
