Excel数据分析处理二

会计学1Excel数据分析处理二数据处理与分析

1、数据处理与分析的概念2、数据处理3、数据常用分析方法第1页/共54页数据处理与分析

何谓数据分析数据分析是指用适当的统计分析方法对收集来的大量数据进行分析，将它们加以汇总和理解并消化，以求最大化地开发数据的功能，发挥数据的作用。数据分析是为了提取有用信息和形成结论而对数据加以详细研究和概括总结的过程。数据分析的分类：描述性数据分析：初级的数据分析探索性数据分析：高级的数据分析，如相关分析、回归分析等验证性数据分析：高级的数据分析，如假设检验等数据分析的作用现状分析现阶段的整体情况；企业各项业务的构成原因分析现状为什么会发生预测分析第2页/共54页数据分析六步曲明确分析目的和思路收集数据数据处理数据分析数据展现（图表展现）报告撰写几个常用指标和术语平均数绝对数与相对数百分比与百分点百分点是指不同时期以百分数的形式表示的相对指标的变动幅度。频数与频率比例与比率倍数与番数同比与环比第3页/共54页对数据的理解字段与记录字段是事物或现象的某种特征。记录是事物或现象某种特征的具体表现。数据类型字符型数据数值型数据第4页/共54页数据表二维表

二维表第5页/共54页利用数据透视表实现二维表转化为一维表Step1：在自定义功能去添加“数据透视表和数据透视图向导”。Step2：单击“数据透视表和数据透视图向导”按钮。Step3：点选多重合并计算数据区域，单击下一步。Step4：选中“创建单页字段”，并点击下一步。Step5：选定区域并添加，单击下一步。Step6：选择结果输出，点击完成。Step7：双击数据透视表的最后一个单元格即可获得一维数据表。第6页/共54页数据处理数据清洗清除不必要的重复数据填充缺失数据检测逻辑错误数据数据加工排序：单关键字排序、多关键字排序、自定义排序分类汇总：一级分类汇总、多级分类汇总筛选：自动筛选、自定义筛选、高级筛选数据计算：利用函数和公式以及求和工具进行运算数据抽样抽样：利用函数以及抽样分析工具进行抽样第7页/共54页数据清洗重复数据的处理利用COUNTIF函数判断当前值是否与历史值相同并计数利用高级筛选点选不重复的记录条件格式法“开始”“条件格式”“突出显示单元格规则”“重复值”删除重复数据“数据”“数据工具”组中的“删除重复项”通过对字段的降序排序通过自定义筛选字段缺失数据处理用样本统计量代替，例如平均值用统计模型计算出来的值代替直接删除保留缺失值的记录，但不做具体的分析检查数据逻辑错误IF函数法第8页/共54页数据分析方法现状分析对比分析法平均分析法综合评价分析法原因分析分组分析法结构分析法交叉分析法杜邦分析法漏斗图分析法矩阵关联分析法预测分析相关性分析回归分析移动平均、指数平滑第9页/共54页对比分析法所谓对比分析法，是指将两个或两个以上的数据进行比较，分析它们的差异，从而揭示这些数据所代表的事物发展变化情况和规律性。可分为静态比较和动态比较静态比较是在同一时间条件下对不同总体指标的比较，如不同部门、地区、国家的比较，也叫横向比较。动态比较是在同一总体条件下对不同时期指标数值的比较，也叫纵向比较。注意：比较的指标要一致比较的对象要有可比性第10页/共54页平均分析法平均分析法是指运用计算平均数的方法来反应总体在一定时间、地点条件下某一数量特征的一般水平。平均的指标有算术平均数、几何平均数、调和平均数、众数和中位数等。综合评价分析法鉴于简单分析的局限性，影响分析结果的因素是多个时，需要对每个因素确立一个权重，最后计算一个合计值进行综合评价。分组分析法根据数据分析对象的特征，按照一定的标志，把数据分析对象划分为不同的部分和类型来进行研究，以揭示其内在的联系和规律性。确定组数组距分组整理汇总分析差异第11页/共54页结构分析法结构分析法是指被分析总体内的各部分与总体之间进行对比的分析方法，即总体内各部分占总体的比例，属于相对指标。交叉分析法通常用于分析两个变量之间的关系，即同时将两个有一定联系的变量及其值交叉排列在一张表格内，使各变量值成为不同变量的交叉结点，形成交叉表，从而分析交叉表中变量之间的关系。漏斗图分析法漏斗图是一个适合业务流程比较规范、周期比较长、各流程环节涉及复杂业务过程比较多的管理分析工具。这是一种最直观的表现形式，很快能发现业务流程中存在的问题环节。第12页/共54页矩阵关联分析法矩阵分析法是指根据事物的两个重要属性作为分析的依据，进行分类关联分析，找出解决问题的一种分析方法。以属性A为横轴，以属性B为纵轴，在两坐标轴上分别按照某一标准进行刻度划分，构成四个象限，将要分析的每个事物对应投射到这四个象限内，进行交叉分类分析。低高重要性满意度高第13页/共54页EXCEL2010图表的使用1、介绍几种常用图表2、几种稍复杂图表的绘制与图表美化第14页/共54页几种常用图表图表的概念所谓图表可分为图和表，图即指用图形来表示数据，表即用表格来表示数据。使用图表能够使表达更加形象化，突出重点，体现出专业化基本图表：表格：一维表格、二维表格等柱形图：簇状柱形图、堆积柱形图、百分比堆积柱形图折线图：普通折线图、堆积折线图、百分堆积折线图以及带数据标

