Excel 数据分析电子书龙马高新教育搜集整理Excel数据分析电子书1.用Excel做数据分析——移动平均移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均，并以此作为数据变化的趋势供分析人员参考。
移动平均在生活中也不乏见，气象意义上的四季界定就是移动平均最好的应用。
某化工反应过程，每隔2分钟对系统测取一次压力数据。
由于反应的特殊性，需要考察每8分钟的压力平均值，如果该压力平均值高于15MPa，则认为属于该平均值计算范围内的第一个压力数据出现时进入反应阶段，请使用Excel 给出反应阶段时间的区间。
提示:本功能需要使用Excel 扩展功能，如果您的Excel 尚未安装数据分析，可以选择【工具】 【加载宏】菜单命令，在安装光盘支持下加载“分析数据库”。
加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
操作步骤如下：➊打开“素材\实验温度压力随温度变化表1.xls”工作簿，本实例为压力随时间变化成对数据，在数据分析时仅采用压力数据列。
提示：因为平均值的求取需要一定的数据量，那么就要求原始数据量不少于求取平均值的个数，在Excel中规定数据量不少于4。
➋选择【工具】 【数据分析】 【移动平均】菜单命令，弹出【移动平均】对话框，在输入选项中单击输入区域右侧按钮，然后选择B2：B29单元格区域，单击选中【标志位于第一行】复选框，间隔输入“4”。
在输出选项中单击输出区域右侧按钮，选择C3单元格，然后单击选中【图表输出】和【标准误差】复选框。
➌输入完毕后，单击【确定】按钮即可立即生成相应的数据和图表。
Excel 数据分析电子书2. 用Excel 做数据分析——抽样分析工具抽样分析工具以数据源区域为总体，从而为其创建一个样本。
当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。
如果确认数据源区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。
也可以采用随机抽样，满足用户保证抽样的代表性的要求。
某校检查学生考试试卷，拟对总体进行抽样调查，对学校某班的全体同学随机抽取25 名作为调查样本。
为了保证结果的非人为性，采用Excel 帮助专家组做出抽查的结果。
➊ 打开“素材\某中学期中考试化学成绩统计表1.xls ”工作簿，即可看到显示的学生学号。
➋ 选择【工具】 【数据分析】【抽样】菜单命令，弹出【抽样】对话框。
Excel数据分析电子书➌在输入选项中，单击输入区域右侧按钮，选择A3：A58单元格，单击选中【标志】复选框，在抽象方法中选择随机并输入“25”，在输出选项中选中输出区域，然后单击右侧按钮，选择B3：B27单元格，输入结果如下图所示。
➍单击【确定】按钮，即可输出抽样结果。
由于随机抽样时总体中的每个数据都可以被多次抽取，所以在样本中的数据一般都会有重复现象，解决此问题有待于程序的完善。
可以使用“筛选”功能对所得数据进行筛选。
➊选中样本数据列，选择【数据】 【筛选】 【高级筛选】菜单命令，弹出【高级筛选】对话框，单击列表区域右侧按钮，选择B3：B27单元格，并勾选【选择不重复的记录】复选框，如下图所示。
Excel 数据分析电子书➋单击【确定】按钮，最后的样本结果如下图所示，可以根据经验适当调整在数据样本选取时的数量设置，以使最终所得样本数量不少于所需数量。
3. 用Excel 做数据分析——相关系数与协方差化学合成实验中经常需要考察压力随温度的变化情况。
某次实验在两个不同的反应器中进行同一条件下实验得到两组温度与压力相关数据，试分析它们与温度的关联关系，并对在不同反应器内进行同一条件下反应的可靠性给出依据。
相关系数是描述两个测量值变量之间的离散程度的指标。
用于判断两个测量值变量的变化是否相关，即，一个变量的较大值是否与另一个变量的较大值相关联(正相关)；或者一个变量的较小值是否与另一个变量的较大值相关联(负相关)；还是两个变量中的值互不关联(相关系数近似于零)。
设(X,Y)为二元随机变量，那么:为随机变量X 与Y 的相关系数。
p 是度量随机变量X 与Y 之间线性相关密切程度的数字特征。
操作步骤如下：➊ 打开“素材\实验温度压力随温度变化表2.xls ”工作簿。
Excel数据分析电子书➋选择【工具】 【数据分析】 【相关系数】菜单命令，弹出【相关系数】对话框，在【输入】选项中单击输入区域右侧按钮，选择A2：C23单元格，单击选中【标志位于第一行】复选框。
在输出选项中选中输出区域，并单击右侧按钮，选择D7单元格，输入结果如下图所示。
➌单击【确定】按钮，即可看到生成的报表。
可以看到，在相应区域生成了一个3×3 的矩阵，数据项目的交叉处就是其相关系数。
显然，数据与本身是完全相关的，相关系数在对角线上显示为1；两组数据间在矩阵上有两个位置，它们是相同的，故右上侧重复部分不显示数据。
左下侧相应位置分别是温度与压力A、B 和两组压力数据间的相关系数。
从数据统计结论可以看出，温度与压力A、B 的相关性分别达到了0.99，这说明它们呈现良好的正相关性，而两组压力数据间的相关性达到了0.998，这说明在不同反应器内的相同条件下反应一致性很好，可以忽略因为更换反应器造成的系统误差。
协方差的统计与相关系数的活的方法相似，统计结果同样返回一个输出表和一个矩阵，分别表示每对测量值变量之间的相关系数和协方差。
不同之处在于相关系数的取值在-1和+1 之间，而协方差没有限定的取值范围。
相关系数和协方差都是描述两个变量离散程度的指标。
Excel 数据分析电子书4. 用Excel 的数据分析——排位与百分比排序操作是Excel 的基本操作，Excel “数据分析”中的“排位与百分比排位”可以使这个工作简化，直接输出报表。
具体操作步骤如下。
➊ 打开“素材\某中学期中考试化学成绩统计表2.xls ”工作簿。
➋ 选择【工具】 【数据分析】 【排位与百分比排位】菜单命令，弹出【相关系数】对话框，在输入选项中单击输入区域右侧按钮，选择B2：B58单元格，选中【列】选项，单击选中【标志位于第一行】复选框。
在输出选项中选中输出区域，并单击右侧按钮，选择C2单元格，输入结果如下图所示。
➌ 单击【确定】按钮即可看到生成的报表。
可以看到，此时生成一个四列的新表格，其中“点”是指排序后原数据的序数，在本例中对应学号；“成绩”即为排序后的数据系列；“排位”采取重复数据占用同一位置的统计方法；“百分比”是按照降序排列的，为了得到真正的“百分比排位”，还需要稍微作一下调整。
Excel数据分析电子书➍在“百分比”列的下一列输入“百分排名”，在第一个单元格中输入公式“=1-F3”，回车。
选中该单元格，向下拖动直至填充完毕。
这样就达到了显示百分比排名的目的。
完成的报表实例如下图所示。
5.用Excel 做数据分析——描述统计根据考试成绩统计平均值、区间，以及给出成绩差异的量化标准，借此来作为解决班与班之间学生成绩的参差不齐的依据。
要求得到标准差等统计数值。
样本数据分布区间、标准差等都是描述样本数据范围及波动大小的统计量，统计标准差需要得到样本均值，计算较为繁琐。
这些都是描述样本数据的常用变量，使用Excel 数据分析中的“描述统计”即可一次完成。
具体操作步骤如下。
➊打开“素材\某中学期中考试化学成绩统计表2.xls”工作簿。
➋选择【工具】 【数据分析】 【描述统计】菜单命令，弹出【相关系数】对话框，在输入选项中单击输入区域右侧按钮，选择B2：B58单元格，选中【逐列】选项，单击选中【标志位于第一行】复选框。
在输出选项中选中输出区域，并单击右侧按钮，选择G1单元格，输入平均数置信度为95%，第K大置和第K小值分别为1。
如下图所示。
Excel 数据分析电子书➌ 单击【确定】按钮即可看到生成的报表,如下图所示6. 用Excel 做数据分析——直方图根据考试成绩统计各分数段人数，并给出频数分布和累计频数表的直方图以供分析。
➊打开“素材\某中学期中考试化学成绩统计表2.xls ”工作簿。
Excel数据分析电子书➋在右侧输入“分数段”，也就是“数据接受序列”，是分段统计的数据间隔，该区域包含一组可选的用来定义接收区域的边界值。
这些值应当按升序排列。
在本实例中，就是以多少分数段作为统计的单元。
可采用拖动的方法生成，也可以按照需要自行设置。
本实例采用10 分一个分数统计单元。
如下图所示。
➌选择【工具】 【数据分析】 【直方图】菜单命令，弹出【相关系数】对话框，在输入选项中单击输入区域右侧按钮，选择B2：B58单元格，在接收区域选择E2：E13，单击选中【标志】选项。
在输出选项中选中输出区域，并单击右侧按钮，选择C14单元格，单击选中【柏拉图】，【累积百分率】，【图表输出】复选框。
设置效果如下图所示。
➍单击【确定】按钮即可看到生成的直方图,如下图所示。
➎为了能更直观的观察直观图，还需要对直方图做进一步的设置，现已Y轴为例，在Y轴单击鼠标右键或者直接双击鼠标，打开【坐标轴格式】对话框，在【刻度】选项卡下设置【最小值】为“0”，【最大值】为“25”，【主要刻度单位】为“2”,然后在字体选项卡下设置Y轴的字体、字号等，设置好参数后单击【确定】按钮。
➏依次按照上述方法分别设置直方图，最终效果如下图所示。
7.使用Excel做数据分析之回归分析方法在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等。
Excel 自带的拟合工具来对此类数据进行处理。
➊打开“素材\某溶液浓度正比对应于色谱仪器中的峰面积.xls”工作簿。
➋选择A1:B9单元格，选择【插入】 【图表】 【XY散点图】菜单命令。
单击【下一步】按钮，进行相关设置。
➌单击【完成】按钮，即可看到XY散点图，然后进行调整，效果如下。
➍在散点图中的点上单击鼠标右键，在弹出的快捷菜单中选择【添加趋势线】选项，弹出【添加趋势线】对话框，在【类型】选项卡下选择【线性】选项，在【选项】选项卡下，单击选中【显示公式】和【显示R平方值】，单击【确定】按钮。
➎效果如下图所示，由图中可知，拟合的直线是y=15620x+6606.1，R2 的值为0.9994。
因为R2>0.99，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其他未知浓度溶液的测量。
也可以使用数据分析中的“回归”工具来详细分析这组数据。
➊选择【工具】 【数据分析】 【回归】菜单命令，弹出【回归】对话框，在【输入】选项中，【Y值输入区域】选择B1:B9，【X值输入区域】选择A1:A9，单击选中【标志】和【常数为零】复选框，在【输出选项】选项中，选择【输出区域】为“D1”，在【残差】选项中单击选中所有复选框，在【正态分布】选项中单击选中【正态概率图】复选框，单击【确定】按钮。