淺談用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.