基于Excel的礦石小塊體重線性回歸方程在小柳溝鎢礦床儲量計算中的應用

金 鵬，孫倉平

（甘肅省有色金屬地質勘查局張掖礦產勘查院，甘肅 張掖 734000）

甘肅省肅南縣小柳溝鎢礦床于2007年10月建成投產，通過近10年來的開采，采礦許可證范圍內被開采過的礦體特征及資源儲量發生了較大的變化。該礦1992年開始普查，2003年進行勘探，2005年提交了勘探報告。在勘探報告中，4號礦體鎢礦石小塊體重采用的數據為2.95t/m3。而根據近年來深部勘探，在所做的礦石小塊體重試驗中，4號礦體鎢礦石小塊體重有所變化。在2015年儲量核實中發現4號礦體鎢礦石平均體重為2.97t/m3左右。為了能夠更準確、更科學、更客觀地解決這個問題，使計算出來的儲量與實際真實儲量接近。本文采用數學地質中的回歸分析法，利用excel函數簡便性，從大量的實驗數據中總結歸納出了一個有關小塊體重的經驗公式，對所測的小塊體重反過來進行校正，并與其它校正方法比較，結果表明：在確定鎢礦床的體重中，線性回歸方程法是一種更科學、更合理、更接近實際的方法。

1 小塊體重線性回歸方程的建立

1.1 回歸分析的概念

回歸分析是通過規定因變量和自變量來確定變量之間的因果關系，建立回歸模型，并根據實測數據來求解模型的各個參數，然后評價回歸模型是否能夠很好的擬合實測數據，找出變量之間最佳的函數關系式，這種研究隨機變量與非隨機變量之間的關系問題稱回歸分析(又叫相關分析)。

1.2 Excel函數

在Excel中，函數就是預定義的內置公式，它使用參數并按照特定的順序進行計算。函數的參數是函數進行計算所必須的初始值，用戶把參數傳遞傳遞給函數，函數按特定的指令對參數進行計算，把計算的結果返回給用戶。

1.3 小塊體重礦石樣品的采集及代表性

在同一礦石類型中，根據礦石樣品在不同的礦體、礦體的不同部位均勻分布的原則，即富、貧礦兼采和接近平均品位多采的原則，使樣品小塊體重測定數據基本上服從正態分布。

1.4 小塊體重礦石樣品變量的測定及質量檢驗

小塊體重按阿基米德原理測定，鎢品位用化學方法測定，并按規范要求抽取內、外檢樣品，以檢查加工化驗質量。

1.5 小塊體重多元線性回歸方程的建立

1.5.1 線性回歸方程建立的原理

先假定隨機變量．y依賴于非隨機變量xi(i=1…m)，y服從正態分布，則m元線性回歸方程=b0+b1x1+∧+bmxm，為了從空間坐標系中n個點擬合出一條最佳直線，即

亦使得殘差平方和最小，這就轉換成數學中有關多元函數求極值問題，為此，分別求Q對b0、b1、b2…bm的偏導數，并令其等于0，便得到m+1個方程即正規方程組，解此方程組，便可得出b0、b1、b2…bm和線性回歸方程，并對其進行顯著性檢驗，以檢驗其線性回歸效果。

1.5.2 回歸方程的建立步驟

對小塊體重樣品原始化驗數據進行必要的整理統計，其結果見表1。為便于與其它方法比較，本表只采用了4號礦體小體重樣品66件樣的原始化驗數據及最后的統計結果（所選樣品含水量均小于3%）。

表1 1號礦體小體重樣品分析結果表

第一步，按照品位-體重將數據輸入到excel表中，A列中第一行輸入“品位”，第二行輸入分析的品位數據；B列中第一行輸入“體重”，第二行行輸入體重測定數據。

第二步，生成散點圖。選定兩列數據，在插入菜單中找到“圖表”，點擊后找到散點圖。點擊后生產散點圖。

圖1 4號礦體小體重樣品體重與品位散點圖

第三步，計算相關系數。在excel表中的任空格內輸入“=CORREL（A2：A124，B2：B124）” 回 車， 算 出r4=0.41。

第四步，用t分布進行顯著性檢驗。計算t。

t=r＊（n-2）^0.5/(1-r2)^0.5=3.74463(n=66)。

在任意單元格內輸入“=TDIST（3.74463，65，2）”，P4=0.00037＜α=0.05，認為4號礦體品位和體重的相關關系是顯著地。通過散點圖可直接得出4號礦體回歸方程。

1.5.3 excel進行顯著性檢驗的方法與步驟

（1）先找ADD-IN，添加數據分析工具data analysis tool。Add-in的選項在File-＞Option-＞Add Ins，選擇analysis tool pack。

（2）會跳出來一個窗口，再選中analysis tookpack，點擊確定。

（3）把得到的兩組數據輸入EXCEL里。

（4）在DATA里面，選擇data analysis，跳出來新窗口，選中correlation（相關性）。然后按照提示，選中要分析的數據。

（5）EXCEL會自動運行回歸分析，給出分析報告。

2 Excel線性回歸結果分析

4號礦體品位和體重是弱相關關系的，4號礦體的比重值可以利用進行計算。從散點圖上可以看出體重值較為集中、波動小，而品位值則較為分散、波動大（圖1）。

3 結論

（1）進行回歸分析前，首先要查明礦石體重和元素之間是否存在一定的線性關系，可以通過品位散點圖和參與回歸的元素與小體重之間的相關系數來判斷，存在顯著線性相關性才能進行線性回歸。

（2）利用Excel建立回歸模型時要進行回歸方程的顯著性檢驗、回歸系數顯著性檢驗通過線性回歸顯著性檢驗后才能利用Excel回歸估算的結果進行實際應用。

（3）利用Excel軟件，以甘肅省肅南縣小柳溝鎢礦床4號礦體WO3元素的百分含量為自變量，小體重為應變量，成功建立線性回歸模型。

（4）充分利用Excel的內部函數可以減少工作量，提高計算精度。