火端文库
当前位置:首页 » Excel数据统计、分析 » 正文

Excel数据统计、分析


数据统计、 Excel 数据统计、分析运用 Excel 能够完成格外多专业软件才干完成的数据统计、分析任务,比如:直方图、相 关系数、协方差、各种概率散布、抽样 与静态模拟、总体均值推断,均值推断、线性、非线 性回来、多元回来分析、时刻序列等。

本专题将教您完成几种最常用的专业数据分析任务。

留意:全部操作将经过 Excel“分析数据库”工具完成,假设您没有装置这项功用,请 留意 依次抉择“工 具”-“加载宏”,在装置光盘中加载“分析数据库”。

加载成功后,能够在 “工具”下拉菜单中看到“数据分析”选项。

直方图 某班停止期中考试后,需求统计成效的 均匀值、区间,并给出班级外部先生成效差 异的量化尺度,借此来作为处置班与班之间 先生成效的良莠不齐的依 据。

要求失掉尺度 差等统计数值。

样本数据散布区间、尺度差等基本上描画 样本数据范围及动摇大小的统计量,统计标 准差需求失掉样本均值,计算较为 繁琐。

这 些基本上描画样本数据的常用变量, 运用 Excel 数据分析中的“描画统计”即可一次完成。

[详细办法] 某班停止期中考试后,需求统计各分数 段人数,并给出频数散布和累计频数表的直 方图以供分析。

以往手工分析的步骤是先将各分数段的 人数分手统计出来制成一张新的表格,再以 此表格为基础树立数据统计直方图。

运用 Excel 能够直截了当完成此义务。

[详细办法]排位与百分比排位描画统计某班级期中考试停止后,依照要求仅公 布成效,但先生及家长要求了解排名。

故欲 发布成效排名,先生能够经过成效查询到自 己的排名, 并同时失掉该成 绩位于班级百分 比排名(即该同窗是排名位于前“X%”的学

生)。

排序操作是 Excel 的差不多操作, Excel“数据分析”中的“排位与百分 比排 位”能够使那个任务精简,直截了当输入报表。

[详细办法]的,还能够对一个周期中特定时刻段中的数 值停止采样。

也能够采取随机抽样,满足用 户保证抽样的代表性的要求。

[详细办法] 移动均匀相关系数与协方差相关系数是描画两个测量值变量之间的 团圆程度的目标。

用于推断两个测量值变量 的改变能否相关,即,一个变量的较大值是 否与另一个变量的较大值相 关联(正相关); 或许一个变量的较小值能否与另一个变量的 较大值相关联(负相关);依然两个变量中的 值互不关联(相关系数近似于零)。

[详细方 法]移动均匀一定是对一系列改变的数据依照 指定的数据数量依次求取均匀,并以此作为 数据改变的趋向供分析人员参考。

移动均匀 在生活中也不乏见, 气候意 义上的四季界定 一定是移动均匀最好的运用。

我们来看怎么样用 Excel 完成移动均匀的统计任务。

[详细办法]回来分析抽样分析工具抽样分析工具以数据源区域为总体,从 而为其创立一个样本。

当总体太大而不能进 行处置或绘制时,能够选用具有代表性的样 本。

假设确认数据源区域中 的数据是周期性溶液浓度正比对应于色谱仪器中的峰面 积,现欲树立不同浓度下对应峰面积的尺度 曲线以供测试未知样品的实践浓度。

已知 8 组对应数据,树立尺度曲 线,同时对此曲线 停止评价,给出残差等分析数据。

这是一个格外典型的线性拟分解绩,手工 计算一定是采取最小二乘法求出拟合直线的待 定参数,同时 能够得出 R 的值,也一定是相关

系数的大小。

在 Excel 中,能够采取先绘图 再添加趋向线的办法完成前两步的要求。

