Excel教程数据统计和处理分析 学习要求 学会使用Excel进行数据高级分析Excel背景介绍 Excel是微软公司office系列办公软件的组件之一，它 是一个功能强大的电子表格程序，具有强大的数据处理 和数据分析能力，是个人及办公事务中进行表格处理和 数据分析的理想工具之一。 Excel不仅可以将整齐而美观的表格呈现给用户，还可 以用来进行数据的分析和预测，完成许多复杂的数据运 算，帮助使用者做出更加有根据的决策。同时它还可以 将表格中的数据通过各种各样的图形、图表的形式表现 出来，增强表格的表达力和感染力。 Excel工作界面 标题栏(工作簿名称) 快速访问工具栏 菜单栏 功能区 编辑栏 状态栏 全选 行标题 列标题 活动单元格 Excel功能 使用统计函数统计数据 按分类汇总统计数据 使用图表统计数据 数据的假设检验分析 数据的描述统计分析 规划求解与最优化方案选择 使用排序和筛选分析数据 数据的预测分析 数据的透视分析 问卷调查分析 使用交互图表分析数据 数据的抽样分析 Excel应用基础1.1 Excel数据录入 1.2 Excel 数据处理 1.3 Excel 安全设置 Excel函数应用2.1 Excel函数介绍 2.2 常用函数介绍 Excel图表应用3.1 图表基础 3.2 常用图表 数据透视表与数据透视图4.1 数据透视表 4.2 数据透视图 Excel统计分析功能5.1 描述统计 5.2 检验分析 5.3 方差分析 5.4回归分析 1.1 Excel数据录入 1900-1-123：59：59 设置单元格格式 1.2 Excel数据处理 数据筛选仅显示满足条件的数据，隐藏不满足条件的数据 高级筛选筛选不重复记录 删除重复项 分类汇总对数据按类别进行汇总统计分析 嵌套分类汇总1.3 Excel安全设置 对工作表保护只希望对工作簿中的某一两个工作表设定只读或隐藏状态 打开审阅-保护工作表 对工作簿保护需要对整个工作簿进行安全设置 打开审阅-保护工作簿 输入保护密码补充：工作簿和工作表区别？ 工作簿是处理和存储数据的文件。

由于每个工作簿可以包含多张工作表，因此可在一个文件中管理多种类 型的相关信息。 工作表可以显示和分析数据。用户能够同时在多张工作表上输入并编辑数据，并且可以对不同工作表的数 据进行汇总计算。在创建图表之后，既可以将其置于 源数据所在的工作表上，也可以放置在单独的图表工 函数定义函数就是预定义的公式。它使用一些参数进行运算并产生返回结果。 Excel中有350多个内置函数，分数学与三角函数、财务函数、统计函数、工程函数等11大类，用户还可以使用VBA自定义函数。 =IF(AVERAGE(A2:A5)>50,SUM(B2:B5),0)=IF(A2=10,”科技处”，IF(A2=20,”财务处”，“人事处”)) 2.1 Excel函数介绍 公式与函数区别？ 函数：是预先定义的计算公式excel数据分析，可实现复杂的计算和操作公式：=（A1+A2+A3+„+A100）/100 函数：= AVERAGE（A1：A100） 公式：=（A1+A2+A3+„+A100）/100 函数：= AVERAGE（A1：A100） 函数组成参数—用括号()括起 参数类型：无参数型——括号必须保留 有参数型——1个/固定数量/不确定数量 参数形式：引用、表达式、函数、常量函数名称(参数1，参数2，[参数3],„.) SUM(A1，C3:C6,E5/3,SUM(F1:F9),325) 函数名称(参数1，参数2，[参数3],„.) SUM(A1，C3:C6,E5/3,SUM(F1:F9),325) 2.2 常见函数介绍 统计数据的个数 的函数 COUNT 计算日期和数值的个数 COUNTA 计算数据的个数 COUNTBLANK 计算空白单元格的个数 COUNTIF 求满足条件的单元格个数 求平均值的函数 AVERAGE 计算数值数据的平均值 AVERAGEA 计算所有数据的平均值 计算倍数和众数 的函数 MEDIAN 计算数据群的中位数 MODE 计算数据群的众数 计算最大值和最小值的 函数 MAX 计算数值的最大值 MAXA 计算所有数据的最大值 MIN 计算数值的最小值 MINA 计算所有数据的最小值 计算方差的函数 VAR 计算数据群的中位数 VARA 计算数据群的众数 VARP 通过数值计算方差 VARPA 通过所有数据计算方差 制作频度的函数 FREQUENCY 计算区间里所含数值的个数 计算标准偏差的函数 STDEV 通过数值推测数据集的标准偏差 计算各种总计值的函数 SUBTOTAL 计算各种总计值 在单元格中插入函数 选择处理的数据区域应用举例一 某班级的成绩表，如下图所示，其中有两个学生的该门课程缺考excel数据分析，需要分别用到AVERAGE和AVERAGEA函 数来计算两个不同的平均值。

