Забыли?

?

# Matrix Commands in Excel (Powerpoint) - Nuffield College

код для вставкиСкачать
```Matrix Commands
in Excel
Anthony Murphy
Nuffield College
anthony.murphy@nuffield.ox.ac.uk
Matrix Commands in Excel
Excel can perform some useful, albeit basic, matrix
operations:
вЂў Scalar multiplication & division;
вЂў Transpose (TRANSPOSE);
вЂў Matrix multiplication (MMULT);
вЂў Matrix inverse (MINVERSE);
вЂў Determinant of matrix (MDETERM);
As well as combinations of these operations.
Matrix Commands in Excel (ContвЂ™d)
вЂў In Excel the matrix commands (and some
other commands) are called ARRAY
commands.
вЂў Can perform more complicated operations
using free add-ins for Excel e.g. MATRIX.
вЂў Alternatively, can use matrix package like
MATLAB (which also does symbolic maths
& matrix algebra).
Excel (ContвЂ™d)
вЂў The matrix commands in Excel are
sufficient for this course.
вЂў If you are really keen, you can play around
with Visual Basic for Applications (VBA),
the Excel programming language.
вЂў For example, see Benninga, S. (2000),
Financial Modelling, MIT Press.
Named Cells
вЂў Most Excel formulae require you to name one or
more cell ranges e.g. b2.c4.
вЂў You can type these in directly or select them
using the mouse.
вЂў However, it is often better to use a named range.
вЂў To assign a name to a range of cells, highlight it
using the mouse and choose Insert в–єName в–є
Define and enter a name.
вЂў Choose a useful name.
вЂў Remember Excel does not distinguish between
the names PRICE, Price and price.
Entering a Matrix
вЂў Choose a location for the matrix (or vector)
and enter the elements of the matrix.
вЂў Highlight the cells of the matrix and
choose INSERT в–є NAME в–є DEFINE.
вЂў Enter a name for the matrix.
вЂў You can now use the name of the matrix in
formulae.
Multiplication Etc.
To add two named 3 x 2 matrices A and B:
вЂў Highlight a blank 3 x 2 results area in the
spreadsheet. (If the results area is too small, you
вЂў Type =A+B in the formula bar and press the
CTRL, SHIFT and ENTER keys simultaneously.
вЂў You must use the CTRL, SHIFT,ENTER keys if
you want to perform a matrix computation. (If
you donвЂ™t do this, you will get an error message
Multiplication Etc. (ContвЂ™d)
вЂў If you click on any cell in the result, the
formula {=A+B} will be displayed. In Excel,
the { } brackets indicate a matrix (array)
command.
вЂў For an example of scalar multiplication,
see the Example Spreadsheet on the web
page.
Matrix Transpose
вЂў Suppose A is a 3 x 2 matrix.
вЂў The transpose of A, AвЂ™, will be 2 x 3.
вЂў Select a 2 x 3 results area, type
=TRANSPOSE(A) in the formula bar and
press CTRL, SHIFT, ENTER.
вЂў Exercise: Choose A and B so that AB
exists. Check that (AB)' = B 'AвЂ� using
MMULT (matrix multiplication).
вЂў What do you think (ABC)' is equal to?
Matrix Multiplication
вЂў Suppose A and B are named 3 x 2 and 2 x 3
matrices.
вЂў Then AB is 3 x 3 and BA is 2 x 2. This illustrates
the fact that, in general, AB is not equal to BA,
even if the matrices are conformable.
вЂў Select a blank 3 x 3 area for the result AB.
вЂў Type =MMULT(A,B) in the formula bar and press
CTRL, SHIFT, ENTER to generate AB.
Matrix Inverse
вЂў Suppose B is a square 2 x2 matrix.
вЂў Select a 2 x 2 area for the inverse of B.
вЂў Type =MINVERSE(B) in the formula bar and
press CRTL, SHIFT, ENTER.
вЂў If B is singular (non-invertible), you will get an
error message.
вЂў Suppose A and B have the same dimension and
are both invertible. Show that (AB)-1 = B-1A-1.
вЂў What do you think (ABC)-1 is equal to?
Matrix Determinant
вЂў Suppose A is a square matrix.
вЂў The determinant of A, det(A) or IAI, is a scalar.
вЂў Select a single cell, type = MDETERM(A) in the
formula area and press CTRL, SHIFT, ENTER
(or just ENTER).
вЂў If A is singular, then det(A) = 0.
вЂў Exercise: Check that det(AB) = det(BA) =
det(A).det(B), where A and B are square
matrices.
```
###### Документ
Категория
Презентации
Просмотров
7
Размер файла
50 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа