EXCEL数据分析与报告制作-2天

EXCEL数据分析与报告制作

课程背景

在企业的日常经营管理中，既需要总结历史规律，更需要预测未来走势；既需要规避经营暗礁，更需要为未来指明方向。作为企业管理者，数据分析能力已经成为必须的能力！企业必须推进数据化管理，利用数据提升管理的精准性、科学性，“用数据说话”，“无数据，不管理”。大数据时代，我们不缺乏海量的数据，也不缺少高效的工具，我们缺少思路和方法，缺少懂数据、会分析的数据分析人士！必须将数据分析的思路、方法，以及将其落实到工作实际的实战应用中，这是企业当前需面对和解决的主要问题。

本课程借助微软办公软件Excel的相关功能，结合数据分析的思维和方法，探讨如何将数据分析与企业管理经营科学结合在一起，让数据分析更高效的为提升企业管理水平服务，用数据治理企业、改变企业，提升企业。

课程收益

通过本课程，你将：

找寻：数据与商业的结合点

看透：数据分析的意义

挖掘：数据隐藏的商机

习得：有效改进管理的方法

掌握：数据分析的工具和报告的制作

培训对象

课程适用于有一定Excel使用基础，需要进一步提升大数据分析水平和使用效率的各级管理者和数据分析人士。

 授课讲师

张晓如 老师（微软OFFICE大师级认证）

 授课时间

2天，6小时/天

课程大纲

小测试：你的数据分析能力有几分？

粮草先行——数据准备

理解数据

数据的价值是什么？

企业中的静态数据和动态数据有哪些？

管理数据

如何管理企业拥有的海量数据？

如何管理三张表?

数据来源

如何快速录入数据

如何让录入的数据自带单位且还能参与运算（如工龄5年/重量10吨）

如何在列中快速录入带有重复字段的数据（如XX部/GH-XXX/0512-……）

如何规范别人录入数据

如何使用数据验证功能限定单元格的录入类型/创建下拉菜单/禁止重复输入

如何从网上抓取数据并实时动态更新

如何实现Power Query轻松合并多个工作表/多个工作薄/文件夹

整装待发——数据整理

互动：什么是脏数据？

数据清洗

四种方法找出重复值

如何快速删除重复值

四种方法填充缺失值

数据合并

字符型字段如何合并——&/Concat函数

日期型字段如何合并——Date函数

字段如何快速匹配

VLOOKUP函数配合COLUMN函数和MATCH函数实现第三个参数自动更新

利用VLOOKUP函数的模糊查询快速实现数据的配对

如何实现逆向查询

利用INDEX和MATCH函数实现数据的反向查询

数据提取

字段拆分的二种方法

随机抽样的二种方法

数据计算

求和/计数/极值

根据条件求和/计数/求平均

使用智能表格快速计算

使用IF函数和数据透视功能对数据进行分组

DATEDIF、TODAY、EDATE自动计算年龄、司龄、退休时间

数据转换

二种方法对数据类型如何转换（数值、文本、日期型互转）

二种方法对行列进行转换

二种方法实现二维表格转为一维表格

数据探索——数据分析

认识数据分析

什么是数据分析？

发现数据规律——找到可行方案——指导管理决策。

数据分析的方法有哪些？

基本描述统计分析

对比分析

关联分析

回归分析

时间序列分析

聚类分析等

常用的数据分析软件有哪些？

第一阵营——EXCEL/WPS

第二阵营——POWER BI/TABLEAU/SPSS/FINEBI/MINITAB

第三阵营——PYTHON/R

EXCEL中的数据分析工具有哪些？

基本分析工具

做个简单的统计，就用分类汇总

排序也可以玩出新花样

单一排序与多条件排序

部门、职务照样可以实现自动排序（如总经办/财务部/人事部……）

筛选精准锁定数据范围

让效率提升的自动筛选功能命令在哪里？

多条件筛选并不难，快速创建条件区域

条件格式——让EXCEL表格根据输入的数据凸显不同的颜色（填充）

智能表格的神奇之处

颜值大变身

切片器功能

自动更新数据源

进阶分析工具

动手制作一份数据透视表

数据透视表的字段和区域

退拽字段和值汇总依据对数据进行描述分析和对比分析

对数据进行排序、筛选

更改字段的格式，美化数据透视表的样式

将数据分类组合：日期、数字范围、指定条件

改变数据透视表的布局（压缩/大纲/表格/）

如何让数据透视表和源数据自动更新（手动刷新/自动刷新）

惊艳的数据透视表计算功能

强大的值显示方式（总计百分比/父行汇总百分比/列总结百分比/差异百分比）

