Орлова И.В. Экономико-математические методы и модели. Выполнение расчетов в среде EXCEL. Практикум
Подождите немного. Документ загружается.
ТРАНСП(ЛИНЕИН(изв_знач_у,изв_знач_х))
Синтаксис
ТРАНСП(массив)
Массив - это транспонируемый массив или диапазон ячеек на рабо-
чем листе. Массив может быть интервалом ячеек. Транспонирование
массива заключается в том, что первая строка массива становится пер-
вым столбцом нового массива, вторая строка массива становится вторым
столбцом нового массива, и т.д.
Пример. Предположим, что ячейки А1:С1 содержат значения 1, 2 и
3 соответственно. Если следующая формула введена как формула масси-
ва в ячейки АЗ:А5, то:
ТРАНСП($А$1:$С$1) равняется тем же значениям 1, 2, 3 в ячейках
АЗ:А5.
Вычисление определителей
Определение.
Определителем
и-го
порядка, соответствующим матрице
А
=
а
и
«12
а
2
\ а
22
«1и
«2/1
\
а
п\
о
п2
...
а
п
„)
называется алгебраическая сумма л! членов, каждый из которых есть
произведение п элементов матрицы, взятых по одному из каждой строки
и из каждого столбца, причем каждый такой член берется со знаком
плюс, если его индексы составляют четную перестановку, и со знаком
минус - в противоположном случае:
Ш
=
«11
«21
««1
«12 •
«22 •
«п2 •
•
а\п
•
а
1п
••
«™,
я"
= У
(_1)"
а
1-
а
2
а„)
а
\щ
а
2а
г
*••••><*№„•>
где суммирование
распространяется
на всевозможные перестановки
(Х|,а;>,...,ос„
из
и
чисел.
10
Вычисление определителей
л-го
порядка производится на основа-
нии свойств определителей и следующей теоремы: определитель равен
сумме произведений элементов любой строки (столбца) на их алгебраи-
ческие дополнения:
H =
a,i4l
+a
i2
A
i2+---
+
a
mA,n-
Алгебраическое дополнение А,, элемента
а
ц
равно: А,, =
(-/)'
+/
М,
;
,
где
М,,
- минор элемента
а
ц
.
Вычисление определителя матриц
с помощью функции EXCEL МОПРЕД
МОПРЕД
Возвращает определитель матрицы (матрица хранится в массиве).
Синтаксис
МОПРЕД (массив)
Массив - это числовой массив с равным количеством строк и
столбцов.
• Массив может быть задан как интервал ячеек, например, А1:СЗ
или как массив констант, например, {1;2;3 : 4;5,6 : 7;8;9), или
как имя, называющее интервал или массив.
• Если какая-либо ячейка в массиве пуста или содержит текст, то
функция МОПРЕД возвращает значение ошибки #ЗНАЧ!.
• МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если
массив имеет неравное количество строк и столбцов.
Замечания
• Определитель матрицы - это число, вычисляемое на основе зна-
чений элементов массива. Для массива А1:СЗ, состоящего из
трех строк и трех столбцов,
определитель
вычисляется следую-
щим образом.
МОПРЕД(А1 СЗ)
равняется
А1-(В2-СЗ-В$С2) +
А2-(ВЗ-С\
-В1СЗ)
+ A3 (В\
С2-В2С1).
• Определители матриц обычно используются при решении сис-
тем уравнений с несколькими неизвестными.
(1
Пример
1.1.3.
Вычисление определителя матрицы из примера 1.1.1
ДСУЕД J
X
-J
А
В
С
4 6
В"
Н i 1
п.|
нед.^-но
М':поиь'.сваБШчхсд
Полный алфавитный перечень
Финансовые
Дата и время
Математические
Статистические
Ссылки и массивы
Работа с базой
данных
Текстовые
МОПРЕД(массив)
Возвращает аределитеяь
матрицы
(гчатрица
хранится
Ь
м<
©3
Рис.
1.1.6.
Первый шаг выполнения
ф>нкции
МОПРЕД
.
М0ПРЕ
Д ..d
x
^=
"МОШ'ЕДА1-В:)
А В С D
4 б
А1В2)
7 8
*.|
7 .
II
i:
Ж1
гМОПРСД-
Возвращает
определитель матрицы
(матрица
>ранится
Е
маеси
Массив числовой
пассив
с равным
ячеек
или
массив.
WJ
Z-начение
10
ве).
очичествс-мстро!
С
истопи
с*
юв,
диапазон
Отмена 1
Рис.
1.1.7.
Второй шаг- интервал ячеек задан
следхет
нажать
кнопку
ОК
12
Dl"
jj
=]
=М0ПРЕДг'А1 B2)
-
I
2
4 !
A
L
4
7
В
6
8
с
D E
-10
!
^j.
1
i
Рис.
1.1.8.
В
ячейке Dl появился
рез>льтат
вычислений
Примеры
МОПРЕД({1;3;8;5:
1;3;6;1
:
1;1;1;0
:
7;3;10;2})
равняется
88.
МОПРЕД({3,6;1 : 1,1;0
*
3;10;2}) равняется 1.
МОПРЕД({3,6
: 1;1})
равняется-3.
МОПРЕД({1;3,8;5
:
1;3;6;1})
равняется #ЗНАЧ!, так как массив
имеет неравное количество строк и столбцов.
Вычисление обратной матрицы
Onpedeiemie.
Квадратная матрица
Л
-1
порядка п называется обрат-
ной матрице А, если она удовлетворяет соотношению:
А~
х
А=АА~
Х
=
Е
Квадратная матрица А порядка п называется невырожденной (не-
особенной), если ее определитель отличен от нуля. В противном случае
матрица/}
называется особенной (вырожденной).
Для всякой невырожденной матрицы А =
(а
и
)
тп
существует единст-
венная обратная матрица, равная
Л
=
А
А*.
\А\
где А
*
- присоединенная матрица, каждый элемент которой есть алгеб-
раическое дополнение элемента
а,,
матрицы А, т е.
13
А*
=
Mi
л
2]
Аг Аг
АЛ
Ал
к
Ап
An
•••
"пп
/
Пример
1.1.4.
Вычислить обратную матрицу для матрицы А
'4
6"
Л
=
7
Решение. Вычислим определитель матрицы (пример
1.1.3):
\А
I
=
=
-10^0.
Определитель матрицы А отличен от нуля, следовательно, для
матрицы А существует единственная обратная матрица. Вычислим присое-
диненную матрицу
Л*:
А|,
= 8,
А\
2
=
-
6,
А
2
г
= 4,
Аг\
- -7,
-6
-7
;А~
1
=
-10
8 -6
-7
4 J
8
-10
-7
-6
-10
4
V—10
-10
/
-0.8
0.6
N
v
0.7
-0.4,
Проверкой убеждаемся,
ЧТОЛ-УГ
1
= Е.
Обратную матрицу можно вычислить на основании следующих
элементарных преобразований (преобразований Жордана-Гаусса) над
строками матрицы:
1)
перемена местами двух строк,
2) умножение строки матрицы на любое число, отличное от нуля,
3) прибавление к одной строке матрицы другой строки, умножен-
ной на любое число.
Для того, чтобы вычислить обратную матрицу для матрицы А, не-
обходимо составить матрицу В
= (А I
Е),
затем с помощью элементарных
преобразований преобразовать матрицу А к виду единичной матрицы Е,
тогда на месте единичной матрицы получим
матрицу/Г
.
Пример
1.1.5.
Вычислить обратную матрицу для матрицы А
fl
3 41
Л
=
-1 0
2 6
0
\2)
14
Решение.
Составим матрицу
В
т
вида:
(
\ 3 4
В
(01
1
0
12
О
1
О
О
Элемент
b\^
и первую строку, содержащую
данный
элемент,
назовем направляющими. Осуществим элементарные преобразования, в
результате которых первый столбец преобразуется в единичный с еди-
ницей в первой строке. Для этого ко второй и третьей строкам прибавим
первую строку, соответственно умноженную на 1 и -2. В результате
данных преобразований получим матрицу:
Z?
(I)
=
>0>.
1
о
о
1
(
\ 3 4
0 9 12
0 0 4-2 0
В матрице
Z?
u
'
преобразуем второй столбец в единичный. В качест-
ве направляющего элемента выберем элемент
bj}
-
9.
Так как направ-
ляющий элемент
Ь$ Ф
1,
то разделим вторую (направляющую) строку
на 9. Затем к первой строке прибавим вторую, умноженную на-3. Полу-
чим матрицу
В
(2)
о
о
о
4/3
4
2/3
1/9
-2
-1/3
1/9
0
Третий столбец матрицы
В
преобразуем в единичный. В качестве
направляющего элемента выбираем элемент
^з
=
^ •
Д
елим
направ-
ляющую (третью) строку на 4 и ко второй строке прибавляем третью,
умноженную на -4. Получим матрицу
В
0)_
откуда
0
1
0
'2/3
= 7/9
0
0
1
2/3
7/9
2
-1/3
1/9
0
о
\
-1/3
1/4
J
V"
1/2
-1/3
1/9
0
о
^
-1/3
1/4
J
15
Вычисление обратной матрицы
с помощью функции EXCEL МОБР
МОБР
Возвращает обратную матрицу для матрицы, хранящейся в массиве.
Синтаксис
МОБР(массив)
• Массив может быть задан как диапазон ячеек, например А1:СЗ;
как массив констант, например {1;2;3 : 4;5;6 : 7;8;9} или как имя
диапазона или массива.
• Если какая-либо из ячеек в массиве пуста или содержит текст, то
функция МОБР возвращает значение ошибки #ЗНАЧ!.
• МОБР также возвращает значение ошибки #ЗНАЧ!, если массив
имеет неравное число строк и столбцов.
Замечания
• Формулы, которые возвращают массивы, должны быть введены
как формулы массива.
• Обратные матрицы, как и
определители,
обычно используются
для решения систем уравнений с несколькими неизвестными.
Произведение матрицы на ее обратную - это единичная матри-
ца, т.е. квадратный массив, у которого диагональные элементы
равны
1.
а все остальные элементы равны 0.
• В качестве примера того, как вычисляется обратная матрица,
рассмотрим массив из двух строк и двух столбцов
А1:В2,
кото-
рый содержит буквы
а,
Ь,
с и d, представляющие любые четыре
числа. В следующей таблице приведена обратная матрица для
А1:В2:
Строка 1
Строка 2
Столбец А
d/(a
•
d-b
•
с)
cl{b
•
с - а
•
d)
Столбец В
Ы(Ь
с-ad)
alia
-d-b
с)
• МОБР производит вычисления с точностью до 16 значащих
цифр,
что может привести к небольшим численным ошибкам
округления.
• Некоторые квадратные матрицы не могут быть обращены, в та-
ких случаях ф>нкция МОБР возвращает значение ошибки
#ЧИСЛО!.
Определитель такой матрицы
равен 0.
16
Решение примера 1.1.4 в EXCEL
МОПРЕД
_jj
X
s/
=
=мо6р(А2ВЗ)
2
3
4
А 1 В
,
4
6
7
8
с
D Е
=мобр(А2.ВЗ)
F
i
Рис.
1.1.9. Массив задан как диапазон ячеек А2:ВЗ, выделен диапазон для размеще-
ния обратной
матрицы,
введена формула для вычислений. Затем следует нажать кла-
виши CTRL+SHIFT+ENTER.
• _\ _•::•
D2
_Ч[
=|{=МОБР(А2:ВЗ)}
2
3
4
1
*•
J ,>
А
i—
i
4
1.
в
!
6
8
С
L
D Е
-0.8|
0.7!
-0.41
•
1
F
•
Рис.
1.1.10.
Результат вычислений в ячейках
D2:E3.
Примеры
МОБР({4;-1 : 2;0}) равняется {0;0,5 : -1;2)
МОБР({1;2;1 :
3;4;-1
: 0;2;0}) равняется
{0,25;0,25;-0,75 : 0;0;0,5 :
0,75;-0,25;-0,25)
1.2. РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ УРАВНЕНИИ С ПОМОЩЬЮ
ТЕОРЕМЫ КРАМЕРА
И
С ПОМОЩЬЮ ОБРА ТНОЙ МА ТРИЦЫ
Правило Крамера применяется при решении системы
//
линейных
уравнений с п неизвестными, определитель которой отличен от нуля.
Решение системы линейных уравнений находится по формуле
Крамера:
17
|/4j
- определитель матрицы А, составленной из коэффициентов при
неизвестных;
\А\
- определитель, полученный из определителя
|/4|
путем замены
у'-го
столбца столбцом свободных членов.
Пример 1.2.1. Решить систему уравнений по правилу Крамера.
Х,+Х
2
+
Х,
+
Х
4
=10,
X] —
Х
2
+
Х^
— Х4
=
—2,
2Х,-ЗХ
2
+
4Х
3
+
Х
4
=12,
ЗХ,
+
4Х
2
-
ЗХ
3
+
9Х
4
= 38.
Решение. Вычислим определитель системы, используя функцию
МОПРЕД:
1111
11
2-3 4 1
3
4-39
= -68.
Рис.
1.2.1. Определитель системы \А\ =-68.
Определитель системы \А\
*
О, следовательно, система совместна и
обладает единственным решением. Вычислим определители
\Aj\,
j =
1..
.4.
18
•-""'--"-"——«*;
т
В6
ZJ:
4
5
б
7
А
10
-2
12
38
с
В
•:
:-..;;
1
-1
-3
4
-681
=М0ПРЕД(А
с
г—
1
1
4
-3
:D4)
0
1
-1
1
9
.
Е.
.
!
.
Рис.
1.2.2. Определитель
\А\\
=
-
68.
Аналогично вычисляем определители
|^2|>|^з|>|^4|
:
|Л
2
|
= -136,
|Л
3
|
= -204,
\А
4
\
=
-272.
Решение системы имеет вид:
-68 ,
•136
„
-204
„
-272
„
-
=
2;
хз
= —— =
У,
А'4
= =
4.
68
-68
-68
Решение системы уравнений с помощью обратной матрицы
С помощью обратной матрицы решаются системы п линейных
уравнений с п неизвестными, определитель которых отличен от нуля.
и
Для этого систему линейных уравнений
Х
а
!/
Х
у
=
^> > '
=
Ь--ч
и
запишем
в виде матричного уравнения АХ = В, где
Л
= (а,,) - квадратная матрица
порядка п, составленная из коэффициентов при неизвестных. Решение
матричного уравнения имеет вид: X
=
А~
]
В.
Пример 1.2.2. Решить систему линейных уравнений матричным
методом.
ЗХ,-Х
2
=1,
2Х, +
Х
2
-ЗХ
3
=
-5,
X,+
2Х
2
+
Х
3
=8.
19