編按：哈嘍，大家好！最近，一位excel學(xué)習(xí)群里的同學(xué)在群里吐槽了他們領(lǐng)導(dǎo)，說領(lǐng)導(dǎo)讓他在一個(gè)圖中展示出三個(gè)不同的項(xiàng)目，在12個(gè)月份中的數(shù)據(jù)值、走勢，還要表現(xiàn)出各項(xiàng)目分別在本月中占所有項(xiàng)目數(shù)據(jù)總和的比例！聽到需求后，這位同學(xué)心態(tài)直接崩了，心想“領(lǐng)導(dǎo)，要不你來試試？”今天我們就針對這位奇葩領(lǐng)導(dǎo)的需求，一起來制作一下這張excel圖表吧！——在實(shí)際工作中，難的不是沒有技術(shù)，而是難在領(lǐng)導(dǎo)五花八門的要求。

＊＊＊前言＊＊＊

在日常工作中，數(shù)據(jù)對比類圖表我們經(jīng)常會遇到，數(shù)據(jù)走勢類圖表也屬于我們必會的圖表技巧，相信很多同學(xué)都能完成。但是最近有位同學(xué)的奇葩領(lǐng)導(dǎo)提出：“既要有對比效果、又要有走勢效果、還得是多系列全年按月份來展示圖表，并且這些都要做一個(gè)圖里！”

所以說，我們工作中，“難”不在沒技術(shù)，而在于腦洞大開的上司。那么今天，就在部落窩中給大家一個(gè)制作“多系列對比/走勢圖”的思路。

＊＊＊數(shù)據(jù)源＊＊＊

在我們看下面的文章之前，同學(xué)們可以自己考慮一下如果這樣的數(shù)據(jù)讓你來設(shè)計(jì)，你會做成什么樣子呢？

?

＊＊＊正文＊＊＊

●如果只有1組數(shù)據(jù)

沒啥說的，柱形圖、條形圖、環(huán)形圖，隨你用就好了。

即便是需要看到百分比和走勢線的話，我們在數(shù)據(jù)中的C列插入一列百分比的輔助列即可，如下：

STEP1：選擇單元格A1:B4區(qū)域，工具欄插入——圖表——簇狀柱形圖。

那么就得到了一個(gè)柱形圖的初稿。

STEP2：鼠標(biāo)右鍵點(diǎn)擊圖表區(qū)域任意位置，在彈出的菜單中，左鍵點(diǎn)選“選擇數(shù)據(jù)”。

在彈出的設(shè)置框中，點(diǎn)擊左側(cè)的“添加”系列，如下錄入數(shù)據(jù)或者選擇工作表區(qū)域，以輸入內(nèi)容。

STEP3：再在圖表區(qū)域任意位置，點(diǎn)擊鼠標(biāo)右鍵，選擇“更改圖表類型”。

將占比系列的圖表類型更改為“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”。

藉此得到了第二步驟的圖表雛形。

STEP4：選中圖中占比系列的圖形，點(diǎn)擊鼠標(biāo)右鍵，在菜單中選擇“設(shè)置數(shù)據(jù)系列格式”選項(xiàng)。

在系列選項(xiàng)中，點(diǎn)選“次坐標(biāo)軸”。

得到圖表，如下：

再美化一下圖表界面，添加數(shù)據(jù)標(biāo)簽，我們就不細(xì)說了，給個(gè)示例圖吧。?

●再來看今天的主要數(shù)據(jù)

不得不說，在實(shí)際工作中，圖表的制作還是應(yīng)該有重點(diǎn)、有突出的去表現(xiàn)某個(gè)數(shù)據(jù)，如果很多信息擠在一張圖中，會顯得混亂。

柱形圖和折線圖（作者在這里用的是散點(diǎn)圖）的組合是很常用的，而且這樣的組合，既有對比又有走勢，很方便觀看。那么我們今天的主要數(shù)據(jù)是不是也可以做成這樣的效果呢？如下圖示例：

?＊＊＊初期制圖規(guī)劃＊＊＊

