Лабораторная №5 по Excel 2010
.pdfЛабораторная работа №5
Задание №1. Решение уравнений с одним неизвестным
Задание. Научиться решать уравнения с одним неизвестным используя стандартные средства
MS Excel. Найти корни уравнения cosx + sin2x на интервале [0,2].
Для наглядности воспользуемся графиком функции у=cosx + sin2x построенном на интервале
[0,2 ] в лабораторной работе №4. Визуально видно, в каких интервалах находятся корни. Осталось уточнить их значения. Для решения уравнения с одним неизвестным воспользуемся удобным и простым для понимания инструментом Подбор параметра.
Открыть файл с заданиями по Excel. После открытия книги перейти на лист с выполненной Лабораторной работой №4.
Порядок выполнения задания.
5.1.Оформить шапку по образцу.
5.2.Отделить корни.
•Заполнить столбец H приближѐнными значениями корней заданного уравнения, согласно графику построенной функции.
5.3.Ввести формулу cosx + sin2x в столбец I.
•Заносим в ячейку I2 формулу = cos(H2) + sin (2*H2).
•Используя маркер заполнения, копируем данную формулу в диапазон I2: I9.
Ячейки заполняться значениями функции от значений аргументов внесѐнных в соседний столбец.
5.4.Вызвать процедуру Подбор параметра.
•Выделать ячейку I2 ►Данные ►Работа с данными ►Анализ «что если» ► Подбор параметра.
•В поле Установить в ячейке л.к.м. щѐлкаем по I2, в поле
Значение с клавиатуры задаѐм 0 (правая часть уравнения), в поле
Изменяя значение ячейки л.к.м. щѐлкаем по H2. ОК.
•Получаем результат подбора, отображаемый в диалоговом
окне Результат подбора параметра. Щѐлкаем ОК.
Сохраняются полученные значения в ячейках, участвовавших в операции. Приближѐнное значение х=1,5707881, погрешность решения (значение правой части уравнения) – вместо 0 получаем – -5,36Е-06.
1.Нахождение следующих корней уравнения на заданном промежутке.
• Проделываем все подпункты пункта 5.4 в соответствующих диапазонах, т.е. H3:I9.
И так, столбец H заполнится приближѐнными значениями корней, столбец I погрешностями значений функций в заданных точках (рис.2.5). Сравните и объясните полученные значения.
Рисунок 2.5. Нахождение корней уравнения с одним неизвестным.
Задание для самостоятельной работы.
1. Найти корни уравнений из самостоятельной работы 1 и 3 лабораторной работы №4 по своему варианту.
Задание №2. Операции с матрицами
Задание. Освоить выполнение операций над матрицами:
−транспонирование,
−вычисление определителя матрицы,
−нахождение обратной матрицы,
−сложение и вычитание матриц,
−умножение матрицы на число,
−умножение матриц.
Открыть файл с заданиями по Excel. После открытия книги перейти на чистый рабочий лист и переименовать его Лаб5.
Порядок выполнения задания.
5.1. Нахождение транспонированной матрицы.
Действия:
• Введите в диапазон ячеек А1:Е2 матрицу размера 2х5
2
A
1 2 3 4 5
6 7 8 9 0
•Выделите блок ячеек под транспонированную матрицу (5х2) - А4:В8.
•Нажмите кнопку или сочетание клавиш Shift+F3 или Формулы ► Библиотека функций ►
Ссылки и массивы.
Впоявившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Выберите функцию - имя функции ТРАНСП.
Врабочее поле Массив введите диапазон исходной матрицы А1:Е2. После чего нажмите сочетание клавиш CTRL+SHIFT+ENTER.
Врезультате в диапазоне А4:В8 появится транспонированная матрица:
• Подпишите матрицы.
5.2. Вычисление определителя матрицы.
Действия:
• Введите в диапазон ячеек А11:С13 матрицу:
1 2 3 A 0 2 3 1 0 3
•Табличный курсор поставьте в ячейку, в которой требуется получить значение определителя, например, в F12.
•Нажмите кнопку или сочетание клавиш Shift+F3 или Формулы ► Библиотека функций ► Математические.
В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите
Математические, а в рабочем поле Функция — имя функции МОПРЕД. После этого щелкните на кнопке ОК.
• Введите диапазон исходной матрицы А11:С1З в рабочее поле Массив (выделяя указателем мыши при нажатой левой кнопке необходимый диапазон). Нажмите кнопку ОК.
В ячейке F12 появится значение определителя матрицы — 6
3
.
• Подпишите матрицу и определитель.
5.3. Нахождение обратной матрицы
Действия:
•Выделите блок ячеек под обратную матрицу, например, блок ячеек А15:С17.
•Вызовите Вставку функции. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МОБР. Щелкните на кнопке ОК.
•Введите диапазон исходной матрицы А11:С1З в рабочее поле Массив (указателем мыши при нажатой левой кнопке).
•Нажмите сочетание клавиш CTRL+SHIFT+ENTER.
•В результате в диапазоне А15:С17 появится обратная матрица:
• Подпишите матрицы.
5.4. Сложение и вычитание матриц
Действия:
•Введите матрицу А в диапазон А25:С26, а матрицу В — в диапазон А29:С30. Необходимо найти матрицу С, являющуюся их суммой.
•Табличный курсор установите в левый верхний угол результирующей матрицы, например в
А33.
•Введите формулу для вычисления первого элемента результирующей матрицы =А25+А29.
•Скопируйте введенную формулу в остальные ячейки результирующей матрицы.
•В результате в ячейках А33:С34 появится матрица, равная сумме исходных матриц.
4
Подобным же образом вычисляется разность матриц, только в формуле для вычисления первого элемента вместо знака + ставится знак -.
• Подпишите матрицы.
5.5. Умножение матрицы на число
Действия:
Воспользуемся введенными матрицами из предыдущего задания (5.4) матрица А введена в
диапазоны А25:С26. Необходимо получить матрицу С=3хА.
•Табличный курсор поставьте в левый верхний угол результирующей матрицы, например в
F25.
•Введите формулу для вычисления первого элемента результирующей матрицы =3*А25.
•Скопируйте введенную формулу в остальные ячейки результирующей матрицы: поставьте
табличный курсор в ячейку F25.
В результате в ячейках F25:H26 появится матрица, равная исходной матрице, умноженной на постоянную — 3.
•Самостоятельно выполните умножение на -1/2.
•Подпишите матрицы.
5.6. Умножение матриц
Действия:
•Введите матрицу А в диапазон A37:D39, а матрица В — в диапазон А42:В45.
•Выделите блок ячеек под результирующую матрицу. Для этого требуется найти размер матрицы-
произведения. Ее размерность в данном примере будет 3х2. Выделите блок ячеек F40:G42.
• Нажмите кнопку или сочетание клавиш Shift+F3.
5
•В появившемся диалоговом окне Мастер функций в поле Категория выберите Математические, а в поле Функция — имя функции МУМН0Ж. После этого щелкните на кнопке ОК.
•Появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходных матриц и введите диапазон исходной матрицы А — A37:D39 в рабочее поле Массив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы В — А42:В45 введите в рабочее поле Массив2 (рис. 2.6). После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.
Рис. 2.6. Пример заполнения рабочих полей диалогового окна МУМНОЖ
• В результате в диапазоне F40:G42 появится произведение матриц:
• Подпишите матрицы.
Задание для самостоятельной работы.
1. Найдите транспонированную матрицу АТ(таблица №1).
2. Вычислить определитель матрицы А (таблица №1).
Таблица 1
1 |
5 |
9 |
2 |
4 |
5 |
9 |
2 |
5 |
4 |
4 |
0 |
|
2 |
3 |
4 |
3 |
3 |
|
2 |
7 |
3 |
5 |
1 |
|
5 |
7 |
2 |
4 |
2 |
|
4 |
2 |
5 |
2 |
4 |
|
4 |
5 |
8 |
6 |
8 |
|
6 |
4 |
5 |
2 |
4 |
|
6 |
5 |
2 |
3 |
7 |
|
3 |
3 |
2 |
1 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
6
2 |
3 |
4 |
3 |
|
1 |
2 |
10 |
5 |
6 |
10 |
|
7 |
2 |
|
|
5 |
6 |
5 |
|
2 |
3 |
|
3 |
4 |
2 |
|
2 |
2 |
|
|
4 |
|
9 |
3 |
|
7 |
5 |
|
2 |
2 |
4 |
|
5 |
3 |
|
1 |
|
4 |
1 |
|
1 |
2 |
|
6 |
8 |
7 |
|
4 |
1 |
|
3 |
7 |
5 |
|
2 |
3 |
|
2 |
1 |
7 |
|
0 |
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
1 |
4 |
3 |
4 |
|
5 |
|
11 |
2 |
7 |
4 |
2 |
3 |
|
|
2 |
8 |
7 |
10 |
13 |
|
|
1 |
3 |
2 |
9 |
2 |
|
|
|
2 |
5 |
6 |
13 |
11 |
|
|
3 |
7 |
5 |
2 |
3 |
|
|
|
2 |
2 |
7 |
7 |
|
2 |
|
|
1 |
2 |
2 |
3 |
2 |
|
|
1 |
8 |
5 |
3 |
|
10 |
|
|
1 |
4 |
1 |
1 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
4 |
3 |
3 |
5 |
3 |
7 |
|
12 |
4 |
6 |
8 |
1 |
2 |
||
|
5 |
4 |
7 |
3 |
10 |
|
|
1 |
7 |
2 |
3 |
1 |
||
|
6 |
6 |
13 |
3 |
13 |
|
|
2 |
8 |
12 |
7 |
4 |
||
|
4 |
2 |
6 |
1 |
|
6 |
|
|
7 |
9 |
17 |
27 |
6 |
|
|
2 |
3 |
4 |
3 |
|
6 |
|
|
8 |
3 |
6 |
2 |
37 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
5 |
9 |
2 |
1 |
3 |
10 |
|
13 |
3 |
12 |
6 |
1 |
2 |
||
|
2 |
6 |
1 |
0 |
|
1 |
|
|
3 |
10 |
6 |
|
1 |
1 |
|
1 |
3 |
1 |
2 |
|
1 |
|
|
2 |
5 |
4 |
|
3 |
0 |
|
1 |
6 |
5 |
1 |
|
2 |
|
|
2 |
10 |
10 |
9 |
15 |
|
|
0 |
1 |
4 |
2 |
|
3 |
|
|
9 |
7 |
4 |
4 |
9 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
4 |
5 |
6 |
5 |
11 |
|
14 |
2 |
10 |
2 |
|
1 |
5 |
|
|
1 |
4 |
2 |
0 |
13 |
|
|
1 |
3 |
2 |
|
9 |
2 |
|
|
1 |
1 |
0 |
1 |
|
5 |
|
|
3 |
13 |
2 |
8 |
7 |
|
|
3 |
2 |
3 |
0 |
|
7 |
|
|
1 |
6 |
3 |
|
2 |
3 |
|
4 |
1 |
2 |
3 |
|
8 |
|
|
4 |
4 |
7 |
11 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
3 |
3 |
5 |
7 |
9 |
|
|
15 |
1 |
3 |
3 |
1 |
1 |
|
|
2 |
1 |
2 |
3 |
4 |
|
|
|
3 |
6 |
7 |
2 |
3 |
|
|
5 |
3 |
3 |
4 |
7 |
|
|
|
7 |
3 |
6 |
2 |
2 |
|
|
4 |
3 |
2 |
1 |
2 |
|
|
|
0 |
4 |
7 |
2 |
5 |
|
|
3 |
7 |
5 |
3 |
3 |
|
|
|
2 |
7 |
5 |
1 |
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8 |
6 |
5 |
1 |
2 |
4 |
|
|
|
|
|
|
|
|
|
|
5 |
5 |
3 |
3 |
7 |
|
|
|
|
|
|
|
|
|
|
5 |
5 |
5 |
4 |
1 |
|
|
|
|
|
|
|
|
|
|
4 |
7 |
2 |
3 |
5 |
|
|
|
|
|
|
|
|
|
|
3 |
3 |
2 |
2 |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3. Найти произведение матриц А и В (таблицы №2):
|
1 |
2 |
2 |
1 |
|
k1 |
2 |
1 |
|
|
2 |
3 |
4 |
5 |
|
||||
А |
В |
1 |
k2 |
3 |
|||||
1 |
3 |
2 |
5 |
||||||
|
|
2 |
4 |
k3 |
|||||
|
|
|
|
|
|
||||
|
3 |
2 |
4 |
3 |
|
1 |
3 |
2 |
|
|
|
|
|
|
|
7
Таблица 2
№ варианта |
k1 |
k2 |
k3 |
|
|
|
|
1 |
-5 |
7 |
-3 |
|
|
|
|
2 |
2 |
5 |
-3 |
|
|
|
|
3 |
-2 |
3 |
1 |
|
|
|
|
4 |
4 |
3 |
-3 |
|
|
|
|
5 |
2 |
3 |
-2 |
|
|
|
|
4 |
4 |
3 |
-3 |
|
|
|
|
5 |
2 |
3 |
-2 |
|
|
|
|
6 |
4 |
-4 |
-3 |
|
|
|
|
7 |
-1 |
-2 |
3 |
|
|
|
|
8 |
2 |
-4 |
1 |
|
|
|
|
9 |
3 |
-5 |
2 |
|
|
|
|
10 |
5 |
2 |
-3 |
|
|
|
|
11 |
1 |
3 |
-1 |
|
|
|
|
12 |
2 |
2 |
-1 |
|
|
|
|
13 |
3 |
-4 |
5 |
|
|
|
|
14 |
2 |
-3 |
1 |
|
|
|
|
15 |
3 |
4 |
3 |
|
|
|
|
4.Найдите произведение матриц ВхА из предыдущего задания.
5.Найти сумму матриц А и В (таблицы №2).
6.Найти разность матриц А и В (таблицы №2):
7. Задана матрица А (таблица №3). Найти матрицу А-1 и установить, что АА-1 = Е.
Таблица 3
1 |
2 |
2 |
3 |
9 |
4 |
2 |
1 |
|
|
|
|
|
|||
|
1 |
1 |
0 |
|
5 |
3 |
2 |
|
|
|
|
|
|||
|
1 |
2 |
1 |
|
3 |
2 |
1 |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
2 |
4 |
2 |
3 |
10 |
1 |
2 |
3 |
|
|
|
|
|
|||
|
1 |
1 |
0 |
|
3 |
2 |
4 |
|
|
|
|
|
|||
|
3 |
2 |
2 |
|
2 |
1 |
0 |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
3 |
2 |
1 |
1 |
11 |
5 |
3 |
1 |
|
4 |
6 |
5 |
|
1 |
5 |
2 |
|
3 |
5 |
4 |
|
5 |
2 |
1 |
|
|
|
|
|
|
|
|
8
4 |
2 |
3 |
4 |
12 |
2 |
3 |
1 |
|
1 |
2 |
3 |
|
4 |
5 |
2 |
|
1 |
3 |
6 |
|
5 |
7 |
3 |
|
|
|
|
|
|
|
|
5 |
1 |
0 |
1 |
13 |
2 |
6 |
3 |
|
0 |
1 |
2 |
|
3 |
2 |
3 |
|
1 |
2 |
4 |
|
4 |
3 |
4 |
|
|
|
|
|
|
|
|
6 |
17 |
10 |
4 |
14 |
3 |
1 |
2 |
|
1 |
1 |
0 |
|
4 |
3 |
3 |
|
2 |
3 |
3 |
|
3 |
0 |
2 |
|
|
|
|
|
|
|
|
7 |
3 |
3 |
2 |
15 |
4 |
3 |
3 |
|
4 |
3 |
2 |
|
2 |
3 |
2 |
|
2 |
2 |
1 |
|
5 |
5 |
4 |
|
|
|
|
|
|
|
|
8 |
2 |
1 |
4 |
|
|
|
|
|
3 |
2 |
4 |
|
|
|
|
|
2 |
1 |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
9