1、Excel數值方法及其應用技巧徐寧迎浙江大學動物科學學院線性方程組求解線性方程組的一般形式mnmnmmnnnnbxaxaxabxaxaxabxaxaxa22112222212111212111用矩陣表示BAX A為系數矩陣，X為解向量，B為常數項向量mnmmnnaaaaaaaaaA212222111211mxxxX21mbbbB21利用矩陣法求解線性方程組BAX1線性方程組的簡單迭代求解法（雅可比法）)(1)(1)(11122112232312122211313212111nnnnnnnnnnnnnbxaxaxaaxbxaxaxaaxbxaxaxaax建立相應的迭代式)(1)(1)(11122

2、111223231212212113132121111nknnnknknnnknknnkkkknnkkkbxaxaxaaxbxaxaxaaxbxaxaxaax求解下列線性方程組72202519103928321321321xxxxxxxxx化為迭代形式20722022051019101103894181213312321xxxxxxxxx用矩陣表示20251103218A321xxxX72199B超定線性方程組mnmnmmnnnnbxaxaxabxaxaxabxaxaxa22112222212111212111 當線性方程組中mn時，稱為超定線性方程組，超定方程組一般沒有精確解，可用最小二乘原

3、理，求它們的近似解用矩陣形式給出求解公式如下BAAXATT請求下列超定方程組的解10312319353268333293621841321321321321xxxxxxxxxxxx312313532633329621841A10983B解7119138253701382346965537096541593123135326333296218413135336235331831262941AAT1075125899109833135336235331831262941BAT1075711913825370125138234696589953709654159321321321xxxxxxxxx用

4、高斯-賽德爾迭代法解得上述方程組的解為2017. 09991. 01667. 0321xxx線性規劃線性規劃方程組的一般形式nnxcxcxcSMin2211)(0, 0, 02122112222212111212111nmnmnmmnnnnxxxbxaxaxabxaxaxabxaxaxannxcxcxcSMax2211)(或 上式中cj是原料價格（元/kg），xj是各種飼料原料，S是飼料配方成本，aij是j種飼料原料第i種營養成分的含量或對飼料原料進行約束的系數。i=1，2m，表示在畜禽飼料配方的數學模型中所考慮營養成分的數目。j=1，2，n，表示在畜禽飼料配方的數學模型中所采用的原料數目。

5、表示 ，=，和 。 對于上述線性規劃模型，通常采用單純形方法或修正單純形方法求解。EXCEL電子表格對線性規劃問題進行求解的同時，還將給出靈敏度分析及各種營養成分的影子價格。關于這方面的內容請參考專門的書籍。 某人想從柳丁、木瓜、香蕉和胡蘿卜獲取維生素A、B、C、D，假設柳丁每個20元、木瓜30元、香蕉每根15元和胡蘿卜每個25元，每天維生素A、B、C、D的最少需要量分別為500、250、600和300國際單位，首先將以上情況列成下表。柳丁（X1）木瓜（X2）香蕉（X3）胡蘿卜（X4）最少需要量維生素 A5501080500維生素 生素 C90203030600維生素

6、 D10151050300價格20301525根據上表可以列出以下線性規劃方程432125153020XXXXZMin30050101510600303020902502040301550080105054321432143214321XXXXXXXXXXXXXXXX目標函數限制條件 某公司要建一個萬頭豬場，現有1100平方米豬欄，350萬飼料資源，98000工時勞力，130萬元種豬經費，已知養一頭母豬，需要種豬費600元，豬欄面積4平方米，年耗飼料1700千克，勞力115工時，年獲利250元；飼養一頭公豬，需要種豬費800元，豬欄面積9平方米，年耗飼料730千克，勞力195工時，年獲利150

7、元；飼養一頭肉豬，需要苗豬費80元，豬欄面積0.8平方米，年耗飼料250千克，勞力2.5工時，年獲利70元。問飼養多少頭母豬、公豬、肉豬才能使盈利最大？母豬（X1）公豬（X2）肉豬（X3）生產要素限制量種豬費（元）600800801300000豬欄面積（平方米）490.811000年耗飼料量（千克）17007302503500000需勞動力（工時）1151952.598000年盈利（元）25015070先將以上情況列成下表32170150250XXXZMax目標函數60020980005 . 219511535000002507301700110008 . 094130000080800600

8、12321321321321XXXXXXXXXXXXXX限制條件 豬飼料配方實例：現有玉米（x1）、四號粉（x2）、麩皮（x3）、菜籽餅（x4）、豆粕（x5）、進口魚粉（x6）、磷酸氫鈣（x7）、石粉（x8）和食鹽（x9）等9種原料，它們的價格分別為每公斤1.3、1.26、1.24、1.2、1.58、4.8、2.0、0.24和1.6元，現以60kg至90kg生長肥育豬階段的各種營養需要為約束條件來計算最優飼料配方，首先根據線性規劃的原理列出數學模型如下：9876543216 . 124. 00 . 28 . 458. 12 . 124. 126. 13 . 1)(xxxxxxxxxSMin0,

9、 0, 099. 0003. 01 . 014. 007. 071 . 58 .119 . 88 . 26 . 17 . 01876. 261. 007. 192. 052. 027. 08 . 0382 .2387. 332. 065. 008. 008. 002. 032. 042. 23 . 15 . 139. 05 . 038. 063. 09 . 445. 23 . 158. 052. 024. 06 . 01876. 261. 007. 192. 052. 027. 06 . 0382 .2387. 332. 065. 008. 008. 002. 0148 .62436 .387

10、 .155 .127 . 89 . 298. 215. 353. 224. 22 . 341. 321987654321932454321765432187654321654321654321765432187654321654321654321nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx數學模型的基本數據輸入電子表格的工作表中的形式“規劃求解參數”對話框“添加約束”對話框“規劃求解選項”對話框“規劃求解結果”對話框目標搜尋目標搜尋 EXCEL提供的目標搜尋法主要是求方程式的根，特別是非

11、線性方程式，例如：363)(23XXXXF 選一單元格（B1），輸入以上公式；然后再選一放根的單元格（A1），選取“插入”、“名稱”、“定義”，在現有名稱框輸入X，按Enter鍵，單元格B1顯示6；選取“工具”、“單變量求解”對話框，在“目標單元格”框輸入$B$1，在“目標值”框輸入3，在“變量單元格”框輸入$A$1，選擇確定。單因素一元協方差分析協方差分析簡表的形式校正增重的方差分析變因dfSSxSSySPxybyxdfSSMSF總變異31691381889428156307427.65組間3675523183880組內286238316576242750.389277129.13264.0

12、4校正處理間3298.5299.510.377協方差分析簡表的構建元格輸入形式G3=B3-1G5=B5-1G6=G3-G5H3=D3-（E3E3）C3H5=D5-（E5E5）C5H6=D3-D5I5=H5G5I6=H6G6J6=I6I5寫入公式后的協方差分析簡表的形式 協方差分析實例：在研究3種不同飼料對豬的增重效果時，用24頭始重不一樣的幼豬分為3組，每組8頭進行試驗，各組的增重結果見下表。分組變量原始數據Ix1513111212161417y8583657680918490IIx1716181821221918y9790100951031069994IIIx2224202325273032

13、y89918395100102105110計算后所得的組平均數和總和分組變量組平均數組總和Ix13.75110y81.75654IIx18.625149y98784IIIx25.375203y96.875775原始數據經計算后所得的平方和和乘積和變因自由度x 平方和y 平方和乘積和總的23720.52555.9581080.75組間2545.251317.583659.875組內21175.251238.375420.875各項乘積和的計算1、根據基本公式進行計算；2、計算相關系數，然后除以二個變量平方和的幾何平均數；3、計算樣本協方差，然后用自由度與樣本協方差相乘；4、計算總體協方差，然后用

14、樣本含量與總體協方差相乘 第四種方法最為簡單，先將原始數據根據x變量和y變量排成2列（或2行），按原來順序后一組數據接在前一組數據后排列，本例可將x和y變量分別排在A和B列，全部數據位于A1：B24的區域中，其中A1：B8為第一組原始數據，A9：B16為第二組數據，A17：B24為第三組數據，然后用粘帖函數COVAR來計算總體協方差，再用樣本含量與總體協方差相乘就得到了乘積和。利用粘帖函數COVAR每次只能計算出一個總體協方差，使用該函數的次數是因素的水平數加1，本例共分3個水平需要使用4次，從而計算出各組的乘積和及總的乘積和，各組乘積和之和就為組內乘積和，那么組間乘積和就可以由總的乘積和減去

15、組內乘積和求得。計算本例總的總體協方差的對話框計算各組總體協方差時對話框Array1和Array2中輸入的區域及計算結果分組Array1Array2協方差樣本含量乘積和IA1：A8B1：B813.81258110.5IIA9：A16B9：B168.125865IIIA17：A24B17：B2430.671888245.375求和420.875協方差分析結果數據庫統計粘貼函數DCOUNT計算數據庫中列符合條件的單元格個數DCOUNTA計算數據庫中列符合條件的非空白單元格個數DGET由數據庫中摘錄符合條件的單一記錄DMAX計算數據庫中符合條件列中的最大值DMIN計算數據庫中符合條件列中的最小值DA

16、VERAGE計算數據庫中符合條件列中的平均值DPRODUCT計算數據庫中符合條件列的值相乘積DSUM計算數據庫中符合條件列的總和DSTDEV計算數據庫中符合條件列的樣本標準差DVAR計算數據庫中符合條件列的樣本方差DSTDEVP計算數據庫中符合條件列的總體標準差DVARP計算數據庫中符合條件列的總體方差數值計算的粘貼函數COUNT計數MAX求最大值MIN求最小值SMALL數據集中第 K 個最小值LARGE數據集中第 K 個最大值MOD求余數QUOTIENT求商PRODUCT求數值連乘離散概率分布的粘貼函數BINOMDIST二項分布CRITBINOM累積二項分布（依臨界值，找最小整數 k）HYP

17、GEOMIST超幾何分布NEGBINOMDIST負二項分布POISSON普哇松分布連續概率分布的粘貼函數BETADIST概率分布函數BETAINV概率分布函數的反函數EXPONDIST指數分布GAMMADIST分布GAMMAINV分布函數的反函數LOGNORMDIST對數正態累積分布LOGINV對數正態累積分布的反函數NORMDIST正態分布NORMINV正態累積分布的反函數NORMSDIST標準正態累積分布NORMSINV標準正態累積分布的反函數WEIBULL韋布分布回歸分析的其它粘貼函數LINEST線性估計最小二乘法LOGEST曲線估計回歸分析GROWTH成長分析TREND趨勢分析FORE

18、CAST線性趨勢預測INTERCEPT線性回歸截距分析SLOPE線性回歸斜率分析STEYX預測 Y 值標準誤差階乘、排列及組合的粘貼函數FACT階 乘FACTDOUBLE雙 倍 階 乘PERM UT排 列COM BIN組 合M ULTINOM IAL多 項 選 取 分 布 函 數求和計算的粘貼函數SUM求 和SUMPRODUCT計 算 數組 乘積 和SUMSQ計 算 變量 平方 和SERIESSUM計 算 冪次 序列 和SUMX2MY2計 算 二數 組平 方和 的差SUMX2PY2計 算 二數 組平 方和 的和SUMXMY2計 算 二數 組差 的平 方和矩陣運算粘貼函數M DETERM求 行 列 式 值M M ULT矩 陣 相 乘TRANSPOSE矩 陣 轉 置M INVERSE求 逆 矩 陣SUM PRODUCT求 矩 陣 乘 積 和數進制的轉換BIN2DEC二進制轉換為十進制BIN2OCT二進制轉換為八進制BIN2HEX二進制轉換為十六進制OCT2BIN八進制轉換為二進制OCT2DEC八進制轉換為十進制OCT2HEX八進制轉換為十六進制DEC2BIN十進制轉換