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