EXCEL內(nèi)置函數(shù)在測量平差中的應(yīng)用教學(xué)提綱

1、Good is good, but better carries it.精益求精，善益求善。EXCEL內(nèi)置函數(shù)在測量平差中的應(yīng)用-EXCEL內(nèi)置函數(shù)在測量平差中的應(yīng)用王海英陳世梅（南寧市勘測院廣西南寧530021）【摘要】測量平差是測量數(shù)據(jù)處理的重要環(huán)節(jié)，然而平差計(jì)算過程中，線性方程解算的問題卻比較麻煩。本文利用Excel的內(nèi)置函數(shù)簡化了線性方程解算的問題，并結(jié)合具體的實(shí)例，講述利用Excel內(nèi)置函數(shù)進(jìn)行測量平差的方法?！娟P(guān)鍵詞】矩陣Excel測量平差0引言在現(xiàn)代測量中，對通過各種測量方法所采集得到的原始數(shù)據(jù)，往往需要根據(jù)誤差理論的方法對存在的各類誤差進(jìn)行平差處理從而取得最或然結(jié)果。而對于偶然

2、誤差的處理，利用最小二乘法準(zhǔn)則進(jìn)行平差計(jì)算的過程，通常都要對誤差方程式或條件方程式進(jìn)行整合處理求得法方程組，然后解算法方程、計(jì)算改正數(shù)、精度評定等等，一系列計(jì)算步驟的進(jìn)行，如果沒有現(xiàn)成的專業(yè)軟件，而用手工的辦法去完成平差計(jì)算工作，將是一件很繁瑣的事情。其實(shí)，測量平差的過程簡單的來說就是解算線性方程組的過程，而最令人煩惱的部分是線性方程的求解。在處理一些小的工程項(xiàng)目平差計(jì)算的時候，如果手頭沒有像MATLAB等具有矩陣運(yùn)算功能的應(yīng)用軟件，利用MicrosoftOfficeExcel的內(nèi)置函數(shù)，同樣的可以幫助計(jì)算者比較輕松的完成計(jì)算任務(wù)。以下要闡述的就是利用Excel轉(zhuǎn)置粘貼功能以及矩陣計(jì)算的函數(shù)M

3、MULT（矩陣乘）、MINVERSE（矩陣求逆），實(shí)現(xiàn)測量平差之線性方程組解算的過程。1平差的數(shù)學(xué)模型為了加快平差解算的作業(yè)效率，應(yīng)該根據(jù)實(shí)際情況選擇適當(dāng)?shù)臄?shù)學(xué)模型。實(shí)際工作中，有兩種數(shù)學(xué)模型得到了較為廣泛的應(yīng)用，即間接平差模型和條件平差模型；它們的應(yīng)用公式1分別如下：1.1間接平差誤差方程：V=Bx+J；J=BX0+B0L；法方程：BTPBx+BTPJ=0；改正數(shù)計(jì)算：x=（BTPB）-1BTPJ；PVV計(jì)算：V-1PV=JTPV+xTBTPV；權(quán)函數(shù)式：=Fx；平差值的權(quán)倒數(shù)計(jì)算：1/P=FT（BTPB）-1F；公式中各種符號的含義：觀測向量L；相應(yīng)的權(quán)陣P；誤差方程的系數(shù)矩陣B；未知數(shù)的

4、改正數(shù)x；常數(shù)陣J；常數(shù)向量B0；未知數(shù)的近似值X0；權(quán)函數(shù)的系數(shù)陣F。1.2條件平差條件方程式：AV+W=0；W=AL+A0；法方程：AP-1ATK+W=0；改正數(shù)計(jì)算：V=P-1ATK；PVV計(jì)算：V-1PV=WTK；權(quán)函數(shù)式：=Fi；平差值的權(quán)倒數(shù)計(jì)算：1/P=FTP-1F（AP-1F）T（AP-1AT）-1AP-1F；公式中各種符號的含義：觀測向量L；相應(yīng)的權(quán)陣P；條件方程的系數(shù)矩陣A；條件改正數(shù)V；條件閉合差W；法方程聯(lián)系數(shù)矩陣K；權(quán)函數(shù)的系數(shù)陣F。2計(jì)算方法不難看出，以上平差計(jì)算的數(shù)學(xué)模型，全都是以矩陣的形式給定的。如果掌握了矩陣計(jì)算的方法，所有的計(jì)算將迎刃而解。由于測量計(jì)算作業(yè)過