记的折线图饼图：饼图、分离型饼图、复合饼图、复合条饼图条形图：簇状条形图、堆积条形图、百分比堆积条形图面积图：普通面积图、堆积面积图、百分比堆积面积图散点图：仅带数据标记的散点图、带平滑线和数据标记的散点图…气泡图：二维、三维气泡图雷达图：普通雷达图、带数据标记的雷达图、填充雷达图第15页/共54页1、表格当需要呈现的数据在3个系列以上时，用图形表示有点繁琐且不容易看清楚，这时用表格是一个良好的选择。你所不知道的表格的那些事突出显示单元格Step1：选中列或者行Step2：“开始”“样式”中的“条件格式”Step3：选中“突出显示单元格规则”进行设置项目选取Step1：选中列或者行Step2：“开始”“样式”中的“条件格式”Step3：选中“项目选取规则”进行设置第16页/共54页你所不知道的表格的那些事（续）数据条数据条的长度代表单元格的值大小。Step1：选中表中的列或行Step2：“开始”“样式”中的“条件格式”Step3：选中“数据条”进行颜色的选择图标集按照阈值进行分类别，每个图表代表一个范围的值Step1：选中表中的列或行Step2：“开始”“样式”中的“条件格式”Step3：选中“图标集”进行图表的选择迷你图它是工作表单元格中的一个微型图表，可提供对数据的形象表示。Step1：选定要制作迷你图的单元格Step2：单击“插入”，在“迷你图”组中选择折线图或者柱形图第17页/共54页第18页/共54页2、柱形图柱形图用来表示一段时间内数据的变化或者描述各项目之间数据比较。它分为簇状柱形图、堆积柱形图、百分比堆积柱形图。其表现形式有二维柱形图、三维柱形图、圆柱图、圆锥图和棱锥图，其本质没有多大差别，仅仅是柱形的形状各不相同。簇状柱形图：第19页/共54页堆积柱形图百分比堆积柱形图第20页/共54页3、折线图折线图是显示数据的变化趋势，它强调的时间性和变动率。可分为折线图、堆积折线图以及百分比堆积折线图。按照有数据标记的可分为带数据标记的折线图、带数据标记的堆积折线图以及带数据标记的百分比堆积折线图。折线图：第21页/共54页带数据标记的折线图第22页/共54页4、饼图饼图是以一个圆形分割成多个部分并填充不同的颜色或图案，可以非常直观的显示出数据占总数的百分比情况。普通的饼图一般只能显示一个序系列的数据比例关系，若有多个系列，则只会显示其中的一个系列。一般饼图只显示五个部分，若多于五个，则利用复合饼图或者复合条饼图。饼图：第23页/共54页复合饼图第24页/共54页5、条形图条形图是显示个项目之间的对比，主要用于表现个项目之间的数据差额。与柱形图类似。可分为簇状条形图、堆积条形图、百分比堆积条形图。其表现形式有二维条形图、三维条形图、圆柱图、圆锥图和棱锥图，其本质没有多大差别，仅仅是条形的形状各不相同簇状条形图第25页/共54页堆积条形图百分比堆积图第26页/共54页6、面积图面积图是显示每个数据的变化量，强调数据随时间变化的幅度。面积图第27页/共54页堆积面积图百分比堆积面积图第28页/共54页7、散点图用于展示成对的数据之间的关系。将每一对数字钟的第一个数字绘制在垂直轴上，将另一个数字绘制在水平轴上，常用于绘制函数图象。散点图：第29页/共54页带平滑线和数据标记的散点图第30页/共54页8、气泡图气泡图是散点图的一种特殊类型，它在散点的基础上附加了数据系列。气泡图：第31页/共54页9、雷达图雷达图是用于显示数据系列相对于中心点以及相对于彼此数据类别间的变化，它的每一个分类拥有自己的坐标轴，这些坐标轴由中心向外辐射，同一系列中的数据值由折线连接起来。常用于多重数据比较。雷达图：第32页/共54页几种稍复杂图表的绘制与图表美化1、平均线图平均线图是对比分析的典型图例。Excel绘制法：Step1：准备数据，增加一列平均值；Step2：选中数据插入柱形图；Step3：将“平均值”系列转化为折线图，单击右键，选择“更改系列图表类型”，把柱形图改为折线图。Step4：图表美化，设置坐标标题与图表标题。第33页/共54页2、双坐标图在图表中有两个及其以上的系列时，并且它们的量纲不同或者数据差异较大时，在同一纵坐标轴下就无法很好的展现出数据原本的面貌，可采用双坐标图来绘制。(1)柱形+折线绘制方法：Step1：选择数据绘制柱形图；Step2：单击任一“收入”柱子，单击鼠标右键设置数据系列格式，在“系列绘制在”选项下，单击“次坐标轴”；Step3：选择“收入”柱子，单击右键，选择“更改系列图表类型”，选择“带数据标记的折线图”；Step4：在“布局”标签内的坐标轴标签内设置左右坐标轴的说明。第34页/共54页(2)双柱形图使用占位法绘制Step1：添加两个占位数据列；Step2：制作柱形图；Step3：设置最右边的两个序列为次坐标轴；Step4：改占位数据全部为0。第35页/共54页3、竖形折线图竖形折线图主要在市场研究、咨询等公司使用较广泛，用它来展示产品功能、品牌形象等在消费者心中的评价，可用于分析与比较多个不同产品、项目在每个指标方面的表现，得出它们在每个指标属性偏向。绘制方法：Step1：选择需要统计的两列数据作“带平滑线和数据标记的散点图”；Step2：分别添加辅助系列1与辅助系列2；Step3：利用JwalkChartTools工具给辅助列1和2添加标签；Step4：把辅助列隐藏，同时把横纵坐标也隐藏；Step5：删除图例和网格线。第36页/共54页4、瀑布图用于表示企业成本构成、变化等情况。利用占位法绘制。Step1：设置占位数；Step2：选中数据插入“堆积柱形图”；Step3：隐藏占位系列；Step4：隐藏纵坐标轴；Step5：添加数据标签。第37页/共54页5、排列图（帕累托图）排列图是按照发生频率的高低顺序绘制的直方图。二八法则：事物的80%都是次要的，其余20%是最重要的。排列图的特点：1)：按数值降序排列的柱形图，折线图上的数据是累计百分比数据；2)：折线图从0开始，并且位于第一个柱子上的最下角；3)：折线图的第二个点位于柱形图的第一个柱子的最右上角；4)：折线图的最后一个数值为100%，位于图的最右上角。绘制方法：Step1：准备数据，算好累计数与累计百分比；Step2：插入“簇状柱形图”；Step3：更改为次坐标轴，更改为“带数据标记的折线图”；Step4：设置次要横坐标轴“显示从左到右坐标轴”，然后设置位置坐标轴在刻度线上并隐藏次要横坐标轴；Step5：设置次从坐标轴最大值和柱形图间隔；Step6：设置主要纵坐标轴最大值，添加折线图数据标签，删除图例和网格线。第38页/共54页排列图实例：第39页/共54页6、成对条形图（旋风图）旋风图应用的主要情形：同一事物在某个活动、行为影响前后不同指标的变化；两个类别之间不同指标的比较；同一事物在某个条件变化下，另一个指标也受影响随之变化。绘制方法：Step1：插入“簇状条形图”；Step2：更改为“次坐标轴”；Step3：设置两个横坐标轴最大值与最小值，并设置坐标轴格式代码为“0;0;0”，显示非负数；Step4：逆序上方的横坐标轴，并隐藏上方的横坐标轴；Step5：设置纵坐标轴，设置坐标轴标签为“低”；Step6：添加“数据标签”，删除网格线。第40页/共54页成对条形图示例第41页/共54页7、漏斗图绘制方法：Step1：选择需要表示的数据列，插入“堆积条形图”；Step2：逆序纵坐标轴；Step3：颠倒系列次序；Step4：隐藏占位序列；Step5：删除图例和网格线。40%75%67%85%第42页/共54页8、矩阵图绘制方法：Step1：插入“仅带数据标记的散点图”；Step2：设置横纵坐标轴分别与纵轴和横轴的交叉点值为平均值；Step3：设置横轴和纵轴的最大值和最小值，并隐藏横轴和纵轴的坐标标签；Step4：利用工具添加标签；Step5：插入箭头和标签说明；Step6：对象限内的点进行颜色设置。低高重要性满意度高第43页/共54页数据处理与分析案例精解企业年度效益分析与客户管理第44页/共54页设计思路分析在企业经营过程中，需要对企业的效益进行分析，分析企业产品的供求关系，并对企业客户进行有效的管理，才能使得企业的销售业绩不断攀升。因此，需要对企业的产品、客户进行深入的分析，大力开发具有竞争力、市场需求量大的产品，另外需要培育和维护企业大客户的战略合作关系。(1)、分别按照客户和产品汇总销售数据，汇总季度数据和年度销售数据；(2)、分析年度效益，使用图表