6步制作Excel动态销售月报看板,数据可视化,高端大气一目了然

2024-05-10 17 0

之前,我们分享了2个简单的数据透视图、一个单页仪表板、一个每日销售报告板和一个每周报告板。今天我们分享一张漂亮的动态月度销售报告表。如果需要,请保存以供以后使用,并注意。按时进行后续课程。

1、效果图

2数据来源

数据来源与日报、周报基本相同。这是公司2018年1月1日到12月31日的全部销售订单数据。为了方便后续计算月度数据,增加了一个辅助列来计算数据。当前日期的月份公式为I2MONTH(B2),MONTH函数可以返回日期中的月份。

3、月报表制作思路

1、明确数据分析和可视化的要素;

2获取绘制和制作图表所需的数据;

3、设计数据分析面板的布局;

4、插入日期调整按钮,进行动态查询;

5、将相关数据和图表放置在黑板上适当的位置;

6调整配色,美化标牌。

四、操作步骤

第一步:阐明数据分析和呈现的要素。

案例视图的要素和呈现方法是:

①采用大字海报展示本月订单数、销售额、最高、最低销售额、本月单日订单状态。

②与上月订单、销售额变化相比,增量为红色上升三角形,数据为红色,减少为绿色下降三角形,数据为绿色,等号为白色等号,数据为是白色的。由于三角形和数据需要显示在单元格中,因此它们以粘贴链接的图像形式显示。

③使用仪表板显示月计划完成率和年度计划完成率。

④用条形图展示商品评分、地区评分、城市评分、卖家当日销量评分。

第二步:根据显示元素的要求设计公式,获取绘图数据并制作图表。

1、使用C2单元格连接日期调整按钮,调整按钮即可改变C2单元格中的数据,实现动态效果。

2使用DATE函数计算本月和上个月的开始和结束日期。

本月的开始日期为C3DATE(2018,C2,1),即2018年所选月份的第一天。

本月的截止日期为C4DATE(2018,C21,1)-1,即所选月份下个月的第一天减去1天。

上个月开始日期I3DATE(2018,C2-1,1)

上个月截止日期I4DATE(2018,C2,1)-1

3使用SUMIF函数统计本月的销售额和上个月的销售额。

