文|仟樱雪
数不如图，图不如思维，数据和图表的结合是最常见的工作汇报模式，但在可视化展示时，时常出现各种函数瞎忙活的情况，导致汇报内容重点不突出，业务痛点诊断不及时、不直接，因此，直观简洁的dashborad设计是必要的。
本篇介绍关于Excel可视化中，简单的dashboard设计时，
1、美观饼图的设计；
2、气泡图、柱形图的组合应用；
3、TopN的汇报美化展示；关于各种热销品的TOP5、TOP10等排名数据的汇报展示，形象直观的介绍各个品类，乃至部门的名列前茅者；
简单Excel-dashboard设计，在未连接系统数据源时，只需要手动复制粘贴，更新数据源，相关图表和函数计算自动刷新即可；
一、需求实现
1、数据分析--Excel可视化，美观饼图设计
案例1：电商平台的产品利润分析，Excel分析时需按照平台盈利贡献占比，分析各平台的贡献力度，以此直观判断各平台的盈利能力。
案例Excel实现：
（1）整理饼图分析、数据粒度；将平台粒度整合为分析维度，计量A、B、C平台的收入、成本、毛利（收入-成本）和毛利占比（毛利/收入）；
主要使用的函数是sumifs函数，sumifs函数的使用规则说明：
sumifs(求和区域，将作为条件进行判断的区域1（和求和区域在同一个数据源中），判断的条件1，将作为条件进行判断的区域2（和求和区域在同一个数据源中），判断的条件2，...)
在本例中C22=SUMIFS($I$3:$I$19,$D$3:$D$19,$B$22:$B$24)
$I$3:$I$19：是需要求和的区域，收入所在列；
$D$3:$D$19：是将作为条件进行判断的区域，为数据源中的谁求和；
$B$22:$B$24：是判断的条件，确定求和的条件，唯一的平台A、B、C为粒度，所以需要进行聚类求和收入。
注意：求和区域，判断条件区域等都需要全部锁定，保证公式向下填充时不会移位，未锁定将会出现移位，会导致数据范围统计误差；
关于iferror函数的应用，主要体现在百分比的适用，分子/分母，分母为0时，会出现报错，不利于图表的绘制，一般会添加iferror处理
（2）整合饼图分析、图表美化：
a、图表区域美化：选择数据源（按下Ctrl选中平台、占比列），点击“插入”，工具下的饼图；删除图例、图表标题；
b、图表区域美化：选中饼图图表边框，右键选择“设置图表区域格式”，填充选项选择，无填充，边框选项，选择无边框；
c、图表区域美化：选中饼图边框，右键选择“设置绘图区格式”，填充选择，无填充，边框选项，选择无边框；
即可去掉图表的背景色、边框线，设置成透明的背景
d、数据标签美化：选中饼图的分区，右键选择“添加数据标签”，选中数据标签，设置标签的底色为白色，
e、数字标签美化：选中数据标签，右键选择“设置数据标签格式”，标签选项，勾选上“类别名称”，则出现各个饼图分区的类别名称；数字选项，设置为百分比，小数位设置成保留1位；
f、饼图颜色美化：选中A品类的区域颜色：默认浅蓝色，双击，设置“填充”，选择深蓝色，更改透明度为“25%”；
a-f 操作之后，饼图的简单美化，则完成。
2、数据分析--Excel可视化，气泡图、条形图的组合应用；
案例1：电商平台的产品利润分析，Excel分析时需按分月进行收入-成本-毛利率的趋势分析，分析各毛利率在各月份的浮动情况，以此直观查看成本和收入对利润率的影响力度。
案例Excel实现：
（1）整理气泡、柱形图图组合分析的数据粒度；将销售月份整合为分析维度，计量A、B、C平台的收入、成本、毛利率=（收入-成本）/收入；
主要使用的函数也是sumifs函数，类比以上操作说明，只是分类维度变成了去重的销售“年月”
（2）整合气泡图、柱形图组合图美化：
a、图表区域美化：选择数据源，点击“插入”，工具下的“柱形图”，选择“更多柱形图”类型中的“组合”，毛利率设置成“折线图”，勾选副坐标轴，删除图表标题、背景线；
b、图表区域美化：选中组合图表边框，右键选择“设置图表区域格式”，填充选项选择，无填充，边框选项，选择无边框；
c、图表区域美化：选中组合图边框，右键选择“设置绘图区格式”，填充选择，无填充，边框选项，选择无边框；
一致去掉图表的背景色、边框线，设置成透明的背景。
d、图例美化：选中图例，右键，选择“设置图例格式”，选择“靠上”；
f、坐标轴美化：选中左侧主纵坐标轴，在“开始”菜单栏下，字体设置成最小，白色字体，选中右侧副纵坐标轴，字体设置成最小，白色字体；
g、气泡图设置美化：选中折线图，右键选择“设置数据系列格式”，选择“线条”，选择“实线”，设置成深蓝色，勾选“平滑线”，---“线条”选项下的“透明度设置成100%”；
h、气泡图气泡美化：选中折线图，右键选择“设置数据系列格式”， 选择“标记”下的“数据标记选项”，选择“自动”，“边框”设置成“渐变线条”，宽度为20磅；
i、气泡图标签美化： 选择折线图数据标签，右键，选择“设置数据标签格式”，设置“标签位置”--居中，“数字”-设置成“百分比”，保留1位小数，加粗显示；
j、柱形图颜色美化：选中柱形图，右键选择“设置数据系列格式”，“填充“--设置“深蓝色”，更改透明度为“25%”，“边框”，选择设置为“实线”，选择“深蓝色”；
k、柱形图标签美化：选中柱形图，右键选择“添加数据标签”；
a-k操作之后，气泡图、柱形图组合图的简单美化，则完成。
3、数据分析--Excel可视化，TopN的汇报展示；
案例1：电商平台的产品利润分析，Excel分析时需按品类利润率，进行毛利率TopN的汇报展示，分析各品类中，名列前茅者，从而直观分析利润贡献主力品类。
案例Excel实现：
（1）综合1-2步骤中的，平台分类饼图、月度利润趋势图，分区布局、版块主题名称，调整配色（蓝色+深棕黄）；
（2）设置“TopN”的展示区域布局，本案例需展示Top4名的销售品类名称+利润率数据，则TopN字段设置1个单元格，品类名臣+毛利润设置1个合并单元格；
Excel函数设置：在合并单元格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函数说明：
a、第一段，获取Top1名：INDEX($H$3:$H$19,MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0),0)；
Top1的名称是large函数+match函数+index函数的组合使用获取的，函数分层解析如下，
首先，是large函数的使用方法解析；
large函数=（需要统计的数组或区域，从大往小排名第几的名次）；
LARGE($N$3:$N$19,ROW(A1))，本例中$N$3:$N$19，是毛利率所在列，row（A1）=1，则获取毛利率为最大的，即获取第1名的单元格的百分比数据，数据是“0.797979798”；
其次，是match函数的使用方法解析；
match函数=（需查询的内容，需查询的区域，需查询的方式，用数字-1、0或者1表示）
查询的方式中：
数字“-1”，表示查找小于或者等于需查询内容的最大值，因此需查询内容的区域数据必须按照升序排列；
数字“0”，表示查找等于需查询内容的第1个数值；
数字“1”，表示查找大于或者等于需查询内容的最小值，因此需查询内容的区域数据必须按照降序排列；
MATCH(LARGE($N$3:$N$19,ROW(A1)),$N$3:$N$19,0)=MATCH("0.797979798”,$N$3:$N$19,0),函数分层等价，
表示查找LARGE函数获取的数值“0.787878798”，在毛利率列$N$3:$N$19，中按照查询第一个数值的方式获取，“0.797979798”所在单元格位置是“2”；
最后，是index函数的使用方法解析；
index函数=（要引用的区域，要引用的行数，要引用的列数），属于index函数的使用方式中的一种--“连续区域引用”的使用方法；
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)，函数分层等价，
表示查找行数等于2的，列数是0的单元格，对应在品类列中$H$3:$H$19的品列名称，即为H4单元格的品类名称，“厨房料理”；
以此类推获取Top2、Top3、Top4的单元格对应的品类名称，修正ROW(An），其中n=1,2,3,4即可获取；
b、第二段,间隔美化：&" "&，保证品类名称和毛利率的数值之间有间隔显示，保证数据美化效果设置的空格，用连接符&设置；
c、第三段，获取Top1的毛利率数值：TEXT(LARGE($N$3:$N$19,ROW(A1)),"0.00%")
Top1的毛利率数值是large函数+text函数组合获取显示的，函数分层解析如下：
首先，LARGE函数的使用；
LARGE($N$3:$N$19,ROW(A1))，表示即获取第1名的单元格的百分比数据，数据是“0.797979798”；
其次，text函数的使用；
text(数据区域，转换数据格式），“0.00%”，将数据设置成百分数且保留2位小数；
TEXT(LARGE($N$3:$N$19,ROW(A1)),"0.00%")=TEXT(78.80%,"0.00%")，
由于存在连接符“&”会导致large函数获取的数值“78.80%”，显示所有小数位“0.797979798”，显示不美观，且不是百分数，因此使用text函数将百分数转换成百分数，且保留2位小数，达到美观显示的效果。
以此类推获取Top2、Top3、Top4的单元格对应的毛利率，修正ROW(An），其中n=1,2,3,4即可获取；
以上3部分是关于一份简洁的工作汇报的可视化图表展示，至于汇报内容则看个人思维的发挥，各显神通；
该可视化dashboard，可保存成日报、周报。月报汇报模板，每次只需粘贴最新的数据源，则左侧的数据统计，都会根据函数自动刷新，右侧的图表也自动刷新成最新的数据哦。
（注：2018.10.19,Excel常见分析大小坑总结，有用就给个小心心哟，后续持续更新ing）
网友评论