公司馬上就要做經(jīng)營分析會議，老板要找人幫忙做一張圖表，需求是這樣的：

?

一共 6 個門店的銷售數(shù)量、金額和利潤數(shù)據(jù)，要求將這些數(shù)據(jù)做成氣泡圖呈現(xiàn)。

?

PS：氣泡圖是表示 3 個變量關系的圖表，橫軸、縱軸和氣泡的大小分別表示一個變量。

在下面的數(shù)據(jù)中，每個門店的手機銷售數(shù)據(jù)有銷售數(shù)量、金額和利潤三項指標。

因此用氣泡圖來呈現(xiàn)較合適。

?

普通人做的圖表可能是這樣的：

由于是 6 個門店的數(shù)據(jù)，那就得每個門店做一張類似的圖表，一共要做 6 張氣泡圖表。哇，你看~

?

而深諳動態(tài)圖制作的同事，做的圖表可能是這樣的：

僅僅用一個圖表，就將多個門店的手機業(yè)績信息展示了出來。

?

如果你是老板你會 Pick 哪一種方案呢？

我想很多人都會不由自主地選擇第 2 種。

?

因為動態(tài)圖表使用了控件，交互性更強，老板想看哪個門店的數(shù)據(jù)，直接鼠標點擊就可以呈現(xiàn)了。

?

那么接下來，咱們就來學習如何借助函數(shù)和控件，做出這樣的圖表。

?

由于本篇教程目的，是讓大家掌握動態(tài)圖的制作原理，因此正文中的案例適當簡化了，更易理解。

?

用到的數(shù)據(jù)源如下：

最終要做出的效果圖如下：

動態(tài)圖表原理

在學習具體如何操作之前，咱們先來理解一下，借助函數(shù)和控件做出動態(tài)圖表的原理。

?

?通過 H1:H5 插入折線圖，H2:H5 中的數(shù)據(jù)變動導致折線圖變動；

?H2:H5 中的數(shù)據(jù)借助函數(shù)從 B2:E5 區(qū)域中取，函數(shù)的其中一個參數(shù)會用到 A7 單元格；

?A7 單元格的數(shù)字變動，導致函數(shù)取值的變動，進而導致 H2:H5 區(qū)域數(shù)據(jù)的變化；

?控件鏈接至 A7 單元格，選擇不同控件，導致 A7 單元格數(shù)值的變動。

操作步驟

理解了動態(tài)圖表背后的工作原理之后，我們通過以下 3 個步驟，就能做出動態(tài)圖表啦。

?

▌插入基礎圖表

先將 B2:B5 的數(shù)據(jù)復制到 H2:H5 中，根據(jù) H2:H5 的數(shù)據(jù)插入基礎的折線圖。

?

▌插入控件

?

這一步要用到的是【開發(fā)工具】選項卡中的【插入控件】命令。

?

如果功能區(qū)中沒有這一選項卡，則需要通過【文件】-【選項】-【自定義功能區(qū)】，將【開發(fā)工具】調(diào)用出來。

?

如下圖：

接下來就可以為圖表插入控件：選項按鈕（窗體控件），并設置控件的屬性，將其鏈接至任意一個單元格（此處鏈接至 A7 單元格）。

?

溫馨提示：在選中控件時，用單擊右鍵的方式選中，要比單擊左鍵選中更方便一些。

?

然后將 4 個控件的名稱分別改為 4 個門店的名稱：王府井、望京、西單、中關村（此步不再演示）。

?

此時再點擊控件時，A7 單元格的數(shù)值就會從 1 到 4 進行變化。

?

動圖演示如下：?

?

做到這一步，我們發(fā)現(xiàn)折線圖并沒有變動。

?

這是因為折線圖引用的數(shù)據(jù)區(qū)域是 H2:H5，而這個區(qū)域的數(shù)據(jù)，仍然是在第?步中復制的 B2:B5 區(qū)域的數(shù)值，是固定的。

?

▌借助函數(shù)動態(tài)引用數(shù)據(jù)

如果想讓圖表「動」起來，就需要借助函數(shù)使得 H2:H5，根據(jù) A7 單元格數(shù)值的變動，動態(tài)引用 B2:E5 區(qū)域的數(shù)值。

?

接下來介紹 3 個函數(shù)（OFFSET、CHOOSE、INDEX）實現(xiàn)這樣的目的，掌握其中一種即可。

?

??借助 OFFSET 函數(shù)

• =OFFSET(A2,0,A7,4,1)
  

以 A2 為參照，向下移動 0 行，向右移動 4（A7 單元格的值）列，取 4 行，1 列。

?

這個函數(shù)的意思是，H2 的值以 A2 為參照進行取值，向下移動 0 行指的是取當前行（A2 所在的行），向右移動 4 列，就到了 E2 單元格，取 4 行 1 列指的是取出 E2:E5 區(qū)域的值。

?

由此通過這一個函數(shù)就取出了 H2:H5 的值。

?

隨著控件按鈕的選擇，A7 單元格的值變動，使得 H2:H5 取出的值變動，從而導致圖表的動態(tài)變化。

?

操作演示如下：

??借助 CHOOSE 函數(shù)

用 CHOOSE 函數(shù)也能實現(xiàn)與 OFFSET 函數(shù)相同的效果，不同之處在于函數(shù)的用法。

?

• =CHOOSE($A$7,B2,C2,D2,E2)
  

指從 B2,C2,D2,E2 中取單元格的值，具體取哪一個呢，由第 1 個參數(shù)的值來決定，A7 當前的值為 4，這個公式代表的意思是：返回 B2,C2,D2,E2 中第 4 個的值（也就是 E2 單元格的值）。

?

其他操作與 OFFSET 的完全一致，接下來只對 CHOOSE 函數(shù)部分做操作演示：

?

??借助 INDEX 函數(shù)

我們還可以使用 INDEX 函數(shù)來取數(shù)，除了函數(shù)不同外，其他操作和上面的一樣。

• =INDEX(B2:E5,,A7)
  

從 B2:E5 單元格區(qū)域中取數(shù)，第 2 個參數(shù)指的是第幾行，這里此參數(shù)為空，代表每一行都要取出來。最后一個參數(shù)代表取第幾列，這里用的是 A7，此時值為 2，則代表取該區(qū)域的第 2 列。

?

于是通過這個函數(shù)就取出了 C2:C5 的數(shù)值。

?

操作演示如下：

以上就是這次分享的借助函數(shù)和控件，制作動態(tài)圖表的教程。

?

最后，我們一起來回顧一下制作動態(tài)圖表的操作步驟。

?

? 插入基礎圖表；

? 插入控件，并將其鏈接至同一單元格；

? 借助函數(shù)動態(tài)引用數(shù)據(jù)（本文介紹了 OFFSET、CHOOSE 和 INDEX 這三個函數(shù)，掌握其中一種即可）。

大家掌握動態(tài)圖表的做法了嗎？

歡迎大家一起留言交流你遇到的困難。

也可以一起討論你遇到那些技巧會讓圖表看起來更加清晰~

如果你想學習更多實用的 Excel 技巧，那么千萬別錯過這次的《3 天 Excel 集訓體驗營》！

用 3 大學習模塊，每天 1 小時，教你玩轉(zhuǎn) Excel！

《秋葉 Excel 3 天集訓營》

想提升，趁現(xiàn)在！

助你不再被加班支配，不再為表格發(fā)愁！

現(xiàn)在報名

還免費贈送【35 個常用函數(shù)說明】！

趕緊掃碼預約吧！

??????

*廣告