[详细办法]做数据分析—— ——直方图 用 Excel 做数据分析——直方图运用 Excel 自带的数据分析功用能够完成格外多专业软件才有的数据统计、分析,这其中 包括:直方图、相关系数、协方差、各种概率散布、抽样与动 态模拟、总体均值推断,均值 推断、线性、非线性回来、多元回来分析、时刻序列等外容。

下面将对以上功用逐一作运用 讲解,便利各位平常读者和相关专业人员参 考运用。

注:本功用需求运用 Excel 扩张功用,假设您的 Excel 尚未装置数据分析,请依次抉择 “工具”-“加载宏”,在装置光盘中加载“分析数据库”。

加载成功后,能够在“工具”下 拉菜单中看到“数据分析”选项。

实例 1 某班级期中考试停止后,需求统计各分数段人数,并给出频数散布和累计频数表的直方 图以供分析。

以往手工分析的步骤是先将各分数段的人数分手统计出来制成一张新的表格,再以此表 格为基础树立数据统计直方图。

运用 Excel 中的“数据分析” 功用能够直截了当完成此义务。

操作步骤 1.翻开原始数据表格,制造本实例的原始数据要求单列,确认数据的范围。

本实例为化 学成效,故数据范围确定为 0-100。

2.在右侧输入数据接纳序列。

所谓“数据接纳序列”,一定是分段统计的数据间隔,该区 域包括一组可选的用来定义接纳区域的边界值。

这些值应该按升 序陈列。

在本实例中,一定是 以多少分数段作为统计的单元。

可采取拖动的办法生成,也能够依照需求自行设置。

本实例 采取 10 分一个分数统计单元。

3.抉择“工具”-“数据分析”-“直方图”后,浮现属性设置框,依次抉择: 输入区域:原始数据区域; 接纳区域:数据接纳序列; 假设抉择“输入区域”,则新对象直截了当拔出往后表格中; 选中“柏拉图”,此复选框可在输入表中按降序来显示数据; 若抉择“累计百分率”,则会在直方图上叠加累计频率曲线;

4.输入终了后,则可立即生成相应的直方图,这张图还需求比拟大的调整。

主假设: 主假设: 横纵坐标的标题、柱型图的间隔以及各种数据的字体、字号等等。

为了抵达柱型图之间无缝的严密陈列,需求将“数据系列格式”中的“选项”中“分类 间距”调整为“0”。

其他细节,请双击要调整的对象依照通例方 法停止调整,那个地点不再赘 述。

调整后的直方图参考如下做数据分析—— ——描画统计 用 Excel 做数据分析——描画统计某班级期中考试停止后,需求统计成效的均匀值、区间,以及给出班级外部先生成效差 异的量化尺度,借此来作为处置班与班之间先生成效的良莠不齐的 依照。

要求失掉尺度差等 统计数值。

样本数据散布区间、尺度差等基本上描画样本数据范围及动摇大小的统计量,统计尺度差 需求失掉样本均值,计算较为繁琐。

这些基本上描画样本数据的常用 变量,运用 Excel 数据分 析中的“描画统计”即可一次完成。

注:本功用需求运用 Excel 扩张功用,假设您的 Excel 尚未装置数据分析,请依次抉择 “工具”-“加载宏”,在装置光盘中加载“分析数据库”。

加载成功后,能够在“工 具” 下拉菜单中看到“数据分析”选项。

操作步骤 1.翻开原始数据表格,制造本实例的原始数据无特殊要求,只需满足行或列中为同一属 性数值即可。

2. 抉择“工具”-“数据分析”-“描画统计”后,浮现属性设置框,依次抉择: 输入区域:原始数据区域,能够选中多个行或列,留意抉择相应的分组方式; 假设数据有标志,留意勾选“标志位于第一行”;假设输入区域没有标志项,该复选框 将被扫除,Excel 将在输入表中生成合适的数据标志; 输入区域能够抉择本表、新任务表或是新任务簿; 汇总统计:包括有均匀值、尺度误差(相关于均匀值)、中值、众数、尺度错误、方差、 峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大 值、最小值和置信度等相关项 目。

