文|仟櫻雪?

數(shù)不如圖乾胶，圖不如思維抖剿，數(shù)據(jù)和圖表的結(jié)合是最常見的工作匯報(bào)模式，但在可視化展示時(shí)识窿，時(shí)常出現(xiàn)各種函數(shù)瞎忙活的情況斩郎，導(dǎo)致匯報(bào)內(nèi)容重點(diǎn)不突出，業(yè)務(wù)痛點(diǎn)診斷不及時(shí)喻频、不直接缩宜，因此，直觀簡潔的dashborad設(shè)計(jì)是必要的甥温。

本篇介紹關(guān)于Excel可視化中锻煌，簡單的dashboard設(shè)計(jì)時(shí)，

1姻蚓、美觀餅圖的設(shè)計(jì)炼幔；

2、氣泡圖史简、柱形圖的組合應(yīng)用；

3、TopN的匯報(bào)美化展示圆兵；關(guān)于各種熱銷品的TOP5跺讯、TOP10等排名數(shù)據(jù)的匯報(bào)展示，形象直觀的介紹各個(gè)品類殉农，乃至部門的名列前茅者刀脏；

簡單Excel-dashboard設(shè)計(jì)，在未連接系統(tǒng)數(shù)據(jù)源時(shí)超凳，只需要手動(dòng)復(fù)制粘貼愈污，更新數(shù)據(jù)源，相關(guān)圖表和函數(shù)計(jì)算自動(dòng)刷新即可轮傍；

一暂雹、需求實(shí)現(xiàn)

1、數(shù)據(jù)分析--Excel可視化创夜，美觀餅圖設(shè)計(jì)

案例1：電商平臺(tái)的產(chǎn)品利潤分析杭跪，Excel分析時(shí)需按照平臺(tái)盈利貢獻(xiàn)占比，分析各平臺(tái)的貢獻(xiàn)力度驰吓，以此直觀判斷各平臺(tái)的盈利能力涧尿。

案例Excel實(shí)現(xiàn)：

（1）整理餅圖分析、數(shù)據(jù)粒度檬贰；將平臺(tái)粒度整合為分析維度姑廉，計(jì)量A、B翁涤、C平臺(tái)的收入桥言、成本、毛利（收入-成本）和毛利占比（毛利/收入）迷雪；

主要使用的函數(shù)是sumifs函數(shù)限书，sumifs函數(shù)的使用規(guī)則說明：

sumifs(求和區(qū)域，將作為條件進(jìn)行判斷的區(qū)域1（和求和區(qū)域在同一個(gè)數(shù)據(jù)源中）章咧，判斷的條件1倦西，將作為條件進(jìn)行判斷的區(qū)域2（和求和區(qū)域在同一個(gè)數(shù)據(jù)源中），判斷的條件2赁严，...)

在本例中C22=SUMIFS($I$3:$I$19,$D$3:$D$19,$B$22:$B$24)

$I$3:$I$19：是需要求和的區(qū)域扰柠，收入所在列；

$D$3:$D$19：是將作為條件進(jìn)行判斷的區(qū)域疼约，為數(shù)據(jù)源中的誰求和卤档；

$B$22:$B$24：是判斷的條件，確定求和的條件程剥，唯一的平臺(tái)A劝枣、B、C為粒度，所以需要進(jìn)行聚類求和收入舔腾。

注意：求和區(qū)域溪胶，判斷條件區(qū)域等都需要全部鎖定，保證公式向下填充時(shí)不會(huì)移位稳诚，未鎖定將會(huì)出現(xiàn)移位哗脖，會(huì)導(dǎo)致數(shù)據(jù)范圍統(tǒng)計(jì)誤差；

關(guān)于iferror函數(shù)的應(yīng)用扳还，主要體現(xiàn)在百分比的適用才避，分子/分母，分母為0時(shí)氨距，會(huì)出現(xiàn)報(bào)錯(cuò)桑逝，不利于圖表的繪制，一般會(huì)添加iferror處理

（2）整合餅圖分析衔蹲、圖表美化：

a肢娘、圖表區(qū)域美化：選擇數(shù)據(jù)源（按下Ctrl選中平臺(tái)、占比列）舆驶，點(diǎn)擊“插入”橱健，工具下的餅圖；刪除圖例沙廉、圖表標(biāo)題拘荡；

