首頁 > 文章 > 專題 > Excel圖表學習如果讓圖表像網頁一樣動起來

Excel圖表學習如果讓圖表像網頁一樣動起來

青灯夜游轉載: 2022-08-16 10:30:112913瀏覽

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

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

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

Excel圖表學習如果讓圖表像網頁一樣動起來

一、學習目標

動態圖表是一種根據我們的選擇來即時展示不同資訊的圖表。這次我們以世界盃小組賽各隊的比賽成績為數據製作動態圖表，原始數據如下：

Excel圖表學習如果讓圖表像網頁一樣動起來

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

1.有一個下拉框，裡面有A-H組8個選項；
2.選取任一選項，就能自動顯示該小組的數據，並自動繪製出相對應的長條圖；

#二、製作方法

處理原始資料

在我們得到資料之後，一般都需要對原始資料進行一定的處理，才能作為製圖資料。

Excel圖表學習如果讓圖表像網頁一樣動起來

如上圖，在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列呈現的結果如下圖：

Excel圖表學習如果讓圖表像網頁一樣動起來

此時可以看到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,點擊選單列的“資料”，點選“資料驗證”，在彈視窗中點選「資料驗證」

Excel圖表學習如果讓圖表像網頁一樣動起來

在彈跳視窗中，設定「允許」為列表，「來源」為「A,B,C,D,E,F ,G,H”，中間用英文逗號隔開。

Excel圖表學習如果讓圖表像網頁一樣動起來

選取儲存格M1,就會出現下拉方塊

Excel圖表學習如果讓圖表像網頁一樣動起來

四、製作輔助資料

如下圖，我們以區域M2:U6為輔助資料區域，也是真正的製圖資料。製作輔助資料區域也很簡單，直接將來源資料中C2-K2儲存格的內容複製到M2-U2儲存格區域，然後在M3儲存格輸入公式：

=IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3 COLUMN(A1)-COLUMN($A1),0),"")，右拉下拉可得到下表。

Excel圖表學習如果讓圖表像網頁一樣動起來

公式解析：

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組球隊的成績長條圖如下。

Excel圖表學習如果讓圖表像網頁一樣動起來