浅谈用Excel对井田煤质数据进行审核

吴继红

(山西省煤炭工业厅煤炭资源地质局，山西 太原 030045)

一个煤田地质勘探区的煤质化验数据少则几十组，多则上千组，每一组数据少则十几项，多则几十项。为确保各项测试结果准确可靠，不出现偏差，同时为煤田地质报告的煤质评价部分提供各煤质相关项目间的统计规律，利用Excel对井田内同一煤层不同钻孔的煤芯煤样的化验测试数据进行回归分析，得出以某一项测试结果为应变量，相关几项的测试结果为自变量的数学模型，再利用数学模型对测试结果进行验证，从而提高了煤质测试数据结果的审核效果和测试报告的可靠性。本文以某一动力煤井田为例详述如下。

1 软件准备

对于使用 Microsoft Office 2000中文版 Excel 2002的，可参照《Excel 2002函数、统计与分析应用范例》［1］的相关方法进行操作。对于使用Excel 2003或Excel 2007的可参照相关书籍加载数据分析功能项(加载宏)。如果是单变量(一元线性)也可直接利用系统自带的统计函数LINEST()进行操作。

2 数据准备

一般情况下，可根据测试人员的技能水平，测试数据结果的质量稳定程度，测试项目本身的复杂程度来确定因变量和自变量。根据实际情况，一般取同一煤层不同钻孔煤芯煤样的挥发分、发热量、氢和碳含量分别作为因变量，以灰分、水分、硫分(仅在发热量统计时选用)分别作为自变量;以煤灰熔融性为因变量，以煤灰成分为自变量;以视密度为因变量，以水分、灰分作为自变量分别进行数据列表。山西阳泉某煤层的工业分析和氢含量的测试数据见表1。

表1 煤质数据一览表

3 进行回归分析，得到数学模型

利用Excel提供的数据分析工具，以Had的数据列为应变量(Y)，以 Mad，Aad，Vad的数据列为自变量(X)，进行回归分析。

点击分析工具，选中回归，点击确定出现界面见图1。

图1 Excel回归分析工具界面操作图

根据回归界面要求，依次选取Y值(Had)数据区域和X值(Mad、Aad、Vad)数据区域，一般选取置信度为95%，常数为非零，回归结果输出区域，待数据区域选择无误后，点击确定，进行回归分析。在统计结果输出区域出现表2，表3。

表2 回归分析结果统计表

表3 利用多元回归函数对分析数据核对结果统计表

根据表2，表3得出，氢值(Had)与工业分析值(Mad、Aad、Vad)的数学统计模型:

该公式的相关系数R(MultipleR)为0.991 346，确定系数 R2(R Square)为 0.982 767，调整后的 R2(Adjusted R Square)为 0.965 533，预测值的标准差为0.111 308。回归分析表中，F鉴定的显著水准0.003 821＜a=0.05，由此可知，其结果为放弃变量与自变量间无回归关系存在的虚无假设，显示煤样中氢含量(Had)与工业分析值(Mad、Aad、Vad)整体间有明显的关系存在。由式(1)计算出的Had值有95%的煤样误差在0.22%以内。

由以上的统计结果可以看出，该回归分析结果统计的R、R2值，比《煤炭化验结果的审核与计算》中提出的“利用Ad和Vdaf计算及审核烟煤Hdaf的回归式:Hdaf=2.888 －0.393(√Vdaf)－0.002 3Ad［2］”计算的精度要好。同时，证明了同一井田，同一煤层煤炭的氢含量与其工业分析的关系相对比较稳定。

4 利用数学模型对数据进行审核

由表3可以看出，第7组数据中氢的计算值和测试值相差较大(计算值 －测试值 =3.34－3.57=－0.23)，通过对此数据进行测试过程回查得出，该样品包装不严密，且水分测试和氢测试中间间隔时间较长(5天)，可能是水分变化引起氢计算出现问题。通过对样品重新测试水分和氢含量，得出氢含量为3.46，误差(计算值 － 测试值 =3.34 － 3.46=－0.12)，小于标准规定的重复性限 0.15。

5 重新对数据进行回归分析

利用表1中第7个样品的重测结果对表1的氢值进行更正后，所得结果见表4。

表4 重测结果统计表

利用表4，重新进行回归分析得出结果，见表5。

表5 重测数据回归分析结果汇总表

根据表5得出，氢值(Had)与工业分析值(Mad、Aad、Vad)的数学统计模型为:

该公式的相关系数R(Multiple R)为0.997 254，确定系数，R2(R Square)为0.994 516，调整后的 R2(Adjusted R Square)为 0.989 031，预测值的标准差为 0.060 523。

回归分析表中，F鉴定的显著水准0.000 688＜a=0.05，显示煤样中的氢含量(Had)、工业分析值(Mad、Aad、Vad)整体间有明显的关系存在。由式(2)计算出的Had值有95%的煤样误差在0.12%以内。优于同一实验室允许差(重复性限)，同时也优于式(1)的计算精度。

6 结语

由于该井田是普查项目，样品数量不多，但随着该项目的不断延伸，用该统计方法完全可以对煤层的氢值测试结果进行审核，得到精度更高的氢值。同时，也提供了在煤质数据审核过程中，利用回归分析方法提出各相关联测试结果间的数学模型，再利用数学模型对测试结果进行核准，以期达到更可靠的测试结果和如实反映样品的属性。

［1］杨世莹.Excel2002函数、统计与分析应用范例［M］.北京:中国青年出版社，2003:239－246.

［2］陈亚非.煤炭化验结果的审核与计算［M］.北京:煤炭工业出版社，2003:237.