DW 2023年1月Free Excel 第十次打卡 Excel看板

第十章Excel看板

数据下载地址与参考链接：https://d9ty988ekq.feishu.cn/docx/Wdqld1mVroyTJmxicTTcrfXYnDd

数据看板作为数据动态展示的一种重要方式，被广泛的应用于各个领域，因此本节根据1个案例讲解使用Excel制作数据看板的过程，以期起到抛砖引玉的效果，在学习技能的同时也将其应用到实践的工作中。

1.业务背景

打开data/chap10/10.1.xlsx中的数据源，这是某厂家在淘宝、拼多多、京东这3个电商销售平台的销售数据。
问题1：现在你是这个厂家的员工，老板想知道商品在不同渠道的每周销售情况，需做一个数据周报看板。

2.实现分析

首先，需要确定每天所在周数与每周的第几天；

然后，根据确定周数对销售金额进行汇总；

最后，对销售金额进行动态展示。

3.实现过程

**1.**确定周数

确定日期所在的周数用【WEEKNUM函数】，确定日期是第几周可以用【WEEKDAY函数】

在H2单元格输入 =WEEKNUM(B2,2)

在I2单元格输入 =WEEKDAY(B2,11) （如果想得到中文星期几 可以输入 =TEXT(B2,“aaaa”)）

WEEKNUM中的Seria_num 代表要确定它位于一年中的几周的特定日期；
Return_type 可选。为一数字，它确定星期计算从哪一天开始，其默认值为1。（1代表从周日开始算，2代表从周一开始算）。
WEEKDAY中的Seria_num 代表返回星期数的日期；
Return_type 为确定返回值类型的数字。

2.销售基础整理

新建1个工作表，确定动态数据的变量为周数，并确定需要计算的指标，本周开始日期、介绍日期、销售额、订单数、上周销售额、上周订单数、销售额变化、订单数变化。

使用公式求解相关的基础数据。

1、在C3单元格输入公式=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001)。

如果发现数据日期变成了44200，显然就是格式不正确，只需要将单元格的格式转为日期即可。

2、在C4单元格输入公式=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001,0,0,-1)。

3、对于本周的销售额，可以使用SUMIFS函数获得 ，在C5单元格中输入

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

4、对于本周的订单数，可以使用COUNTIFS函数获得 ，在C6单元格中输入

=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

5、对于上周开始日期，可以使用本周开始日期-7，在E3单元格中输入

=C3-7

6、对于上周结束日期，可以使用本周结束日期-7，在E4单元格中输入

=C4-7

7、对于上周销售额，可以使用SUMIFS函数获得 ，在E5单元格中输入

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)

8、对于上周的订单数，可以使用COUNTIFS函数获得 ，在E6单元格中输入

=COUNTIFS(数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)

9、对于相比上周销售额变化，可以使用百分比计算，使用【TEXT函数】确定格式，在C7单元格中输入

=TEXT(((C5-E5)/E5)*100,"0.00")&"%"

10、对于相比上周订单数变化，可以使用百分比计算，使用【TEXT函数】确定格式，在C8单元格中输入

=TEXT(((C6-E6)/E6)*100,"0.00")&"%"

当然也可以直接设置单元格格式为【百分比】。

当然这个【相比上周销售额变化】指标是一个比较重要的指标，可以对这个指标进行格式设置。

选择C6单元格并设置条件格式，判断C5单元格与E5单元格值的大小，如果C5>=E5，那么C6单元值为红，否则为绿色。

3.周销售数据变化趋势

对于每周的数据需要做一个统计，并对每周数据的下单金额与下单数进行整理。

装B13-B19单元格输入星期数。

1、现在需要统计每周1-7的下单金额，因此在C13单元格中输入公式，并向下填充公式

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&'计算过程'!B13)

2、现在需要统计每周1-7的下单金额，因此在C13单元格中输入公式，并向下填充公式

=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&'计算过程'!B13)

接下来就对数据进行可视化展示。

4.周销售渠道整理

对于每周的数据需要做一个统计，并对每周数据的渠道分布进行整理。

装G3-G6单元格分别输入产品的电商渠道京东、拼多多、淘宝

在H3单元格中输入SUMIFS函数求不同渠道的销售金额，并进行向下填充

=SUMIFS(数据源!$F:$F,数据源!$E:$E,G3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

在I3单元格中输入COUNTIF函数求不同渠道的下单数，并进行向下填充

=COUNTIFS(数据源!$E:$E,G3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

接下来就对数据进行可视化展示，将图片进行简单的美化，就可以得到下单金额饼状图与下单数的柱状图。

5.看板绘制

完成了前面的全部计算后，接下来的工作就是将绘制的图结合在一起进行展示。

首先是上述计算的数据与绘图的信息集合到一起。

其中本周的总销量、本周订单数、当前周数、开始日期以及结束日期都是引用计算过程中的值。

本周总销量的值为=计算过程!$C$5

本周总订单的值为=计算过程!$C$6

本周周数的值为=计算过程!$C$2

本周的开始日期为=计算过程!$C$3

本周的结束日期为=计算过程!$C$4

因为本周的变化情况带有条件单元格式，因此不能直接引用，需要将其转化成链接图片的形式，具体操作为

1.复制C7单元格

2.粘贴选择链接的图片

对于本周的周数，需要设置按钮方便调节

1.选择开发工具

2.选择插入，选择数值调节按钮

3.选择设置控件格式

4.设置控件的对应参数

到这里就完成了看板90%的步骤，点击按钮，能看动态展示看板的相关数据。

最后就对看板进行相应的美化，在看板美化过程中也参考了其他EXCEL教程中的配色，因此最后的看板展示效果为

总结

本小节有点小复杂，一点一点的理解还是可以的，但是需要花更多的时间，经过美化后的看板非常美观，辨识度也还不错。

后记

行文至此，以上就是Free-excel教程的全部内容，整个教程的编写过程中也广泛的参阅了各式各样的Excel教程，尽力让本教程变得轻松易学，希望读者能掌握相关的Excel技巧，并能灵活的运用到工作中，面对数字化社会，数据将渗透到生活的点点滴滴，拥抱数字，同时也拥抱变化。

–作者：牧小熊，Datawhale成员，数据分析师