计算字段——给数据透视表增加我们需要的字段

数据透视表的高级功能有哪些？

自动导出明细数据报表

数据透视表帮你瞬间拆成N个工作表

如何运用数据透视表生成数据透视图？

使用切片器/日程表进行数据筛选

智能表格与数据透视表联合实现数据自动刷新

高级分析工具

描述数据现状

集中趋势

平均数/中位数/众数

离散程度

方差/标准差/变异系数

练习：

如何用描述分析查看公司的工资整体状况？

使用百分比排位分析员工的工资排名（如75分位的员工）？

相关分析——挖掘潜藏商机

商业中的相关关系及其重要性

相关分析的基本步骤

利用散点图判断相关类别

相关系数如何计算

用图形法和函数法求相关系数

练习：使用相关分析对员工满意度进行分析，建立四象限图并找出待改善之处。

回归分析——预测未来

回归方程应如何确定

决定系数的意义

企业中的一元回归方程分析

企业中的二元回归方程分析

练习：用回归对各银行用工总数和净利润之间关系进行分析并进行预测。

用时间序列预判走势

移动平均法

指数平滑法

练习：使用移动平均法/指数平滑法预测公司销售额的走势，并绘制图表

结果呈现——图表制作与数据分析报告制作

数据可视化——图表的制作

选择正确的图表类型

商务图表制作的四原则（布局/配色/文字/结论）

掌握基本图表制作技巧

柱型图/折线图/饼图/条形图/面积图/散点图/气泡图

组合图形的画法（线柱组合双轴图、饼图条形图组合图）

熟练掌握图表美化技巧（突出最值、平滑曲线、渐变填充）

高阶图表制作（人行柱形图、温度计图表、背靠背图、控制线图、直方图、箱线图、帕累托图）

动态图表的制作

筛选呈现动态效果

切片器/日程表呈现动态效果

表单控件呈现动态效果

仪表盘报告制作

流行的仪表盘报表的制作逻辑（数据层、筛选层、导航层）

制作公司人力资源结构表（使用COUNTIFS函数，练习条形图、环形图并美化）

自己动手设计制作一份仪表盘（对多个数据透视图进行组合生成仪表盘式的工作报告）

结论报告化——数据分析报告的制作

数据分析报告类型

描述类分析报告

因果类分析报告

预测类报告

数据分析报告的注意事项

结构合理——内容逻辑清晰、结构严谨简洁

格式规范——标准统一前后一致（模板/字体/配色）

准确严谨——数据真实可靠内容全面可行

创新前瞻——新思维/新方法/新观点

数据分析报告的逻辑结构

分析报告的整体结构

总分、总分总结构

自上而下梳理结构

自下而上数据结构

分析报告的内部结构

时间结构

流程结构

地点结构

现状-危机-解决方案结构

技能-功能-利润结构

数据分析网站和优秀数据分析报告赏析

使用WORD撰写数据分析报告

封面——主题明确、引起兴趣

标题拟定原则

标题拟定方法

前言或内容提要——阐述背景、总括概述

一般分为三大部分（背景、目标和思路）

举例说明如何撰写前言

目录——逻辑清晰、展示章节

目录如何结论化

如何自动生成目录

正文——详细分析、提出结论

依据分析思路详细阐述

利用分析方法展开分析

运用图表呈现分析结果

借助模型梳理正文逻辑

添加章节序号规范层次

加入分析者的个人见解

明确分析的结论和建议

附录——补充解释、提供依据

附录的构成要素有哪些？（名词解释、原始数据来源、分析方法等）

举例说明附录的构成

练习：

标题拟定/如何自动生成目录/章节序号如何添加/如何在WORD中插入EXCEL数据和图表

使用PPT编写数据分析报告

确定分析报告的受众——谁要看分析报告

他们是谁？高层、中层

他们关注什么？关注的内容是不一样的

他们会问什么问题？提前准备问题

确定汇报的场合——决定PPT制作的尺寸和色调

场地大小、屏幕类型、屏幕尺寸、电脑版本

设计制作PPT

封面页的制作——图+字结合

前言或内容提要页的制作

目录页的制作——图文型目录如何制作

过渡页的制作

总结页的制作

内容页的制作

演示PPT

幻灯片播放的小技巧

如何使用演讲者视图模式

如何开场尤为关键——开场原则和导入方法

如何收尾不得忽视——收尾原则和收尾方法

练习：

PPT尺寸、颜色的快速调整

图+字封面页如何制作

目录页、过渡页、总结页如何制作

建议使用微软Office2016、2019或Office365版本，低版本某些功能没有。