在之前的文章《Excel圖表學(xué)習(xí)之通過(guò)案例，聊聊怎么繪制量筒式柱形圖》中，我們了解了繪制量筒式柱形圖的方法。而今天我們?cè)俜窒硪粋€(gè)Excel圖表教程，聊一個(gè)讓Excel圖表像網(wǎng)頁(yè)一樣動(dòng)起來(lái)的方法，只要輸入關(guān)鍵字，表格數(shù)據(jù)和圖表就會(huì)自動(dòng)改變，特別是公司的數(shù)據(jù)需要分部門統(tǒng)計(jì)時(shí)，簡(jiǎn)直太方便啦！

手機(jī)如何做表格：點(diǎn)擊查看

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

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

一、學(xué)習(xí)目標(biāo)

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

實(shí)現(xiàn)目標(biāo)需要滿足的條件是：

• 1.有一個(gè)下拉框，里面有A-H組8個(gè)選項(xiàng)；

• 2.選中任意一個(gè)選項(xiàng)，就能自動(dòng)顯示該小組的數(shù)據(jù)，并自動(dòng)繪制出相對(duì)應(yīng)的柱形圖；

二、制作方法

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

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

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

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

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

如果在計(jì)數(shù)區(qū)域中的單元格滿足計(jì)數(shù)條件，則加1。就A3單元格的公式來(lái)分析，在B2:B3這個(gè)區(qū)域中，返回等于B3單元格的內(nèi)容（A字母）出現(xiàn)次數(shù)?？梢詳?shù)出來(lái)是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ū)域單元格沒(méi)有絕對(duì)引用，向下填充公式時(shí)，計(jì)數(shù)區(qū)域會(huì)自動(dòng)改變行號(hào)，依次為B2：B3，B3:B4，B4:B5……，大家會(huì)發(fā)現(xiàn)，這時(shí)候的所有計(jì)數(shù)區(qū)域都是2個(gè)單元格，所以在A列中最大的號(hào)數(shù)就是2。

要想讓計(jì)數(shù)累計(jì)，就需要鎖定計(jì)數(shù)區(qū)域開(kāi)始單元格的行號(hào)，所以公式中計(jì)數(shù)區(qū)域是B$2:B3，向下拖動(dòng)公式時(shí)，依次為B$2:B3，B$2:B4，B$2:B5……

3、&符號(hào)的作用

&的功能就是把單元格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”，中間用英文逗號(hào)隔開(kāi)。

選中單元格M1,就會(huì)出現(xiàn)下拉框

四、制作輔助數(shù)據(jù)

如下圖，我們以區(qū)域M2:U6為輔助數(shù)據(jù)區(qū)域，也是真正的制圖數(shù)據(jù)。制作輔助數(shù)據(jù)區(qū)域也很簡(jiǎn)單，直接將源數(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)就會(huì)返回1；

2.ROW(指定單元格/單元格區(qū)域)函數(shù)

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

3.VLOOKUP(查找值，查找區(qū)域，返回值列號(hào)，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單元格開(kāi)始，依次需要返回的列號(hào)是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è)列號(hào)相減得到1，使用B1列號(hào)減A1列號(hào)就可以得到1，所以需要將第二個(gè)COLUMN的引用單元格列號(hào)鎖定，所以最終得到公式第三參數(shù)為3+COLUMN(A1)-COLUMN($A1)。

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

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

五、制作柱形圖

選中數(shù)據(jù)M3:U6，點(diǎn)擊插入選項(xiàng)，選擇柱形圖，修改圖表標(biāo)題，就可得到A組球隊(duì)的成績(jī)柱形圖如下。

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

相關(guān)學(xué)習(xí)推薦：excel教程