這一節(jié)內(nèi)容介紹動(dòng)態(tài)交互圖表制作的核心內(nèi)容，就是取數(shù),。沒(méi)看過(guò)前兩節(jié)的朋友可移步《這樣用Excel動(dòng)態(tài)交互圖表,，還要什么前端？》,、《動(dòng)態(tài)圖表交互揭秘：制作選擇器的奧秘》,。首先要理解，圖表能動(dòng)起來(lái),，是作圖的這部分數(shù)據(jù)在動(dòng)態(tài)變化,，因此我們說(shuō)動(dòng)態(tài)圖表，其實(shí)是動(dòng)態(tài)數(shù)據(jù),，數(shù)據(jù)動(dòng)起來(lái)了,，圖表自然而然就跟著變化了。
不要破壞原數(shù)據(jù),，在原數(shù)據(jù)的基礎(chǔ)上建立一個(gè)取數(shù)區(qū)域,，取數(shù)區(qū)域是根據(jù)取數(shù)函數(shù)動(dòng)態(tài)取數(shù)的。當(dāng)然也可以不做輔助的取數(shù)區(qū)域直接讓圖表動(dòng)起來(lái)的方法,，見(jiàn)文末,。
最常用的函數(shù)，這個(gè)函數(shù)已經(jīng)寫(xiě)了無(wú)數(shù)遍,，每天工作也用了無(wú)數(shù)遍,，但還是要來(lái)啰嗦，因?yàn)樗鼘?shí)在是太重要了,，先來(lái)看看函數(shù)結(jié)構(gòu)：vlookup（Lookupvalue,，Table array，Col index num,，Range lookup）函數(shù)參數(shù)解釋說(shuō)明如下：Table array：從哪個(gè)區(qū)域/表找,。Col index num：選擇區(qū)域/表的第幾列,，默認(rèn)序號(hào)是從1開(kāi)始。Range lookup：0是精確查找,，1是模糊查找,。下面是使用vlookup函數(shù)取數(shù)的完整步驟：Step1：做選擇器和固定表頭。先做一個(gè)組合框,，組合框選擇的是城市,，單元格鏈接到D50單元格，也就是圖中標(biāo)黃的單元格,，表頭是日期,，要取的是日期固定下的不同城市的值,。
Step2：取數(shù)。先取城市,，在D51單元格中輸入公式“=VLOOKUP($D$50,$A$1:$M$32,2,0)”,。vlookup的第一個(gè)參數(shù)要查找的單元格，肯定是選擇器的結(jié)果即D50單元格,，第二個(gè)參數(shù)值在哪找,，當(dāng)然是在原數(shù)據(jù)中找，即$A$1:$M$32區(qū)域,，第三個(gè)找第幾列,，城市是第二列，最后一個(gè)參數(shù)是精確查找,。再來(lái)回顧一下上節(jié)內(nèi)容制作選擇器,，選擇器制作完選擇的結(jié)果通常是數(shù)字（1/2/3等）或邏輯值（TRUE/False），我們知道使用vlookup函數(shù)連接兩張表的時(shí)候,，兩張表必然有一個(gè)公共字段,，所以要在原數(shù)據(jù)中添加一列序號(hào)值，作為公共字段,。添加了序號(hào)列的原數(shù)據(jù)如下：
Step3：至此已經(jīng)完成一個(gè)數(shù)據(jù)的動(dòng)態(tài)變化了,，選擇器選擇不同的城市在D51單元格中就顯示相應(yīng)的城市，因?yàn)関lookup函數(shù)往右拖Excel沒(méi)法智能識(shí)別出你的用意,，所以如果你不嫌麻煩的話,，就在每一個(gè)固定日期下輸入相同的函數(shù)，只是列數(shù)選擇不一樣,。
一個(gè)一個(gè)改參數(shù)實(shí)在有點(diǎn)麻煩,，所以我們?cè)诠潭ū眍^日期的上方做一行輔助行，數(shù)字表示該列數(shù)據(jù)在原數(shù)據(jù)中的位置,。這樣vlookup函數(shù)就可以向右拖動(dòng)了,。
index（Array，Row num,，Column num）Index函數(shù)有3個(gè)參數(shù)：Step1：還是做表頭和選擇器,，就不多說(shuō)了。Step2：用index函數(shù)取數(shù),，在D52單元格中輸入公式“=INDEX(B2:B32,$D$50)”,，從B2:B32區(qū)域中找第$D$50行的值，注意B2:B32區(qū)域只有一列，所以就不填第幾列這個(gè)參數(shù)了,，$D$50依舊是組合框選擇的值,，這個(gè)函數(shù)可以右拖，是不是比vlookup函數(shù)好用許多呢,。
index還可以和match函數(shù)結(jié)合來(lái)取數(shù),。Index函數(shù)是取一個(gè)精確坐標(biāo)下對(duì)應(yīng)的值，而Match函數(shù)是用來(lái)確定一個(gè)值的坐標(biāo),，返回指定范圍內(nèi)值所在的序號(hào),，結(jié)構(gòu)如下：Match（Lookup value，Lookup array,，Match type）Lookup array：是要查找的值所在的區(qū)域Match type ：同vlookup一樣精確匹配0Step1：制作選擇器,，這里用數(shù)據(jù)有效性來(lái)制作。鼠標(biāo)放在B54單元格中,，選擇【數(shù)據(jù)】——【數(shù)據(jù)驗(yàn)證】,，驗(yàn)證條件選擇序列，來(lái)源是原數(shù)據(jù)的城市一列,，點(diǎn)擊確定就得到了B54單元格的數(shù)據(jù)有效性,。
Step2：先來(lái)看一下使用match函數(shù)的結(jié)果，在D53單元格中輸入公式“=MATCH(B54,B2:B32,0)”,，表示查找B54單元格這個(gè)值在B2:B32區(qū)域中是第幾行,，結(jié)果返回2，表示是第二行,。
Step3：用indext+match函數(shù)結(jié)合取數(shù),。在D54單元格中輸入公式“=INDEX(B2:B32,MATCH($B$54,$B$2:$B$32,0))”，表示在B2:B32這一列中取第幾行的值,，第幾行由match函數(shù)算出,，這個(gè)公式做好相對(duì)引用絕對(duì)引用后可以右拉。這兩個(gè)函數(shù)結(jié)合取數(shù)的方式用在對(duì)一個(gè)值直接取數(shù),，而不是選擇值對(duì)應(yīng)的數(shù)字取數(shù),。
以指定的引用為參照系，返回新的引用,，這個(gè)函數(shù)有點(diǎn)復(fù)雜了,，但學(xué)會(huì)了還是很有用的，該函數(shù)結(jié)構(gòu)如下：Offset（reference,rows,cols,height,width）,，有5個(gè)參數(shù)，分別表示：Reference：指定參照系起始位置,，表示從哪個(gè)位置開(kāi)始引用Rows：相對(duì)于起始位置,，向下偏移幾行同其他幾個(gè)取數(shù)函數(shù)一樣,，先制作選擇器,，如下圖所示，組合框選擇的結(jié)果在D50單元格中顯示,，在D55單元格中輸入公式“=OFFSET(B1,$D$50,0)”,，這個(gè)公式的意思是，從B1單元格開(kāi)始,，向下偏移$D$50單元格中顯示的行數(shù),，向右偏移0行，得到的值即為取出的值,。B1單元格是城市那一列字段的最開(kāi)頭的單元格,，$D$50是選擇器顯示的結(jié)果，后兩個(gè)參數(shù)不寫(xiě)也是可以的,。
offset動(dòng)態(tài)定義名稱(chēng) 接下來(lái)放個(gè)大招,，無(wú)需制作取數(shù)區(qū)域，制作完選擇器直接畫(huà)出動(dòng)態(tài)圖表,，就要用到offset動(dòng)態(tài)定義名稱(chēng)這個(gè)功能來(lái)實(shí)現(xiàn)了,。
在【公式】——【定義名稱(chēng)】中，名稱(chēng)命名為t_data,，就是要畫(huà)圖的數(shù),，在引用位置處填寫(xiě)公式“=OFFSET(Sheet1!$C$2,Sheet1!$A$5,1,1,11)”，表示從C2單元格開(kāi)始,，向下偏移A5單元格所顯示的行數(shù)（A5是選擇器的單元格鏈接,，這里組合框選擇了天津，顯示2,，就是向下偏移兩行）,，向右偏移1列，選擇1行,，選擇11列（因?yàn)檎麄€(gè)數(shù)據(jù)源有11列）,，這時(shí)就選擇上了天津市對(duì)應(yīng)的數(shù)據(jù)。
在做一個(gè)名稱(chēng)為b_data的offset偏移函數(shù),，引用位置處的公式為“=OFFSET(Sheet1!$C$2,Sheet1!$A$5,0)”,，就是將城市名選擇上。
對(duì)前兩行數(shù)據(jù)插入一個(gè)柱形圖,。
在柱子上右鍵【選擇數(shù)據(jù)】,，在彈出的【選擇數(shù)據(jù)源】對(duì)話框中，對(duì)【圖例項(xiàng)】進(jìn)行編輯
這一步很重要,，將系列名稱(chēng)改為剛剛編輯的動(dòng)態(tài)名稱(chēng),，b_data,，系列值改為動(dòng)態(tài)名稱(chēng)，t_data,，點(diǎn)擊確定,。
組合框選擇不同的值，圖表就跟著動(dòng)態(tài)變化,，如下動(dòng)圖所示,，還沒(méi)有另做輔助區(qū)域，是個(gè)大招,，不過(guò)也比較燒腦,，需要在腦海中構(gòu)想出offset函數(shù)偏移的過(guò)程。
Excel動(dòng)態(tài)圖表交互系列就到這里了,，see you next week~
提供入門(mén)級(jí)數(shù)據(jù)分析的學(xué)習(xí)路線規(guī)劃,，分享從Excel到統(tǒng)計(jì)學(xué)的干貨。數(shù)據(jù)分析是一項(xiàng)技能,，希望人人都能分析數(shù)據(jù),。 碼字不易，喜歡就點(diǎn)個(gè)右下角 在看 唄
|
