Excel Power BI商业智能

DAY1：【数据分析概述】&【分析工具Excel概述】 #CDA学习打卡 #CDA数据分析师

1、什么是数据分析

2、知识、智慧、信息和数据的不同定义

3、SQL和Excel在数据分析中的定位

4、业务人员、IT技术人员及分析人员的不同职责

5、数据化驱动业务对企业的意义

6、数据分析方法分类 - 业务数据分析、数据挖掘分析、大数据分析

7、业务数据分析流程

8、商业智能（BI）和商业洞察

9、数据可视化分析方法 - 对比分析、结构分析、透视分析、其他分析方法

10、可视化&交互式是数据分析真正移交阅读者主动权的特点

11、Excel的原理、内外部对象结构、历史、五大必备功能和Power BI四项功能（Power Query、Power Pivot、Power View和Power Map）

DAY2：【第二章：Excel基础】&【第三章：Excel数据加工处理及基本公式介绍】#CDA学习打卡 #CDA数据分析师

【“表格”结构数据与“表”结构数据】

12、表格结构数据：以单元格为基本数据存储及操作单位 -> 处理批量数据效率低

表结构数据：以字段（列）为基本数据存储及操作单位，比单元格高一个维度 -> 易于处理批量大量数据，是数据分析的全场景

e.g. Excel中的已建表，方便批量进一步操作；还可以导入关联外部数据源并与之同步更新，在不同表之间进行快速连接，包括通过Power Query和Power Pivot进一步处理。

————————————————————————————————————————————————————————

【Excel数据透视图表与切片器】

13、数据透视表：由“筛选器”、“行/列标签”、“值”构成的二维值汇总表；易操作性、可计算性、数据处理方法多

@数据透视表用来将一维原始数据转换为由行列构成的二维值汇总表。

14、数据透视图：基于数据透视表生成的图表，用户可以通过鼠标拖拽来快速更改图标显示结果；没有Excel基本图表类型中的散点图和气泡图。

@数据透视图既可以通过数据透视表生成也可以直接引用一般表格数据生成，此时会自动生成与其对应的数据透视表，一一对应。

！图和表的升降排序顺序相反

15、切片器（2010版本以上）：当切片器与数据透视图表关联后，对切片器进行选择时，数据透视表会一起发生变化。

@切片器与数据透视图表的组合应用是生成BI报表高度交互性可视化界面的基础。

！只要是同一个底层数据源下，不同数据透视图表都可以通过同一个切片器（右键，报表连接）控制

————————————————————————————————————————————————————————

【Excel条件格式与迷你图】

16、条件格式

1）以单元格值为基础，设定条件格式：

e.g. “单元格值介于15和25之间的单元格标为黄色”，全选单元格，只为包含以下内容的单元格设置格式，单元格值-介于-15到25，格式为填充黄色

2）以公式返回值为基础，设定条件格式：

e.g. “第二列值大于第一列值时将第一列中单元格标为黄色”，只选第一列，使用公式确定要设置格式的单元格，=B9>A9（勿锁定！），格式为填充黄色

