Скачать или смотреть Microsoft Excel: Use Matrix/Array Formulas to Solve Simultaneous Linear Equations

In this video we will use Excel's minverse() and mmult() matrix function, which belong to the category of "array formulas" in Excel to solve a set of simultaneous linear equations. Our example will be a set of linear equations that arise from an analysis of a resistor network using Kichhoff's Laws. In this example, the variables are currents (around loops in the circuit), the coefficients are resistances (that the currents pass through), and the constants are voltage (such as batteries). The set of equations can be thought of as a single matrix equation.

Coefficient_Matrix * Variable_Vector = Constant_Vector

which is then solved as

Variable_Vector = Coefficient_Matrix_Inverse * Constant_Vector

One enters the coefficient matrix and uses the minverse() formula to calculate the inverse of the matrix. It is an "array formula" which requires highlighting a set of cells for the result and typing the formula followed by Ctrl-Shift-Enter. Next one types in the constant vector and then uses the mmult() formula -- another array formula. It multiplies the matrix inverse and constant vector to arrive at the variable vector (solution).