七周学会数据分析 泽楷

1、 基本的数据类型：文本 整数 小数 布尔 日期 其他类型
变量类型 名义（性别、血型） 有序（好中差） 连续型（身高体重等）

数值前加单引号 将数值转化为文本
单元格格式的设置

2、 excel的基本操作
表格转表：表是数据库的概念
开始-套用格式 选择某一个格式
单元格操作：shift连续多行
复制粘贴-选转置 行和列分开
隔行插入 逆序排列
查找和替换： *表示任意多个字符 ？表示一个字符
选择粘贴：选择性粘贴 数据验证：东,南,西,北
粘贴运算
粘贴图片链接

3、 函数公式
Offset函数，定位新的引用区域
Offset（起点，上下位移，向右移动一列，得到新的基准点，向下取几行，向右取几行）
使用在名称定义里面，

嵌套函数
If（iserror（a1），“error”，“ok”）

4、条件格式：
一单元格的值来设置条件格式
二公式返回的格式来设置格式
一、格式-自定义-只包含以下内容的单元格设置格式
二、使用公式确定要设置格式的单元格
美元符号￥$是锁定单元格用的，要去掉

图标集：套用条件格式
数据条和色阶：
数据条，一组数据中的最大值为最长条，色阶也是
迷你图：折线图、柱形图、盈亏图
插入—迷你图

5、
数据透视图 ：筛选框
在表内排序 ，图也会变
插入——切片器（如果要关联，那么取的底层数据要是同一个）
选项-切片器样式
切片器 和数据透视图表关联

6、基本图表功能

3.1 power query 导入多源数据方法
Power query 整合多数据源数据（数据库，txt，csv，web，hdfs）
突破行数限制
通过M函数灵活创建自定义数据处理计算规则
创建流程可以多次使用
Excel13版以上可以用
数据-获取和转换

仅创建链接
数据-新建查询 导入accdb文件
选择多项√ 加载到 仅创建连接 在表格界面没有变化
数据-显示查询 双击打开
关闭并上载（过程才会保存）

Csv是文本文件
加载到 表

Txt文件导入-从文本导入

3.2 横向 纵向合并多源数据
字段（字段名和记录） 、表、数据库 （层层包含的关系）
横向合并：主表和副表中 字段名有对应的

先选主表-开始-合并查询（即横向合并）-再选副表-选择关键字段-选择id-连接种类-左外部
选择内部（主表有，副表也有）
上面的是左边，下面的是右表
选择展开字段
使用原始列名作为前缀 打勾 显示原来的字段名

连接演示
合并学员信息表1和2 —
合并查询
关闭并上载

纵向合并—
追加查询
一个字段只能有一个字段类型
点击 关闭并上载

3.3菜单栏基本功能
开始----解决错误信息 ：路径下文件不存在，退回到源里面
复制路径，点击回车
填充功能：向下填充—用空值null最上面临近的值填充
向上填充 同理
透视列：透视 将数据按照某种规则进行汇总
转换-透视列-
逆透视功能：转换-逆透视列-逆透视其他列-插入
添加自定义列：添加列-自定义列-选择可用列-输入公式
视图----

3.4 M函数精讲
会改m函数 ，会写m函数
编辑栏、添加自定义列、高级编辑器
在编辑器中输入：=#shared 查看m函数列表

Eg：把身份证号变成一个新的表
开始-新建源-空查询-输入=#shared
转换-到表中
文本数据text 表数据tab
添加列-索引列-
Text.range([身份证号码],16,1)
转换-信息-奇偶
开始-替换值 和ctrl f 一样 ，替换
Let：处理的步骤
In：处理的结果

自动化运行重复性工作
写好逻辑-点击数据刷新

3.5 基本数据处理方法
识别重复行方法： 使用公式countif 计算出现的频率、使用关键字段后使用公式 if 、利用数据透视表（凡是大于一的就是重复的）

去重方法：1 数据-排序和筛选-高级 -选择不重复
识别重复行后删除：
Q中有 开始-删除重复项

查找缺失值：ctrl+f ctrl+g 利用排序筛选
替换缺失值：1使用0填充 2使用平均数填充 3删除含有缺失值的记录 4暂时保留缺失值行

处理异常值：
数值or 文本

处理格式错误：
设置格式
使用分列功能
使用公式补全信息后转换

数据分组：

数据标准化和权重：
Min-max标准化：新数据=（原数据-极小值）/（极大值-极小值）
直接对比一线和二线城市的房价是不合理的，要进行标准
标准分=（原始分-平均分）/标准差

加权平均：
1 横向和纵向对比，横向重要就为1，纵向为0
2 横向加总
3 每个阶段合计值/合计总值*100%

加权平均值=变量11的权重+…+变量n变量n的权重

多条件数据抽取:
(了解一下就可以了)

3.6 power query数据处理案例
处理各大城市3-12月的大气质量数据和城市属性数据 结合

