基于Excel三次樣條函數(shù)的求解

張遠雙

（武漢船舶職業(yè)技術(shù)學(xué)院，湖北武漢 430050）

在計算機輔助船舶設(shè)計和建造中，三次樣條函數(shù)是船體型線典型的數(shù)學(xué)表達。在平面直角坐標系xoy中，給定若干個型值點Pi（xi，yi）（i＝0，1，2，…，n），船體型線可用三次樣條函數(shù)描述為［1］：

式中：mi·i＋1為一階差商；

ci為三次樣條函數(shù)的系數(shù)，根據(jù)節(jié)點關(guān)系式和端點條件，系數(shù)ci的求解表達式可表示為：

用三次樣條函數(shù)描述船體型線的關(guān)鍵在于求解三次樣條函數(shù)的系數(shù)。

1 線性方程組求解方法分析

系數(shù)ci的表達式（1）、（2）、（3）可化為n元一次方程組，根據(jù)數(shù)學(xué)知識將線性方程組改寫成矩陣方程AX＝B，系數(shù)方陣A為平行于對角線的三列稀疏矩陣，若其逆陣存在（即系數(shù)行列式的值不為零），則方程組有唯一解X＝A－1B。

用常規(guī)追趕法［2］求解上述n元一次線性方程組，隨n值的增大，求解過程愈復(fù)雜，具有相當難度。Excel是目前非常流行且計算功能強大的電子表格軟件，使用Excel的規(guī)劃求解工具或函數(shù)功能求解方程組可大大提高工作效率、保證求解精度?，F(xiàn)以150t冷藏船3500WL為例，說明基于Excel系數(shù)ci求解的方法和過程。根據(jù)船體型線圖，已知條件如表1所示。

表1 型值和端點條件

2 用Excel的規(guī)劃求解工具求解

2.1 準備計算

為更好地使用Excel求解線性方程組，先進行一階差商mi·i＋1和線性方程組增廣矩陣的計算。

（1）一階差商的計算

一階差商mi·i＋1的計算表達式為：mi·i＋1＝利用相關(guān)Excel公式求得各一階差商大小如表2所示。

表2 差商m值的計算

（2）線性方程組增廣矩陣的計算

根據(jù)三次樣條函數(shù)系數(shù)ci的表達式，利用相關(guān)Excel公式求得線性方程組的增廣矩陣如表3所示，其中A9：K19單元格區(qū)域為系數(shù)矩陣，A9：L19單元格區(qū)域為增廣矩陣。

本例系數(shù)行列式的Excel計算公式可表達為“＝MDETERM（A9：K19）”，經(jīng)計算其值為6.956×1045。根據(jù)克萊姆法則，該三次樣條函數(shù)系數(shù)線性方程組有唯一解。

在系數(shù)ci的求解表達式（1）、（2）、（3）中，將方程等號右邊的常數(shù)項移至左邊，能使左邊多項式的值為零時的ci值即為線性方程組的解。

2.2 “規(guī)劃求解”命令宏的加載和設(shè)置

在默認狀態(tài)下，Excel的【工具】菜單中無【規(guī)劃求解】選項。加載方法：單擊【工具】菜單的【加載宏】選項，在彈出的【加載宏】對話框中選中【規(guī)劃求解】選項，單擊【確定】按鈕。

打開【規(guī)劃求解選項】對話框，根據(jù)求解需要設(shè)置【最長運算時間】、【迭代次數(shù)】、【精度】、【允許誤差】和【收斂度】等選項。

2.3 用規(guī)劃求解工具求解樣條函數(shù)系數(shù)［3］

如表4、5所示，B21：B31單元格區(qū)域用于存放系數(shù)的計算結(jié)果；C21：C31單元格區(qū)域中的各單元格計算公式分別設(shè)置為“＝A9＊B21＋B9＊B22－L9”、“＝A10＊B21＋B10＊B22＋C10＊B23－L10”、…和“＝J19＊B30＋K19＊B31－L19”，用于表示方程組左邊多項式的值，規(guī)劃求解前多項式的值如表4所示。

表4 規(guī)劃求解（1）