5、程中采用不同的數(shù)學(xué)模型，以及圖形條件的多樣化，對于誤差方程或者條件方程的產(chǎn)生，在本文所述的解算方案當(dāng)中，還不能自動完成；當(dāng)然，可以利用Excel中的VBA功能編程處理，但將可能給計(jì)算者增加了難度。在此僅就手工生成誤差方程組或者條件方程組之后，如何使用Excel內(nèi)置函數(shù)進(jìn)行矩陣計(jì)算的方案進(jìn)行闡述。2.1數(shù)據(jù)的輸入將系數(shù)矩陣的數(shù)據(jù)填入電子表格的相應(yīng)區(qū)域中。2.2矩陣的轉(zhuǎn)置（1）選中電子表格中系數(shù)矩陣的數(shù)據(jù)區(qū)域，點(diǎn)擊右鍵，選擊復(fù)制菜單選項(xiàng)。（2）在電子表格中點(diǎn)擊系數(shù)矩陣數(shù)據(jù)區(qū)域以外的任意單元格，點(diǎn)擊右鍵，選擊“選擇性粘帖”菜單項(xiàng)。（3）點(diǎn)擊“選擇性粘帖”菜單項(xiàng)，出現(xiàn)“選擇性粘帖”對話框；該對話框有三

6、部分選項(xiàng)，第一部分“粘帖”，選中“全部”選項(xiàng)，第二部分“運(yùn)算”，選中“無”選項(xiàng)，第三部分選中“轉(zhuǎn)置”選項(xiàng)，單擊確定，即可完成系數(shù)矩陣數(shù)據(jù)的轉(zhuǎn)置操作。2.3矩陣乘運(yùn)算（1）選中電子表格中的空白區(qū)域，該區(qū)域的行數(shù)、列數(shù)等于矩陣相乘所得矩陣的行數(shù)、列數(shù)，點(diǎn)擊工具欄中的fx（函數(shù)）工具按鈕。（2）彈出插入函數(shù)的對話框，在該對話框的左邊的函數(shù)分類中選擇“數(shù)學(xué)與三角函數(shù)”，在右邊的函數(shù)名中選中“MMULT”，單擊確定。（3）彈出函數(shù)參數(shù)輸入的對話框，該對話框提示輸入兩組參數(shù)，第一個參數(shù)Array1欄中輸入第4步驟中轉(zhuǎn)置矩陣區(qū)域的行列號，在Array2欄中輸入系數(shù)矩陣區(qū)域的行列號可以不用鍵盤輸入，而用Arr

7、ay*內(nèi)右端的按鈕，回到表格視圖中用鼠標(biāo)涂選表格區(qū)域，按一下回車鍵即可；在表格視圖的公式欄應(yīng)該有“=MMULT（轉(zhuǎn)置矩陣區(qū)域：系數(shù)矩陣區(qū)域）”的描述。（4）同時按住Shift和Ctrl兩鍵，按下回車鍵，這時，第1步驟選定的區(qū)域內(nèi)的單元格所顯示的結(jié)果即為聯(lián)系方程的系數(shù)陣。2.4矩陣求逆運(yùn)算（1）選中電子表格中的空白區(qū)域，其行列數(shù)與系數(shù)矩陣相同，點(diǎn)擊工具欄中的fx工具按鈕。（2）在彈出的對話框中左邊的函數(shù)分類選取“數(shù)學(xué)與三角函數(shù)”，右邊選擇函數(shù)名“MINVERSE”，單擊確定。（3）在彈出的參數(shù)選擇對話框中Array1中輸入系數(shù)矩陣，在表格視圖的公式欄中也應(yīng)該有“=MINVERSE（系數(shù)矩陣區(qū)域）