b、圖表區(qū)域美化：選中餅圖圖表邊框撬陵，右鍵選擇“設(shè)置圖表區(qū)域格式”珊皿，填充選項(xiàng)選擇，無填充巨税，邊框選項(xiàng)蟋定，選擇無邊框；

c草添、圖表區(qū)域美化：選中餅圖邊框驶兜，右鍵選擇“設(shè)置繪圖區(qū)格式”，填充選擇远寸，無填充抄淑，邊框選項(xiàng)，選擇無邊框驰后；

即可去掉圖表的背景色肆资、邊框線，設(shè)置成透明的背景

d灶芝、數(shù)據(jù)標(biāo)簽美化：選中餅圖的分區(qū)郑原，右鍵選擇“添加數(shù)據(jù)標(biāo)簽”唉韭，選中數(shù)據(jù)標(biāo)簽，設(shè)置標(biāo)簽的底色為白色犯犁，

e纽哥、數(shù)字標(biāo)簽美化：選中數(shù)據(jù)標(biāo)簽，右鍵選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”栖秕，標(biāo)簽選項(xiàng)，勾選上“類別名稱”晓避，則出現(xiàn)各個(gè)餅圖分區(qū)的類別名稱簇捍；數(shù)字選項(xiàng)，設(shè)置為百分比俏拱，小數(shù)位設(shè)置成保留1位暑塑；

f、餅圖顏色美化：選中A品類的區(qū)域顏色：默認(rèn)淺藍(lán)色锅必，雙擊事格，設(shè)置“填充”，選擇深藍(lán)色搞隐，更改透明度為“25%”驹愚；

a-f?操作之后，餅圖的簡單美化劣纲，則完成逢捺。

2、數(shù)據(jù)分析--Excel可視化癞季，氣泡圖劫瞳、條形圖的組合應(yīng)用；

案例1：電商平臺(tái)的產(chǎn)品利潤分析绷柒，Excel分析時(shí)需按分月進(jìn)行收入-成本-毛利率的趨勢分析志于，分析各毛利率在各月份的浮動(dòng)情況，以此直觀查看成本和收入對(duì)利潤率的影響力度废睦。

案例Excel實(shí)現(xiàn)：

（1）整理氣泡伺绽、柱形圖圖組合分析的數(shù)據(jù)粒度；將銷售月份整合為分析維度郊楣，計(jì)量A憔恳、B、C平臺(tái)的收入净蚤、成本钥组、毛利率=（收入-成本）/收入；

主要使用的函數(shù)也是sumifs函數(shù)今瀑，類比以上操作說明程梦，只是分類維度變成了去重的銷售“年月”

（2）整合氣泡圖点把、柱形圖組合圖美化：

a、圖表區(qū)域美化：選擇數(shù)據(jù)源屿附，點(diǎn)擊“插入”郎逃，工具下的“柱形圖”，選擇“更多柱形圖”類型中的“組合”挺份，毛利率設(shè)置成“折線圖”褒翰，勾選副坐標(biāo)軸，刪除圖表標(biāo)題匀泊、背景線优训；

b、圖表區(qū)域美化：選中組合圖表邊框各聘，右鍵選擇“設(shè)置圖表區(qū)域格式”揣非，填充選項(xiàng)選擇，無填充躲因，邊框選項(xiàng)早敬，選擇無邊框；

c大脉、圖表區(qū)域美化：選中組合圖邊框搞监，右鍵選擇“設(shè)置繪圖區(qū)格式”，填充選擇箱靴，無填充腺逛，邊框選項(xiàng)，選擇無邊框衡怀；

一致去掉圖表的背景色棍矛、邊框線，設(shè)置成透明的背景抛杨。

d够委、圖例美化：選中圖例，右鍵怖现，選擇“設(shè)置圖例格式”茁帽，選擇“靠上”；

f屈嗤、坐標(biāo)軸美化：選中左側(cè)主縱坐標(biāo)軸潘拨，在“開始”菜單欄下，字體設(shè)置成最小饶号，白色字體铁追，選中右側(cè)副縱坐標(biāo)軸，字體設(shè)置成最小茫船，白色字體琅束；

