mysql+excel：数据分析----销售情况分析仪表盘

2021/12/24 19:09:50

编程Tag： 数据 mysql excel 添加设置 ---- 销售大区完成率

本文主要是介绍mysql+excel：数据分析----销售情况分析仪表盘，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、介绍

二、业务场景定义

三、目标

四、数据探索

1、将数据导入mysql中

2、数据初步探索

五、数据指标

六、数据处理

1、在mysql中处理

2、在power query中处理（excel）

3、在power pivot中处理

七、销售情况分析仪表盘

1、仪表盘布局

2、第一个卡片的设置（累计完成指标）

3、第二个卡片的设置（当周完成指标）

4、第三个指标的设置（周度进展）

5、第四指标的设置（人员完成升降序图表）

6、将图表放入仪表盘布局中

八、最后效果

一、介绍

这篇文章的学习来源于网上，将csv文件导入mysql workbench进行数据处理加工，然后通过ODBC驱动程序（网上老师用的是sql server导入，这个方法在导入时就可以仅创建链接以及加载到数据模型）将mysql workbench中加工好的数据表导入excel中，并通过power query将数据表添加到数据模型中以及使用power pivot制作分析仪表盘。（有些步骤和教程有不一致的地方）

二、业务场景定义

某大型电子设备生产厂商，旗下有四款主力产品，采取总部、大区经理、城市经理的销售管理模式。

具体执行上，以季度为节点来制定销售目标，每季度划分为13周。销售目标中会详细制定每种产
品每周的销售节奏，明确规定每个城市经理要完成的每种产品的季度任务额。区域经理不背负额
外的任务指标，其销售目标就是下属城市经理的任务总和。

每周一上午，总部销售管理团队会拿到本季度截止到上周末的销售数据，并对这些数据进行分析。

三、目标

掌握销售现状，找到影响销售进度的关键责任人，为下一步行动措施提供依据。

四、数据探索

1、将数据导入mysql中

个人用的是很粗暴的方法，直接导进去，比较慢，使用命令窗口会快点。（网上老师是先一个一个创建表再导入数据的）

就销售表花了大概4分钟多。

为什么总表和进度表后面用了英文命名备了一份呢，因为后面使用ODBC驱动程序导出数据表示，中文名只显示了几个空，这样就导致后面导不出了，所以就重新命了英文名。

2、数据初步探索

（1）查看产品表

select * from 产品表;

产品表里包含产品编号和产品字段，有四种产品A，B，C，D。

（2）查看进度表

select * from 进度表;

因为数据量比较小，只有52条记录，很清晰的就可以看出来，进度表里存放的是每种产品本季度1周到13周的进度，没有缺失值。

（3）查看目标表

select * from 目标表;

目标表记录的是各城市经理的产品目标，包含了产品编号、产品、城市经理编号、城市经理、目标和识别码字段。

（4）查看区域表

select * from 区域表;

select 大区 from 区域表 group by 大区;

区域表主要纪录的是大区下的城市经理，其中包含大区编号、大区、城市编号和城市字段，通过查询可知有7个大区。

（5）查看人员表

select * from 人员表;

select count(distinct(大区编号)),count(distinct(大区经理)) from 人员表;

人员表记录了大区、大区经理和城市经理的对应，包含大区经理、城市编号、大区经理编号、大区经理、城市经理编号和城市经理字段。通过对大区编号和大区经理不重复的计数可得，有两个大区是由一个大区经理来管理的，那么在进行数据分析的时候就不能仅仅用大区经理的维度进行分析，要加入大区维度，因为大区经理和大区不能一一对应。

（6）查看销售表

select * from 销售表;

select min(日期),max(日期) from 销售表;

时间是从2018年1月1日到2018年2月25日的，一共56天，8周，也就是只有8周的销售数据。而目标表是有13周的目标值。

（7）查看ER图

将表和表的字段聚集在一起分析观察，可以看到让销售表来连接其他的表是比较方便的，需要的数据是销售表中的城市字段、城市经理字段、日期字段、销量字段、区域表中的大区字段、人员表中的大区经理字段、产品表中的产品字段和目标表中的目标字段以及进度表的。

