Excel数据分析处理(二).pptx

EXCEL 数据处理分析（二） 东南大学计算机科学与工程学院 数据库技术研究室 2013年4月20日 C 数据处理与分析 1、数据处理与分析的概念 2、数据处理 3、数据常用分析方法 数据处理与分析 何谓数据分析 数据分析是指用适当的统计分析方法对收集来的大量数据进行分析 ，将它们加以汇总和理解并消化，以求最大化地开发数据的功能， 发挥数据的作用。 数据分析是为了提取有用信息和形成结论而对数据加以详细研究和 概括总结的过程。 数据分析的分类： 描述性数据分析：初级的数据分析 探索性数据分析：高级的数据分析，如相关分析、回归分析等 验证性数据分析：高级的数据分析，如假设检验等 数据分析的作用 现状分析 现阶段的整体情况；企业各项业务的构成 原因分析 现状为什么会发生 预测分析 数据分析六步曲 明确分析目的和思路 收集数据 数据处理 数据分析 数据展现（图表展现） 报告撰写 几个常用指标和术语 平均数 绝对数与相对数 百分比与百分点 百分点是指不同时期以百分数的形式表示的相对指标的变动幅度。 频数与频率 比例与比率 倍数与番数 同比与环比 对数据的理解 字段与记录 字段是事物或现象的某种特征。 记录是事物或现象某种特征的具体表现。 数据类型 字符型数据 数值型数据 数据表 二维表 二维表 利用数据透视表实现二维表转化为一维表 Step1：在自定义功能去添加“数据透视表和数据透视图向导”。 Step2：单击“数据透视表和数据透视图向导”按钮。 Step3：点选多重合并计算数据区域，单击下一步。 Step4：选中“创建单页字段”，并点击下一步。 Step5：选定区域并添加，单击下一步。 Step6：选择结果输出，点击完成。 Step7：双击数据透视表的最后一个单元格即可获得一维数据表。 数据处理 数据清洗 清除不必要的重复数据 填充缺失数据 检测逻辑错误数据 数据加工 排序：单关键字排序、多关键字排序、自定义排序 分类汇总：一级分类汇总、多级分类汇总 筛选：自动筛选、自定义筛选、高级筛选 数据计算：利用函数和公式以及求和工具进行运算 数据抽样 抽样：利用函数以及抽样分析工具进行抽样 数据清洗 重复数据的处理 利用COUNTIF函数 判断当前值是否与历史值相同并计数 利用高级筛选 点选不重复的记录 条件格式法 “开始”“条件格式”“突出显示单元格规则”“重复值 ” 删除重复数据 “数据”“数据工具”组中的“删除重复项” 通过对字段的降序排序 通过自定义筛选字段 缺失数据处理 用样本统计量代替，例如平均值 用统计模型计算出来的值代替 直接删除 保留缺失值的记录，但不做具体的分析 检查数据逻辑错误 IF函数法 数据分析方法 现状分析 对比分析法 平均分析法 综合评价分析法 原因分析 分组分析法 结构分析法 交叉分析法 杜邦分析法 漏斗图分析法 矩阵关联分析法 预测分析 相关性分析 回归分析 移动平均、指数平滑 对比分析法 所谓对比分析法，是指将两个或两个以上的数据进行比 较，分析它们的差异，从而揭示这些数据所代表的事物 发展变化情况和规律性。 可分为静态比较和动态比较 静态比较是在同一时间条件下对不同总体指标的比较，如不同 部门、地区、国家的比较，也叫横向比较。 动态比较是在同一总体条件下对不同时期指标数值的比较，也 叫纵向比较。 注意： 比较的指标要一致 比较的对象要有可比性 平均分析法 平均分析法是指运用计算平均数的方法来反应总体在一 定时间、地点条件下某一数量特征的一般水平。 平均的指标有算术平均数、几何平均数、调和平均数、 众数和中位数等。 综合评价分析法 鉴于简单分析的局限性，影响分析结果的因素是多个时 ，需要对每个因素确立一个权重，最后计算一个合计值 进行综合评价。 分组分析法 根据数据分析对象的特征，按照一定的标志，把数据分 析对象划分为不同的部分和类型来进行研究，以揭示其 内在的联系和规律性。 确定组数组距分组整理汇总分析差异 结构分析法 结构分析法是指被分析总体内的各部分与总体之间进行 对比的分析方法，即总体内各部分占总体的比例，属于 相对指标。 交叉分析法 通常用于分析两个变量之间的关系，即同时将两个有一 定联系的变量及其值交叉排列在一张表格内，使各变量 值成为不同变量的交叉结点，形成交叉表，从而分析交 叉表中变量之间的关系。 漏斗图分析法 漏斗图是一个适合业务流程比较规范、周期比较长、各 流程环节涉及复杂业务过程比较多的管理分析工具。这 是一种最直观的表现形式，很快能发现业务流程中存在 的问题环节。 矩阵关联分析法 矩阵分析法是指根据事物的两个重要属性作为分析的依 据，进行分类关联分析，找出解决问题的一种分析方 法。 以属性A为横轴，以属性B为纵轴，在两坐标轴上分别按 照某一标准进行刻度划分，构成四个象限，将要分析的 每个事物对应投射到这四个象限内，进行交叉分类分 析。 低 高 重要性 满意度高 EXCEL2010图表的使用 1、介绍几种常用图表 2、几种稍复杂图表的绘制与图表美化 几种常用图表 图表的概念 所谓图表可分为图和表，图即指用图形来表示数据，表即用表 格来表示数据。 使用图表能够使表达更加形象化，突出重点，体现出专业化 基本图表： 表格：一维表格、二维表格等 柱形图：簇状柱形图、堆积柱形图、百分比堆积柱形图 折线图：普通折线图、堆积折线图、百分堆积折线图以及带数据标 记的折线图 饼图：饼图、分离型饼图、复合饼图、复合条饼图 条形图：簇状条形图、堆积条形图、百分比堆积条形图 面积图：普通面积图、堆积面积图、百分比堆积面积图 散点图：仅带数据标记的散点图、带平滑线和数据标记的散点图 气泡图：二维、三维气泡图 雷达图：普通雷达图、带数据标记的雷达图、填充雷达图 1、表格 当需要呈现的数据在3个系列以上时，用图形表示有 点繁琐且不容易看清楚，这时用表格是一个良好的选 择。 你所不知道的表格的那些事 突出显示单元格 Step1：选中列或者行 Step2：“开始”“样式”中的“条件格式” Step3：选中“突出显示单元格规则”进行设置 项目选取 Step1：选中列或者行 Step2：“开始”“样式”中的“条件格式” Step3：选中“项目选取规则”进行设置 你所不知道的表格的那些事（续） 数据条 数据条的长度代表单元格的值大小。 Step1：选中表中的列或行 Step2：“开始”“样式”中的“条件格式” Step3：选中“数据条”进行颜色的选择 图标集 按照阈值进行分类别，每个图表代表一个范围的值 Step1：选中表中的列或行 Step2：“开始”“样式”中的“条件格式” Step3：选中“图标集”进行图表的选择 迷你图 它是工作表单元格中的一个微型图表，可提供对数据的形象表 示。 Step1：选定要制作迷你图的单元格 Step2：单击“插入”，在“迷你图”组中选择折线图或者柱形 图 2、柱形图 柱形图用来表示一段时间内数据的变化或者描述各项目之间数据比 较。 它分为簇状柱形图、堆积柱形图、百分比堆积柱形图。 其表现形式有二维柱形图、三维柱形图、圆柱图、圆锥图和棱锥图， 其本质没有多大差别，仅仅是柱形的形状各不相同。 簇状柱形图： 堆积柱形图 百分比堆积柱形图 3、折线图 折线图是显示数据的变化趋势，它强调的时间性和变动率。 可分为折线图、堆积折线图以及百分比堆积折线图。 按照有数据标记的可分为带数据标记的折线图、带数据标记的堆积折 线图以及带数据标记的百分比堆积折线图。 折线图： 带数据标记的折线图 4、饼图 饼图是以一个圆形分割成多个部分并填充不同的颜色或图案，可以非 常直观的显示出数据占总数的百分比情况。普通的饼图一般只能显示 一个序系列的数据比例关系，若有多个系列，则只会显示其中的一个 系列。一般饼图只显示五个部分，若多于五个，则利用复合饼图或者 复合条饼图。 饼图： 复合饼图 5、条形图 条形图是显示个项目之间的对比，主要用于表现个项目之间的数据差 额。与柱形图类似。 可分为簇状条形图、堆积条形图、百分比堆积条形图。 其表现形式有二维条形图、三维条形图、圆柱图、圆锥图和棱锥图， 其本质没有多大差别，仅仅是条形的形状各不相同 簇状条形图 堆积条形图 百分比堆积图 6、面积图 面积图是显示每个数据的变化量，强调数据随时间变化的幅度。 面积图 堆积面积图 百分比堆积面积图 7、散点图 用于展示成对的数据之间的关系。将每一对数字钟的第一个数字绘制 在垂直轴上，将另一个数字绘制在水平轴上，常用于绘制函数图象。 散点图： 带平滑线和数据标记的散点图 8、气泡图 气泡图是散点图的一种特殊类型，它在散点的基础上附加了数据系 列。 气泡图： 9、雷达图 雷达图是用于显示数据系列相对于中心点以及相对于彼此数据类别间 的变化，它的每一个分类拥有自己的坐标轴，这些坐标轴由中心向外 辐射，同一系列中的数据值由折线连接起来。常用于多重数据比较。 雷达图： 几种稍复杂图表的绘制与图表美化 1、平均线图 平均线图是对比分析的典型图例。 Excel绘制法： Step1：准备数据，增加一列平均值； Step2：选中数据插入柱形图； Step3：将“平均值”系列转化为折线图，单击右键，选择“更改系列图表类型”，把 柱形图改为折线图。 Step4：图表美化，设置坐标标题与图表标题。 2、双坐标图 在图表中有两个及其以上的系列时，并且它们的量纲不同或者数据 差异较大时，在同一纵坐标轴下就无法很好的展现出数据原本的面 貌，可采用双坐标图来绘制。 (1) 柱形+折线绘制方法： Step1：选择数据绘制柱形图； Step2：单击任一“收入”柱子，单击鼠标右键设置数据系列格式，在“系列绘制 在”选项下，单击“次坐标轴”； Step3：选择“收入”柱子，单击右键，选择“更改系列图表类型”，选择“带数 据标记的折线图”； Step4：在“布局”标签内的坐标轴标签内设置左右坐标轴的说明。 (2) 双柱形图 使用占位法绘制 Step1：添加两个占位数据列； Step2：制作柱形图； Step3：设置最右边的两个序列为次坐标轴； Step4：改占位数据全部为0。 3、竖形折线图 竖形折线图主要在市场研究、咨询等公司使用较广泛，用它来展示 产品功能、品牌形象等在消费者心中的评价，可用于分析与比较多 个不同产品、项目在每个指标方面的表现，得出它们在每个指标属 性偏向。 绘制方法： Step1：选择需要统计的两列数据作“带平滑线和数据标记的散点图”； Step2：分别添加辅助系列1与辅助系列2； Step3：利用Jwalk Chart Tools工具给辅助列1和2添加标签； Step4：把辅助列隐藏，同时把横纵坐标也隐藏； Step5：删除图例和网格线。 4、瀑布图 用于表示企业成本构成、变化等情况。 利用占位法绘制。 Step1：设置占位数； Step2：选中数据插入“堆积柱形图”； Step3：隐藏占位系列； Step4：隐藏纵坐标轴； Step5：添加数据标签。 5、排列图（帕累托图） 排列图是按照发生频率的高低顺序绘制的直方图。 二八法则：事物的80%都是次要的，其余20%是最重要的。 排列图的特点： 1)：按数值降序排列的柱形图，折线图上的数据是累计百分比数据 ； 2)：折线图从0开始，并且位于第一个柱子上的最下角； 3)：折线图的第二个点位于柱形图的第一个柱子的最右上角； 4)：折线图的最后一个数值为100%，位于图的最右上角。 绘制方法： Step1：准备数据，算好累计数与累计百分比； Step2：插入“簇状柱形图”； Step3：更改为次坐标轴，更改为“带数据标记的折线图”； Step4：设置次要横坐标轴“显示从左到右坐标轴”，然后设置位 置坐标轴在刻度线上并隐藏次要横坐标轴； Step5：设置次从坐标轴最大值和柱形图间隔； Step6：设置主要纵坐标轴最大值，添加折线图数据标签，删除图 例和网格线。 排列图实例： 6、成对条形图（旋风图） 旋风图应用的主要情形： 同一事物在某个活动、行为影响前后不同指标的变化； 两个类别之间不同指标的比较； 同一事物在某个条件变化下，另一个指标也受影响随之变化。 绘制方法： Step1：插入“簇状条形图”； Step2：更改为“次坐标轴”； Step3：设置两个横坐标轴最大值与最小值，并设置坐标轴格式代 码为“0;0;0”，显示非负数； Step4：逆序上方的横坐标轴，并隐藏上方的横坐标轴； Step5：设置纵坐标轴，设置坐标轴标签为“低”； Step6：添加“数据标签”，删除网格线。 成对条形图示例 7、漏斗图 绘制方法： Step1：选择需要表示的数据列，插入“堆积条形图”； Step2：逆序纵坐标轴； Step3：颠倒系列次序； Step4：隐藏占位序列； Step5：删除图例和网格线。 40% 75% 67% 85% 8、矩阵图 绘制方法： Step1：插入“仅带数据标记的散点图”； Step2：设置横纵坐标轴分别与纵轴和横轴的交叉点值为平均值； Step3：设置横轴和纵轴的最大值和最小值，并隐藏横轴和纵轴的坐标 标签； Step4：利用工具添加标签； Step5：插入箭头和标签说明； Step6：对象限内的点进行颜色设置。 低 高 重要性 满意度高 数据处理与分析案例精解 企业年度效益分析与客户管理 设计思路分析 在企业经营过程中，需要对企业的效益进行分析，分析企业产 品的供求关系，并对企业客户进行有效的管理，才能使得企业 的销售业绩不断攀升。因此，需要对企业的产品、客户进行深 入的分析，大力开发具有竞争力、市场需求量大的产品，另外 需要培育和维护企业大客户的战略合作关系。