作者:仟樱雪
链接:https://www.jianshu.com/p/5615fe765f68
多项目达成数据的可视化图表中,最常见的是组合折线图和柱形图,但还是有些高端、大气、接地气的图表待大家参考的。
本文主要介绍涉及到Excel可视化中,多项目完成进度--“达成率”的汇报展示中,一款新颖、简便、美观的数据可视化图表------“WIFI图”。
多项目达成--WiFi图
多项目达成率--WiFi图的展示,是由组合环形图构成,通过增加辅助列,调整扇形角度、分区显色等设计而成的。
案例1:电商平台产品的收入达成分析,Excel分析时需按照多平台收入完成,分析各平台完成效率高低,以此把控各平台年终目标完成进度。
案例Excel实现:
1、数据整理--无间隔“WiFi图 ”
“WiFi图”的数据构成:达成量+收入重排+部门重排+辅助列
(1)WiFi图环形达成--达成量-截止完成:=SUMIFS($J$3:$J$20,$D$3:$D$20,$B$24:$B$27,$B$3:$B$20,'<='&'2018/11/8');
说明:sumifs(求和区域:收入完成区域J3:J20,
条件区域1:平台名称所在列$D$3:$D$20,判定1:$B$24:$B$27,
条件区域2:平台名称所在列$B$3:$B$20,判定1:'<='&'2018/11/8':即在时间2018年11月8日之前的所有的收入金额);
(2)WiFi图的数字标签--收入重排=SMALL($C$24:$C$27,ROW(A1)),查找数据区域中的第几最小数的时候会用到small函数;
small函数=(需查找的数值或者数据区域,返回在数值或者数据区域中的位置(由小到大))
数据区域:$C$24:$C$27,原始收入所在的列(未排序);
数据位置:ROW(A1)=1,在数据区域中第一小的数据,向下填充的时候,会变成row(A2)=2,因此是升序,从小到大的顺序排列收入;
(3)WiFi图的名称标签--部门重排=INDEX($B$24:$B$27,MATCH(E24,$C$24:$C$27,)),按照某一条件查找固定区域内的位置,使用index+match函数的组合;
目标:实现收入达成,按照升序排列,对应的部门也按照收入升序排列。
index(需返回的值或者区域:$B$24:$B$27,返回值所在的行号,返回值所在的列号);
match(需查找的值或者区域:E24单元格,可能包含所要查找的数值的连续单元格区域:$C$24:$C$27,查找类型)
查找类型:-1、1、0 ,表示查找方式,用于指定精确查找(查找区域无序排列)或模糊查找(查找区域升序排列),0代表精确查找,-1代表大于查找,1代表小于查找;
计算规则:
match函数:通过match函数精确找到:最小收入的E24=590元,在整个升序排列的数据区域:$C$24:$C$27中的位置,因此返回的是“4”,说明590元在的行数是第4行,列号是0;
index函数:index($B$24:$B$27,4)=在$B$24:$B$27中第4行是部门“D”;
(4)WiFi图的扇面角度--辅助列=MAX($E$24:$E$27,ROW(A1))*3-$C$28,作为扇形的覆盖的最大面积;
辅助说明:MAX($E$24:$E$27,ROW(A1))*3,将收入区域中的最大值乘以3之后,找到最大的辐射面积;
$C$28=SUM(C24:C27)/3,总收入的1/3作为最大辐射面积的冲减区域,因此精准定位WiFi图的扇形区域是整个圆形的1/3的扇形区;
即:扇形的最大覆盖面积=核算收入的最大值的3倍-总收入的1/3;
2、多项目完成可视化--无间隔“WiFi图”
WiFi图构成:多层环形图
a、环形图--图表区域美化:选择数据源(选中环形图行:排序后的部门、排序后的收入、辅助列),点击“插入”,工具下的“环形图”;删除图表标题、图例;
b、环形图--图表区域美化:选中圆环图边框,右键选择“设置图表区域格式”,填充选项选择,无填充,边框选项,选择无边框;
c、环形图--图表区域美化:选中圆环图边框,右键选择“设置绘图区格式”,填充选择,无填充,边框选项,选择无边框;
即可去掉图表的背景色、边框线,设置成透明的背景。
d、环形图--层次设置:选中环形图,右键选择“数据源“,点击“切换行/列”,确定,即可设置环形图的图形系列数据源,为“部门”系列;
e、环形图-区域美化:选中环形图,右键选择“设置数据系列格式”,设置“系列选项”--第一扇形区起始角度为:“300度”--圆环图内径大小为:15%;
f、环形图-区域美化:
双击,选中环形图内层的“未达成区域”,右键选择“设置数据系列格式”,设置“系列选项”--填充,选择无填充--边框。选择,无线条;
以此类推将所有环形的“未达成区域”,都设置成无颜色、无线条区域;
注意:切记,是双击!否则会将需要保留的“达成区域”,也给透明隐藏掉了哦;
g、环形图--标签美化:选中“达成区域”,双击图形区域,右键,选择“添加数据标签”,以此类推逐个添加多环达成标签;
双击选中数字标签,使标签变成“白色”被选中状态,右键,选择“设置数据标签格式”,设置“标签选项--”勾选“系列名称”选项,则出现每个达成的部门系列名称,设置为加粗,黑色字体显示,以此类推设置多环达成的数据标签格式;
h、环形图-WiFi美化:点击“插入”,选择“形状”,插入“圆形”,则在已经绘制好的WiFi图的下方,绘制出以个圆形,调整圆形大小,使其和WiFi图的环径大小一致,点击“格式”,形状填充--选择蓝色,形状;轮廓--选择白色;
i、WiFi图--组合美化:选中绘制好的“圆形”,按下“shift键”,选中设置完毕的环形图,右键,选择“组合”,则拖动美化完成的多项目达成WiFi图即可。
综上:多项目达成--无间隔WiFi图,经过组合环形图的优化整合,即可达成,将收入达成区域用rand()函数替换,即可解锁变化的动态WiFi图哦!
2、多项目完成分析--间隔“WiFi图”
WiFi图构成:多层环形图
1、数据整理--无间隔“WiFi图 ”
“WiFi图”的数据构成:达成率+达成率重排+部门重排+辅助列
(1)WiFi图环形达成--达成率=截止完成/全年目标;
(2)WiFi图大小顺序-达成率重排=SMALL($I$24:$I$27,ROW(A1));
(3)WiFi图的名称标签--部门重排=INDEX($H$24:$H$27,MATCH(J24,$I$24:$I$27,));
(4)WiFi图的扇面角度--辅助列=J24/3,即为1/3的重排之后的达成率;
2、数据转置
(1)系列1的数据:将辅助列的收入达成率,分别等价到对应的平台;
系列1的数据:辅助列1=辅助列2=辅助列3=1/3=33%
(2)系列2:各平台系列2达成率=1/3-各平台系列1对应的达成率,系列2对应的辅助列1=辅助列2=辅助列3=0;
(3)系列3:全部=2/3
3、多项目完成可视化--间隔“WiFi图”
WiFi图构成:多层环形图
a、环形图--图表区域美化:选择数据源(选中数据转置后的行列:系列信息、平台、辅助列),点击“插入”,工具下的“环形图”;删除图表标题、图例;
b、环形图--图表区域美化:选中圆环图边框,右键选择“设置图表区域格式”,填充选项选择,无填充,边框选项,选择无边框;
c、环形图--图表区域美化:选中圆环图边框,右键选择“设置绘图区格式”,填充选择,无填充,边框选项,选择无边框;
即可去掉图表的背景色、边框线,设置成透明的背景。
d、环形图--层次设置:选中环形图,右键选择“数据源“,点击“切换行/列”,确定,即可设置环形图的图裂系列数据源,为“部门”系列;
e、环形图-区域美化:选中环形图,右键选择“设置数据系列格式”,设置“系列选项”--第一扇形区起始角度为:“300度”--圆环图内径大小为:10%;
备注:对比无间隔WiFi图,该图只是多了辅助列的环形,各环形的环径是10%,略微宽一些;
f、环形图-区域美化:
双击,选中环形图内层的“未达成区域”,右键选择“设置数据系列格式”,设置“系列选项”--填充,选择无填充--边框。选择,无线条;
以此类推将所有环形的“未达成区域”,都设置成无颜色、无线条区域;
g、环形图-区域美化:选择环形图的“辅助列”对应的内环整环,右键选择“设置数据系列格式”,填充--选择无填充,线条--选择无线条;
备注:对比无间隔WiFi图,该图只是多了辅助列的环形,也类似设置成透明无线条边框,作为间隔区间;
h、环形图--标签美化:选中“达成区域”,双击图形区域,右键,选择“添加数据标签”,以此类推逐个添加多环达成标签;
双击选中数字标签,使标签变成“白色”被选中状态,右键,选择“设置数据标签格式”,设置“标签选项--”勾选“系列名称”选项,去掉“值”勾选,则出现每个达成的部门系列名称,设置为加粗,黑色字体显示,以此类推设置多环达成的数据标签格式;
i、环形图-WiFi美化:点击“插入”,选择“形状”,插入“圆形”,则在已经绘制好的WiFi图的下方,绘制出以个圆形,调整圆形大小,使其和WiFi图的环径大小一致,点击“格式”,形状填充--选择蓝色,形状;轮廓--选择白色;
j、WiFi图--组合美化:选中绘制好的“圆形”,按下“shift键”,选中设置完毕的环形图,右键,选择“组合”,则拖动美化完成的多项目达成WiFi图即可。
备注:对比无间隔WiFi图,该图只是数字标签的显示比无间隔WiFi图少了数值,只显示部门类别,因为是百分比的1/3,因此数字标签可不显示。
综上:a-j多项目达成--间隔WiFi图,经过组合环形图的优化整合,即可达成,将收入达成区域用rand()函数替换,即可解锁变化的动态WiFi图哦!
联系客服