EXCEL解方程组实用教案.ppt

本章(běn￿zhānɡ)主要内容3.1 Excel3.1 Excel矩阵计算矩阵计算3.2 3.2 解线性联立方程解线性联立方程(lián lì fānɡ (lián lì fānɡ chénɡ)chénɡ)3.2.1 3.2.1 行列式法行列式法3.2.2 3.2.2 矩阵法矩阵法3.3 Newton-Raphson3.3 Newton-Raphson迭代法迭代法 解非线性方程组解非线性方程组3.4 3.4 方程组的规划求解方程组的规划求解 第1页/共55页第一页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•1.￿Excel的矩阵格式•矩阵是数据（数值和变量）按行和列排成矩形(jǔxíng)的阵列：•￿￿￿￿•Excel工作表中用数组表示矩阵，单元格的位置对应•矩阵元素的位置，但标记顺序相反:￿矩阵元素aij是先行•(i)后列(j)，而单元格A3是先列(A)后行(3)第2页/共55页第二页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•1.￿Excel的矩阵格式•￿￿￿￿列矩阵或行矩阵在Excel工作表中放在同一列•或同一行，称为一维数组。

•￿￿￿￿m×n矩阵元素依次(yīcì)放在工作表的一个区域•中，称为二维数组第3页/共55页第三页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•2.￿数组加（减）法•￿￿￿￿进行数组加（减）法运算时，一般(yībān)要求数组•中单元格的个数相同•示例：建立二维数组A1:D3，一维数组F1:I1•一维数组加（减）法：￿￿￿￿￿•1）A1:D1一行数据与F1:I1另一行数组相加，得到的仍是含四个单元格的一行数组，因此必须选定加和结果数组的位置，本例选F3:I3第4页/共55页第四页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•2.￿数组加（减）法•2）键入“＝”，用鼠标选定A1:D1区域，被选区域出现闪动(shǎn￿dònɡ)的虚线框，同时编辑框的等号后出现A1:D1•3）键入“＋”，选中F1:I1区域同样被选区域出现闪动(shǎn￿dònɡ)虚线框，加号后出现F1:I1•4）按Ctrl＋Shift＋Enter键F3:I3区域出现加和结果，编辑框的公式被大括号括住:{=A1:D1+F1:I1}第5页/共55页第五页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•注意：•1）若开始未选定区域F3:I3，而只选中了F3单元格，则完成上述各步计算后在F3出现数值2。

•2）数组公式是一个整体，无论是编辑、移动、清除都必须选取公式包括对所有单元格•3）大括号不用输入，按Ctrl＋Shift＋Enter后自动产生，表明这是数组公式如果输入公式时自己(zìjǐ)加上，Excel将认为输入的是文字•4）若相加的两个数组单元格数目不等，则出现错误信号#N/A第6页/共55页第六页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•注意：•5）若加一常数到一数组，Excel会自动将该常数加到数组的每个单元格中•￿￿￿￿二维数组加法( jiāfǎ)类似于一维数组，此外还有如下特点：二维数组可以与行单元格或列单元格数目相同的一维数组相加第7页/共55页第七页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•示例(shìlì)结果：第8页/共55页第八页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•3.￿数组乘以一常数•￿￿￿数组乘以一常数，得到(dé￿dào)与原数组大小相同的新数组，新数组的每个单元格的值等于原数组单元格的值乘以此常数第9页/共55页第九页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•4. 矩阵乘法• 矩阵A（m×n）与B（n×p）相乘，得一矩阵C（m×p）。

