今夜无眠了 / Excel / Excel销售周报看板,多维度动态图表展示数...

分享

   

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

2020-09-17  今夜无眠了

一、效果图

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

二、数据源

数据源是公司2018年1月1日至12月31日的所有销售订单数据,为方便后续计算每周的情况,增加辅助列计算当前日期是一年中的第几周,公式为I2=WEEKNUM(B2,2),函数WEEKNUM可以返回一个数字,该数字代表该日期在一年中的第几周。

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

三、操作步骤

第一步:明确数据分析和展示的要素。

案例展示的要素和展示方式为:

① 用大字报的方式展示本周订单数和销售额,本周单日最高和最低销售金额、订单情况。

② 与前1天相比订单和销售额的变化情况,增加显示红色向上的三角形,数据为红色,减少显示绿色向下的三角形,数据为绿色,相等显示白色等号,数据为白色。由于要在一个单元格显示三角形和数据两项内容,所以用粘贴链接的图片的形式展示。

③ 用【条件格式\数据条】制作旋风图,再用粘贴链接的图片形式展示。

④ 用条形图展示截止到当天为止的商品排行版、区域排行版、城市排行版,业务员销售排行版和渠道排行版情况。

第二步:根据展示要素要求设计公式求出作图数据,制作图表

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

1、用C2单元格与后续日期调节按钮连接,通过按钮的调整使C2单元格数据变化,从而实现动态效果。

2、本周截止日期C4=LOOKUP(1,0/(数据源!I:I=C2),数据源!B:B)。

这是通过lOOKUP查找最后一次出现的数据的功能,查找数据源中选定的周数的最后一天。(LOOKUP的详细课程见本号分享的课程《9个LOOKUP函数经典用法,学会秒变EXCEL达人》)

本周起始日期C3=C4-6

上周起始日期H3=C3-7

上周截止日期H4=C4-7

3、用SUMIF函数统计本周销售额和上周销售额。

本周销售额C5=SUMIFS(数据源!$E:$E,数据源!$B:$B,'>='&$C$3,数据源!$B:$B,'<='&$C$4)

上周销售额H5= =SUMIFS(数据源!$E:$E,数据源!$B:$B,'>='&$H$3,数据源!$B:$B,'<='&$H$4)

4、用COUNTIF函数统计今日订单和昨日订单。

本周订单C6=COUNTIFS(数据源!$B:$B,'>='&$C$3,数据源!$B:$B,'<='&$C$4)

上周订单H6=COUNTIFS(数据源!$B:$B,'>='&$H$3,数据源!$B:$B,'<='&$H$4)

5、用IF函数实现本周销售额和订单与上周相比,不同情况显示不同。

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

E5=IF(C5>H5,'▲',IF(C5=H5,'=','▼'))

E6=IF(C6>H6,'▲',IF(C6=H6,'=','▼'))

F5=IF(C5>H5,(C5-H5)/C5,IF(C5=H5,0,(C5-H5)/C5))

F6=IF(C6>H6,(C6-H6)/C6,IF(C6=H6,0,(C6-H6)/C6))

想了解SUMIF、COUNTIF和IF等函数详细用法的可以回看我往期分享的详细教程。

6、用条件格式实现增加比例和向上三角形为红色,减少比例和向下三角形显示为绿色,相同时等号和0显示白色

设置增加显示红色数据:选择E5和F5单元格→【开始】→【条件格式】→【新建规则】→【使用公式确定格式的单元格】→在【为符合此公式的值设置单元格】中输入公式=$C$5>$H$5→点【格式】→【字体】→选择红色,确定即可。

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

②设置减少显示绿色数据:同样的方法设置E5和F5单元格当$C$7=$C$8时显示白色,当$C$7<$C$8时显示为绿色。

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

7、设置本周和上周每一天的销售金额和订单数,制作旋风图

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

① 本周第1天日期:C13=$C$3+ROW(1:1)-1,公式往下拖;

② 本周第1天销售额:D13=SUMIFS(数据源!$E:$E,数据源!$B:$B,C13),订单数E13=COUNTIF(数据源!$B:$B,C13),公式往下拖;

③ 上周第1天日期:C23=$C$3-8+ROW(1:1), 公式往下拖;

④ 上周第1天天销售额:D23=SUMIFS(数据源!$E:$E,数据源!$B:$B,C23),订单数=COUNTIF(数据源!$B:$B,C23);

⑤ 用【条件格式/数据条】制作旋风图。设置H13=D13,J13=D23公式往下拖得出周一至周日本周和上周的销售额,再用【条件格式/数据条】制作旋风图(旋风图制作方法可回看本号分享的详细教程)

8、设置排行版公式,制作排行版条形图或柱形图

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

① C32=SUMIFS(数据源!$E:$E,数据源!$C:$C,$B32,数据源!$I:$I,周报计算!$C$2)

② F32=LARGE($C$32:$C$36,ROW(A1)),其中,LARGE(array,k),返回数据集中第K个最大值。ROW(A1)返回是1,当公式往下填充时依次得出第1、第2个……最大值。

③ 用LOOKUP函数根据F列的数据查找出第1、第2个……最大值对应的商品情况。E32=LOOKUP(1,0/($C$32:$C$36=F32),$B$32:$B$36)

④ 用E32:F36数据插入条形图,并设置好图形的格式和标签等。为了简化后续多个图表的美化程序,可以将设置好的图表存为模板,下次做图时直接套用。

⑤ 同样的方法可以制作区域排行版、城市排行版,业务员销售排行版和渠道排行版条形图。

⑥ LOOKUP函数的详细教程可关注后回看我前面分享的详细教程,条形图的美化设置可回看我前面分享的图表教程。

第三步:设计日报看板布局。根据展示要求和美观考虑,设计如下所示的布局。

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

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

1、用文本框实现周报大字报数据。

比如本年第20周,是用三个文本框组成,其中显示第20周的文本引用的是【周报计算】工作表中C2单元格的数据。同样析方法将本周销售额、订单数,与上单日最高和最低的金额、订单都用文本框实现。与上周对比情况用粘贴为链接的图片实现。

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

2、 插入日期调节按钮。

点【开发工具】→【插入】→在表单控件中点击【数值调节钮(窗体控件)】→在放置位置划出调节按钮→右键→【设置控件格式】→在弹出的【设置对象格式】对话框中点【控制】→【当前值】任意设个值→【最小值】设为1→【最大值】设为53,因为1年最多增加53周→【单元格链接】点右边的小红箭头后再点选【周报计算】工作表的C2单元格。

Excel销售周报看板,多维度动态图表展示数据,高端大气上档次

3、 将【周报计算】工作表中已经做好的排行版图复制粘贴到相应位置。

第五步:调整配色,美化看板。

将区域之间的单元格设置为浅一点的蓝色,将日报表边的单元格设置成浅一点的蓝色。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>