文|仟櫻雪?
數(shù)不如圖，圖不如思維，數(shù)據(jù)和圖表的結(jié)合是最常見的工作匯報模式，但在可視化展示時，時常出現(xiàn)各種函數(shù)瞎忙活的情況，導(dǎo)致匯報內(nèi)容重點(diǎn)不突出，業(yè)務(wù)痛點(diǎn)診斷不及時、不直接，因此，直觀簡潔的dashborad設(shè)計(jì)是必要的。
本篇介紹關(guān)于Excel可視化中，簡單的dashboard設(shè)計(jì)時，
1、美觀餅圖的設(shè)計(jì)；
2、氣泡圖、柱形圖的組合應(yīng)用；
3、TopN的匯報美化展示；關(guān)于各種熱銷品的TOP5、TOP10等排名數(shù)據(jù)的匯報展示，形象直觀的介紹各個品類，乃至部門的名列前茅者；
簡單Excel-dashboard設(shè)計(jì)，在未連接系統(tǒng)數(shù)據(jù)源時，只需要手動復(fù)制粘貼，更新數(shù)據(jù)源，相關(guān)圖表和函數(shù)計(jì)算自動刷新即可；
一、需求實(shí)現(xiàn)
1、數(shù)據(jù)分析--Excel可視化，美觀餅圖設(shè)計(jì)
案例1：電商平臺的產(chǎn)品利潤分析，Excel分析時需按照平臺盈利貢獻(xiàn)占比，分析各平臺的貢獻(xiàn)力度，以此直觀判斷各平臺的盈利能力。
案例Excel實(shí)現(xiàn)：
（1）整理餅圖分析、數(shù)據(jù)粒度；將平臺粒度整合為分析維度，計(jì)量A、B、C平臺的收入、成本、毛利（收入-成本）和毛利占比（毛利/收入）；
主要使用的函數(shù)是sumifs函數(shù)，sumifs函數(shù)的使用規(guī)則說明：
sumifs(求和區(qū)域，將作為條件進(jìn)行判斷的區(qū)域1（和求和區(qū)域在同一個數(shù)據(jù)源中），判斷的條件1，將作為條件進(jìn)行判斷的區(qū)域2（和求和區(qū)域在同一個數(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：是判斷的條件，確定求和的條件，唯一的平臺A、B、C為粒度，所以需要進(jìn)行聚類求和收入。
注意：求和區(qū)域，判斷條件區(qū)域等都需要全部鎖定，保證公式向下填充時不會移位，未鎖定將會出現(xiàn)移位，會導(dǎo)致數(shù)據(jù)范圍統(tǒng)計(jì)誤差；
關(guān)于iferror函數(shù)的應(yīng)用，主要體現(xiàn)在百分比的適用，分子/分母，分母為0時，會出現(xiàn)報錯，不利于圖表的繪制，一般會添加iferror處理
（2）整合餅圖分析、圖表美化：
a、圖表區(qū)域美化：選擇數(shù)據(jù)源（按下Ctrl選中平臺、占比列），點(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)各個餅圖分區(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：電商平臺的產(chǎn)品利潤分析，Excel分析時需按分月進(jìn)行收入-成本-毛利率的趨勢分析，分析各毛利率在各月份的浮動情況，以此直觀查看成本和收入對利潤率的影響力度。
案例Excel實(shí)現(xiàn)：
（1）整理氣泡、柱形圖圖組合分析的數(shù)據(jù)粒度；將銷售月份整合為分析維度，計(jì)量A、B、C平臺的收入、成本、毛利率=（收入-成本）/收入；
主要使用的函數(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)”，選擇“自動”，“邊框”設(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的匯報展示；
案例1：電商平臺的產(chǎn)品利潤分析，Excel分析時需按品類利潤率，進(jìn)行毛利率TopN的匯報展示，分析各品類中，名列前茅者，從而直觀分析利潤貢獻(xiàn)主力品類。
案例Excel實(shí)現(xiàn)：
（1）綜合1-2步驟中的，平臺分類餅圖、月度利潤趨勢圖，分區(qū)布局、版塊主題名稱，調(diào)整配色（藍(lán)色+深棕黃）；
（2）設(shè)置“TopN”的展示區(qū)域布局，本案例需展示Top4名的銷售品類名稱+利潤率數(shù)據(jù)，則TopN字段設(shè)置1個單元格，品類名臣+毛利潤設(shè)置1個合并單元格；
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個數(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ù)分層等價，
表示查找LARGE函數(shù)獲取的數(shù)值“0.787878798”，在毛利率列$N$3:$N$19，中按照查詢第一個數(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ù)分層等價，
表示查找行數(shù)等于2的，列數(shù)是0的單元格，對應(yīng)在品類列中$H$3:$H$19的品列名稱，即為H4單元格的品類名稱，“廚房料理”；
以此類推獲取Top2、Top3、Top4的單元格對應(yīng)的品類名稱，修正ROW(An），其中n=1,2,3,4即可獲?。?/b>
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%")，
由于存在連接符“&”會導(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的單元格對應(yīng)的毛利率，修正ROW(An），其中n=1,2,3,4即可獲??；
以上3部分是關(guān)于一份簡潔的工作匯報的可視化圖表展示，至于匯報內(nèi)容則看個人思維的發(fā)揮，各顯神通；
該可視化dashboard，可保存成日報、周報。月報匯報模板，每次只需粘貼最新的數(shù)據(jù)源，則左側(cè)的數(shù)據(jù)統(tǒng)計(jì)，都會根據(jù)函數(shù)自動刷新，右側(cè)的圖表也自動刷新成最新的數(shù)據(jù)哦。
（注：2018.10.19,Excel常見分析大小坑總結(jié)，有用就給個小心心喲，后續(xù)持續(xù)更新ing）
