excel数据分析教程.docx

excel数据分析教程.docx

《excel数据分析教程.docx》由会员分享，可在线阅读，更多相关《excel数据分析教程.docx（44页珍藏版）》请在冰豆网上搜索。

excel数据分析教程

用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做数据分析—抽样分析工具

省教育厅派专家组进行某校检查学生考试试卷，专家组拟对总体进行抽样调查，对学校某班的全体同学随机抽取25名作为调查样本。

为了保证结果的非人为性，采用Excel帮助专家组做出抽查的结果。

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

当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。

如果确认数据源区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。

也可以采用随机抽样，满足用户保证抽样的代表性的要求。

注：

本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘的支持下加载“数据分析库”。

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

　　操作步骤：

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

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

　　2.选择“工具”—“数据分析”—“抽样”后，出现对话框，依次选择：

　　输入区域：

把原始总体数据放在此区域中，数据类型不限，数值型或者文本型均可；

　　抽样方法：

有间隔和随机两种。

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

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

（在本例题中没有采用）；随机抽样是指直接输入样本数，电脑自行进行抽样，不用受间隔的规律限制；

　　样本数：

在此输入需要在输出列中显示需要抽取总体中数据的个数。

每个数值是从输入区域中的随机位置上抽取出来的，请注意：

任何数值都可以被多次抽取!

所以抽样所得数据实际上会有可能小于所需数量。

本文末尾给出了一种处理方法；

　　输出区域：

在此输入对输出表左上角单元格的引用。

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

如果选择的是“周期”，则输出表中数值的个数等于输入区域中数值的个数除以“间隔”。

如果选择的是“随机”，则输出表中数值的个数等于“样本数”；

　　3.然后单击确定就可以显示结果了（这是电脑自行随机抽样的结果）。

　　原始数据文档在这里下载>>　操作结果文档在这里下载>>

　　需要说明的情况：

　　由于随机抽样时总体中的每个数据都可以被多次抽取，所以在样本中的数据一般都会有重复现象，解决此问题有待于程序的完善。

可以使用“筛选”功能对所得数据进行筛选。

　　选中样本数据列，依次执行“数据”-“筛选”-“高级筛选”，如下图所示。

　　最后的样本结果如下图所示，请您根据经验适当调整在数据样本选取时的数量设置，以使最终所得样本数量不少于所需数量。

实现Excel动态链接外部数据库

我们有时需要在Excel中调取其他数据库的数据，并且希望其他数据库数据改变时，Excel中调取的数据也随之动态改变。

下面介绍在Excel中通过“新建数据库查询”（MicrosoftQuery）的方法来实现动态链接数据库。

　　您在Excel中第一次使用“新建数据库查询”查询数据时，如果系统未安装MicrosoftQuery，系统会提示您安装。

　　一、Excel链接vfp数据库

vfp是常用的数据库，先以此来介绍。

假设从px.dbf表中取出相应学校的数据，已知学校的后5位代码，数据所在字段为x16，数据值为03行和06行相加，放入Excel单个单元格中。

　　步骤如下:

　　1、点击菜单“数据”—“导入外部数据”—“新建数据库查询”，出现“选择数据源”对话框，选择“VisualFoxproTables*”，按“确定”。

如果您对SQL语句不是很熟悉的话，请使用默认的“查询向导”创建。

（图1）

　　2、在“configureconnection”对话框中，按“Browse”按钮选择表px.dbf所在的路径，按“ok”键。

（图2）

　　3、在“查询向导—选择列”对话框中，选择查询结果中的列，要选取的数据在“x16”字段，所以将可用表“px”中的“x16”列移至“查询结果中的列”，按“下一步”。

（图3）

　　4、在“查询向导—筛选数据”对话框中，因为此例中设置的条件在字段学校代码“xxdm”和行号“bh”，非字段“x16”，所以点击“下一步”，暂时不筛选数据。

（图4）

　　5、在“查询向导—排序顺序”对话框中，因为此例中所取的是单个数据，所在点击“下一步”。

（图5）

　　6、出现“查询向导—完成”对话框，因还要对数据进行筛选，所以选择“在MicrosoftQuery中查看数据或编辑查询”，点击“完成”。

（图6）

　　7、进入“MicrosoftQuery”，看到查询结果x16列出了所有行的数据。

　　接下来要通过设置条件来筛选所需的数据。