g扭屁、氣泡圖設(shè)置美化：選中折線圖，右鍵選擇“設(shè)置數(shù)據(jù)系列格式”涩禀，選擇“線條”料滥，選擇“實(shí)線”，設(shè)置成深藍(lán)色艾船，勾選“平滑線”葵腹，---“線條”選項(xiàng)下的“透明度設(shè)置成100%”；

h屿岂、氣泡圖氣泡美化：選中折線圖礁蔗，右鍵選擇“設(shè)置數(shù)據(jù)系列格式”， 選擇“標(biāo)記”下的“數(shù)據(jù)標(biāo)記選項(xiàng)”雁社，選擇“自動(dòng)”，“邊框”設(shè)置成“漸變線條”晒骇，寬度為20磅霉撵；

i、氣泡圖標(biāo)簽美化： 選擇折線圖數(shù)據(jù)標(biāo)簽洪囤，右鍵徒坡，選擇“設(shè)置數(shù)據(jù)標(biāo)簽格式”，設(shè)置“標(biāo)簽位置”--居中瘤缩，“數(shù)字”-設(shè)置成“百分比”喇完，保留1位小數(shù)，加粗顯示剥啤；

j锦溪、柱形圖顏色美化：選中柱形圖，右鍵選擇“設(shè)置數(shù)據(jù)系列格式”府怯，“填充“--設(shè)置“深藍(lán)色”刻诊，更改透明度為“25%”，“邊框”牺丙，選擇設(shè)置為“實(shí)線”则涯，選擇“深藍(lán)色”；

k冲簿、柱形圖標(biāo)簽美化：選中柱形圖粟判，右鍵選擇“添加數(shù)據(jù)標(biāo)簽”；

a-k操作之后峦剔，氣泡圖档礁、柱形圖組合圖的簡單美化，則完成羊异。

3事秀、數(shù)據(jù)分析--Excel可視化彤断，TopN的匯報(bào)展示；

案例1：電商平臺(tái)的產(chǎn)品利潤分析易迹，Excel分析時(shí)需按品類利潤率宰衙，進(jìn)行毛利率TopN的匯報(bào)展示，分析各品類中睹欲，名列前茅者供炼，從而直觀分析利潤貢獻(xiàn)主力品類。

案例Excel實(shí)現(xiàn)：

（1）綜合1-2步驟中的窘疮，平臺(tái)分類餅圖袋哼、月度利潤趨勢圖，分區(qū)布局闸衫、版塊主題名稱涛贯，調(diào)整配色（藍(lán)色+深棕黃）；

（2）設(shè)置“TopN”的展示區(qū)域布局蔚出，本案例需展示Top4名的銷售品類名稱+利潤率數(shù)據(jù)弟翘，則TopN字段設(shè)置1個(gè)單元格，品類名臣+毛利潤設(shè)置1個(gè)合并單元格骄酗；

Excel函數(shù)設(shè)置：在合并單元格AE8輸入= INDEX($H$3:$H$19,MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0),0)&" "&TEXT(LARGE($N$3:$N$19,ROW(A1)),"0.00%")稀余；

Excel函數(shù)說明：

a、第一段趋翻，獲取Top1名：INDEX($H$3:$H$19,MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0),0)睛琳；

Top1的名稱是large函數(shù)+match函數(shù)+index函數(shù)的組合使用獲取的，函數(shù)分層解析如下踏烙，

首先师骗，是large函數(shù)的使用方法解析；

large函數(shù)=（需要統(tǒng)計(jì)的數(shù)組或區(qū)域讨惩，從大往小排名第幾的名次）丧凤；

LARGE($N$3:$N$19,ROW(A1))，本例中$N$3:$N$19步脓，是毛利率所在列愿待，row（A1）=1，則獲取毛利率為最大的靴患，即獲取第1名的單元格的百分比數(shù)據(jù)仍侥，數(shù)據(jù)是“0.797979798”；

其次鸳君，是match函數(shù)的使用方法解析农渊；

match函數(shù)=（需查詢的內(nèi)容，需查詢的區(qū)域或颊，需查詢的方式砸紊，用數(shù)字-1传于、0或者1表示）

查詢的方式中：

數(shù)字“-1”，表示查找小于或者等于需查詢內(nèi)容的最大值醉顽，因此需查詢內(nèi)容的區(qū)域數(shù)據(jù)必須按照升序排列沼溜；

數(shù)字“0”，表示查找等于需查詢內(nèi)容的第1個(gè)數(shù)值游添；