8、”的描述。（4）同時按住Shift和Ctrl兩鍵，按下回車鍵，第1步驟選定區(qū)域內(nèi)的單元格所顯示的結(jié)果即為系數(shù)矩陣的逆矩陣。熟練掌握上述矩陣計(jì)算的步驟、方法，解決測量平差之線性方程組解算的問題就會變得簡單、容易了。3注意事項(xiàng)在計(jì)算過程中，受Excel軟件本身的約束和限制，可能會帶來不可預(yù)想的結(jié)果，因此需要注意一下幾個事項(xiàng)：（1）受電子表格列數(shù)的限制，Excel最多可以計(jì)算有256列的矩陣。若要求解有更多列的矩陣，可以利用分塊矩陣的辦法，或者VBA語言編程進(jìn)行解決。（2）改變計(jì)算結(jié)果的精度，可作以下操作：格式菜單單元格數(shù)字?jǐn)?shù)值小數(shù)位數(shù)，選定相應(yīng)的數(shù)值即可。若需用雙精度計(jì)算，設(shè)定小數(shù)位為15位，即可

9、得到雙精度的求解結(jié)果。（3）有關(guān)矩陣的計(jì)算，還有其他的運(yùn)算功能，如相加、相減等，均可以在Excel編輯功能的復(fù)制與選擇性粘貼中實(shí)現(xiàn)。4實(shí)例應(yīng)用根據(jù)以上提供的計(jì)算步驟，本文以“后方交會、附加測邊條件”的一個實(shí)際應(yīng)用為例，進(jìn)一步說明Excel內(nèi)置函數(shù)在測量平差中的應(yīng)用。其中涉及矩陣運(yùn)算的環(huán)節(jié)均利用Excel的內(nèi)置函數(shù)求解（計(jì)算過程中邊長觀測以及角度觀測值的權(quán)系數(shù)均取1；單位為米、秒）。4.1已知數(shù)據(jù)及觀測成果（待定點(diǎn)：P）測站觀測方向觀測點(diǎn)縱坐標(biāo)觀測點(diǎn)橫坐標(biāo)觀測方向角觀測邊長P仙鶴頂2431773.087491172.970000000龍珠塔2427738.078494836.670303502南

10、劍2426330.488493695.187413750YN2562430554.354485826.9571753147147.4054.2近似值計(jì)算取待定點(diǎn)P的近似坐標(biāo)為：X=2430597.925；Y=485967.775；并解算得以下近似的邊長、方位角：待定點(diǎn)已知點(diǎn)邊長方位角P仙鶴頂5336.203771640.03龍珠塔9318.5851075220.41南劍8827.4521185434.29YN256147.4052524826.004.3方向、邊長誤差方程系數(shù)及常數(shù)項(xiàng)由公式aij=y*/Sij2；bij=-x*/Sij2可計(jì)算出x項(xiàng)和y項(xiàng)的系數(shù)。對S=SQR（x2+y2）求偏導(dǎo)

11、并推導(dǎo)出邊長條件的線性方程，求得邊長條件的系數(shù)。由觀測值和近似方位角，解算得誤差方程的常數(shù)項(xiàng)J=BX0+B0L.根據(jù)誤差方程的數(shù)學(xué)模型V=Bx+J;其系數(shù)矩陣和常數(shù)陣如下：待定點(diǎn)已知點(diǎn)x項(xiàng)系數(shù)y項(xiàng)系數(shù)常數(shù)項(xiàng)V01P仙鶴頂V02龍珠塔21.16.838.4V03南劍20.511.34.3V04YN2561336.8413.61.0Vs（邊長條件）YN2560.31.00.04.4改化后的角度、邊長誤差方程系數(shù)及常數(shù)項(xiàng)待定點(diǎn)（自）方向（到）方向x項(xiàng)系數(shù)y項(xiàng)系數(shù)常數(shù)項(xiàng)改正數(shù)V1P仙鶴頂龍珠塔16.615.338.40V2仙鶴頂南劍0V3仙鶴頂YN256137

