Excel動態圖表方法大集合,總有一款適合你!

圖文 | 朱莉 來源 | 精進Excel

在這個萬物都要看顏值的年代,做個數據報表也要好看。如果這時候能做個會動的圖表,相信一定會加分不少。

動態圖表製作的方法很多,根據不同情況,可以使用數據透視圖加切片器,或者函數公式加上名稱管理等來實現。

以下會用數據透視圖和3個函數公式法分別舉例,操作略有不同。快來找一個適合自己的方法吧。

以下內容信息量較大,建議先收藏哦~~

01 數據透視圖法

數據透視圖如透視表一樣,適用於數量量大且格式規範的數據源。

通過透視圖做出的圖表與普通圖表之間一個很大的區別是,透視圖可以如透視表一樣,靈活的變換布局,以及排序和篩選。

通過透視圖做的動態圖表,就是使用了數據透視的切片器功能,直觀進行選項間的切換。

下面我們來說說製作步驟。

我們要處理的數據是一份銷售記錄,裡面包含銷售的城市、地區以及銷售量等。我們現在想要以城市作為選項,查看每一個城市各產品的銷量。

① 插入數據透視圖

滑鼠選中要透視的數據中的任意單元格,然後點擊「插入」選項卡下的「數據透視圖」,因為今天重點在圖,所以我選擇的是只創建數據透視圖。

② 將數據透視圖欄位,滑鼠左鍵點擊拖拽到下方的四個框中

方法就是,想讓哪個欄位出現在什麼位置,就將它拖到哪個框中。如下方動圖所示:

想要出現在圖中橫坐標軸位置的欄位,就把它拖到坐下角的軸(類別)框中,想要作為篩選查看的欄位就放置在右上角的圖例(系列)中。最後把要求和計算的「數量」拖到右下角的「值」區域。

③ 插入切片器

重點步驟,選中數據透視圖,Excel中自動感應出三個數據透視圖選項卡,然後單擊「插入切片器」,然後勾選「城市」。

這時候,在切片器上單擊任意的城市,透視圖中就會出現相應城市的數據啦。

如果覺得默認的透視圖外觀不夠美觀,可以對透視圖進行修改,比如可以對欄位按鈕單擊滑鼠右鍵,選擇隱藏欄位按鈕。

切片的外觀也可以修改,可以改成多列的排列,也可以修改按鈕或者切片器的大小等。

對圖表類型不滿意,也可以點擊「設計」選項卡-「更改圖表類型」,選擇合適的圖形。

02 函數公式法1 - INDIRECT

函數公式法在小批量的二維表格中比較適用。不同的公式用到的步驟略有差異,但最終目的都是要通過公式的選擇,來創造一個根據選項變話的區域,然後我們再用這個區域作圖即可。

知識點:

製作下拉列表;

批量創建名稱;

名稱管理器;

INDIRECT函數。

① 製作供選擇用的下拉列表

選中要製作下拉列表的單元格,點擊「數據」-「數據驗證」(數據有效性),「允許」中選擇「序列」,「來源」選擇左邊這一列城市名。

這個步驟同樣適用於後面幾種函數公式,後續不再贅述。

② 批量插入名稱

選擇除第一行標題外的所有行,點擊「公式」選項卡,在「定義的名稱」區域選擇「根據所選內容創建」,彈出的對話框選擇「最左列」。

我們可以看到,剛才這一步起到的效果。

下圖左上角的名稱框中,我們選擇任意城市後,表格中這個城市後面所有的單元格都被選中了。

也就是說這個城市,就是後面這幾個單元格的名字,城市名就代表這幾個單元格的。

③ 新建名稱

然後再次在「名稱管理器」中點「新建」,「名稱」輸入「銷量」,引用位置輸入:

=INDIRECT(函數公式法1!$J$3)

點擊確定,這時候名稱管理器中就創建好了一個叫「銷量」的名稱。

INDIRECT函數在這裡的作用是,將括弧里的文字,變成真正的單元格引用。

當J3單元格中是「成都」時,

=INDIRECT(函數公式法1!$J$3)

=INDIRECT(成都)

=B5:H5

所以「銷量」這個名稱代表的內容,當J3為「成都」時,就是B5到H5單元格的引用;

同理,當J3為「北京」時,就是B3到H3單元格的引用。

這樣,「銷量」就代表了一個根據J3單元內容隨時變化的區域。

