編按：

哈嘍，大家好！本篇教程是做懂領(lǐng)導(dǎo)心思圖表的第三篇，分享多系列數(shù)據(jù)圖表的制作經(jīng)驗(yàn)。多系列數(shù)據(jù)圖表的制作，在工作中，一直是個(gè)難題。由于數(shù)據(jù)系列較多，做出來(lái)的圖表看著總顯得混亂，各數(shù)據(jù)之間的對(duì)比性也不強(qiáng)，基本違背了我們作圖的初衷。那有沒(méi)有什么好的思路或者方法來(lái)制作多系列的圖表呢？下面我們就來(lái)看看excel高手是如何進(jìn)行制作的。

【前言】

很多同學(xué)都問(wèn)過(guò)作者E圖表述，如何才能快速的學(xué)到EXCEL。這個(gè)話題，好像從EXCEL2003的年代，就一直有人問(wèn)。其實(shí)說(shuō)簡(jiǎn)單也不簡(jiǎn)單，說(shuō)難也不是很難，最主要的是，你每天能和EXCEL有多少的互動(dòng)，并且這份互動(dòng)的內(nèi)容是不是總會(huì)有新的方式來(lái)呈現(xiàn)。

什么意思呢？換一種方法來(lái)說(shuō)，現(xiàn)實(shí)生活中，你對(duì)周圍的人簡(jiǎn)單的分個(gè)類：“認(rèn)識(shí)的”和“不認(rèn)識(shí)的”，那么你總結(jié)一下，什么樣的人是你認(rèn)識(shí)的呢？是不是那些和你有過(guò)接觸的人就會(huì)是認(rèn)識(shí)的，另外沒(méi)有接觸過(guò)的就有可能是不認(rèn)識(shí)的了；再在“認(rèn)識(shí)的”人中再分類：“熟悉的”和“不熟悉的”，那是不是那些經(jīng)常在工作中或生活上頻繁接觸的人就會(huì)是熟絡(luò)的人，另外的不太有交集的人，即便是認(rèn)識(shí)也不會(huì)是很熟的人；再在“熟悉的”人中再分類：“知心的”和“好朋友”，不用再分析了吧，都是成年人了，這些就是同學(xué)們應(yīng)該了解的“處世之道”。想融入一個(gè)“圈子”，就要多和圈子里的人用心地去接觸去互動(dòng)去交流。

【正文】

學(xué)習(xí)EXCEL其實(shí)和與人交往的過(guò)程很像，同學(xué)們和EXCEL接觸的時(shí)間長(zhǎng)短，接觸的內(nèi)容廣泛度，對(duì)它的用心程度，這些就能把EXCELER們區(qū)分為“認(rèn)識(shí)”“熟悉”“知心”的等級(jí)。

對(duì)于作者：E圖表述來(lái)說(shuō)，我應(yīng)該算是EXCEL的好朋友吧，因?yàn)槲覀儭耙?jiàn)面”的機(jī)會(huì)相當(dāng)?shù)亩?，平均每天我都?huì)使用10個(gè)小時(shí)以上，涉及的內(nèi)容也很全“操作、函數(shù)、數(shù)透、圖表、VBA ”，所以……，希望你能GET到我的“學(xué)習(xí)方法”。

前些日子有一位學(xué)生，找我問(wèn)一個(gè)圖表的問(wèn)題，下面是她的原圖：

這是一個(gè)發(fā)刊閱讀量的計(jì)劃/實(shí)際的數(shù)量對(duì)比圖，這個(gè)圖表中有幾個(gè)問(wèn)題：

1.堆積柱形圖系列中，計(jì)劃數(shù)量和實(shí)際數(shù)量是混著的，不能快速?gòu)膱D表中找到相應(yīng)的數(shù)據(jù)；

2.代表各月累計(jì)數(shù)量的走勢(shì)折線，沒(méi)有分布到相對(duì)應(yīng)的各個(gè)月份上，造成觀感上的錯(cuò)覺(jué)；

3.主標(biāo)題不夠明顯，對(duì)于各個(gè)系列的數(shù)據(jù)對(duì)比不能一目了然。

【設(shè)計(jì)制作新圖表】

根據(jù)上面的這些內(nèi)容，E圖表述也給出了一些建議，同時(shí)做了一份“組合圖”，如下所示：

【制圖思路】

看到這樣的一個(gè)數(shù)據(jù)源，按照觀看者比較容易能夠接受的圖表習(xí)慣來(lái)說(shuō)，對(duì)比類的最好是柱形圖，走勢(shì)類的最好是折線圖。

在這樣的一個(gè)思路基礎(chǔ)上，作者沒(méi)有使用其他圖表的表現(xiàn)形式，延用了慣性思維，但是有一個(gè)原則要堅(jiān)持：一個(gè)圖表中的信息量不要太多，否則會(huì)顯得很亂，而且讓觀閱者，不容易抓到重點(diǎn)信息。

所以，我們采用了一組組合圖表來(lái)表達(dá)每一個(gè)系列的值。

【制作過(guò)程】

步驟1：清洗數(shù)據(jù)。

