Excel+MySQL制作某公司销售情况分析仪升级版

一，原始数据一共有6张表：

原数据表.png

二，在MySQL中创建对应的表，并导入数据

（上一篇已展示用sql代码创建并导入数据，此次展示用MySQL Workbench直接创建）
1-创建数据库和数据表

创建.png

2-直接键入表名，字段名，数据类型，约束等，点击Apply应用即可

MySQLWorkbench创建.png

导入数据.png

三，在MySQL中对数据进行处理，得到处理后的总表

use db2;
create table 总表 as
select 区域表.大区,
       产品表.产品,
       人员表.大区经理,
       销售表.城市,
       销售表.城市经理,
       销售表.日期,
       销售表.销量,
       目标表.目标
from
销售表
join
产品表 on 销售表.产品编号=产品表.产品编号
join
人员表 on 销售表.城市经理编号=人员表.城市经理编号
join
区域表 on 销售表.城市编号=区域表.城市编号
join
目标表 on 销售表.识别码=目标表.识别码

得到总表：

总表.png

此时，MySQL中处理已全部完毕，转战至Excel

四，在Power Query，Power Pivot中进行数据处理

1-从MySQL导入数据（总表+进度表）
2-使用Power Query处理数据
原数据中，没有季度，季度周数，年度周数字段
3-总表中添加自定义列
季度=Date.QuarterOfYear([日期])
年度周数=Date.WeekOfYear([日期])
季度周数=number.mod([年度周数,13])
(注意，当周数为13周时，余数为0，此时需要将0进行转换-替换，转换成13)
对以上3个字段，以及其余字段类型进行调整
关闭并上载，保存命名为整合数据
4-进入Power Pivot中进行进一步处理，
在Excel中，创建一张表，键入当周日期，用于判断是否当周
命名为参数表，将其也导入到Power Query中

5-总表中添加自定义列：
当周=if('db2 总表'[日期]>=min('参数表'[周初]),true,false)
6=添加汇总规则
累计销售:=sum('db2 总表'[销量])
目标销售:=average('db2 总表'[目标])
累计完成率:=[累计销售]/[目标销售]
当周销售:=sumx(FILTER('db2 总表','db2 总表'[当周]=true),'db2 总表'[销量])
7-最后，为了把总表，进度表关联起来，添加匹配列
总表：匹配列='db2 总表'[产品]&'db2 总表'[年度周数]
进度表：匹配列='db2 进度表'[产品]&right('db2 进度表'[周数],len('db2 进度表'[周数])-1)
最后，用匹配列将2表关联起来

五，在Excel准备区中做数据准备

1-制作时间参数+名称定义（后面会用到）

2-首先生成一个数据透视表，如下图，按累计完成率排序
发现此时生成的目标销售值，不是一个完整的值，因为做了平均，而目标销售是对应到每个人每个产品上面的，所以需要加入一个切片器（产品）进行校正，比如选择产品A如下，此时显示正确，才可以开始取汇总值

计算计划进度，插入计划进度透视表如下：

（注意，此时在按周数排序时可能出现问题，可以在文件-选项-高级-编辑自定义列表将W1-W13按顺序键入，此时在周数列按照其他顺序排序就可以显示正常了）

3-用SUM函数求出如下汇总数据+名称定义
计划完成率=OFFSET(I6,B3,0,1,1)
【I6为计划进度表中的进度，B3为时间参数表中的周数】

4-制作累计完成率的仪表盘初步模型
完成=累计完成率=13%
未完成=辅助-完成=87%
累计角度=180-180/100%完成=156.69
目标角度=180-180/100%计划完成率=109.8
（角度：从坐标原点沿着X轴正方向定义为0°，往逆时针转动角度为正值逐渐增大）
指针时从坐标0开始到端点0.95的位置
X=0.95COS(RADIANS(累计角度))=-0.87
Y=0.95SIN(RADIANS(累计角度))=0.376
（弧度=RADIANS(角度)）
后续X,Y值算法相似
BGT标签值="BGT,"&TEXT(计划完成率,"0%")
TEXT(value,format_text)，“0%”表示将文本型数字按百分比样式显示
BGT标签值如果存在换行显示，可以在数据标签格式中，取消勾选形状中的文字自动换行
添加指针，右击图形选择数据，可根据需要添加，编辑，删除数据
最后添加文本框=累计完成率

5-制作当周完成率的仪表盘初步模型
算出单周节奏，W1是本身，后面W2-W13就是当周减上一周的差值

按照汇总数据同样的方法，取出当周数据的汇总数据+名称定义
当周目标=目标销售*OFFSET(I23,周数,0,1,1) 【I23为单周节奏表中的进度】

6-制作周度进展的组合图
累计销售第一行=IF(J41<=周数,SUM($$I$41:I41),NA())
【J41为辅助列第一行1】，向下拖拽填充剩余值
如果辅助列周数小于时间参数表中的周数，那么就对相应累计销售进行加和，否则返回错误值
目标(每周的目标值)=目标销售*计划进度

利用上表数据，就可以做周度进展的组合图了

7-添加按钮控件，形成升序降序两种排列方式
再次生成一个数据透视表，对汇总列进行升序排列

在开发工具，插入控件，滚动条
右击设置控件格式，点击单元格连接，将控件，滚动条连接到指定单元格，变换控件，拖动滚动条会显示相应数据变化
滚动条还可以调整最大最小值和步长

8-构建人员数据表
王福州=IF(人员排序=1,OFFSET(准备区!A7,下拉位置,0,1,1),OFFSET(准备区!A76,下拉位置,0,1,1))
如果人员排序=1表示降序，则数据从人员降序表中去取，否则从人员升序表中去取
向右拖拽出3列，向下拖拽出15行（页步长设置为15了），再稍加修饰，添加进度条

六，在Excel分析仪sheet中做组合显示

1-将之前制作好的表，图，控件，滚动条等复制进分析仪sheet，控件，滚动条在拷贝过程中可能指定单元格会发生变化，要再次确认
2-生成需要的切片器以便控制表格值显示，将切片器与报表连接
3-最后调试一下，美化页面即可

销售情况分析仪升级版.png