④ 最後一步作圖

點擊「插入」-「圖表」中的「柱狀圖」(根據需要選擇圖形)

對著圖形單擊滑鼠右鍵,點擊「選擇數據」,「系列名稱」,可以選擇J3單元格,「系列值」中輸入:

=函數公式法1!銷量

然後點擊確定。(藍色部分「函數公式法1!」是工作表的名稱)

水平標籤選擇從B2單元格開始的第一行的標題。

這時,動態圖表就做好了。

03 函數公式法2 - OFFSET+MATCH

第一種函數公式,重點是靠兩次區域命名加INDIRECT函數來實現動態區域的引用。

第二種函數利用OFFSET函數自身的功能來實現偏移的效果。

知識點:

OFFSET函數;

MATCH函數;

名稱管理。

① 公式選項卡,新建名稱。

在「名稱」中輸入:「銷量2」(主要為了跟上一個區分開來)

然後在引用位置中輸入:

=OFFSET(函數公式法2!$B$2:$H$2,

MATCH(函數公式法2!$J$3,函數公式法2!$A$3:$A$16,0),0)

函數講解:

OFFSET函數語法如下:

OFFSET函數是以指定的引用區域為參考,通過給定偏移量得到新的引用,返回的區域既可以為一個單元格或單元格區域,也可以指定返回的行數和列數。

MATCH函數語法如下:

MATCH函數的作用是,找到某個值,在給定區域中的位置。(在第幾行或者第幾列)

在這次的例子中,

MATCH(函數公式法2!$J$3,函數公式法2!$A$3:$A$16,0)

就是查找J3單元格中的內容,在A3到A6區域中的第幾行,也就確定了OFFSET函數需要向下偏移幾行。

比如,當J3單元格中是「成都」時,MATCH函數找到「成都」在A3到A16,也就是這些城市列表中,在第3行。所以OFFSET函數,就以上圖藍色區域的標題行作為參考,向下偏移3行(也就是成都所在的行)。

=OFFSET(函數公式法2!$B$2:$H$2,

MATCH(函數公式法2!$J$3,函數公式法2!$A$3:$A$16,0),0)

第三參數為0,表示向右不偏移。

省略第4、5參數,則返回與第一參數相同大小的區域。

所以上述公式,就能根據J3單元格中內容的不同,返回J3內容在表格區域中的對應的數據。

② 插入圖表

步驟與函數公式法1相同。

04 函數公式法3 - VLOOKUP

上面兩種函數公式法,都是通過公式,生成一個根據J3內容實時變動的引用區域。下面這種方法,不直接生成引用區域,而是通過構造一個「輔助」的區域,區域固定不變,但是區域中的內容根據公式變化。這樣作圖時只需在「輔助」的區域上做即可。

知識點:

VLOOKUP函數。

① 構造「輔助」行

在區域下方的空白單元格中,A18單元格中輸入:

=J3

然後在B18到H18單元格中輸入:

=VLOOKUP($A$17,$A$2:$H$15,COLUMN(),0)

VLOOKUP函數語法如下:

作用就是找到A18單元格中的內容,在上面表格中,對應的值。

第一參數是要找誰,第二參數是在哪找,第三參數是返回第幾列的內容,第四參數是精確或模糊匹配。

這裡,第三參數嵌套了一個COLUMN函數,目的是使用當前的列號,作為VLOOKUP的第三參數,也可以直接手動輸入值,2,3,4……等。

這樣,當J3內容發生變化時,18行中相應的數值就會發生變化。

接下來就是對18行的數據進行製圖。

② 插入圖表

過程類似,不再贅述。作圖區域選擇18行即可。

動圖完成了,剩下的就是圖形的美化過程了,大家可以根據自己的喜好修改圖表布局、顏色等等。

以上就是今天教程的主要內容,大家都get到了嗎?歡迎留言告訴我,你還有什麼別的操作方法?

公眾號【精進Excel】後台回復:動圖,可下載此文章相關原文件。


推薦閱讀:

DAX查詢進階:駕駛DAX Studio
實戰②:如何用圖表來表現滿額減後原售價、單本折扣額、折後單價三者的關係
太神奇了,Excel居然還能做動畫版的《海上明月圖》,潮起潮落的海水、閃爍的星星、滾動的字幕,一樣不少
Excel數據可視化分析方法大全

TAG:MicrosoftExcel | MicrosoftOffice | 图表 |