如果我們決定使用柱形圖+折線圖的方式來表現(xiàn)這個(gè)表的話，那么我們需要在紙上，或是腦中有一個(gè)大致的草圖：每一組圖形對應(yīng)一個(gè)項(xiàng)目組12個(gè)月的數(shù)據(jù)。難點(diǎn)就在于，每一組圖形的底端要對齊；折線要在本項(xiàng)目圖形組中出現(xiàn)。

＊＊＊圖表類型選擇＊＊＊

很明顯，如果多個(gè)柱形圖疊加起來的話，我們應(yīng)該選擇“堆積柱形圖”，可是如果我們使用原始數(shù)據(jù)操作的話，就會是下面的圖表。

?各項(xiàng)目組的底端沒有對齊，這不是我們需要的，但如果我們在每個(gè)系列中間插入一個(gè)“占位圖形”，是不是就可以把各個(gè)系列圖形“抬高”呢？再把占位圖形的顏色和邊框設(shè)置為“無顏色”，就可以模擬出每行都是柱形圖，并且底端對齊的效果了，如下：

同學(xué)們可以看一下，這里我們已經(jīng)把A、B點(diǎn)占位圖形的顏色都去掉了，C點(diǎn)綠色的占位圖形還保留著，是不是可以看出來，底端已經(jīng)對齊了。

而有了占位的空位之后，我們就可以在這些空位上添加散點(diǎn)，用來標(biāo)記占比，最后再連接這些散點(diǎn)，模擬折線圖的效果。所以我們對于走勢線，采用“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”。

＊＊＊整理數(shù)據(jù)＊＊＊

?我們需要在兩個(gè)系列圖形中間，插入一個(gè)占位圖形，如果在數(shù)據(jù)表中考慮的話，就是加入一個(gè)輔助數(shù)據(jù)。下面我們來看一下數(shù)據(jù)的整理階段。

?“好圖表往往都不是源數(shù)據(jù)做出來的”，善于使用輔助數(shù)據(jù)，是做圖表進(jìn)階的一個(gè)重要標(biāo)志，所以也有說法“圖表的使用，最后都?xì)w結(jié)于函數(shù)的使用”。

上圖中“紅色”字，是系列的數(shù)據(jù)標(biāo)簽顯示值，而“藍(lán)色”字，就是圖表的占位圖形和散點(diǎn)的標(biāo)記點(diǎn)，逐一來看下每個(gè)數(shù)據(jù)的函數(shù)設(shè)計(jì)思路：

●?A：項(xiàng)目A的數(shù)據(jù)標(biāo)簽值

B8單元格函數(shù)：=B2

●?A點(diǎn)占位：項(xiàng)目A的占位圖形

B9單元格函數(shù)：=MAX($B$2:$M$4)*2-B8

說明：B2:M4是源數(shù)據(jù)區(qū)域，我們以數(shù)據(jù)中最大值的兩倍作為圖形中最高的比例參考，那么各個(gè)項(xiàng)目做出來的圖形就會比例均勻，其原理：數(shù)據(jù)點(diǎn)數(shù)值+對應(yīng)占位點(diǎn)數(shù)值=數(shù)據(jù)源中最大值*2，所以對應(yīng)占位點(diǎn)數(shù)值=數(shù)據(jù)源中最大值*2-數(shù)據(jù)點(diǎn)數(shù)值

●?B：項(xiàng)目B的數(shù)據(jù)標(biāo)簽值

B10單元格函數(shù)：=B3

●?B點(diǎn)占位：項(xiàng)目B的占位圖形

B11單元格函數(shù)：=MAX($B$2:$M$4)*2-B10

?●?C點(diǎn)占位：項(xiàng)目C的占位圖形

B12單元格函數(shù)：=B4

●?C點(diǎn)占位：項(xiàng)目C的占位圖形

B13單元格函數(shù)：=MAX($B$2:$M$4)*2-B12

●?A、B、C占比：項(xiàng)目A、B、C的數(shù)據(jù)點(diǎn)，分別在本月中占所有項(xiàng)目數(shù)據(jù)總和的比例。

B14單元格函數(shù)：=B2/SUM(B$2:B$4)

