6步制作Excel动态销售日报看板,当日数据对比、排行版一目了然

2024-06-25 50

今天给大家分享一个美观又实用的动态销售日报板。下次我会分享月报和年报展板。如果您需要,请保存以备后用,并关注后续课程

1、效果图

2数据来源

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

3创建每日公告板的想法

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

2、根据显示需求设计公式,获取绘图所需数据,制作图表;

3、设计每日公告栏布局;

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

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

6搭配颜色并美化标牌。

7数据源放置在【数据源】工作表中,流程公式和图表放置在【流程公式】工作表中,日报表放置在【日报表】工作表中。彼此但又相对独立。

四、操作步骤

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

案例展示的要素和方法是:

1、用大字海报展示当天的订单数和销量。

2与前一日订单和销售额的变化相比,如果向上的红色三角形增加,则数据为红色,如果向下的绿色三角形减少,则数据为绿色。由于三角形和数据需要显示在一个单元格中,因此它们以图像的形式显示并附加链接。

3、以条形图展示当日产品排名、区域排名、城市排名、销售人员销售排名、渠道排名。

步骤2:根据显示元素要求设计公式,获取图像数据并创建图表。

1为了方便起见,我们在单独的“流程公式”工作表中设计了公式并创建了图表。

2、每日公告板数据为2018年全年,所以我们在B4单元格输入2018年第一天,2018年1月1日作为基准日期,C4单元格包含小于364条的数据(一次总共365条)一年中的第几天,基数为1天,所以最大为364天),下一个日期调整按钮会链接到C4单元格,调整按钮会影响C4单元格的数据,从而达到动态效果。

3、今天(选定的)日期为C5C5B4C4,即基准日期加上日期按钮调整值,确定当前选定的日期。所选日期的星期几是使用TEXT函数实现的,即D5TEXT(C5,'aaaa')。

4使用SUMIF函数计算今天的销售额和昨天的销售额。

今日销量C7SUMIF(数据源!B:B,流程公式!C5,数据源!E:E)

昨天的销售额C8SUMIF(数据源!B:B,流程公式!C6,数据源!E:E)

5、使用COUNTIF函数统计今天的订单和昨天的订单。

今日订单C9COUNTIF(数据源!B:B,处理公式!C5)

昨天的订单C10COUNTIF(数据源!B:B,处理公式!C6)

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

E7IF(C7>C8,'▲',IF(C7C8,'','▼'))

E8IF(C9>C10,'▲',IF(C9C10,'','▼'))

F7IF(C7>C8,(C7-C8)/C7,IF(C7C8,0,(C7-C8)/C7))

F8IF(C9>C10,(C9-C10)/C9,IF(C9C10,0,(C9-C10)/C9))

如果你对SUMIF、COUNTIF、IF等函数不熟悉,可以参考我之前分享的详细教程。

7使用条件格式增加比例,向上的三角形显示为红色,减小比例,向下的三角形显示为绿色。

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

②设置减少绿色数据显示:选择单元格F7→【开始】→【条件格式】→【新建规则】→【使用公式确定格式化单元格】→输入【为与此公式匹配的值设置单元格】Formula$C7美元

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

8定义累计产品销售数据并创建产品排名条形图。

①使用SUMIFS函数计算到目前为止产品1的销售数量。公式为C14SUMIFS(数据源[数量],数据源[商品],B14,数据源[日期],'

②使用LARGE函数计算数据C14:C19的排名。公式为F14大($C$14:$C$18,行(A1))

其中,LARGE(array,k)函数返回数据集中的第K个最大值。ROW(A1)的返回值为1。公式填写时,依次获取第一个、第二个……最大值。

③利用LOOKUP函数根据F列的数据查找第一、第二…最大值对应的产品情况,公式为E14LOOKUP(10/($C$14:$C$18F14),$B$14:$B$18)

④使用E14:F18数据插入条形图,然后设置图表格式和标签。为了简化后续美化多个图表的过程,您可以将一组图表保存为模板,并在下次创建图表时直接应用。

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

⑥同样的方法可以创建区域排名、城市排名、销售队伍销售排名、渠道排名条形图。

第三步:设计每日公告板布局。

根据外观要求和美观考虑,设计布局如下图。

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

1输入今天的日期和星期(选定)。

在D3单元格中输入工艺公式公式!C5,在E3单元格中输入工艺公式!D5

2输入日期调整按钮。

单击【开发工具】→【插入】→单击窗体控件中的【数值调整按钮(窗体控件)】→在设计位置绘制调整按钮→右键→【设置控件格式】→在弹出的【设置]在对话框中间,单击[控制]→[当前值]设置为0→[最小值]设置为0→[最大值]设置为364,因为有365天一年,以第一天为基数,增量最大为364天→【单元格链接】单击右侧红色小箭头,然后单击【工艺公式】工作表中的C4单元格。操作动画如下:

第五步:将相关数据和图表放置在黑板上适当的位置;

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

今天总销售额的C5流程公式!C7、今日订单号处理公式C9

2以图片形式显示与前一天相比的总销售额和订单数,并附有链接。

选择[处理公式]工作表中的单元格区域E7:F7→右键单击[复制]→将光标置于[每日看板]工作表的C6单元格→右键单击[选择性粘贴]→[粘贴为链接图像]→按住ALT可随单元格调整图像,这样当日期变化、增减变化时,图像会随着【处理公式】工作表上单元格数据E7:F7的变化而变化。以同样的方式,将[工艺公式]工作表中的单元格E7:F7粘贴到[每日报告]工作表中的单元格D6中。操作动画如下:

2将【工艺公式】工作表中准备好的五个排名条形图复制并粘贴到适当的位置。操作动画如下:

第六步:调整配色,美化板面。

将区域之间的单元格设置为浅蓝色,并将每日报告旁边的单元格设置为浅蓝色。一个漂亮的动态销售日报板就完成了。

感谢您的关注和支持。其他EXCEL技能请关注“EXCEL学习微课”。如果您今天需要原文档【每日销售报告】,可以评论转发并私信联系我!

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