新建一個(gè)空白工具表，使用函數(shù)將源數(shù)據(jù)整理成我們需要的數(shù)據(jù)格式。

在B3單元格輸入函數(shù)：

=ROUND(INDEX(發(fā)刊數(shù)據(jù)!$B$3:$Y$6,MATCH(B$1,發(fā)刊數(shù)據(jù)!$A$3:$A$6,0),MATCH($A3&B$2,發(fā)刊數(shù)據(jù)!$B$2:$Y$2,0))%%,2)

在C3單元格輸入函數(shù)：

=ROUND(INDEX(發(fā)刊數(shù)據(jù)!$B$3:$Y$6,MATCH(B$1,發(fā)刊數(shù)據(jù)!$A$3:$A$6,0),MATCH($A3&C$2,發(fā)刊數(shù)據(jù)!$B$2:$Y$2,0))%%,2)

在B3:I14單元格區(qū)域填充函數(shù)，得到我們的圖表數(shù)據(jù)源。

函數(shù)解析：通過(guò)兩個(gè)MATCH函數(shù)，在源數(shù)據(jù)中，索引出對(duì)應(yīng)的系列，再用ROUND函數(shù)，將源數(shù)據(jù)，轉(zhuǎn)化成以“萬(wàn)”為單位。

小知識(shí)

做圖表，別總是拿過(guò)來(lái)就做。多看看，如果數(shù)值超過(guò)10000，在圖表中就會(huì)顯得很亂，也不好讀數(shù)，所以用%%（除以10000）的方式減少數(shù)值位數(shù)的顯示，可以增加可讀性，提高圖表整體的觀感。

在J3單元格輸入函數(shù)：

=SUM(B3,D3,F3,H3)…月度計(jì)劃合計(jì)量

在K3單元格輸入函數(shù)：

=SUM(C3,E3,G3,I3)…月度實(shí)際和計(jì)量

在L3單元格輸入函數(shù)：

=SUM(B3,D3,F3,H3)+N(J2)…累計(jì)計(jì)劃量

在M3單元格輸入函數(shù)：

=SUM(C3,E3,G3,I3)+N(K2)…累計(jì)實(shí)際量

步驟2：制作圖表

選中A2:C14單元格區(qū)域，插入圖表——簇狀柱形圖。

選中系列圖形，鼠標(biāo)右鍵——設(shè)置系列格式，調(diào)整系列重疊為（0%），調(diào)整間隙寬度（50%）。

去掉無(wú)用元素，更改文本字體（微軟雅黑），更改數(shù)值字體（ARIAL），更改標(biāo)題內(nèi)容，添加表元素，最后再進(jìn)行一波配色：黃色（254、248、136），紅色（229、70、70），藍(lán)色（16、54、103）。下表的亮點(diǎn)在于，標(biāo)題部分將數(shù)據(jù)中重要的“類型”內(nèi)容體現(xiàn)了出來(lái)，方便閱讀者更直觀地進(jìn)行數(shù)據(jù)的對(duì)比，如下圖所示。

給它起個(gè)“好吃”的名字——“西紅柿雞蛋圖”。

分別再以對(duì)應(yīng)的數(shù)據(jù)源，制作AFS、AW、GMMC三個(gè)圖表，用“按住ALT鍵，再移動(dòng)圖表”的錨定方式，將4個(gè)小圖“組合”起來(lái)。

用同樣的方法制作最后的合計(jì)圖，注意的是，同一個(gè)圖表中制作累計(jì)折線選擇為“次坐標(biāo)軸”即可。

到這里，設(shè)計(jì)制作的過(guò)程完成了，但是有時(shí)候事情的發(fā)展總是不會(huì)按照我們?cè)O(shè)想的那樣進(jìn)行，這位學(xué)生又來(lái)和我“訴苦”了，領(lǐng)導(dǎo)不同意用這個(gè)圖，還是希望用原來(lái)的圖來(lái)做。

好吧，作者E圖表述前文已經(jīng)寫了之前的圖表設(shè)計(jì)是有問(wèn)題的，那么現(xiàn)在你的“雇主”非要“一意孤行”，怎么辦呢？做事不入東，累死也沒(méi)功。

【反轉(zhuǎn)，再設(shè)計(jì)】

為了區(qū)分計(jì)劃數(shù)量和實(shí)際數(shù)量，我一定要讓系列區(qū)分開來(lái)，走勢(shì)線也一定要對(duì)應(yīng)到各個(gè)月份，否則我都會(huì)認(rèn)為“圖表沒(méi)做到位”。

【第二次的制作過(guò)程】

步驟1：清洗數(shù)據(jù)

有的時(shí)候，同學(xué)們總會(huì)說(shuō)作者E圖表述的函數(shù)過(guò)程過(guò)于復(fù)雜，這是因?yàn)槲覀兛傄η髮⒐ぷ鞅碜龀伞白詣?dòng)化”的表格。今天為了方便同學(xué)們理解，就簡(jiǎn)化一下函數(shù)思考過(guò)程，介紹一種函數(shù)填充的方式。

在B3單元格輸入函數(shù)：

