导图社区 EXCEL数据处理与分析(五)
这是我个人通过EXCEL模拟训练做的一个笔记,适合学过的人回忆EXCEL技巧,不适合新手学习。要想真正掌握EXCEL技巧,光靠记忆是不可行的,还是得亲自动手。
编辑于2020-12-15 17:09:29
一、数据透视表
功能
对大量数据,按照指定类别汇总
根据汇总数据制作分析报告
思考数据,并对汇总数据合理布局
分区
页字段
对整个数据透视表筛选
行字段
数据区域行标题
列字段
数据区域列标题
值字段
指定区域汇总值
字段列表
整个数据源区域的列标题
布局
数据结构
根据需求调整分区
选中透视表任一单元格→
分析→显示组→字段列表
显示方式
调整列标题行标签字样
设计→报表布局→以表格形式显示
排序
选中汇总区域任一单元格→
右键→排序→降序/升序
格式化
选中透视表任一单元格→设计→样式
调整数据格式
汇总区域加粗
开始→数字→货币
将透视表字体调整为微软雅黑
数据透视图1
选中透视表任一单元格→分析→数据透视图
筛选
下拉列表→值筛选→前10项
切片器
分析→插入切片器
默认选中所有项目
切片器样式应与图一致
日程表
原始数据中有日期
分析→插入日程表
可在年、季、月切换
日程表和切片器可在选项中设置报表连接
数据透视图中,数据系列与图例均有下拉菜单
数据透视图2
添加计算字段
分析→字段、项目和集→计算字段
→名称→以插入字段方式输入公式
添加计算项
预求值项目不属于数据源字段情况下
选中透视表中任意项→分析→
字段、项目和集→计算项→插入公式
不需行合计时,设计→总计→仅对列启用
二、数据分析工具
模拟分析
安装分析工具库
文件→选项→加载项→转到→
→勾选分析工具库+分析工具库-VBA
模拟运算表
单变量
须先计算出运算表区域第一个目标值
选中运算表区域→数据→模拟分析→
→模拟运算表→输入引用列的单元格
然后使用单变量模拟运算表计算其他值
用于分析单独一个参数变化对结果的影响
双变量
在变量相交处公式计算第一个目标值
选中运算表区域→数据→模拟分析→
模拟运算表→引用行的单元格(行变量)→
→输入引用列的单元格(列变量)
用于分析两个参数变化对结果的影响
单变量求解
数据→预测→模拟分析→单变量求解→
→目标单元格→目标值→可变单元格
假设已知一个公式目标值,求解其中的变量
分析工具库
操作方式
选中数据单元格→数据→数据分析→
方差分析/相关系数/协方差/移动平均→
输入区域→分组方式→标志第一行→输出区域
判断过去
方差分析
方差值越小,越稳定
P值<0.05,需要深入分析
P值>0.05,不存在显著差异
相关系数
接近于1,属于高度正相关
接近于-1,属于高度负相关
接近于0,表示相关性不大
协方差
与相关系数分析方法相似
预测未来
指数平滑
先确定阻尼系数,后根据公式预测
移动平均
未来值=前N个时间段移动平均值的算术平均数
根据标准误差,选择最佳间隔数N
下集预告:数据分析的具体应用
EXCEL数据处理与分析(五) 数据透视表