Excel数值方法及其应用技巧

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十进制转换