　　点击菜单“条件”—“增加条件（A）...”，字段“bh”,运算符“等于”，指定值为“03”，按“添加”。

（图7）

　　继续“添加条件”对话框，选择“或”，字段“bh”,运算符“等于”，指定值为“06”，按“添加”。

（图8）

　　继续“添加条件”对话框，选择“与”，字段“xxdm”,运算符“等于”，指定值为“11201”，按“添加”后按“关闭”。

（图9）

　　但是看到符合条件x16为空，这是因为学校代码的后五位为“11201”。

　　可以通过“显示SQL”来直接修改条件语句,将SQL语句中px.xxdm="11201"改成right（px.xxdm,5）="11201",按“确定”。

（图10）

　　也可以在“条件字段”直接修改。

（图11）

　　光标放在x16字段，点击“循环总计”，将查询出的两个值相加，当然也可以直接在SQL语句中修改。

（图12）

　　8、关闭“MicrosoftQuery”，弹出“导入数据”对话框，选择“数据的放置位置”，选择“$B$2”，按“确定”。

>

（图13）

　　9、但我们发现显示了标题行，真正的数据却显示到了$B$3，这时我们需要修改$B$2单元格的“数据区域属性”。

点击$B$2单元格，按右键菜单的“数据区域属性”，弹出“外部数据区域属性”窗口，将“数据格式与布局”中“包含字段名”等前面的对勾去掉，将“数据区域中的行数随刷新而更改的方式”改成“用新数据覆盖现有单元格，并清除没有使用的单元格内容”，按“确定”。

但还是有标题，没有关系，点击$B$2单元格，按右键菜单的“刷新数据”即可。

（图14）

　　10、如果px.dbf数据库有所改变，那么如何在Excel中刷新，可以点击链接数据所在单元格右键菜单中的“刷新”手工刷新;也可以在“数据区域属性”—“刷新控件”中选上“打开工作簿时，自动刷新”（见图14），从而在每次打开工作簿时，在“查询刷新”的提示框中按“启动自动刷新”按钮刷新（见图15）。

（图15）

　　11、如果需要编辑查询，则点击$B$2单元格，按右键菜单的“查询编辑”，如果出现“查询向导”无法编辑此查询的提示，只要按“确定”即可进入“MicrosoftQuery”。

　　12、$B$3单元格查询条件与$B$2相差的只是学校代码，可以将$B$2单元格复制到$B$3单位格，再用$B$3的“编辑查询”来修改，修改一下学校代码的值即可。

（图16）

　　二、Excel链接SQL数据库

　　链接SQL数据库与链接vpf大致是相同的。

这里介绍的是连接两表的查询。

　　点击菜单“数据”—“导入外部数据”—“新建数据库查询”，出现“选择数据源”对话框，可直接选取所需链接的SQL数据库名。

　　如果SQL数据库有密码的话，将提示输入密码，按“确定”。

如果SQLSERVER服务管理器未开启的话，将出现错误提示。

　　如果不使用“查询向导”的话，可直接进入“MicrosoftQuery”。

添加表news和表type，news表中的typeid和type表的typeid建立关联。

在“条件字段”中选择字段名，并在“值”中输入条件值，这里输入的条件是type表的typeid字段值为1且news表的topic中含有"小学生"。

点击选择要输出的字段名，这里选择的是type表中的typename字段和news表的topic字段。

（图17）

　　当然，如果您对SQL语句熟悉，也可以直接输入SQL语句。

　　接下来的步骤请参考一、中的8、至10、。

用Excel做数据分析—相关系数与协方差

化学合成实验中经常需要考察压力随温度的变化情况。

某次实验在两个不同的反应器中进行同一条件下实验得到两组温度与压力相关数据，试分析它们与温度的关联关系，并对在不同反应器内进行同一条件下反应的可靠性给出依据。

　　相关系数是描述两个测量值变量之间的离散程度的指标。

用于判断两个测量值变量的变化是否相关，即，一个变量的较大值是否与另一个变量的较大值相关联（正相关）；或者一个变量的较小值是否与另一个变量的较大值相关联（负相关）；还是两个变量中的值互不关联（相关系数近似于零）。

设（X,Y）为二元随机变量，那么:

　　为随机变量X与Y的相关系数。

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

　　注:

本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。

加载成功后，可以在“工具”下拉菜单中看到“数据