站長資訊網(wǎng)在之前的文章《Excel圖表學(xué)習(xí)之通過案例，聊聊怎么繪制量筒式柱形圖》中，我們了解了繪制量筒式柱形圖的方法。而今天我們再分享一個(gè)Excel圖表教程，聊一個(gè)讓Excel圖表像網(wǎng)頁一樣動起來的方法，只要輸入關(guān)鍵字，表格數(shù)據(jù)和圖表就會自動改變，特別是公司的數(shù)據(jù)需要分部門統(tǒng)計(jì)時(shí)，簡直太方便啦！
手機(jī)如何做表格：點(diǎn)擊查看
在我們?yōu)g覽網(wǎng)頁的時(shí)候，網(wǎng)頁會根據(jù)我們的選擇來展示不同的內(nèi)容，有沒有想過，把excel表格也做成“輸入關(guān)鍵字，就自動顯示對應(yīng)的數(shù)據(jù)和圖表”的動態(tài)表格呢？比如我們要做工資統(tǒng)計(jì)時(shí)，把部門作為關(guān)鍵字，每輸入一個(gè)部門名稱，就自動顯示該部門對應(yīng)的工資明細(xì)和統(tǒng)計(jì)圖表。
最近世界杯很火，我們就拿世界杯統(tǒng)計(jì)數(shù)據(jù)舉例，最終實(shí)現(xiàn)的效果如下：
一、學(xué)習(xí)目標(biāo)
動態(tài)圖表是一種根據(jù)我們的選擇來實(shí)時(shí)展示不同信息的圖表。此次我們以世界杯小組賽各個(gè)球隊(duì)的比賽成績?yōu)閿?shù)據(jù)制作動態(tài)圖表，原始數(shù)據(jù)如下：
實(shí)現(xiàn)目標(biāo)需要滿足的條件是：
-
1.有一個(gè)下拉框，里面有A-H組8個(gè)選項(xiàng)；
-
2.選中任意一個(gè)選項(xiàng)，就能自動顯示該小組的數(shù)據(jù)，并自動繪制出相對應(yīng)的柱形圖；
二、制作方法
處理原始數(shù)據(jù)
在我們得到數(shù)據(jù)之后，一般都需要對原始數(shù)據(jù)進(jìn)行一定的處理，才能作為制圖數(shù)據(jù)。
如上圖，在A列前插入新的一列，用來區(qū)分同一小組中不同的球隊(duì)。在單元格A3中輸入公式：
=B3&COUNTIF(B$2:B3,B3)。雙擊向下填充，就可以得到上圖的結(jié)果。
1、COUNTTF(計(jì)數(shù)區(qū)域,計(jì)數(shù)條件)：
如果在計(jì)數(shù)區(qū)域中的單元格滿足計(jì)數(shù)條件，則加1。就A3單元格的公式來分析，在B2:B3這個(gè)區(qū)域中，返回等于B3單元格的內(nèi)容（A字母）出現(xiàn)次數(shù)。可以數(shù)出來是1次，所以COUNTIF返回1；
2、為什么是(B$2:B3,B3)，而不是(B2:B3,B3)
我們先將公式改成=B3&COUNTIF(B2:B3,B3)，最終A列呈現(xiàn)的結(jié)果如下圖：
此時(shí)可以看到A列的數(shù)據(jù)中，每個(gè)小組只有前兩個(gè)隊(duì)是正確的結(jié)果。這是因?yàn)橛?jì)數(shù)區(qū)域單元格沒有絕對引用，向下填充公式時(shí)，計(jì)數(shù)區(qū)域會自動改變行號，依次為B2：B3，B3:B4，B4:B5……，大家會發(fā)現(xiàn)，這時(shí)候的所有計(jì)數(shù)區(qū)域都是2個(gè)單元格，所以在A列中最大的號數(shù)就是2。
要想讓計(jì)數(shù)累計(jì)，就需要鎖定計(jì)數(shù)區(qū)域開始單元格的行號，所以公式中計(jì)數(shù)區(qū)域是B$2:B3，向下拖動公式時(shí)，依次為B$2:B3，B$2:B4，B$2:B5……
3、&符號的作用
&的功能就是把單元格B3的內(nèi)容和COUNTTF函數(shù)返回的內(nèi)容拼接在一起。如A3單元格公式中，B3的內(nèi)容為“A”,COUNTIF函數(shù)返回1，所以最終為 “ A1 ”。
三、制作下拉框
選中單元格M1,點(diǎn)擊菜單欄的“數(shù)據(jù)”，點(diǎn)擊“數(shù)據(jù)驗(yàn)證”，在彈窗中點(diǎn)擊“數(shù)據(jù)驗(yàn)證”
在彈窗中，設(shè)置“允許”為列表，“源”為“A,B,C,D,E,F,G,H”，中間用英文逗號隔開。
選中單元格M1,就會出現(xiàn)下拉框
四、制作輔助數(shù)據(jù)
如下圖，我們以區(qū)域M2:U6為輔助數(shù)據(jù)區(qū)域，也是真正的制圖數(shù)據(jù)。制作輔助數(shù)據(jù)區(qū)域也很簡單，直接將源數(shù)據(jù)中C2-K2單元格的內(nèi)容復(fù)制到M2-U2單元格區(qū)域，然后在M3單元格輸入公式：
=IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3+COLUMN(A1)-COLUMN($A1),0),"")，右拉下拉可得到下表。
公式解析：
IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3+COLUMN(A1)-COLUMN($A1),0),"")
1.COLUMN( 指定單元格/單元格區(qū)域)函數(shù)
其作用是返回指定單元格或單元格區(qū)域的列數(shù)。如上，COLUMN(A1)就會返回1；
2.ROW(指定單元格/單元格區(qū)域)函數(shù)
其作用是返回指定單元格或單元格區(qū)域的行號。如上，ROW(A1)就會返回1；
3.VLOOKUP(查找值，查找區(qū)域，返回值列號，0)函數(shù)
在M3單元格中，要得到A組的第一個(gè)球隊(duì)名稱，第一參數(shù)為$M$1&ROW(A1)，得到查找值A(chǔ)1，第二參數(shù)為$A$3:$K$34，表示在源數(shù)據(jù)的A3-K34單元格區(qū)域查找，第三參數(shù)為3+COLUMN(A1)-COLUMN($A1)，由于我們的公式要向右拉，所以要找一下數(shù)學(xué)規(guī)律，從M3單元格開始，依次需要返回的列號是3+0、3+1、3+2、3+3……，所以M3單元格公式的第三參數(shù)是
3+COLUMN(A1)-COLUMN(A1)，此時(shí)再右拉一格，N3單元格公式的第三參數(shù)變成了
3+COLUMN(B1)-COLUMN(B1)，此時(shí)我們要想辦法讓3后面的兩個(gè)列號相減得到1，使用B1列號減A1列號就可以得到1，所以需要將第二個(gè)COLUMN的引用單元格列號鎖定，所以最終得到公式第三參數(shù)為3+COLUMN(A1)-COLUMN($A1)。
4.IFERROR(正確時(shí)返回值，錯誤時(shí)返回值)函數(shù)
其作用是定義發(fā)生錯誤時(shí)的應(yīng)對措施。正確時(shí)返回第一參數(shù)，錯誤時(shí)返回第二參數(shù)。
五、制作柱形圖
選中數(shù)據(jù)M3:U6，點(diǎn)擊插入選項(xiàng)，選擇柱形圖，修改圖表標(biāo)題，就可得到A組球隊(duì)的成績柱形圖如下。
好啦，今天的教程就到這里，你做出來了嗎？快試試修改單元格M1的內(nèi)容為B,看看數(shù)據(jù)區(qū)域M3:U6是否會變化，柱形圖是否會自動變化，如果變成了下面的樣子，則表明你制作的動態(tài)圖表成功啦~
相關(guān)學(xué)習(xí)推薦：excel教程