仅当第一个矩阵的列数等于第二个矩阵的行数时，两矩阵才能相乘• 矩阵相乘时，各矩阵元素按一定规则相乘再相加Excel有矩阵相乘的工作(gōngzuò)表函数MMULT(array1，array2）第10页/共55页第十页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•4.￿矩阵乘法•计算( jì￿suàn)规则：•其中i为行数，j为列数•例1.￿MMULT({1,3;7,2},{2,0;0,2})•例2.￿MMULT({3,0;2,0},￿{2,0;0,2})￿•例3.￿MMULT({1,3,0;7,2,0;1,0,0},￿{2,0;0,2})￿第11页/共55页第十一页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算注意：1）输入MMULT函数前要根据乘积矩阵大小（m×p）选定它的单元格区域2）函数名前有等号，输入完毕同时(tóngshí)按Ctrl＋Shift＋Enter3）数组乘法不等于矩阵乘法，它是两个数组对应单元格各自相乘，数组相乘用*，公式写作＝数组1*数组2第12页/共55页第十二页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•5.￿逆矩阵•￿￿￿￿￿行数等于(děngyú)列数的矩阵称为方阵。

若一方阵的主对角元素aii均为1，其他位置上的元素均为0，则该矩阵称为单位矩阵I￿•￿￿￿￿￿￿设有一n阶方阵A和另一n阶方阵B，它们的乘积为：AB=BA=I•￿￿￿则B为A的逆阵，可记为A-1第13页/共55页第十三页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•5.￿逆矩阵•￿￿Excel求逆矩阵的工作表函数为￿￿•￿￿￿￿￿￿￿￿￿￿￿￿￿MINVERSE(array）•例1.￿MINVERSE({4,-1;2,0})•例2.￿MINVERSE({1,2,1;3,4,-1;0,2,0})•例3.￿MINVERSE()为一矩阵，可参与(cānyù)其他矩阵运算第14页/共55页第十四页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•6.￿行列式•行列式是在研究线性方程组的解法中产生的概念(gàiniàn)n阶行列式可表示为：￿￿￿￿￿第15页/共55页第十五页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•6.￿行列式•￿￿￿行列式通常用D表示，其元素用竖直线(zhíxiàn)而非圆括号括住•￿￿￿方阵与行列式是两个不同的概念：n阶方阵是n2个数按一定方式排列成的数据表；n阶行列式是按一定的运算规则得到的一个确定值。

第16页/共55页第十六页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•6.￿行列式•Excel计算(jì￿suàn)行列式值的函数：•￿￿￿￿￿￿￿￿￿MDETERM(array)•例1￿MDETERM({1,3,8,5;1,3,6,1;1,1,1,0;7,3,10,2})第17页/共55页第十七页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•7.￿转置矩阵•￿￿￿矩阵A的行和列的元素(yuán￿sù)交换后得到它的转置矩阵A’：第18页/共55页第十八页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•7.￿转置矩阵•1）Excel转置矩阵函数(hánshù)：•￿￿TRANSPOSE(数组）•注：要先根据原矩阵选定转置矩阵区域，且数组函数(hánshù)输入同时按Ctrl＋Shift＋Enter键•2）选择性粘贴－转置•注：粘贴时只需选定区域的左上单元格即可第19页/共55页第十九页，共56页3.1￿Excel￿矩阵(jǔ￿zhèn)运算•7.￿转置(zhuǎn￿zhì)矩阵第20页/共55页第二十页，共56页3.2￿解线性联立方程(lián￿lì￿fānɡ￿chénɡ)线性联立方程：￿￿￿设一组含n个未知数的n个方程：￿￿￿￿￿￿其中xi(x1,￿x2,￿x3,…)￿是实验未知数，ci是实验测量值，aij是系数。

这些方程必须是线性独立(dúlì)的，上式的矩阵表示为：￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿AX=C第21页/共55页第二十一页，共56页3.2￿解线性联立方程(lián￿lì￿fānɡ￿chénɡ)A是系数(xìshù)矩阵：X和C分别为解矩阵和常数矩阵：第22页/共55页第二十二页，共56页3.2.1￿行列式法•根据Cramer法则，线性联立方程有唯一解的条件(tiáojiàn)是其系数行列式为非零值：•此时方程的解为：第23页/共55页第二十三页，共56页3.2.1￿行列式法•￿￿￿￿是方程组的系数行列式，但其中的第i列元素被常数列阵C所取代例如(lìrú)￿￿￿￿￿￿是用常数C列阵取代系数矩阵的第一列元素所得的行列式：第24页/共55页第二十四页，共56页3.2.1￿行列式法•示例：•Excel步骤：•1）输入注释文字和系数数值•2）在E2输入:￿=MDETERM(A2:C4)，得到系数行列式的值－9，说明联立方程(lián￿lì￿fānɡ￿chénɡ)有唯一解第25页/共55页第二十五页，共56页3.2.1￿行列式法3）复制联立方程的系数矩阵(jǔ￿zhèn)A2:C4，粘贴到下边A6:C8、A10:C12和A14:C16。

再复制常数列数组D2:D4，将其分别粘贴到A6:A8、B10:B12、C14:C16得到分子行列式4）分别解出三个分子行列式的值，再根据公式解出x，y，z的值第26页/共55页第二十六页，共56页3.2.1￿行列式法第27页/共55页第二十七页，共56页3.2.2￿矩阵(jǔ￿zhèn)法线性方程的矩阵表示(biǎoshì)经数学变换得：￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿X＝A-1C即解矩阵（X）等于系数矩阵的逆矩阵（A-1）乘于常数矩阵（C）示例：同前联立方程1）输入标注、系数矩阵和常数矩阵2）选中E2:E4，输入公式：=MMULT(MINVERSE(A2:C4),D2:D4)，同时按Ctrl＋Shift＋Enter，可得解第28页/共55页第二十八页，共56页3.2.2￿矩阵(jǔ￿zhèn)法第29页/共55页第二十九页，共56页3.3￿Newton-Raphson迭代法•原理：•￿￿￿￿N-R迭代法属于间接解法，可用于非线性方程组原理类似于一元方程求解(qiú￿jiě)然而因有不止一个自变量，导数变为偏导，迭代公式成为迭代增量的线性方程组•￿￿￿•￿￿￿￿设一非线性方程组：第30页/共55页第三十页，共56页。

3.3￿Newton-Raphson迭代法•原理：￿￿￿￿￿•￿￿￿￿对方程(fāngchéng)组的每一个方程(fāngchéng)在其近似解￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿处用Teller级数展开，只取线性项得：第31页/共55页第三十一页，共56页3.3￿Newton-Raphson迭代法•原理：￿￿￿￿￿•￿￿￿￿其中(qízhōng)￿￿￿￿￿￿是方程￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿在￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿处的一阶偏导数上式为△xi的线性方程组：第32页/共55页第三十二页，共56页3.3￿Newton-Raphson迭代法•原理：￿￿￿￿￿•￿￿￿￿将初始值￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿代入函数fi和偏导数￿￿￿￿￿￿，然后用解线性方程组的方法计算得到￿￿￿￿￿￿￿，从而(cóng￿ér)得到自变量xi的第一次迭代值：•继续迭代，可得：第33页/共55页第三十三页，共56页3.3￿Newton-Raphson迭代法•原理：￿￿￿￿￿•￿￿￿￿直至：•￿￿•￿￿￿￿￿￿￿￿为一小正数•￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿•￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿即为满足指定(zhǐdìng)精度的原方程的解。

第34页/共55页第三十四页，共56页3.3￿Newton-Raphson迭代法•示例(shìlì)：￿￿￿￿￿•方程组：•￿￿￿￿•每个方程对每一自变量的偏导数为：第35页/共55页第三十五页，共56页3.3￿Newton-Raphson迭代法•示例(shìlì)：￿￿￿￿￿•￿￿￿￿于是有线性方程组（1）：第36页/共55页第三十六页，共56页3.3￿Newton-Raphson迭代法•Excel解方程组步骤：￿￿￿￿￿•1）A列标记迭代次数在A2、A3、A4输入x1:0、￿x2:0、￿x3:0后，选定A2:A4区域，拖拽填充柄到适当位置冒号后的数字表示迭代次数•2）B2、B3、B4分别(fēnbié)输入x1、x2、x3的初始值0.5•3）C2:E4单元格区域输入线性方程组（1）的系数矩阵Δxi的系数是含xi的数学表达式第37页/共55页第三十七页，共56页3.3￿Newton-Raphson迭代法•Excel解方程组步骤：￿￿￿￿￿•4）F2:F4区域输入常数列矩阵根据式(1)，F2输入=-(B2^2+B3^2+B4^2-1)/2，F3单元格输入=-(2*B2^2+B3^2-4*B4)/2，F4输入•￿￿￿￿￿￿=-(3*B2^2-4*B3+B4^2)/2。

•5）用矩阵法解线性方程式（1）：选定(xuǎn￿dìnɡ)单元格G2:G4，输入公式：=MMULT(MINVERSE(C2:E4),F2:F4），同时按Ctrl+Shift+Enter，得到第一次迭代改变量Δxi1￿第38页/共55页第三十八页，共56页3.3￿Newton-Raphson迭代法•Excel解方程组步骤：￿￿￿￿￿•6）用矩阵(jǔ￿zhèn)加法在B5:B7填入第一次迭代后的自变量xi1￿：选定B5:B7，输入公式=B2:B4+G2:G4，按Ctrl＋Shift＋Enter•7）重复解线性方程组过程：选取C2:G4，将填充柄向下拖3行在G4:G7得到第二次迭代改变量Δxi1￿•8）重复迭代：选B5:G7，向下拖填充柄直至两次迭代改变量小于10－15第39页/共55页第三十九页，共56页3.3￿Newton-Raphson迭代法第40页/共55页第四十页，共56页3.4￿规划(guīhuà)求解•规划求解解方程组是单变量求解解一元方程的延伸•目标单元格是方程组中任一方程所在，目标值是该方程的常数可变单元格是方程组的自变量所在•约束为方程组中其余方程规划求解”改变可变单元格的值，使得目标值与约束均得到(dé￿dào)满足。

第41页/共55页第四十一页，共56页3.4￿规划(guīhuà)求解•3.4.1￿规划求解解线性方程组•￿￿￿以前面(qián￿mian)的线性方程组为例：•￿￿￿•￿￿￿￿x，y，z是可变单元格变量，将F1的常数作为目标单元格的数值，F2和F3的常数作为约束条件任意设置可变单元格的值，目标是寻找满足约束条件 (6,3)，并使目标值为0的x，y，z第42页/共55页第四十二页，共56页3.4￿规划(guīhuà)求解•Excel步骤(bùzhòu)：•1）B2:D2输入未知数初始值(任意数)1,1,1•2）根据F1式，在B4单元格输入公式：＝2*B2+C2-D2；根据F2，在C4输入：＝B2-C2+D2；根据F3，在D4输入：=B2+2*C2+D2•3）打开“工具”菜单，单击“规划求解”，出现“规划求解参数”对话框￿￿￿第43页/共55页第四十三页，共56页3.4￿规划(guīhuà)求解•Excel步骤：•4）“设置(shèzhì)目标单元格”输入方程F1所在位置：$B$4，“等于”栏设置(shèzhì)目标值为0可变单元格”编辑框输入未知数所在单元格：$B$2:$D$2第44页/共55页第四十四页，共56页。

3.4￿规划(guīhuà)求解•Excel步骤：•5）单击“约束”选项区的“添加”按钮，出现添加约束对话框，在“单元格引用位置”处输入(shūrù)方程F2的位置：$C$4，用下拉箭头选取等号“＝”，在“约束值”处输入(shūrù)F2的常数6单击“添加”，给出新的添加约束对话框，继续增“单元格引用位置”处输入(shūrù)F3的位置：$D$4，选“＝”，约束值为3单击“确定”，回到“规划求解参数”对话框￿￿￿第45页/共55页第四十五页，共56页3.4￿规划(guīhuà)求解第46页/共55页第四十六页，共56页3.4￿规划(guīhuà)求解6）约束条件将出现在“约束”列表(liè￿biǎo)区：$C$4=6和$D$4=3若约束条件设置有误，可通过“更改”或“删除”对约束条件进行改变7）所有参数设置好后，按“求解”，得到相应结果：第47页/共55页第四十七页，共56页3.4￿规划(guīhuà)求解•3.4.2￿解非线性方程组•￿￿￿多元一次线性方程比较(bǐjiào)容易，对初始值设定无严格要求若方程为非线性，则必须设置不同初始值，才能保证解的可信性•例：在Cd(ClO4)2和NaCN的水溶液中有如下平衡：第48页/共55页第四十八页，共56页。

3.4￿规划(guīhuà)求解￿￿￿根据上述三个解离平衡(pínghéng)，由物料平衡(pínghéng)和电荷平衡(pínghéng)可得到含三个未知数[CN]、[Cd2+]和[H+]的三个方程：￿￿￿第49页/共55页第四十九页，共56页3.4￿规划(guīhuà)求解￿￿￿CL是加入的NaCN总浓度，CM是加入的Cd(ClO4)2总浓度，KW是水的离子积（＝10-14）￿￿￿三个方程均为未知浓度的高次函数，用“规划求解”步骤(bùzhòu)如下：1）在B1:B3单元格输入Cd2＋总浓度CM￿，CN－的总浓度CL，HCN的酸解离常数Ka2）D2:F2是要求解的未知浓度[CN]、[Cd2+]和[H+]所在位置第50页/共55页第五十页，共56页3.4￿规划(guīhuà)求解3）三个方程均有求和项，先将其分别算出B6:B9是各级积累稳定(wěndìng)常数β￿i在C6、D6、E6分别输入公式：=B6*$D$2^A6、=A6*C6、=(2-A6)*C6用自动填充得各项的值，然后用自动求和，在C10、D10、E10分别得到F1、F2、F3方程中的求和项4）在D4:F4单元格，根据三个方程用D2:F2单元格的值计算，分别输入公式：￿￿￿＝E2+E2*C10-B1￿￿￿=D2+D2*F2/B3+E2*D10-B2￿￿￿=F2+B2-1.0E-14/F2-2*B1-D2+2*E2+E2*E10第51页/共55页第五十一页，共56页。

3.4￿规划(guīhuà)求解5）解非线性方程组，初始值的设置(shèzhì)很重要由于Cd2＋与CN－形成的配合物很稳定，加上HCN的解离常数很小，可以设[CN-]的初始值：0.01-3*0.001＝0.007 Cd2＋几乎全部与CN－配位，游离的很少，可以设初始[Cd2＋]＝10－12HCN酸性很弱，CN-在水溶液中显碱性，初始[H＋]＝10－106）以D4为目标，E4和F4为约束，用“规划求解”改变D2:F2单元格的值，使目标值和约束值均为0按“求解”后，显示找不到有用的解第52页/共55页第五十二页，共56页3.4￿规划(guīhuà)求解7）改变设置，初始[Cd2＋]＝10－13，仍然找不到有用的解8）再改变设置，初始[H＋]＝10－11，“规划求解结果”显示可找到一解，满足所有(suǒyǒu)的约束及最优状态9）结果很好，F1、F2、F3接近于0第53页/共55页第五十三页，共56页3.4￿规划(guīhuà)求解第54页/共55页第五十四页，共56页感谢您的欣赏(xīnshǎng)！第55页/共55页第五十五页，共56页内容(nèiróng)总结本章主要内容同样被选区域出现闪动虚线框，加号后出现F1:I1。

4）按Ctrl＋Shift＋Enter键如果(rúguǒ)输入公式时自己加上，Excel将认为输入的是文字2）函数名前有等号，输入完毕同时按Ctrl＋Shift＋Enter行列式通常用D表示，其元素用竖直线而非圆括号括住设一组含n个未知数的n个方程：8）重复迭代：选B5:G7，向下拖填充柄直至两次迭代改变量小于10－15＝E2+E2*C10-B1第五十六页，共56页。