chapter31

UN-20EMATRICES IN EXCELMatrix A (a row vector) Matrix B (square 3 x 3 matrix)Matrix C(columnvector)2 3 4 13 -8 -3 13-8 10 -1 -8-3 -1 11 -3Matrix D (a 4 x 3 matrix)13 -8 -3-8 10 -1-3 -1 110 13 3A B C D E F G H I1234567891011EXCEL中的矩阵矩阵A（行向量） 矩阵B (3x3方阵) 矩阵C(列向量)2 3 4 13 -8 -3 13-8 10 -1 -8-3 -1 11 -3矩阵D (4x3矩阵)13 -8 -3-8 10 -1-3 -1 110 13 3MULTIPLYING A MATRIX BY ASCALARScalar 6Matrix B 13 -8 -3-8 10 -1-3 -1 11Scalar * Matrix B78 -48 -18 - =D4*$B$2-48 60 -6-18 -6 66A B C D E1234567891011矩阵乘以一个量量 6矩阵B 13 -8 -3-8 10 -1-3 -1 11量*矩阵B78 -48 -18 - =D4*$B$2-48 60 -6-18 -6 66ADDITION OF MATRICESMatrix A Matrix B Sum of A + B1 3 0.1 0 1.1 33 0 23 0 26 06 -9 8 -33.4 14 -42.45 11 -15 0 -10 117 12 2.33 1.2 9.33 13.2A B C D E F G H1234567- =B3+E3ADDITION OF MATRICESI1234567矩阵相加矩阵A 矩阵B A + B之和1 3 0.1 0 1.1 3 - =B3+E33 0 23 0 26 06 -9 8 -33.4 14 -42.45 11 -15 0 -10 117 12 2.33 1.2 9.33 13.2- =B3+E3TRANSPOSITION OF MATRICESMatrix E Transpose of E: ET1 2 3 4 1 0 160 3 77 -9 2 3 716 7 7 2 3 77 74 -9 2Cells F3:H6 are generated with the array function Transpose(A3:D5). This function is inserted by marking off thetarget area, typing the formula, and then finishing by pressing Ctrl+Shift+Enter . See Chapter 35 for more details.A B C D E F G H12345678- =TRANSPOSE(A3:D5)TRANSPOSITION OF MATRICESCells F3:H6 are generated with the array function Transpose(A3:D5). This function is inserted by marking off thetarget area, typing the formula, and then finishing by pressing Ctrl+Shift+Enter . See Chapter 35 for more details.I12345678矩阵转置矩阵E E的转置: ET1 2 3 4 1 0 16 - =TRANSPOSE(A3:D5)0 3 77 -9 2 3 716 7 7 2 3 77 74 -9 2单元格F3:H6 是由数组函数Transpose(A3:D5)生成的。使用这个函数：选中整个区域输入公式，然后按Ctrl+Shift+Enter完成。细节见35章。- =TRANSPOSE(A3:D5)MULTIPLYING MATRICESMatrix A Matrix B2 -7 6 9 -120 3 -5 2 4Product AB47 4 -52 - =MMULT(A3:B4,D3:F4)-15 6 12A B C D E F12345678矩阵相乘矩阵A 矩阵B2 -7 6 9 -120 3 -5 2 4乘积AB47 4 -52 - =MMULT(A3:B4,D3:F4)-15 6 12MATRIX MULTIPLICATION:Number of columns of first matrix must equalnumber of rows of second matrixCant multiply matrix B times matrix A!Matrix A Matrix B2 -7 6 9 -120 3 -5 2 4Product BA#VALUE! #VALUE! #VALUE! - =MMULT(D3:F4,A3:B4)#VALUE! #VALUE! #VALUE!A B C D E F12345678矩阵乘法：第一个矩阵的列数必须等于第二个矩阵的行数不能用矩阵B乘以矩阵A!矩阵A 矩阵B2 -7 6 9 -120 3 -5 2 4Product BA#VALUE! #VALUE! #VALUE! - =MMULT(D3:F4,A3:B4)#VALUE! #VALUE! #VALUE!MATRIX INVERSEUse array function Minverse to compute the inverse of a square matrixMatrix A Inverse of A1 -9 16 1 -0.0217 1.8913 0.53623 3 2 3 0.0000 -1.0000 -0.16672 4 0 -2 0.0652 -0.6739 -0.10875 7 3 4 -0.0217 -0.1087 -0.2971Verifying the inverseWe multiply A*Inverse A: cells below contain arrayfunction =MMULT(A3:D6,F3:I6)1 1.069E-15 -2.22045E-16 -9.4369E-160 1 -1.11022E-16 2.22045E-166.939E-18 8.327E-17 1 5.55112E-161.388E-17 1.166E-15 -4.44089E-16 1A B C D E F G H12345678910111213-1.1449 - =MINVERSE(A3:D6)0.66670.43480.1884MATRIX INVERSEUse array function Minverse to compute the inverse of a square matrixInverse of AI J12345678910111213矩阵的逆使用数组函数Minverse计算方阵的逆矩阵矩阵A A的逆1 -9 16 1 -0.0217 1.8913 0.5362 -1.14493 3 2 3 0.0000 -1.0000 -0.1667 0.66672 4 0 -2 0.0652 -0.6739 -0.1087 0.43485 7 3 4 -0.0217 -0.1087 -0.2971 0.1884逆的验证我们将A的逆和A相乘:下面的单元格包含数组函数 =MMULT(A3:D6,F3:I6)1 1.07E-15 -2.2E-16 -9.4E-160 1 -1.1E-16 2.22E-166.94E-18 8.33E-17 1 5.55E-161.39E-17 1.17E-15 -4.4E-16 1- =MINVERSE(A3:D6)矩阵的逆使用数组函数Minverse计算方阵的逆矩阵MATRIX INVERSEUse array function Minverse to compute the inverse of a square matrixMatrix A Inverse of A1 -9 16 1 -0.0217 1.8913 0.53623 3 2 3 0.0000 -1.0000 -0.16672 4 0 -2 0.0652 -0.6739 -0.10875 7 3 4 -0.0217 -0.1087 -0.2971Verifying the inverseWe multiply A*Inverse A: cells below contain arrayfunction =MMULT(A3:D6,F3:I6)1.0000 0.0000 0.0000 0.00000.0000 1.0000 0.0000 0.00000.0000 0.0000 1.0000 0.00000.0000 0.0000 0.0000 1.0000A B C D E F G H12345678910111213-1.1449 - =MINVERSE(A3:D6)0.66670.43480.1884MATRIX INVERSEUse array function Minverse to compute the inverse of a square matrixInverse of AI J12345678910111213矩阵的逆使用数组函数Minverse计算方阵的逆矩阵矩阵A A的逆1 -9 16 1 -0.0217 1.8913 0.53623 3 2 3 0.0000 -1.0000 -0.16672 4 0 -2 0.0652 -0.6739 -0.10875 7 3 4 -0.0217 -0.1087 -0.2971逆的验证我们将A的逆和A相乘:下面的单元格包含数组函数=MMULT(A3:D6,F3:I6)1.0000 0.0000 0.0000 0.00000.0000 1.0000 0.0000 0.00000.0000 0.0000 1.0000 0.00000.0000 0.0000 0.0000 1.0000-1.1449 - =MINVERSE(A3:D6)0.66670.43480.1884矩阵的逆使用数组函数Minverse计算方阵的逆矩阵A的逆SOLVING SIMULTANEOUS EQUATIONSMatrix A of coefficients Columnvector y SolutionA-1 Y3 4 66 16 0.43430 -33 1 77 -2.322342 3 2 12 0.3634Checking that the solution works1677 - =MMULT(A3:C5,G3:G5)12A B C D E F G12345678910- =MMULT(MINVERSE(A3:C5),E3:E5)SOLVING SIMULTANEOUS EQUATIONSH12345678910解方程组系数矩阵A 列向量y 解A-1 Y3 4 66 16 0.43430 -33 1 77 -2.322342 3 2 12 0.3634解的检查1677 - =MMULT(A3:C5,G3:G5)12- =MMULT(MINVERSE(A3:C5),E3:E5)解方程组