e.g. “将位于偶数行的单元格标为黄色”，全选单元格，使用公式确定要设置格式的单元格，=MOD(ROW(),2)=0(ROW函数返回行号），格式为填充黄色

17、图标集

用图标的不同状态来标注当前单元格值与阈值间的对比关系。注意，1）三种状态、四种状态和五种状态三种类型的图标集；2）数字、百分比、公式及百分点值（PERCENTILE）四种阈值类型。

@红黄绿三色“红绿灯”图标使用最为频繁，如果选择困难可以优先使用

18、数据条、色阶

数据条：用数据条的长短来标注各单元格值的大小关系，以一组单元格中的最大值为最长条形，用数据条的长短变化来标注其他单元格值与次单元格值的对比关系。

色阶：用不同颜色变化来标注各单元格值的大小关系，以一组单元格中的最大值为最重颜色，用颜色变化来标注其他单元格值与此单元格值的对比关系。

@如果要利用数据条或色阶来体现各部分值与汇总值之间的占比关系，选定单元格区域应包含汇总值，形象观察部分和整体的关系

19、迷你图

是放入单个单元格中的小型图，代表所选内容中的一行数据；折线图、柱形图、盈亏图。

@迷你图可以使用在表格中与单元格结合使用，令报表阅读者可以通过图表直观掌握到具体表格数据的对比趋势，加深对抽象数据的理解及印象。

————————————————————————————————————————————————————————

【Excel函数公式】

20、函数公式

用来对单个单元格或指定单元格区域进行计算后返回计算结果，返回可以是数值、数组或单元格地址等。函数由返回值、等号、函数表达式、参数、操作符五部分组成。

OFFSET函数：以指定引用为参照系，通过给定偏移量返回新的引用区域，OFFSET(reference, rows, cols, height, width)（rows从上到下，cols从左到右，height是向下取m行，width是向右取n列）-> 用于名称定义，e.g. 名称指定到对应的图表的名称进行数据源使用，实现图表动态展现效果。

21、嵌套函数

将内部函数的返回值作为外部函数的参数使用的方法，使用时内部函数的返回值数据类型一定要与外部函数的参数数据类型保持一致。

基本数据类型包括文本型text、数值型numerical、布尔型boolean（true或false，不是0或1）、数组array

e.g. IF(ISERROR(I2),"ERROR","OK")

————————————————————————————————————————————————————————

【Excel基本图表】

22、基本图表类型

柱形图（基于分类）：1个变量；多个分类项目比较

柱形图（基于时间）：1个变量；不同时点的数值比较，适用时间点偏少

条形图：1个变量；多个分类项目比较

折线图：1个变量；不同时点的数值比较，适用连续时间段

饼图：1个变量；整体中各部分的占比

散点图：2个变量；数据间的分布及关联性，可添加回归公式和趋势线

气泡图：3个变量；数据间的联系比较，用每个点的不同大小代表第三个变量

雷达图：多个变量；多重数据间的比较，多重维度间平级关系

————————————————————————————————————————————————————————

【Excel常用函数】

23、

1）数学函数：SUMIF、SUMPRODUCT、MOD

2）文本函数：LEFT、MID、RIGHT、REPT

3）逻辑和引用函数：IF、ISERROR

4）查找函数：VLOOKUP、HLOOKUP、MATCH、INDEX

5）日期和时间函数：TODAY、WEEKDAY

6）统计函数：COUNTIF

ex1. 根据身份证号提取性别和出生年月并计算年龄

left()/mid()/right() mod() year(today()) =E3&"性"

ex2. 利用2015年节假日统计表计算具体日期及对应的星期数

date(year,mth,day) weekday(date(...))

ex3. 使用INDEX及MATCH组合函数查找标黄部分相关信息

@定位索引列前方列信息，用index()嵌套match()实现vlookup()的效果

- MATCH(lookup-value要查找的值, lookup-array查找连续区域, match-type查找方式，1升序/-1降序/0任意)

- INDEX(array查找区域, row-num返回值所在行号, column-num返回值所在列号，唯一时可不填)

ex4. 用HLOOKUP公式查找出H列标黄部分值

！横向查找；！一般False精确匹配

HLOOKUP(lookup_value要查找的值, table_array查找区域, row_index_num返回数据在区域的第几行数, range_lookup模糊匹配/精确匹配的T/F)

ex5. 使用VLOOKUP函数求I列、K列及M列标黄部分值，并用ISERROR与IF函数的嵌套函数处理异常值

VLOOKUP(lookup_value要查找的值, table_array查找区域, col_index_num返回数据在区域的第几列数, range_lookup模糊匹配/精确匹配的T/F)

ISERROR返回Boolean值，注意存在错误时为T，无异常时为F

ex6. 用SUMPRODUCT函数计算H列标黄单元格值

将数组间的元素相乘后返回乘积之和

=SUMPRODUCT($D$2:$G$2,D4:G4)

=SUM(D2:G2*D4:G4)

按条件收入合计：

SUMIF(range, criteria, [sum_range])

多条件汇总：销售人员为“苏术平”且国家地区为“加拿大”的销售收入总和

=SUMPRODUCT((($D$2:$D$50=$L$8)*($C$2:$C50=$C$3)),$G$2:$G$50)

#用*号在SUMPRODUCT中表示条件间的AND连接，用+号表示OR连接

按条件统计订单数量：

COUNTIF(range, criteria)

用REPT在单元格内做虚拟条形图，表现趋势：

REPT(text返回值, number_times返回遍数)

————————————————————————————————————————————————————————

【Excel基本数据处理方法】

24、处理数据室数据分析流程中的起点，也是耗时最多的环节。

数据处理方法：

1）处理重复数据

针对单列数据识别重复行：

I. 使用公式COUNTIF

II. 排序关键字段后使用公式IF

III. 利用数据透视表计数汇总

针对多列重复的去重：

I. 数据-排序和筛选-高级

（Power Query，删除行-删除重复行）

II. 识别重复行后删除

@需要结合业务需求决定删除和保留重复行

2）处理不完整数据

查找缺失值：

I. 使用 Ctrl+F

II. 使用定位条件 Ctrl+G，定位格式为“空值”

！眼睛看上去是空白的不一定就没有值

III. 利用排序筛选

处理缺失值：

I. 使用0替换数值类缺失值

II. 使用平均值替换数值类缺失值

III. 删除含有缺失值的记录或不对此类记录进行操作

IV. 暂时保留缺失值行，（填充色区分或添加辅助列标注，方便快速定位），在有必要时再进行处理

3）处理异常值

！{编号}一般是文本型格式，位数相同，不合系列逻辑很可能是异常值。e.g. 原始数据0001转入Excel可能作为数值型变成1，这时需要转回文本型并补齐前方的000

！0值在日期格式下是“1900/1/0”，确认异常值

！数值型字段出现“0.0.XXXX”或数量级差距巨大可能是误输入的异常值 -> 可以设置平均值的倍数作为阈值；或散点图的outlier；或正态分布3倍标准差外的outlier……

4）数据分组

利用先计数再设置不同计数对应的分档，用辅助列+VLOOKUP对应区间定义文字，最终返回区间定义文字导目标列

5）转换变量类型

处理格式错误数据：

I. 设置格式

II. 使用分列功能，列数据格式为“文本”

III. 使用公式补全信息后，转换成日期型

2001-10-02=LEFT(C2,4)&"-"&MID(C2,5,2)&"-"&RIGHT(C2,2)

@常需要相互转换的数据类型包括文本型、日期型、数值型

6）数据标准化

I. Min-Max标准化：新数据=（原数据-极小值）/（极大值-极小值）

@适用于量级差异大的相同维度间进行比较

II. 使用标准分进行标准化：标准分=（原始分-平均分）/ 标准差 STDEV

@适用于评价标准或许不同的相同维度间进行比较

7）设置变量权重

III. 加权平均

利用交叉表设置权重：

- 纵向和横向对比，横向重要则为1，纵向重要则为0

- 横向加总，每项都加1，保证数值最低项合计值不为零，以免没有意义无法后续进行除法计算

- 每个阶段合计值/合计总值*100%

- 加权平均值 = 变量1*变量1的权重 +……+变量n*变量n的权重

主要数据类型：文本型、整数型、小数型、布尔型、日期型、其他 类型（会计、特殊）

主要变量类型：名义型（彼此间没有顺序关系，只表示分类的数据，例如姓名）、有序型（有顺序关系的数据，例如优良中差）、连续型（包含若干小数位，取值密集，例如温度 ）

+思考题：用唯一的数字区分开唯一的业务类型，形成映射关系，方便进一步数据处理和分析

取一个1-3之间的随机小数：=RAND()*(3-1)+1 取一个1-3之间的随机整数：=ROUND(RAND()*(3-1)+1,0)

————————————————————————————————————————————————————————

【Excel基础描述性统计类图表】

频数：是落在各类别种的数据个数

频率：是各类别频数与总频数之比

@频数和频率分别从绝对数和相对数上，反映出数据在各变量值上的分布状况。

25、直方图

用来展现数据在不同组距间分布状态的图表，组距=（最大值-最小值）/ 组数，组数根据实际业务情况设置。

！柱形图看高度， 直方图看面积

26、盒须图（箱型图）

用来体现数据分散情况。将数据由小到大排列并分成四等份，处于三个分割点位置的数值就是四分位数。

上边缘 = Q3+1.5*（Q3-Q1）

下边缘 = Q3-1.5*（Q3-Q1）

DAY3：【第4章: Power Query多源大规模数据加工处理方法】 #CDA学习打卡 #CDA数据分析师

【Power Query概述及导入多源数据】

27、Power Query：多源大规模数据加工处理方法

- 提取整合多数据源数据，包括各种关系型数据库、Excel文件、txt格式（制表符Tab分割）及csv格式（逗号comma分割）的等文本文件（只有数据信息&分隔符，没有其他信息）、Web页面、Hadoop的HDFS等等

- 突破Excel表格的数据限制，可快速处理几百万甚至上千万行（大约2G）的数据

- 提供丰富的数据处理分析功能

- 可通过M函数灵活创建自定义数据处理及计算规则

- 创建好的数据处理流程可以无限次复用

@2016以上版本：数据-“获取和转换”功能组

！勿通过数据-获取外部数据进行导入

数据导入方式一：仅创建连接，只是将外部数据源导入到Power Query的查询引擎中，而在Excel表格界面中不会留下任何数据痕迹——适用于只需要在Power Query中进行数据处理，而不需要Excel表格界面中对导入数据进行预览或单元格计算时，可节省文件容量，减少计算压力

数据导入方式二：表，不仅可以将外部数据源导入到Power Query的查询引擎中，同时在Excel表格界面中生成一份与Power Query相关联的Excel工作表数据。在Power Query中发生的变化，点击加载后Excel工作表中的数据也会随之发生变化。——适用于需要在Excel表格界面中使用数据时

@“加载到…”支持两种方式间的切换

@Power Query进行修改后，记得左上角点“关闭并上载”或“关闭并上载至…”（第一次加载时未设置加载规则的情况）

———————————————————————————————————————————————————————

【Power Query合并数据】

字段的集合就是数据表，数据表的集合就是数据库。每张表字段首行叫标题行，标题行中内容叫字段名，第二到最后一行叫记录，记载了数据内容。

存放最终合并结果的叫主表，为主表提供必要信息的表叫附表。

28、横向合并数据：将附表中有而主表中没有的字段信息合并至主表中，从而充实完善主表信息的方法。即合并列，添加新的字段进来。

选中主表，合并-合并查询

1）确定用于合并两表的关键字段（主附表中都有且值对应的上的字段，最好没有重复值）

2）进行合并操作

3）选择需要展示的字段信息（连接过来的Table附表展开后设置）

@：两表若担心同名字段存在，可以勾选上“使用原始列名作为前缀”

@1：当两表中用于合并的关键字段值不是一一对应时，不同的联接种类会出现不同的联接结果（默认选“内部”，另一种是“左外部”）

@2：当关键字段中有重复值时，连接后的总行数为关键字段值重复出现次数的乘积

29、纵向合并数据：即添加行，在现有数据的基础上添加记录。

选中主表，合并-追加查询，“两个表”或“三个或更多表”

1）将有相同字段名的字段纵向合并到一起

2）将不同字段名的字段追加在最后

3）非匹配字段标记为“null”值

@Excel是否自动添加标题行：看导入的原始数据的第一行的数据类型是否和下方所有记录数据的数据类型完全一致，是就会默认首行也是记录并添加系统标题行 -> Power Query，转换-将第一行用作标题

！一个字段只允许有一种数据类型，将文本型字段与数值型字段成功合并后，将会改变字段类型为文本型（因为数值可以变成文本，而文本无法变成数值！！！）

———————————————————————————————————————————————————————

【Power Query基本功能】

30、

1）“开始”选项卡：对数据进行上载查询、增减行或列、拆分列、分组、改变数据类型、更改属性、导入及合并外部数据等基本操作。

2）“转换”选项卡：提供了针对行与列间的结构性加工处理功能、数据类型、格式等设置功能以及基本的函数计算功能。

选中目标列，转换-任意列-填充，向上/向下

！空值很危险，null，不知道位数也不知道下一步该如何操作->转换为已知状态的真正控制，比如“-”

选中目标列，转换-任意列-透视列，文本型只能计数

选中目标列，转换-任意列-逆透视列-逆透视其他列，即从二维交叉表转换为一维表

3）“添加列”选项卡：针对字段信息的加工处理，包括添加新字段、更改字段格式、位字段增加计算公式等（其中大部分功能与转换选项卡中功能重复，但适用于新添加的字段上的处理）。

选中目标列，添加列-常规-自定义列，进行参数定义即可

4）“视图”选项卡：用来显示或隐藏“查询编辑器“中的主要功能区域。

@查询在Power Query 中应用的步骤都通过M函数形式保存在查询设置中。

@“数据透视”：按照某种规则将数据进行汇总，汇总行列标签结果形成二位汇总表

———————————————————————————————————————————————————————

【Power Query M函数】

31、编辑M函数（Power Query 80%的能力，提高重复性工作效率）方法：

600+个，专用于数据处理的编程语言，先要会改，后要会写！M函数先继承上一步处理的结果，在此基础上进行目前本身处理，生成本身的处理结果，依次循环……

1）通过“编辑栏”、“添加自定义列”或“高级编辑器”(批量写入M函数的处理逻辑）等功能了解M函数表达式写法

2）根据需求重新编辑表达式程序

3）编辑错误通过“查询设置”区域退回重新编辑

4）新建一个空查询，再在fx编辑器中输入“=#shared”可以查看M函数List列表，转换-到表中 可以转为Table表数据方便通过筛选按钮查询定位，例如Text. 或者Date. 等等

- 添加序号列：添加列-常规-索引列，Table.AddIndexColumn()

- 重排序的列：鼠标选中拖拽，Table.ReorderColumns()

-提取数据：添加列-从文本-提取，Text.Range()（从前一位往后取一位数！）

- 转换数据类型：开始-转换-数据类型，设置为“整数”Int64.Type,Table.TransformColumnTypes()

+文本数据类型：type text

+日期数据类型：type date

- 添加新字段：Table.AddColumn()

- 判断奇偶：转换-编号列-信息，设置为“偶数”

- 替换值：开始-转换-替换值，Table.ReplaceValue()

32、Power Query高级编辑器

let-in结构，let中记录的是每一步操作步骤，in里面记录的是最终显示出来的处理结果，let等式左边是当前操作的结果值，右边是操作内容，蛇形相连，各操作间用逗号隔开，最后一步操作后没有逗号后接in部分！

！借鉴已有M函数写新函数时，注意修改每行结果值，句末逗号与否，in显示内容

@未来有新纪录加进来，只需要将新纪录加进Excel原始数据sheet中，上方功能栏找到数据-全部刷新，就可以按之前的Power Query逻辑得到最新更新后的数据

DAY4：【第5章: Power Pivot多维数据透视分析方法】 #CDA学习打卡 #CDA数据分析师

【Power Pivot 概述及导入数据】

透视是在指定维度下对指标值进行汇总观测的方法。在海量原始数据面前，我们是得不到任何有用信息的，只有将其分类汇总后，才能得到这些数据背后隐藏的信息。这就是透视分析的意义。

在复杂的业务逻辑面前，非单一数据源的简单数据分析需求，比如想看到不同销售区域下每名不同销售人员的销售业绩，与所有销售人员的总平均业绩的差距是多少，销售人员业绩比平均低时用红色标注，高用绿色标注。其中，区域信息来自区域表，销售业绩信息来自于销售表。

33、Power Pivot是一个加强版的数据透视工具，不仅在数据处理量上，更在透视规则及自定义规则上得到了大幅改善。不仅拥有比传统数据透视表更加强大灵活的计算分析能力，还可以导入并关联多种不同数据源的大量数据，并在内存中创建自己的多维数据模型（多个表关联在一起的数据集合）。

- 搭建多维数据分析环境，形成统一的数据环境

- 突破数据行数限制（勿大于2G！）

- 简洁的操作界面功能

- 强大的自定义功能（200+个函数）

@“数据模型”及Power Pivot中的数据

@从一个外部数据源导入一个表数据即在Power Pivot形成一个工作表

@从Power Query导入：在Power Query首次加载时，勾选“将此数据添加到数据模型”，再直接进入Power Pivot界面可见

@从Excel导入：选中目标区域，在Excel工具栏找到Power Pivot-表格-添加到数据模型，再直接进入Power Pivot界面可见

————————————————————————————————————————————————————————

【Power Pivot 搭建多维数据分析模型】

34、多维数据模型：又叫多维数据集、立方体（可以从多角度多侧面，用数据全面映射某种业务的实际状况），指的是相互间通过某种联系被关联在一起的不同类别的数据集合。涉及该业务与之相连的其他业务的数据全部整合到一起，创建全面的、统一的多维数据环境才能深入全面观察某个业务问题，搭建环境越充实，分析结果的可参考性越高。

@Power Pivot中的操作都是最终Excel数据透视表的前期准备工作

多维数据集搭建方法：

1）明确表与表之间用于匹配的关键字段

2）在关系图视图模式下，选中某个表的关键字段拖拽向另一个表的关键字段

注意：

@1 两表关联时必然有一个主表（*）和一个附表（1）。用于连接两表的两个关键字段中，至少有一个关键字段是没有重复值的。（Power Pivot的强制要求，不允许“多对多”）

@2 如果两个关键字段都没有重复值，连接时先选中的表为主表，形成“一对一”关系 。

@3 系统会将关键字段中有重复值的表作为主表，没有重复值的表为附表，形成“多对一”关系。在数据透视表中，只有当行/列标签均来自附表时，附表才能提供值字段。（行/列标签有一个来自主表就会报错！）

-> 以上连接规则将用在创建的数据透视表中：

主页-数据透视表

————————————————————————————————————————————————————————

【Power Pivot 创建层次结构】

35、在不同字段间创建层次结构方法：

1）在关系图视图模式下选中父级字段后，鼠标右键选中“创建层次结构”

2）为层次结构命名

3）将子级字段拖拽到创建好的层次结构中父级字段的下方

@创建层次结构时一定要符合业务逻辑！

————————————————————————————————————————————————————————

【Power Pivot DAX表达式】

36、DAX数据分析表达式：

Power Pivot的特有函数集，用于在Power Pivot编辑器内为数据透视表创建透视规则（复杂条件下的筛选或计算）以及增加新的字段内容。

1）DAX表达式的结果应用在数据透视表中

2）DAX表达式的结果作用于整列或者表中所有行

3）注意：

@Power Query和Power Pivot中处理的都是表结构数据，有基于表（字段的合集）和字段的数据类型，区分于Excel中的文本型、数值型等数据类型

- 表名用' '引用，字段名（列名）用[ ]引用，例如'商机记录'[赢单率]

- 要注意函数表达式中参数的数据类型，尤其是要将“表”与“数值”正确区分

- 与Excel公式相同，除了直接在编辑器的公式区域输入公式外，还可以单击公式编辑栏前的fx图标启动插入函数对话框，在对话框中选择需要的函数使用

- 表达式中的函数名不分大小写（只有Power Query的M函数区分大小写，每个单词首字母大写）

e.g.

高赢单率：[赢单率] = 0.75

低风险：[有无拖欠还款情况] = “无”

@右侧DAX编辑新字段：针对左侧预览区域内某一个字段中的每一行值来进行计算时使用

[赢单率文字替换]=switch('商机记录'[赢单率],0.15,"低",0.25,"低",0.5,"中",0.75,"高","-")

（！先将两表创建好连接关系才能使用related）

[有无拖欠]=related('商机相关企业信息'[有无拖欠还款情况])

@下方DAX编辑汇总规则：针对上方预览区域的某一个字段整个字段创建汇总规则时使用（建议字段正下方书写！）-> 只有在创建数据透视表中才能发挥作用

高赢单率低风险商机金额加总值:=calculate(sum('商机记录'[商机金额(M)]),filter('商机记录','商机记录'[赢单率文字替换]="高"),filter('商机记录','商机记录'[有无拖欠]="无"))

总商机金额:=sum('商机记录'[商机金额(M)])

@注意等号前的冒号！注意字段名可用鼠标点选整列完成

高赢单率低风险商机金额百分比:=[高赢单率低风险商机金额加总值]/[总商机金额]，在主页-格式设置-格式，设置为“百分比”

————————————————————————————————————————————————————————

【Power Pivot 使用KPI】

37、KPI即在Excel数据透视表中创建图标集的方法，图标集方便用户快速了解数据的好坏程度。常见的三色图标有两个阈值来区分，最小和最大阈值

1）在编辑器下方公式区域内用公式为需要创建KPI的字段指定汇总规则（至少一个DAX）

2）点击“创建KPI”在“关键绩效指标（KPI）”对话框中设定KPI规则

3）定义KPI目标值的方法有“度量值”及“绝对值”两种方法：

度量值 - （需要两个汇总规则）通过一种汇总规则和另外一种汇总之间的比对关系，以两种汇总值之间的百分比为判断依据的KPI规则

绝对值 - 以一个汇总规则与某个绝对数值之间的大小关系为判断依据的KPI规则

e.g.

平均销售金额:=average([销售金额])

城市维度总平均金额:=calculate(average([销售金额]),all('表1'[城市]))

@ALL函数：“所有XX的…”

@目标即理想中的状态

选中[平均销售金额]，主页-创建KPI，度量值：城市维度总平均金额，100%-120% -> 生成红绿灯图标

销量:=count('表1'[订单ID])

选中[销量]，主页-创建KPI，绝对值：10000，1000-4000 -> 生成红绿灯图标

DAY5：【第6章: Excel高级数据可视化方法精讲】 #CDA学习打卡 #CDA数据分析师

【数据可视化分析方法】

38、对比分析

1）纵向对比：时间序列分析，同一指标不同时间下的变化趋势（不同间断分散的时间段用柱状图，连续连贯密集的时间点用折线图）

2）进度分析：展现目标完成情况的分析方法，仪表盘、图标图、温度图、游标图、方块图……

3）横向对比：在同一时间下，部分与总体，部分与部分或是对象与对象之间的对比，常用饼图、环形图（横向占比对比，部分与部分）、条形图（横向值对比，部分与总体）、分段折线图（横向趋势对比，对象与对象）等

@与纵向对比的区别：纵向是不同时间条件下同一指标的对比情况，横向是同一时间条件下不同指标之间的对比情况

4）标准值、平均值及计划值之间的对比，图表类型选择灵活

@子弹图，常用于平均对比分析

@Excel簇状柱形图适用于实际值与计划值（非定值）对比

5）同环比分析：同比即本期值与同期值之间的对比；环比即本期值与上期值之间的对比，常见簇状柱形图或折线图，[同/环比增长率]增加图标集

@一定要考虑业务场景去设计才有实际意义，例如餐饮业的额星期比日期更具有周期性意义

6）预警分析：用KPI分析、预警色填充单元格等方式对关键指标进行预警，了解关键值的变化及好坏程度。

7）***透视分析：使用数据透视图表功能进行多维度、多层次、多规则的透视分析，通过对观测指标的洞察，理解及掌握指标背后的实际业务情况及风险。

39、结构分析

1）构成分析：部分与整体间构成关系的分析方法，常见漏斗图、瀑布图、滑珠图、饼图、环形图等。

2）杜邦分析：不仅适用于财务指标分析，而且可扩展延伸至各种关键指标间有明显结构关系的业务分析中使用。

40、其他分析方法

1）变化分析：反映同一指标或多种指标状态及数值变化情况的分析方法，常见组合图表、指标构成图等，是一种动态分析方法，要结合自身实际业务情况，尽可能直观地选择合适的图表对变化中的指标（观测值）进行展现

2）分组分析：用来展现关键指标在不同区间（组距）内的分布情况，常见直方图

3）增维分析：将不同类型的图表嵌套使用的方法，达到增加信息展现维度，扩展分析广度的目的，例如折线图与环形图的嵌套图表

————————————————————————————————————————————————————————

【专业自定义图表创建及和应用方法】

数据可视化：通过对数据的图形化展现来直观快速地传递复杂的数据信息，体现数据之美的手段。数据分析人员通过使用数据可视化技巧来令数据变得好看（一层是容易看容易理解的意思，另一层是美观赏心悦目的意思）。因为人类对视觉信息更强的认知水平，好看的图表能将信息准确、直观、生动地传递给阅读者，不仅能帮其迅速理解图表意思，把握关键信息，还能加深其对图表的印象，达到过目不忘的效果。在实际应用中，好看的图表能帮助决策者快速把握业务情况，并在重要的商业决策中做出正确判断。

41、自定义图表：为了增强图表可视化能力而应用的一种方法。

1）不是图表的图表制作方法：使用Excel基本制图功能之外的功能创建的数据可视化图表，即迷你图表。迷你图表多与报表中其他单元格数据结合使用，表格+ 迷你图表的同时使用的报表，不仅能直观传递可视化信息，还能完整精确地传递数据信息，达到“表图合一”的效果。

2）嵌套图表的制作方法：将主图表与其他图表或其他图片、图形嵌套在一起形成的新图表，目的一是为了改变原有图表的展现形式，二是为了增加原有图表的展示维度。

- 半圆形仪表盘（亲切感，会展现深刻认知，突出重要性使用）

- 多维折线图（扩展维度）

3）基本图表的再创新方法：以改变Excel基本图表的格式及引用数据源数据排列规则等方式来改变设计意图的展现形式，需要清晰的设计思路和灵活的展现技巧。

- 瀑布图（堆叠柱形图+下层占位部分无填充颜色）

- 分段折线图（看得更为清晰，数据分组分拆成三列错开）

————————————————————————————————————————————————————————

【交互式图表制作及应用方法】

42、交互式图表：又叫动态图表，指能够随时响应用户操作指令，改变展现结果的图表。图表从静态变为动态后，分析的深度和广度都将得到质的改变。

1）切片器与数据透视图的组合应用：用切片器直接控制透视图表（适用于2010版本以上带切片器的透视图表）

切片器创建方法：选中需要关联的数据透视表或图，插入-切片器，为切片器选择字段（字段可多选）

@按住Ctrl键点选支持切片器选项多选

@切片器可占用区域小又希望完整展示所有选项时，可以右键格式设置中找到列数，设置为2列

更改切片器关联方法：右键点击切片器，选择“报表链接”，选择需要关联的数据透视表或图

2）控件与公式的组合应用：使用控件、公式，改变静态图标数据源（限制条件较多，但支持图表类型广泛），将控件作为交互平台，将函数的返回值作为图表的数据源，通过选择控件来改变函数返回值，参照基准点发生的变化，实现图表的动态展现效果的方法。

动态图表制作步骤：选择并设置控件（开发工具-插入-表单控件-组合框），准备图表数据，创建图表，测试

动态图表制作逻辑：以控件返回值为基础参考值，用offset等查找定位函数选择图表参考数据区域，将公式写入创建名称中作为图表的参考数据使用，将图表的数据源设定为创建的名称

e.g.

=OFFSET($B$3,$A$3,1,1,7)，B3是表格首行首个单元格，A3是组合框的返回值结果

指定名称定义：因为在图表里无法直接写公式，Excel工具栏中公式-名称管理器-新建，命名并将引用位置设置为=OFFSET($B$3,$A$3,1,1,7)

再关联图表：图表中右键，选择数据，编辑，将系列值改为=柱形图示例!{新建公式名称}

动态图表制作注意事项：

- 根据实际需要选择适当的控件

- 根据业务需求决定动态图表互动方式

- 需要注意不要将函数写死，业务可能后续发生变化

- 图标制作好后，一定要先进行测试，保证图表结果的准确性

43、主要控件

1）选项按钮

2）复选框

3）列表框

4）滚动条、数值调节按钮

————————————————————————————————————————————————————————

【Power Map数据地图创建及应用】

44、数据地图是展现数据地理信息最为直观的方法。Power Map是Power BI系列插件工具中用来生成数据地图的工具，结合Bing地图，支持用户绘制可视化的地理和时态数据，并用3D方式进行分析。需要联网与Bing地图通信环境下才可使用Power Map。

Excel菜单栏，插入-Power Map-地图

静态地图：右侧区域图层窗格，先选地理和地图级别（下一步），再设置高度，勾选关注指标（默认求和，注意按实际调整），切换图形类型

动态地图（走马灯）：步骤如前，时间序列场景下添加时间为月份，有新指标要加入地图，首先添加图层！

@切换平面地图并调整右下角角度会让地图更直观

————————————————————————————————————————————————————————

【Power View交互式仪表板制作】

45、Power View：可以快速简单地制作仪表盘，功能上类似于Excel中的数据透视图表与切片器的组合工具，可以对数据进行快速筛选查看，还可以用它制作出功能丰富的动态图表。

DAY6：【第7章: Excel商业智能分析报表初级案例】及【第8章: Excel商业智能分析报表高级案例】 #CDA学习打卡 #CDA数据分析师

【财务杜邦分析仪】

46、下层指标贴近业务，从下层指标找到影响变化的原因，从原因点做出指导业务决策，改善下层指标趋势，从而改善上层指标优化。

@添加环比变化及图标集，可知本月趋势变好变坏

@连接图片形式粘贴：只支持单元格（组），图片里的数据可以随变化同步更新 + 图表想实现相同效果，需要复制图表下方的所有单元格区域（利用Shift+方向键选取）

！控件之列表只支持竖向罗列选项

@Offset设置对齐原表的行数数字辅助列，是对人为制作的原表的一次核实，保证数据分析过程的准确性

@=IF(ISERROR(K2-L2)/L2,0,(K2-L2)/L2)，嵌套一层Iserror可以保证当除法计算分母为零时，跳过计算报错，直接返回0值

@滑珠图：条形图结合散点图，条形图是每行103%的数据做出来等长“细轴”，散点图的横坐标是指标各行数值，纵坐标分别为0.5/1.5/2.5/3.5/…，就可以让各值形成滑珠落在细轴上

————————————————————————————————————————————————————————

【销售管理分析仪】

47、销售漏斗分析是关系型销售运营管理（需要长期维护销售关系）中最为重要的分析方法，科学反应商机状态以及效率的重要的销售管理模型。

销售漏斗从发现商机开始到最终与客户成交为止的销售周期，按不同销售进度分为潜在、接触、意向、明确、投入、谈判、成交的销售阶段（具体依据企业经营模式不同而不同），进而对每一个销售阶段进行精准管理。销售漏斗管理的目的是让每个阶段的销售商机都尽可能顺利地过渡到下一个销售阶段，只有将每一个销售阶段的商机管理好，才能实现尽可能多的销售机会，靠近漏斗的最底层，达成企业销售最大化的目标。本质是企业风险管理，直接关系企业的盈亏状况，是企业销售环节的生命线。

环节分析的目的是提前发现各种销售阶段潜在的商机以及隐藏的风险，在通过分析将风险明确化，以指导销售人员及时做出销售策略调整（补充新商机OR现有商机尽快推进），达到销售资源最优化和商机资源转换最大化。

销售管理分析仪可以帮助洞察环节风险，快速掌握问题所在，快速做出商业决策判断，得出详细结论，做出明确对策。

销售管理分析多维度模型：Power Query连接多部门多数据源，Power Pivot搭建统一模型

- 客户维度 - 产品维度 - 地域维度 - 销售维度 - 渠道维度 - 商机维度

！销售直接关系企业营收，营收又是企业的生命线

！涉及业务活动多，还涉及渠道或合作厂商的外部维度

！主要管理维度是商机维度，由销售人员手动录入，有主观因素和分析难度

-> 了解业务特点，理清业务流程

1）设计思路 - 故事序章：仪表盘展现总体趋势问题

- 地域维度达成率

- 产品维度达成率

- 行业/领域维度达成率

@到达高阶段的商机是销售管理者最为关注的指标，“阶段4以上”是已明确客户购买意向的商机 -> 达到1.5倍以上的销售目标值才预计可以完成本期销售目标；“阶段5以上”是销售成本投入阶段为与某客户达成成交目的（具有分配销售人员、搞好销售关系、投入资金和人员的支持） -> 达到1.2倍以上的销售目标值才预计可以完成本期销售目标

2）设计思路 - 故事展开：瀑布图展现各阶段占比情况

了解高低不同阶段商机占比情况以及商机金额总量的数值情况。如果低阶段商机过多，说明商机向高阶段转换时比较乏力 -> 间接标明销售人员需要积极把手头现有商机向前推进；另外若是商机总量总值过低，说明商机总量不足 -> 做出补充新商机进来的销售决策

3）设计思路 - 故事高潮：动态的组合柱状图形成周变化图了解单个销售阶段商机构成，目的为上一步最终决策提供数据依据

3）设计思路 - 故事尾声：了解细节

对详细信息进行总结，可供销售管理者对某一个销售人员做出更为细致的销售指导，使得大的销售策略方便更顺利地展开

MySQL数据库入门

DAY7：【第1&2章: Mysql安装、序章】&【第3章: 数据库、数据表与字段操作】#CDA学习打卡 #CDA数据分析师

【MySQL】

数据库是存储、调用、分析数据的仓库，主要分为关系型数据库和非关系型数据库。关系型数据库管理系统称为RDBMS。

数据库是表的集合，带有相关的数据；一个表是多个字段的集合；一个字段是一列数据，由字段名和记录两个部分组成，记录是实际数据信息。

子商业数据分析中使用的绝大部分数据都来自于企业数据库，企业数据库多为关系型数据库。数据库负责数据收集、数据整合、数据调用等工作。

@几乎所有数据分析工具都支持调用数据库中数据，将所需要分析的数据在数据库中整合、处理和调用是最为理想的，处理后的数据也可被其他所有数据分析类工具所使用的，推广至其他工具中去。

关系型数据库：数据库应用的主流，以行和列的形式存储数据，一系列的行和列组成表，一组表组成了数据库，当前主流的关系型数据库有Oracle、MySQL、IBM DB2、Microsoft SQL Server等。

操作关系型数据库时使用结构化查询语言，简称SQL，是一种数据库查询和程序设计语言，用于存取数据，计算、查询、更新和管理数据库系统。

————————————————————————————————————————————————————————

【MySQL数据库操作】

1、创建数据库：在数据库系统中划分一块空间，用来存储相应的数据

create database {数据库名称};

！每一个完整语句后加上“;”终止符

2、查看创建好的数据库

show create database {数据库名称};

3、查看所有数据库列表

show databases;

4、使用数据库

use {数据库名称};

5、删除数据库

drop database {数据库名称};

@注释方法：“-- ”或者“#”之后写注释，注意第一个最后有一个空格

！Excel编码方式为ANSI，MySQL默认编码方式为utf8，两者连接时需要先将编码方式统一！

————————————————————————————————————————————————————————

6、创建数据表

数据表是数据库存储及操作数据的基本单位，承载数据的容器。数据库由多个数据表构成，每张数据表存储多个字段，每个字段由不同的字段名及记录构成，每个字段有自己的数据结构及约束条件。

@表为了在最大程度上保证数据资源的准确、完整以及高效利用，在表中必须严格规定每个字段的相关属性，无论是字段的数据类型还是约束条件，都要严谨对待！创建时必须按业务要求指定设置好！

@char(#)--固定长#个字节的文本型字段

varchar(n)--可变长最长为n个字节的文本型字段

#创建并使用test数据库

create database test;

use test;

#创建员工信息表

create table emp(

depid char(3),

depname varchar(20),

peoplecount int

);

#查看表是否创建成功

show tables;

#删除数据表

drop table emp;

————————————————————————————————————————————————————————

7、数据类型

位：bit，是电子计算机中最小的数据单位，每一位的状态只能是0或1

字节：Byte，由8个二进制位构成1个字节，它是存储空间的基本计量单位

数据类型：不同的数据类型具有不同的字节长度；字节长度越长，能取值的数值区间也就越大。

所谓“一把钥匙开一把锁”，字段的数据类型限定了只有与其相匹配的数据信息，才能录入到字段中来，以保证字段的完整性和准确性。

1）整数型：INT()、TINYINT、SMALLINT、MEDIUMINT、BIGINT，括号里设置最大显示宽度，并不限制实际取值范围和占用空间

e.g. INT(11)，包括正负号和十位数

@均存在有（正负）符号的和无符号的。如果在数据类型后加上UNSIGNED属性可以禁止负数

2）小数型：FLOAT(M,D)（4字节，默认为(10,2)）、DOUBLE(M,D)（8字节，默认为(16,4)）、DECIMAL(M,D)——M是显示宽度，D是小数位数

@均只能是有符号的

3）日期时间型：DATE、DATETIME（YYYY-MM-DD HH:MM:SS格式）、TIME、TIMESTAMP（时间戳，例如20241007153600）、YEAR（2位或4位，2位是1970~2069，4位是1901~2155，默认四位）

4）字符串型：CHAR(M)、VARCHAR(M)、BLOB或TEXT、TINYBLOB或TINYTEXT、MEDIUMBLOB或MEDIUMTEXT、LONGBLOB或LONGTEXT、ENUM

@CHAR是固定长度，VARCHAR是可变长度（L+1，需要占用一位存储长度信息），长度为1-255，注意指定的字符串长度M一定要大于实际输入数据的最大长度，这样才能完成保存输入数据信息 -> 数据长度不一时，优先选用VARCHAR；长度出入不大时，CHAR比VARCHAR运行效果更佳

————————————————————————————————————————————————————————

8、约束条件

约束条件是在表上强制执行的数据检验规则，可用来保证创建的表的数据完整和正确性，类似不同大小筛眼的筛子。

1）PRIMARY KEY 主键约束：主键又称主码，是数据表中一列或多列的组合。主键约束要求主键列的数据必须是唯一的并且不允许为空，非空和唯一，保证表中每行记录都不重复。使用主键，能够唯一的标识表中的一条记录，还可以加快数据库查询的速度。

字段名 数据类型 PRIMARY KEY

- 单字段主键

e.g. depid char(3) primary key,

- 多字段联合主键

e.g. primary key(depname, depid)

2）NOT NULL 非空约束：要求字段的值不能为空，非空。

字段名 数据类型 NOT NULL

e.g. depname varchar(20) not null,

3）UNIQUE 唯一约束：要求该列的值必须是唯一的，唯一；允许但只能出现一个空值；一个表中可以有多个字段声明为唯一的，确保数据表的一列或几列不出现重复值。

字段名 数据类型 UNIQUE

e.g. peoplecount int unique

4）AUTO_INCREMENT 自增字段：一个表只能有一个自增字段，必须为主键的一部分，默认从1开始自增，步长为1

字段名 数据类型 AUTO_INCREMENT

e.g. #创建整数型自增主键

id INT PRIMARY KEY AUTO_INCREMENT

5）DEFAULT 默认值：当插入记录时，如果没有明确为字段赋值或空值时，系统会自动按默认值填充赋值

字段名 数据类型 DEFAULT 默认值

e.g. depname varcahr(20) default'-',

@标准SQL用''单引号引用字符串，MySQL拓展至''或""单双引号皆可

@desc {表名}——可见按字母排序后各字段的数据类型及约束条件

——————————————————————————————————————————————————————

9、向数据表中填充数据

1）（手动录入）insert into：

insert into 表名(字段1, 字段2, ...)values(),(),(), ...

！字段排列顺序要与建表时建立字段顺序一致

e.g. #插入数据

insert into fruits(f_id,s_id,f_name,f_price)

values('a1',101,'apple',5.2),

('b1',101,'blackberry',10.2),

……

('b5',107,'xxxx',3.6);

2）（外部数据源文件批量导入）

e.g. #为Monthly_Indicator表导入外部txt文件，按绝对路径（左斜斜杠分隔），指定Tab制表符为分隔符，忽略数据文件中第一行（字段名）

load data local infile '文件路径.txt'

into table Monthly_Indicator

fields terminated by '\t'

ignore 1 lines;

3）从企业数据库直接导入：先联系数据库管理员授权权限

-> 检查表数据：对导入表中的数据一般从导入内容、导入数据总行数以及表结构三方面进行检查

e.g.

#检查导入内容Monthly_Indicator

Select * from Monthly_Indicator;

#检查导入数据总行数Monthly_Indicator

Select count(*) from Monthly_Indicator;

#检查表结构

Desc Monthly_Indicator;

@主键选择需要保证聚焦明确分析对象的描述条件，需要考虑是否多个主键以限定

————————————————————————————————————————————————————————

10、修改数据表

修改数据库中已经存在的数据表的结构，包括修改表名、修改字段数据类型或字段名、增加或删除字段、修改字段的排列位置等。使用alter table语句进行修改。

e.g.

#将数据表emp改名为empdep

alter table emp rename empdep;

#将数据表empdep中depname字段的数据类型由varchar(20)修改成varchar(30)

alter table empdep modify depname varcahr(30);

#将数据表empdep中depname字段的字段名改为dep

alter table empdep change depname dep varchar(30);

#将数据表empdep中dep字段的字段名改回为depname，并将该字段数据类型改回为varchar(20);

alter table empdep change dep depname varchar(20);

#为数据表empdep添加新字段maname，新字段数据类型为varchar(10)，约束条件为非空

alter table empdep add maname varchar(10) not null;

@change相对modify，可以同时修改数据名（重新命名）及数据类型，只需要修改数据类型就用modify

#将数据表empdep中maname字段的排列顺序改为第一位

alter table empdep modify maname varchar(10) first;

#将数据表empdep中maname字段的排列顺序改到depid字段之后

alter table empdep modify maname varchar(10) after depid;

#删除maname字段

alter table empdep drop maname;

#CDA学习打卡 #CDA数据分析师 #CDA学习打卡 #CDA数据分析师 #CDA学习打卡 #CDA数据分析师