mysql+excel：数据分析----餐饮业日销售情况分析仪

目录

一、介绍

二、KPI指标

三、数据结构信息

四、导入数据库

五、数据处理

六、将mysql数据导入excel中

1、使用ODBC驱动

2、导出newbill表、neworderdetail表和shoptotal表

七、在power query中处理数据 

八、power pivot编辑器处理

1、进入编辑器

2、数据类型处理

3、创建连接

4、创建销售金额KPI

5、创建kpi指标的数据透视表

6、创建数据透视图

7、饼图和数据条

九、最后的效果

---

一、介绍

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

二、KPI指标

• 销售金额：当天店铺实际收入
• 销售金额KPI：每家店的销售金额与所有店的总平均金额的比（如果这家店的销售金额比所有有店的总平均金额，那么这家店的销售金额抬高了所有店的总平均金额，标为绿色，反之，标为红色）
• 折扣额：占销售总额的10%-20%
• 折扣率：10%-20%
• 台数：店铺总台(桌）数
• 翻台率：当日店铺每张桌子被使用的平均次数
• 单均消费：当日店铺所有缴费单的平均金额
• 座位数：店铺实际拥有座位的数量
• 上座率：当日店铺每个座位被使用的平均次数
• 人均消费：当日到店每个人的平均消费金额（销售总额/客流量）

三、数据结构信息

1、单号详细：Bill表

2、点菜明细：orderDetail表

3、 店面情况：ShopDetail表

四、导入数据库

1、bill表（682行）

2、orderdetail表（3410行）

3、shopdetail表（5行）

五、数据处理

1、用orderdetail表创建单汇总金额表（OrderGroup）

以orderdetail表的billnumber字段为汇总依据，求出每条billnumber下pay的加总值。

新表字段：billnumber（单号）、pay（金额）

2、用Bill表与OrderGroup表创建新单号详细表（NewBill）

以billnumber为关键字段关联两表，将OrderGroup表中的pay字段合并到Bill表中，并使用pay与billdiscount字段计算出折扣金额。

新表字段：所有Bill表中的字段、pay（金额）、rebate（折扣金额）

计算逻辑：Rebate = pay * billdiscount

3、用Shopdetail表创建新店面情况表（NewShopDetail）

在原有shopdetail表字段基础上计算并添加allseats字段

新表字段：所有ShopDetail表中的字段、allseats（总座位数）

计算逻辑：allseats = twotable * 2 + three * 3 + fourtable * 6

4、用OrderDetail表与Bill表创建新点菜明细表（NewOrderDetail）

以billnumber为关键字段关联两表，并用Bill表中的shopname与OrderDetail表中的所有字段组成新表

新表字段：shopname（店名）、OrderDetail表中的所有字段

5、用NewBill表与NewShopDetail表创建店汇总信息表（ShopTotal）

以shopname字段为关键字段关联两表，并以shopname字段为汇总条件，创建以下字段

新表字段：

店名： b.shopname

单数： b.billnumber的计数

人数： b.peoplecount的加总

折扣总金额： b.rebate的加总

店汇总金额： b.pay的加总

单均消费： b.pay的合计值/b.billnumber的计数值

人均消费： b.pay的合计值/b.peoplecount的合计值

总台数： s.alltable

总座位数： s.allseats

翻台率： b.billnumber的计数值/s.alltable

上座率： b.peoplecount的合计值/s.allseats

折扣率： b.rebate的合计值/b.pay的合计值

六、将mysql数据导入excel中

因为没有sql server，这里使用的是odbc将数据库的表导出到excel，导出之后再使用power query添加数据源，再进行处理数据

这是个麻烦的方法，ODBC导入数据的时候并不能选择仅创建连接的选项，也不能选择将此数据添加到数据模型。

1、使用ODBC驱动

（1）“数据”->“获取外部数据”->“自其他来源”->“来自数据连接向导”

2、导出newbill表、neworderdetail表和shoptotal表

这里是分步导出的，这里貌似不能多选

 

七、在power query中处理数据 

1、将cateringcase中的billdiscount字段的数据类型改为百分比

2、将cateringcase中的rebate字段的数据类型改为整数

3、 将shoptotal表中的折扣总金额、单均消费、人均消费、翻台率和上座率的数据类型改为整数

4、  将shoptotal表中的折扣率的数据类型改为百分比

5、关闭并上载，之后必须将这三个工作簿加载到数据模型中，不然无法使用power pivot编辑

（1）选中其中一个表，右键，在下拉框中选择“加载到”

（2）如下图所示

八、power pivot编辑器处理

1、进入编辑器

接下来点击power pivot，点击管理进入power pivot的编辑器

2、数据类型处理

3、创建连接

4、创建销售金额KPI

店面销售情况:=average('表_cateringcase_shoptotal'[店汇总金额])/CALCULATE(AVERAGE('表_cateringcase_shoptotal'[店汇总金额]),ALL('表_cateringcase_shoptotal'))

点击“创建kpi”

（1）定义目标值：绝对值1.5

如果一家店的平均值超过了所有店的总平均值的1.5倍，那么该店就是很好的销售情况

（2）定义状态阈值：

最小阈值 1：某家店的平均值等于所有店的总平均值，如果小于1，也就是该店的平均值没有达到所有店的总平均值，图标就为红色。

最高阈值1.2：表示如果某家店的销售平均值所有点的总平均值高于1.2倍，那么就认为这家的销售金额拉高了所有店的总平均金额，图标就为绿色。

5、创建kpi指标的数据透视表

在power pivot中选择

将下面选择的表命名为准备区。

 如下图所示在准备区创建数据表，选择字段 

选中列名和一行数据，复制ctr+c

到另一表（分析仪）中（这个表先去掉网格线）

按照这个步骤将指标一个个已链接图片粘贴过去，将所有连接图片进行组合，调整表格的大小样式和字体同步到分析仪表。

接着在准备区的数据透视表中选中任意一个单元格，选择插入切片器，选择shoptotal表中的店名，确定，将切片器剪切到分析仪的表中，设置样式和调整大小 

   

6、创建数据透视图

用以展示一天当中不同小时的订单数量和金额

还是在power pivot中选择数据透视图

选择字段

 接着隐藏所有字段名和图例，选中图表右键更改图表类型：

 

 最后更改样式即可。

7、饼图和数据条

（1）在power pivot中选择数据透视图

第一个饼图：

第二个饼图：

（2）在power pivot中选择数据透视表

选择在准备区中创建，可以放在第一个数据透视表的后面

下如图所示，选择字段，修改字段名

调整样式，选中列表数据，在开始选项卡中选择条件格式，选择数据条即可。

九、最后的效果