应用EXCEL软件建立水泥及熟料的强度预测

程并对回归分析的检验。 1 在 EXCEL 中插入 LINEST 与 TREND 函数常识
设线性方程 y=mx+b 或 y=m1x1+m2x2+…+b(如果自变 量多于 1 个时)，式中因变量 y 是自变量 x 的函数。利用 EXCEL 插 入 函 数 LINEST 可 以 根 据 已 知 数 据 拟 合 最 佳 直 线 ，并 返 回 描 述 此 直 线 的 数 组 。 因 此 此 函 数 返 回 的 是 数 值 数 组 ，需 以 数 组 公 式 的 形 式 输 入 。 强 调 的 是 输 入 数 组必须有如下规则：
（4）下 面 举 例 说 明 如 何 利 用 LINEST 函 数 建 立 y 与 x1,x2……x6 的多元线性回归分析，结果如表 4 所示。
2006 / 3 水泥技术
67
电气自动化
表4
表5
具体步骤为：在表 4 中选取要存放回归统计值的单 元格区域，这里设定（B36 H40）区域，在单元格 B36 中输 入 数 组=LINEST (B4 B23,C4 H23,TRUE,TRUE), 按 Ctrl+ Shift+Enter 结束操作，可得 y 与自变量间的回归方程如 下 所 示 ：y =0.415x1 +41.496x2 +193.1x3 +67.44x4 -0.924x5 + 9.983x6-606，其它参数如上例一元线性回归分布。在表 4 中 可 知 此 方 程 与 一 元 回 归 方 程 相 比 ，多 元 线 性 回 归 方 程 的剩余标准误差 S 比一元线性回归方程的剩余标准误 差 S 小（1.35<1.70）,判定系数 R2 比一元更高(0.69>0.32), 因此可判断用多元线性回归建立的方程的回归精度更 高,更适用于实际应用中的预测。 3 28d 强度计算值与实际值的相对误差分析
在表 5 中选定区域(K4 K23),输 入 数 组=TREND（B4 B23,C4 C23,,TRUE），按 Ctrl+Shift+Enter 结 束 操 作 ，便 可 以 在 选 定 区 域 返 回 一 元 线 性 回 归 方 程 的 28d 强 度 计 算 值 y1。在 L4 单元格输入(K4-J4) / J4*100,Enter 结束操作， L4 单元格便返回相对误差值（注：表中带括号的数值为 负数）。点击 L4 单元格向下选取区域至单元格 L23，用编 辑 / 填充 / 向下填充工具，便可以得到如下(L4 L23)区域所 示的相对误差值。同理,选定区域单元格(M4 M23), 输入 数 组=TREND（B4 B23,C4 H23,,TRUE），可 以 得 到 上 面 多 元 线 性 回 归 方 程 的 28d 强 度 计 算 值 y2（M4 M23），同 理 可得相对误差为（N4 N23）。相比较可以发现一元线性回
电气自动化
熟料和水泥的强度是水泥生产的一个重要考核指 标，它是按照国家标准 GB / T 17671 水泥胶砂强度检验 方 法 来 检 测 的 。 由 于 水 泥 工 业 生 产 的 连 续 性 、水 泥 库 存 量 的 限 制 及 用 户 对 水 泥 的 急 切 需 要 ，生 产 出 来 的 水 泥 绝 大多数不能等 28d 以后再出厂。因此，打破目前水泥各 种繁琐的以传统物理检验为基础的快速强度方法的框 框，寻求一种快速而又准确地预测熟料或水泥的新途 径，是目前各水泥厂迫切需要的。
电气自动化
含量设为 x2、熟料饱和比 KH1 设为 x3、硅率 N 设为 x4、熟 料中的 fCaO 设为 x5 和 C4AF 设为 x6，即单元格区域（C4 H23）。
（2）在 表 2 单 元 格 C24 中 输 入=CORREL（)*)+,)* )23，C4 C23）,便可求 得 x1 与 y 的 相 关 系 数 为 0.57，点 击 C24 单元格向右拖动鼠标至 H24，点编辑 / 填充 / 向右填 充工具，便可以得到所有变量与 y 的相关系数。此系数可 以 用 作 选 取 哪 几 个 变 量 做 回 归 的 参 考 ，但 有 时 并 不 代 表 此值较小就对 y 影响就不重要。还需要 T 值统计检验， 在后面会讲到如何进行 T 值统计检验。
表 1 统计值在数组中的顺序
Mn
Mn-1
……
M2
M1
b
Sen
Sen-1
……
Se2
Se1
Seb
R2
S
#N / A
#N / A
#N / A #N / A
ቤተ መጻሕፍቲ ባይዱ
F
f
#N / A
#N / A
#N / A #N / A
S回
S余
#N / A
#N / A
#N / A #N / A
通讯地址：浙江省诸暨八方水泥有限责任公司，浙江 诸暨 311816； 收稿日期：2005-10-03； 编辑：赵莲
（1）如 果 数 组 公 式 将 返 回 多 个 结 果 ，请 点 击 鼠 标 左 键不放向右下方拖动选定需要输入数组公式的单元格 区域。区域的列数为各种变量的总和，行数为 5 行。如： 需要求解 3 个自变量，一个因变量，则需要选定 4 列 5 行的单元格区域就可以。
（2）函数 LINEST 的数组公式为 LINEST（Known-y’s, Known-x’s,const,stats）,其 中 ：Known-y’s 是 历 史 数 据 中 y 的 单 元 格 区 域 ，Known-x’s 是 历 史 数 据 中 x 的 单 元 格 区 域 ，如 果 只 用 到 一 个 变 量 ，只 要 输 入 行 列 维 数 相 同 ，它 们 可 以 是 任 何 形 状 的 选 定 区 域 ，可 用 点 击 鼠 标 左 键 不 放 拖 拉选取区域即可。如果用到不只一个变量，Known-y’s 必 须 是 一 行 或 一 列 的 区 域 。 Const 为 一 逻 辑 值 ， 设 为 TRUE 或省略，b 将被正常计算，如果为 FALSE 时，b 将 被视为 0，并同时调整 m 值使 y=mx。stats 为 TRUE 时， 函数将返回附加回归统计值，反之，如果为 FALSE 或省 略，函数只返回系数 m 和常数项 b。具体附加返回统计值 在数组中的顺序见表 1。
（3）下 面 具 体 说 明 如 何 利 用 LINEST 函 数 建 立 y 与 x1 的一元线性回归分析。在表 3 中先选定要存放回归方 程 统 计 值 的 区 域 ，这 里 为（G27：H31），在 单 元 格 G27 中 输入数组公式=LINEST (B4 B23,C4 C23,TRUE,TRUE),按 Ctrl+Shift+Enter 组 合 键 结 束 操 作 ， 将 返 回 表 3 中（G27 H31）区 域 中 的 数 值 。 即 回 归 方 程 为 ：y=0.4173x1+39.94， 其各参数可查附加回归统计值顺序表格。如表 3 中 G27、 H27 分 别 为 方 程 的 X1 斜 率 系 数 和 b 值 ，G28、H28 分 别 为 x1 和 b 的标准误差，G29、H29 分别为方程的复相关系 数和估算方程的剩余标准误差，G30、H30 分别为方程的 F 统计值和自由度，G31、H31 分别为方程的回归平方和 和残余平方和。
传统利用回归分析法计算具有相关关系的一个变 量 或 多 个 变 量 ，有 一 元 线 性 回 归 、多 元 线 性 回 归 及 逐 步 回 归 分 析 法 ，但 由 于 现 实 中 要 计 算 的 数 据 资 料 多 ，而 且 通 常 影 响 质 量 的 要 素 不 止 一 个 ，要 找 出 这 些 因 素 和 质 量 之 间 的 数 量 关 系 就 是 多 元 分 析 ，而 多 元 分 析 要 计 算 的 公 式复杂，计算繁琐，本人通过运用 EXCEL 建立的计算一 元或多元线性回归即简捷又易于检验及预测未来值。因 此 ，大 大 地 提 高 了 工 作 效 率 ，也 有 利 于 更 改 模 型 做 逐 步 回归分析和数理统计和要预测的未来数据值等等。下面 就具体讲讲如何用办公软件 EXCEL 来求解线性回归方
（1）搜 集 历 史 数 据 库 ，并 将 历 史 检 测 数 据 输 入 或 复 制到 EXCEL 工作表中（表 2）。
表 2 中 A 列 从 上 到 下 分 别 为 20 个 连 续 样 本 序 列 数。从 B 到 H 列分别为熟料 28d 强度实际值 y，即单元 格 区 域（B4 B23）；将 熟 料 3d 强 度 设 为 x1、熟 料 中 Al2O3
（3）在 选 定 需 要 存 放 附 加 返 回 统 计 值 的 数 组 区 域 后 , 在 区 域 左 上 方 第 一 个 单 元 格 内 输 入 输 入 ==LINEST （Known-y’s,Known-x’s,TRUE,TRUE）, 再 按 Ctrl +Shift + Enter 组合键结束操作,函数自动会在数组公式之间插入 {},并返回回归统计值表。查表 1 就可以知道各个回归参 数值。
66
CEMENT TECHNOLOGY 3 / 2006
其中 M1,M2,……Mn-1,Mn 分 别 为 X1,X2,……,Xn-1,Xn 的 系数，Se1,Se2,……Sen-1，Sen,分别为 M1,M2,……Mn-1,Mn 和常 数项 b 的标准误差值。R2 为判定系数，实为复相关系数 r 的平方，表示 y 估计值与实际值之比，在 0～1 之间。如果 接近 1，则样本有很好的相关性；反之，趋于 0 的话，则表 示此回归方程不能用来预测 y 值。它是回归方程分析的 结果反映变量间关系程度的标志。S 为估计值的标准误 差，即 等 于 剩 余 标 准 偏 差 ，此 值 越 小 ，回 归 精 度 越 高 ，此 值也等于残余平方和与自由度的商的平方根。F 为 F 统 计 值 或 观 察 值 ，用 来 判 断 因 变 量 与 自 变 量 之 间 是 否 偶 尔 发生过观察到的关系，当 F 统计值>F 临界值时，则回归 模型预测的置信度较高，反之，则较低，不能用来预测。S 回为回归平方和，用来表示由于自变量 X 的变化引起的 因变量 y 的变化。S 余为残余平方和，每点的 y 估计值和 实际值的平方差之和。因此，总平方和 L00=S 回+S 余,表 示 y 的实际值与平均值的平方差之和，S 余 / L00 的比值 越小，R2 则越大。f 为自由度,用于在统计表上查找 F 临 界值。所查得的值和函数 LINEST 返回的 F 统计值的比 值可用来判断模型的置信度。
在 水 泥 工 业 和 许 多 科 技 领 域 中 ，经 常 遇 到 一 些 相 互 制 约 的 关 系 量 ，它 们 之 间 存 在 着 一 定 的 联 系 ，由 于 变 量 之 间 的 不 确 定 性 ，使 测 定 值 参 差 不 齐 ，以 致 在 绝 大 多 数 情 况 下 难 以 用 准 确 的 数 学 公 式 表 示 出 来 ，变 量 之 间 的 这 种关系称作相关关系。两个变量间的相关关系通常用相 关系数 r 来表示（多元线性回归称复相关系数），例如水 泥的 1d、3d、7d 抗压强度与 28d 抗压强度之间，熟料或 水泥的化学成分与其抗压强度之间存在的关系就是相 关关系。通常用回归分析法统计处理各变量之间相关关 系。利用这种方法可以确定几个特定的变量之间是否存 在 相 关 关 系 ， 并 对 所 得 关 系 式 的 可 靠 程 度 进 行 检 验 ；利 用 所 建 立 的 关 系 式 ，根 据 一 个 或 几 个 变 量 的 值 ，预 测 或 控制另一个变量的取值，并估计预测值的精度。
（4）简单讲一下 TREND 在这儿的使用，值的一提的 是它也是通过数组输入公式，和 LINEST 大同小异。可以 用 函 数 TREND(known-yˊs,known-xˊs，new-x’s,const)来 计 算回归直线的拟合值 y 不用人工输入方程式，计算简便。 new-xˊs 为 希 望 通 过 TREND 函 数 推 出 相 应 需 要 预 测 的 未来 x 值的区域，可用点击鼠标左键不放拖拉选取区域。 其它三项使用方法与 LINEST 使用一致。如果函数不带 参数 new-xˊs，可在实际数据点上根据直线来预测 y 的 数 组 值 ，然 后 可 以 将 预 测 值 与 实 际 值 进 行 比 较 。 还 可 以 用图表方式来直观地比较二者。只要在需要放入预测 y 值 的 单 元 格 或 区 域 中 输 入 = TREND (known-yˊs, known-xˊs，new-x’s,const)，并按 Ctrl+Shift+Enter 组 合 键 结束操作,函数自动会在数组公 式 之 间 插 入{},并 返 回 回 归预测值 y。 2 两个函数在建立回归分析上的应用