本月销售额C5SUMIFS(datasource!$E:$E,datasource!$B:$B,'>'$C$3,datasource!$B:$B,'

上个月的销售额I5SUMIFS(数据源!$E:$E,数据源!$B:$B,'>'$I$3,数据源!$B:$B,'

4、使用COUNTIF函数统计本月的订单和上个月的订单。

本月订单C6COUNTIFS(数据源!$B:$B,'>'$C$3,数据源!$B:$B,'

上月订单I6COUNTIFS(数据源!$B:$B,'>'$I$3,数据源!$B:$B,'

5使用IF函数将本月的销售额和订单与上个月进行比较不同的情况会有不同的显示。

即增加时显示红色向上三角形,相等时显示等号,减少时显示绿色向下三角形,并显示增减比例数据。公式为:

E5IF(C5>I5,'▲',IF(C5I5,'','▼'))

E6IF(C6>I6,'▲',SE(C6I6,'','▼'))

F5SE(C5>I5,(C5-I5)/C5,SE(C5I5,0,(C5-I5)/C5))

F6SE(C6>I6,(C6-I6)/C6,SE(C6I6,0,(C6-I6)/C6))

如果你想了解SUMIF、COUNTIF、IF等函数的详细用法,可以查看我之前分享过的详细教程。

6使用条件格式,增大纵横比,上升三角形显示为红色,减小纵横比,下降三角形显示为绿色,相等时,等号和0显示为白色。

①设置增加红色数据显示:选择单元格E5和F5→【开始】→【条件格式】→【新建规则】→【使用公式确定格式化单元格】→在【将单元格设置为与此公式匹配的值】输入公式$C$5>$I$5→点击【格式】→【字体】→选择红色并确认。

②设置减少绿色数据显示:同样设置E5、F5单元格在$C$6$I$6时显示白色,在$C$6时显示白色

③用同样的方法设置E6和F6单元格的条件格式。

7计算本月计划销售值和今年计划销售值的完成率并创建仪表板

①每月计划销售金额为260万,即在表格L13单元格中输入计划金额

②本月完成金额L14C5

③本月计划完成率为L16L14/L13

④本年度计划销售额L202600000*12

⑤今年完成的累计值L21SUMIFS(数据源!$E:$E,数据源!$B:$B,'>2018-1-1',数据源!$B:$B,'

⑥今年累计完成率为L22L21/L20

⑦L17、L24为面板指针,L18、L25为制作面板的辅助占位数据。仪表盘制作方法可以参考我之前分享的教程《4步完成EXCEL高仿真数据仪表盘让你的老板了解你的数据仪表盘》我眼睛一亮》

8定义每日销售额和本月订单数的公式,并创建销售额折线图和标识最高和最低订单的柱形图。

①由于一年中每个月的天数为28、30、31,而做图表时,NA误差只占空格,不显示,因此,我们必须利用这个特性来绘制公式,无论有多少个。天,显示图表不会有问题。

②本月第一天的日期C13IF(($C$3ROW(1:1)-1)>$C$4,NA(),($C$3ROW(1:1)-1))

③本月第一天销售额D13IF(($C$3ROW(1:1)-1)>$C$4,NA(),SUMIFS(数据源!$E:$E,数据源!$B:$B,C13))

④选择第1天到第31天的销售额,将其输入到折线图中,创建折线面积图,如下图所示。为了清楚地显示数值,这里的轴单位是万元。

⑤本月第一天请求E13IF(($C$3ROW(1:1)-1)>$C$4,NA(),COUNTIF(数据源!$B:$B,C13))

⑥由于我们要确定最大和最小的顺序,所以需要添加辅助列

最大订单F13IFERROR(IF(E13$C$9,E13,0),0)

最小订单G13IFERROR(IF(E13$C$10,E13,0),0)

⑦用订单数、最大订单数、最小订单数三列数据制作如下柱形图。这个图表的具体制作方法可以参考我之前分享的教程《EXCEL动态标注图表最高值和最低值,让你快速看出谁表现最好!》

9设置排名公式并创建排名条形图

①C55SUMIFS(数据源!$E:$E,数据源!$C:$C,$B55,数据源!$J:$J,月报计算!$C$2)

②F55LARGE($C$55:$C$59,ROW(A1)),其中LARGE(array,k)返回数据集中的第K个最大值。ROW(A1)的返回值为1。公式填写时,依次得到第一个、第二个……最大值。

③利用LOOKUP函数根据F列的数据查找第一、第二……最大值对应的产品情况。E55LOOKUP(1,0/($C$55:$C$59F55),$B$55:$59巴西利亚元)

④使用E55:F59数据插入条形图并设置图表格式和标签。

⑤同样的方法可以创建地区排名、城市排名、卖家销量排名条形图。

⑥关于LOOKUP功能的详细教程,可以关注和回顾我之前分享的详细教程。关于条形图美化设置,可以查看我之前分享的图表教程。

第三步:设计每日公告板布局。根据您的显示要求和美观考虑,设计如下所示的布局。

第四步:根据布局,插入日期调整按钮、相关数据和图表。

1使用文本框以大字符显示每月海报数据。

显示1月的文本是指[月度报告计算]工作表的单元格C2中的数据。同样,本月的销量、订单数以及前一天的最高和最低金额和订单都是通过文本框实现的。与上个月的比较是使用粘贴为链接的照片进行的。

2插入日期调整按钮。

单击【开发工具】→【插入】→单击窗体控件上的【数值调整按钮(窗体控件)】→在放置位置绘制调整按钮→右键单击→【设置格式控件】→在【设置对象格式】中中间弹出对话框,点击【控制】→【当前值】设置为1到12之间的一个值→【最小值】设置为1→【最大值】设置为12,因为一年最多有12个月→【单元格链接】点击右侧红色小箭头,然后点击【月报计算】工作表的C2单元格。

步骤5:将【月报表计算】工作表中准备好的分类布局复制粘贴到相应位置。

第六步:调整配色,美化标牌。

本教程涉及很多知识点。本账号开头有关于这些知识点的详细教程。如果你需要的话,你可以自己学习。讨论。原创并不容易。如果您觉得不错,欢迎点赞、评论、转发!

关注“EXCEL学习微课堂”,学习EXCEL技能,学习如何制作漂亮的图表,让老板刮目相看!

本站文章均由用户上传或转载而来,该文章内容本站无法检测是否存在侵权,如果本文存在侵权,请联系邮箱:2287318951@qq.com告知,本站在7天内对其进行处理。

发布评论