12、4.5422.11.00Vs（邊長）PYN2560.31.00.00.0（*從此開始，把方程組的系數(shù)陣、常數(shù)陣的系數(shù)鍵入EXCEL的相應(yīng)單元格中，并進(jìn)行相應(yīng)計(jì)算。）4.5矩陣轉(zhuǎn)置運(yùn)算（1）將系數(shù)陣的數(shù)據(jù)填入電子表格的（A1：B3）區(qū)域中；將常數(shù)項(xiàng)填入（D1：D3）區(qū)域中；如下圖：（2）選?。ˋ1：B3）區(qū)域，做編輯復(fù)制的菜單操作；（3）鼠標(biāo)點(diǎn)擊一下A6單元格；（4）做編輯菜單下的選擇性粘貼操作（5）在選擇性粘貼的對話框中，選擇：粘貼的全部，運(yùn)算的無，以及轉(zhuǎn)置共三項(xiàng)；然后確定，便完成了矩陣的轉(zhuǎn)置。如下圖：4.6法方程組生成（矩陣乘運(yùn)算）（1）選中（A9：B10）區(qū)域；點(diǎn)擊插入函數(shù)的工具按鈕fx；

13、（2）彈出插入函數(shù)的對話框，在對話框的左邊的函數(shù)分類中選擇“數(shù)學(xué)與三角函數(shù)”，在右邊的函數(shù)名中選中“MMULT”，單擊確定。（3）根據(jù)BTPB，輸入函數(shù)的參數(shù)：在Array1欄中輸入A6：C7；在Array2欄中輸入A1：B3；此時表格視圖的公式欄中應(yīng)有MMULT（A6:C7，A1:B3）的描述。同時按住SHIFT和CTRL鍵不放，按下回車鍵，這時選中的（A9：B10）區(qū)域便有了結(jié)果。如下圖（4）、計(jì)算BTPJ時使用類似的方法，即：選中（A17：A18）區(qū)域，在Array1欄中輸入A6：C7；在Array2欄中輸入D1：D3；此時表格視圖的公式欄中應(yīng)有MMULT（A6:C7，D1:D3）的描述

14、。同時按住SHIFT和CTRL鍵不放，按下回車鍵，這時選中的（A17：A18）區(qū)域便有了結(jié)果。如下圖4.7法方程組解算（矩陣求逆）顧及全站儀的觀測精度以及現(xiàn)場的情況，擬將邊長觀測值作為附加約束條件，進(jìn)行有附加條件的間接平差。則有附加條件間接平差的聯(lián)系方程的系數(shù)為：xyKL1889821.65580770.990.3663.10580770.99178794.541.0250.560.31.00.00.0那么，須作：（1）在單元格A19中填入0.0（2）在（C9：C11）；（A11：C11）兩區(qū)域鍵入K的系數(shù)。如下圖（3）選中（A13：C15）區(qū)域，點(diǎn)擊插入函數(shù)的工具按鈕fx；在彈出的對話框中左

15、邊函數(shù)分類選擇“數(shù)學(xué)與三角函數(shù)”，右邊選擇函數(shù)名“MINVERSE”，單擊確定。輸入函數(shù)的參數(shù)：在Array1欄中輸入A9：C11；此時表格視圖欄中應(yīng)有MMULT（A9:C11）的描述。同時按住SHIFT和CTRL鍵不放，按下回車鍵，這時選中的（A9：A10）區(qū)域便得到計(jì)算的結(jié)果。如下圖這就是聯(lián)系（法）方程組系數(shù)陣的逆陣（即權(quán)逆陣）。4.8方程的解根據(jù)公式：x=（BTPB）-1（BTPJ）進(jìn)行計(jì)算。選中（A21：A23）區(qū)域；在Array1欄中輸入A13：C15；在Array2欄中輸入A17：A19；此時表格視圖的公式欄中應(yīng)有MMULT（A13：C15，A17:A19）的描述。同時按住SHIFT和CTRL鍵不放，按下回車鍵，這時選中的（