城市属性+城市大气质量=带城市属性的大气数据
三个表合一
将第一行做标题
选择月份-日期-提取年or 月
删除市 –用替换
开始-合并查询-左外部
不勾选2个表里面相同的字段
处理null：替换

4.0 数据透视表的高级方法
对数值的汇总观测方法：透视
1百分比显示
总计的百分比和列总计的百分比
行汇总的百分比
父集中的百分比
添加3%的销售提成的字段（数据透视表中-分析-字段项目于求和）
删除字段：同上-删除
修改字段：同上
添加计算项：同上——字段项目-计算项

2不同维度及层级的汇总显示
3添加计算字段
4删除及修改计算字段
5添加及删除计算项

4.1power pivot导数：加强版的数据透视工具
导入文本 （access文件）accdb csv文件等
企业版的excel才有pivot 家庭版没有
数据-数据管理模型-进入pivot功能

4.2搭建多维数据透视分析模型
多维数据集、立方体 关联在一起不同类别的数据集合
表和表之间匹配的关键字段
关系图视图下选莫哥表的关键字段拖拽到另一个表的关键字段

主页-关系图视图
主页-数据透视表：生成povit的数据透视表

只有当副表的行列标签做行列的时候，才能用副表的行列标签做值

4.3 pivot创建层次结构
选择父集字段-选择创建层次结构-
-选择子集字段-拖拽到父集下面

4.4 pviot DAX表达式：数据分析表达式 pivot特有函数集
和excel的基本函数很相近或相同
Switch （表达式1，结果1……，表达式n，结果n）
Related（合并字段） 类似于vlookup 创建了链接关系才可以用
Filter（表，筛选条件）在筛选条件下把表提取出来
Calculate（计算规则，条件1，条件2）

4.5 pivot使用kpi
三色图标

5.1 数据可视化分析方法论
对比分析——纵向对比 柱状图和折线图
进度分析-仪表盘 温度图 游标图
横向对比-饼图 条形图 复段折线图
标准值 平均值 计划值 对比
子弹图 ：平均对比分析
实际和计划值的对比：对比柱形图
同环比对比：
预警分析：kpi分析 关键指标的达成进度
透视分析：
结构分析：杜邦分析 树形结构分析
变化分析
分组分析
增维分析 ： 折线+环形图

5.2描述性统计图表
频数 频率
直方图： 输入区域 接受区域
分类间距改为0
盒须图：用来体现数据分散情况
四分位数：将数据由小到达排列分成4等份，Q1 Q2 Q3 ,其中Q3-Q1就是盒子 占整个数据的50%。出于三个分割点位置的数值就是四分位数

5.3专业自定义图表制作
迷你图表 五星图表 ：（if（大于，“”，：“”））
公式、公式加条件格式、
嵌套图表：仪表盘：内圈 外圈 环形图 下半圈改成白色
插入文本框

多维折线图：先做折线图 ，再做环形图（小的）
基本图表再创新：
瀑布图：堆积柱形图，底下设为无填充
分段折线图：列的数据 表格分段显示

5.4交互式图表制作
选择数据透视表图，插入 切片器，选择关键字段
右键-报表连接 选择数据透视表
Offset 引用
开发工具——插入-选项按钮-改名字-设置控件格式

5.5power map数据地图
必须要联网下使用-
插入-三维地图-
选择时间，有播放按钮，
无聚合
选择-新场景

5.6 power view 交互式仪表板创建
文件-选项-加载项-com加载项-勾选power view选项
新建一个空的选项卡-然后把原来的拖拽到新的选项卡下面
计数-不重复

6.0杜邦分析仪

6.1业务背景介绍
销售漏斗分析：潜在，接触，意向，明确，投入，谈判，成交、

销售管理分析；客户维度，产品维度，地域维度，销售维度，渠道维度，商机维度
多维度分析

6.2分析仪制作方法
Getpivotdata函数

7.1 MySQL安装及调试
先关杀毒软件
Custom
Mysql 5.7版本
Workbench 6.3
Connector 5.3
记住账号密码
测试 select 1+1；

7.2数据库概述
关系型数据库
非关系型数据库
数据库-表（多个字段的集合）-字段（字段名和记录组成）
Sql可以对应多种的关系型数据库

7.3数据库操作
创建数据库 creat datebase test；
查看创建好的数据库 show creat datebase test；
查看所有的数据库列表 show datebases；
使用数据库： 使用创建好的test数据库 use test；
删除数据库 drop database test；

用sys下的
Code,sql 文件 拖动到软件里面
注释 – #
点击闪电or ctrl 加回车 执行代码
左侧要点刷新按钮
–创建数据库
Creat database test；
–查看创建好的数据库
Show creat database test；
–查看所有数据库列表
Show databases；
–使用数据库
Use test；
–删除数据库
Drop database test；

