《[PPT模板]化工计算机数据与图形处理PPT3.ppt》由会员分享，可在线阅读，更多相关《[PPT模板]化工计算机数据与图形处理PPT3.ppt（55页珍藏版）》请在三一办公上搜索。
1、第三章 EXCEL解方程组,本章主要内容,3.1 Excel矩阵计算3.2 解线性联立方程3.2.1 行列式法3.2.2 矩阵法3.3 Newton-Raphson迭代法 解非线性方程组3.4 方程组的规划求解,3.1 Excel 矩阵运算,1.Excel的矩阵格式矩阵是数据（数值和变量）按行和列排成矩形的阵列：Excel工作表中用数组表示矩阵，单元格的位置对应矩阵元素的位置，但标记顺序相反:矩阵元素aij是先行(i)后列(j)，而单元格A3是先列(A)后行(3)。,3.1 Excel 矩阵运算,1.Excel的矩阵格式 列矩阵或行矩阵在Excel工作表中放在同一列或同一行，称为一维数组。mn
2、矩阵元素依次放在工作表的一个区域中，称为二维数组。,3.1 Excel 矩阵运算,2.数组加（减）法 进行数组加（减）法运算时，一般要求数组中单元格的个数相同。示例：建立二维数组A1:D3，一维数组F1:I1。一维数组加（减）法：1）A1:D1一行数据与F1:I1另一行数组相加，得到的仍是含四个单元格的一行数组，因此必须选定加和结果数组的位置，本例选F3:I3。,3.1 Excel 矩阵运算,2.数组加（减）法2）键入“”，用鼠标选定A1:D1区域，被选区域出现闪动的虚线框，同时编辑框的等号后出现A1:D1。3）键入“”，选中F1:I1区域。同样被选区域出现闪动虚线框，加号后出现F1:I1。4
3、）按CtrlShiftEnter键。F3:I3区域出现加和结果，编辑框的公式被大括号括住:=A1:D1+F1:I1。,3.1 Excel 矩阵运算,注意：1）若开始未选定区域F3:I3，而只选中了F3单元格，则完成上述各步计算后在F3出现数值2。2）数组公式是一个整体，无论是编辑、移动、清除都必须选取公式包括对所有单元格。3）大括号不用输入，按CtrlShiftEnter后自动产生，表明这是数组公式。如果输入公式时自己加上，Excel将认为输入的是文字。4）若相加的两个数组单元格数目不等，则出现错误信号#N/A。,3.1 Excel 矩阵运算,注意：5）若加一常数到一数组，Excel会自动将该
4、常数加到数组的每个单元格中。二维数组加法类似于一维数组，此外还有如下特点：二维数组可以与行单元格或列单元格数目相同的一维数组相加。,3.1 Excel 矩阵运算,示例结果：,3.1 Excel 矩阵运算,3.数组乘以一常数 数组乘以一常数，得到与原数组大小相同的新数组，新数组的每个单元格的值等于原数组单元格的值乘以此常数。,3.1 Excel 矩阵运算,4.矩阵乘法 矩阵A（mn）与B（np）相乘，得一矩阵C（mp）。仅当第一个矩阵的列数等于第二个矩阵的行数时，两矩阵才能相乘。矩阵相乘时，各矩阵元素按一定规则相乘再相加。Excel有矩阵相乘的工作表函数MMULT(array1，array2）。
5、,3.1 Excel 矩阵运算,4.矩阵乘法计算规则：其中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),3.1 Excel 矩阵运算,注意：1）输入MMULT函数前要根据乘积矩阵大小（mp）选定它的单元格区域。2）函数名前有等号，输入完毕同时按CtrlShiftEnter。3）数组乘法不等于矩阵乘法，它是两个数组对应单元格各自相乘，数组相乘用*，公式写作数组1*数组2。,3.1 Excel 矩阵运算,5.逆矩阵 行数等于列数的矩阵称为方阵。若一方阵的
6、主对角元素aii均为1，其他位置上的元素均为0，则该矩阵称为单位矩阵I。设有一n阶方阵A和另一n阶方阵B，它们的乘积为：AB=BA=I 则B为A的逆阵，可记为A-1。,3.1 Excel 矩阵运算,5.逆矩阵 Excel求逆矩阵的工作表函数为 MINVERSE(array）例1.MINVERSE(4,-1;2,0)例2.MINVERSE(1,2,1;3,4,-1;0,2,0)例3.MINVERSE()为一矩阵，可参与其他矩阵运算。,3.1 Excel 矩阵运算,6.行列式行列式是在研究线性方程组的解法中产生的概念。n阶行列式可表示为：,3.1 Excel 矩阵运算,6.行列式 行列式通常用D表
7、示，其元素用竖直线而非圆括号括住。方阵与行列式是两个不同的概念：n阶方阵是n2个数按一定方式排列成的数据表；n阶行列式是按一定的运算规则得到的一个确定值。,3.1 Excel 矩阵运算,6.行列式Excel计算行列式值的函数：MDETERM(array)例1 MDETERM(1,3,8,5;1,3,6,1;1,1,1,0;7,3,10,2),3.1 Excel 矩阵运算,7.转置矩阵 矩阵A的行和列的元素交换后得到它的转置矩阵A：,3.1 Excel 矩阵运算,7.转置矩阵1）Excel转置矩阵函数：TRANSPOSE(数组）注：要先根据原矩阵选定转置矩阵区域，且数组函数输入同时按CtrlSh
8、iftEnter键。2）选择性粘贴转置注：粘贴时只需选定区域的左上单元格即可。,3.1 Excel 矩阵运算,7.转置矩阵,3.2 解线性联立方程,线性联立方程：设一组含n个未知数的n个方程：其中xi(x1,x2,x3,)是实验未知数，ci是实验测量值，aij是系数。这些方程必须是线性独立的，上式的矩阵表示为：AX=C,3.2 解线性联立方程,A是系数矩阵：X和C分别为解矩阵和常数矩阵：,3.2.1 行列式法,根据Cramer法则，线性联立方程有唯一解的条件是其系数行列式为非零值：此时方程的解为：,3.2.1 行列式法,是方程组的系数行列式，但其中的第i列元素被常数列阵C所取代。例如 是用常数
9、C列阵取代系数矩阵的第一列元素所得的行列式：,3.2.1 行列式法,示例：Excel步骤：1）输入注释文字和系数数值。2）在E2输入:=MDETERM(A2:C4)，得到系数行列式的值9，说明联立方程有唯一解。,3.2.1 行列式法,3）复制联立方程的系数矩阵A2:C4，粘贴到下边A6:C8、A10:C12和A14:C16。再复制常数列数组D2:D4，将其分别粘贴到A6:A8、B10:B12、C14:C16。得到分子行列式。4）分别解出三个分子行列式的值，再根据公式解出x，y，z的值。,3.2.1 行列式法,3.2.2 矩阵法,线性方程的矩阵表示经数学变换得：XA-1C即解矩阵（X）等于系数矩
10、阵的逆矩阵（A-1）乘于常数矩阵（C）。示例：同前联立方程1）输入标注、系数矩阵和常数矩阵2）选中E2:E4，输入公式：=MMULT(MINVERSE(A2:C4),D2:D4)，同时按CtrlShiftEnter，可得解。,3.2.2 矩阵法,3.3 Newton-Raphson迭代法,原理：N-R迭代法属于间接解法，可用于非线性方程组。原理类似于一元方程求解。然而因有不止一个自变量，导数变为偏导，迭代公式成为迭代增量的线性方程组。设一非线性方程组：,3.3 Newton-Raphson迭代法,原理：对方程组的每一个方程在其近似解 处用Teller级数展开，只取线性项得：,3.3 Newto
11、n-Raphson迭代法,原理：其中 是方程 在 处的一阶偏导数。上式为xi的线性方程组：,3.3 Newton-Raphson迭代法,原理：将初始值 代入函数fi和偏导数，然后用解线性方程组的方法计算得到，从而得到自变量xi的第一次迭代值：继续迭代，可得：,3.3 Newton-Raphson迭代法,原理：直至：为一小正数。即为满足指定精度的原方程的解。,3.3 Newton-Raphson迭代法,示例：方程组：每个方程对每一自变量的偏导数为：,3.3 Newton-Raphson迭代法,示例：于是有线性方程组（1）：,3.3 Newton-Raphson迭代法,Excel解方程组步骤：1）
12、A列标记迭代次数。在A2、A3、A4输入x1:0、x2:0、x3:0后，选定A2:A4区域，拖拽填充柄到适当位置。冒号后的数字表示迭代次数。2）B2、B3、B4分别输入x1、x2、x3的初始值0.5。3）C2:E4单元格区域输入线性方程组（1）的系数矩阵。xi的系数是含xi的数学表达式。,3.3 Newton-Raphson迭代法,Excel解方程组步骤：4）F2:F4区域输入常数列矩阵。根据式(1)，F2输入=-(B22+B32+B42-1)/2，F3单元格输入=-(2*B22+B32-4*B4)/2，F4输入=-(3*B22-4*B3+B42)/2。5）用矩阵法解线性方程式（1）：选定单元
13、格G2:G4，输入公式：=MMULT(MINVERSE(C2:E4),F2:F4），同时按Ctrl+Shift+Enter，得到第一次迭代改变量xi1。,3.3 Newton-Raphson迭代法,Excel解方程组步骤：6）用矩阵加法在B5:B7填入第一次迭代后的自变量xi1：选定B5:B7，输入公式=B2:B4+G2:G4，按CtrlShiftEnter。7）重复解线性方程组过程：选取C2:G4，将填充柄向下拖3行。在G4:G7得到第二次迭代改变量xi1。8）重复迭代：选B5:G7，向下拖填充柄直至两次迭代改变量小于1015。,3.3 Newton-Raphson迭代法,3.4 规划求解,
14、规划求解解方程组是单变量求解解一元方程的延伸。目标单元格是方程组中任一方程所在，目标值是该方程的常数。可变单元格是方程组的自变量所在。约束为方程组中其余方程。“规划求解”改变可变单元格的值，使得目标值与约束均得到满足。,3.4 规划求解,3.4.1 规划求解解线性方程组 以前面的线性方程组为例：x，y，z是可变单元格变量，将F1的常数作为目标单元格的数值，F2和F3的常数作为约束条件。任意设置可变单元格的值，目标是寻找满足约束条件(6,3)，并使目标值为0的x，y，z。,3.4 规划求解,Excel步骤：1）B2:D2输入未知数初始值(任意数)1,1,1。2）根据F1式，在B4单元格输入公式：
15、2*B2+C2-D2；根据F2，在C4输入：B2-C2+D2；根据F3，在D4输入：=B2+2*C2+D2。3）打开“工具”菜单，单击“规划求解”，出现“规划求解参数”对话框。,3.4 规划求解,Excel步骤：4）“设置目标单元格”输入方程F1所在位置：$B$4，“等于”栏设置目标值为0。“可变单元格”编辑框输入未知数所在单元格：$B$2:$D$2。,3.4 规划求解,Excel步骤：5）单击“约束”选项区的“添加”按钮，出现添加约束对话框，在“单元格引用位置”处输入方程F2的位置：$C$4，用下拉箭头选取等号“”，在“约束值”处输入F2的常数6。单击“添加”，给出新的添加约束对话框，继续增
16、“单元格引用位置”处输入F3的位置：$D$4，选“”，约束值为3。单击“确定”，回到“规划求解参数”对话框。,3.4 规划求解,3.4 规划求解,6）约束条件将出现在“约束”列表区：$C$4=6和$D$4=3。若约束条件设置有误，可通过“更改”或“删除”对约束条件进行改变。7）所有参数设置好后，按“求解”，得到相应结果：,3.4 规划求解,3.4.2 解非线性方程组 多元一次线性方程比较容易，对初始值设定无严格要求。若方程为非线性，则必须设置不同初始值，才能保证解的可信性。例：在Cd(ClO4)2和NaCN的水溶液中有如下平衡：,3.4 规划求解,根据上述三个解离平衡，由物料平衡和电荷平衡可得
17、到含三个未知数CN、Cd2+和H+的三个方程：,3.4 规划求解,CL是加入的NaCN总浓度，CM是加入的Cd(ClO4)2总浓度，KW是水的离子积（10-14）。三个方程均为未知浓度的高次函数，用“规划求解”步骤如下：1）在B1:B3单元格输入Cd2总浓度CM，CN的总浓度CL，HCN的酸解离常数Ka。2）D2:F2是要求解的未知浓度CN、Cd2+和H+所在位置。,3.4 规划求解,3）三个方程均有求和项，先将其分别算出。B6:B9是各级积累稳定常数 i。在C6、D6、E6分别输入公式：=B6*$D$2A6、=A6*C6、=(2-A6)*C6。用自动填充得各项的值，然后用自动求和，在C10、
18、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,3.4 规划求解,5）解非线性方程组，初始值的设置很重要。由于Cd2与CN形成的配合物很稳定，加上HCN的解离常数很小，可以设CN-的初始值：0.01-3*0.0010.007。Cd2几乎全部与CN配位，游离的很少，可以设初始Cd21012。HCN酸性很弱，CN-在水溶液中显碱性，初始H10106）以D4为目标，E4和F4为约束，用“规划求解”改变D2:F2单元格的值，使目标值和约束值均为0。按“求解”后，显示找不到有用的解。,3.4 规划求解,7）改变设置，初始Cd21013，仍然找不到有用的解。8）再改变设置，初始H1011，“规划求解结果”显示可找到一解，满足所有的约束及最优状态。9）结果很好，F1、F2、F3接近于0。,3.4 规划求解,
