小編有話說：都說靈感來源于生活，大家每天瀏覽網(wǎng)頁時有沒有想過讓自己的excel表格也變得像網(wǎng)頁一樣呢？只要輸入關鍵字，表格數(shù)據(jù)和圖表就會自動改變，特別是公司的數(shù)據(jù)需要分部門統(tǒng)計時，簡直太方便啦！

在我們?yōu)g覽網(wǎng)頁的時候，網(wǎng)頁會根據(jù)我們的選擇來展示不同的內(nèi)容，有沒有想過，把excel表格也做成“輸入關鍵字，就自動顯示對應的數(shù)據(jù)和圖表”的動態(tài)表格呢？比如我們要做工資統(tǒng)計時，把部門作為關鍵字，每輸入一個部門名稱，就自動顯示該部門對應的工資明細和統(tǒng)計圖表。

最近世界杯很火，我們就拿世界杯統(tǒng)計數(shù)據(jù)舉例，最終實現(xiàn)的效果如下：

一、學習目標

動態(tài)圖表是一種根據(jù)我們的選擇來實時展示不同信息的圖表。此次我們以世界杯小組賽各個球隊的比賽成績?yōu)閿?shù)據(jù)制作動態(tài)圖表，原始數(shù)據(jù)如下：

實現(xiàn)目標需要滿足的條件是：

1.有一個下拉框，里面有A-H組8個選項；

2.選中任意一個選項，就能自動顯示該小組的數(shù)據(jù)，并自動繪制出相對應的柱形圖；

二、制作方法

1.處理原始數(shù)據(jù)

在我們得到數(shù)據(jù)之后，一般都需要對原始數(shù)據(jù)進行一定的處理，才能作為制圖數(shù)據(jù)。

如上圖，在A列前插入新的一列，用來區(qū)分同一小組中不同的球隊。在單元格A3中輸入公式：

=B3&COUNTIF(B$2:B3,B3)。雙擊向下填充，就可以得到上圖的結果。

1.COUNTTF(計數(shù)區(qū)域,計數(shù)條件)：

如果在計數(shù)區(qū)域中的單元格滿足計數(shù)條件，則加1。就A3單元格的公式來分析，在B2:B3這個區(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)的結果如下圖：

   

    此時可以看到A列的數(shù)據(jù)中，每個小組只有前兩個隊是正確的結果。這是因為計數(shù)區(qū)域單元格沒有絕對引用，向下填充公式時，計數(shù)區(qū)域會自動改變行號，依次為B2：B3，B3:B4，B4:B5……，大家會發(fā)現(xiàn)，這時候的所有計數(shù)區(qū)域都是2個單元格，所以在A列中最大的號數(shù)就是2。

要想讓計數(shù)累計，就需要鎖定計數(shù)區(qū)域開始單元格的行號，所以公式中計數(shù)區(qū)域是B$2:B3，向下拖動公式時，依次為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,點擊菜單欄的“數(shù)據(jù)”，點擊“數(shù)據(jù)驗證”，在彈窗中點擊“數(shù)據(jù)驗證“

在彈窗中，設置“允許”為列表，“源”為“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)容復制到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組的第一個球隊名稱，第一參數(shù)為$M$1&ROW(A1)，得到查找值A1，第二參數(shù)為$A$3:$K$34，表示在源數(shù)據(jù)的A3-K34單元格區(qū)域查找，第三參數(shù)為3+COLUMN(A1)-COLUMN($A1)，由于我們的公式要向右拉，所以要找一下數(shù)學規(guī)律，從M3單元格開始，依次需要返回的列號是3+0、3+1、3+2、3+3……，所以M3單元格公式的第三參數(shù)是

3+COLUMN(A1)-COLUMN(A1)，此時再右拉一格，N3單元格公式的第三參數(shù)變成了

3+COLUMN(B1)-COLUMN(B1)，此時我們要想辦法讓3后面的兩個列號相減得到1，使用B1列號減A1列號就可以得到1，所以需要將第二個COLUMN的引用單元格列號鎖定，所以最終得到公式第三參數(shù)為3+COLUMN(A1)-COLUMN($A1)。

4.IFERROR(正確時返回值，錯誤時返回值)函數(shù)

其作用是定義發(fā)生錯誤時的應對措施。正確時返回第一參數(shù)，錯誤時返回第二參數(shù)。

五、制作柱形圖

選中數(shù)據(jù)M3:U6,點擊插入選項，選擇柱形圖，修改圖表標題，就可得到A組球隊的成績柱形圖如下。

好啦，今天的教程就到這里，你做出來了嗎？快試試修改單元格M1的內(nèi)容為B,看看數(shù)據(jù)區(qū)域M3:U6是否會變化，柱形圖是否會自動變化，如果變成了下面的樣子，則表明你制作的動態(tài)圖表成功啦~

           

 

本文配套的練習課件請加入QQ群：316492581下載。

如果您因工作所需使用到Excel，不妨關注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學習。

相關推薦：

《excel量筒式柱形圖繪制案例教程》