- •Тема 7. Технология решения задач в среде табличного процессора
- •7.1 Методические указания и основные термины
- •7.1.1 Функциональные возможности и пользовательский интерфейс программы Excel
- •Упражнение 7.1
- •Упражнение 7.2
- •7.1.2. Создание и редактирование электронных таблиц
- •7.1.3. Форматирование электронных таблиц и операции над документами
- •Упражнение 7.3
- •Упражнение 7.4
- •Экзаменационная ведомость
- •Упражнение 7.5
- •Упражнение 7.6
- •7.1. 4. Построение, редактирование и форматирование диаграмм
- •Упражнение 7.7
- •Упражнение 7.8
- •Упражнение 7.9
- •Упражнение 7.10
- •Упражнение 7.11
- •Упражнение 7.12
- •Упражнение 7.13
- •Упражнение 7.14
- •Упражнение 7.15
- •Упражнение 7.16
- •7.1.5. Использование встроенных функций
- •Упражнение 7.16
- •Упражнение 7.17
- •Упражнение 7.18
- •Упражнение 7.19
- •Упражнение 7.20
- •Упражнение 7.21
- •Упражнение 7.22
- •Упражнение 7.23
- •Упражнение 7.24
- •Упражнение 7.25
- •Упражнение 7.26
- •Упражнение 7.27
- •Упражнение 7.28
- •Упражнение 7.29
- •Упражнение 7.30
- •Упражнение 7.31
- •Упражнение 7.32
- •Упражнение 7.33
- •Упражнение 7.34
- •Упражнение 7.35
- •7.1.6 Приемы автоматизации обработки данных и решения аналитических задач
- •Упражнение 7.36
- •Упражнение 7. 37
- •Упражнение 7.38
- •Упражнение 7.39
- •Упражнение 7.40
- •Упражнение 7.41
- •Упражнение 7.42
- •7.1.7. Работа со списками и сводными таблицами
- •Упражнение 7.43
- •Упражнение 7.44
- •Упражнение 7.45
- •Упражнение 7.46
- •7.2. Вопросы для самоконтроля
Упражнение 7.19
Откройте рабочую книгу Функции и добавьте в нее лист Матрицы.
В первую строку листа введите заголовок Работа с матрицами.
Далее на лист вводите данные и функции, чтобы получить результат аналогичный приведенному ниже рисунку 7.31 .
Рис. 7.31. Примеры использования матричных функций
Пояснения к рисунку.
Введите в диапазон ячеек (A5:C7) матрицу М1, а в диапазон (E5:G7) матрицу М2.
Для нахождения матрицы, обратной для матрицы М1, выделите диапазон ячеек A11: C13, затем с помощью Мастера функций введите функцию МОБР и завершите процесс одновременным нажатием трех клавиш <Ctrl + Shift + Enter>. Аналогичную операцию (но теперь для диапазона E11:G13) проведите для второй матрицы. Сравните полученные результаты с рисунком.
Для нахождения определителя матрицы М1 выделим ячейку A17, введем в нее функцию МОПРЕД и процесс закончим нажатием клавиши <OK>. Выполним аналогичные операции для нахождения определителя второй матрицы, задав ячейку E17. Сравните результаты с рисунком.
Для получения произведения матриц М1 и М2 выделим диапазон ячеек A21:C23, введем функцию МУМНОЖ и завершим процесс одновременным нажатием трех клавиш <Ctrl + Shift + Enter>. Аналогичную операцию только для нахождения произведения матрицы М1 на обратную ей выполним, предварительно выделив диапазон E21:G23. Сравните полученные Вами результаты с результатами на рисунке 7.31.
Функции работы с матрицами эффективны для решения системы линейных уравнений вида методом обратной матрицы:
A 11 X1 + A12 X2 + …… + A1N XN = B1
A21 X1 + A22 X2 + …… + A2N XN = B2
……………………………………………………
AN1 X1 + AN2 X2 + ……+ ANN XN = BN
В матричном виде такая система уравнений может быть записана следующим образом : A * X = B,
г де
A11 A12 …A1N B1 X1
A = A22 A22 …..A2N B = B2 X= X2
…………………….. …. …..
AN1 AN2 ……ANN BN X3
Решением системы уравнений будет X = A-1 * B, где A-1 - обратная матрица по отношению к A, а B – вектор.
Пример
Используя функции работы с матрицами, решить следующую систему линейных уравнений методом обратной матрицы
Х + 2Y + 8 Z+ 22 = 0
A = X - Y + Z + 2, 5 = 0
10 X - 3 Z - 19 = 0
Пояснения
Открыв рабочую книгу Функции, добавим в нее лист Уравнения и на нем сформируем две матрицы : М1 в диапазоне A19 : C21 и М2 в диапазоне E19 : E21. Далее в диапазоне A26 : C28 найдем матрицу, обратную М1.
Выделив диапазон E26:E28 и перемножив обратную матрицу на матрицу М2, завершив ввод функции МУМНОЖ одновременным нажатием трех клавиш <CTRL + SHIFT + ENTER>, получим искомый результат: X = 1, Y = 0,5 Z = -3. (рис. 7.32)
Рис. 7.32. Решение системы линейных уравнений методом обратной матрицы
Упражнение 7.20
В рабочей книге Функции на листе Уравнения, используя функции для работы с матрицами, решите следующие системы линейных уравнений:
5X + 3Y – Z = 8
2X – 4Y + 3Z = 3
7X + 4Y - 2Z = 9
а )
5X + 7Y - 2Z = 3
2X – 12Y - 8Z = 0
X – 3Y = 0
б)
A + 2B – 3C =4
2A – 8B = 6
3B + 4C = 14
в)
X + 2Y + 4 Z = 9
X + 5Y + 4Z = 6
2X + 5Y + Z - 3 = 0
г )
7X – 8Y + 4Z = 3
11X + 5Y – 6Z = -2
X + 4Y – 3Z = 0
д)
Более общим приемом решения систем линейных уравнений является метод наименьших квадратов, когда число столбцов и строк матрицы не совпадает. Для этого обе части уравнения следует умножить на транспонированную матрицу системы - Ат.
АтАХ =АтB
Затем обе части уравнения надо умножить на (АтА)-1.
Если эта матрица существует, то система определена. Тогда решение системы получается в виде Х=(АтА)-1 *АтВ.
Ниже рассматривается технология решения систем линейных уравнений методом наименьших квадратов.
П усть требуется решить систему уравнений
3х + 2у =7
4х – 5у =40
3х + 3у = 3
Последовательность решения системы уравнений следующая.
Вводим значения элементов матрицы А в диапазон рабочего листа (А2:B4).
Вводим значения элементов вектора B в ячейки рабочего листа D2:D4.
Транспонируем исходную матрицу A. Для этого выделяем диапазон размерностью 3 х 2 , например (A6:C7), вводим в него функцию категории Массивы и ссылки =ТРАНСП(A2:B4) и нажимаем комбинацию клавиш <Ctrl + Shift +Enter>. В результате в выделенном диапазоне появится результат транспонирования.
Вычисляем произведение двух матриц - Aт и B. Для этого выделяем диапазон из двух ячеек (E6:E7) и вводим в него функцию МУМНОЖ (A6:C7; D2:D4).
Вычисляем произведение двух новых матриц - Aт и A. С этой целью выделяем диапазон (A9:B10) и вводим в него функцию МУМНОЖ (A6:C7; A2:B4).
Выделяем диапазон (D9:E10), задаем в нем функцию МОБР(A9:B10), чтобы вычислить обратную матрицу (AтА)-1.
Для вычисления итогового результата, т.е. решения системы линейных уравнений, выделяем диапазон (B12:B13) и вводим в него функцию перемножения матриц МУМНОЖ(D9:E10; E6:E7). Таким образом, в ячейках B12 и B13 будет получен результат решения системы линейных уравнений методом наименьших квадратов.
Ход решения описанной задачи представлен на рис. 7.33.
Рис. 7. 33. Результаты решение системы уравнений методом наименьших квадратов