其中: 中值:排序后位于中间的数据的值; 众数:浮现次数最多的值; 峰值:权衡数据散布坎坷改变的目标,以正态散布为基准,比其陡峭时值为正,反之则 为负; 偏斜度:权衡数据峰值偏移的指数,依照峰值在均值左侧或许右侧分手为正值或负值; 极差:最大值与最小值的差。

第 K 大(小)值:输入表的某一行中包括每个数据区域中的第 k 个最大(小)值。

均匀数置信度:数值 95% 可用来计算在清楚性程度为 5% 时的均匀值置信度。

结果示例如下(本实例演示了双列数据的描画统计结果): 成效 均匀 尺度误差 中位数 众数 尺度差 方差 峰度 偏度 区域 最小值 最大值 求和 观测数 最大 (1) 78.64285714 2.408241878 85 98 18.02163202 324.7792208 1.464424408 -1.130551511 85 15 100 4404 56 100 均匀 尺度误差 中位数 众数 尺度差 方差 峰度 偏度 区域 最小值 最大值 求和 观测数 最大 (1) 学习时刻 62.91428571 1.926593502 68 78.4 14.41730562 207.8587013 1.464424408 -1.13055151 68 12 80 3523.2 56 80

最小 (1)15最小 (1)12置信度 (95.0%) 4.826224539 置信度 (95.0%) 3.860979631做数据分析—— ——排位与百分比排位 用 Excel 做数据分析——排位与百分比排位某班级期中考试停止后,依照要求仅发布成效,但先生及家长要求了解排名。

故欲发布 成效排名, 先生能够经过成效查询到自己的排名, 并同时失掉该成 绩位于班级百分比排名(即 该同窗是排名位于前“X%”的先生)。

点那个地点看专题:用 Excel 完成专业化数据统计、分析任务排序操作是 Excel 的差不多操作, Excel“数据分析”中的“排位与百分比排位”能够使这 个任务精简,直截了当输入报表。

注:本功用需求运用 Excel 扩张功用, 假设您的 Excel 尚未装置数据分析, 请依次抉择“工 具”-“加载宏”,在装置光盘中加载“分析数据 库”。

加载成功后,能够在“工具”下拉 菜单中看到“数据分析”选项。

操作步骤 1. 翻开原始数据表格,制造本实例的原始数据无特殊要求,只需满足行或列中为同一属 性数值即可。

2. 抉择“工具”-“数据分析”-“描画统计”后,浮现属性设置框,依次抉择; 输入区域:抉择数据区域,假设有数据标志,留意同时勾选下方“标志位于第一行”; 分组方式:指示输入区域中的数据是按行依然按列琢磨,请依照原数据格式抉择; 输入区域能够抉择本表、新任务表组或是新任务簿。

3.点击“确定”即可看到生成的报表。

能够看到,此刻生成一个四列的新表格,其中“点”是指排序后原数据的序数,在本实 例中对应与学号,这也是格外有用的一个序列;“成效”即为排序后 的数据系列;“排位”采 取反双数据占用同一位子的统计办法;“百分比”是依照降序陈列的,为了失掉真正的“百 分比排位”,还需求稍微作一下调整。

4.在“百分比”列的下一列输入“百分排名”,在第一个单元格中输入公式“=1-G3(对 应于‘百分排名’)”,回车。

选中该单元格,向下拖动 直至填充终了。

如此就抵达了显示 百分比排名的目的。

完成的报表实例如下图所示。

做数据分析—— ——相关系数与协方差 用 Excel 做数据分析——相关系数与协方差化学分解试验中常常需求考察压力随温度的改变状况。

某次试验在两个不同的反响器中 停止同一条件下试验失掉两组温度与压力相关数据,试分析它们与 温度的关联关系,并对在 不同反响器内停止同一条件下反响的牢靠性给出依照。

