6步制作Excel动态销售月报看板，数据可视化，高端大气一目了然
EXCEL学习微课堂2020-05-06 22:10
前面我们分享了2个简单动态图表、一页纸仪表板、销售日报看板和周报看板，今天再分享1个漂亮的动态销售月报看板，需要的小伙伴快收藏备用，并及时关注后续课程。
一、效果图
二、数据源
数据源与日报、周报基本一样，为公司2018年1月1日至12月31日的所有销售订单数据，为了方便后续计算月度数据，增加辅助列计算当前日期的月份，公式为I2=MONTH(B2)，函数MONTH可以返回日期中的月份。
三、月报看板的制作思路
1、 明确数据分析和展示要素；
2、 求出作图所需数据，制作图表；
3、 设计数据分析看板布局；
4、 插入日期调节按钮，实现动态查询；
5、将相关的数据和图表放在看板合适的位置；
6、 调整配色，美化看板。
四、操作步骤
第一步：明确数据分析和展示要素。
案例展示的要素和展示方式为：
① 用大字报的方式展示本月订单数和销售金额，本月单日最高和最低销售金额、订单情况。
② 与上月相比订单和销售额的变化情况，增加显示红色向上的三角形，数据为红色，减少显示绿色向下的三角形，数据为绿色，相等显示白色等号，数据为白色。由于要在一个单元格显示三角形和数据两项内容，所以用粘贴链接的图片的形式展示。
③ 用仪表盘展示本月计划完成率和年度计划完成率。
④ 用条形图展示截止到当天为止的商品排行版、区域排行版、城市排行版和业务员销售排行版。
第二步：根据展示要素要求设计公式求出作图数据，制作图表
1、用C2单元格与日期调节按钮连接，通过按钮的调整使C2单元格数据变化，实现动态效果。
2、用DATE函数计算本月和上月的起止日期
本月起始日期C3=DATE(2018,C2,1)，即2018年选定月份的第1天。
本月截止日期C4=DATE(2018,C2+1,1)-1，即选定月份下一个月第1天再减1天。
上月起始日期I3=DATE(2018,C2-1,1)
上月截止日期I4=DATE(2018,C2,1)-1
3、用SUMIF函数统计本月销售额和上月销售额。
本月销售额C5=SUMIFS(数据源!$E:$E,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)
上月销售额I5=SUMIFS(数据源!$E:$E,数据源!$B:$B,">="&$I$3,数据源!$B:$B,"<="&$I$4)
4、用COUNTIF函数统计本月订单和上月订单。
本月订单C6=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)
上月订单I6=COUNTIFS(数据源!$B:$B,">="&$I$3,数据源!$B:$B,"<="&$I$4)
5、用IF函数实现本月销售额和订单与上月相比，不同情况显示不同。
即增加时显示红色向上三角形，相同时显示等号，减少时显示绿色向下三角形，并显示增加和减少比例数据。公式为：
E5=IF(C5>I5,"▲",IF(C5=I5,"=","▼"))
E6=IF(C6>I6,"▲",IF(C6=I6,"=","▼"))
F5=IF(C5>I5,(C5-I5)/C5,IF(C5=I5,0,(C5-I5)/C5))
F6=IF(C6>I6,(C6-I6)/C6,IF(C6=I6,0,(C6-I6)/C6))
想了解SUMIF、COUNTIF和IF等函数详细用法的可以回看我往期分享的详细教程。
6、用条件格式实现增加比例和向上三角形为红色，减少比例和向下三角形显示为绿色，相同时等号和0显示白色
① 设置增加显示红色数据：选择E5和F5单元格→【开始】→【条件格式】→【新建规则】→【使用公式确定格式的单元格】→在【为符合此公式的值设置单元格】中输入公式=$C$5>$I$5→点【格式】→【字体】→选择红色，确定即可。
②设置减少显示绿色数据：同样的方法设置E5和F5单元格当$C$6=$I$6时显示白色，当$C$6<$C$6时显示为绿色。
③同样的方法设置E6、F6单元格的条件格式。
7、计算本月计划销售金额完成率和本年计划销售金额完成率，制作仪表盘
① 每月的计划销售金额都是2600000，即表中L13单元格输入计划金额
② 本月完成金额L14=C5
③ 本月计划完成率L16=L14/L13
④ 本年的计划销售金额L20=2600000*12
⑤ 本年累计完成金额L21=SUMIFS(数据源!$E:$E,数据源!$B:$B,">=2018-1-1",数据源!$B:$B,"<="&$C$4)
⑥ 本年累计完成率L22 =L21/L20
⑦ L17和L24是仪表盘的指针，L18和L25是制作仪表盘的占位辅助数据，仪表盘制作方法请看我前面分享的教程《4步完成EXCEL高仿真数据仪表盘，你的数据看板让老板眼前一亮》
8、设置本月每天销售金额和订单数公式，制作销售金额折线图和标识最高最低的订单柱形图
① 由于全年各月天数有28、30和31天之分，而在制作图表时，NA错误是只占位不显示的，所以我们要运用这个特点设计公式，不论天数多少图表显示都没问题。
② 本月第1天日期C13 =IF(($C$3+ROW(1:1)-1)>$C$4,NA(),($C$3+ROW(1:1)-1))
③ 本月第1天销售金额D13 =IF(($C$3+ROW(1:1)-1)>$C$4,NA(),SUMIFS(数据源!$E:$E,数据源!$B:$B, C13))
④ 选中第1至31天的销售金额插入折线图，制作如下图所示的折线面积图，为了金额显示清晰，这里坐标轴的单位用的是万元。
⑤ 本月第1天订单E13=IF(($C$3+ROW(1:1)-1)>$C$4,NA(),COUNTIF(数据源!$B:$B,C13))
⑥ 由于我们要将最多和最少的订单标识出来，所以要增加辅助列
最多订单F13=IFERROR(IF(E13=$C$9,E13,0),0)
最少订单G13=IFERROR(IF(E13=$C$10,E13,0),0)
⑦ 用订单数、最多订单和最少订单三列数据制作如下柱形图，此图的具体制作方法见我前面分享的教程《EXCEL动态标注最高和最低值的图表，让你一眼看出谁的业绩最好！》
9、设置排行版公式，制作排行版条形图
① C55=SUMIFS(数据源!$E:$E,数据源!$C:$C,$B55,数据源!$J:$J,月报计算!$C$2)
② F55=LARGE($C$55:$C$59,ROW(A1))，其中，LARGE(array,k)，返回数据集中第K个最大值。ROW(A1)返回是1，当公式往下填充时依次得出第1、第2个……最大值。
③ 用LOOKUP函数根据F列的数据查找出第1、第2个……最大值对应的商品情况。E55=LOOKUP(1,0/($C$55:$C$59=F55),$B$55:$B$59)
④ 用E55:F59数据插入条形图，并设置好图形的格式和标签等。
⑤ 同样的方法可以制作区域排行版、城市排行版和业务员销售排行版条形图。
⑥ LOOKUP函数的详细教程可关注后回看我前面分享的详细教程，条形图的美化设置可回看我前面分享的图表教程。
第三步：设计日报看板布局。根据展示要求和美观考虑，设计如下所示的布局。
第四步：根据布局情况，插入日期调节按钮，相关数据和图表。
1、用文本框实现月报大字报数据显示。
其中显示1月的文本引用的是【月报计算】工作表中C2单元格的数据。同样的方法将本月销售额、订单数，与上单日最高和最低的金额、订单都用文本框实现。与上月对比情况用粘贴为链接的图片实现。
2、 插入日期调节按钮。
点【开发工具】→【插入】→在表单控件中点击【数值调节钮（窗体控件）】→在放置位置划出调节按钮→右键→【设置控件格式】→在弹出的【设置对象格式】对话框中点【控制】→【当前值】任意设为1-12之间的值→【最小值】设为1→【最大值】设为12，因为1年最多12个月→【单元格链接】点右边的小红箭头后再点选【月报计算】工作表的C2单元格。
第五步： 将【月报计算】工作表中已经做好的排行版图复制粘贴到相应位置。
第六步：调整配色，美化看板。
本教程涉及的知识点比较多，这些知识点本号前面都有详细教程，需要的自己去学习，如有不清楚的地方，欢迎大家留言讨论，坚持原创不易，如果觉得好的话，欢迎点赞、评论、转发！
关注 “EXCEL学习微课堂”，学习EXCEL技巧，学做漂亮图表，让老板刮目相看！
举报/反馈
0
0
收藏
分享
