excel 数据分析核心思路是先整理清洗原始数据,再使用排序筛选分类汇总数据透视表还有数据分析工具库,完成统计汇总对比计算,新手做数据分析最容易踩坑的地方就是原始数据不规范,存在空行合并单元格文本型数字,导致后续分析出错,excel 数据分析底层就是对结构化表格做统计运算,理解这个逻辑之后,就可以从数据清洗入门,逐步掌握分类汇总数据透视表,还有回归描述统计这类高级分析功能。
一 第一步 数据清洗预处理
正式分析之前必须整理原始数据
第一 删除多余空行空列,取消合并单元格
第二 检查文本型数字,转换成数值格式,保证可以正常求和计算
第三 统一日期格式,清理单元格多余空格
第四 删除重复记录,使用删除重复值功能
第五 补齐缺失数据,标记异常值
这一步是数据分析的基础,数据不干净,后面统计结果就会失真
二 排序筛选 初步查看数据
排序操作
选中带表头的数据区域,点击数据选项卡,选择升序降序,可以按单字段或者多字段排序,快速观察极值
筛选操作
点击数据选项卡筛选,表头出现下拉箭头,按条件筛选目标记录,只查看指定类别数据,适合抽样查看数据分布
三 分类汇总 按类别汇总统计
适合按某一个分类字段,比如部门地区,自动分组求和计数平均值
操作步骤
第一步 先对分类字段做排序,同类别记录排在一起
第二步 选中完整数据表,点击数据选项卡,选择分类汇总
第三步 设置分类字段,汇总方式,可选求和计数平均值最大值最小值
第四步 勾选汇总项,选择需要统计的列
第五步 点击确定,表格自动分组,左侧出现分级显示加号减号,可以展开或者折叠分组
注意,如果没有提前排序,分类汇总会把同类拆成多组,结果错误
四 数据透视表 核心分析工具
数据透视表是 excel 数据分析最常用工具,可以拖拽字段,快速做多维度交叉统计,不用写复杂公式
操作步骤
第一步 选中原始数据表,包含表头
第二步 点击插入选项卡,选择数据透视表
第三步 选择放置位置,可以新工作表或者现有工作表,点击确定
第四步 在右侧字段面板,把分类字段拖入行区域,数值字段拖入值区域
第五步 值区域默认求和,点击可以修改汇总方式,改成计数平均值最大值
第六步 还可以把字段拖入列区域筛选区域,实现交叉表分析
特点,数据源更新之后,右键点击数据透视表,选择刷新,就可以更新统计结果
五 数据分析工具库 高级统计分析
如果需要描述统计回归分析方差分析直方图,需要启用数据分析工具库
启用步骤
第一步 文件选项,加载项
第二步 底部管理选择 excel 加载项,点击转到
第三步 勾选分析工具库,点击确定
第四步 顶部数据选项卡,就会出现数据分析按钮
常用功能
描述统计,一次性输出平均值标准差最大值最小值中位数
直方图,制作频数分布
回归分析,做线性回归预测
操作步骤,点击数据分析,选择对应分析项目,设置输入区域输出区域,确定生成分析报告
六 常用分析方法对比表格
| 工具 | 特点 | 适用场景 |
|---|---|---|
| 排序筛选 | 操作简单 | 初步查看极值筛选记录 |
| 分类汇总 | 按单类别分组 | 单维度分组汇总 |
| 数据透视表 | 多维度交叉统计 | 多类别交叉分析 |
| 分析工具库 | 专业统计计算 | 描述统计回归方差分析 |
七 常见异常处理
第一 数据透视表数值变成计数,原因是该列存在文本内容,把文本改成数值,刷新透视表
第二 分类汇总结果错乱,分类字段没有提前排序,重新排序之后再做分类汇总
第三 找不到数据分析按钮,是没有加载分析工具库,按照加载项步骤启用
第四 数据源新增数据之后透视表不更新,需要手动点击刷新,或者把数据源转为超级表自动扩展区域
第五 合并单元格表格无法生成透视表,取消合并单元格再操作
总结
excel 数据分析怎么弄,新手优先掌握数据清洗,排序筛选,分类汇总,重点熟练数据透视表,需要专业统计再启用数据分析工具库,做分析前一定要规范原始数据,每次数据源变动记得刷新结果,日常办公绝大多数统计分析,数据透视表就可以完成,不用复杂函数。
【常见问题】
问题 1 excel 数据分析怎么弄,数据透视表新增数据之后为什么不更新
回答 1 excel 数据分析怎么弄,透视表不会自动识别新增行,需要右键点击透视表选择刷新,或者把原始数据转为超级表,区域自动扩展,是 excel 数据分析怎么弄高频问题。
问题 2 excel 数据分析怎么弄,找不到数据分析选项怎么办
回答 1 excel 数据分析怎么弄,默认没有开启分析工具库,进入文件选项加载项,勾选分析工具库加载,数据选项卡就会出现数据分析按钮。
问题 3 excel 数据分析怎么弄,分类汇总为什么同类分成很多组
回答 1 excel 数据分析怎么弄,分类汇总必须先对分类字段排序,同类记录集中之后再做分类汇总,没有排序就会分组错乱。
问题 4 excel 数据分析怎么弄,数据透视表为什么默认变成计数
回答 1 excel 数据分析怎么弄,一般是数值列里面混有文本,文本类型透视表默认计数,把单元格转为数值格式,刷新透视表就可以改成求和。
【免责申明】
本文教程基于主流 excel 版本编写,分析工具库加载方式不同版本略有差异,统计分析结果仅供参考,本文仅作为办公学习参考,不承担因数据清洗错误造成统计结论偏差相关责任。
