目 录
用Excel做数据分析—移动平均 ...................... 2 用Excel做数据分析—抽样分析工具 .................. 4 实现Excel动态链接外部数据库 ...................... 8 用Excel做数据分析—相关系数与协方差 ............. 18 用Excel做数据排序的常用方法与技巧 ............... 20 Excel的数据分析—排位与百分比 ................... 24 用Excel做数据分析—描述统计 ..................... 27 用Excel做数据分析—直方图 ....................... 30 excel数据分析 ................................... 35 使用Excel做数据分析之回归分析方法 ............... 39 用单元格数据作为Excel工作簿名称 ................. 44
1
用Excel做数据分析—移动平均
某化工反应过程，每隔2分钟对系统测取一次压力数据。由于反应的特殊性，需要考察每8分钟的压力平均值，如果该压力平均值高于15MPa，则认为自属于该平均值计算范围内的第一个压力数据出现时进入反应阶段，请使用Excel给出反应阶段时间的区间。
移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均，并以此作为数据变化的趋势供分析人员参考。移动平均在生活中也不乏见，气象意义上的四季界定就是移动平均最好的应用。
注:本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
操作步骤
1.打开原始数据表格，制作本实例的原始数据要求单列，请确认数据的类型。本实例为压力随时间变化成对数据，在数据分析时仅采用压力数据列。
需要注意的是，因为平均值的求取需要一定的数据量，那么就要求原始数据量不少于求取平均值的个数，在Excel中规定数据量不少于4。
2
2.选择“工具”-“数据分析”-“直方图”后，出现属性设置框，依次选择: 输入区域:原始数据区域;如果有数据标签可以选择“标志位于第一行”; 输出区域:移动平均数值显示区域; 间隔:指定使用几组数据来得出平均值;
图表输出;原始数据和移动平均数值会以图表的形式来显示，以供比较;
标准误差:实际数据与预测数据(移动平均数据)的标准差，用以显示预测与实际值的差距。数字越小则表明预测情况越好。
3.输入完毕后，则可立即生成相应的数据和图表。
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尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘的支持下加载“数据分析库”。加载成功后，可以在工具的下拉菜单中看到“数据分析”选项。
操作步骤：
4
1. 打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。实例中显示的是学生学号。
2. 选择“工具”—“数据分析”—“抽样”后，出现对话框，依次选择：
输入区域：把原始总体数据放在此区域中，数据类型不限，数值型或者文本型均可；
5
抽样方法：有间隔和随机两种。间隔抽样需要输入周期间隔，输入区域中位于间隔点处的数值以及此后每一个间隔点处的数值将被复制到输出列中。当到达输入区域的末尾时，抽样将停止。(在本例题中没有采用)；随机抽样是指直接输入样本数，电脑自行进行抽样，不用受间隔的规律限制；
样本数：在此输入需要在输出列中显示需要抽取总体中数据的个数。每个数值是从输入区域中的随机位置上抽取出来的，请注意：任何数值都可以被多次抽取!所以抽样所得数据实际上会有可能小于所需数量。本文末尾给出了一种处理方法；
输出区域：在此输入对输出表左上角单元格的引用。所有数据均将写在该单元格下方的单列里。如果选择的是“周期”，则输出表中数值的个数等于输入区域中数值的个数除以“间隔”。如果选择的是“随机”，则输出表中数值的个数等于“样本数”；
3.然后单击确定就可以显示结果了(这是电脑自行随机抽样的结果)。
原始数据文档在这里下载>> 操作结果文档在这里下载>> 需要说明的情况：
6
由于随机抽样时总体中的每个数据都可以被多次抽取，所以在样本中的数据一般都会有重复现象，解决此问题有待于程序的完善。可以使用“筛选”功能对所得数据进行筛选。
选中样本数据列，依次执行“数据”-“筛选”-“高级筛选”，如下图所示。
最后的样本结果如下图所示，请您根据经验适当调整在数据样本选取时的数量设置，以使最终所得样本数量不少于所需数量。
7
实现Excel动态链接外部数据库
我们有时需要在Excel中调取其他数据库的数据，并且希望其他数据库数据改变时，Excel中调取的数据也随之动态改变。下面介绍在Excel中通过“新建数据库查询”(Microsoft Query)的方法来实现动态链接数据库。
您在Excel中第一次使用“新建数据库查询”查询数据时，如果系统未安装Microsoft Query，系统会提示您安装。
一、Excel链接vfp数据库
vfp是常用的数据库，先以此来介绍。假设从px.dbf表中取出相应学校的数据，已知学校的后5位代码，数据所在字段为x16，数据值为03行和06行相加，放入Excel单个单元格中。
步骤如下:
1、点击菜单“数据”—“导入外部数据”—“新建数据库查询”，出现“选择数据源”对话框，选择“Visual Foxpro Tables*”，按“确定”。如果您对SQL语句不是很熟悉的话，请使用默认的“查询向导”创建。
8
(图1)
2、在“configure connection”对话框中，按“Browse”按钮选择表px.dbf所在的路径，按“ok”键。
(图2)
3、在“查询向导—选择列”对话框中，选择查询结果中的列，要选取的数据在“x16”字段，所以将可用表“px”中的“x16”列移至“查询结果中的列”，按“下一步”。
9
(图3)
4、在“查询向导—筛选数据”对话框中，因为此例中设置的条件在字段学校代码“xxdm”和行号“bh”，非字段“x16”，所以点击“下一步”，暂时不筛选数据。
(图4)
5、在“查询向导—排序顺序”对话框中，因为此例中所取的是单个数据，所在点击“下一步”。
10
(图5)
6、出现“查询向导—完成”对话框，因还要对数据进行筛选，所以选择“在Microsoft Query中查看数据或编辑查询”，点击“完成”。
(图6)
7、进入“Microsoft Query”，看到查询结果x16列出了所有行的数据。 接下来要通过设置条件来筛选所需的数据。
点击菜单“条件”—“增加条件(A)...”，字段“bh”,运算符“等于”，指定值为“03”，按“添加”。
11
(图7)
继续“添加条件”对话框，选择“或”，字段“bh”,运算符“等于”，指定值为“06”，按“添加”。
(图8)
继续“添加条件”对话框，选择“与”，字段“xxdm”,运算符“等于”，指定值为“11201”，按“添加”后按“关闭”。
12
(图9)
但是看到符合条件x16为空，这是因为学校代码的后五位为“11201”。
可以通过“显示SQL”来直接修改条件语句,将SQL语句中px.xxdm=\改成right(px.xxdm,5)=\按“确定”。
(图10)
也可以在“条件字段”直接修改。
13
(图11)
光标放在x16字段，点击“循环总计”，将查询出的两个值相加，当然也可以直接在SQL语句中修改。
(图12)
14
8、关闭“Microsoft Query”，弹出“导入数据”对话框，选择“数据的放置位置”，选择“$B$2”，按“确定”。
>(图13)
9、但我们发现显示了标题行，真正的数据却显示到了$B$3，这时我们需要修改$B$2单元格的“数据区域属性”。点击$B$2单元格，按右键菜单的“数据区域属性”，弹出“外部数据区域属性”窗口，将“数据格式与布局”中“包含字段名”等前面的对勾去掉，将“数据区域中的行数随刷新而更改的方式”改成“用新数据覆盖现有单元格，并清除没有使用的单元格内容”，按“确定”。但还是有标题，没有关系，点击$B$2单元格，按右键菜单的“刷新数据”即可。
(图14)
15
10、如果px.dbf数据库有所改变，那么如何在Excel中刷新，可以点击链接数据所在单元格右键菜单中的“刷新”手工刷新;也可以在“数据区域属性”—“刷新控件”中选上“打开工作簿时，自动刷新”(见图14)，从而在每次打开工作簿时，在“查询刷新”的提示框中按“启动自动刷新”按钮刷新(见图15)。
(图15)
11、如果需要编辑查询，则点击$B$2单元格，按右键菜单的“查询编辑”，如果出现“查询向导”无法编辑此查询的提示，只要按“确定”即可进入“Microsoft Query”。
12、$B$3单元格查询条件与$B$2相差的只是学校代码，可以将$B$2单元格复制到$B$3单位格，再用$B$3的“编辑查询”来修改，修改一下学校代码的值即可。
16
(图16)
二、Excel链接SQL数据库
链接SQL数据库与链接vpf大致是相同的。这里介绍的是连接两表的查询。 点击菜单“数据”—“导入外部数据”—“新建数据库查询”，出现“选择数据源”对话框，可直接选取所需链接的SQL数据库名。
如果SQL数据库有密码的话，将提示输入密码，按“确定”。如果SQL SERVER服务管理器未开启的话，将出现错误提示。
如果不使用“查询向导”的话，可直接进入“Microsoft Query”。添加表news和表type，news表中的typeid和type表的typeid建立关联。在“条件字段”中选择字段名，并在“值”中输入条件值，这里输入的条件是type表的typeid字段值为1且news表的topic中含有\小学生\。点击选择要输出的字段名，这里选择的是type表中的typename字段和news表的topic字段。
(图17)
当然，如果您对SQL语句熟悉，也可以直接输入SQL语句。 接下来的步骤请参考一、中的8、至10、。
17
用Excel做数据分析—相关系数与协方差
化学合成实验中经常需要考察压力随温度的变化情况。某次实验在两个不同的反应器中进行同一条件下实验得到两组温度与压力相关数据，试分析它们与温度的关联关系，并对在不同反应器内进行同一条件下反应的可靠性给出依据。
相关系数是描述两个测量值变量之间的离散程度的指标。用于判断两个测量值变量的变化是否相关，即，一个变量的较大值是否与另一个变量的较大值相关联(正相关)；或者一个变量的较小值是否与另一个变量的较大值相关联(负相关)；还是两个变量中的值互不关联(相关系数近似于零)。设(X,Y)为二元随机变量，那么:
为随机变量X与Y的相关系数。p是度量随机变量X与Y之间线性相关密切程度的数字特征。
注:本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
操作步骤
1. 打开原始数据表格，制作本实例的原始数据需要满足两组或两组以上的数据，结果将给出其中任意两项的相关系数。
2. 选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择:
18
输入区域:选择数据区域，注意需要满足至少两组数据。如果有数据标志，注意同时勾选下方“标志位于第一行”；
分组方式:指示输入区域中的数据是按行还是按列考虑，请根据原数据格式选择； 输出区域可以选择本表、新工作表组或是新工作簿；
3.点击“确定”即可看到生成的报表。
可以看到，在相应区域生成了一个3×3的矩阵，数据项目的交叉处就是其相关系数。显然，数据与本身是完全相关的，相关系数在对角线上显示为1；两组数据间在矩阵上有两个位置，它们是相同的，故右上侧重复部分不显示数据。左下侧相应位置分别是温度与压力A、B和两组压力数据间的相关系数。
19
从数据统计结论可以看出，温度与压力A、B的相关性分别达到了0.95和0.94，这说明它们呈现良好的正相关性，而两组压力数据间的相关性达到了0.998，这说明在不同反应器内的相同条件下反应一致性很好，可以忽略因为更换反应器造成的系统误差。
协方差的统计与相关系数的活的方法相似，统计结果同样返回一个输出表和一个矩阵，分别表示每对测量值变量之间的相关系数和协方差。不同之处在于相关系数的取值在 -1 和 +1 之间，而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程度的指标。
用Excel做数据排序的常用方法与技巧
在用Excel制作相关的数据表格时，我们可以利用其强大的排序功能，浏览、查询、统计相关的数字。下面，我们以图1所示的“员工基本情况登记表”为例，来全面体验一番Excel的排序功能。
一、快速排序
20
如果我们希望对员工资料按某列属性(如“工龄”由长到短)进行排列，可以这样操作:选中“工龄”列任意一个单元格(如I3)，然后按一下“常用”工具栏上的“降序排序”按钮即可(参见图1)。
小提示:①如果按“常用”工具栏上的“升序排序”按钮，则将“工龄”由短到长进行排序。②如果排序的对象是中文字符，则按“汉语拼音”顺序排序。③如果排序的对象是西文字符，则按“西文字母”顺序排序。
二、多条件排序
如果我们需要按“学历、工龄、职称”对数据进行排序，可以这样操作:选中数据表格中任意一个单元格，执行“数据→排序”命令，打开“排序”对话框(图2)，将“主要关键词、次要关键词、第三关键词”分别设置为“学历、工龄、职称”，并设置好排序方式(“升序”或“降序”)，再按下“确定”按钮就行了。
三、按笔划排序
对“姓名”进行排序时，国人喜欢按“姓氏笔划”来进行:选中姓名列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框(参见图2)，单击其中的“选项”按钮，打开“排序选项”对话框(图3)，选中其中的“笔划排序”选项，确定返回到“排序”对话框，再按下“确定”按钮即可。
21
小提示:如果需要按某行属性对数据进行排序，我们只要在上述“排序选项”对话框中选中“按行排序”选项即可。
四、自定义排序
当我们对“职称”列进行排序时，无论是按“拼音”还是“笔划”，都不符合我们的要求。对于这个问题，我们可以通过自定义序列来进行排序:
先把相应的职称序列按需要排序的顺序输入到相应的单元格区域(如N2至N18)中(图4);执行“工具→选项”命令，打开“选项”对话框(图5)，切换到“自定义序列”标签下，在“从单元格中导入序列”右侧的方框中输入“$N$2:$N$18”(也可以用鼠标选择输入)，然后单击“导入”按钮，将相应的序列导入到系统中，确定返回。
22
小提示:序列导入后，原来N2至N18区域中输入的数据可以删除，导入的序列在其他Excel文档中均可直接使用。
选中“职称”列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框，单击其中的“选项”按钮，打开“排序选项”对话框(参见图3)，按“自定义排序次序”选项右侧的下拉按钮，在随后弹出的下拉列表中，选中上述“导入”的序列，确定返回到“排序”对话框，再按下“确定”按钮即可。
五、用函数进行排序
有时，我们对某些数值列(如“工龄、工资”等)进行排序时，不希望打乱表格原有数据的顺序，而只需要得到一个排列名次。对于这个问题，我们可以用函数来实现(以“工龄”为例):在“工龄”右侧插入一个空白列(J列)，用于保存次序(图6)，然后选中J2单元格，输入公式:=RANK(I2,$I$2:$I$101)，然后再次选中J2单元格，将鼠标移至该单元格右下角成“细十字线状”时(这种状态，我们通常称之为“填充柄”状态)，按住左键向下拖拉至最后一条数据为止，次序即刻显示出来(图6)。
23
本文图片来自网络
小提示:若要升序排序，可在公式最后增加一个“非零”参数，如将上述公式改为:=RANK(I2,$I$2:$I$101,1)。
六、让序号不参与排序
当我们对数据表进行排序操作后，通常位于第一列的序号也被打乱了，如何不让这个“序号”列参与排序呢?我们在“序号”列右侧插入一个空白列(B列)，将“序号”列与数据表隔开。用上述方法对右侧的数据区域进行排序时，“序号”列就不参与排序了。
小提示:插入的空列会影响表格的打印效果，我们可以将其隐藏起来:选中B列(即插入的空列)，右击鼠标，再选择“隐藏”选项即可。
Excel的数据分析—排位与百分比
某班级期中考试进行后，按照要求仅公布成绩，但学生及家长要求知道排名。故欲公布成绩排名，学生可以通过成绩查询到自己的排名，并同时得到该成绩位于班级百分比排名(即该同学是排名位于前“X%”的学生)。
排序操作是Excel的基本操作，Excel“数据分析”中的“排位与百分比排位”可以使这个工作简化，直接输出报表。
注:本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
操作步骤
24
1. 打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。
2. 选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择；
输入区域:选择数据区域，如果有数据标志，注意同时勾选下方“标志位于第一行”； 分组方式:指示输入区域中的数据是按行还是按列考虑，请根据原数据格式选择； 输出区域可以选择本表、新工作表组或是新工作簿。
3.点击“确定”即可看到生成的报表。
可以看到，此时生成一个四列的新表格，其中“点”是指排序后原数据的序数，在本实例中对应与学号，这也是很实用的一个序列；“成绩”即为排序后的数据系列；“排位”采取重复数据占用同一位置的统计方法；“百分比”是按照降序排列的，为了得到真正的“百分比排位”，还需要稍微作一下调整。
25
4.在“百分比”列的下一列输入“百分排名”，在第一个单元格中输入公式“=1-G3(对应于?百分排名?)”，回车。选中该单元格，向下拖动直至填充完毕。这样就达到了显示百分比排名的目的。
完成的报表实例如下图所示。
26
用Excel做数据分析—描述统计
某班级期中考试进行后，需要统计成绩的平均值、区间，以及给出班级内部学生成绩差异的量化标准，借此来作为解决班与班之间学生成绩的参差不齐的依据。要求得到标准差等统计数值。
样本数据分布区间、标准差等都是描述样本数据范围及波动大小的统计量，统计标准差需要得到样本均值，计算较为繁琐。这些都是描述样本数据的常用变量，使用Excel数据分析中的“描述统计”即可一次完成。
注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
操作步骤
1.打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。
27
2. 选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择： 输入区域：原始数据区域，可以选中多个行或列，注意选择相应的分组方式； 如果数据有标志，注意勾选“标志位于第一行”；如果输入区域没有标志项，该复选框将被清除，Excel 将在输出表中生成适宜的数据标志；
输出区域可以选择本表、新工作表或是新工作簿；
汇总统计：包括有平均值、标准误差(相对于平均值)、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大值、最小值和置信度等相关项目。
其中：
中值：排序后位于中间的数据的值； 众数：出现次数最多的值；
28
峰值：衡量数据分布起伏变化的指标，以正态分布为基准，比其平缓时值为正，反之则为负；
偏斜度：衡量数据峰值偏移的指数，根据峰值在均值左侧或者右侧分别为正值或负值；
极差：最大值与最小值的差。
第K大(小)值：输出表的某一行中包含每个数据区域中的第 k 个最大(小)值。 平均数置信度：数值 95% 可用来计算在显著性水平为 5% 时的平均值置信度。
结果示例如下(本实例演示了双列数据的描述统计结果)：
成绩
78.64285714
2.408241878
85 98 18.02163202
29
学习时间
62.91428571
1.926593502
68 78.4 14.41730562
平均 平均
标准误差 标准误差
中位数 众数
中位数 众数
标准差 标准差
方差
324.7792208
1.464424408
-1.130551511
85 15 100 4404 56 100 15 4.826224539
方差
207.8587013
1.464424408
-1.13055151
68 12 80 3523.2 56 80 12 3.860979631
峰度 峰度
偏度 偏度
区域 最小值 最大值 求和 观测数 最大 (1) 最小 (1) 置信度 (95.0%)
区域 最小值 最大值 求和 观测数 最大 (1) 最小 (1) 置信度 (95.0%)
用Excel做数据分析—直方图
使用Excel自带的数据分析功能可以完成很多专业软件才有的数据统计、分析，这其中包括：直方图、相关系数、协方差、各种概率分布、抽样与动态模拟、总体均值判断，均值推断、线性、非线性回归、多元回归分析、时间序列等内容。下面将对以上功能逐一作使用介绍，方便各位普通读者和相关专业人员参考使用。
注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数
据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。
30
实例1
某班级期中考试进行后，需要统计各分数段人数，并给出频数分布和累计频数表的直方图以供分析。
31
以往手工分析的步骤是先将各分数段的人数分别统计出来制成一张新的表格，再以此表格为基础建立数据统计直方图。使用Excel中的“数据分析”功能可以直接完成此任务。
操作步骤
1.打开原始数据表格，制作本实例的原始数据要求单列，确认数据的范围。本实例为化学成绩，故数据范围确定为0-100。
2.在右侧输入数据接受序列。所谓“数据接受序列”，就是分段统计的数据间隔，该区域包含一组可选的用来定义接收区域的边界值。这些值应当按升序排列。在本实例中，就是以多少分数段作为统计的单元。可采用拖动的方法生成，也可以按照需要自行设置。本实例采用10分一个分数统计单元。
3.选择“工具”-“数据分析”-“直方图”后，出现属性设置框，依次选择： 输入区域：原始数据区域; 接受区域：数据接受序列;
如果选择“输出区域”，则新对象直接插入当前表格中;
32
选中“柏拉图”，此复选框可在输出表中按降序来显示数据; 若选择“累计百分率”，则会在直方图上叠加累计频率曲线;
4.输入完毕后，则可立即生成相应的直方图，这张图还需要比较大的调整。
33
主要是：
横纵坐标的标题、柱型图的间隔以及各种数据的字体、字号等等。
为了达到柱型图之间无缝的紧密排列，需要将“数据系列格式”中的“选项”中“分类间距”调整为“0”。其余细节，请双击要调整的对象按照常规方法进行调整，这里不再赘述。
调整后的直方图参考如下
34
excel数据分析
公式
公式是Excel的核心功能之一，它使工作表具备“计算”能力，用户只需输入原始数据，进一步的计算用公式来实现，准确、快速又方便。公式由等号（=）、运算符（加（＋）、减（－）、乘（*）、除（／）、乘方（^）、括号（（）））、数等要素组成。
① 等号：这是公式的标志。若想对数值进行计算，必须在数值运算公式前加等号。 ② 操作符：表示执行哪种运算。 ③数：引用的单元格、函数或常数。
〖案例三〗用EXCEL公式计算《统计学原理》教材第22页表2—4中全地区从业总人数、全地区工业增加值总额、全地区上交税金总额（见统计学原理.XLS的?第二章第三节 统计表及其设计?工作表）。
计算步骤：
①在A10单元格输入?表2—4?，在B10单元格输入?某年某地区甲乙两地工业企业从业人数、工业增加值和税金?，将B10单元格到D10单元格合并，并居中和自动换行；
②在A11单元格输入?按地区分组?，在B11单元格输入?从业人数（万人）?，在C11单元格输入?工业增加值（万元）? ，在D11单元格输入?上交税金（万元）?；
③在A12单元格输入?（甲）?，在B12单元格输入?（1）?，在C12单元格输入?（2）? ，在D12单元格输入?（3）?；
④在A13单元格输入?甲地?，在B13单元格输入?11.47?，在C13单元格输入?216622.42?，在D13单元格输入?643.20?；在A14单元格输入?乙地?，在B14单元格输入?35.99?，在C14单元格输入?368573.59?，在D14单元格输入?9141.46?；
⑤在A15单元格输入?全地区?，在B15单元格输入?=B13+B14?，然后敲回车键，则B15单元格显示的结果为47.46；
⑥然后用鼠标左键点击一次B15单元格，再次将鼠标光标移动到该单元格的右下角，这时鼠标变成十字星，利用该填充功能将C15和D15单元格分别填充为?=C13+C14?和?=D13+D14?；
35
⑦将B13到D15单元格格式全部选为数值，小数位保留2位，然后敲回车键，则在B15单元格显示的结果为47.46，C15单元格显示的结果为585196.01，D15单元格显示的结果为9784.66；
⑧给各单元格绘制边框（见统计学原理.XLS的?第三节 统计表及其设计?工作表）。计算过程完成。
〖案例四〗用EXCEL公式计算《统计学原理》教材第22页表2—5中各地区和全地区每人平均工业增加值、每人平均上交税金（见统计学原理.XLS的?第二章第三节 统计表及其设计?工作表）。
计算步骤：
①将A10到D15单元格全部选中，然后点击复制，再用鼠标点击A18单元格，点击粘贴；将输入?某年某地区甲乙两地工业企业从业人数、工业增加值和税金?的单元格内容改写为?某年某地区甲乙两地工业企业人均增加值和税金?，再将B18单元格到D18单元格的合并撤消，在C18单元格内输入?单位：元?；
②将B19到B23单元格删除，并将右边单元格左移；
③在新的B20单元格内输入?（1）?，在B21单元格内输入?=C13/B13?，然后敲回车键，再利用填充功能将B21单元格内的公式填充到B22和B23单元格中；
④在C20单元格内输入?（2）?，在C21单元格内输入?=D13/B13?，然后敲回车键，再利用填充功能将C21单元格内的公式填充到C22和C23单元格中；
⑤设定各单元格数值格式和绘制边框（见统计学原理.XLS的?第三节 统计表及其设计?工作表）。计算过程完成。
〖案例五〗用EXCEL公式计算《统计学原理》教材第40页表3—4中销售收入各组的企业比重、销售收入的上限、下限、组中值、组距（见统计学原理.XLS的?第三章第三节次数分布?工作表）。
计算步骤：
①用鼠标右键点击统计学原理.XLS?第二章第三节统计表及其设计?工作表标签，将它复制为统计学原理.XLS?第二章第三节统计表及其设计（2）?工作表，再将该工作表标签改写成?第三章第三节次数分布?工作表。
②将A1单元格改写成?表3—4?，将B1单元格改写成?某地区商业企业销售收入统计表?；将A2单元格改写成?销售收入（万元）?，将B2单元格改写成?企业数（个）?；将第3行删除；在A3单元格内输入80~90，在A4单元格内输入90~100。在第5行处插入一行，然后，在A5单元格内输入100~110，在A6单元格内输入110~120。
③在B3到B7单元格内分别输入2、4、16、6、=B3+B4+B5+B6，然后敲回车键，再利用填充功能将B7单元格内的公式填充到C7单元格中。再将C4单元格内的公式填充到C5单元格中。这时，销售收入各组的企业比重数就计算出来了。
④在D2单元格内输入?下限?，在E2单元格内输入?上限?，在F2单元格内输入?组中值?，在G2单元格内输入?组距?，然后在F3单元格内输入?=(D3+E3)/2?，在G3单元格内输入?=E3-D3?，敲回车键，再利用填充功能将F3和G3单元格内的公式填充到F4到G6单元格内。
⑤在D3到E4单元格内依次输入80、90、90、100，然后将D3到E4单元格选中，再利用填充功能将D3到E4内有规律的数字遵循其规律填充到D5到E6单元格内。这时，销售收入的上限、下限、组中值、组距就计算出来了。
⑥设定各单元格数值格式和绘制边框（见统计学原理.XLS的?第三章第三节次数分布?工作表）。计算过程完成。
五、绘制曲线图
36
㈠绘制直方图
〖案例六〗用EXCEL图表功能绘制《统计学原理》教材第43页表3—4中销售收入各组的直方图（见统计学原理.XLS的?第三章第三节次数分布?工作表）。
绘制步骤：
①在A1到B13单元格内创建表5—1（1），在A2到B12单元格内输入相关数据。 ②点击命令条中的图表（H）图标，然后选择柱形图，按着操作步骤，就可以绘制出表3—4中销售收入各组的直方图（见统计学原理.XLS的?第三章第三节次数分布?工作表）。
第二章 EXCEL函数 第一节 平均指标类函数 一、算术平均数
〖案例七〗用EXCEL函数计算《统计学原理》教材第64页〔例5-1〕工人平均日产量（见统计学原理.XLS的?第五章平均指标和变异指标?工作表）。
计算步骤：
①在A1到B13单元格内创建表5—1（1），在A2到B12单元格内输入相关数据，在A13单元格内输入?平均?。
②点击B13单元格，然后再点击命令条中的函数（F）图标，选择函数分类中的统计，在函数名中选?AVERAGE?（见图2—1—1），点击确定，出现图2—1—2。
③点击图2—1—2确定按钮，计算完成。 二、调和平均数
〖案例八〗用EXCEL函数计算《统计学原理》教材第69页〔例5-4〕三批产品的平均价格（见统计学原理.XLS的?第五章平均指标和变异指标?工作表）。
计算步骤：
①在A16到DB21单元格内创建表5—3，在A22单元格内输入?平均?。
②点击B22单元格，然后再点击命令条中的函数（F）图标，选择函数分类中的数学与三角函数，在函数名中选?SUM?，在Number1对话框内内输入C18：C20，点击确定。然后，将B22单元格内的计算公式改写成?=SUM（C18：C20）/SUM（D18：D20）?。计算过程结束。
第二节 变异指标类函数
一、标准差
〖案例九〗用EXCEL函数计算《统计学原理》教材第82页〔例5-13〕工人日产量零件数的标准差（见统计学原理.XLS的?第五章平均指标和变异指标?工作表）。
计算步骤：
①在A25到A38单元格内创建表5—15，在A39单元格内输入?标准差?。
②点击A40单元格，然后再点击命令条中的函数（F）图标，选择函数分类中的统计函数，在函数名中选?STDEVP?，在Number1对话框内内输入A29：A38，点击确定。计算过程结束。
也可以采用EXCEL数据分析工具计算。 计算步骤：
①在A25到A38单元格内创建表5—15，在B39单元格内输入?标准差?。
②点击A41单元格，然后再点击命令条中的工具（F）图标，选择数据分析（D）（见图2—2—1），在数据分析的分析工具（A）对话框内将?描述统计?中选，点击确定（见图2—2—2）。
37
③在描述统计的输入区域对话框内输入$A$28：$A$38，分组方式选择?逐列?，将标志位于第一行选中。在输出选项的输出区域对话框内输入$A$41，再将汇总统计选中，点击确定。
④在B40单元格内输入?=B47*SQRT((COUNT(A29:A38)-1)/COUNT(A29:A38))?，敲回车键。计算过程结束（见图2—2—3）。
第三节 平均发展速度类函数 一、乘幂函数 power（底,指数）
〖案例十〗用EXCEL乘幂函数计算《统计学原理》教材第99页〔例6-6〕1985—2004年间平均发展速度（见统计学原理.XLS的?第六章动态数列?工作表）。
计算步骤：
①在A1到B4单元格内创建表6—1（1），在C2单元格内输入?平均发展速度?。
②在C3单元格，输入?=POWER(B4/B3,1/(A4-A3))?，然后，敲回车键。计算过程结束。 二、几何平均函数
GEOMEAN（数值区域，数值区域，…）
〖案例十一〗用EXCEL几何平均数函数计算《统计学原理》教材第100页〔例6-7〕1999—2004年的平均发展速度（见统计学原理.XLS的?第六章动态数列?工作表）。
计算步骤：
①在A7到G11单元格内创建表6—7（1），在A11单元格内输入?平均发展速度?。 ②在B11单元格输入?=GEOMEAN(C10:G10)?，然后，敲回车键。计算过程结束。 第四节 最小平方法类函数 一、截距函数
INTERCEPT（因变量数据单元格区域, 自变量数据单元格区域） 〖案例十一〗用EXCEL截距函数计算《统计学原理》教材第107页〔例6-12〕1991—2000年间某地区粮食产量的直线趋势方程常数项a（见统计学原理.XLS的?第六章动态数列?工作表）。
计算步骤：
①在A14到C25单元格内创建表6—14（1），在A27单元格内输入?直线趋势方程常数项?。
②在B27单元格输入?=INTERCEPT(C16:C25,B16:B25)?，然后，敲回车键。计算过程结束。
二、斜率函数
SLOPE（因变量数据单元格区域, 自变量数据单元格区域） 〖案例十二〗用EXCEL截距函数计算《统计学原理》教材第107页〔例6-12〕1991—2000年间某地区粮食产量的直线趋势方程系数b（见统计学原理.XLS的?第六章动态数列?工作表）。
计算步骤：
①在A14到C25单元格内创建表6—14（1），在A28单元格内输入?直线趋势方程系数?。
②在B28单元格输入?=SLOPE(C16:C25,B16:B25)?，然后，敲回车键。计算过程结束。 第三章 EXCEL相关与回归分析 第一节 EXCEL相关分析 一、绘制散点图
38
〖案例十三〗用EXCEL图表功能绘制《统计学原理》教材第158页散点图（见统计学原理.XLS的?第十章相关与回归分析?工作表）。
绘制步骤：
①在A1到B12单元格内创建表9—1。 ②点击插入命令条中的图表图标，然后选择散点图，点击下一步,按照图表向导—4步骤就可以绘制出散点（见统计学原理.XLS的?第十章相关与回归分析?工作表）。
二、计算相关系数
〖案例十四〗用EXCEL数据分析功能计算《统计学原理》教材第160页〔例9-1〕生产总值（亿元）与社会商品零售总额（亿元）的相关系数（见统计学原理.XLS的?见统计学原理.XLS的?第十章相关与回归分析?工作表）。
计算步骤：
①在A19到C27单元格内创建表9—4。
②点击工具命令条中的数据分析，在数据分析对话框内选择相关系数，点击确定。在相关系数对话框内的输入区域对话框输入$B$20：$C$27，在分组方式中选择?逐行?，将标志位于第一行选中，在输出区域对话框内输入$A$28，点击确定（见统计学原理.XLS的?第十章相关与回归分析?工作表），计算过程结束。
第二节 EXCEL回归分析 一、建立一元线性回归方程
〖案例十五〗用EXCEL数据分析功能建立《统计学原理》教材第164〔例9-2〕一元线性回归方程（见统计学原理.XLS的?第五章平均指标和变异指标?工作表）。
建立一元线性回归方程步骤：
①在A33到A41单元格内创建表9—7（1），在A42单元格内输入?建立一元线性回归方程?。
②点击A43单元格，然后再点击工具命令条中的数据分析，在数据分析对话框内选择回归，点击确定。在回归对话框内的Y值输入区域对话框输入$B$34：$B$41，X值输入区域对话框输入$A$34：$A$41，将标志选中，在输出选项对话框输出区域内输入$A$42（见图3—2—1），点击确定（见图3—2—2）。
③建立的财政收入y关于增加值x一元线性回归方程：
建立一元线性回归方程过程结束（见统计学原理.XLS的?第十章相关与回归分析?工作表）。
使用Excel做数据分析之回归分析方法
在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。
注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘支持下加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项
39
实例 某溶液浓度正比对应于色谱仪器中的峰面积，现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。已知8组对应数据，建立标准曲线，并且对此曲线进行评价，给出残差等分析数据。
这是一个很典型的线性拟合问题，手工计算就是采用最小二乘法求出拟合直线的待定参数，同时可以得出R的值，也就是相关系数的大小。在Excel中，可以采用先绘图再添加趋势线的方法完成前两步的要求。
选择成对的数据列，将它们使用“X、Y散点图”制成散点图。
X、Y散点图
在数据点上单击右键，选择“添加趋势线”-“线性”，并在选项标签中要求给出公式和相关系数等，可以得到拟合的直线。
40
给出公式和相关系数
由图中可知，拟合的直线是y=15620x+6606.1，R2的值为0.9994。
因为R2 >0.99，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其他未知浓度溶液的测量。
为了进一步使用更多的指标来描述这一个模型，我们使用数据分析中的“回归”工具来详细分析这组数据。
41
回归工具
在选项卡中显然详细多了，注意选择X、Y对应的数据列。“常数为零”就是指明该模型是严格的正比例模型，本例确实是这样，因为在浓度为零时相应峰面积肯定为零。先前得出的回归方程虽然拟合程度相当高，但是在x=0时，仍然有对应的数值，这显然是一个可笑的结论。所以我们选择“常数为零”。
“回归”工具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。
42
残差图、线性拟合图和正态概率图
在线性拟合图中可以看到，不但有根据要求生成的数据点，而且还有经过拟和处理的预测数据点，拟合直线的参数会在数据表格中详细显示。本实例旨在提供更多信息以起到抛砖引玉的作用，由于涉及到过多的专业术语，请各位读者根据实际，在具体使用中另行参考各项参数，此不再对更多细节作进一步解释。
残差图是有关于世纪之与预测值之间差距的图表，如果残差图中的散点在中州上下两侧零乱分布，那么拟合直线就是合理的，否则就需要重新处理。
图表
更多的信息在生成的表格中，详细的参数项目完全可以满足回归分析的各项要求。下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。
43
用单元格数据作为Excel工作簿名称
1、启动Excel，执行“工具→宏→Visual Basic 编辑器”命令，进入VBA编辑状态(如图1)。
图1 VBA编辑
2、在左侧的“工程资源管理器”窗口中，选中“VBAproject(PERSONAL.XLS)”(个人宏工作簿)选项。
3、执行“插入→模块”命令，插入一个模块(模块1)。
44
4、将下述代码输入到右侧的代码编辑窗口中： Sub baocun()
lj = InputBox(\请输入文档保存路径\
ActiveWorkbook.SaveAs Filename:=lj & ActiveCell.Value & \ End Sub
退出VBA编辑状态。
5、以后要保存某个工作簿文档时，先选中作为名称的字符所在的单元格(参见图2)，然后执行“工具→宏→宏”命令，打开“宏”对话框(如图3)。
图2 单元格
ER-BOTTOM-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-RIGHT-COLOR: #000000\alt=教你用单元格数据作为
Excel
工作簿名称
src=\
图3 宏对话框
6、选中刚才编辑的宏(PERSONAL.XLS!baocun)，单击“执行”按钮，系统弹出如图4所示的对话框。
45
图4 对话框
7、输入保存文档的路径(如“E:\\office技巧\\”)，单击“确定”按钮。 文档保存成功(参见图5)。
图5 保存成功
注意：如果不需要保存路径，文档将被保存到“我的文档”文件夹中。
46
excel数据分析教程