相关系数是描画两个测量值变量之间的团圆程度的目标。

用于推断两个测量值变量的变 化能否相关,即,一个变量的较大值能否与另一个变量的较大值相 关联(正相关);或许一个 变量的较小值能否与另一个变量的较大值相关联(负相关); 依然两个变量中的值互不关联(相 关系数近似于零)。

设(X,Y)为二元 随机变量,那样:为随机变量 X 与 Y 的相关系数。

p 是度量随机变量 X 与 Y 之间线性相关严密程度的数字 特征。

注:本功用需求运用 Excel 扩张功用, 假设您的 Excel 尚未装置数据分析, 请依次抉择“工 具”-“加载宏”,在装置光盘中加载“分析数据 库”。

加载成功后,能够在“工具”下拉 菜单中看到“数据分析”选项。

操作步骤 1. 翻开原始数据表格,制造本实例的原始数据需求满足两组或两组以上的数据,结果将 给出其中恣意两项的相关系数。

2. 抉择“工具”-“数据分析”-“描画统计”后,浮现属性设置框,依次抉择: 输入区域:抉择数据区域,留意需求满足至少两组数据。

假设有数据标志,留意同时勾选 下方“标志位于第一行”; 分组方式:指示输入区域中的数据是按行依然按列琢磨,请依照原数据格式抉择;

输入区域能够抉择本表、新任务表组或是新任务簿;3.点击“确定”即可看到生成的报表。

能够看到,在相应区域生成了一个 3×3 的矩阵,数据项目的交织处一定是其相关系数。

显 然,数据与本身是完整相关的,相关系数在对角线上显示为 1; 两组数据间在矩阵上有两个 位子,它们是相反的,故右上着反复部分不显示数据。

左下侧相应位子分手是温度与压力 A、 B 和两组压力数据间的相关系数。

从数据统计结论能够看出,温度与压力 A、B 的相关性分手抵达了 0.95 和 0.94,这阐明 它们展现良好的正相关性,而两组压力数据间的相关性达 到了 0.998,这阐明在不同反响器 内的相反条件下反响分歧性格外好,能够疏忽由于改换反响器形成的系统误差。

协方差的统计与相关系数的活的办法相象,统计结果异样前往一个输入表和一个矩阵, 分手显示每对测量值变量之间的相关系数和协方差。

不同之处在于 相关系数的取值在 -1 和 +1 之间,而协方差没有限制的取值范围。

相关系数和协方差基本上描画两个变量团圆程度的指 标。

做数据分析—— ——抽样分析工具 用 Excel 做数据分析——抽样分析工具省教育厅派专家组停止某校检验先生考试试卷,专家组拟对总体停止抽样调查,对学校 某班的全体同窗随机抽取 25 名作为调查样本。

为了保证结果的非 人为性,采取 Excel 关心 专家组做出抽查的结果。

抽样分析工具以数据源区域为总体,从而为其创立一个样本。

当总体太大而不能停止处 理或绘制时,能够选用具有代表性的样本。

假设确认数据源区域中 的数据是周期性的,还可 以对一个周期中特定时刻段中的数值停止采样。

也能够采取随机抽样,满足用户保证抽样的 代表性的要求。

注:本功用需求运用 Excel 扩张功用,假设您的 Excel 尚未装置数据分析,请依次抉择 “工具”-“加载宏”,在装置光盘的支援下加载“数据 分析库”。

加载成功后,能够在工 具的下拉菜单中看到“数据分析”选项。

操作步骤: 操作步骤: 1. 翻开原始数据表格,制造本实例的原始数据无特殊要求,只需满足行或列中为同一属 性数值即可。

实例中显示的是先生学号。

2. 抉择“工具”—“数据分析”—“抽样”后,浮现对话框,依次抉择:

输入区域:把原始总体数据放在此区域中,数据类型不限,数值型或许文本型均可; 抽样办法:有间隔和随机两种。

