6步制作Excel动态销售日报看板,数据可视化,老板看了都点赞

一、效果图

二、数据源

数据源为公司2018年1月1日至12月31日的所有销售订单数据。

三、日报看板的制作思路

1、 根据实际需求明确数据分析和展示要素;

2、 根据展示要求设计公式,求出作图所需数据,制作图表;

3、 设计日报看板布局;

4、 根据需要插入日期调节按钮,实现动态查询;

5、将相关的数据和图表放在看板合适的位置;

6、 调整配色,美化看板。

7、数据源放在【数据源】工作表,过程公式及图表放在【过程公式】工作表,日报看板放在【日报看板】工作,三表相互关联又相对独立。

四、操作步骤

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

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

1、用大字报的方式展示当日订单数和销售额。

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

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

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

1、为了方便,我们在单独的“过程公式”工作表设计公式,制作图表。

2、日报看板数据为2018年全年的,所以我们在B4单元格输入2018年的第1天,2018年1月1日作为基础日期,C4单元格为小于364的任意数据(1年共365天,基础有1天,所以最多为364天),后续日期调节按钮将与C4单元格连接按钮的调整将影响C4单元格数据,从而实现动态效果。

3、今日(所选)日期C5=C5=B4+C4,即基础日期加上日期按钮调节值来确定当前所选日期。所选日期的星期用TEXT函数实现,即D5=TEXT(C5,'aaaa') ,昨天的日期公式为 C6=C5-1。

4、用SUMIF函数统计今日销售额和昨日销售额。

今日销售额C7=SUMIF(数据源!B:B,过程公式!C5,数据源!E:E)

昨天销售额C8=SUMIF(数据源!B:B,过程公式!C6,数据源!E:E)

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

今日订单C9=COUNTIF(数据源!B:B,过程公式!C5)

昨日订单C10=COUNTIF(数据源!B:B,过程公式!C6)

6、用IF函数实现今日销售额和订单相比不同情况显示不同。即增加时显示红色向上三角形,相同时显示等号,减少时显示绿色向下三角形,并显示增加和减少比例数据。公式为:

E7=IF(C7>C8,'▲',IF(C7=C8,'=','▼'))

E8=IF(C9>C10,'▲',IF(C9=C10,'=','▼'))

F7=IF(C7>C8,(C7-C8)/C7,IF(C7=C8,0,(C7-C8)/C7))

F8=IF(C9>C10,(C9-C10)/C9,IF(C9=C10,0,(C9-C10)/C9))

如果对SUMIF、COUNTIF和IF等函数不熟悉的可以回看我往期分享的详细教程。

7、用条件格式实现增加比例和向上三角形为红色,减少比例和向下三角形显示为绿色。

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

②设置减少显示绿色数据:选择F7单元格→【开始】→【条件格式】→【新建规则】→【使用公式确定格式的单元格】→在【为符合此公式的值设置单元格】中输入公式=$C$7<$C$8→点【格式】→【字体】→选择绿色,确定即可。

③同样的方法设置E7、E9、F9单元格的条件格式。

8、 设置商品累计销售数据,并制作商品排行版条形图。

① 用SUMIFS函数计算到今日为止商品1的销售金额。公式为C14=SUMIFS(数据源[金额],数据源[商品],B14,数据源[日期],'<'&$C$5),由于我的数据源转化成了超级表,所以公式显示不一样,如对超级表不了解的可回看前面的教程。

② 用LARGE函数计算C14:C19的数据中的排行情况。公式为F14=LARGE($C$14:$C$18,ROW(A1))

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

③ 用LOOKUP函数根据F列的数据查找出第1、第2个……最大值对应的商品情况。公式为E14=LOOKUP(1,0/($C$14:$C$18=F14),$B$14:$B$18)

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

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

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

第3步:设计日报看板布局。

根据展示要求和美观考虑,设计如下所示的布局。

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

1、 插入今天(选定)日期和星期。

在D3单元格输入公式=过程公式!C5,E3=过程公式!D5

2、 插入日期调节按钮。

点【开发工具】→【插入】→在表单控件中点击【数值调节钮(窗体控件)】→在设计的位置划出调节按钮→右键→【设置控件格式】→在弹出的【设置对象格式】对话框中点【控制】→【当前值】设为0→【最小值】设为0→【最大值】设为364,因为1年365天,基础是第1天,最多增加364天→【单元格链接】点右边的小红箭头后再点选【过程公式】工作表的C4单元格。操作动图如下:

第5步:将相关的数据和图表放在看板合适的位置;

1、设置今日总销售金额和订单数的公式

今日总销售额C5=过程公式!C7,今日订单数E5=过程公式!C9

2、 用粘贴链接的图片形式显示销售总额和订单数与前1天对比的情况

选中【过程公式】工作表中的E7:F7单元格区域→右键【复制】→将光标放在【日报看板】工作表C6单元格→右键【选择性粘贴】→【粘贴为链接的图片】→按住ALT将图片与单元格贴合,这样当日期变化,增减情况变化时图片会随着【过程公式】工作表中的E7:F7单元格数据的变化而变化。同样的方法,将【过程公式】工作表中的E7:F7单元格粘贴到【日报看板】工作表D6单元格。操作动图如下:

2、 将【过程公式】工作表中已经做好的5个排行版条形图复制粘贴到相应位置。操作动图如下:

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

将区域之间的单元格设置为浅一点的蓝色,将日报表边的单元格设置成浅一点的蓝色,漂亮的动态销售日报看板就完成了,是不是特别有成就感呢?

(0)

相关推荐