统计分析培训上课用PPT课件.ppt

统统 计计 分分 析析 ExcelExcelExcel在统计分析中的应用在统计分析中的应用在统计分析中的应用1 1.郑振华郑振华主要内容一、如何进行统计分析三、数据的处理四、数据的汇总分析二、数据的输入五、数据汇总结果的表示六、高级统计分析介绍统计分析的流程介绍如何显示和查询数据介绍常用的汇总分析方法介绍数据的输入技巧如何绘制统计表和统计图统计分析工具库的应用2 2一、如何进行统计分析3 3二、数据输入与编辑技巧v技巧1 1：如何快速输入序号，如1-1001-100，shyy001-shyy100shyy001-shyy100v技巧2 2：如何用填充数据和自定义序列输入数据，如姓名v技巧3 3：如何准确输入身份证号、用身份证提取信息，如性别、年龄v技巧4 4：如何利用数据有效性输入数据，如学历 、职务v技巧5 5：如何用不同方式输入日期和时间v技巧6 6：如何在多个单元格输入相同数据v技巧7 7：如何为行设置不同填充颜色防止输错行v技巧8 8：如何有选择地利用“选择性粘贴”v技巧9 9：如何实现在单元格里手动换行v技巧1010：如何显示标题行固定不变v技巧1111：如何设置打印多页时的打印相同的标题v技巧1212：如何建立和使用工作组进行相同操作4 4技巧1：如何快速输入序号v输入数值型数字：输入1 1，2 2，然后拖动鼠标v输入文本型数字：输入“1”,“1”,然后拖动鼠标v精确输入：输入1 1，然后编辑填充序列v输入带字母的数字：单元格数字自定义，如”shyy”000”shyy”000在“二、数据输入”表中编号列中任务1：输入数字型编号1-20。任务2：输入文本型数字：1，然后拖动鼠标至20。任务3：利用精确输入，输入编号1-100。任务4：输入shyy001-shyy100。5 5技巧2：如何用填充数据和自定义序列输入数据v填充数据填充等差数列、等比数列填充日期v自定义序列如自定义姓名的序列在“二、数据输入”表中任务5：在空白处，填充等差数列为奇数列，初始值为1，终止值为10。任务6：在空白处，填充等比数列，初始值为1，比例为2，终止值为64。任务7：在空白处，填充日期2010-4-26，步长为7，终止值为2010-12-31。任务8：定义姓名的序列，然后删除姓名，用自定义序列的方式输入。6 6技巧3：如何准确输入身份证号、用身份证提取信息v利用“数据有效性”设置身份证号的长度：1818位v用身份证提取信息：提取性别(E3)：=mid(d3,17,1)提取出生日期(F3)：=mid(d3,7.8)转换日期格式：=Text(mid(d3,7.8),”#-00-00”)v利用身份证生成数据转换性别：=if(mod(f3,2)=1,”男”,”女”)计算年龄（以年）：=year(today()-year(F3)计算年龄（以日期）：=datedif(J3,TODAY(),y)在“二、数据输入”表中任务9：设置身份证号的长度为18。任务10：利用身份证号生成性别。任务11：利用身份证号生成出生日期。任务12：计算年龄和工龄。7 7技巧4：如何利用数据有效性输入数据v数据有效性序列，以逗号分隔输入分类数据在“二、数据输入”表中任务13：使用数据有效性设置学历的类型，并根据原始表进行选择。任务14：使用数据有效性设置职务的类型。任务15：设置手机号码的数字类型为文本，并设置文本长度为11。任务16：E-mail为内部邮箱（如姓名全拼字母）的输入方法。8 8技巧5：如何用不同方式输入日期和时间v输入可更新时间=today()=now()v输入不可更新时间直接输入，以-或/分隔使用快捷键：ctrl+;，ctrl+shift+;在“二、数据输入”表中任务17：在空白处，输入可更新日期和时间。任务18：在空白处，输入不可更新的日期和时间。9 9技巧7：如何为行设置不同填充颜色v首先判断奇数行和偶数行；=mod(row(),2)=mod(row(),2)v然后，筛选出奇数行或偶数行；v最后，设置不同的填充颜色。在“二、数据输入”表中任务19：判断奇数行和偶数行。任务20：奇数行和偶数行设置不同的填充颜色。1010技巧8：如何有选择地利用“选择性粘贴”v数值：仅粘贴数字结果，而非粘贴公式v转置：行列互换在“二、数据输入”表中任务21：粘贴年龄列为选择性粘贴，至空白处。任务22：选择性粘贴B2:N22转置至自B25单元格开始处。1111技巧9：如何实现在单元格里换行v自动换行：格式单元格对齐自动换行；v手动换行：ALT+ENTERALT+ENTER在“二、数据输入”表中任务23：在备注N3中随意输入文字，然后设置自动换行。任务24：对N3中的文字进行手动强制换行。1212技巧10：如何显示标题行固定不变v窗口冻结窗格在“二、数据输入”表中任务25：设置标题行固定不变。1313技巧11：如何设置打印多页时的打印相同的标题v文件页面设置工作表顶端标题行（或左侧标题行）在“二、数据输入”表中任务26：设置打印标题行为顶端标题行。1414技巧12：如何建立和使用工作组进行相同操作v快速生成格式相同的工作表，如1-121-12月报表任务27：新建工作簿，生成1-12月格式相同报表，并设置相同的格式。1515三、数据的处理显示和查询v（一）排序数据v（二）使用条件格式标识数据v（三）筛选数据1616（一）排序数据v升序：如按年龄v降序v多关键字排序：如按年龄升序，再按工龄升序v自定义排序：如按学历排序，硕士-本科-专科，按职务大小排序，经理主管职员按笔画排序按行排序在表“三、数据处理（排序、筛选、条件格式）”中，任务28：对年龄升序。任务29：对年龄升序，再按工龄降序。任务30：按学历由高到低排序。任务31：对姓名按笔画排序。1717（二）使用条件格式标识数据v格式条件格式如工龄10年以上用红色加粗显示。在表“三、数据处理（排序、筛选、条件格式）”中，任务32：工龄15年以上用红色加粗显示1818（三）筛选数据v自动筛选v高级筛选1919自动筛选v数据筛选自动筛选根据列表筛选：如筛选性别是男的自定义筛选：如筛选年龄30岁以下的空白非空白 注：？代表单个字符，*代表多个字符 在表“三、数据处理（排序、筛选、条件格式）”中，任务33：筛选性别为男的。任务34：筛选年龄30岁以下的。2020高级筛选v设置多个条件区域条件是“与”的关系：同行如筛选年龄35岁以下，且职务是经理的条件是“或”的关系：不同行如筛选年龄35岁以下，或职务是经理的在表“三、数据处理（排序、筛选、条件格式）”中，任务35：筛选年龄35岁以下，且职务是经理的任务36：筛选年龄35岁以下，或职务是经理的2121四、数据的汇总分析v（一）分组分析法v（二）合并计算汇总数据v（三）数据透视表从不同角度分析表中的数据2222（一）分组分析法v分类汇总的方法：先排序，后汇总如男、女人数v利用函数的方法COUNTIF函数如学历构成FREQUENCY函数如年龄构成v利用统计分析工具库直方图在表“四、数据汇总分析(一）分组分析”中，任务37：利用分类汇总统计男女人数。任务38：利用COUNTIF函数统计学历构成。任务39：利用FREQUENCY函数统计龄构成。任务40：利用直方图统计年龄构成。2323（二）合并计算v数据合并计算在表“四、数据汇总分析(二）合并计算”中，任务41：在销售情况总表中计算总销售数量。任务42：在销售情况总表中计算平均销售数量、总销售金额。2424（三）数据透视表25在表“四、数据汇总分析(三）数据透视表”中，任务43：制作“企业员工档案管理表”数据透视表并进行浏览和修改。2525五、数据汇总结果的表示v（一）绘制统计表v（二）绘制统计图2626（一）绘制统计表v如何绘制分组汇总表（次数分布表）27某公司年龄构成表年龄人数（人）比例（%）30岁以下21030-3552535-40115540岁以上210合计20100在表“五、数据汇总结果表示绘制统计表和统计图(一）”中，任务44：按照统计要求制作年龄构成表。2727统计表设计口诀v标题准确又简明v布局均匀又美观v顶线底线加粗线v左右两侧不要封v横栏纵栏要对应v计量单位要注明v单位相同右上方v纵栏不同右下方v横行不同加一栏v栏目多时加序号v主体叙述栏不同v主体栏用甲乙丙v叙述栏用1 1、2 2、3 32828（二）绘制统计图v柱形图：数值的变化和对比v条形图：数值的变化和对比，表现数据差额v饼图：显示一组数据结构比例v圆环图：显示多组数据的结构比例v折线图：显示数据的变化趋势，利用折线图预测v雷达图：相对于中心点及批次数据类别间的差别v散点图：显示成对数据之间的关系v气泡图：散点图的特殊类型v组合图表：如柱形图+折线图29在表“五、数据汇总结果表示绘制统计表和统计图(一）”中，任务45：根据年龄构成表绘制柱形图。任务46：根据年龄构成表绘制饼图。任务47：根据一公司和二公司年龄构成表绘制圆环图。在表“五、数据汇总结果表示绘制统计表和统计图(二）”中，任务48：根据销售额历史数据绘制折线图，并添加趋势线进行预测。任务49：根据销售额历史数据绘制线-柱图的组合图。任务50：根据考核表，绘制雷达图。2929六、高级统计分析工具加载宏分析工具库v（一）描述统计法认识数据总体的特征v（二）相关及回归分析法因变量如何受其他自变量（1 1个变量或多个变量）影响的v（三）时间序列预测法根据过去的模式预测未来v（四）方差分析法分类变量对数值变量有无影响v（五）抽样推断法如何由抽取的样本数据推断总体v（六）假设检验法检验假设是否成立303030（一）描述统计法工具数据分析描述统计，显示如下统计量v平均值、标准误差、中位数、众数、标准差、方差、v峰度、偏度v最小值、最大值v求和v观测数31在表“六、高级统计分析（一）描述统计”中，任务51：根据历史销售额，计算显示描述统计量。3131（二）相关及回归分析法v相关分析：计算相关系数，检验变量间的变化是否相关。v回归分析：利用变量间的关系进行预测。线性回归32在表“六、（二）相关回归分析”中，任务52：根据年份和销售额，绘制散点图。任务53：计算相关系数。任务54：进行回归分析。任务55：根据回归分析结果和年份数据，预测销售额。3232（三）时间序列预测法v移动平均法v指数平滑法v趋势模型预测法33在表“六、（三）时间序列预测”中，任务56：利用移动平均法计算一次移动平均和二次移动平均预测值。任务57：利用指数平滑法预测。3333（四）方差分析法（ANOVA）推断两组或多组资料的总体均值是否相同，判断分类变量对数值变量是否有显著影响。v单因素方差分析v双因素方差分析34在表“六、（四）方差分析”中，任务58：为实验三种镇咳药（可待因、复方1号、复方2号），分别用这三种药给三组小白鼠灌胃，测得小白鼠延迟咳嗽时间（秒）如表所示，利用单因素方差分析，比较三种药物的镇咳作用。（显著性水平=0.05）3434（五）抽样推断法 根据部分资料推断总体的数据特征。v推断的区间：95%95%，t=1.96t=1.9635在表“六、（五）抽样推断法”中，任务59：某企业有职工1385人，现从中随机抽出50人，调查工资收入情况，请以95%的置信度估计该企业月工资的范围。3535（六）假设检验法检验是否应拒绝原先的假设。vF F检验：检验两个正态总体方差有无显著差异。vt t检验：检验两个总体均值有无显著差异。双样本等方差双样本异方差平均值的成对二样本vZ Z检验：检验两个正态总体均值有无显著差异。双样本平均差检验363636F检验：检验两个正态总体方差有无显著差异在表“六、（六）假设检验法”中，任务60：已知某校男女新生录取分数，判断方差是否相等？3737t检验：检验两个总体均值有无显著差异v双样本等方差v双样本异方差v平均值的成对二样本在表“六、（六）假设检验法”中，任务63：某医疗器械公司最近推出一种新的减肥仪器，并以“免费试用”的营销手段大肆宣传。国家质最监督机构为了检查其实际效果，随机访问了10 位试用者，得知其试用前后体重的变化如“减肥仪器试用前后的体重变化”表，请检验体重是否有显著变化？在表“六、（六）假设检验法”中，任务61：根据“某高校男女新生录取分数”表，判断男女新生录取分数是否存在显著差别？在表“六、（六）假设检验法”中，任务62：根据“分性别的工资表”首先根据F检验，判断是否存在异方差，然后判断男女工资是否存在显著性差异？3838Z检验：检验两个正态总体均值有无显著差异在表“六、（六）假设检验法”中，任务64：人们一向传说，北京东北部地区的商品房价格高于西北部地区。为了验证这一说法，在东北部地区按比例抽查了7 家开发商，在西北部地区抽查了6 家开发商，现将其住宅楼每平米起价列表如“东北、西北地区房价对比”表，根据经验已知两地区房价的方差，要求在显著性水平0.05下，用“Z 一检验”工具对两地区平均房价有无显著差异作出判断。3939总结v一般的统计分析流程v常用的ExcelExcel数据输入、处理和汇总统计方法v高级统计分析方法分析工具库的应用4040