晉升excel高手的必學技能:“自定義名稱”(應用篇)
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2020-02-18 16:48:28點擊:3013
編按:
哈嘍,大家好!在上篇文章中,我們給大家簡單介紹了一些“自定義名稱”的基礎(chǔ)內(nèi)容,相信在座的小伙伴對“自定義名稱”已經(jīng)有了一個初步的認識,但是光講基礎(chǔ),不說應用怎么行!今天我們就一起來感受一下“自定義名稱”在實際應用中的魅力吧!
【前言】
在上篇的學習中,很多同學都反饋對于“自定義名稱”有了很大的認識,也學到了很多的內(nèi)容。但是對于作者E圖表述來說,如果只講基礎(chǔ)不講應用,那就是“耍流氓”!
所以如果覺得上篇內(nèi)容對同學們有所幫助的話,那么本篇內(nèi)容,我們就一起來學習一些在實際工作中,常用而且實用的“自定義名稱”的應用。
一、多級下拉菜單
這個技巧是作者第一個想給大家介紹的功能,因為多級下拉菜單對于很多崗位上錄入數(shù)據(jù)源都是一個非常方便的操作,而且規(guī)范錄入的內(nèi)容,對于后期的數(shù)據(jù)統(tǒng)計分析有著舉足輕重的作用。
還記得以前我們說過的“三類表格”的文章嗎?《源數(shù)據(jù)表》、《信息表》、《統(tǒng)計展示表》,而我們對于建立多級下拉菜單來說,就一定要建立一個可以被引用的《信息表》,這樣比較方便我們?nèi)蘸髮?shù)據(jù)的更新。我們以省市縣的行政劃分為例,需要做一個下圖這樣的《信息表》。
限于篇幅,我們就用“京津冀”來做這個案例,同學們可以看到我們給單元格區(qū)域填充了顏色。從第一行的顏色開始我們做了一個某級下拉菜單的標識:黃色的部分是“一級下拉菜單”的內(nèi)容;綠色的部分是“二級下拉菜單”的內(nèi)容;藍色是“三級下拉菜單”的內(nèi)容。
下面我們需要對每列數(shù)據(jù)分別制作對應的“自定義名稱”,“名稱”是此列首行單元格顯示的值,“引用范圍”是此列第2行開始向下的單元格區(qū)域。如果每一列都挨著做一遍,那就太費時間了,況且這還只是“京津冀”三個部分的數(shù)據(jù),所以我們這里給大家介紹一種比較方便的“批量制作自定義名稱”的過程。
選擇D1:R27區(qū)域,在“公式”選項卡下,點擊“根據(jù)所選內(nèi)容創(chuàng)建”。
我們在《上篇》文章中也提到過,然后會彈出一個選擇菜單。
在“根據(jù)下列內(nèi)容中的值創(chuàng)建名稱:”中勾選“首行”,再點擊“確定”按鈕,然后按Ctrl+F3組合鍵,你就會發(fā)現(xiàn)我們的創(chuàng)建工作完成了,如下所示:
這個方法操作比較簡單,但是卻有一個“詬病”,向下看同學們就明白了。
我們利用“數(shù)據(jù)有效性”創(chuàng)建“下拉菜單”:
步驟1:
選中B2單元格,按照Alt、A、V、V的順序,在鍵盤上依次按鍵(注意:這不是組合鍵,是工具欄對應快捷鍵),彈出“數(shù)據(jù)驗證”窗口,在“允許”的下拉列表中選擇“序列”,在“來源”中輸入“=省”。這個[省]就是我們剛才制作的一個“自定義名稱”。
此時的B2單元格中,就有了一個關(guān)于省份的下拉菜單。
步驟2:
選擇B3單元格,還是按照剛才的步驟,繼續(xù)添加二級下拉菜單,讓B3單元格中的備選項可以根據(jù)B2單元格的內(nèi)容自動更新。我們在數(shù)據(jù)驗證的來源中輸入=INDIRECT(B2)。
還記得我們最開始要建立的那個《信息表》嗎,其目的就是為了在層級之間建立聯(lián)系,父級的內(nèi)容即是子級的名稱,這樣我們就可以使用INDIRECT函數(shù)對父級單元格中的子級名稱進行引用。
步驟3:
同理,在B4單元格中制作數(shù)據(jù)驗證,應用B3單元格的子級名稱,形成第三級的下拉菜單,藉此完成,看一下效果吧。
看完上述動圖后,不知道同學們有沒有發(fā)現(xiàn)我們在前面提到的關(guān)于此方法的“詬病”?沒錯,用此方法建立下拉菜單時雖然簡便,但是下拉框中會出現(xiàn)空白選項,而且當選項內(nèi)容增加時,還需要修改名稱的范圍,不是很智能。想創(chuàng)建更加智能的下拉菜單,同學們還可以學習一下這篇文章《Excel進階之路必學函數(shù):動態(tài)統(tǒng)計之王——OFFSET(下篇)》
二、宏表函數(shù)的使用
在EXCEL的使用中,有一種叫做EXCEL4.0函數(shù),也叫“宏表函數(shù)”。我們先不說宏表函數(shù)都有什么內(nèi)容,在本章中我們只說跟宏表函數(shù)有關(guān)的一個問題:如果要使用宏表函數(shù),就一定要在“自定義名稱”中使用。
例如我們之前介紹過的 “將表達式轉(zhuǎn)換成結(jié)果值”,就使用了宏表函數(shù)EVALUATE;又例如“在工作表中制作目錄”,使用了宏表函數(shù)GET.WORKBOOK,等等。因為介紹此類的文章有很多,我們就不浪費篇幅了,有興趣的同學可以點擊鏈接學習一下,都是很經(jīng)典、實用的用法。
三、智能選取圖表數(shù)據(jù)源
最近發(fā)現(xiàn)越來越多的同學都喜歡做動態(tài)圖表,先不說動態(tài)圖表的優(yōu)缺點,但是有一種情況是一定會遇到的,如果我們經(jīng)常會增加圖表數(shù)據(jù)源,那每一次想要圖表顯示“齊全”,都要再調(diào)整一次圖表的引用范圍;同樣,如果需要刪除數(shù)據(jù)源內(nèi)容,就得調(diào)整圖表的引用范圍,否則圖表就會顯示有“空”的內(nèi)容。這時,我們依然可以使用“自定義函數(shù)”來達到真正的“圖表動態(tài)引用”。
我們來看一個數(shù)據(jù)源的范例:
我們要做一個“時間軸走勢圖”,這種圖表的數(shù)據(jù)源有一個特點,就是隨著時間的推移,會伴有增刪的操作,那如果每次都要更改引用范圍,勢必會給日常工作造成一定的影響。如果某天忘記更改引用范圍,圖表還有可能出錯。
步驟1:
我們利用函數(shù)來制作兩個“自定義函數(shù)”——TBRQ、TBSL
引用位置處的函數(shù)設(shè)置,是比較典型的OFFSET函數(shù)的使用,利用COUNTA函數(shù)確定引用的范圍,這樣就有了一個“隨動的數(shù)據(jù)源”。不熟悉這個用法的同學,可以看一下我們往期的教程《Excel進階之路必學函數(shù):動態(tài)統(tǒng)計之王——OFFSET(下篇)》
這兩個名稱我們會在后面告訴同學們在哪里使用。
步驟2:
建立圖表,我們本小節(jié)的主要內(nèi)容是自定義名稱在圖表中的使用,所以同學們就不要糾結(jié)用什么圖表了,我們就選擇折線圖。選中數(shù)據(jù)源區(qū)域,工具欄“插入——圖表——折線圖”,再選擇一種格式,小小的美化一下。
步驟3:
鼠標右鍵點擊圖表繪圖區(qū),在彈出的菜單中選擇“選擇數(shù)據(jù)”。
在彈出的“選擇數(shù)據(jù)源”窗口中,點擊左側(cè)的“圖例項:編輯”按鈕。
此時會彈出一個“編輯數(shù)據(jù)系列”窗口,在“系列值”列表框中,輸入我們剛才建立的“自定義名稱:TBSL”,再按“確定”按鈕。
步驟4:
再點擊右側(cè)的“水平軸標簽:編輯”。
在彈出的“軸標簽”窗口中,將“軸標簽區(qū)域”的值修改為“自定義名稱:TBRQ”。
藉此設(shè)置完畢,我們看一下效果吧!
【編后語】
今天的文章就到此結(jié)束了,但是對于“自定義名稱”的使用還遠遠沒有講完,更多的是靈活性,同學們記住一點,對于自定義名稱,只要能夠使用函數(shù)的地方就可以使用它。所以我們說:自定義名稱是EXCEL的“基礎(chǔ)部分”,因為它可以用函數(shù)來建立,更可以用于函數(shù)的引用,便利的內(nèi)存數(shù)組錄入和調(diào)取的特點,讓它有很多的用武之地,同學們趕快學起來吧。
本文配套的練習課件請加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:
相關(guān)推薦:
自定義名稱(基礎(chǔ)篇)《晉升excel高手的必學技能:“自定義名稱”(基礎(chǔ)篇)》
用宏表函數(shù)制作目錄《用GET.WORKBOOK函數(shù)實現(xiàn)excel批量生成帶超鏈接目錄且自動更新》
用自定義名稱制作下拉菜單《還不會做Excel三級下拉菜單?其實它跟復制粘貼一樣簡單》
OFFSET函數(shù)基礎(chǔ)篇《Excel進階之路必學函數(shù):動態(tài)統(tǒng)計之王——OFFSET(上篇)》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!