=ROUND(INDEX(發(fā)刊數(shù)據(jù)!$B$3:$Y$6,MATCH(LEFT($A3,LEN($A3)-2),發(fā)刊數(shù)據(jù)!$A$3:$A$6,0),MATCH(B$2,發(fā)刊數(shù)據(jù)!$B$2:$Y$2,0))%%,2)

函數(shù)思路和上面講到的一樣，大家自己理解一下。關(guān)鍵看下面說(shuō)的操作手法：

選擇B3:B6單元格區(qū)域，按CTRL+D，填充函數(shù)；再選擇B3:C6單元格區(qū)域，向右拉動(dòng)填充柄，填充到Y列。此時(shí)就形成了，隔一列空列的數(shù)據(jù)形式。

在C7單元格輸入函數(shù)：

=ROUND(INDEX(發(fā)刊數(shù)據(jù)!$B$3:$Y$6,MATCH(LEFT($A7,LEN($A7)-2),發(fā)刊數(shù)據(jù)!$A$3:$A$6,0),MATCH(I$2,發(fā)刊數(shù)據(jù)!$B$2:$Y$2,0))%%,2)

用同樣的方式填充到Y列。

在B11單元格輸入函數(shù)：

=IF(B3="",A11+(C11-A11)/2,SUM($B$3:B6))向右拉填充柄，填充函數(shù)到X11單元格。

函數(shù)解析：對(duì)于IF函數(shù)的使用，相信同學(xué)們?cè)诶斫馍喜粫?huì)有難度的。但是為什么要這樣做，其目的就在于，B11:X11單元格區(qū)域是代表累計(jì)計(jì)劃數(shù)量走勢(shì)折線圖的源數(shù)據(jù)，為了讓這條折線可以和柱形圖的月份對(duì)應(yīng)上，我們?cè)诳罩昧械牡胤剑恿艘粋€(gè)連接兩端數(shù)據(jù)的點(diǎn)，這樣可以使折線平滑上升。

在B12單元格輸入函數(shù)：

=IF(SUM(C7:Y10)=0,NA(),IF(B7="",SUM($C$7:C10),A12+(C12-A12)/2))向右拉填充柄，填充函數(shù)到X11單元格

函數(shù)解析：和B11單元格的作用是一樣的，代表了實(shí)際數(shù)量折線圖的數(shù)據(jù)源。這里的NA()，是指如果實(shí)際數(shù)量等于0的時(shí)候，不顯示數(shù)據(jù)點(diǎn)。

步驟2：制作主圖表

選中A2:Y10單元格區(qū)域，插入圖表——堆積柱形圖。

從這個(gè)圖中的系列圖形的顏色，大家不難發(fā)現(xiàn)，柱形系列已經(jīng)有了區(qū)分，之前的計(jì)劃柱形和實(shí)際柱形只能同時(shí)編輯，而現(xiàn)在就可以分開調(diào)整了。

去掉無(wú)用的圖表元素，排版，配色，一波操作我們就不詳細(xì)的說(shuō)了，給大家一組配色方案吧。

因?yàn)樵陂_始的時(shí)候，發(fā)現(xiàn)源數(shù)據(jù)上有“売上、計(jì)畫、実績(jī)”這樣的文本，猜想是一個(gè)日企，那么就給他們配了一組“日本風(fēng)格的色系”。主顏色就是上面的配色，可以通過(guò)透明度、陰影、三維效果讓顏色有更多的變化。

步驟3：加入累計(jì)走勢(shì)折線

選中圖表“繪圖區(qū)”，單擊鼠標(biāo)右鍵，點(diǎn)選“選擇數(shù)據(jù)”，將累計(jì)數(shù)據(jù)系列添加進(jìn)圖表。

然后更改圖表類型，改成“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”。

再將折線改成“次坐標(biāo)顯示”，并通過(guò)改變線條顏色、粗細(xì)等，美化折線圖。

及此也就完成了我們的圖表制作。

【編后語(yǔ)】

對(duì)于一組數(shù)據(jù)的圖表表現(xiàn)，真的是仁者見(jiàn)仁智者見(jiàn)智，我覺(jué)得“西紅柿雞蛋圖”可以很好表現(xiàn)數(shù)據(jù)對(duì)比和走勢(shì)，但是人家領(lǐng)導(dǎo)不喜歡，那么我也只能按照他人的思路“再創(chuàng)作”。其實(shí)這個(gè)過(guò)程挺好的，我現(xiàn)在掌握的這些技能，就是在不斷的“做、思考、再做”的過(guò)程中，慢慢積累下來(lái)的，希望同學(xué)們不僅要學(xué)技能，更要掌握學(xué)習(xí)的方法。

本文配套的練習(xí)課件請(qǐng)加入QQ群：1043683754下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

用Excel制作曲線圖《年終報(bào)告，你需要這張Excel圖表來(lái)“撐場(chǎng)面”》

Excel折線圖新玩法《5個(gè)小步驟，excel折線圖比同事漂亮10倍》

Excel告白圖表《一個(gè)苦逼Excel技術(shù)直男的追女日記：520，我為你今夜不眠！》

Excel九宮格圖表《秀翻全場(chǎng)！用excel制作一份“九宮格”圖表》