间隔抽样需求输入周期间隔,输入区域中位于间隔点处 的数值以及尔后每一个间隔点处的数值将被复制到输出列中。

当到 达输入区域的末尾时,抽 样将中止。

(在本例题中没有采取);随机抽样是指直截了当输入样本数,电脑自行停止抽样,不 用受间隔的法规限制; 样本数:在此输入需求在输出列中显示需求抽取总体中数据的个数。

每个数值是从输入 区域中的随机位子上抽取出来的,请留意:任何数值都能够被屡次 抽取!因此抽样所得数据 实践上会有能够小于所需数量。

本文末尾给出了一种处置办法; 输入区域:在此输入对输入表左上角单元格的援用。

全部数据均将写在该单元格下方的 单列里。

假设抉择的是“周期”,则输入表中数值的个数等于输入 区域中数值的个数除以 “间隔”。

假设抉择的是“随机”,则输入表中数值的个数等于“样本数”; 3.接着单击确定就能够显示结果了(这是电脑自行随机抽样的结果)。

需求阐明的状况: 需求阐明的状况: 由于随机抽样时总体中的每个数据都能够被屡次抽取,因此在样本中的数据平常都会有 反复现象,处置此成绩有待于次第的完善。

能够运用“抉择”功用 对所得数据停止抉择。

选中样本数据列,依次实行“数据”-“抉择”-“初级抉择”,如下图所示。

最终来的样本结果如下图所示,请您依照经验适当调整在数据样本选取时的数量设置,以 使最终来所得样本数量许多于所需数量。

如有成绩能够发信至: wangbigbird@163.com 与我交流。

做数据分析—— ——移动均匀 用 Excel 做数据分析——移动均匀某化工反响进程,每隔 2 分钟对系统测取一次压力数据。

由于反响的特殊性,需求考察 每 8 分钟的压力均匀值,假设该压力均匀值高于 15MPa,则认 为自属于该均匀值计算范围内 的第一个压力数据浮现时进入反响时期,请运用 Excel 给出反响时期时刻的区间。

移动均匀一定是对一系列改变的数据依照指定的数据数量依次求取均匀,并以此作为数据 改变的趋向供分析人员参考。

移动均匀在生活中也不乏见,气候意 义上的四季界定一定是移动 均匀最好的运用。

注:本功用需求运用 Excel 扩张功用, 假设您的 Excel 尚未装置数据分析, 请依次抉择“工 具”-“加载宏”,在装置光盘支援下加载“分析数 据库”。

加载成功后,能够在“工具” 下拉菜单中看到“数据分析”选项。

操作步骤 1.翻开原始数据表格,制造本实例的原始数据要求单列,请确认数据的类型。

本实例为 压力随时刻改变成对数据,在数据分析时仅采取压力数据列。

需求留意的是,由于均匀值的求取需求一定的数据量,那样就要求原始数据量许多于求 取均匀值的个数,在 Excel 中规章数据量许多于 4。

2.抉择“工具”-“数据分析”-“直方图”后,浮现属性设置框,依次抉择: 输入区域:原始数据区域;假设有数据标签能够抉择“标志位于第一行”; 输入区域:移动均匀数值显示区域;

间隔:指定运用几组数据来得出均匀值; 图表输入;原始数据和移动均匀数值会以图表的方式来显示,以供比拟; 尺度误差:实践数据与预测数据(移动均匀数据)的尺度差,用以显示预测与实践值的差 距。

数字越小则标明预测状况越好。

3.输入终了后,则可立即生成相应的数据和图表。

从生成的图表上能够看出格外多信息。

依照要求,生成的移动均匀数值在 9:02 时曾经抵达了 15.55MPa,也一定是说,包括本次 数据在内的四个数据前就曾经抵达了 15MPa,那样 阐明在 8 分钟前,也一定是 8:56 时,系统

进入反响时期;采取异样的分析办法能够了解,反响时期完毕于 9:10,反响时期时刻区间为 8:56-9:10,共 延续 14 分钟。