7.4 数据表操作
1 使用test数据库
use test；
2 创建员工信息表
creat table emp（
Depid char（3），固定长为3的文本型
Depname varchar（20），最大长为20的可变长的文本型
Peoplecount int 整数型
）；
3 查看表是否创建成功
Show tables；

4 删除数据表
Drop table emp；

7.5数据类型
注册时间 ：日期型
用户名： 文本型
充值金额 ：小数型
位：bit 0或1
字节：8个字节组成的 byte
数据类型：具体不同类型的直接长度
一个int整数型占4个byte 长度
Int
Tinyint
Smallint
Mediuint
Bigint
在指定字段数据类型时如果在数据类型后加上unsigned可以禁止负数
Int（11）显示宽度为11

小数型：
Float 4字节
Double 8字节
Decimal

日期型：
Datetime：yyyy-mm-dd hh：mm：ss显示年月日和时分秒
Date：只显示年月日
Timestamp
Year(2|4)

字符串：
Char 固定长度
Varchar 可变长度字符串
防止数据溢出
Blob或text
Tinyblob和tinytext

7.66约束条件
约束是在表上强制执行的数据检验规则，用来保证创建的表的数据完整和正确
#主键约束 primary key 唯一的标识 是非空和唯一的
可以加快数据库的查询速度，
#非空约束 not null
写在要限定字段的后面
#唯一约束 Unique 不能重复
写在后面
#自增字段 auto increment
写在后面
字段名 数据类型 default默认值

#默认值 default 默认情况下从1开始自增
写在后面

Use test；
Creat table emp（
Depid char（3）primary key，
Depname varchar（20）not null，
Peoplecount int unique default 0
）；

8.1填充数据
Insert into 表名（字段1，字段2……）values……

实际应用场景中，下面应用的更多
为Monthly_indicator表导入外部txt文件:
Load date local infile’文件路径.txt’
Into table Monthly_indicator
Fields terminated by ‘\t’ 导入的是txt文件，所以要用分割符\t
Ignore 1 lines; 第一行一般是字段名 所以忽略

检查导入内容 selectfrom xxx；
检查导入数据总行数 select count（）from xxx；
检查表结构 desc xxx；

8.2修改数据表
使用alter table语句修改数据表结构
将数据表xxx改名为xxxdep
Alter table xxx rename xxxdep；
讲数据表xxx中aaa字段数据的数据类型由varchar（20）修改为varchar（30）
Alter table xxx modify depname varchar（30）；
将数据表xxx中aaa字段的字段名改为 bbb
Alter table xxx change aaa bbb varchar（30）；

将数据表xxx中aaa字段的字段名改回卫 bbb，并将该字段的数据类型改回为varchar（20）
Alter table xxx change aaa bbb carchar（20）；

为数据表xxx添加新字段 ddd 新字段的数据类型为varchar（10），约束条件为非空
Alter table xxx add ddd varchar（10）not null；

Change可以改表结构和数据类型，modify只能修改数据类型

将数据表xxx中的name字段的排列顺序改为第一位
Alter table xxx modify name varchar（10） first；

将数据表xxx中name字段的排列顺序改到 bbb字段之后
Alter table xxx modify name varchar（10）after bbb；

删除name字段
Alter table xxx drop name；

查字段类型
Desc xxx

9.1数据整合 函数
主要用于处理数字值
函数 说明
ABS（x） 返回x的绝对值
LOG(X,Y) 返回x的二进制
MOD(X,Y) 返回x/y的模（余数）
RAND（） 返回0到1内的随机值，可以通过提高一个参数（种子）使RAND（）随机数生成器生成一个指定的值
CEILING(X) 返回大于x的最小整数值（进一取整）
ROUND（x，y） 返回参数x的四舍五入的有y位小数的值
SQRT(x) 返回一个数的平方根

Concat（s1，s2…….，sn） 将s1sn连接成字符串

Now（） 返回当前的日期和时间
Date（datetime） 返回datetime的日期值
Time（datetime） 返回datetime的时间值

Group_concat（col）
使用group_contact 函数查询不同sid下对应的所有f_name信息
Select sid，group_concat （f_name） from fruits
Group by Sid

Cast将一个值转化为指定的

Dpdate set 为字段赋值

Delete
删除fid为b5的数据记录
Delete from fruits where fid=’b5’

9.2 sql查询
加减乘除 + - * /
！=不等于 ！<不小于
大于> 大于等于>= 小于类似

聚合类函数
Avg
Sum
Max
Min
Count 计数（和excel一样）

Select cityname ，avg（pm25），avg（pm10）
From monthly_indicator
Where pm25>50
Group by cityname,monthkey having cityname<>’北京’
Order by avg(pm25)desc;

3min

9.3查询操作符与子查询

10.1 使用SQL语句处理及查询电商业务数据1

10.2 使用SQL语句处理及查询电商业务数据2