利用公共字段将这些表连接起来（除了进度表，因为通过进度表中的产品和进度不是一一对应的，还需要对周数进行匹配才能或能对应的精度），通过城市编号可以连接销售表和区域表，通过城市经理编号可以连接销售表和人员表，通过产品编号可以连接销售表和产品表，通过识别码可以连接销售表和目标表进度字段。（这里的连接使用的公共字段不是唯一的）

以下是需要的数据：

而进度表中不同产品不同周数的进度可以不合并在销售表上，直接用于数据分析。

五、数据指标

1、本季度的完成情况：掌握销售总进度

累计销售
季度目标
累计完成率

2、当周完成情况：掌握上周销售情况

当周销售
当周目标
当周完成率

3、周度进展情况：掌握实际与计划的节奏匹配

13周销售
13周目标
差距

4、城市经理排名：锁定关键责任人

城市经理
城市
完成率

六、数据处理

1、在mysql中处理

（1）关联人员表、产品表、区域表、目标表和销售表，将大区、城市、产品、大区经理内容补充进销售表，并生成用于分析的新销售表。

create table all_datas (select
	区域表.大区,
	产品表.产品,
	人员表.大区经理,
	销售表.城市,
	销售表.城市经理,
	销售表.日期,
	销售表.销量,
	目标表.目标
FROM 销售表 JOIN 产品表 on 销售表.产品编号 = 产品表.产品编号
			JOIN 人员表 on 销售表.城市经理编号 = 人员表.城市经理编号
			JOIN 区域表 on 销售表.城市编号 = 区域表.城市编号
            JOIN 目标表 on 销售表.识别码 = 目标表.识别码);

返回2988行数据，没有问题

（2）导出数据表

首先在ODBC中添加数据源

在用户DSN下，点击添加

选择下面这个数据源

填一些信息

就ok啦

2、在power query中处理（excel）

（1）将数据库的数据表（all_data、schedule）导入excel中

导入进度表（schedule）也是一样的步骤。

（2）启动power query

选择当前这个xlsx文件，我自己使用中间那组数据

（3）在all_data表中添加季度、年度周数和季度周数字段

1）添加季度字段

2）添加年度周数

3）添加季度周数

4）完成后

（4）添加匹配列，也就是进度表与总表的公共字段

all_data表中：

schedule表中：

（4）检查数据类型

1）将季度、年度周数、季度周数改为整数，添加的匹配列改为文本类型

2）注意日期类型是不是日期

3）查看schedule(进度表)中的进度是不是百分比类型

（5）关闭并上载

然后将这两个表加载到数据模型中（选中表右键，选择加载到）

3、在power pivot中处理

（1）检查数据类型

（2）表的关联

（3）创建汇总规则

累计销售 = 本季度所有的销售值总和

将所有的销售值全部加起来就可以了。

累积销售:=sum('all_data'[销量])

季度目标（目标销量）= 所有目标值总和的平均值（因为同一个产品有很多个重复的目标值，使用切片器筛选之后还是该产品那个目标值）

目标销售:=AVERAGE('all_data'[目标])

累计完成率 = 累计销售/目标销量

累计完成率:=[累积销售]/[目标销售]

到这里，第一个数据指标需要的数据就完成了

当周销售 = 就是截止到目前的最后一周（2018/2/19-2018/2/15）的销售

当周销售:=SUMX(FILTER(all_data,all_data[日期]>=Date(2018,2,19)),all_data[销量])

七、销售情况分析仪表盘

1、仪表盘布局

（1）设置列宽和行高

列	A	B	C	D	E	F	G-L	M	N	O	P	Q	R	S	T	U	V	W
列宽	0.5	1	6	10	15	1	复制A-F	0.5	1	30	1	0.5	16	0.5	8	0.5	8	0.5

行	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
行高	5	15	15	5	5	10	15	80	15	10	5	10	15	120	10

（2）调整样式

去掉网格线，填充为灰色

（3）统一提前设置主题、字体、颜色

（4）设置布局

合并如下框的区域，编辑题目

