Excel BI 数据整合及建模分析
前言：
l 面对海量的数据，如何提炼浓缩表单信息，将重要信息明显展示给客户和领导？
l 如何向老板和客户展示一份完美的交互式报告？
l 如何从数据中发现数据变化的特点和运营过程中存在的问题？
l 如何让不会写VBA代码的普通员工或老板也能制作出具有交互式功能的报表
l Excel BI 让专业的交互式可视化报表走近普通用户
课程要求
Office365 或 Office2016以上版本
课程目标
使用Power Query 进行数据整合清洗、Power Pivot进行数据建模、Pivot Chart制作交互式可视化报表，高效的数据自动化能大大提升数据处理分析的效率，进而提升企业经营效益。
ü 目标人群：财务、人事、市场、销售中运用数据分析的人员。
ü 提升数据分析能力，用数据说话，用图表说话。
ü 进行表格的规范化、科学化管理，实现精细化高效管理数据。
ü 及时从数据中发现问题，做好预测和预防。
ü 带着问题来，带着模版走
课程大纲 共计（讲12小时，学员操作6小时，共计18小时）
第一章：规范化基础数据清单表是数据分析的基础（2.1小时）
主题1：清单表的数据结构 （24分钟）
A 内容：
l Excel BI 概述
l 规范化是数据清单表的要求
l 行为记录，列为字段
l 每个字段要有一个字段名
l 清单表中尽量要排除的内容
A 作业：
l 示例1-示例3
主题2：清单表的三同原则（56分钟）
A 内容：
l 同物同名称
l 去除多余空格及非打印字符
l 同表同结构
l 同列同类型
B 作业：
l 示例4-示例8
主题3：区域转换为清单表（47分钟）
A 内容：
l 区域与清单表的区域
l 区域转换为清单表前提、方法
l 清单表转换为区域
l 清单表的自动化功能
B 作业：
l 示例9
第二章：Power Query数据获取整合分析（2.5小时）
主题4：批量获取合并同一文件夹下规则的同结构单工作表文件（35分钟）
A 内容：
l Power Query 数据获取-文件夹中所有EXCEL文件
l 数据获取导航
l Power Query导入合并的每个文件中工作表名称要一致
l Power Query提取某列数据中想要的内容
l 数据类型转换
l Power Query 将整合清洗整理后的数据载入到Excel
l 生成数据透视表添加计算项
B 作业：
l 示例10
主题5：批量获取合并同一文件夹下规则的同结构多工作表文件（30分钟）
A 内容：
l Power Query 数据获取一个文件夹中具有个工作表的所有EXCEL文件
l 根据整合清洗后的数据制作数据透视表
l 制作透视表切片器
l 根据整合清洗后的数据制作透视图
l 制作透视图切片器
B 作业：
l 示例11
主题6：批量获取数据合并同一文件夹下不规则的同结构表（39分钟）
A 内容：
l M语言的Excel.Workbook函数
l 插入列、删除列、扩展列
l 设置标题行，List.First、Table.ColumnNames函数
l 根据整合清洗后的数据制作透视图
l 制作透视表切片器产生交互式报表
B 作业：
l 示例12
主题7：合并另一工作簿中所有规则数据表（23分钟）
A 内容：
l Power Query获取另一个EXCEL文件数据
l 数据源表格：导入时包含标题
l 设置标题行，List.First、Table.ColumnNames函数
l 拆分列
l 插入自定义列创建日期
l 输出透视表按日期排序
B 作业：
l 示例13
主题8：合并另一工作簿中所有不规则交叉数据表（27分钟）
A 内容：
l 删除顶端非标题行
l 提升标题行
l 删除错误行
l 去除空数据
l 逆透视数据列
l 添加日期列
l 输出透视图按日期排序
B 作业：
l 示例14
第三章：Power Query分组聚合操作（2小时）
主题9：分组聚合与合并查询（38分钟）
A 内容：
l 单字段分组聚合操作
l 多字段分组聚合操作
l 根据日期拆分年月
l 多字段关联的合并查询
l 添加日期列
l 返回EXCEL制作透视图
B 作业：
l 示例15
主题10：不重复行记数&聚合关联记录（25分钟）
A 内容：
l 非重复行记数
l 聚合关联记录
l 深化引用表中字段
l 提取列表（List）
l 多依据排序查询
B 作业：
l 示例16
主题11：结合M语言的分组聚合-汇总编号范围（34分钟）
A. 内容：
l M语言的语法规范
l 结合List.Min & List.Max 扩展分组
l 分组依据的函数Table.Group
B 作业：
l 示例17
主题12：结合M语言的分组聚合-按会计科目逐级汇总（30分钟）
A. 内容：
l 根据会计科目编码自动汇总借方金额
l Table.AddColumn
l List.Sum
l Text.StartsWith
l Table.SelectRows
l 表示当前记录的匿名函数（x)=>
B 作业：
l 示例18
第四章：Power Query透视列&逆透视列的特殊处理（36分钟）
主题13：透视列操作（15分钟）
A. 内容：
l 透视列语法
l 替换值
l 透视操作
B 作业：
l 示例19
主题14：透视列&逆透视列混合操作（21分钟）
A. 内容：
l Unpivot Other Columns
l Split Column
l Add Conditional Column
l 筛选行
l Pivot Columns
l 删除列&重命名列&更改数据类型
l 添加排序列
l 多列排序
B 作业：
l 示例20
第五章：Power Pivot数据建模及可视化（5.2小时）
主题15：多工作表建模创建透视表（25分钟）
A 内容：
l 加载Power Pivot
l 区域转换为表格
l 添加数据到数据模型
l 创建关系
l 输出透视表
B 作业：
l 示例21
主题16：多文件建模（39分钟）
A 内容：
l 打开Power Pivot管理器
l 导入多个EXCEL文件建模
l Power Query导入后添加到数据模型
l 数据建模创建关系
l 输出透视表理解建模关系
B 作业：
l 示例22
主题17：Dax语言与理解上下文（58分钟）
A 内容：
l Dax 语言的四个基本概念
l CALCULATE函数:多条件计算
l 使用Dax语言添加度量值
l Filter函数：筛选表
l 理解Calculate扩展上下文
l 输出透视表
B 作业：
l 示例22
主题18：Power Pivot创建KPI（44分钟）
A 内容：
l Power Query导入一个文件夹中不同结构的多个文件
l 根据Content创建新查询
l 根据订单明细汇总：每年每月每类产品销售额
l 加载到数据模型并建模
l Lookupvalue根据多条件匹配数据
l 输出透视表创建KPI
B 作业：
l 示例23
主题19：ALL & Calculate扩展筛选上下文（73分钟）
A 内容：
l ALL函数功能及语法
l ALL & Calculate
l 计算同比、环比：DIVIDE 、DISTINCTCOUNT 、DATEADD
l 计算提成额：HASONEVALUE、VALUES、SUMX
B 作业：
l 示例24
主题20：制作交互式图表仪表板（77分钟）
A 内容：
l 线柱双轴图：观察排名及平均线及同比或环比走
l 柏拉图：观察数据累计占比
l 瀑布图：数据增减变化对总计数据的影响
l 饼形图：每个扇区点整体的贡献率
l 堆积面积图：显示每个数值所占大小随时间或类别变化的趋势线
l 设置切片器让图表仪表板具有交互功能
B 作业：
l 示例24
联系我们
制订详细企业内训方案或邀约老师：联系人：隋老师
电话：13121392666
扫描左侧二维码添加老师微信，详细咨询。
