關(guān)于召開(kāi)第六屆山西省煤炭總工程師論壇暨山西省煤炭安全綠色智能開(kāi)采論壇的通知

如何用EXCEL進(jìn)行附和導(dǎo)線近似平差及計(jì)算中若干問(wèn)題的解決方法

地測(cè)隊(duì)：楊沖

2011年11月
如何用EXCEL進(jìn)行附和導(dǎo)線近似平差及

計(jì)算中若干問(wèn)題的解決方法

在煤礦測(cè)量工作中，我們經(jīng)常需要進(jìn)行單一附和導(dǎo)線近似平差。傳統(tǒng)的手工計(jì)算，不但效率低，而且算錯(cuò)之后不易改正（因?yàn)楹笠徊降钠鹚銛?shù)據(jù)由前一步計(jì)算得到，一旦算錯(cuò)，后續(xù)步驟都需重新計(jì)算），運(yùn)用EXCEL強(qiáng)大的函數(shù)計(jì)算功能，就可以解決這個(gè)難題。

用EXCEL進(jìn)行導(dǎo)線近似平差的流程大致為：a.輸入各站水平角、豎直角、起終端坐標(biāo)方位角、起終端坐標(biāo)及高程、各站斜距、儀器高、棱鏡高。b.計(jì)算坐標(biāo)方位角及其閉合差，并進(jìn)行閉合差分配。c.計(jì)算各導(dǎo)線點(diǎn)平距、坐標(biāo)和坐標(biāo)閉合差。如閉合差不超限，分配坐標(biāo)閉合差。d.計(jì)算導(dǎo)線點(diǎn)高程和高差閉合差，如不超限，分配高差閉合差。（具體的計(jì)算方法及格式見(jiàn)附表算例）

但是，在計(jì)算過(guò)程中，我們會(huì)遇到諸如角度計(jì)算、數(shù)值取位等問(wèn)題。

首先是角度計(jì)算問(wèn)題。我們知道，在EXCEL中，不能對(duì)角度值直接進(jìn)行三角函數(shù)運(yùn)算，需要將角度值換算成弧度。這里有兩個(gè)問(wèn)題，第一是角度輸入問(wèn)題。在EXCEL表格中，不能直接輸入度、分、秒。這里，筆者提供一種輸入角度的方法：首先自定義單元格格式，將其定義為[h]"°"mm"′"ss"〃"的形式。角度值輸入時(shí)，度、分、秒間以冒號(hào)隔開(kāi)，例如“23:30:01”，輸入完成后將自動(dòng)變成“23°30′01〃”，這樣輸入的角度可直接進(jìn)行加減運(yùn)算；第二是角度的三角函數(shù)運(yùn)算問(wèn)題，需運(yùn)用角度轉(zhuǎn)弧度函數(shù)“RADIANS()”。這里需要注意的是，先前輸入的角度值需要乘以24，例如：COS(RADIANS(A1*24))（假設(shè)輸入的角度值位置在A1上）。經(jīng)過(guò)以上兩個(gè)步驟，便可將斜距、水平角輕松轉(zhuǎn)換為平距、坐標(biāo)方位角。

再談數(shù)值取位問(wèn)題，我們知道，在角度計(jì)算中，遵循“四舍六入，五前奇進(jìn)偶不進(jìn)”原則。在坐標(biāo)增量（Δx=S×cosα、Δy=S×sinα）計(jì)算時(shí)，我們也會(huì)遇到取位問(wèn)題。如果一律按四舍五入方法計(jì)算，勢(shì)必造成坐標(biāo)增量偏大。故坐標(biāo)計(jì)算時(shí)，也應(yīng)遵循角度計(jì)算的取值方法，以避免因取值方法不當(dāng)造成的誤差累積。我們可以采用下面的方法實(shí)現(xiàn)這一過(guò)程。

需要用到EXCEL中的幾個(gè)函數(shù)：取絕對(duì)值函數(shù)ABS、嵌套條件函數(shù)IF、取小數(shù)位數(shù)函數(shù)ROUND、取余數(shù)函數(shù)MOD。以中間計(jì)算過(guò)程保留四位小數(shù)為例。需先將要計(jì)算的數(shù)值取絕對(duì)值，最后帶上正負(fù)號(hào)。例如：ABS(A1)/A1*K。計(jì)算中需要檢測(cè)小數(shù)點(diǎn)后第四位和第五位的數(shù)值（A1為要保留四位有效數(shù)字的數(shù)據(jù)）。過(guò)程為：a、如果MOD(A1*10000,1) <0.5，則直接保留四位有效數(shù)字輸出結(jié)果，即：ROUND(A1,4)；b、否則，如果MOD(A1*10000,1)<0.6，需要檢測(cè)小數(shù)點(diǎn)后第四位（保留四位小數(shù)后）的數(shù)值，如果是偶數(shù)，則輸出保留四位小數(shù)后的數(shù)值ROUND(A1,4)，否則，輸出ROUND(A1,4)-0.0001。c、如果MOD(A1*10000,1)>=0.6則輸出ROUND(A1,4)。具體實(shí)現(xiàn)算法為：

“=A1/ABS(A1)*IF(MOD(ABS(A1)*10000,1)<0.5,ROUND(ABS(A1),4),(IF(MOD(ABS(A1)*10000,1)<0.6,(IF(MOD(ROUND(ABS(A1),4)*10000,2)=0,ROUND(ABS(A1),4),ROUND(ABS(A1),4)-0.0001)),ROUND(ABS(A1),4))))”

解決了上述兩個(gè)難題，再運(yùn)用簡(jiǎn)單的三角函數(shù)及加減運(yùn)算，便可輕松在EXCEL上進(jìn)行導(dǎo)線近似平差。當(dāng)然，上述方法仍有一些瑕疵，如：負(fù)值角度在EXCEL中不能正確顯示，雖然并不影響計(jì)算結(jié)果，但還是希望大家在以后的研究中能夠進(jìn)一步改進(jìn)；其次，由于不能在一個(gè)單元格中進(jìn)行復(fù)雜的角度運(yùn)算，表格中存在部分冗余數(shù)據(jù)（如90°00′00〃、180°00′00〃），使得表格整體感覺(jué)龐大；再次，由于存在取位（近似值）問(wèn)題，計(jì)算結(jié)果與理論值仍存在毫米級(jí)偏差，當(dāng)然以目前礦山控制測(cè)量要求的精度來(lái)說(shuō)，完全可以忽略不計(jì)。

 地測(cè)隊(duì)：楊沖

2011年11月