T2和V2是季度和周数的体现

设置卡片的背景颜色

2、第一个卡片的设置（累计完成指标）

（1）数据透视表

在power pivot中选择数据透视表并添加切片器，添加到准备区表格里

如下图所示：

（这个透视表就可以展示每个经理的销售情况，添加切片器，做每种产品的销售请况统计）

（2）做汇总数据

计划完成率的汇总计算需要进度表

在power pivot中添加数据透视表，如下图所示，计划进度透视表中的进度是累计进度率，所以在汇总累计数据时，直接引用即可。

（如果这里的行标签不能正确排序，则可以在选项的高级里面编辑自定义列表，添加从W1-W13即可）

offset(i7,8,0,1,1)，意思是取i7单元格下方第8个单元格值，第8周的进度就是计划完成率，（数据目前也只是进行到第8周）。

完成后将这四个汇总数据进行名称定义，方便引用

（3）第一个卡片的数据可视化（累计完成率的图表）

这个是最后要完成的图表样子，图表是由散点图和环形图构成的，外环包括累计完成率值，未完成率值和辅助值。

为什么要用到辅助？

下图是没有使用辅助数据的环形图，取不出半圆的数据

然后外环的设置就先到这里，将不需要的那个半圆放置下半部分，设置起始角度270度，将背景填充为深灰色方便自己看，然后将下半圆无填充无轮廓，设置累计完成率部分填充为白色，未完成部分无填充。

接下俩是指针的设置，使用的是带直线的散点图，需要两组数据分别作为端点，第一组是原点（0，0），第二组需要计算，根据累计完成率计算出角度，然后使用正余弦公式计算出x,y值。

累计角度：180-累计完成率 * 180

半径：0.95，意思是不覆盖外环的外边线（圆环的半径为1）

带直线的散点图需要两个端点，一个端点为原点（0，0），另一个端点利用正余弦求（x,y）

（L15单元格时半径对应的单元格数值）

根据上面的圆环图可知，需要的指针大概是这样的

所以外端点在第二象限，角度应为180-13%*180，根据正余弦公式，x=r*cos(radians(角度))，y=r*sin(radians(角度))。

把横纵坐标的刻度调整好后，如下图所示

（一开始添加端点数据时，要将其更改为带直线的散点图类型）

再接下来还是指针的设置，这个是计划完成率的指针，这样和累计完成率的指针就有对比了

标签：

目标角度：

端点（x,y）:

选择数据，添加之后

继续添加左边线和右边线，也是给出半径和角度求端点

为什么一个半径选0.7，一个半径选1呢，因为要在两条外环所在的半径上取得，外圆环得半径是1，要取外圆环两边得端点半径就设为1，内圆环设置得半径为0.75，选择0.7可能更好看点吧，如果选择0.75不就和圆环的边线完全重合了么。

调整样式：

添加文本框，内容等于计划完成率：

3、第二个卡片的设置（当周完成指标）

（1）数据汇总

当周目标需要当周的计划进度*总目标销售，因为进度表给的进度数据是累积的，所以需要计算出每周的计划进度，也就是单周节奏

完成后将这三个汇总数据进行名称定义

（2）当周完成数据可视化

删除目标值的数据

复制前面做好的图表，选择数据编辑，绑定当周完成指标的数据

调整样式：

4、第三个指标的设置（周度进展）

（1）数据透视表

在power pivot中选择插入透视表，位置还是在准备区

计算每周的累计销售

计算每周的累积目标销售

添加不及效果和超过效果

查看实际销售和目标销售相比的情况，不及就是实际的累积销售赶不上累积目标销售，超过就是累计销售超过了目标销量

首先不及的x坐标轴为8，表示在第八周的数据上显示，第一个y点的公式是 =IF(累计销售<目标销售*39%,累计销售,NA())，如果累积销售小于累积目标销售（目标销售乘以第八周的累计进度就是第八周的累计目标销售）就返回累计销售值，第二个y点的公式是 =IF(累计销售<目标销售*39%,目标销售*39%,NA())，如果累计销售小于累计目标销售就返回累计目标销售。