B15單元格函數(shù)：=B3/SUM(B$2:B$4)

B16單元格函數(shù)：=B4/SUM(B$2:B$4)

●?A占比點(diǎn)：A項(xiàng)目的走勢圖，對應(yīng)散點(diǎn)的坐標(biāo)位置。

B17單元格函數(shù)：=B8+B9*1/3

說明：散點(diǎn)圖，本身是需要橫縱兩個(gè)坐標(biāo)點(diǎn)的，如果我們不設(shè)置X軸，那么默認(rèn)的就是序號，如下：

?為了更貼合柱形圖，我們的Y軸就設(shè)置成“對應(yīng)項(xiàng)目占位點(diǎn)的三分之一處”。

●?B、C占比點(diǎn)：B、C項(xiàng)目的走勢圖，對應(yīng)散點(diǎn)的坐標(biāo)位置。

B18單元格函數(shù)：=SUM(B8:B10)+B11*1/3

B19單元格函數(shù)：=SUM(B8:B12)+B13*1/3

說明：同學(xué)們要注意，因?yàn)楦鱾€(gè)項(xiàng)目的散點(diǎn)Y軸坐標(biāo)是有“高度”要求的，所以它們的數(shù)據(jù)源，是累加其他項(xiàng)目數(shù)據(jù)，再加上對應(yīng)占比點(diǎn)的三分之一位置，這樣比較貼近對應(yīng)的柱形圖。

＊＊＊圖表制作＊＊＊

最困難的數(shù)據(jù)整理部分已經(jīng)做完了，還記得我們做單組數(shù)據(jù)時(shí)是如何插入圖表的嗎？趕緊動手試一試吧！

藍(lán)色區(qū)域：柱形圖

選中A7:M13區(qū)域，插入“堆積柱形圖”，將A、B、C系列添加“數(shù)據(jù)標(biāo)簽”

黃色區(qū)域：散點(diǎn)圖

按照文章開頭中對單組數(shù)據(jù)設(shè)計(jì)圖表的操作步驟，重復(fù)其中的步驟2、3。

選中B17:M17區(qū)域，插入“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”

選中B18:M18區(qū)域，插入“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”

選中B19:M19區(qū)域，插入“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”

添加“數(shù)據(jù)標(biāo)簽”

添加散點(diǎn)圖的“數(shù)據(jù)標(biāo)簽”，并選中“數(shù)據(jù)標(biāo)簽”，在“設(shè)置數(shù)據(jù)標(biāo)簽格式”中，設(shè)置為按對應(yīng)“單元格中的值”顯示內(nèi)容：

如此這般，同學(xué)們就會得到如下的圖表“毛坯”。

＊＊＊后期整理＊＊＊

后期整理工作可以參考作者E圖表述的“一刪、二調(diào)、三排”的做法來處理。

1、刪除圖表中的“縱坐標(biāo)軸”和“刻度線”，以及不需要顯示的圖例。

2、調(diào)整柱形圖的顏色和平滑線的顏色，將不需顯示的占位柱形以及平滑線上的標(biāo)記點(diǎn)設(shè)置為無線條、無填充。

3、通過上述操作，圖表已經(jīng)基本成型了，選中柱形圖的某一個(gè)系列，調(diào)整間隙寬度為5%。

將柱形圖上的數(shù)據(jù)標(biāo)簽位置調(diào)整為軸內(nèi)側(cè)，平滑線上的數(shù)據(jù)標(biāo)簽位置調(diào)整為居中。

再調(diào)整下圖表中各個(gè)元素的位置，并更改字體、字體顏色等，就完成了我們上圖的效果。

＊＊＊編后語＊＊＊

對于圖表的制作建議大家：所有Excel的默認(rèn)效果都不要使用。圖表更像一個(gè)平面設(shè)計(jì)，每個(gè)人的審美不同，出來的效果也不同，但是“可視化”既然是要給人看的，就要講究美觀。最后分享今天圖文教程中，所使用的這一組配色方案，在“其他顏色——自定義”中輸入RGB值即可。

＊＊＊＊部落窩教育－excel多項(xiàng)組合圖表＊＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng