Excel數(shù)值方法及其應(yīng)用技巧

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

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

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

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

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

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

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

8、12321321321321XXXXXXXXXXXXXX限制條件 豬飼料配方實(shí)例：現(xiàn)有玉米（x1）、四號粉（x2）、麩皮（x3）、菜籽餅（x4）、豆粕（x5）、進(jìn)口魚粉（x6）、磷酸氫鈣（x7）、石粉（x8）和食鹽（x9）等9種原料，它們的價格分別為每公斤1.3、1.26、1.24、1.2、1.58、4.8、2.0、0.24和1.6元，現(xiàn)以60kg至90kg生長肥育豬階段的各種營養(yǎng)需要為約束條件來計算最優(yōu)飼料配方，首先根據(jù)線性規(guī)劃的原理列出數(shù)學(xué)模型如下：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數(shù)學(xué)模型的基本數(shù)據(jù)輸入電子表格的工作表中的形式“規(guī)劃求解參數(shù)”對話框“添加約束”對話框“規(guī)劃求解選項”對話框“規(guī)劃求解結(jié)果”對話框目標(biāo)搜尋目標(biāo)搜尋 EXCEL提供的目標(biāo)搜尋法主要是求方程式的根，特別是非

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

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

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

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

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

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

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

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