在之前的文章《Excel圖表學習之通過案例，聊聊怎么繪制量筒式柱形圖》中，我們了解了繪制量筒式柱形圖的方法。而今天我們再分享一個Excel圖表教程，聊一個讓Excel圖表像網頁一樣動起來的方法，只要輸入關鍵字，表格數據和圖表就會自動改變，特別是公司的數據需要分部門統計時，簡直太方便啦！

手機如何做表格：點擊查看

在我們瀏覽網頁的時候，網頁會根據我們的選擇來展示不同的內容，有沒有想過，把excel表格也做成“輸入關鍵字，就自動顯示對應的數據和圖表”的動態表格呢？比如我們要做工資統計時，把部門作為關鍵字，每輸入一個部門名稱，就自動顯示該部門對應的工資明細和統計圖表。

最近世界杯很火，我們就拿世界杯統計數據舉例，最終實現的效果如下：

一、學習目標

動態圖表是一種根據我們的選擇來實時展示不同信息的圖表。此次我們以世界杯小組賽各個球隊的比賽成績為數據制作動態圖表，原始數據如下：

實現目標需要滿足的條件是：

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

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

二、制作方法

處理原始數據

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

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

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

1、COUNTTF(計數區域,計數條件)：

如果在計數區域中的單元格滿足計數條件，則加1。就A3單元格的公式來分析，在B2:B3這個區域中，返回等于B3單元格的內容（A字母）出現次數。可以數出來是1次，所以COUNTIF返回1；

2、為什么是(B$2:B3,B3)，而不是(B2:B3,B3)

我們先將公式改成=B3&COUNTIF(B2:B3,B3)，最終A列呈現的結果如下圖：

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

要想讓計數累計，就需要鎖定計數區域開始單元格的行號，所以公式中計數區域是B$2:B3，向下拖動公式時，依次為B$2:B3，B$2:B4，B$2:B5……

3、&符號的作用

&的功能就是把單元格B3的內容和COUNTTF函數返回的內容拼接在一起。如A3單元格公式中，B3的內容為“A”,COUNTIF函數返回1，所以最終為 “ A1 ”。

三、制作下拉框

選中單元格M1,點擊菜單欄的“數據”，點擊“數據驗證”，在彈窗中點擊“數據驗證”

在彈窗中，設置“允許”為列表，“源”為“A,B,C,D,E,F,G,H”，中間用英文逗號隔開。

選中單元格M1,就會出現下拉框

四、制作輔助數據

如下圖，我們以區域M2:U6為輔助數據區域，也是真正的制圖數據。制作輔助數據區域也很簡單，直接將源數據中C2-K2單元格的內容復制到M2-U2單元格區域，然后在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( 指定單元格/單元格區域)函數

其作用是返回指定單元格或單元格區域的列數。如上，COLUMN(A1)就會返回1；

2.ROW(指定單元格/單元格區域)函數

其作用是返回指定單元格或單元格區域的行號。如上，ROW(A1)就會返回1；

3.VLOOKUP(查找值，查找區域，返回值列號，0)函數

在M3單元格中，要得到A組的第一個球隊名稱，第一參數為$M$1&ROW(A1)，得到查找值A1，第二參數為$A$3:$K$34，表示在源數據的A3-K34單元格區域查找，第三參數為3+COLUMN(A1)-COLUMN($A1)，由于我們的公式要向右拉，所以要找一下數學規律，從M3單元格開始，依次需要返回的列號是3+0、3+1、3+2、3+3……，所以M3單元格公式的第三參數是

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

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

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

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

五、制作柱形圖

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

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

相關學習推薦：excel教程