淺談用Excel對井田煤質(zhì)數(shù)據(jù)進行審核

吳繼紅

(山西省煤炭工業(yè)廳煤炭資源地質(zhì)局，山西 太原 030045)

一個煤田地質(zhì)勘探區(qū)的煤質(zhì)化驗數(shù)據(jù)少則幾十組，多則上千組，每一組數(shù)據(jù)少則十幾項，多則幾十項。為確保各項測試結(jié)果準確可靠，不出現(xiàn)偏差，同時為煤田地質(zhì)報告的煤質(zhì)評價部分提供各煤質(zhì)相關(guān)項目間的統(tǒng)計規(guī)律，利用Excel對井田內(nèi)同一煤層不同鉆孔的煤芯煤樣的化驗測試數(shù)據(jù)進行回歸分析，得出以某一項測試結(jié)果為應變量，相關(guān)幾項的測試結(jié)果為自變量的數(shù)學模型，再利用數(shù)學模型對測試結(jié)果進行驗證，從而提高了煤質(zhì)測試數(shù)據(jù)結(jié)果的審核效果和測試報告的可靠性。本文以某一動力煤井田為例詳述如下。

1 軟件準備

對于使用 Microsoft Office 2000中文版 Excel 2002的，可參照《Excel 2002函數(shù)、統(tǒng)計與分析應用范例》［1］的相關(guān)方法進行操作。對于使用Excel 2003或Excel 2007的可參照相關(guān)書籍加載數(shù)據(jù)分析功能項(加載宏)。如果是單變量(一元線性)也可直接利用系統(tǒng)自帶的統(tǒng)計函數(shù)LINEST()進行操作。

2 數(shù)據(jù)準備

一般情況下，可根據(jù)測試人員的技能水平，測試數(shù)據(jù)結(jié)果的質(zhì)量穩(wěn)定程度，測試項目本身的復雜程度來確定因變量和自變量。根據(jù)實際情況，一般取同一煤層不同鉆孔煤芯煤樣的揮發(fā)分、發(fā)熱量、氫和碳含量分別作為因變量，以灰分、水分、硫分(僅在發(fā)熱量統(tǒng)計時選用)分別作為自變量;以煤灰熔融性為因變量，以煤灰成分為自變量;以視密度為因變量，以水分、灰分作為自變量分別進行數(shù)據(jù)列表。山西陽泉某煤層的工業(yè)分析和氫含量的測試數(shù)據(jù)見表1。

表1 煤質(zhì)數(shù)據(jù)一覽表

3 進行回歸分析，得到數(shù)學模型

利用Excel提供的數(shù)據(jù)分析工具，以Had的數(shù)據(jù)列為應變量(Y)，以 Mad，Aad，Vad的數(shù)據(jù)列為自變量(X)，進行回歸分析。

點擊分析工具，選中回歸，點擊確定出現(xiàn)界面見圖1。

圖1 Excel回歸分析工具界面操作圖

根據(jù)回歸界面要求，依次選取Y值(Had)數(shù)據(jù)區(qū)域和X值(Mad、Aad、Vad)數(shù)據(jù)區(qū)域，一般選取置信度為95%，常數(shù)為非零，回歸結(jié)果輸出區(qū)域，待數(shù)據(jù)區(qū)域選擇無誤后，點擊確定，進行回歸分析。在統(tǒng)計結(jié)果輸出區(qū)域出現(xiàn)表2，表3。

表2 回歸分析結(jié)果統(tǒng)計表

表3 利用多元回歸函數(shù)對分析數(shù)據(jù)核對結(jié)果統(tǒng)計表

根據(jù)表2，表3得出，氫值(Had)與工業(yè)分析值(Mad、Aad、Vad)的數(shù)學統(tǒng)計模型:

該公式的相關(guān)系數(shù)R(MultipleR)為0.991 346，確定系數(shù) R2(R Square)為 0.982 767，調(diào)整后的 R2(Adjusted R Square)為 0.965 533，預測值的標準差為0.111 308。回歸分析表中，F(xiàn)鑒定的顯著水準0.003 821＜a=0.05，由此可知，其結(jié)果為放棄變量與自變量間無回歸關(guān)系存在的虛無假設，顯示煤樣中氫含量(Had)與工業(yè)分析值(Mad、Aad、Vad)整體間有明顯的關(guān)系存在。由式(1)計算出的Had值有95%的煤樣誤差在0.22%以內(nèi)。

由以上的統(tǒng)計結(jié)果可以看出，該回歸分析結(jié)果統(tǒng)計的R、R2值，比《煤炭化驗結(jié)果的審核與計算》中提出的“利用Ad和Vdaf計算及審核煙煤Hdaf的回歸式:Hdaf=2.888 －0.393(√Vdaf)－0.002 3Ad［2］”計算的精度要好。同時，證明了同一井田，同一煤層煤炭的氫含量與其工業(yè)分析的關(guān)系相對比較穩(wěn)定。

4 利用數(shù)學模型對數(shù)據(jù)進行審核

由表3可以看出，第7組數(shù)據(jù)中氫的計算值和測試值相差較大(計算值 －測試值 =3.34－3.57=－0.23)，通過對此數(shù)據(jù)進行測試過程回查得出，該樣品包裝不嚴密，且水分測試和氫測試中間間隔時間較長(5天)，可能是水分變化引起氫計算出現(xiàn)問題。通過對樣品重新測試水分和氫含量，得出氫含量為3.46，誤差(計算值 － 測試值 =3.34 － 3.46=－0.12)，小于標準規(guī)定的重復性限 0.15。

5 重新對數(shù)據(jù)進行回歸分析

利用表1中第7個樣品的重測結(jié)果對表1的氫值進行更正后，所得結(jié)果見表4。

表4 重測結(jié)果統(tǒng)計表

利用表4，重新進行回歸分析得出結(jié)果，見表5。

表5 重測數(shù)據(jù)回歸分析結(jié)果匯總表

根據(jù)表5得出，氫值(Had)與工業(yè)分析值(Mad、Aad、Vad)的數(shù)學統(tǒng)計模型為:

該公式的相關(guān)系數(shù)R(Multiple R)為0.997 254，確定系數(shù)，R2(R Square)為0.994 516，調(diào)整后的 R2(Adjusted R Square)為 0.989 031，預測值的標準差為 0.060 523。

回歸分析表中，F(xiàn)鑒定的顯著水準0.000 688＜a=0.05，顯示煤樣中的氫含量(Had)、工業(yè)分析值(Mad、Aad、Vad)整體間有明顯的關(guān)系存在。由式(2)計算出的Had值有95%的煤樣誤差在0.12%以內(nèi)。優(yōu)于同一實驗室允許差(重復性限)，同時也優(yōu)于式(1)的計算精度。

6 結(jié)語

由于該井田是普查項目，樣品數(shù)量不多，但隨著該項目的不斷延伸，用該統(tǒng)計方法完全可以對煤層的氫值測試結(jié)果進行審核，得到精度更高的氫值。同時，也提供了在煤質(zhì)數(shù)據(jù)審核過程中，利用回歸分析方法提出各相關(guān)聯(lián)測試結(jié)果間的數(shù)學模型，再利用數(shù)學模型對測試結(jié)果進行核準，以期達到更可靠的測試結(jié)果和如實反映樣品的屬性。

［1］楊世瑩.Excel2002函數(shù)、統(tǒng)計與分析應用范例［M］.北京:中國青年出版社，2003:239－246.

［2］陳亞非.煤炭化驗結(jié)果的審核與計算［M］.北京:煤炭工業(yè)出版社，2003:237.