excel dashboard制作_通过Excel实现数据可视化

背景：

某大型电子设备生产厂商，旗下有四种产品，采取总部，大区经理，城市经理的销售管理模式。以季度为节点制定销售目标，每季度划分为13周，销售目标中会制定每种产品的销售节奏，明确规定每个城市经理要完成的每种产品的季度任务额。掌握销售现状，找到影响销售进度的关键责任人，为下一步行动措施提供依据。

业务规则梳理：

本次操作一共有五个表，之间的关系如下图：

通过梳理表与表之间的关系我们可以确定分析指标：

1. 季度完成情况：累计销售，季度目标，累计完成率
2. 当周完成情况：当周销售，当周目标，当周完成率
3. 周度进展情况：13周销售，13周目标，差距
4. 城市经理排名：筛选关键字段-城市经理，城市，完成率

数据处理流程：

1.在MySQL中导入所有的数据表，关联人员表，产品表，区域表，目标表和销售表，将大区，城市，产品，大区经理补充到销售表中，生成用于最后分析的新的销售表。

方法：create table总表（
select 区域表.大区
产品表.产品
人员表.大区经理
销售表.城市
销售表.城市经理
销售表.日期
销售表.销量
目标表.目标
from 销售表
join 产品表 on 销售表.产品编号=产品表.产品编号
join 人员表 on 销售表.城市经理编号=人员表.城市经理编号
join 区域表 on 销售表.城市编号=区域表.城市编号
join 目标表 on 销售表.识别码=目标表.识别码）

最后新的销售表应该具有以下几个因素：

2.打开Excel，用query连接MySQL，导入新的销售表和进度表。

在新的销售表中增加季度，年周数，季周数字段

添加季度：date.quarterofyear([日期]）
添加年周数：date.weekofyear([日期])
添加季周数：number.mod([年周数]，13）
替换季度周数：0，13

3.打开pivot,导入保存的query文件，在新销售表中增加当周新增，添加累计销售，目标销售，累计完成率汇总规则

设立一张参数表，建立周初和周末字段。

当周新增：if(总表.日期>=min(参数表.周初），true，false)
累计销售：sum（总表.销量）
目标销售：average（总表.目标）
累计完成率：累计销售/目标销售
当周新增：sumx(filter(总表，总表.当周新增=true），总表.销售）
sumx和filter函数同时使用，先筛选出当周新增为true的行，再统计所有销售之和。

创建匹配列，建立新销售表与计划表的关联关系，使用季周数+产品作为匹配列

---

4.开始制作dashboard,画出分析仪的草图

1. 创建参数表，在表中添加数据透视表，以当周作为筛选项，季度和季度周数作为行，当周选择true。

2.创建准备表，建立时间参数为季度为1，周数为8，制作人员降序数据透视表。

选择经理数据透视表格局。

3.创建汇总数据

计算计划完成率，制作计划进度数据透视表

计划完成率=offset(以下项目的，8，0，1，1)，以以下项目的参照系，向下8，返回一行一列。得到结果39%。

计算当周目标需要建立单周节奏表

当周目标=目标销售*offset（进度，8，0，1，1）=8307

目标=目标销售*39%=113147

4.制作累计完成表和当周完成表

5.制作周度进展表，制作周度进展数据透视表

6.制作人员升序数据透视表

制作控件，

选择条件为=IF(人员排序=1,OFFSET(准备区!A10,下拉位置,0,1,1),OFFSET(准备区!A80,下拉位置,0,1,1))

7.分别以产品，大区，大区经理，城市经理为选项插入切片器

最后将所有表都进行报表关联