單擊【工具】菜單中的【規(guī)劃求解】選項，彈出【規(guī)劃求解參數(shù)】對話框，正確設(shè)置【設(shè)置目標單元格】、【等于】、【可變單元格】和【約束】等項目，如圖1所示，再單擊【求解】按鈕；在彈出的【規(guī)劃求解結(jié)果】對話框中，選取【保存規(guī)劃求解結(jié)果】，最后單擊【確定】按鈕。

經(jīng)規(guī)劃求解，三次樣條函數(shù)系數(shù)的計算結(jié)果如表5中的B21：B31單元格區(qū)域所示。注意：目標單元格的值和約束條件的設(shè)置必須保證各多項式的值為零。

圖1 規(guī)劃求解參數(shù)的設(shè)置

表5 規(guī)劃求解（2）

3 用Excel的函數(shù)功能求解

一階差商mi·i＋1和線性方程組增廣矩陣的計算同2.1.1和2.1.2?？捎肕INVERSE和MMULT函數(shù)求系數(shù)矩陣的逆陣A－1和方程組的解X＝A－1B。

3.1 用MINVERSE函數(shù)求系數(shù)矩陣的逆陣

如表6所示，在Excel工作表中選定A33：K43單元格區(qū)域，插入函數(shù) MINVERSE，打開MINVERSE函數(shù)對話框。

如圖2所示，在【函數(shù)參數(shù)】對話框中的“Array”參數(shù)文本框中選取A9：K19單元格區(qū)域作為計算逆陣的源數(shù)據(jù)區(qū)域，單擊【確定】按鈕，將光標定位在編輯欄中所插入函數(shù)的結(jié)尾處，同時按下組合鍵“Ctrl＋Shift＋Enter”，用數(shù)組公式“｛＝MINVERSE（A9：K19）｝”返回多個計算結(jié)果。A33：K43單元格區(qū)域顯示出逆陣的計算結(jié)果，在如表6所示。

表6 系數(shù)矩陣逆陣的計算和方程組的求解

圖2 “MINVERSE”函數(shù)參數(shù)的設(shè)置

3.2 用MMULT函數(shù)求解樣條函數(shù)系數(shù)

如表6所示，在Excel工作表中選定L33：L43單元格區(qū)域，插入函數(shù) MMULT，打開MMULT函數(shù)對話框。

如圖3所示，在【函數(shù)參數(shù)】對話框中的“Array1”和“Array2”參數(shù)文本框中分別選取A33：K43和L9：L19單元格區(qū)域作為計算系數(shù)ci的源數(shù)據(jù)區(qū)域，單擊【確定】按鈕，將光標定位在編輯欄中所插入公式的結(jié)尾處，同時按下組合鍵“Ctrl＋Shift＋Enter”，用數(shù)組公式“｛＝MMULT（A33：K43，L9：L19）｝”計算出系數(shù)ci的計算結(jié)果，如表6所示中L33：L43單元格區(qū)域所示。

圖3 “MMULT”函數(shù)參數(shù)的設(shè)置

討論：本例亦可利用Excel的 MDETERM函數(shù)，根據(jù)克萊姆法則［4］進行求解（求解方法和過程略）。

4 結(jié) 語

在無相關(guān)專業(yè)軟件的情況下，三次樣條函數(shù)的求解是一個非常復(fù)雜且難以求解的專業(yè)問題，用Excel的規(guī)劃求解工具和函數(shù)功能進行計算，不僅能實現(xiàn)復(fù)雜問題的簡單化處理，大大提高求解效率，同時具有計算模型通用性強、計算過程簡明、計算結(jié)果準確等優(yōu)點。

1 徐兆康.船舶建造工藝學(xué)［M］.人民交通出版社，2000：50～54.

2 王勇毅.計算機輔助船體建造［M］.人民交通出版社，1995：41～43.

3 謝國鋒等.Excel 2000中文版入門提高［M］.清華大學(xué)出版社，2000：281～285.

4 同濟大學(xué)數(shù)學(xué)教研室.線性代數(shù)［M］.高等教育出版社，1991：21.