1

    目 录

    用 Excel 做数据分析 — 移动平均 .............................................. .............................................. .............................................. .............................................. 2

    用 Excel 做数据分析 — 抽样分析工具 ...................................... ...................................... ...................................... ...................................... 4

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

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

    用 Excel 做数据排序的常用方法与技巧 ................................ ................................ ................................ ................................ 20

    Excel 的数据分析 — 排位与百分比 ......................................... ......................................... ......................................... ......................................... 24

    用 Excel 做数据分析 — 描述统计 ............................................ ............................................ ............................................ ............................................ 27

    用 Excel 做数据分析 — 直方图 ................................................ ................................................ ................................................ ................................................ 30

    excel 数据分析 ......................................................................... ......................................................................... ......................................................................... ......................................................................... 35

    使用 Excel 做数据分析之回归分析方法 ................................ ................................ ................................ ................................ 39

    用单元格数据作为 Excel 工作簿名称 .................................... .................................... .................................... .................................... 442

    用 用 用 用 Excel Excel Excel Excel 做数据分析 做数据分析 做数据分析 做数据分析 — — — — 移动平均 移动平均 移动平均 移动平均

    某化工反应过程 ， 每隔 2 分钟对系统测取一次压力数据 。 由于反应的特殊性 ， 需要考察

    每 8 分钟的压力平均值，如果该压力平均值高于 15MPa ，则认为自属于该平均值计算范围

    内的第一个压力数据出现时进入反应阶段，请使用 Excel 给出反应阶段时间的区间。

    移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均 ， 并以此作为

    数据变化的趋势供分析人员参考 。 移动平均在生活中也不乏见 ， 气象意义上的四季界定就是

    移动平均最好的应用。

    注 : 本功能需要使用 Excel 扩展功能 ， 如果您的 Excel 尚未安装数据分析 ， 请依次选择 “ 工

    具 ” - “ 加载宏 ” ，在安装光盘支持下加载 “ 分析数据库 ” 。加载成功后，可以在 “ 工具 ” 下拉菜单

    中看到 “ 数据分析 ” 选项。

    操作步骤

    1. 打开原始数据表格 ， 制作本实例的原始数据要求单列 ， 请确认数据的类型 。 本实

    例为压力随时间变化成对数据，在数据分析时仅采用压力数据列。

    需要注意的是 ， 因为平均值的求取需要一定的数据量 ， 那么就要求原始数据量不少

    于求取平均值的个数，在 Excel 中规定数据量不少于 4 。3

    2. 选择 “ 工具 ” - “ 数据分析 ” - “ 直方图 ” 后，出现属性设置框，依次选择 :

    输入区域 : 原始数据区域 ; 如果有数据标签可以选择 “ 标志位于第一行 ” ;

    输出区域 : 移动平均数值显示区域 ;

    间隔 : 指定使用几组数据来得出平均值 ;

    图表输出 ; 原始数据和移动平均数值会以图表的形式来显示，以供比较 ;

    标准误差 : 实际数据与预测数据 ( 移动平均数据 ) 的标准差 ， 用以显示预测与实际值的

    差距。数字越小则表明预测情况越好。

    3. 输入完毕后，则可立即生成相应的数据和图表。4

    从生成的图表上可以看出很多信息。

    根据要求 ， 生成的移动平均数值在 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 Excel Excel Excel 做数据分析 做数据分析 做数据分析 做数据分析 — — — — 抽样分析工具 抽样分析工具 抽样分析工具 抽样分析工具

    省教育厅派专家组进行某校检查学生考试试卷 ， 专家组拟对总体进行抽样调查 ， 对学校

    某班的全体同学随机抽取 25 名作为调查样本。为了保证结果的非人为性，采用 Excel 帮助

    专家组做出抽查的结果。

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

    行处理或绘制时，可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的 ，还可以对一个周期中特定时间段中的数值进行采样 。 也可以采用随机抽样 ， 满足用户保证抽

    样的代表性的要求。

    注：本功能需要使用 Excel 扩展功能，如果您的 Excel 尚未安装数据分析，请依次选择

    “ 工具 ” - “ 加载宏 ” ， 在安装光盘的支持下加载 “ 数据分析库 ” 。 加载成功后 ， 可以在工具的下拉

    菜单中看到 “ 数据分析 ” 选项。

    操作步骤：5

    1. 打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为

    同一属性数值即可。实例中显示的是学生学号。

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

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

    抽样方法 ： 有间隔和随机两种 。 间隔抽样需要输入周期间隔 ， 输入区域中位于间隔

    点处的数值以及此后每一个间隔点处的数值将被复制到输出列中。当到达输入区域的末尾

    时 ， 抽样将停止 。 ( 在本例题中没有采用 ) ； 随机抽样是指直接输入样本数 ， 电脑自行进行抽

    样，不用受间隔的规律限制；

    样本数 ： 在此输入需要在输出列中显示需要抽取总体中数据的个数 。 每个数值是从

    输入区域中的随机位置上抽取出来的，请注意：任何数值都可以被多次抽取 ! 所以抽样所得

    数据实际上会有可能小于所需数量。本文末尾给出了一种处理方法；

    输出区域 ： 在此输入对输出表左上角单元格的引用 。 所有数据均将写在该单元格下

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

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

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

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

    需要说明的情况：7

    由于随机抽样时总体中的每个数据都可以被多次抽取 ， 所以在样本中的数据一般都

    会有重复现象，解决此问题有待于程序的完善。可以使用 “ 筛选 ” 功能对所得数据进行筛选 。

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

    最后的样本结果如下图所示，请您根据经验适当调整在数据样本选取时的数量设

    置，以使最终所得样本数量不少于所需数量。8

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

    我们有时需要在 Excel 中调取其他数据库的数据，并且希望其他数据库数据改变时，Excel 中调取的数据也随之动态改变。下面介绍在 Excel 中通过 “ 新建数据库查询 ” (Microsoft

    Query) 的方法来实现动态链接数据库。

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

    Query ，系统会提示您安装。

    一、 Excel Excel Excel Excel 链接 vfp vfp vfp vfp 数据库

    vfp 是常用的数据库 ， 先以此来介绍 。 假设从 px.dbf 表中取出相应学校的数据 ， 已知学

    校的后 5 位代码，数据所在字段为 x16 ，数据值为 03 行和 06 行相加，放入 Excel 单个单元

    格中。

    步骤如下 :

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

    框，选择 “ Visual Foxpro Tables ” ，按 “ 确定 ” 。如果您对 SQL 语句不是很熟悉的话，请使用

    默认的 “ 查询向导 ” 创建。9

    ( 图 1)

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

    ( 图 2)

    3 、 在 “ 查询向导 — 选择列 ” 对话框中 ， 选择查询结果中的列 ， 要选取的数据在 “ x16 ”

    字段，所以将可用表 “ px ” 中的 “ x16 ” 列移至 “ 查询结果中的列 ” ，按 “ 下一步 ” 。10

    ( 图 3)

    4 、在 “ 查询向导 — 筛选数据 ” 对话框中，因为此例中设置的条件在字段学校代码

    “ xxdm ” 和行号 “ bh ” ，非字段 “ x16 ” ，所以点击 “ 下一步 ” ，暂时不筛选数据。

    ( 图 4)

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

    一步 ” 。11

    ( 图 5)

    6 、 出现 “ 查询向导 — 完成 ” 对话框 ， 因还要对数据进行筛选 ， 所以选择 “ 在 Microsoft

    Query 中查看数据或编辑查询 ” ，点击 “ 完成 ” 。

    ( 图 6)

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

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

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

    加 ” 。12

    ( 图 7)

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

    加 ” 。

    ( 图 8)

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

    ( 图 9)

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

    可以通过 “ 显示 SQL ” 来直接修改条件语句 , 将 SQL 语句中 px.xxdm=11201 改 成

    right(px.xxdm,5)=11201, 按 “ 确定 ” 。

    ( 图 10)

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

    ( 图 11)

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

    SQL 语句中修改。

    ( 图 12)15

    8 、 关闭 “ Microsoft Query ” ， 弹出 “ 导入数据 ” 对话框 ， 选择 “ 数据的放置位置 ” ， 选择

    “ B2 ” ，按 “ 确定 ” 。

    >

    ( 图 13)

    9 、 但我们发现显示了标题行 ， 真正的数据却显示到了 B3 ， 这时我们需要修改 B 2

    单元格的 “ 数据区域属性 ” 。 点击 B2 单元格 ， 按右键菜单的 “ 数据区域属性 ” ， 弹出 “ 外部数

    据区域属性 ” 窗口 ， 将 “ 数据格式与布局 ” 中 “ 包含字段名 ” 等前面的对勾去掉 ， 将 “ 数据区域中

    的行数随刷新而更改的方式 ” 改成 “ 用新数据覆盖现有单元格，并清除没有使用的单元格内

    容 ” ， 按 “ 确定 ” 。 但还是有标题 ， 没有关系 ， 点击 B2 单元格 ， 按右键菜单的 “ 刷新数据 ” 即

    可。

    ( 图 14)16

    10 、 如果 px.dbf 数据库有所改变 ， 那么如何在 Excel 中刷新 ， 可以点击链接数据所

    在单元格右键菜单中的 “ 刷新 ” 手工刷新 ; 也可以在 “ 数据区域属性 ”—“ 刷新控件 ” 中选上 “ 打开

    工作簿时 ， 自动刷新 ” ( 见图 14) ， 从而在每次打开工作簿时 ， 在 “ 查询刷新 ” 的提示框中按 “ 启

    动自动刷新 ” 按钮刷新 ( 见图 15) 。

    ( 图 15)

    11 、如果需要编辑查询，则点击 B2 单元格，按右键菜单的 “ 查询编辑 ” ，如果出

    现 “ 查询向导 ” 无法编辑此查询的提示，只要按 “ 确定 ” 即可进入 “ Microsoft Query ” 。

    12 、 B3 单元格查询条件与 B2 相差的只是学校代码，可以将 B2 单元格复制

    到 B3 单位格，再用 B3 的 “ 编辑查询 ” 来修改，修改一下学校代码的值即可。17

    ( 图 16)

    二、 Excel Excel Excel Excel 链接 SQL SQL SQL SQL 数据库

    链接 SQL 数据库与链接 vpf 大致是相同的。这里介绍的是连接两表的查询。

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

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

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

    如果不使用 “ 查询向导 ” 的话 ， 可直接进入 “ Microsoft Query ” 。 添加表 news 和表 type ，news 表中的 typeid 和 type 表的 typeid 建立关联。在 “ 条件字段 ” 中选择字段名，并在 “ 值 ” 中

    输入条件值 ， 这里输入的条件是 type 表的 typeid 字段值为 1 且 news 表的 topic 中含有 小学

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

    字段。

    ( 图 17)

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

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

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

    化学合成实验中经常需要考察压力随温度的变化情况 。 某次实验在两个不同的反应器中

    进行同一条件下实验得到两组温度与压力相关数据 ， 试分析它们与温度的关联关系 ， 并对在

    不同反应器内进行同一条件下反应的可靠性给出依据。

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

    的变化是否相关 ， 即 ， 一个变量的较大值是否与另一个变量的较大值相关联 ( 正相关 ) ； 或者

    一个变量的较小值是否与另一个变量的较大值相关联 ( 负相关 ) ； 还是两个变量中的值互不关

    联 ( 相关系数近似于零 ) 。设 (X,Y) 为二元随机变量，那么 :

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

    的数字特征。

    注 : 本功能需要使用 Excel 扩展功能 ， 如果您的 Excel 尚未安装数据分析 ， 请依次选

    择 “ 工具 ” - “ 加载宏 ” ， 在安装光盘中加载 “ 分析数据库 ” 。 加载成功后 ， 可以在 “ 工具 ” 下拉菜单

    中看到 “ 数据分析 ” 选项。

    操作步骤

    1. 打开原始数据表格，制作本实例的原始数据需要满足两组或两组以上的数据，结果将给出其中任意两项的相关系数。

    2. 选择 “ 工具 ” - “ 数据分析 ” - “ 描述统计 ” 后，出现属性设置框，依次选择 :19

    输入区域 : 选择数据区域，注意需要满足至少两组数据。如果有数据标志，注意同

    时勾选下方 “ 标志位于第一行 ” ；

    分组方式 : 指示输入区域中的数据是按行还是按列考虑，请根据原数据格式选择；

    输出区域可以选择本表、新工作表组或是新工作簿；

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

    可以看到，在相应区域生成了一个 3 × 3 的矩阵，数据项目的交叉处就是其相关系

    数 。 显然 ， 数据与本身是完全相关的 ， 相关系数在对角线上显示为 1 ； 两组数据间在矩阵上

    有两个位置 ， 它们是相同的 ， 故右上侧重复部分不显示数据 。 左下侧相应位置分别是温度与

    压力 A 、 B 和两组压力数据间的相关系数。20

    从数据统计结论可以看出，温度与压力 A 、 B 的相关性分别达到了 0.95 和 0.94 ，这说明它们呈现良好的正相关性，而两组压力数据间的相关性达到了 0.998 ，这说明在不同

    反应器内的相同条件下反应一致性很好，可以忽略因为更换反应器造成的系统误差。

    协方差的统计与相关系数的活的方法相似 ， 统计结果同样返回一个输出表和一个矩

    阵 ， 分别表示每对测量值变量之间的相关系数和协方差 。 不同之处在于相关系数的取值在 -1

    和 +1 之间，而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程

    度的指标。

    用 用 用 用 Excel Excel Excel Excel 做数据排序的常用方法与技巧 做数据排序的常用方法与技巧 做数据排序的常用方法与技巧 做数据排序的常用方法与技巧

    在用 Excel 制作相关的数据表格时，我们可以利用其强大的排序功能，浏览、查询 、 统

    计相关的数字 。 下面 ， 我们以图 1 所示的 “ 员工基本情况登记表 ” 为例 ， 来全面体验一番 Exce l

    的排序功能。

    一、快速排序21

    如果我们希望对员工资料按某列属性 ( 如 “ 工龄 ” 由长到短 ) 进行排列 ， 可以这样操作 : 选中

    “ 工龄 ” 列任意一个单元格 ( 如 I3) ， 然后按一下 “ 常用 ” 工具栏上的 “ 降序排序 ” 按钮即可 ( 参见 图

    1) 。

    小提示 : ① 如果按 “ 常用 ” 工具栏上的 “ 升序排序 ” 按钮，则将 “ 工龄 ” 由短到长进行排

    序 。 ② 如果排序的对象是中文字符 ， 则按 “ 汉语拼音 ” 顺序排序 。 ③ 如果排序的对象是西文字

    符，则按 “ 西文字母 ” 顺序排序。

    二、多条件排序

    如果我们需要按 “ 学历、工龄、职称 ” 对数据进行排序，可以这样操作 : 选中数据表

    格中任意一个单元格，执行 “ 数据 → 排序 ” 命令，打开 “ 排序 ” 对话框 ( 图 2) ，将 “ 主要关键词 、次要关键词 、 第三关键词 ” 分别设置为 “ 学历 、 工龄 、 职称 ” ， 并设置好排序方式 ( “ 升序 ” 或 “ 降

    序 ” ) ，再按下 “ 确定 ” 按钮就行了。

    三、按笔划排序

    对 “ 姓名 ” 进行排序时 ， 国人喜欢按 “ 姓氏笔划 ” 来进行 : 选中姓名列任意一个单元格 ，执行 “ 数据 → 排序 ” 命令，打开 “ 排序 ” 对话框 ( 参见图 2) ，单击其中的 “ 选项 ” 按钮，打开 “ 排序

    选项 ” 对话框 ( 图 3) ，选中其中的 “ 笔划排序 ” 选项，确定返回到 “ 排序 ” 对话框，再按下 “ 确定 ”

    按钮即可。22

    小提示 : 如果需要按某行属性对数据进行排序，我们只要在上述 “ 排序选项 ” 对话框

    中选中 “ 按行排序 ” 选项即可。

    四、自定义排序

    当我们对 “ 职称 ” 列进行排序时 ， 无论是按 “ 拼音 ” 还是 “ 笔划 ” ， 都不符合我们的要求 。

    对于这个问题，我们可以通过自定义序列来进行排序 :

    先把相应的职称序列按需要排序的顺序输入到相应的单元格区域 ( 如 N2 至 N18) 中

    ( 图 4); 执行 “ 工具 → 选项 ” 命令 ， 打开 “ 选项 ” 对话框 ( 图 5) ， 切换到 “ 自定义序列 ” 标签下 ， 在 “ 从

    单元格中导入序列 ” 右侧的方框中输入 “ N2:N18 ” ( 也可以用鼠标选择输入 ) ， 然后单击 “ 导

    入 ” 按钮，将相应的序列导入到系统中，确定返回。23

    小提示 : 序列导入后，原来 N2 至 N18 区域中输入的数据可以删除，导入的序列在

    其他 Excel 文档中均可直接使用。

    选中 “ 职称 ” 列任意一个单元格 ， 执行 “ 数据 → 排序 ” 命令 ， 打开 “ 排序 ” 对话框 ， 单击

    其中的 “ 选项 ” 按钮，打开 “ 排序选项 ” 对话框 ( 参见图 3) ，按 “ 自定义排序次序 ” 选项右侧的下

    拉按钮，在随后弹出的下拉列表中，选中上述 “ 导入 ” 的序列，确定返回到 “ 排序 ” 对话框 ， 再

    按下 “ 确定 ” 按钮即可。

    五、用函数进行排序

    有时，我们对某些数值列 ( 如 “ 工龄、工资 ” 等 ) 进行排序时，不希望打乱表格原有数

    据的顺序，而只需要得到一个排列名次。对于这个问题，我们可以用函数来实现 ( 以 “ 工龄 ”

    为例 ): 在 “ 工龄 ” 右侧插入一个空白列 (J 列 ) ， 用于保存次序 ( 图 6) ， 然后选中 J2 单元格 ， 输入

    公式 :=RANK(I2,I2:I101) ，然后再次选中 J2 单元格，将鼠标移至该单元格右下角成 “ 细

    十字线状 ” 时 ( 这种状态 ， 我们通常称之为 “ 填充柄 ” 状态 ) ， 按住左键向下拖拉至最后一条数据

    为止，次序即刻显示出来 ( 图 6) 。24

    本文图片来自网络

    小提示 : 若要升序排序，可在公式最后增加一个 “ 非零 ” 参数，如将上述公式改

    为 :=RANK(I2,I2:I101,1) 。

    六、让序号不参与排序

    当我们对数据表进行排序操作后 ， 通常位于第一列的序号也被打乱了 ， 如何不让这

    个 “ 序号 ” 列参与排序呢 ? 我们在 “ 序号 ” 列右侧插入一个空白列 (B 列 ) ， 将 “ 序号 ” 列与数据表隔

    开。用上述方法对右侧的数据区域进行排序时， “ 序号 ” 列就不参与排序了。

    小提示 : 插入的空列会影响表格的打印效果，我们可以将其隐藏起来 : 选中 B 列 ( 即

    插入的空列 ) ，右击鼠标，再选择 “ 隐藏 ” 选项即可。

    Excel Excel Excel Excel 的数据分析 的数据分析 的数据分析 的数据分析 — — — — 排位与百分比 排位与百分比 排位与百分比 排位与百分比

    某班级期中考试进行后 ， 按照要求仅公布成绩 ， 但学生及家长要求知道排名 。 故欲公布

    成绩排名 ， 学生可以通过成绩查询到自己的排名 ， 并同时得到该成绩位于班级百分比排名 ( 即

    该同学是排名位于前 “ X% ” 的学生 ) 。

    排序操作是 Excel 的基本操作 ， Excel “ 数据分析 ” 中的 “ 排位与百分比排位 ” 可以使这

    个工作简化，直接输出报表。

    注 : 本功能需要使用 Excel 扩展功能 ， 如果您的 Excel 尚未安装数据分析 ， 请依次选择 “ 工

    具 ” - “ 加载宏 ” ，在安装光盘中加载 “ 分析数据库 ” 。加载成功后，可以在 “ 工具 ” 下拉菜单中看

    到 “ 数据分析 ” 选项。

    操作步骤25

    1. 打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为

    同一属性数值即可。

    2. 选择 “ 工具 ” - “ 数据分析 ” - “ 描述统计 ” 后，出现属性设置框，依次选择；

    输入区域 : 选择数据区域 ， 如果有数据标志 ， 注意同时勾选下方 “ 标志位于第一行 ” ；

    分组方式 : 指示输入区域中的数据是按行还是按列考虑，请根据原数据格式选择；

    输出区域可以选择本表、新工作表组或是新工作簿。

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

    可以看到 ， 此时生成一个四列的新表格 ， 其中 “ 点 ” 是指排序后原数据的序数 ， 在本

    实例中对应与学号 ， 这也是很实用的一个序列 ； “ 成绩 ” 即为排序后的数据系列 ； “ 排位 ” 采取

    重复数据占用同一位置的统计方法； “ 百分比 ” 是按照降序排列的，为了得到真正的 “ 百分比

    排位 ” ，还需要稍微作一下调整。26

    4. 在 “ 百分比 ” 列的下一列输入 “ 百分排名 ” ， 在第一个单元格中输入公式 “ =1-G3( 对应

    于 ‘ 百分排名 ) ” ，回车。选中该单元格，向下拖动直至填充完毕。这样就达到了显示百分比

    排名的目的。

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

    用 用 用 用 Excel Excel Excel Excel 做数据分析 做数据分析 做数据分析 做数据分析 — — — — 描述统计 描述统计 描述统计 描述统计

    某班级期中考试进行后 ， 需要统计成绩的平均值 、 区间 ， 以及给出班级内部学生成绩差

    异的量化标准 ， 借此来作为解决班与班之间学生成绩的参差不齐的依据 。 要求得到标准差等

    统计数值。

    样本数据分布区间 、 标准差等都是描述样本数据范围及波动大小的统计量 ， 统计标

    准差需要得到样本均值 ， 计算较为繁琐 。 这些都是描述样本数据的常用变量 ， 使用 Excel 数

    据分析中的 “ 描述统计 ” 即可一次完成。

    注 ：本功能需要使用 Excel 扩展功能，如果您的 Excel 尚未安装数据分析，请依次选择

    “ 工具 ” - “ 加载宏 ” ， 在安装光盘中加载 “ 分析数据库 ” 。 加载成功后 ， 可以在 “ 工具 ” 下拉菜单中

    看到 “ 数据分析 ” 选项。

    操作步骤

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

    一属性数值即可。28

    2. 选择 “ 工具 ” - “ 数据分析 ” - “ 描述统计 ” 后，出现属性设置框，依次选择：

    输入区域：原始数据区域，可以选中多个行或列，注意选择相应的分组方式；

    如果数据有标志 ， 注意勾选 “ 标志位于第一行 ” ； 如果输入区域没有标志项 ， 该复选

    框将被清除， Excel 将在输出表中生成适宜的数据标志；

    输出区域可以选择本表、新工作表或是新工作簿；

    汇总统计：包括有平均值、标准误差 ( 相对于平均值 ) 、中值、众数、标准偏差 、 方

    差 、 峰值 、 偏斜度 、 极差 、 最小值 、 最大值 、 总和 、 总个数 、 最大值 、 最小值和置信度等相

    关项目。

    其中：

    中值：排序后位于中间的数据的值；

    众数：出现次数最多的值；29

    峰值：衡量数据分布起伏变化的指标，以正态分布为基准，比其平缓时值为正 ， 反

    之则为负；

    偏斜度 ： 衡量数据峰值偏移的指数 ， 根据峰值在均值左侧或者右侧分别为正值或负

    值；

    极差：最大值与最小值的差。

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

    平均数置信度：数值 95% 可用来计算在显著性水平为 5% 时的平均值置信度。

    结果示例如下 ( 本实例演示了双列数据的描述统计结果 ) ：

    成绩 学习时间

    平均 78.64285714 平均 62.91428571

    标准误差 2.408241878 标准误差 1.926593502

    中位数 85 中位数 68

    众数 98 众数 78.4

    标准差 18.02163202 标准差 14.41730562

    方差 324.7792208 方差 207.858701330

    峰度 1.464424408 峰度 1.464424408

    偏度 -1.130551511 偏度 -1.13055151

    区域 85 区域 68

    最小值 15 最小值 12

    最大值 100 最大值 80

    求和 4404 求和 3523.2

    观测数 56 观测数 56

    最大 (1) 100 最大 (1) 80

    最小 (1) 15 最小 (1) 12

    置信度 (95.0%) 4.826224539 置信度 (95.0%) 3.860979631

    用 用 用 用 Excel Excel Excel Excel 做数据分析 做数据分析 做数据分析 做数据分析 — — — — 直方图 直方图 直方图 直方图

    使用 Excel 自带的数据分析功能可以完成很多专业软件才有的数据统计 、 分析 ， 这其中

    包括 ： 直方图 、 相关系数 、 协方差 、 各种概率分布 、 抽样与动态模拟 、 总体均值判断 ， 均值

    推断 、 线性 、 非线性回归 、 多元回归分析 、 时间序列等内容 。 下面将对以上功能逐一作使用

    介绍，方便各位普通读者和相关专业人员参考使用。

    注 ：本功能需要使用 Excel 扩展功能，如果您的 Excel 尚未安装数据分析，请依次

    选择 “ 工具 ” - “ 加载宏 ” ，在安装光盘中加载 “ 分析数

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

    实例 1

    某班级期中考试进行后 ， 需要统计各分数段人数 ， 并给出频数分布和累计频数表的

    直方图以供分析。32

    以往手工分析的步骤是先将各分数段的人数分别统计出来制成一张新的表格 ， 再以

    此表格为基础建立数据统计直方图。使用 Excel 中的 “ 数据分析 ” 功能可以直接完成此任务 。

    操作步骤

    1. 打开原始数据表格 ， 制作本实例的原始数据要求单列 ， 确认数据的范围 。 本实例

    为化学成绩，故数据范围确定为 0-100 。

    2. 在右侧输入数据接受序列。所谓 “ 数据接受序列 ” ，就是分段统计的数据间隔 ， 该

    区域包含一组可选的用来定义接收区域的边界值。这些值应当按升序排列。在本实例中 ， 就

    是以多少分数段作为统计的单元 。 可采用拖动的方法生成 ， 也可以按照需要自行设置 。 本实

    例采用 10 分一个分数统计单元。

    3. 选择 “ 工具 ” - “ 数据分析 ” - “ 直方图 ” 后，出现属性设置框，依次选择：

    输入区域：原始数据区域 ;

    接受区域：数据接受序列 ;

    如果选择 “ 输出区域 ” ，则新对象直接插入当前表格中 ;33

    选中 “ 柏拉图 ” ，此复选框可在输出表中按降序来显示数据 ;

    若选择 “ 累计百分率 ” ，则会在直方图上叠加累计频率曲线 ;

    4. 输入完毕后，则可立即生成相应的直方图，这张图还需要比较大的调整。34

    主要是：

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

    为了达到柱型图之间无缝的紧密排列，需要将 “ 数据系列格式 ” 中的 “ 选项 ” 中 “ 分类

    间距 ” 调整为 “ 0 ” 。其余细节，请双击要调整的对象按照常规方法进行调整，这里不再赘述 。

    调整后的直方图参考如下35

    excel excel excel excel 数据分析 数据分析 数据分析 数据分析

    公式

    公式是 Excel 的核心功能之一，它使工作表具备 “ 计算 ” 能力，用户只需输入原始数据 ，进一步的计算用公式来实现，准确、快速又方便。公式由等号( = )、运算符(加(+) 、减(-)、乘( )、除()、乘方( ^ )、括号())、数等要素组成。

    1 等号 ： 这是公式的标志 。 若想对数值进行计算 ， 必须在数值运算公式前加等号 。

    ② 操作符：表示执行哪种运算。

    ③ 数：引用的单元格、函数或常数。

    〖案例三 〗 用 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 ，在 C1 4

    单元格输入 ‘ 368573.59 ，在 D14 单元格输入 ‘ 9141.46 ；

    ⑤ 在 A15 单元格输入 ‘ 全地区 ， 在 B15 单元格输入 ‘ =B13+B14 ， 然后敲回车键 ， 则 B1 5

    单元格显示的结果为 47.46 ；

    ⑥ 然后用鼠标左键点击一次 B15 单元格，再次将鼠标光标移动到该单元格的右下角，这时鼠标变成十字星，利用该填充功能将 C15 和 D15 单元格分别填充为 ‘ =C13+C14 和

    ‘ =D13+D14 ；36

    ⑦ 将 B13 到 D15 单元格格式全部选为数值 ， 小数位保留 2 位 ， 然后敲回车键 ， 则在 B1 5

    单元格显示的结果为 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 单元格内输入 ‘ =C13B13 ， 然后敲回车键 ，再利用填充功能将 B21 单元格内的公式填充到 B22 和 B23 单元格中；

    ④ 在 C20 单元格内输入 ‘ ( 2 ) ，在 C21 单元格内输入 ‘ =D13B13 ，然后敲回车键，再

    利用填充功能将 C21 单元格内的公式填充到 C22 和 C23 单元格中；

    ⑤ 设定各单元格数值格式和绘制边框 ( 见统计学原理 .XLS 的 ‘ 第三节 统计表及其设计

    工作表)。计算过程完成。

    〖案例五 〗 用 EXCEL 公式计算 《 统计学原理 》 教材第 40 页表 3 — 4 中销售收入各组的

    企业比重 、 销售收入的上限 、 下限 、 组中值 、 组距 ( 见统计学原理 .XLS 的 ‘ 第三章第三节次

    数分布 工作表)。

    计算步骤：

    ① 用鼠标右键点击统计学原理 .XLS ‘ 第二章第三节统计表及其设计 工作表标签 ， 将它复

    制为统计学原理 .XLS ‘ 第二章第三节统计表及其设计( 2 ) 工作表，再将该工作表标签改写

    成 ‘ 第三章第三节次数分布 工作表。

    ② 将 A 1 单元格改写成 ‘ 表 3 — 4 ， 将 B 1 单元格改写成 ‘ 某地区商业企业销售收入统计表 ；

    将 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 单元格内的公式填充到 C 5

    单元格中。这时，销售收入各组的企业比重数就计算出来了。

    ④ 在 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 的 ‘ 第三章第三节次数分布

    工作表)。计算过程完成。

    五、绘制曲线图37

    ㈠绘制直方图

    〖案例六 〗 用 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 )。38

    ③ 在描述统计的输入区域对话框内输入 A28 ： A38 ，分组方式选择 ‘ 逐列 ，将标志

    位于第一行选中 。 在输出选项的输出区域对话框内输入 A41 ， 再将汇总统计选中 ， 点击确

    定。

    ④ 在 B40 单元格内输入 ‘ =B47SQRT((COUNT(A29:A38)-1)COUNT(A29:A38)) ，敲回

    车键。计算过程结束(见图 2 — 2 — 3 )。

    第三节 平均发展速度类函数

    一、乘幂函数

    power (底 , 指数)

    〖案例十 〗 用 EXCEL 乘幂函数计算 《 统计学原理 》 教材第 99 页 〔 例 6-6 〕 1985 — 200 4

    年间平均发展速度(见统计学原理 .XLS 的 ‘ 第六章动态数列 工作表)。

    计算步骤：

    ① 在 A1 到 B4 单元格内创建表 6 — 1 ( 1 )，在 C2 单元格内输入 ‘ 平均发展速度 。

    ② 在 C3 单元格 ， 输入 ‘ =POWER(B4B3,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 — 200 0

    年间某地区粮食产量的直线趋势方程常数项 a ( 见统计学原理 .XLS 的 ‘ 第六章动态数列 工作

    表)。

    计算步骤：

    ① 在 A14 到 C25 单元格内创建表 6 — 14 ( 1 )，在 A27 单元格内输入 ‘ 直线趋势方程常

    数项 。

    ② 在 B27 单元格输入 ‘ =INTERCEPT(C16:C25,B16:B25) ， 然后 ， 敲回车键 。 计算过程结

    束。

    二、斜率函数

    SLOPE (因变量数据单元格区域 , 自变量数据单元格区域)

    〖案例十二 〗 用 EXCEL 截距函数计算 《 统计学原理 》 教材第 107 页 〔 例 6-12 〕 1991 — 200 0

    年间某地区粮食产量的直线趋势方程系数 b (见统计学原理 .XLS 的 ‘ 第六章动态数列 工作

    表)。

    计算步骤：

    ① 在 A14 到 C25 单元格内创建表 6 — 14 ( 1 )，在 A28 单元格内输入 ‘ 直线趋势方程系

    数 。

    ② 在 B28 单元格输入 ‘ =SLOPE(C16:C25,B16:B25) ，然后，敲回车键。计算过程结束 。

    第三章 EXCEL 相关与回归分析

    第一节 EXCEL 相关分析

    一、绘制散点图39

    〖案例十三 〗 用 EXCEL 图表功能绘制 《 统计学原理 》 教材第 158 页散点图 ( 见统计学

    原理 .XLS 的 ‘ 第十章相关与回归分析 工作表)。

    绘制步骤：

    ① 在 A1 到 B12 单元格内创建表 9 — 1 。

    ② 点击插入命令条中的图表图标 ， 然后选择散点图 ， 点击下一步 , 按照图表向导 — 4 步骤

    就可以绘制出散点(见统计学原理 .XLS 的 ‘ 第十章相关与回归分析 工作表)。

    二、计算相关系数

    〖案例十四〗用 EXCEL 数据分析功能计算《统计学原理》教材第 160 页〔例 9-1 〕生

    产总值 ( 亿元 ) 与社会商品零售总额 ( 亿元 ) 的相关系数 ( 见统计学原理 .XLS 的 ‘ 见统计学

    原理 .XLS 的 ‘ 第十章相关与回归分析 工作表)。

    计算步骤：

    ① 在 A19 到 C27 单元格内创建表 9 — 4 。

    ② 点击工具命令条中的数据分析 ， 在数据分析对话框内选择相关系数 ， 点击确定 。 在相

    关系数对话框内的输入区域对话框输入 B20 ： C27 ， 在分组方式中选择 ‘ 逐行 ， 将标志位

    于第一行选中，在输出区域对话框内输入 A28 ，点击确定(见统计学原理 .XLS 的 ‘ 第十章

    相关与回归分析 工作表)，计算过程结束。

    第二节 EXCEL 回归分析

    一、建立一元线性回归方程

    〖案例十五 〗 用 EXCEL 数据分析功能建立 《 统计学原理 》 教材第 164 〔 例 9-2 〕 一元线

    性回归方程(见统计学原理 .XLS 的 ‘ 第五章平均指标和变异指标 工作表)。

    建立一元线性回归方程步骤：

    ① 在 A33 到 A41 单元格内创建表 9 — 7 ( 1 ) ， 在 A42 单元格内输入 ‘ 建立一元线性回归

    方程 。

    ② 点击 A43 单元格，然后再点击工具命令条中的数据分析，在数据分析对话框内选择

    回归，点击确定。在回归对话框内的 Y 值输入区域对话框输入 B34 ： B41 ， X 值输入区

    域对话框输入 A34 ： A41 ，将标志选中，在输出选项对话框输出区域内输入 A42 (见

    图 3 — 2 — 1 )，点击确定(见图 3 — 2 — 2 )。

    ③ 建立的财政收入 y 关于增加值 x 一元线性回归方程：

    建立一元线性回归方程过程结束(见统计学原理 .XLS 的 ‘ 第十章相关与回归分析 工作

    表)。

    使用使用使用使用 Excel Excel Excel Excel 做数据分析之回归分析方法 做数据分析之回归分析方法 做数据分析之回归分析方法 做数据分析之回归分析方法

    在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述 ， 趋

    势预测和残差分析等等 。 很多专业读者遇见此类问题时往往寻求专业软件 ， 比如在化工中经

    常用到的 Origin 和数学中常见的 MATLAB 等等 。 它们虽 很专业 ， 但其实使用 Excel 就完全

    够用了。我们已经知道在 Excel 自带的数据库中已有线性拟合工具，但是它还稍显单薄 ， 今

    天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。

    注 ： 本功能需要使用 Excel 扩展功能 ， 如果您的 Excel 尚未安装数据分析 ， 请依次

    选择 “ 工具 ” - “ 加载宏 ” ，在安装光盘支持下加载 “ 分析数据库 ” 。加载成功后，可以在

    “ 工具 ” 下拉菜单中看到 “ 数据分析 ” 选项40

    实例 某溶液浓度正比对应于色谱仪器中的峰面积，现欲建立不同浓度下对应峰面

    积的标准曲线以供测试未知样品的实际浓度 。 已知 8 组对应数据 ， 建立标准曲线 ， 并且对此

    曲线进行评价，给出残差等分析数据。

    这是一个很典型的线性拟合问题 ， 手工计算就是采用最小二乘法求出拟合直线的待

    定参数 ， 同时可以得出 R 的值 ， 也就是相关系数的大小 。 在 Excel 中 ， 可以采用先绘图再添

    加趋势线的方法完成前两步的要求。

    选择成对的数据列，将它们使用 “ X 、 Y 散点图 ” 制成散点图。

    X 、 Y 散点图

    在数据点上单击右键，选择 “ 添加趋势线 ” - “ 线性 ” ，并在选项标签中要求给出

    公式和相关系数等，可以得到拟合的直线。41

    给出公式和相关系数

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

    因为 R2 >0.99 ，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以

    大于 99.99% 地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其

    他未知浓度溶液的测量。

    为了进一步使用更多的指标来描述这一个模型 ， 我们使用数据分析中的 “ 回归 ” 工

    具来详细分析这组数据。42

    回归工具

    在选项卡中显然详细多了，注意选择 X 、 Y 对应的数据列。 “ 常数为零 ” 就是指明

    该模型是严格的正比例模型，本例确实是这样，因为在浓度为零时相应峰面积肯定为零 。 先

    前得出的回归方程虽然拟合程度相当高 ， 但是在 x=0 时 ， 仍然有对应的数值 ， 这显然是一个

    可笑的结论。所以我们选择 “ 常数为零 ” 。

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

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

    残差图、线性拟合图和正态概率图

    在线性拟合图中可以看到 ， 不但有根据要求生成的数据点 ， 而且还有经过拟和处理

    的预测数据点 ， 拟合直线的参数会在数据表格中详细显示 。 本实例旨在提供更多信息以起到

    抛砖引玉的作用 ， 由于涉及到过多的专业术语 ， 请各位读者根据实际 ， 在具体使用中另行参

    考各项参数，此不再对更多细节作进一步解释。

    残差图是有关于世纪之与预测值之间差距的图表 ， 如果残差图中的散点在中州上下

    两侧零乱分布，那么拟合直线就是合理的，否则就需要重新处理。

    图表

    更多的信息在生成的表格中，详细的参数项目完全可以满足回归分析的各项要求 。

    下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。44

    用单元格数据作为 用单元格数据作为 用单元格数据作为 用单元格数据作为 Excel Excel Excel Excel 工作簿名称 工作簿名称 工作簿名称 工作簿名称

    1 、 启动 Excel ， 执行 “ 工具 → 宏 → Visual Basic 编辑器 ” 命令 ， 进入 VBA 编辑状态 ( 如

    图 1) 。

    图 1 VBA 编辑

    2 、 在左侧的 “ 工程资源管理器 ” 窗口中 ， 选中 “ VBAproject(PERSONAL.XLS) ” ( 个

    人宏工作簿 ) 选项。

    3 、执行 “ 插入 → 模块 ” 命令，插入一个模块 ( 模块 1) 。45

    4 、将下述代码输入到右侧的代码编辑窗口中：

    Sub baocun

    lj = InputBox( 请输入文档保存路径 )

    ActiveWorkbook.SaveAs Filename:=lj ActiveCell.Value .xls

    End Sub

    退出 VBA 编辑状态。

    5 、以后要保存某个工作簿文档时，先选中作为名称的字符所在的单元格 ( 参见 图

    2) ，然后执行 “ 工具 → 宏 → 宏 ” 命令，打开 “ 宏 ” 对话框 ( 如图 3) 。

    图 2 单元格

    ER-BOTTOM-COLOR: 000000; BORDER-TOP-COLOR: 000000; BORDER-RIGHT-COLOR:

    000000 alt= 教 你 用 单 元 格 数 据 作 为 Excel 工 作 簿 名 称

    src=http:images.itdb.cnNews2006120782118002352.jpg border=1>

    图 3 宏对话框

    6 、 选中刚才编辑的宏 (PERSONAL.XLS!baocun) ， 单击 “ 执行 ” 按钮 ， 系统弹出如 图

    4 所示的对话框。46

    图 4 对话框

    7 、输入保存文档的路径 ( 如 “ E:\office 技巧 \ ” ) ，单击 “ 确定 ” 按钮。

    文档保存成功 ( 参见图 5) 。

    图 5 保存成功

    注意：如果不需要保存路径，文档将被保存到 “ 我的文档 ” 文件夹中。 ......