用excel做一張更“懂”領(lǐng)導(dǎo)心思的圖表(下篇)
?
作者:E圖表述來(lái)源:部落窩教育發(fā)布時(shí)間:2020-03-07 00:41:20點(diǎn)擊:3040
編按:
哈嘍,大家好!在上篇文章中,我們說(shuō)到要去挖掘數(shù)據(jù)背后的意義,不能只是單純的將原始數(shù)據(jù),從數(shù)字變成圖表。今天我們又給大家?guī)?lái)了幾種更容易獲得領(lǐng)導(dǎo)賞識(shí)的制圖方法,趕緊來(lái)看看吧!
【前言】
在我們的實(shí)際工作中,通常會(huì)看到很多類似下面這種架構(gòu)的數(shù)據(jù)。
類似這樣的數(shù)據(jù)形式還有很多,比如各個(gè)生產(chǎn)線的產(chǎn)量、居委會(huì)各社區(qū)人口數(shù)量、同類公司營(yíng)業(yè)額數(shù)據(jù)等等,都屬于這一類內(nèi)容。這種匯總類的數(shù)據(jù)相對(duì)于我們?nèi)粘9ぷ鱽?lái)說(shuō),應(yīng)該是比比皆是的,而我們也經(jīng)常會(huì)處理到這一類數(shù)據(jù),如果同學(xué)們想要在自己的崗位上有所建樹(shù),作者E圖表述建議大家可以把學(xué)習(xí)的重點(diǎn)多放在這種常規(guī)的數(shù)據(jù)上,能在常規(guī)問(wèn)題上做出不常規(guī)的操作,才能顯現(xiàn)出我們和別人不一樣的優(yōu)越性。
【正文】
因?yàn)檫@樣的數(shù)據(jù)是非常常見(jiàn)的,那么領(lǐng)導(dǎo)就會(huì)經(jīng)常找我們要這些數(shù)據(jù),切記一點(diǎn)“報(bào)表的呈閱要盡力和清晰明了的圖表放在一起”,這樣才能有一個(gè)比較的直觀效果,那么同學(xué)你的圖表是什么樣子的呢?是不是也像下面這些,應(yīng)付差事就完了!(我們以上面展示的“示例2”的數(shù)據(jù)為例)
一定會(huì)有很多的同學(xué)把圖表做成這個(gè)樣子就OK了,上面4個(gè)圖的樣式就不在這里給大家介紹了,無(wú)外乎就是選定數(shù)據(jù)區(qū)域,在工具欄中點(diǎn)擊“插入”,再選擇合適的圖表就可以了。但是不得不說(shuō),這樣一份沒(méi)有重點(diǎn)的圖表,就連“中規(guī)中矩”都談不上吧,那么領(lǐng)導(dǎo)自然也就不會(huì)記住你做的什么工作,歸根結(jié)底就是將本身的數(shù)據(jù),從數(shù)字變成圖形而已,而這但凡是個(gè)會(huì)操作EXCEL圖表的人都可以完成,實(shí)在沒(méi)有什么可以被別人記住的地方!
▲好圖表往往都不是源數(shù)據(jù)做出來(lái)的,重要的話說(shuō)一遍▲
圖表說(shuō)明:
由系列積木堆積出一條隱形的等高線,代表12個(gè)月的平均數(shù)值,超出的部分用橙色標(biāo)記,不夠平均數(shù)的用灰色補(bǔ)足。
制作過(guò)程:
步驟1:
首先處理源數(shù)據(jù),如下圖所示:
在C2單元格輸入函數(shù):
=IF(B2<AVERAGE($B$2:$B$13),B2,AVERAGE($B$2:$B$13))
利用AVERAGE函數(shù)計(jì)算出12個(gè)月數(shù)據(jù)的平均值,再判斷B2單元格的值是否小于平均值,如果小于則返回B2的值,否則返回平均值。下拉填充柄填充函數(shù)到C2:C13區(qū)域。
在D2單元格輸入函數(shù):
=IF(B2<AVERAGE($B$2:$B$13),AVERAGE($B$2:$B$13)-B2,NA())
判斷B2單元格的值是否小于平均值,如果小于則返回平均值減去B2數(shù)值后的值,否則用NA函數(shù)返回#N/A值。這部分就是我們圖表中的灰色柱條,使其形成一種視覺(jué)上的占位效果。
在E2單元格輸入函數(shù):
=IF(B2>=AVERAGE($B$2:$B$13),B2-AVERAGE($B$2:$B$13),NA())
判斷B2單元格的值是否大于等于平均值,如果大于等于則返回B2數(shù)值減去平均值的值,否則用NA返回#N/A值。這部分表示如果數(shù)值大于平均值,就只顯示大于部分的柱形條,也就是我們的橙色部分。
步驟2:
選中C2:E13單元格區(qū)域,點(diǎn)擊工具欄“插入”——“圖表”——“堆積柱形圖”。
這樣我們的圖表就創(chuàng)建好了,然后就是排版的工作,這里就仁者見(jiàn)仁、智者見(jiàn)智,看同學(xué)們自己的偏好和排版功底了。
▲好圖表往往都不是源數(shù)據(jù)做出來(lái)的,重要的話說(shuō)兩遍▲
圖表說(shuō)明:
這個(gè)就是“旋風(fēng)圖”了,也被叫做“漏斗圖”,這樣的圖表最大的意義就是可以把無(wú)序的數(shù)據(jù)按升序或者降序目視化,讓觀閱者可以很清晰地看出最大值或者最小值。
制作過(guò)程:
步驟1:
首先處理源數(shù)據(jù),如下圖所示:
在C2單元格輸入函數(shù):
=INDEX($A$2:$A$13,MATCH(LARGE($B$2:$B$13,ROW(C1)),$B$2:$B$13,0))
函數(shù)首先使用了LARGE函數(shù)結(jié)合ROW函數(shù),確定了數(shù)據(jù)中第N個(gè)最大值的數(shù)值,再使用MATCH函數(shù)確定這個(gè)第N大的值在區(qū)域中的排列序號(hào),接著使用INDEX函數(shù)引用相對(duì)序號(hào)所對(duì)應(yīng)的A2:A13區(qū)域的月份,下拉填充柄填充函數(shù)到C2:C13單元格區(qū)域,這樣就將月份關(guān)鍵字按降序排列出來(lái)了。
在E2單元格輸入函數(shù):
=VLOOKUP(C2,$A$1:$B$13,2,0)
很常規(guī)的VLOOKUP函數(shù)的用法,將對(duì)應(yīng)月份的數(shù)值引用到E2:E13單元格區(qū)域。
在D2單元格輸入函數(shù):
{=MAX($E$2:$E$13/2)-E2/2}
這里用了一個(gè)數(shù)組函數(shù),表示E2:E13單元格區(qū)域的值都除以2之后的最大值,再減去E列中對(duì)應(yīng)行的單元格除以2的值,最后下拉填充柄填充函數(shù),就得到了這樣一組數(shù)據(jù)。它的作用是在條形圖中形成圖形占位,再把此部分的圖形“填充顏色、邊框”都設(shè)置無(wú)顏色即可。
步驟2:
選中C2:E13單元格區(qū)域,在工具欄中點(diǎn)擊“插入”——“圖表”——“堆積條形圖”:
藉此完成,記得把占位的輔助圖形的填充顏色和邊框,都設(shè)置為無(wú)顏色,你的旋風(fēng)圖就做好了。
▲好圖表往往都不是源數(shù)據(jù)做出來(lái)的,重要的話說(shuō)三遍▲
圖表說(shuō)明:
“半圓餅圖”可以讓觀者在從左到右的瀏覽,如果是一個(gè)圓環(huán)的話,勢(shì)必會(huì)讓觀者上下左右地看,這樣容易產(chǎn)生視覺(jué)疲勞。我們同樣以上例排序的效果來(lái)做出餅圖,這樣可以使圖表更有層次感,讓數(shù)據(jù)更加的有重點(diǎn)。
制作過(guò)程:
步驟1:
首先處理源數(shù)據(jù),如下圖所示:
在C2單元格輸入函數(shù),下拉填充到C2:C13單元格區(qū)域
=INDEX($A$2:$A$13,MATCH(LARGE($B$2:$B$13,ROW(C1)),$B$2:$B$13,0))
和上例“旋風(fēng)圖”是一樣的,在這里就不做說(shuō)明了。
在D2單元格輸入函數(shù),下拉填充到D2:D13單元格區(qū)域
=VLOOKUP(C2,$A$2:$B$13,2,0)
在D14單元格輸入函數(shù)(或者直接使用“ALT =”組合鍵做匯總)
=SUM(D2:D13)
做到這里基本上就已經(jīng)完成餅圖源數(shù)據(jù)的處理了,選中C2:D14單元格區(qū)域,在工具欄中點(diǎn)選“插入”——“圖表”——“餅圖”。
這里注意一下,有可能在你選擇插入圖表后,只得到了一個(gè)“空白的圖表”,莫慌,此時(shí)我們選中圖表,單擊鼠標(biāo)右鍵,在彈出的菜單中選擇“更改圖表類型”,如下:
再按下圖選擇出圖表即可。
此時(shí)再選中圖表的繪圖區(qū),在設(shè)置數(shù)據(jù)系列格式中將“第一扇區(qū)起始角度”設(shè)置為270°,如下圖所示。
再按下圖所示給圖表添加“系列標(biāo)簽”。
再選中數(shù)據(jù)標(biāo)注,在標(biāo)簽設(shè)置中按下圖勾選“類別名稱”、“百分比”。當(dāng)然同學(xué)們也可以自由選擇想要顯示的內(nèi)容。
但是,對(duì)于作者E圖表述來(lái)說(shuō),一般作為餅圖系列中的標(biāo)簽內(nèi)容,很少選擇默認(rèn)的數(shù)據(jù),就比如上圖的樣式,標(biāo)簽是不是顯得有些凌亂。所以我們?cè)诠ぷ鞅韰^(qū)域中的E2單元格輸入函數(shù),并下拉填充到E2:E13單元格區(qū)域。
=C2&" "&TEXT(D2/SUM($D$2:$D$13),"0.0%")
計(jì)算出每一行的數(shù)值占總體合計(jì)的百分比,再用TEXT函數(shù)保留一位小數(shù),接著用&連接符連接月份、空格、百分比值,此時(shí)再選擇標(biāo)簽,在標(biāo)簽設(shè)置中,選擇“單元格中的值”,就會(huì)出現(xiàn)如下窗口,我們把引用區(qū)域設(shè)置為E2:E13區(qū)域,并取消勾選“類別名稱”、“百分比”,那么標(biāo)簽就能顯示我們需要的內(nèi)容了,你也可以DIY自己的內(nèi)容。
最后將代表匯總的那個(gè)餅圖顏色和標(biāo)簽都改成無(wú)顏色,再進(jìn)行排版和美工,屬于你的半圓餅圖就做好了。
▲好圖表往往都不是源數(shù)據(jù)做出來(lái)的,重要的話說(shuō)四遍▲
同學(xué)們,如果我們說(shuō)起動(dòng)態(tài)圖表,你會(huì)想到什么?那么下面這個(gè)動(dòng)態(tài)圖表的思路是不是可以給你更多的啟發(fā)呢?
圖表說(shuō)明:
“帶說(shuō)明文字”的動(dòng)圖,一樣的控件使用,不一樣的圖表表現(xiàn)。
制作過(guò)程:
步驟1:
先處理控件對(duì)輔助數(shù)據(jù)的操控,在工具欄中的“開(kāi)發(fā)工具”中,插入“表單控件”——“組合框”。
鼠標(biāo)右鍵點(diǎn)擊控件,在菜單中選擇“設(shè)置控件格式”,在“控制”選項(xiàng)卡中按下圖設(shè)置內(nèi)容。
此時(shí)我們選擇控件中的內(nèi)容時(shí),E1單元格就會(huì)出現(xiàn)對(duì)應(yīng)值的序號(hào),下面我們就要圍繞這個(gè)單元格制作圖表的數(shù)據(jù)源。
步驟2:
添加輔助數(shù)據(jù)。
E2單元格輸入函數(shù):
=INDEX(A2:A13,E1)
E3單元格輸入函數(shù):
=VLOOKUP(E2,A:B,2,0)
E4單元格輸入函數(shù):
=SUM(B2:B13)-E3
E5單元格輸入函數(shù):
=E3+E4
E6單元格輸入函數(shù):
=E3/SUM(B2:B13)
合并D9:F10單元格區(qū)域,在這個(gè)合并單元格中輸入函數(shù):
="“"&E2&"”"&"銷售額為:"&E3&"萬(wàn)元,"&CHAR(10)&"占總體銷售額的"&TEXT(E6,"0.00%")
都是比較簡(jiǎn)單的函數(shù),D9單元格輸入的函數(shù)就是用&連接符將我們需要展示的內(nèi)容羅列出來(lái)即可。
步驟3:
選中E3:E5單元格區(qū)域,工具欄中點(diǎn)選“插入”——“餅圖”,默認(rèn)情況下,起始扇區(qū)的角度是0°。
步驟4:
在工具欄中點(diǎn)選“插入”——“形狀”——“圓角矩形”,如下圖。
選中圖形,在編輯欄中輸入“=D9”。
再按照自己的喜好排版圖表的美工和顏色,藉此我們的動(dòng)態(tài)圖表就做完了。
【編后語(yǔ)】
上面的四個(gè)范例都是利用了源數(shù)據(jù)的衍生數(shù)據(jù),也就是我們常說(shuō)的用“輔助列”來(lái)展示數(shù)據(jù),同樣的圖表類型不同的表達(dá)方式,給觀閱者不同的呈現(xiàn),重要的是讓我們的圖表能夠突出表達(dá)我們要表現(xiàn)的數(shù)據(jù)重點(diǎn)。如果同學(xué)你對(duì)這樣的圖表感興趣,那就繼續(xù)關(guān)注部落窩教育以后的文章,我們會(huì)給出更高級(jí)的圖表制作教程。
本文配套的練習(xí)課件請(qǐng)加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
一張更“懂”領(lǐng)導(dǎo)心思的圖表(上篇)《用excel做一張更“懂”領(lǐng)導(dǎo)心思的圖表》
Excel像素圖表《新同事用條件格式制作的圖表,竟然比我的還好看?》
Excel紅旗圖表《看膩了折線圖,一起來(lái)做創(chuàng)意十足的紅旗圖表》
Excel變色曲線圖《年終報(bào)告,你需要這張Excel圖表來(lái)“撐場(chǎng)面”》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類別
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)