數(shù)字“1”系草，表示查找大于或者等于需查詢內(nèi)容的最小值，因此需查詢內(nèi)容的區(qū)域數(shù)據(jù)必須按照降序排列唆涝；

MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0)=MATCH("0.797979798”,$N$3:$N$19,0),函數(shù)分層等價(jià)找都，

表示查找LARGE函數(shù)獲取的數(shù)值“0.787878798”，在毛利率列$N$3:$N$19廊酣，中按照查詢第一個(gè)數(shù)值的方式獲取能耻，“0.797979798”所在單元格位置是“2”；

最后亡驰，是index函數(shù)的使用方法解析嚎京；

index函數(shù)=（要引用的區(qū)域，要引用的行數(shù)隐解，要引用的列數(shù)），屬于index函數(shù)的使用方式中的一種--“連續(xù)區(qū)域引用”的使用方法诫睬；

INDEX($H$3:$H$19,MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0),0)=INDEX($H$3:$H$19,2,0)煞茫，函數(shù)分層等價(jià)，

表示查找行數(shù)等于2的摄凡，列數(shù)是0的單元格续徽，對(duì)應(yīng)在品類列中$H$3:$H$19的品列名稱，即為H4單元格的品類名稱亲澡，“廚房料理”钦扭；

以此類推獲取Top2、Top3床绪、Top4的單元格對(duì)應(yīng)的品類名稱客情，修正ROW(An），其中n=1,2,3,4即可獲锐骸膀斋；

b、第二段,間隔美化：&" "&痹雅，保證品類名稱和毛利率的數(shù)值之間有間隔顯示仰担，保證數(shù)據(jù)美化效果設(shè)置的空格，用連接符&設(shè)置绩社；

c摔蓝、第三段赂苗，獲取Top1的毛利率數(shù)值：TEXT(LARGE($N$3:$N$19,ROW(A1)),"0.00%")

Top1的毛利率數(shù)值是large函數(shù)+text函數(shù)組合獲取顯示的，函數(shù)分層解析如下：

首先贮尉，LARGE函數(shù)的使用拌滋；

LARGE($N$3:$N$19,ROW(A1))，表示即獲取第1名的單元格的百分比數(shù)據(jù)绘盟，數(shù)據(jù)是“0.797979798”鸠真；

其次，text函數(shù)的使用龄毡；

text(數(shù)據(jù)區(qū)域吠卷，轉(zhuǎn)換數(shù)據(jù)格式），“0.00%”沦零，將數(shù)據(jù)設(shè)置成百分?jǐn)?shù)且保留2位小數(shù)祭隔；

TEXT(LARGE($N$3:$N$19,ROW(A1)),"0.00%")=TEXT(78.80%,"0.00%")，

由于存在連接符“&”會(huì)導(dǎo)致large函數(shù)獲取的數(shù)值“78.80%”路操，顯示所有小數(shù)位“0.797979798”疾渴，顯示不美觀，且不是百分?jǐn)?shù)屯仗，因此使用text函數(shù)將百分?jǐn)?shù)轉(zhuǎn)換成百分?jǐn)?shù)搞坝，且保留2位小數(shù)，達(dá)到美觀顯示的效果魁袜。

以此類推獲取Top2桩撮、Top3、Top4的單元格對(duì)應(yīng)的毛利率峰弹，修正ROW(An）店量，其中n=1,2,3,4即可獲取鞠呈；

以上3部分是關(guān)于一份簡潔的工作匯報(bào)的可視化圖表展示融师，至于匯報(bào)內(nèi)容則看個(gè)人思維的發(fā)揮，各顯神通蚁吝；

該可視化dashboard旱爆，可保存成日?qǐng)?bào)、周報(bào)窘茁。月報(bào)匯報(bào)模板疼鸟，每次只需粘貼最新的數(shù)據(jù)源，則左側(cè)的數(shù)據(jù)統(tǒng)計(jì)庙曙，都會(huì)根據(jù)函數(shù)自動(dòng)刷新空镜，右側(cè)的圖表也自動(dòng)刷新成最新的數(shù)據(jù)哦。

（注：2018.10.19,Excel常見分析大小坑總結(jié)，有用就給個(gè)小心心喲吴攒，后續(xù)持續(xù)更新ing）