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

之前，我们分享了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技能，学习如何制作漂亮的图表，让老板刮目相看！