【操作步骤】 在单元格D3中输入公式“=AVERAGE(B2:B9)”，可以计算参加考试学生的平均成绩，函数AVERAGE 在计算时对缺考的两个学生的成绩自动认为是0。 在单元格D6中输入公式“=AVERAGEA(B2:B9)”，可以计算全体学生的平均成绩，对缺考的学生成 绩，AVERAGE函数将自动忽略。 某班级的成绩表，如下图所示。需要用到FREQUENCY函数来来统计各个分数段的分布学生人数。 应用举例二 功能：统计指定单元格区域中数据的频率分布。由于函数 FREQUENCY 返回一个数组，必须以数组 公式的形式输入。 语法形式:FREQUENCY(data_array,bins_array) data_array为一数组或对一组数值的引用，用来计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零数组。 bins_array为一数组或对数组区域的引用，设定对data_array 进行频率计算的分段点。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回 data_array 元素的数目。 【操作步骤】 在E4~E7单元格输入进行频率计算的分段点{59,69,79,89}； 在公式栏输入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“Ctrl＋Shift＋Enter” 组合键锁定数 组公式即可。

输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个 单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁 定数组公式，Excel将在公式两边自动加上花括号“{ 功能：计算单元格区域中的两个数列之间的相关系数。 语法形式:CORREL（arrnyl，arrny2） 某医生为了探讨缺碘地区母婴TSH水平关系，应用免疫放射分析测定了160名孕妇（孕周15～17w）及分 娩时脐带血TSH水平（mU/L），现随机抽取10对数据 如图所示，试求脐带血TSH水平对母血TSH水平的相 关系数。 应用举例三 【操作步骤】 选中结果输出单元格B14； 在公式栏输入“=CORREL(C3:C12,B3:B12)”,点击 公式栏 按钮； 在单元格A14输入“相关系数”即可。 链接：相关系数 相关系数是研究变量之间线性相关程度的量，一般用字母 表示。Cov(X,Y)为X与Y的协方差，Var[X]为X的方差，Var[Y]为Y的方差 r（X，Y）是表示变量X与Y之间紧密程度的量 Y的相关程度，即|ρxy|越大，相关程度越大；|ρxy|=0越大，相关程 度越大； 3.1 图表基础 图表：是数据的图形化表示，是动态的——随对应数据的变化而自动更新。

图表组成图表区 绘图区 数据系列(某一行/列) 横坐标(分类轴) 纵坐标(数值轴) 网格线 图例 标题(图表标题、横纵坐标标题) 数据标签 3.2 常用图表 面积图：强调数量随时间变化的程度/引起对总之趋势的注意 Excel中常见图表 创建图表过程 插入图表选择图表类型生成基本图表 “图表工具”关联菜单更改外观/布局、图表数据、图表元素修饰/修改图表 为了反映医院管理的质量，某市抽样调查了几所 医院的6 项指标（指标值越大表示质量越高）， 其中三所医院的调查结果如图2-65所示，请绘制 雷达图反映三所医院的管理质量。 应用举例四 THANK YOU SUCCESS 2019-8-3 可编辑 •数据透视表是一种对大量数据快速汇总和建立交 叉列表的交互式表格。 •它不仅可以转换行和列以查看源数据的不同汇总 结果，显示不同页面以筛选数据，还可以根据需 要显示区域中的明细数据，这对分析、组织复杂 数据是非常方便有用的。 •数据透视表最大的特点就是它的交互性。创建一 个数据透视表以后，可以任意重新排列数据信息， 并且还可以根据习惯将数据分组。 4.1 数据透视表 创建数据透视表制作数据透视表 所需要的数据 选择数据源 选择透视表位置 数据透视表设置和显示数据透视表 数据透视表 设置区域 4.2 数据透视图 数据透视图可以看作是数据透视表和图表的结合，它以图形 的形式表示数据透视表中的数据。

正像在数据透视表里那样， 用户可以更改数据透视图报表的布局和显示的数据。数据透视 图具有Excel图表显示数据的所有能力，而且同时又具有数据 透视表的方便和灵活等特性。 创建数据透视图1、选中数据透视表 2、点击插入 3、插入数据透视图 4、选择插入的透视图 数据透视图数据透视表 数据透视图 5.1 描述统计 •描述统计是基础分析工具之一，用于生成对输入区域 中数据的统计分析报表，提供有关数据趋中性和易变 性的信息，而这些信息是进一步分析数据的起点，可 以指示哪一种检验适合进一步的探讨。 •描述统计的统计函数一般包括：MAX、MIN、AVERAGE、 MEDIAN、GEOMEAN、HARMEAN、AVEDEV、STDEV、VAR、 KURT和SKEW函数等，他们分别用于计算最大值、最小 值、平均值、中位数、几何平均数、调和平均数，以 及变异统计的平均差、标准差、方差峰度和偏度。 某老师对几名学生的五次考试成绩进行分析，以 便对这几个学生在学习方面存在的问题加以解决。 使用“描述统计”分析工具对其数据进行分析。 应用举例五 1、执行菜单栏的“数据”|“数据分析”命令，打开 “描述统计”对话框。

2、在“输入区域”编辑框中键入三列数据所在的单元 格区域引用（$A$4:$F$8）。 3、单击“逐行”选项。 4、选中“标志位于第一列”选项。 5、在“输出选项”下单击“新工作表组”选项，并在 对应编辑框中输入新工作表的名称，如：“描述统计 结果”。 【操作步骤】 5.2 检验分析 t-检验：“平均值的成对双样本分析”分析工具及 其公式可以进行成对双样本t-检验，用来确定样本 平均值是否不等。此t-检验窗体并不假设两个总体 的方差是相等的。当样本中出现自然配对的观察值 时，可以使用这种成对检验。例如，对一个样本组 进行了两次检验，抽取实验前的一次和实验后的一 某医院用中药治疗9例再生障碍性贫血，结果如下图所示。试用“t-检验：平均值的成对二样本分 析”工具，分析治疗前后是否有改变？ 应用举例六 【操作步骤】 （1）在Excel2016，创建一个新的Excel工作薄，如 上图所示输入需要分析的数据； （2）执行菜单栏中的“数据”|“数据分析”命令， 打开“数据分析”对话框，选中“分析工具”列 表框中的“t-检验：平均值的成对二样本分析” 选项。 5.3 方差分析 单因素方差分析该分析工具通过简单的方差分析，对两个以上样本平均值 进行相等性假设检验（样本取自具有相同平均值的样本总 体）。

此方法是对双平均值检验（如t-检验）的扩充。 某医院用三种方法治疗某种疾病，结果如下表所示， 试问三组治愈天数有无差 1211 应用举例七【操作步骤】 （1）执行菜单栏中的“数据”|“数据分析”命令， 打开“数据分析”对话框，选中“分析工具”列 表框中的“方差分析：单因素方差分析”选项； （2）单击“确定”按钮，执行“方差分析：单因素 方差分析”对话框； “协方差”工具用来度量两个区域中数据的关系，用于返回各数据点与其各自所在数据组的平均值之间的 偏差乘积的平均值。 使用协方差工具可以用来确定两个区域中数据的变化是否相关，即一个集合的较大数据是否与另一个集合 的较大数据相对应（正协方差）；或者一个集合的较 小数据是否与另一个集合的较大数据相对应（负协方 差）；还是两个集合中的数据互不相关（协方差为 零）。协方差对度量单位是敏感的。 18名儿童身高、体重、表面积测定数据如下图所示，试利用“协方差”分析工具分析身高、体重、 表面积三个指标之间的关系。 应用举例八 【操作步骤】 （1）执行菜单栏中的“数据”|“数据分析”命令， 打开“数据分析”对话框，选中“分析工具”列 表框中的“协方差”选项； （2）选择数据输入区域和输出区域 （3）单击“确定”按钮，执行“协方差”对话框。

5.4 回归分析 通过对一组观察值使用“最小二乘法”直线拟合进行线性回归分析，可

来源【首席数据官】，更多内容/合作请关注「辉声辉语」公众号，送10G营销资料！