单击其中一个单元格“D6”,能够看出它是“B3-B6”的均匀值,而单元格“E11”则是 “SQRT(SUMXMY2(B6:B9,D6:D9)/4)”,它的意义是 B6-B9,D6-D9 对应数据的差的平方的均匀 值再取平方根,也一定是数组的 尺度差。

做数据分析—— ——回来分析 用 Excel 做数据分析——回来分析在数据分析中,关于成对成组数据的拟合是常常遇到的,触及到的义务有线性描画,趋 势预测和残差分析等等。

格外多专业读者遇见此类成绩时往往追求专 业软件,比如在化工中经 常用到的 Origin 和数学中罕见的 MATLAB 等等。

它们虽格外专业,但事实上运用 Excel 就完整够 用了。

我们曾经了解在 Excel 自带的数据库中已有线性拟合工具,但是它还稍显薄弱,改日 我们来尝试运用较为专业的拟合工具来对此类数据停止处置。

注:本功用需求运用 Excel 扩张功用,假设您的 Excel 尚未装置数据分析,请依次抉择 “工具”-“加载宏”,在装置光盘支援下加载“分析数 据库”。

加载成功后,能够在“工 具”下拉菜单中看到“数据分析”选项 现欲树立不同浓度下对应峰面积的标 实例 某溶液浓度正比对应于色谱仪器中的峰面积, 准曲线以供测试未知样品的实践浓度。

已知 8 组对应数据,树立尺度曲线,同时对 此曲线进 行评价,给出残差等分析数据。

这是一个格外典型的线性拟分解绩,手工计算一定是采取最小二乘法求出拟合直线的待定参 数,同时能够得出 R 的值,也一定是相关系数的大小。

在 Excel 中,能够采取先绘图再添加趋 势线的办法完成前两步的要求。

抉择成对的数据列,将它们运用“X、Y 散点图”制成散点图。

在数据点上单击右键,抉择“添加趋向线”-“线性”,并在选项标签中要求给出公式和 相关系数等,能够失掉拟合的直线。

由图中可知,拟合的直线是 y=15620x+6606.1,R2 的值为 0.9994。

由于 R2 >0.99,因此这是一个线性特征异常清楚的试验模型,即阐明拟合直线能够以大 于 99.99%地讲解、涵盖了实测数据,具有格外好的平常性,能够作为 尺度任务曲线用于其他 未知浓度溶液的测量。

为了进一步运用更多的目标来描画这一个模型,我们运用数据分析中的“回来”工具来 详细分析这组数据。

在选项卡中清楚详细多了,留意抉择 X、Y 对应的数据列。

“常数为零”一定是指明该模型 是严厉的正比例模型,本例真实是如此,由于在浓度为零时相应 峰面积一定为零。

往日得出 的回来方程尽管拟合程度相当高,但是在 x=0 时,照旧有对应的数值,这清楚是一个可笑的 结论。

因此我们抉择“常数为零”。

“回来”工具为我们提供了三张图,分手是残差图、线性拟合图和正态概率图。

重点来 看残差图和线性拟合图。

在线性拟合图中能够看到,不单有依照要求生成的数据点,而且还有经过拟和处置的预 测数据点,拟合直线的参数会在数据表格中详细显示。

本实例旨在 提供更多信息以起到抛砖 引玉的作用,由于触及到过多的专业术语,请各位读者依照实践,在详细运用中另行参考各 项参数,此不再对更多细节作进一步讲解。

残差图是有关于世纪之与预测值之间差距的图表,假设残差图中的散点在中州上下两侧 零乱散布,那样拟合直线一定是合理的,否则就需求从头处置。

更多的信息在生成的表格中,详细的参数项目完整能够满足回来分析的各项要求。

下图 提供的是拟合直线的得回来分析中方差、尺度差等各项信息。

 
 

微信扫一扫 送福利