учебное пособие : Excel в математических и статистических расчетах - twidler.ru ▲ Вверх
рефераты
сочинения
Решебники (ГДЗ)
топики
краткие содержания
биографии
Рефераты - Математика - Excel в математических и статистических расчетах
Название:

Excel в математических и статистических расчетах

Раздел: Математика
Тип: учебное пособие
Дата добавления: 19.01.2014
Размер: 1284 кб
Короткая ссылка:
Оценить работу:
голосов (0)
Просмотров: 933

Министерство сельского хозяйства РФ
Федеральное государственное образовательное учреждение
высшего профессионального образования
«Мичуринский государственный аграрный университет»
Кафедра информатики
__________________________________________________________________________
Утверждено Протокол № 6 экономического факультета от 14 февраля 2007
ПРАКТИКУМ
для самостоятельной работы студентов
Часть 1
и
Мичуринск-наукоград 2007
Методическое пособие разработано старшим преподавателем кафед-
ры информатики М.А. Ильченко, старшим лаборантом Л.С. Струковой на основании государственных образовательных стандартов высшего про- фессионального образования. Методическое пособие рекомендовано сту- дентам, изучающим математику, преподавателям, а также аспирантам, сталкивающимся с необходимостью математической обработки данных.
Рецензент:
Г.Б. Ширяева ст. преподаватель кафедры математического моделирова-
ния экономических систем
Рассмотрено на заседании кафедры Протокол № 63 от 14 декабря 2006г.
Технический редактор – Т.И. Медведева
Отпечатано в издательско-полиграфическом центре
ФГОУ ВПО МичГАУ
Подписано в печать 19.03.07. г. Формат 60х84 1 /16,
Заказ №
Бумага офсетная № 1. Усл.печ.л. 4,8 Тираж 75 экз. Ризограф
Издательско-полиграфический центр
Мичуринского государственного аграрного университета
_______________________________________________________________
393760, Тамбовская обл., г. Мичуринск, ул. Интернациональная, 101,
тел. +7 (47545) 5-55-12
E-mail: vvdem@mgau.ru
©Издательство Мичуринского государственного аграрного университета, 2007
2
Содержание
1. Аналитическая геометрия_______________________________________ 4
Поверхности в трехмерном пространстве__________________________ 4
Плоскость ____________________________________________________ 4 УПРАЖНЕНИЯ _______________________________________________ 8 Поверхности второго порядка в пространстве. ______________________ 8 УПРАЖНЕНИЯ ______________________________________________ 18
2. Линейная алгебра ______________________________________________ 20
Матрицы _____________________________________________________ 20 Операции с матрицами _________________________________________ 22
Транспонирование ____________________________________________ 22 Вычисление определителя матрицы______________________________ 24 Нахождение обратной матрицы _________________________________ 26 Сложение и вычитание матриц __________________________________ 28 Умножение матрицы на число __________________________________ 29 Умножение матриц____________________________________________ 30
УПРАЖНЕНИЯ _______________________________________________ 34 Решение систем линейных уравнений____________________________ 37
Система n линейных уравнений с n неизвестными. _________________ 37 УПРАЖНЕНИЯ ______________________________________________ 42 Система m линейных уравнений с n неизвестными _________________ 43 УПРАЖНЕНИЯ ______________________________________________ 47
3. Задачи оптимизации ___________________________________________ 48
Постановка задачи оптимизации ________________________________ 48 Решение уравнений с одним неизвестным ________________________ 49 УПРАЖНЕНИЯ _______________________________________________ 52 Линейное программирование ___________________________________ 52 УПРАЖНЕНИЯ _______________________________________________ 62 Аппроксимация экспериментальных данных _____________________ 63
Одна независимая переменная __________________________________ 64
УПРАЖНЕНИЯ _______________________________________________ 74
Несколько независимых переменных_____________________________ 75
УПРАЖНЕНИЯ _______________________________________________ 83
3
1. АНАЛИТИЧЕСКАЯ ГЕОМЕТРИЯ
Поверхности в трехмерном пространстве
В этом разделе мы рассмотрим плоскость, а также поверх-
ности второго порядка: эллипсоид, гиперболоид, параболоид и конус второго порядка.
Плоскость
Любое линейное уравнение определяет плоскость и, наобо-
рот, уравнение любой плоскости есть уравнение первой степени.
Уравнение вида: Ax + By +Cz + D = 0 называется общим
уравнением плоскости.
Важные частные случаи уравнения плоскости возникают
при равенстве нулю некоторых из коэффициентов А, В, Си D. Если D = 0, то уравнение Ах + By + Cz = 0 определяет плоскость, проходящую через начало координат. Если А = 0, то уравнение By + Cz + D = 0 определяет плоскость, параллельную оси Ох, ес- ли Л = D = О, то уравнение By + Cz = 0 определяет плоскость, проходящую через ось Ох, если А = В = 0, то уравнение Cz + D = 0 определяет плоскость, параллельную плоскости Оху; если А = В = D = 0, то уравнение Cz = 0 (или 2 = 0) определяет координатную плоскость Оху.
Существует также ряд уравнений, определяющих плоско-
сти, обладающие специальными свойствами:
1. Уравнение плоскости в отрезках:
x y z
+ + = 1 ,
a b c
а,b,с — отрезки, отсекаемые плоскостью на осях координат
с учетом знака.
2. Уравнение плоскости, проходящей через заданную точку
M(x ,y ,z ):
1 1 2
a ( x − x ) + b ( y − y ) + c ( z − z ) = 0 .
1 1 1
3. Уравнение плоскости, проходящей через три точки
M(x ,y ,z1), M(x ,y ,z ), M(x ,y ,z ):
1 1 2 2 2 3 3 3
x − x y − y z − z
1 1 1
x − x y − y z − z = 0 .
1 2 1 2 1
x − x y − y z − z
1 3 1 3 1
4
В MS Excel Мастер диаграмм может быть также использо-
ван и для построения плоскостей. Необходимо ввести точки плоскости в рабочую таблицу, вызвать Мастер диаграмм, задать тип диаграммы, диапазоны данных и подписей оси x, ввести на- звания осей.
Пример. Рассмотрим построение плоскости в Excel на
примере уравнения 2 x + 4 y − 2 z + 2 = 0 . Пусть необходимо по-
строить часть плоскости, лежащей в I квадранте ( x ∈ [ 0 ; 6 ] с ша-
гом ? 0 , 5 , y ∈ [ 0 ; 6 ] с шагом ? = 1 ).
Решение. Вначале необходимо разрешить уравнение отно-
сительно переменной z, В примере z = x + 2 y + 1 .
Введем значения переменной x в столбец A. Для этого в
ячейку А1 вводим символ x. В ячейку А2 вводится первое значе- ние аргумента — левая граница диапазона (0). В ячейку A3 вво- дится второе значение аргумента — левая граница диапазона плюс шаг построения (0,5). Затем, выделив блок ячеек А2:АЗ, ав- тозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А16).
Значения переменной y вводим в строку 1. Для этого в ячей-
ку В1 вводится первое значение переменной — левая граница диапазона (0). В ячейку С1 вводится второе значение переменной — левая граница диапазона плюс шаг построения (1). Затем, вы- делив блок ячеек В1:С1, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки H1).
Далее вводим значения переменной z. В ячейку В2 вводим
ее уравнение — = $А2 + 2*B$1 + 1, предварительно переклю- чившись на английский язык (Alt+Shift). Обращаем внимание, что символы $ предназначены для фиксации адреса столбца. А — переменной x и строки 1 — переменной у. Затем автозаполнени- ем (протягиванием вправо) копируем эту формулу вначале в диа- пазон В2:Н2, после чего — в диапазон ВЗ:Н14 (протягиванием вниз).
В результате должна быть получена следующая таблица
(рис. 1).
5
Рис. 1. Пример подготовки данных для построения плоскости
На панели инструментов Стандартная необходимо нажать
кнопку Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указываем тип диаграммы — Поверхность, и вид — Проволочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.
В появившемся диалоговом окне Мастер диаграмм (шаг 2
из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных В2:Н14.
Далее необходимо указать в строках или столбцах располо-
жены ряды данных. Это определит ориентацию осей х и у. В примере переключатель Ряды в с помощью указателя мыши ус- тановим в положение столбцах.
Выбираем вкладку Ряд и в поле Подписи оси X указываем
диапазон подписей. Для этого следует активизировать поле Под- писи оси X, щелкнув в нем указателем мыши, и ввести диапазон подписей оси х — А2:А14.
Далее вводим значения подписей оси у. Для этого в рабочем
поле Ряд указываем первую запись Ряд У и в рабочее Поле имя, активировав его указателем мыши, вводим первое значение пе- ременной у — 0. Затем в поле Ряд указываем вторую запись Ряд 2 и в рабочее поле Имя вводим второе значение переменной у — 1.
6
Повторяем таким образом до последней записи — Ряд 7. В ре- зультате вкладка Ряд будет иметь следующий вид (рис. 2).
Рис. 2. Пример заполнения вкладки Ряд
После появления требуемых записей необходимо нажать кнопку Далее. В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле Название диа- граммы, ввести с клавиатуры в данное поле название: Плоскость. Затем аналогичным образом ввести в рабочие поля Ось X (кате- горий), Ось Y (рядов данных) и Ось Z (значений) соответствую- щие названия: x, y и z.
Далее нажать кнопку Готово, и после небольшого редакти-
рования будет получена следующая диаграмма (рис. 3).
7
Плоскость
20
15
z 10
5
0 5
0 1 2 3 4 5 6 0 y
x
Рис 3. Диаграмма плоскости из примера
УПРАЖНЕНИЯ 1.
Построить плоскость, параллельную плоскости Оху и пересе-
кающую ось Ozв точке М(0, 0, 2). Диапазоны изменения пере- менных х и у:
x ∈ [ ] 0 ; 6 с шагом ? = 0 , 5 , y ∈ [ − 1 ; 3 ] с шагом ? = 1 .
2. Построить плоскость, отсекающую на координатных осях от-
резки а = 3, b = 2 и с = 1. Диапазоны изменения переменных х и у.х
[ ]
x ∈ − 1 ; 4 с шагом ? = 0,5, у ∈ [-1;3] с шагом ?= 1.
3. Построить плоскость, проходящую через точки М (3,3,1), М
1 2
(2,3, 2), М (1,1,3). Диапазоны изменения переменных
3
хи у: х ∈ [-1;4] с шагом ? = 0,5,y ∈ [-1;3] с шагом ? = 1.
Поверхности второго порядка в пространстве.
Общее уравнение поверхностей второго порядка имеет вид
уравнения второй степени:
Ах 2 + By 2 + Cz 2 +2Dxy + 2Eyz+ 2Fzx + 2Gx + 2Hy + 2Kz + L = 0 (1)
Причем коэффициенты А, В, С, D, E, F не могут быть равны
нулю одновременно.
Частными случаями уравнения (1) являются основные по-
верхности второго порядка: эллипсоид, гиперболоид и параболо- ид.
8
Эллипсоид Эллипсоидом называется поверхность, которая в некоторой
системе декартовых прямоугольных координат определяется уравнением:
x 2 y 2 z 2
+ + = 1 . (2)
a 2 b 2 c 2
Это уравнение называется каноническим уравнением эллип-
соида.
Эллипсоид, представляет собой замкнутую овальную по-
верхность, обладающую тремя взаимно перпендикулярными плоскостями симметрии.
Для построения эллипсоида в Excel каноническое уравнение
(2) необходимо разрешить относительно переменной z (предста- вить в виде z=ƒ(x,y)).
Пример. Рассмотрим построение эллипсоида на примере
уравнения:
x 2 y 2
+ + z 2 = 1 .
9 4
Пусть необходимо построить верхнюю часть эллипсоида,
лежащую в диапазонах: х ∈ [-3;3],y ∈ [-2; 2] с шагом ? = 0,5 для
обеих переменных.
Решение.Вначале необходимо разрешить уравнение отно-
сительно переменной z.
В примере
x 2 y 2
z = 1 − − .
9 4
Введем значения переменной х в столбец А. Для этого в
ячейку А1 вводим символ х. В ячейку А2 вводится первое значе- ние аргумента — левая граница диапазона (-3). В ячейку A3 вво- дится второе значение аргумента — левая граница диапазона плюс шаг построения (-2,5). Затем, выделив блок ячеек А2:АЗ, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки А14).
Значения переменной у вводим в строку 1. Для этого в ячей-
ку В1 вводится первое значение переменной — левая граница диапазона (-2). В ячейку С1 вводится второе значение перемен-
9
ной — левая граница диапазона плюс шаг построения (-1,5). За- тем, выделив блок ячеек В1:С1, автозаполнением получаем все значения аргумента (за правый нижний угол блока протягиваем до ячейки J1).
Далее вводим значения переменной z. Для этого табличный
курсор необходимо поместить в ячейку В2 и на панели инстру- ментов Стандартная нажать кнопку Вставка функции (ƒ
). В поя-
x
вившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функ- ция выбираем функцию Корень. Нажимаем кнопку ОК. Появля- ется диалоговое окно Корень. В рабочее поле вводим подкорен- ное выражение: 1 -$А2^2/9-В$1^2/4. Обращаем внимание, что символы $ предназначены для фиксации адреса столбца А — пе- ременной х и строки 1 — переменной у. Нажимаем кнопку ОК. В ячейке В2 появляется # ЧИСЛО! (при х = -3 и у = -2 точек рас- сматриваемого эллипсоида не существует). Теперь необходимо скопировать функцию из ячейки В2. Для этого автозаполнением (протягиванием вправо) копируем эту формулу вначале в диапа- зон B2.-J2, после чего (протягиванием вниз) — в диапазон ВЗ:Л4.
В результате должна быть получена следующая таблица
(рис.4).
Рис 4. Результаты вычислений точек эллипсоида из примера
Для построения диаграммы на панели инструментов Стан-
дартная необходимо нажать кнопку Мастер диаграмм. В появив- шемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диа-
10
граммы указываем тип диаграммы — Поверхность, и вид — Про- волочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из
4): источник данных диаграммы необходимо выбрать, вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных B2:J14.
Далее необходимо указать в сороках или столбцах располо-
жены ряды данных. Это определит ориентацию осей х и у. В примере переключатель Ряды в с помощью указателя мыши ус- тановим в положение столбцах.
Выбираем вкладку Ряд ив поле Подписи оси X указываем
диапазон подписей. Для этого щелкните в нем указателей мыши и введите диапазон подписей оси х —А2:А14.
Вводим значения подписей оси у. Для этого в рабочем поле
Ряд указываем первую запись Ряд 1 и в рабочее поле Имя, акти- визировав его указателем мыши, вводим первое значение пере- менной у ? ? 2. Затем в поле Ряд указываем вторую запись Ряд 2 ив рабочее поле Имя вводим второе значение переменной у ? 1,5. Повторяем таким образом до последней записи — Ряд 9.
После появления требуемых записей необходимо нажать
кнопку Далее.
В третьем окне требуется ввести заголовок диаграммы и на-
звания осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле На- звание диаграммы указателем мыши, ввести с клавиатуры в поле название: Эллипсоид. Затем аналогичным образом ввести в рабо- чие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (зна- чений) соответствующие названия: х, у и z.
Далее следует нажать кнопку Готово, и после небольшого
редактирования будет получена следующая диаграмма (рис. 5).
11
Элипсоид
1
0,8
0,6
z
0,4
0,2
0 0,5
- 3 - 2 - 1 0 1 2 3 -2 y
x
Рис. 5. Верхняя часть эллипсоида из примера
Гиперболоид Существует два вида гиперболоидов: однополостные и дву-
полостные.
Однополостным гиперболоидом называется поверхность,
которая в некоторой системе декартовых прямоугольных коор- динат определяется уравнением
x 2 y 2 z 2
+ − = 1 . (3)
a 2 b 2 c 2
Однополостный гиперболоид имеет вид бесконечной труб-
ки, расширяющейся в обе стороны от горловины.
Двуполостным гиперболоидом называется поверхность, оп-
ределяемая уравнением
x 2 y 2 z 2
+ + = − 1 . (4)
a 2 b 2 c 2
Двуполостный гиперболоид представляет собой поверх-
ность, состоящую из двух отдельных полостей, каждая из кото- рых имеет вид бесконечной выпуклой чаши. Уравнения (3) и (4) называются каноническими уравнениями гиперболоидов.
Для построения гиперболоида в Excel канонические уравне-
ния (3) или (4), как и в случае с эллипсоидом, необходимо разре- шить относительно переменной z (представить в виде z = ƒ(x, y)).
Пример. Рассмотрим построение двуполостного гипербо-
лоида вида.
12
x 2 y 2
+ − z 2 = 1 .
9 4
Пусть необходимо построить верхнюю часть гиперболоида,
лежащую в диапазонах: х ∈ [-3; 3], у ∈ [-2; 2] с шагом ? = 0,5 для
обеих переменных.
Решение. Вначале необходимо разрешить уравнение отно-
сительно переменной z. В примере
x 2 y 2
z = 1 − + .
9 4
Введем значения переменной х в столбец А. Для этого в
ячейку А1 вводим символ х. В ячейку А2 вводится первое значе- ние аргумента — левая граница диапазона (-3). В ячейку A3 — второе значение аргумента — левая граница диапазона плюс шаг построения (-2,5). Затем, выделив блок ячеек А2:АЗ, автозаполне- нием получаем все значения аргумента.
Значения переменной у вводим в строку 1. Для этого в ячей-
ку В1 вводится первое значение переменной — левая граница диапазона (-2). В ячейку С1 — второе значение переменной — левая граница диапазона плюс шаг построения (-1,5). Затем, вы- делив блок ячеек В1:С1, автозаполнением получаем все значения аргумента.
Далее вводим значения переменной z. Для этого табличный
курсор необходимо поместить в ячейку В2 и на панели инстру- ментов Стандартная нажать кнопку Вставка функции (f
). В поя-
x
вившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функ- ция выбираем функцию Корень. Нажимаем кнопку ОК. Появля- ется диалоговое окно Корень. В рабочее поле вводим подкорен- ное выражение: 1 + $А2^2/9 + В$1^2/4. Нажимаем кнопку ОК. В ячейке В2 появляется 1,732051. Теперь необходимо скопировать функцию из ячейки В2. Для этого автозаполнением (протягива- нием вправо) копируем эту формулу вначале в диапазон B2:J2, после чего (протягиванием вниз) — в диапазон ВЗ:J4.
В результате должна быть получена следующая таблица
(рис. 6).
13
Рис. 6. Результаты вычислений точек гиперболоида из примера
Для построения диаграммы на панели инструментов Стан-
дартная необходимо нажать кнопку Мастер диаграмм. В появив- шемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диа- граммы указываем тип диаграммы — Поверхность, и вид — Про- волочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в налоговом окне.
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из
4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных В2:J4.
Далее необходимо указать в строках или столбцах располо-
жены ряды данных. Это определит ориентацию осей х и у. В примере переключатель Ряды в с помощью указателя мыши ус- тановим в положение столбцах.
Выбираем вкладку Ряд и в поле Подписи оси X указываем
диапазон подписей. Для этого следует активизировать данное по- ле, щелкнув в нем указателем мыши, и ввести в него диапазон подписей оси х — А2:А14.
Вводим значения подписей оси у. Для этого в рабочем поле
Ряд выбираем первую запись Ряд 1 ив рабочее поле Имя, активи- зировав его указателем мыши, вводим первое значение перемен- ной у 2. Затем в поле Ряд выбираем вторую запись — Ряд 2 и в рабочее поле Имя вводим второе значение переменной у 1,5. По- вторяем таким образом до последней записи — Ряд 9.
14
После появления требуемых записей необходимо нажать
кнопку Далее.
В третьем окне требуется ввести заголовок диаграммы и на-
звания осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Далее щелкнув в рабочем поле Название диаграммы, ввести в него с клавиатуры название: Дву- полостной гиперболоид. Затем аналогичным образом ввести в ра- бочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: x,y и z.
Далее следует нажать кнопку Готово, и после небольшого
редактирования будет получена следующая диаграмма (рис. 7).
Двухполостный гиперболоид
2
1,5
z 1
0,5
0 1
- 3 - 2 - 1 0 1 2 3 -2 y
x
Рис. 7. Верхняя часть двуполостного гиперболоида
Параболоид
Существует два вида параболоидов: эллиптические и гипер-
болические.
Эллиптическим параболоидом называется поверхность, ко-
торая в некоторой системе декартовых прямоугольных координат определяется уравнением
x 2 y 2
+ = 2 z . (5)
p q
Эллиптический параболоид имеет вид бесконечной выпук-
лой чаши. Он обладает двумя взаимно перпендикулярными плос- костями симметрии. Точка, с которой совмещено начало коорди- нат, называется вершиной эллиптического параболоида; числа р и q называются его параметрами.
15
Гиперболическим параболоидом называется поверхность,
определяемая уравнением
x 2 y 2
− = 2 z .
p q
Гиперболический параболоид имеет форму седла. Он обла-
дает двумя взаимно перпендикулярными плоскостями симмет- рии. Точка, с которой совмещено начало координат, называется вершиной гиперболического параболоида; числа р и q называют- ся его параметрами.
Пример. Рассмотрим построение гиперболического парабо-
лоида вида
x 2 y 2
− = 2 z .
9 4
Пусть необходимо построить часть параболоида, лежащую в
диапазонах: х ∈ [-3; 3], у ∈ [-2; 2] с шагом ? = 0,5 для обеих пе-
ременных.
Решение. Вначале необходимо разрешить уравнение отно-
сительно переменной z. В примере
x 2 y 2
z = − .
18 8
Введем значения переменной л: в столбец А. Для этого в
ячейку А1 вводим символ х. В ячейку А2 вводится первое значе- ние аргумента — левая граница диапазона (—3). В ячейку A3 — второе значение аргумента — левая граница диапазона плюс шаг построения (-2,5). Затем, выделив блок ячеек А2:АЗ, автозапол- нением получаем все значения аргумента.
Значения переменной у вводим в строку 1. Для этого в ячей-
ку В1 вводится первое значение переменной — левая граница диапазона (-2). В ячейку С1 — второе значение переменной — левая граница диапазона плюс шаг построения ( -1,5). Затем, вы- делив блок ячеек В1:С1, автозаполнением получаем все значения аргумента.
Далее вводим значения переменной г. Для этого табличный
курсор необходимо поместить в ячейку В2 и ввести формулу — = $А2^2/18-В$1^2/8, после чего нажать клавишу Enter. В ячейке В2 появляется 0. Теперь необходимо скопировать функцию из ячей- ки В2. Для этого автозаполнением (протягиванием вправо) копи-
16
руем эту формулу вначале в диапазон B2:J2, после чего (протяги- ванием вниз) — в диапазон В2:J4.
В результате в диапазоне В2:J14. Для построения диаграм-
мы на панели инструментов Стандартная необходимо нажать кнопку Мастер диаграмм. В появившемся диалоговом окне Мас- тер диаграмм (шаг 1 из 4): тип диаграммы указываем тип диа- граммы — Поверхность, и вид — Проволочная (прозрачная) по- верхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из
4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных B2:J14.
Далее необходимо указать в строках или столбцах располо-
жены ряды данных. Это определит ориентацию осей х и y. В примере переключатель Ряды в с помощью указателя мыши ус- тановим в положение столбцах.
Выбираем вкладку Ряд и в поле Подписи оси X указываем
диапазон подписей. Для этого следует активизировать данное Поле, щелкнув в нем указателем мыши, и ввести диапазон подпи- сей оси х — А2:А14.
Вводим значения подписей оси у. Для этого в рабочем поле
Ряд выбираем первую запись Ряд 1 и, активизировав рабочее по- ле Имя указателем мыши, вводим первое значение переменной у
2. Затем в поле Ряд выбираем вторую запись Ряд 2 и в рабо-
чее поле Имя вводим второе значение переменной у? ?1,5. По- вторяем таким образом до последней записи — Ряд 9.
После появления требуемых записей следует нажать кнопку
Далее.
В третьем окне требуется ввести заголовок диаграммы и на-
звания осей. Для этого нужно выбрать вкладку Заголовки, щелк- нув на ней указателем мыши. После чего в рабочее поле Название диаграммы ввести с клавиатуры название: Гиперболический па- раболоид. Затем аналогичным образом ввести в рабочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответ- ствующие названия: х, у и z.
Далее нужно нажать кнопку Готово, и после небольшого ре-
дактирования будет получена следующая диаграмма (рис. 8).
17
Гиперболический параболоид
0,6
0,4
0,2
z 0
-0,2
-0,4
-0,6 1
- 3 - 2 - 1 0 1 2 3 -2 y
x
Рис 8. Гиперболический параболоид
Конус второго порядка Конусом второго порядка называется поверхность, которая
в некоторой системе декартовых прямоугольных координат оп- ределяется уравнением
x 2 y 2 z 2
+ − = 0 .
a 2 b 2 c 2
Конус образован прямыми линиями (образующими), прохо-
дящими через начало координат (вершина конуса). Сечение ко- нуса плоскостью, не проходящей через начало координат, дает эллипс.
В Excel построение конуса второго порядка аналогично по-
строению других поверхностей, рассмотренных ранее. УПРАЖНЕНИЯ 1.
Построить верхнюю часть эллипсоида:
x 2 y 2 z 2
+ + = 1 .
4 9 4
Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом
? = 0,5, у ∈ [-3; 3] с шагом ? = 1.
2. Построить верхнюю часть однополостного гиперболоида:
x 2 y 2 z 2
+ − = 1 .
4 9 4
18
Диапазоны изменения переменных х и у: х ∈ [-3; 3] с шагом
? = 0,5, у ∈ [-4; 4] с шагом ? =1.
3. Построить эллиптический параболоид:
x 2 y 2
+ = 2 z .
9 4
Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом
? = 0,5, у ∈ [-3; 3] с шагом ? =1.
4. Построить верхнюю часть конуса
x 2 y 2 z 2
+ − = 0 .
4 9 4
Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом
? = 0,5, y ∈ [-3;3] с шагом ? = 1.
19
2. ЛИНЕЙНАЯ АЛГЕБРА
Средства MS Excel оказываются весьма полезны в линейной
алгебре, прежде всего для операций с матрицами и решения сис- тем линейных уравнений.
Значительная часть математических моделей различных
объектов и процессов записывается в достаточно простой и ком- пактной матричной форме. В частности, при решении линейных уравнений мы имеем дело с матрицами и арифметическими дей-
Матрицы
ствиями с ними. Что же такое матрица? Как выполняются дейст- вия с матрицами?
Матрицей размера m × n называется прямоугольная таблица
чисел, содержащая m строк и n столбцов. Матрицы обозначаются прописными (заглавными) буквами латинского алфавита. Числа, составляющие матрицу, называются элементами матрицы и обо- значаются строчными буквами с двойной индексацией: a
, где
ij
i — номер строки, j— номер столбца. Например, матрица А раз- мера
m × n может быть представлена в виде:
? a a K a ?
? 11 12 1 n ?
A = ? a 21 a 22 K a 2 n ? = ( a ),
? K K K K ? ij
? ?
? a a K a ?
m 1 m 2 3 n
где i=1, …, m; j=1, …, n. Две матрицы А и В одного размера называются равными,
если они совпадают поэлементно, то есть a = b для любых
ij
i = 1,2,..., m; j = 1,2,..., n. Матрица, состоящая из одной строки, называется матрицей (вектором) - строкой:
A = ( a a K a ),
11 12 1 n
а из одного столбца — матрицей (вектором) - столбцом:
? b ?
? 11 ?
B = ? b 21 ?
? K ? .
? ?
? b ?
m 1
20
Если число строк матрицы равно числу столбцов и равно и,
то такую матрицу называют квадратной n -го порядка. Напри- мер, квадратная матрица 2-го порядка:
? 1 3 ?
A = ? ? .
? 4 5 ?
Если у элемента матрицы a номер столбца равен номеру
ij
строки (i = j), то такой элемент называется диагональным. Диаго- нальные элементы образуют главную диагональ матрицы.
Квадратная матрица с равными нулю всеми недиагональ-
ными элементами называется диагональной.
Квадратная матрица называется единичной, если она диаго-
нальная, и все диагональные элементы равны единице. Единич- ная матрица имеет следующий вид:
? 1 0 K K K 0 ?
? ?
? 0 1 0 K K 0 ?
E = ? ? 0 0 1 0 K 0 ? ? .
? 0 0 0 1 0 0 ?
? K K K K K K ?
? ?
? ? 0 K K K 0 1 ? ?
Различают единичные матрицы первого, второго, третьего и
т. д. порядков:
E = 1 ;
? 1 0 ?
E = ? ? ;
? 0 1 ?
? 1 0 0 ?
? ?
E = ? 0 1 0 ? .
? ? 0 0 1 ? ?
Матрица любого размера называется нулевой или нуль -
матрицей, если все ее элементы равны нулю:
? 0 0 K 0 ?
? ?
0 = ? 0 0 K 0 ?
m × n ? K K K K ? .
? ?
? 0 0 K 0 ?
21
Операции с матрицами
Как и над числами, над матрицами можно проводить ряд
операций, причем в случае с матрицами некоторые из операций являются специфическими.
Транспонирование
Транспонированной называется матрица (A T ), в которой
столбцы исходной матрицы (А) заменяются строками с соответ- ствующими номерами.
В сокращенной записи, если А = (a ), то (A T ) = (а ).
ij ji
? 3 − 7 11 ?
? ?
A = ? 18 19 39 ? ;
? ? − 5 91 87 ? ?
? 3 18 − 5 ?
? ?
A T = ? − 7 19 91 ?
? ? 11 39 87 ? ?
Для обозначения транспонированной матрицы иногда ис-
пользуют символ «’» (А’). Транспонированием называется опе- рация перехода от исходной матрицы (А) к транспонированной (A
T ).
Из определения транспонированной матрицы следует, что
если исходная матрица А имеет размер m × n , то транспонирован-
ная матрица (A T ) имеет размер m × n . Для осуществления транс-
понирования в Excel используется функция ТРАНСП, которая позволяет поменять ориентацию массива на рабочем листе с вер- тикальной на горизонтальную и наоборот.
Функция имеет вид ТРАНСП (массив). Здесь массив – это
транспонируемый массив или диапазон ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и т.д.
Пример. Предположим, что в диапазон ячеек А1:Е2 введена
матрица размера 2 × 5
? 1 2 3 4 5 ?
A = ? ? .
? 6 7 8 9 0 ?
Необходимо получить транспонированную матрицу.
22
Решение 1.
Выделите (указателем мыши при нажатой левой кноп-
ке) блок ячеек под транспонированную матрицу 2 × 5 .Например,
А4:В8.
2. Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
3. В появившемся диалоговом окне Мастер функций в рабо-
чем поле Категория выберите Ссылки и массивы, а в рабочем по- ле Функция — имя функции ТРАНСП (рис. 9). После этого щелкните на кнопке ОК.
Рис. 9. Пример выбора вида функции в диалоговом окне
Мастер функций
4. Появившееся диалоговое окно ТРАНСП мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис- ходной матрицы А1:Е2 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После чего нажмите сочетание кла- виш CTRL+SHIFT+ENTER (рис. 10).
23
Рис. 10. Пример заполнения диалогового окна ТРАНСП
5. Если транспонированная матрица не появилась в диапа-
зоне А4:В8, то следует щелкнуть указателем мыши в строке фор- мул и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А4:В8 появится транспонированная матрица:
? 1 6 ?
? ?
? 2 7 ?
A = ? 3 8 ? .
? ?
? 4 9 ?
? ? 5 0 ? ?
Вычисление определителя матрицы
Важной характеристикой квадратных матриц является их
определитель. Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Определитель матрицы А обозначается как
A или ?.
Определителем матрицы первого порядка А = (a ), или оп-
ij
ределителем первого порядка, называется элемент a .
ij
Определителем матрицы второго порядка А = (a )или определи-
ij
телем второго порядка, называется число» которое вычисляется по формуле:
? a a ?
? = A = ? 11 12 ? = a a − a a
2 ? a a ? 11 22 12 21 .
21 22
24
Произведения a a и a a называются членами опреде-
11 22 12 21 .
лителя второго порядка.
С ростом порядка матрицы n резко увеличивается число
членов определителя (n!). Например, при n = 4 имеем 24 слагае- мых. Существуют специальные правила, облегчающие вычисле- ние определителей вручную, учитываются свойства определите- лей и т. п. При, применении компьютера в использовании этих приемов нет необходимости.
В MS Excel для вычисления определителя квадратной мат-
рицы используется функция МОПРЕД.
Функция имеет вид МОПРЕД (массив). Здесь массив — это числовой массив, в котором хранится
матрица с равным количеством строк и столбцов. При этом мас- сив может быть задан как интервал ячеек, например, А1:СЗ; или как массив констант, например, {1;2;3:4;5;6:7;8;9}. Для массива А1:СЗ, состоящего из трех строк и трех столбцов (матрица разме- ром 3 х 3), определитель вычисляется следующим образом:
?(А1:СЗ) = А1 х (В2 х СЗ - ВЗ х С2) + А2 х (ВЗ х С1 - В1 х
СЗ) + A3 х (В1 х С2 - В2 х С1).
Рассмотрим пример нахождения определителя матрицы. Пример. Предположим, что в диапазон ячеек А1:СЗ введе-
на матрица:
? 1 2 3 ?
? ?
A = ? 0 2 3 ? .
? ? 1 0 3 ? ?
Необходимо вычислить определитель этой матрицы. Решение 1. Табличный курсор поставьте в ячейку, в которой требует-
ся получить значение определителя, например, в А4.
2. Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
3. В появившемся диалоговом окне Мастер функций в рабо-
чем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МОПРЕД. После этого щелкните на кнопке ОК.
25
4. Появившееся диалоговое окно МОПРЕД мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис- ходной матрицы А1:СЗ в рабочее поле Массив (указателем мыши при нажатой левой кнопке). Нажмите кнопку ОК (рис. 11).
Рис 11. Пример заполнения диалогового окна МОПРЕД
В ячейке А4 появится значение определителя матрицы — 6.
Нахождение обратной матрицы
Для каждого числа a ≠ 0 (Cуществует обратное число a -1 , и
для квадратных матриц вводится аналогичное понятие Обратные матрицы обычно используются для решения систем уравнений с несколькими неизвестными.
Матрица A -1 называется обратной по отношению к квад-
ратной матрице А, если при умножении этой матрицы на данную как слева, так и справа получается единичная матрица:
A × A − 1 = A − 1 × A = E .
Как следует из определения, обратная матрица является
квадратной того же порядка, что и исходная матрица.
Необходимым и достаточным условием существования об-
ратной матрицы является невырожденность исходной матрицы. Матрица называется невырожденной ) или неособенной, если ее определитель отличен от нуля (
A ≠ 0 ); в противном случае (при
( A ≠ 0 ) матрица называется вырожденной или особенной. Суще-
ствуют специальные достаточно сложные алгоритмы для ручного вычисления обратных матриц. В качестве примера того, как вы- числяется обратная матрица, рассмотрим квадратную матрицу второго порядка
? a b ?
A = ? ?
? c d ? .
Тогда обратная матрица вычисляется следующим образом:
26
? d /( ad − bc ) b /( bc − ad ) ?
A − 1 = ? ? .
? c /( bc − ad ) a /( ad − bc ) ?
В MS Excel для нахождения обратной матрицы используется
функция МОБР, которая вычисляет обратную матрицу для мат- рицы, хранящейся в таблице в виде массива. Функция имеет вид МОБР (массив).
Здесь массив — это числовой массив с равным количеством
строк и столбцов. Массив может быть задан как диапазон ячеек, например
А1:СЗ; как массив констант, например
{1;2;3:4;5;6:7;8;9} или как имя диапазона или массива.
Рассмотрим пример нахождения обратной матрицы. Пример. Пусть в диапазон ячеек А1:СЗ введена матрица
? 1 2 3 ?
? ?
A = ? 0 2 3 ? .
? ? 1 0 3 ? ?
Необходимо получить обратную матрицу. Решение 1.
Выделите блок ячеек под обратную матрицу, напри-
мер, блок ячеек А5:С7 (указателем мыши при нажатой левой кнопке).
2. Нажмите на панели инструментов Стандартная кнопку
Вставка функции. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МОБР. Щелкните на кнопке ОК.
3. Появившееся диалоговое окно МОБР мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис- ходной матрицы А1.СЗ в рабочее поле Массив (указателем мыши при нажатой левой кнопке).
4. Нажмите сочетание клавиш CTRL+SHIFT+ENTER
(рис. 12).
Рис. 12. Пример заполнения диалогового окна МОБР
27
5. Если обратная матрица не появилась в диапазоне А5:С7,
то следует щелкнуть указателем мыши в строке формул и повто- рить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне А5:С7 появится обратная матрица:
? 1 − 1 0 ?
? ?
? 0 , 5 0 − 0 , 5 ? .
? ? − 0 , 33333 0 , 33333 0 , 33333 ? ?
Сложение и вычитание матриц
Складывать (вычитать) можно матрицы одного размера.
Суммой матриц А = (a ) и B= (b )размера m × n называется мат-
ij ij
рица С = А + В, элементы которой с = a + b , для i = 1,2, ..., m; j
ij ij ij
= 1, 2, ..., n(то есть матрицы складываются поэлементно). На- пример, если:
? 1 2 7 ?
A = ? ? ; (1)
? 9 − 1 13 ?
? 0 − 4 − 3 ?
В = ? ? ; (2)
? 5 19 31 ?
то C= A+ B;
? 1 + 0 2 − 4 7 − 3 ? ? − 1 2 4 ?
C = ? ? 9 + 5 − 1 + 19 13 + 31 ? ? = ? ? 14 18 44 ? ? .
В частном случае А + 0 = А. Аналогично определяется разность двух матриц С = А - В. В MS Excel для выполнения операций суммирования и вы-
читания матриц могут быть использованы формулы, вводимые в соответствующие ячейки.
Пример. Пусть матрица А из рассмотренного примера (2.1),
введена в диапазон А1:С2, а матрица В — в диапазон А4:С5. Не- обходимо найти матрицу С, являющуюся их суммой.
Решение 1.
Табличный курсор установите в левый верхний угол
результирующей матрицы, например в А7.
2. Введите формулу для вычисления первого элемента
результирующей матрицы =А1 + А4 (предварительно установив английскую раскладку клавиатуры).
28
3. Скопируйте введенную формулу в остальные ячейки
результирующей матрицы: установите табличный курсор в ячей- ку А7; наведите указатель мыши на точку в правом нижнем углу ячейки, так чтобы указатель мыши принял вид тонкого крестика; при нажатой левой кнопке мыши протяните указатель до ячейки С7; затем так же протяните указатель мыши до ячейки С8.
В результате в ячейках А7:С8 появится матрица, равная
сумме исходных матриц.
Подобным же образом вычисляется разность матриц (2.1),
только в формуле для вычисления первого элемента вместо знака + ставится знак ?.
? 1 − 2 4 ?
C = A + B = ? ? ;
? 14 18 44 ?
? 1 6 10 ?
C = A − B = ? ? .
? 4 − 20 − 18 ?
Умножение матрицы на число
Произведением матрицы А на число k называется матрица В
= kA, элементы которой b = ka для i - 1, 2, ..., m; j = 1,2,..., п.
ij ij
Иначе говоря, при умножении матрицы на постоянную каждый элемент этой матрицы умножается на эту постоянную: k*A
= (k*a ).
ij ij
Например, для матриц A и B из предыдущего параграфа:
1 ? 0 , 5 1 3 , 5 ?
∗ A = ? ? ;
2 ? 4 , 5 − 0 , 5 6 , 5 ?
? 0 8 6 ?
− 2 ∗ B = ? ? .
? − 10 − 38 − 62 ?
В частности, произведений матрицы А на Число 0 есть ну-
левая матрица, то есть 0хЛ = 0.
В MS Excel для выполнения операции умножения матрицы
на число могут быть использованы формулы, вводимые в соот- ветствующие ячейки.
Пример. Пусть, как и в предыдущем параграфе матрица A
введена в диапазоны А1:С2. Необходимо получить матрицу С = 3 х A.
29
Решение 1.
Табличный курсор поставьте в левый верхний угол ре-
зультирующей матрицы, например в El.
2. Введите формулу для вычисления первого элемента
результирующей матрицы =3*А1 (предварительно установив английскую раскладку клавиатуры).
3. Скопируйте введенную формулу в остальные ячейки
результирующей матрицы: поставьте табличный курсор в ячейку Е1; наведите указатель мыши на точку в правом нижнем углу ячейки, так чтобы указатель мыши принял вид тонкого крестика; при нажатой левой кнопке мыши протяните указатель до ячейки G1; таким же образом протяните указатель мыши до ячейки G2.
В результате в ячейках E1:G2 появится матрица, равная ис-
ходной матрице, умноженной на постоянную —3.
? 3 6 21 ?
3 ∗ A = ? ? .
? 27 − 33 9 ?
Умножение матриц
Произведение матриц определено, если число столбцов пер-
вой матрицы равно числу строк второй.
Пусть A = (a ) m × n , B= (b ) n × p , тогда размерность про-
ij ij
изведения Ах В равна m × p .
При этом матрица С (размера m × p ) называется произведе-
нием матриц A и В, если каждый ее элемент с , равен сумме
ij
произведений элементов i-й строки матрицы А на соответствую- щие элементы j-гo столбца матрицы В:
c = a b + a b + K + a b = ∑ p a b , i = 1 , 2 , K , m ; j = 1 , 2 , K , n .
ij i 1 1 j i 2 2 j ip pj ik kj
k = 1
Таким образом, перемножение матриц осуществляется по сле- дующему правилу:
? 1 стр ∗ 1 стб 1 стр ∗ 2 стб K 1 стр ∗ p стб ?
? ?
C = A ∗ B = ? 2 стр ∗ 1 стб 2 стр ∗ 2 стб K 2 стр ∗ p стб ? .
? K K K K K K K K K K K K K K K ?
? ?
? m стр ∗ 1 стб m стр ∗ 2 стб K m стр ∗ 2 стб ?
30
Пусть, например,
? 1 3 ?
? 1 3 4 2 ? ? ?
C = A ∗ B = ? ? 3 2 0 − 1 ? ? ∗ ? ? 10 2 2 0 ? ? =
? ? 0 1 − 1 2 ? ? ? ?
? 12 − 1 ?
? 1 ∗ 1 + 3 ∗ 2 + 4 ∗ 10 + 2 ∗ 12 1 ∗ 3 + 3 ∗ 2 + 4 * 0 + 2 * ( − 1 ) ? ? 71 7 ?
? ? ? ?
= ? 3 * 1 + 2 * 2 + 0 * 10 − 1 * 12 3 * 3 + 2 * 2 + 0 * 0 − 1 * ( − 1 ) ? = ? − 5 14 ? .
? ? 0 * 1 + 1 * 2 − 1 * 10 + 2 * 12 0 * 3 + 1 * 2 − 1 * 0 + 2 * ( − 1 ) ? ? ? ? 16 − 1 ? ?
Многие свойства, присущие операциям над числами, спра-
ведливы и для операций умножения матриц (что следует из опре- делений этих операций).
Для матриц верны общие свойства операции умножения.
1. А(ВС) = (АВ)С — ассоциативность.
2. А(В + С) = АВ + АС — дистрибутивность.
3. (А + В)С= АВ + ВС.
4. (αА)В = А(αВ) = α(АВ), α ? константа. Однако имеются и специфические свойства операций умно-
жения матриц.
5. Умножение матриц некоммутативно — АВ ≠ ВА. В частном случае коммутативным законом обладает произ-
ведение любой квадратной матрицы А i-го порядка на единичную матрицу Е того же порядка, причем это произведение равно А.
6. Если Е — единичная матрица, то ЕА = А; ВЕ = В. Таким образом, единичная матрица играет при умножении
ту же роль, что и число 1 при умножении чисел.
7. Из того, что Ах В = 0, не следует, что А = 0 или В = 0. В алгебре матриц нет действия деления. Выражение А/В не
имеет смысла. Его заменяют два различных выражения В -1 х А и
А х В -1 , если существует В -1 .
Для квадратных матриц возможна операция возведения в
степень. По определению полагают, что А 0 = Е и А 1 = А. Целой
положительной степенью А m (m > 1) квадратной матрицы А на-
зывается произведение m матриц, равных А, то есть:
A m A * A * ... * A
m раз
31
Для нахождения произведения двух матриц в Excel исполь-
зуется функция МУМНОЖ, которая вычисляет произведение матриц (матрицы хранятся в массивах).
Функция имеет вид МУМНОЖ(массив1,массив2). Здесь
массив1и массив2 — это перемножаемые массивы. При этом ко-
личество столбцов аргумента массив 1 должно быть таким же,
как количество строк аргумента массив2, и оба массива должны
содержать только числа. Результатом является массив с таким же
числом строк, как массив1 и с таким же числом столбцов, как
массив2.
Массив С, который является произведением двух массивов
А и В, определяется следующим образом: C = ( ∑ a b ) , где i—
ij ij
номер строки, aj — номер столбца.
Рассмотрим примеры умножения матриц.
Пример. Пусть матрица А из рассмотренного примера (2.2)
введена в диапазон A1:D3, а матрица В (2.2) — в диапазон А4:В7.
Необходимо найти произведение этих матриц С.
Решение
1. Выделите блок ячеек под результирующую матрицу. Для
этого требуется найти размер матрицы-произведения. Ее размер- ность будет
m × p ,в данном примере 3 х 2. Например, выделите
блок ячеек Fl:G3 (указателем мыши при нажатой левой кнопке).
2. Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
3. В появившемся диалоговом окне Мастер функций в поле
Категория выберите Математические, а в поле Функция — имя
функции МУМНОЖ. После этого щелкните на кнопке ОК.
4. Появившееся диалоговое окно МУМНОЖ мышью ото-
двиньте в сторону от исходных матриц и введите диапазон ис-
ходной матрицы А — A1:D3 в рабочее поле Массив1 (указателем
мыши при нажатой левой кнопке), а диапазон матрицы
В — А4:В7 введите в рабочее поле Массив2 (рис. 13). После
этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.
32
Рис. 13. Пример заполнения рабочих полей диалогового окна МУМНОЖ
5. Если произведение матриц Ах В не появилось в диапазо-
не F1:G3, то следует щелкнуть указателем мыши в строке формул и еще раз нажать комбинацию клавиш CTRL+SHIFT+ENTER.
В результате в диапазоне F1:G3 появится произведение мат-
риц:
? 7 17 ?
? ?
C = A * B = ? − 5 14 ? .
? ? 16 0 ? ?
Пример. Предприятие выпускает продукцию трех видов:
Р1, Р2, РЗ и использует сырье двух типов S1 и S2. Нормы расхо- да сырья характеризуются матрицей
? 3 2 ?
? ?
A = ? 1 4 ? .
? ? 5 2 ? ?
где каждый элемент показывает, сколько единиц сырья j-гo
типа расходуется на производство единицы продукции. План вы- пуска продукции задан матрицей - строкой В = (100, 130,90). Не- обходимо определить затраты сырья для планового выпуска про- дукции.
Решение. Для нахождения затрат сырья необходимо вычис-
лить произведение матриц В х А.
1. Введите матрицу А в диапазон А1:ВЗ, а матрицу В — в
диапазон А4:С4.
2. Выделите блок ячеек под результирующую матрицу. Ее
размерность будет m × p , в данном примере 1x2. Например, вы-
делите блок ячеек F1:G1 (указателем мыши при нажатой левой кнопке).
33
3. Нажмите на панели инструментов Стандартную кнопку
Вставка функции.
4. В появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Математические, а в рабочем по- ле Функция — имя функции МУМНОЖ. После этого щелкните на кнопке ОК.
5. Появившееся диалоговое окно МУМНОЖ мышью ото-
двиньте в сторону от исходных матриц и введите диапазон ис- ходной матрицы А — А 1:ВЗ в рабочее поле Массив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы
В — А4:С4 введите в рабочее поле Массив2. После этого
нажмите сочетание клавиш CTRL+SHIFT+ENTER.
6. Если произведение матриц В х А не появилось в диапа-
зоне Fl :G 1, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне F1:G1 появятся затраты сырья для
планового выпуска продукции (то есть произведение матриц) Вх А = (880,900). Таким образом, для выполнения плана необходимо S1 - 880 единиц сырья первого типа и S2 = 900 единиц сырья вто- рого типа.
УПРАЖНЕНИЯ
1. Найдите произведение матриц А х В, где
? 4 ?
A = [ 1 2 3 ] , B = ? ? 5 ? ? .
? ? 6 ? ?
2. Найдите произведение матриц Вх А из упражнения 1. 3. Найдите определитель матрицы В х А из упражнения 2. 4. Найдите произведение матриц С = Ах Е, где
? 2 3 5 ?
? ?
A = ? 4 1 3 ? ;
? ? 7 2 0 ? ?
? 1 0 0 ?
? ?
E = ? 0 1 0 ? .
? ? 0 0 1 ? ?
34
5. Найдите матрицу, обратную данной:
? 1 − 2 3 ?
? ?
A = ? 2 3 − 4 ? .
? ? 3 − 2 − 5 ? ?
6. Воспользуйтесь определением обратной матрицы (А х А -1 =
А -1 х А = Е) и проверьте, верно ли найдена обратная матрица в
упражнении 5: для этого найдите произведение матрицы А в уп- ражнении 5 на обратную матрицу. Таким же способом проверьте, что А
-1 х А = Е.
7. Предприятие выпускает продукцию трех видов: Р1, Р2, РЗ и использует сырье двух типов S1 и S2. Нормы расхода сырья ха- рактеризуются матрицей
? 3 2 ?
? ?
A = ? 1 4 ? .
? ? 5 2 ? ?
где каждый элемент показывает, сколько единиц сырья j-гo
типа расходуется на производство единицы продукции. Стои- мость единицы каждого типа сырья задана матрицей – столбцом
? 50 ?
C = ? ? .
? 40 ?
Определите стоимость затрат сырья на единицу продукции.
8. Какие из матриц можно перемножить? Найдите эти произве- дения.
? 1 3 2 0 ?
A = ? ? ;
? 2 1 − 1 2 ?
? 2 0 ?
B = ? ? ;
? 1 3 ?
? 1 ?
? ?
C = ? 1 ? ;
? 0 ?
? ?
? 2 ?
[ ]
D = 2 1 1 0 .
35
9. Вычислите: (А х В) х С; А х (В х С).
? 1 0 ?
? 1 2 0 ? ? ? ? 1 0 2 1 ?
A = ? ? 3 1 2 ? ? ; B = ? 1 1 ? ; C = ? ? 3 1 − 1 0 ? ? .
? ? 2 − 1 ? ?
10. Покажите вычислением, что для указанных матриц верно ут- верждение: (A + B)C = AC + BC.
? 0 3 ? ? 1 0 ?
? ? ? ? ? 1 0 2 1 ?
A = ? 1 1 ? ; B = ? 1 1 ? ; C = ? ? 3 1 − 1 0 ? ? .
? ? − 1 2 ? ? ? ? 2 − 1 ? ?
11. Вычислите:
? 1 2 ?
? ? ? − 0 2 4 ?
1) A * B = ? 3 4 ? * ? ? 1 3 2 ? ? ;
? ? 5 6 ? ?
? 1 2 ?
? − 0 2 4 ? ? ?
2) B * A = ? ? 1 3 2 ? ? * ? 3 4 ? .
? ? 5 6 ? ?
Какое свойство умножения матриц демонстрирует данный
пример? 12. Найдите матрицы, обратные данным:
? 2 − 4 3 ?
? ?
1) A = ? 1 − 2 4 ? ;
? ? 3 − 1 5 ? ?
? 2 − 1 1 ?
? ?
2) B = ? 3 2 2 ? ;
? ? 1 − 2 1 ? ?
? 2 3 1 ?
? ?
3) C = ? 4 − 1 5 ? .
? ? 1 − 2 4 ? ?
36
Решение систем линейных уравнений
Многие прикладные задачи в технике, экономике и других
областях сводятся к решению системы линейных уравнений, по- этому особенно важно уметь их решать.
Система n линейных уравнений с n неизвестными.
Две системы уравнений являются равносильными или экви-
валентными, если они имеют одно и то же множество решений. Система, равносильная данной может быть получена с помощью элементарных преобразований системы.
Систему можно также записать в виде матричного уравне-
ния;
Ах Х = В, где А — матрица коэффициентов при переменных, или мат-
рица системы:
? a a K a ?
? 11 12 1 n ?
A = ? a 21 a 22 K a 2 n ? ;
? K K K K ?
? ?
? a a K a ?
m 1 m 2 nn
X — матрица-столбец (вектор) неизвестных:
? x ?
? 1 ?
X = ? x 2 ? ;
? K ?
? ?
? x ?
n
В — матрица-столбец (вектор) свободных членов:
? b ?
? 1 ?
B = ? b 2 ? .
? K ?
? ?
? b ?
n
В развернутом виде систему можно представить следую-
щим образом:
37
? a a K a ? ? x ? ? b ?
? 11 12 1 n ? ? 1 ? ? 1 ?
? a 21 a 22 K a 2 n ? * ? x 2 ? = ? b 2 ? .
? K K K K ? ? K ? ? K ?
? ? ? ? ? ?
? a a K a ? ? x ? ? b ?
m 1 m 2 nn n n
Существует ряд методов решения системы, ориентирован-
ных на вычисления вручную: методы Крамера, Гаусса и т. д. Предполагая использование компьютера для проведения вычис- лений, наиболее целесообразно рассмотреть решение - системы в общем виде (метод обратной матрицы). Будем считать, что квад- ратная матрица системы А
является невырожденной, то есть ее
nn
определитель A ≠ 0 . В этом случае существует обратная матрица
A -1 .
Умножая слева обе части матричного равенства на обрат-
ную матрицу A -1 получим:
A − 1 × A × X = A − 1 × B , E × X = A − 1 × B ;
E × X = X ,
отсюда решением системы методом обратной матрицы бу-
дет матрица – столбец
X = A − 1 × B .
Таким образом, для решения системы (нахождения вектора
X) необходимо найти обратную матрицу коэффициентов и умно- жить ее справа на вектор свободных членов.
Пример. Пусть необходимо решить систему
? 3 x + 2 y = 7
?
? 4 x − 5 y = 40 .
Решение 1. Введите матрицу А (в данном случае размера 2 х 2) в
диапазон А1:В2
? 3 2 ?
A = ? ? .
? 4 − 5 ?
Вектор В = (7 40) введите в диапазон С1:С2. 2. Найдите обратную матрицу А
-1 . Для этого:
• выделите блок ячеек под обратную матрицу. Напри-
мер, выделите блок АЗ:В4 (указателем мыши при нажатой левой кнопке);
38
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Математические, а в рабочем по- ле Функция — имя функции МОБР. После этого щелкните на кнопке 0К;
• появившееся диалоговое окно МОБР мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис- ходной матрицы А1:В2 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). Нажмите сочетание клавиш CTRL+SHIFT+ENTER;
• если обратная матрица не появилась в диапазоне
АЗ:В4, то следует щелкнуть указателем мыши в Строке формул и повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне АЗ:В4 появится обратная матрица:
? 0 , 217391 0 , 086957 ?
? ? .
? 0 , 173913 − 0 , 13043 ?
3. Умножением обратной матрицы А -1 на вектор В най-
дите вектор X. Для этого:
• выделите блок ячеек под результирующую матрицу
(под вектор X). Ее размерность будет m × p , в данном примере
2x1. Например, выделите блок ячеек СЗ:С4 (указателем мыши при нажатой левой кнопке);
• нажать на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Математические, а в рабочем по- ле Функция имя функции — МУМН0Ж. Щелкните на кнопке 0К; •
появившееся диалоговое окно МУМН0Ж мышью отодвинь-
те в сторону от исходных матриц и введите диапазон обратной матрицы А
-1 — АЗ:В4 в рабочее поле Массив1 (указателем мыши
при нажатой левой кнопке), а диапазон матрицы В — С1:С2— в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;
• если вектор X не появился в диапазоне СЗ:С4, то сле-
дует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.
39
В результате в диапазоне СЗ:С4 появится вектор X. Причем
х = 5 будет находиться в ячейке СЗ, а у = - 4 — в ячейке С4.
Можно осуществить проверку найденного решения. Для
этого найденный вектор X необходимо подставить в исходное
матричное уравнение А х Х= В.
Проверка производится следующим образом.
1. Выделите блок ячеек под результирующую матрицу (под
вектор В). Ее размерность будет m × p ,в данном примере 2x1.
Например, выделите блок ячеек D1:D2 (указателем мыши при
нажатой левой кнопке).
2. Нажмите на панели инструментов Стандартная кнопку
Вставка функции.
3. В появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Математические, а в рабочем по-
ле Функция — имя функции МУМНОЖ. Щелкните на кнопке
ОК.
4. Появившееся диалоговое окно МУМН0Ж мышью ото-
двиньте в сторону от исходных матриц и введите диапазон ис-
ходной матрицы А — А1:В2 в рабочее поле Массив1 (указателем
мыши при нажатой левой кнопке), а диапазон матрицы X —
СЗ:С4 — в рабочее поле Массив2. После этого нажмите сочета-
ние клавиш CTRL+SHIFT+ENTER.
5. Если вектор В не появился в диапазоне D1:D2, то следует
щелкнуть указателем мыши в строке формул и повторить нажа-
тие CTRL+SHIFT+ENTER.
В результате в диапазоне Dl:D2 появится вектор В, и, если
система решена правильно, появившийся вектор будет равен ис-
ходному В = (7 40).
Пример. Ресторан специализируется на выпуске трех видов
фирменных блюд: В1, В2, ВЗ, при этом используются ингредиен-
ты трех типов S1, S2, S3. Нормы расхода каждого из них на одно
блюдо и объем расхода ингредиентов на 1 день заданы таблицей:
Нормы расхода ингредиентов на блюдо Расход ингре-
Ингредиент (у. е.) диентов на 1
B1 B2 B3 день (у. е.)
S1 5 3 4 2700
S2 2 1 1 800
S3 3 2 2 1600
Нужно найти ежедневный объем выпуска фирменных блюд
каждого вида.
40
Решение. Пусть ежедневно ресторан выпускает х блюд ви-
1
да В1, х блюд вида В2 и х — блюд вида ВЗ. Тогда в соответст-
2 3
вии с расходом ингредиентов каждого типа имеем систему:
? 5 x + 3 x + 4 x = 2700 ,
? 1 2 3
? 2 x + x + x = 900 ,
1 2 3
?
? 3 x + 2 x + 2 x = 1600 .
1 2 3
Решаем систему:
1. Введите матрицу А (в данном случае размера 3 х 3) в
диапазон А1:СЗ
? 5 3 4 ?
? ?
A = ? 2 1 1 ?
? ? 3 2 2 ? ?
Вектор В = (2700 900 1600) введите в диапазон D1:D3.
2. Найдите обратную матрицу А -1 . Для этого:
• выделите блок ячеек под обратную матрицу. Напри-
мер, выделите блок А4:С6 (указателем мыши при нажатой левой
кнопке);
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите пункт Математические, а в рабо-
чем поле Функция — имя функции МОБР. Щелкните на кнопке
0К;
• появившееся диалоговое окно МОБР мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис-
ходной матрицы А1:СЗ в рабочее поле Массив (указателем мыши
при нажатой левой кнопке). Нажмите сочетание клавиш
CTRL+SHIFT+ENTER;
• если обратная матрица не появилась в диапазоне
А4:Сб, то следует щелкнуть указателем мыши в строке формул и
повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне А4:С6 появится обратная матрица.
3. Умножением обратной матрицы А -1 на вектор В найдите век-
тор X. Для этого:
• выделите блок ячеек под результирующую матрицу
(под вектор X). Ее размерность будет m × p , в данном примере
3x1. Например, выделите блок ячеек D4:D6 (указателем мыши
при нажатой левой кнопке);
41
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите пункт Математические, а в рабо-
чем поле Функция — имя функции МУМНОЖ. Щелкните на
кнопке 0К;
• появившееся диалоговое окно МУМНОЖ мышью ото-
двиньте в сторону от исходных матриц и введите диапазон об-
ратной матрицы А'1 — АФ.С6 в рабочее поле Массив1 (указате-
лем мыши при нажатой левой кнопке), а диапазон матрицы В —
D1.D3 введите в рабочее поле Массив2. После этого нажмите со-
четание клавиш CTRL+SHIFT+ENTER;
• если вектор X не появился в диапазоне D4:D6, следует
щелкнуть указателем мыши в строке формул и повторить нажа-
тие CTRL+SHIFT+ENTER.
В результате в диапазоне D4:D6 появится вектор X (рис. 14).
Рис. 14. Результаты вычислений из предыдущего примера
Причем х = 200 будет находиться в ячейке D4, х = 300 — в
1 2
ячейке D5, а х = 200 в ячейке D6.
3
Рекомендуется сделать проверку, подставив найденные зна-
чения в уравнение системы. Для этого найденный вектор X необ-
ходимо подставить в исходное матричное уравнение Ах X = В.
При проверке используется тот же способ, что и для решения
предыдущего примера.
УПРАЖНЕНИЯ
Решите самостоятельно следующие примеры.
? x − x + x = 3
? 1 2 3
? 2 x + x + x = 11
1. 1 2 3
?
? x + x + 2 x = 8
1 2 3
42
? x + 2 x + 3 x − 2 x = 6
? ? 1 2 3 4
? 2 x + 4 x − 2 x − 3 x = 18
1 2 3 4
2. ? ? 3 x + 2 x − x + 2 x = 4
1 2 3 4
? 2 x − 3 x + 2 x + x = − 8
1 2 3 4
? 2 x − 3 y + z − 2 = 0
?
3. ? x + 5 y − 4 z + 5 = 0
?
? 4 x + y − 3 z + 4 = 0
? 3 x + 2 y + z = 5 ? x − 2 y + 3 z = 6
?
? 2 x + 3 y + z = 1 ? ? 2 x + 3 y − 4 z = 20
4. ? ? 2 x + y + 34 z = 11 ? ? 3 x − 2 y − 5 z = 6
? 4 x − 3 y + 2 z = 9 ? x + y + 2 z = − 1
? ?
5. ? 2 x + 5 y − 3 z = 4 ? 2 x − y + 2 z = − 4
? ? 5 x + 6 y − 2 z = 18 ? ? 4 x + y + 4 z = − 2
? 2 x − y − z = 4 ? 3 x + 4 y + 2 z = 8
? ?
6. ? 3 x + 4 y − 2 z = 11 ? 2 x − y − 3 z = − 4
? ? 3 x − 2 y + 4 z = 11 ? ? x + 5 y + z = 0
? x + y − z = 1 ? x − 4 x − 2 x = − 3
? ? 1 2 3
7. ? ? 8 x + 3 y − 6 z = 2 ? ? 3 x 1 + x 2 + x 3 = 5
? 4 x + y − 3 z = 3 ? 3 x − 5 x − 6 x = − 9
1 2 3
? 4 x + 7 x − 3 x = − 10 ? − 3 x + 6 x + 8 x = − 7
? 1 2 3 ? 1 2 3
8. ? 2 x 1 + 9 x 2 − x 3 = 8 ? 9 x 1 − 11 x 2 − 14 x 3 = − 15
? ?
? x − 6 x + 3 x = − 3 ? 18 x − 22 x − 30 x = − 20
1 2 3 1 2 3
Система m линейных уравнений с n неизвестными
Система m линейных уравнений с n неизвестными имеет
вид:
a x + a x + K + a x = b
11 1 12 2 1 n n 1
a x + a x + K + a x = b
21 1 22 2 2 n n 2
a x + a x + K + a x = b
m 1 1 m 2 2 mn n m
Она может быть представлена в матричном виде: Ax X = B.
43
Однако при применении компьютера удобнее использовать
более общий подход — метод наименьших квадратов. Для этого
обе части матричного уравнения системы умножаем слева на
транспонированную матрицу системы А Т .
A T AX = A T B.
Затем обе части уравнения умножаем слева на матрицу
(А Т А -1 ) Если эта матрица существует, то система определена. С
учетом того, что (А Т А -1 ) * (А Т А) = Е, получаем
X = (A T A ) -1 A T B.
Матричное уравнение является решением системы m ли-
нейных уравнений с n неизвестными при m > n.
Пример.Пусть необходимо решить систему
? 3 x + 2 y = 7
?
? 4 x − 5 y = 40
? ? 3 x + 3 y = 3 .
Решение
1. Введите матрицу А (в данном случае размера 3 х 2) в
диапазон А1:ВЗ
? 3 2 ?
? ?
A = ? 4 − 5 ? ,
? ? 3 3 ? ?
Вектор В = (7 40 3) введите в диапазон С1:СЗ.
2. Найдите транспонированную матрицу А Т . Для этого:
• выделите блок ячеек под транспонированную матрицу.
Его размер в примере будет 2x3. Например, выделите блок А4:С5
(указателем мыши при нажатой левой кнопке);
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Ссылки и массивы, а в рабочем
поле Функция — имя функции ТРАНСП. Щелкните на кнопке О
К;
• появившееся диалоговое окно ТРАНСП мышью ото-
двиньте в сторону от исходной матрицы и введите диапазон ис-
ходной матрицы А1:ВЗ в рабочее поле Массив (указателем мыши
при нажатой левой кнопке). После этого нажмите сочетание кла-
виш CTRL+SHIFT+ENTER;
44
• если обратная матрица не появилась в диапазоне
АЗ:В4, то следует щелкнуть указателем мыши в Строке формул и
повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне А4:С5 появится транспонирован-
ная матрица А Т .
3. Найдите произведение A T х В. Для этого:
• выделите блок ячеек под результирующую матрицу
(под вектор A T В). Ее размерность будет n х 1, в данном примере
2x1. Например, выделите блок ячеек Е4:Е5 (указателем мыши
при нажатой левой кнопке);
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите Математические, а в рабочем по-
ле Функция — имя функции МУМНОЖ. Щелкните на кнопке 0К;
• появившееся диалоговое окно МУМНОЖ мышью ото-
двиньте в сторону от исходных матриц и введите диапазон
транспонированной матрицы A T — А4:С5 в рабочее поле Мас-
сив1 (указателем мыши при нажатой левой кнопке), а диапазон
матрицы В — С1:СЗ — в рабочее поле Массив2. После этого на-
жмите сочетание клавиш CTRL+SHIFT+ENTER;
• если вектор АТВ не появился в диапазоне Е4:Е5, то
следует щелкнуть указателем мыши в строке формул и повторить
нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне Е4:Е5 появится вектор
? 190 ?
A T B = ? ? .
? − 177 ?
Аналогично находим произведение A T х А:
• выделите блок ячеек под результирующую матрицу A T
А. Ее размерность будет n × n , в данном примере 2x2. Например,
выделите блок ячеек А7:В8 (указателем мыши при нажатой левой
кнопке);
• далее действуйте так, как описано в пункте 3, указывая
соответствующие диапазоны.
В результате в диапазоне А7:В8 появится матрица A T А:
? 34 − 5 ?
? ? .
? − 5 38 ?
4. Находим обратную матрицу (A T А) -1 . Для этого необхо-
димо:
45
• выделить блок ячеек под обратную матрицу. Напри-
мер, блок А1О:В11 (указателем мыши при нажатой левой кноп-
ке);
• нажать на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выбрать пункт Математические, а в рабо-
чем поле Функция — имя функции МОБР. Затем щелкнуть на
кнопке ОК;
• появившееся диалоговое окно МОБР мышью отодви-
нуть в сторону от исходной матрицы и ввести диапазон исходной
матрицы A T А — А7:В8 в рабочее поле Массив (указателем мы-
ши при нажатой левой кнопке). После этого нажать сочетание
клавиш CTRL+SHIFT+ENTER;
• если обратная матрица не появилась в диапазоне
А1О:В11, то следует щелкнуть указателем мыши в строке формул
и повторить нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне А10:В11 появится обратная матри-
ца (A T А) -1
? 0 , 029992 0 , 003946 ?
? ? .
? 0 , 003946 0 , 026835 ?
5. Теперь умножением обратной матрицы (A T А) -1 на вектор
A T В находим вектор X.
Для этого:
• выделите блок ячеек под результирующую матрицу
(под вектор X). Ее размерность будет n х 1, в данном примере
2x1. Например, выделите блок ячеек Dl:D2 (указателем мыши
при нажатой левой кнопке);
• нажмите на панели инструментов Стандартная кнопку
Вставка функции;
• в появившемся диалоговом окне Мастер функций в ра-
бочем поле Категория выберите пункт Математические, а в рабо-
чем поле Функция — имя функции МУМНОЖ. Щелкните на
кнопке 0К;
• появившееся диалоговое окно МУМ НОЖ мышью
отодвиньте в сторону от исходных матриц и введите диапазон
обратной матрицы (АТА)~1 — А10.В11 в рабочее поле Массив1
(указателем мыши при нажатой левой кнопке), а диапазон матри-
цы АТВ — Е4.Е5 — в рабочее поле Массив2. После этого на-
жмите сочетание клавиш CTRL+SHIFT+ENTER;
46
• если вектор X не появился в диапазоне D1:D2, то сле-
дует щелкнуть указателем мыши в Строке формул и повторить
нажатие CTRL+SHIFT+ENTER.
В результате в диапазоне D1:D2 появится вектор X .Причем
х = 5 будет находиться в ячейке D1, а у = - 4 — в ячейке D2.
Рис. 15. Результаты вычислений из предыдущего примера
Можно осуществить проверку найденного решения. Для
этого найденный вектор X необходимо подставить в исходное
матричное уравнение
Ax X = B.
Причем достаточно подставить X в любые n уравнений.
УПРАЖНЕНИЯ
1. Решите систему:
? 8 x + 5 y = 10
?
? 5 x + 2 y = 4
? ? 7 x + 4 y = 8
2. Решите систему:
? 2 x − 4 y + 3 = 1
? ?
? 3 x − y + 5 z = 2
? ? x − 2 y + 4 z = 3
? 6 x − 2 y = 10 z = 4
3. Решите систему:
? 2 x − y − z = 1
? ?
? x + 2 y − 3 x = 5
? ? 4 x − 2 y − 2 z = 2
? x + 3 y + 4 z = 6
47
3. ЗАДАЧИ ОПТИМИЗАЦИИ
Задачи оптимизации называют экстремальными задачами.
Их решение сопряжено с большим количеством вычислений. Рассмотрим некоторые типы задач оптимизации: решение урав- нений с одним неизвестным, задачи линейного программирова- ния и аппроксимацию функций.
Постановка задачи оптимизации
В задачах оптимизации требуется найти значения парамет-
ров или функций, реализующих максимум или минимум некото- рой зависящей от них величины, например:
z=f(x ,x ,...,x ), (3.1)
l 2 n
часто при дополнительных условиях-неравенствах:
? ( x , x ,..., x ) ( ≤ 0 i = 1 , 2 ,..., m ) (3.2)
i 1 2 n
В инженерных и экономических задачах желательно найти
максимум меры выполнения или минимум стоимости.
Другим приложением задач оптимизации является получе-
ние приближенных решений выбором неизвестных значений па- раметров или функций так, чтобы они давали минимум ошибки.
В простейшем случае одной независимой переменной хло-
кальные максимум и минимум функции определяются следую- щим образом. Действительная функция f(x),определенная при х= а,имеет в точке а(локальный) минимум или (локальный) максимум f (а), если существует такое положительное число
δ
,
что при всех ? x = x − a ,для которых выполняются неравенства
0 ⟨ ? x ⟨ δ и существует значение f ( a + ? x ) , соответственно
? f ≡ f ( a + ? x ) ( ) − f a ⟨ 0 или
? f ≡ f ( a + ? x ) ( ) − f a ⟩ 0
Максимум и минимум функции - это экстремум функции.
Определение локальный подчеркивает тот факт, что понятие экстремума связано лишь с достаточно малой окрестностью точки а. При решении оптимизационных задач важно нахожде- ние не локальных экстремумов, а глобального максимума или глобального минимума (наибольшего или наименьшего значений) функции на промежутке X.
48
Для поиска экстремумов существуют различные методы.
Часто случается, что при поиске максимумов и минимумов функций многих переменных получают сложную систему урав- нений, в этих случаях экстремумы находятся численными мето- дами, то есть при помощи последовательного применения мето- да проб. При этом применение компьютера является практиче- ски единственным способом решения задачи.
Решение уравнений с одним неизвестным
Одним из приложений задач оптимизации является числен-
ное решение систем уравнений с одним или несколькими неиз- вестными вида:
f (x)= 0. ( 3.3 )
Нахождение корней уравнения вида (3.3) даже в случае ал-
гебраических уравнений выше третьей степени представляет дос- таточно сложную задачу. Трансцендентные же уравнения чаще всего вообще не имеют аналитического решения. В этих случаях единственным путем является получение приближенных реше- ний, выбором неизвестных значений параметров так, чтобы они давали минимум ошибки некоторой целевой функции (как пра- вило, квадратичной). Обычно используются итерационные мето- ды, когда вначале выбирают некоторое начальное приближение х
0 затем вычисляют последовательные приближения
x [ ] j + 1 = ? ( ) x [ ] j ( j = 0 , 1 , 2 ,... )
Итерационные методы обеспечивают сходимость таких
приближений к искомому значению х.
В MS Excel для решения уравнений вида (3.3) используется
удобный и простой для понимания инструмент Подбор парамет- ра. Он реализует алгоритм численного решения уравнения, зави- сящего от одной переменной.
Процесс решения с помощью процедуры Подбор параметра
распадается на два этапа:
1 . Задание на рабочем листе ячейки, содержащей перемен-
ную решаемого уравнения (так называемой влияющей ячейки), и ячейки содержащей формулу уравнения (зависящей или целевой ячейки).
49
С помощью процедуры Подбор параметра могут быть ре-
шены только некоторые типы уравнений.
Пример.Найти решение уравнения ln x = 0
Решение
Первый этап
1. Открываем новый рабочий лист (команда Вставка >
Лист).
2. Заносим в ячейку А1 ориентировочное значение корня,
например, 3.
3. Заносим в ячейку В1 левую часть уравнения, используя в
качестве независимой переменной ссылку на ячейку А1. Для это- го нажимаем на панели инструментов Стандартная кнопку Вставка функции; в появившемся диалоговом окне Мастер функций в рабочем поле Категория выбираем Математические, а в рабочем поле Функция имя функции LN. После чего щелкаем на кнопке ОК; появившееся диалоговое окно LN мышью отодви- гаем в сторону от ячейки А1 и в рабочее поле Число щелчком мыши на ячейке А1 вводим ее адрес. После чего нажимаем на кнопку ОК.
В ячейке В1 появляется число 1,098612. Второй этап
1. Вызываем процедуру Подбор параметра (команда Сер-
вис > Подбор параметра).
2. В поле Установить в ячейке мышью указываем В1, в
поле Значение с клавиатуры задаем 0(правая часть уравнения), в поле Изменяязначение ячейки мышью указываем Л У (рис. 16).
Рис. 16. Пример заполнения диалогового окна Подбор параметра
50
3. Щелкаем на кнопке ОК и получаем результат подбора,
отображаемый в диалоговом окне Результат подбора параметра. Щелкаем на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. В ячейке А1 получаем при- ближенное значение х= 0,999872(рис. 17). При этом погреш- ность решения (значение правой части уравнения) — вместо 0в ячейке В1 получаем -0,00013.
Рис. 17. Результаты вычислений
Таким образом, при значении х =0,999872 правая часть
уравнения ln x = 0 приближается к нулю (-0,00013). Принимая во
внимание, что полученный корень это приближенное решение, его можно округлить до 1, то есть х=1,что и является известным аналитическим решением этого уравнения.
Пример.Найти решение уравнения х 2 -З х: +2 = 0.
Решение.Уравнение имеет 2 корня. Решение начинаем с
нахождения первого корня.
1. Открыть новый рабочий лист (команда Вставка >Лист).
Заносим в ячейку А1 ориентировочное значение первого
корня, например, 3.
2.Заносим в ячейку В1 левую часть уравнения, используя в
качестве независимой переменной ссылку на ячейку А1. Соответ- ствующая формула будет иметь вид =А1
л 2-3*А1 +2.
3. Вызываем процедуру Подбор параметра (команда Сер-
вис > Подбор параметра).
4. В поле Установить в ячейке указываем В1,в поле Значе-
ние задаем 0 (правая часть уравнения), в поле Изменяя значение ячейки указываем А1.
5. Щелкаем на кнопке ОК и получаем результат подбора,
отображаемый в диалоговом окне Результат подбора параметра. Щелкаем на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. Таким образом, в ячейке А1 получаем приближенное значение x
= 2,000048.При этом точ-
i
ность решения (значение правой части уравнения) — вместо 0в ячейке В1 получаем 4,85Е-05 (0,0000485).
51
6. Повторяем расчет для второго корня х ,задавая в ячейке
2
А1 другое начальное значение, например —3.Получаем значение второго корня уравнения х
= 0,9996.
2
УПРАЖНЕНИЯ
1. Решить уравнение cosx= 0 в диапазоне хє [0; 2].
2. Решить уравнение 2 x 2 − 3 x 2 + 1 = 0
3. Решить уравнение х 3 - Зх 2 + х =0.
Линейное программирование
В случае, когда оптимизируемая целевая функция (3.1) и ог-
раничения (3.2) линейны, задача оптимизации решается метода- ми линейного программирования и обычно называется задачей линейного
программирования. Задача линейного про-
граммирования заключается в нахождении г переменных x , x ...,
l 2
х ,минимизирующих данную линейную функцию (целевую
1
функцию):
( )
Z = f x , x ,..., x ≡ c x + c x + ... + c x
(3.4)
1 2 r 1 1 2 2 r r
(или максимизирующую — Z) при линейных ограничени-
ях-равенствах:
a i 1 x 1 + a i 2 + x 2 + ... + a ir x r = A i , где i = 1 , 2 ,..., n (3.5)
и линейных ограничениях-неравенствах:
A x + A x + ... + A x ≥ B , где j=1,2,…,m (3.6)
j 1 1 j 2 2 jr r j
Задачу линейного программирования (3.4-3.6) сводят путем
введения вспомогательных переменных к стандартной форме (основной задаче линейного программирования). При этом тре- буется минимизировать целевую функцию:
Z = f ( x , x ,..., x ) ≡ c x + c x + ... + c x (3.7)
1 2 n 1 1 2 2 n n
при т < плинейных ограничениях-равенствах
a i 1 x 1 + a i 2 x 2 + ... + a in x n = b i , где i = 1 , 2 ,..., m (3.8)
и n линейных ограничениях-неравенствах
x ≥ 0 , где k=1,2, ...,п. (3.9)
k
52
Допустимым решением (планом) задачи линейного про-
граммирования является упорядоченное множество чисел (
x , x , ..., x ),удовлетворяющих ограничениям (3.8) и (3.9). Это
1 2 n
точка в n-мерном пространстве. Допустимое решение, минимизи- рующее целевую функцию (3.7), называется оптимальным реше- нием (оптимальным планом).
Чаще всего оптимальное решение, если оно существует, яв-
ляется и единственным. Однако возможны случаи, когда опти- мальных решений бесчисленное множество.
Процесс решения задачи линейного программирования
обычно состоит из ряда этапов:
1-й этап: осмысление задачи, выделение наиболее важных
качеств, свойств, величин, параметров. Это можно делать, со- ставляя схемы, таблицы, графики и т.п.;
2-й этап: введение обозначений (неизвестных). Желательно
ограничиваться как можно меньшим количеством неизвестных, выражая по возможности одни величины через другие;
3-й этап: создание целевой функции. Обычно в качестве
цели могут выступать максимальная стоимость всего объема продукции, максимальная прибыль, минимальные затраты и т. п. Целевая функция записывается в виде (3.4) или (3.7).
4-й этап: составление системы ограничений, которым
должны удовлетворять введенные величины (3.5), (3.6) или (3.8), (3.9).
5-й этап: решение задачи на компьютере.
Инструментом для поиска решений задач оптимизации в
Excel служит процедура Поиск решения (Сервис >Поиск реше- ния). При этом открывается диалоговое окно Поиск решения. Оно содержит следующие рабочие поля:
Установить целевую ячейку — служит для указания целе-
вой ячейки, значение которой необходимо максимизировать, ми- нимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу:
Равной — служит для выбора варианта оптимизации значе-
ния целевой ячейки (максимизация, минимизация или подбор за- данного числа). Чтобы установить число, необходимо ввести его в поле;
53
Изменяя ячейки — служит для указания ячеек, значения ко-
торых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимиза- ции значения ячейки, указанной в поле Установить целевую ячейку;
Предположить — используется для автоматического поис-
ка ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки;
Ограничения — служит для отображения списка граничных
условий поставленной задачи;
Добавить — используется для отображения диалогового
окна Добавить ограничение;
Изменить — применяется для отображения диалогового
окна Изменить ограничение;
Удалить — служит для снятия указанного ограничения;
Выполнить — используется для запуска поиска решения
поставленной задачи;
Закрыть— служит для выхода из окна диалога без запуска
поиска решения поставленной задачи. При этом сохраняются ус- тановки, сделанные в окнах диа-. лога, появлявшихся после на- жатий на кнопки Параметры, Добавить, Изменить или Удалить;
Параметры — применяется для отображения диалогового
окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения;
Восстановить — служит для очистки полей окна диалога и
восстановления значений параметров поиска решения, исполь- зуемых по умолчанию.
Рассмотрим примеры решения задач оптимизации.
Пример.В ресторане готовятся фирменные блюда трех видов (блюдо А, блюдо В и блюдо С) с использованием при приготов- лении ингредиентов трех видов (ингредиент 1, ингредиент 2 и ингредиент 3). Расход ингредиентов в граммах на блюдо задается следующей таблицей:
54
Вид ингредиентаБлюдо А Блюдо В Блюдо С
Ингредиент 1 20 50 10
Ингредиент 2 20 0 40
Ингредиент 3 20 10 10
Стоимость приготовления блюд одинакова (100 руб.).
Ежедневно в ресторан поступает 5 кг ингредиента 1 и по 4
кг ингредиентов видов 2 и 3. Каково оптимальное соотношение дневного производства блюд различного вида, если производст- венные мощности ресторана позволяют использовать весь запас поступивших продуктов?
Решение.Для решения задачи введем обозначения: пусть x
l
—дневной выпуск блюда А; х —дневной выпуск блюда В; х —
2 3
дневной выпуск блюда С.
Составим целевую функцию — она заключается в стоимо-
сти выпущенных рестораном блюд:
Z= 100 × x +100×х +100 ×х .
1 2 3
Определим имеющиеся ограничения (руководствуясь таб-
лицей):
1. 20×x + 50×х +10×х ≤ 5000;
1 2 3
2. 20×x + 0×х +40×х ≤ 4000;
1 2 3
3. 20×x + 10×х +10×х ≤ 4000.
1 2 3
Кроме того, поскольку нельзя реализовать часть блюда и
количество блюд не может быть отрицательным, добавим еще ряд ограничений:
1. x ≥ 0;
1
2. х ≥ 0;
2
3. x ≥ 0;
3
4. x — целое;
1
5. х —целое;
2
6. х —целое.
3
Теперь можно приступить к решению задачи на компьюте-
ре.
1. Откроем новый рабочий лист (Вставка > Лист).
2. В ячейки А2, A3 и А4 занесем дневной запас продуктов
— числа 5000,4000и 4000соответственно.
55
3. В ячейки С1, D1и Е1 занесем начальные значения неиз-
вестных х х и х (нули) — в дальнейшем значения этих ячеек
1 , 2 3
будут подобраны автоматически.
4. В ячейках диапазона С2:Е4 разместим таблицу расхода
ингредиентов.
5. В ячейках В2:В4 укажем формулы для расчета расхода
ингредиентов по видам. В ячейке В2 формула будет иметь вид =$С$1*С2+ $D$1*D2+ $Е$1*Е2,а остальные формулы можно получить методом автозаполнения (копирования).
6. В ячейку F1 занесем формулу целевой функции =100*(С1
+ D1 + Е1).Результат ввода данных в рабочую таблицу представ- лен на рис. 18.
Рис. 18. Результат ввода данных из примера
Дадим команду Сервис >Поиск решения — откроется диа-
логовое окно Поиск решения.
В поле Установить целевую ячейку мышью укажем ячейку,
содержащую оптимизируемое значение (F1) (рис.19). Установим переключатель Равной в положение максимальному значению (требуется максимальный объем производства).
Рис. 19. Пример заполнения диалогового окна Поиск решения
56
9. В поле Изменяя ячейки мышью зададим диапазон под-
бираемых параметров (неизвестных x )— С1:Е1.
i
10. Чтобы определить набор ограничений, щелкнем на
кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку мышью укажем диапазон В2:В4.В каче- стве условия зададим <=. В поле Ограничение мышью зададим диапазон А2:А4(рис. 20). Это условие указывает, что дневной расход ингредиентов не должен превосходить запасов. Щелкнем на кнопке ОК.
Рис.20. Пример заполнения диалогового окна Добавление ограничения
11. Снова щелкнем на кнопке Добавить. В поле Ссылка на
ячейку укажем диапазон С1:Е1.В качестве условия зададим >=. В поле Ограничение зададим число 0.Это условие указывает, что число приготавливаемых блюд неотрицательно. Щелкнем на кнопке ОК.
12. Снова щелкнем на кнопке Добавить. В поле Ссылка на
ячейку укажем диапазон С1:Е1.В качестве условия выберем пункт цел. Это условие не позволяет производить доли блюд. Щелкнем на кнопке ОК.
13. Щелкнем на кнопке Выполнить. По завершении оптими-
зации откроется диалоговое окно Результаты поиска решения.
14. Установим переключатель Значения параметров в поло-
жение Сохранить найденное решение, после чего щелкнем на кнопке ОК.
В результате получится оптимальный набор переменных
(оптимальное количество приготавливаемых фирменных блюд) при данных ограничениях (при данном количестве ингредиен- тов): блюда А — 184 порции (х
),блюда В — 24 порции (х )и
1 2
блюда С — 8 порций (х ).При этом общая стоимость блюд (Z)
3
будет максимальной и равной 21 600 руб. При этом останутся неизрасходованными 40 г первого ингредиента (рис. 21).
57
Рис. 21. Результат вычислений из примера
Проанализируем полученное решение. Проверить его опти-
мальность можно, экспериментируя со значениями ячеек С1:Е1. Например, допустим, что решили приготовить количества блюд, соответственно 184, 23, 9. Тогда при той же общей стоимости блюд будет перерасход второго ингредиента на 40 г, что, естест- венно, недопустимо. Можно рассмотреть и другие варианты. Чтобы восстановить оптимальные значения, можно в любой мо- мент повторить операцию поиска решения.
Пример.Туристская фирма заключила контракт с двумя
турбазами: в г. Сухуми и в окрестных городах, рассчитанных, со- ответственно, на 200 и 150 человек.
Туристам для осмотра предлагается обезьяний питомник в
городе, ботанический сад и поход в горы. Составьте маршрут движения туристов так, чтобы это обошлось возможно дешевле, если:
Обезьяний питомник принимает в день 70 человек, ботани-
ческий сад — 180 человек, а в горы в один день могут пойти 110 человек;
Стоимость одного посещения выражается таблицей:
Турбаза ОП БС Поход
1 5 6 20
2 10 12 5
Решение.Для решения задачи введем обозначения: пусть
x — число туристов из турбазы в Сухуми, посещающих обезья-
1
ний питомник; х —число туристов из турбазы в Сухуми, посе-
2
щающих ботанический сад; х —число туристов из турбазы в Су-
3
хуми, отправляющихся в поход; x — число туристов из окрест-
4
ной турбазы, посещающих обезьяний питомник; х —число ту-
5
ристов из окрестной турбазы, посещающих ботанический сад; х
6
—число туристов из окрестной турбазы, отправляющихся в по-
58
ход. Составим целевую функцию — она заключается в миними- зации стоимости дневных мероприятий турфирмы:
Z=5 x + 6×х +20×x + 10 ×x + 12 ×х +5 ×х .
l 2 3 4 5 6
Определим имеющиеся ограничения (руководствуясь усло-
виями задачи):
1 . x + x ≤ 70
1 4
2 . x + x ≤ 180
2 5
3 . x + x ≤ 110
3 6
4 . x + x + x = 200
1 2 3
5 . x + x + x = 150
4 5 6
Кроме того, поскольку турист неделим и количество тури-
стов, участвующих в каждом мероприятии, не может быть отри- цательным, добавим еще ряд ограничений:
1) x ≥ 0 ;
1
2) х ≥ 0 ;
2
3) х ≥0;
3
4) х ≥0;
4
5) х ≥ 0;
5
6) х ≥ 0;
6
7) x —целое;
l
8) х — целое;
2
9) х — целое;
3
10) х —целое;
4
11) х —целое;
5
12) х —целое.
6
Теперь можно приступить к решению задачи на компьюте-
ре.
1. Откройте новый рабочий лист (Вставка > Лист).
2. В ячейки А2, A3 и А4 занесите дневное количество по-
сетителей различных мероприятий — числа 70, 180и 110,соот- ветственно.
3. В ячейки А5 и Аб занесите количество туристов в обеих
гостиницах — числа 200,и 150,соответственно.
4. В ячейки С1:Н1 занесите начальные значения неизвест-
ных х х ,...,х (нули) — в дальнейшем значения этих ячеек будут
1 , 2 6
подобраны автоматически.
59
5. В ячейках диапазона С2:Нб разместите таблицу коэффи-
циентов основных ограничений:
• 1,0,0,1,0,0;
• 0,1,0,0,1,0;
• 0,0,1,0,0,1;
• 1,1,1,0,0,0;
• 0,0,0,1,1,1.
6. В ячейках В2:Вб укажите формулы для расчета ограни-
чений. В ячейке В2 формула будет иметь вид=$С$1*С2 + $D$1*D2 + $Е$1*Е2 + $F$1*F2+ $G$1*G2+$Н$1*Н2,аосталь- ные формулы можно получить методом автозаполнения (копиро- вания).
7. В ячейку I1 занесите формулу целевой функции =5*С1
+ 6*D1 + 20*Е1 + + 10*F1 + 12* G1 +5*Н1.
8. Дайте команду Сервис > Поиск решения — откроется
диалоговое окно Поиск решения.
9. В поле Установить целевую ячейку укажите ячейку, со-
держащую оптимизируемое значение (11).Установите переклю- чатель Равной в положение минимальному значению (требуется минимальный объем затрат).
10. В поле Изменяя ячейки задайте диапазон подбираемых
параметров (неизвестных xi)- С1:Н1.
11. Чтобы начать определять набор ограничений, щелкните
на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диапазон В2:В4.В качестве ус- ловия задайте <=. В поле Ограничение задайте диапазон А2:А4. Это условие указывает, что дневное количество посетителей ме- роприятий не должно превосходить их возможностей. Щелкните на кнопке ОК.
12. Для продолжения определения набора ограничений
щелкните на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диапазон В5:В6.В качестве условия задайте =. В поле Ограничение задайте диапа- зон А5:А6.Это условие указывает, что дневное количество по- сетителей мероприятий должно быть равно количеству туристов. Щелкните на кнопке ОК.
60
13. Снова щелкните на кнопке Добавить. В поле Ссылка на
ячейку укажите диапазон С1:Н1.В качестве условия задайте >=. В поле Ограничение задайте число 0.Это условие указывает, что число участников мероприятий неотрицательно. Щелкните на кнопке ОК.
14. Снова щелкните на кнопке Добавить. В поле Ссылка на
ячейку укажите диапазон С1:Н1,В качестве условия выберите пункт цел. Это условие указывает, что турист неделим. Щелкните на кнопке ОК.
15. Щелкните на кнопке Выполнить. По завершении опти-
мизации откроется диалоговое окно Результаты поиска решения.
16. Установите переключатель Сохранить найденное ре-
шение, после чего щелкните на кнопке ОК.
В результате получится оптимальный набор переменных
(оптимальное количество туристов для участия в каждом меро- приятии из каждой гостиницы) при данных ограничениях (при заданных возможностях мероприятий): число туристов из турба- зы в Сухуми, посещающих обезьяний питомник (x
= 30), ботани-
l
ческий сад (х =170) и отправляющихся в поход (х = 0); число
2 3
туристов из окрестной турбазы, посещающих обезьяний питом- ник (х
= 40), посещающих ботанический сад (х = 0) и отправ-
4 5
ляющихся в поход (х -110). При этом суммарные расходы тур-
ъ
фирмы (Z) составят 2120 руб. и будут минимальными.
Можно проанализировать полученное решение. Его опти-
мальность проверяется путем эксперимента со значениями ячеек С1:Н1. Чтобы восстановить оптимальные значения, можно в лю- бой момент повторить операцию поиска решения.
Другими типовыми примерами задач линейного програм-
мирования являются задачи:
1. О рационе питания.
2. Об оптимальных перевозках.
3. Об оптимальном плане пошивочной мастерской.
4. О рациональном использовании сырья. Рассмотрим эти задачи в качестве упражнений.
61
УПРАЖНЕНИЯ
1. Каждому животному нужно ежедневно выдать не менее 6
единиц белков, 8 единиц жиров и 12 единиц углеводов. Есть два вида корма. Одна единица первого корма содержит 21 единицу белка, 2 единицы жира, 4 единицы углеводов и стоит 3 руб. Для второго корма соответствующие цифры следующие: 3, 2, 2 и 2. Составьте математическую модель и найдите оптимальный раци- он питания.
2. Продукцию, производимую на предприятиях А и В, надо
развезти по магазинам № 1, № 2 и № 3. Предприятие А произво- дит 320 единиц продукции, предприятие В — 380. Магазин № 1 реализует за сутки 200 кг, № 2 — 280 кг, № 3 — 220 кг. Составьте план перевозок продукции, при котором их стоимость будет наи- меньшей, если стоимость перевозки 1 кг продукции задана таб- лицей:
3. Пошивочная мастерская планирует выпуск двух видов кос-
тюмов: мужских и женских. На женский костюм требуется 1 м шерсти, 2 м лавсана и 1 человеко-день трудозатрат. На мужской костюм — 3,5 м шерсти, 0,5 м лавсана и 1 человеко-день трудоза- трат. Всего имеется 350 м шерсти и 240 м лавсана, 150 человеко- дней трудозатрат. Предусматривается выпуск не менее 110 кос- тюмов, причем необходимо обеспечить прибыль не менее 1400 руб. Определите оптимальное количество костюмов каждого ви- да, если прибыль от реализации женского костюма составляет 10 руб., а мужского — 20 руб.
4. Составьте оптимальный план производства продукции,
чтобы стоимость всего объема произведенного была максималь- ной, если: цена 1 единицы каждой продукции по 20 денежных единиц. На каждую единицу первой продукции расходуется 2
62
единицы сырья; 4 единицы материалов и 1 человеко-день; второй продукции — соответственно, 2, 3 и 3. Общие объемы ресурсов:
• фонд рабочего времени — 12;
• фонд сырья — 16;
• фонд материалов — 9;
• цена 1 единицы сырья —-1 денежная единица;
• цена материалов — 3 денежных единицы.
Проанализируйте математическую постановку этой задачи;
как увеличить стоимость всей продукции, если можно привлечь дополнительные ресурсы, лишние продавать?
5. Составьте оптимальный план производства, чтобы стоимость
всей продукции была максимальной, если:
Стоимость 1 ед . Норма расходов ресурсов
Продукция продукции Трудовых Сырьевых Материалов
1 40 6 8 6
2 30 5 7 5
Общие объемы ресурсов:
• трудовых — 48;
• сырьевых — 56;
• материалов — 72;
• цена одной единицы сырья — 2 денежные единицы;
• материалов — 1,5 денежные единицы.
Проанализируйте составленный оптимальный план: как
можно увеличить стоимость всей продукции, если исходить из возможности свободно распоряжаться ресурсами.
Аппроксимация экспериментальных данных
Аппроксимацией называется процесс подбора эмпириче-
ской формулы φ(х)для установленной из опыта функциональной зависимости y=f(x).Эмпирические формулы служат для аналити- ческого представления опытных данных.
63
Одна независимая переменная
Обычно задача аппроксимации распадается на две части:
1. Устанавливают вид зависимости y=f(x)и вид эмпирической
формулы, то есть решают, является ли она линейной, квадратич- ной, логарифмической или какой-либо другой. 2.
Определяются численные значения неизвестных параметров
выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим. Если нет каких-либо теоретических соображений для подбора вида формулы, обычно выбирают функциональную зависимость из числа наиболее простых, сравнивая их графики с графиком за- данной функции. После выбора вида формулы определяют ее па- раметры. Для наилучшего выбора параметров задают меру бли- зости аппроксимации экспериментальных данных. Если функция f(x) задана графиком или таблицей (на дискретном множестве то- чек), для оценки степени приближения рассматривают разности f(х
)- φ(х ) для точек х х х .Существуют различные меры бли-
1 i 0, 1,…, n
зости и, соответственно, способы решения этой задачи. Некото- рые из них очень просты, быстро приводят к результату, но ре- зультат этот является сильно приближенным. Другие более точ- ные, но и более сложные. Обычно определение параметров при известном виде зависимости осуществляют по методу наи- меньших квадратов. При этом функция φ(x) считается наилуч- шим приближением к f(х),если для нее сумма квадратов невязок δ
, или отклонений «теоретических» значений φ(х ), найденных по
i i
эмпирической формуле, от соответствующих опытных значений y
i
Z = ∑ n [ f ( x ) − ? ( x ) ] 2 ? ?→ min (3.10)
i i
i = 0
имеет наименьшее значение по сравнению с другими функ-
циями, из числа которых выбирается искомое приближение.
Используя методы дифференциального исчисления, метод
наименьших квадратов формулирует аналитические условия дос- тижения суммой квадратов отклонений (3.10) своего наименьше- го значения. Так, если функция φ(х) вполне определяется своими параметрами k, l, т, ...,то наилучшие (в указанном смысле (3.10)) значения этих параметров находятся из решения системы урав-
64
нений. Например, в простейшем случае, когда функция φ(х)представлена линейным уравнением у = ах + b,система име- ет вид:
? a * ∑ n x 2 + b * ∑ n x = ∑ n x * y
? ? ? i i i i
i = 1 i = 1 i = 1
? ? a * ∑ n x + b * n = ∑ n y (3.11)
? i i
i = 1 i = 1
В простейшем случае задача аппроксимации эксперимен-
тальных данных выглядит следующим образом.
Пусть есть какие-то данные, полученные практическим пу-
тем (в ходе эксперимента или наблюдения), которые можно пред- ставить парами чисел (х; у).Зависимость между ними отражает таблица:
X x …… x
1 n
Y y …… y
1 n
На основе этих данных требуется подобрать функцию
у= φ(х), которая наилучшим образом сглаживала бы эксперимен- тальную зависимость между переменными и по возможности точно отражала общую тенденцию зависимости между хи у,ис- ключая погрешности измерений и случайные отклонения. Это значит, что отклонения y
- y (х ) в каком-то смысле были бы наи-
i i i
меньшими. Например, в смысле (3.10).
Выяснить вид функции можно либо из теоретических сооб-
ражений, либо анализируя расположение точек (х ;y ) на коорди-
n n
натной плоскости.
Например, пусть точки расположены так, как показано на
рис. 22, учитывая то, что практические данные получены с неко- торой погрешностью, обусловленной неточностью измерений, необходимостью округления результатов и т. п., естественно предположить, что здесь имеет место линейная зависимость у = ах + b.
65
Рис.22. Возможный вариант расположения экспериментальных точек
Чтобы функция приняла конкретный вид, необходимо ка-
ким-то образом вычислить аи b.Для этого можно решить систе- му (3.11).
Расположение экспериментальных точек в виде кривой на
рис. 23 наводит на мысль, что зависимость обратно пропорцио- нальна и функцию φ(х)нужно подбирать в виде у = а + b/х.Здесь также необходимо вычислить параметры аи b.
Рис. 23. Другой вариант расположения экспериментальных точек
Таким образом, расположение экспериментальных точек
может иметь самый различный вид, и каждому соответствует конкретный тип функции. Построение эмпирической функции сводится к вычислению входящих в нее параметров, так чтобы из всех функций такого вида выбрать ту, которая лучше других опи- сывает зависимость между изучаемыми величинами. То есть сумма квадратов разности между табличными значениями функ- ции в некоторых точках и значениями, вычисленными по полу- ченной формуле, должна быть минимальна.
В MS Excel аппроксимация экспериментальных данных
осуществляется путем построения их графика (х— отвлеченные величины) или точечного графика (х —имеет конкретные значе-
66
ния) с последующим подбором подходящей аппроксимирующей функции (линии тренда). Возможны следующие варианты функ- ций:
1. Линейная — у = ах+b.Обычно применяется в простей-
ших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью.
2. Полиномиальная — y = a + a x + a x 2 + ... + a x n , где до
0 1 2 n
шестого порядка включительно (п≤6), а — константы. Использу-
i
ется для описания экспериментальных данных, попеременно воз- растающих и убывающих. Степень полинома определяется коли- чеством экстремумов (максимумов или минимумов) кривой. По- лином второй степени может описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени — не более трех экстремумов и т. д.
3. Логарифмическая — у= alnx + b,где аи b— константы,
In —функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале бы- стро растут или убывают, а затем постепенно стабилизируются.
y = bx a
4. Степенная— ,где аи b —константы. Аппрок-
симация степенной функцией используется для эксперименталь- ных данных с постоянно увеличивающейся (или убывающей) скоростью роста. Данные не должны иметь нулевых или отрица- тельных значений.
5. Экспоненциальная — y = be ax ,где аи b —константы,
е— основание натурального логарифма. Применяется для описа- ния экспериментальных данных, которые быстро растут или убы- вают, а затем постепенно стабилизируются. Часто ее использова- ние вытекает из теоретических соображений.
Степень близости аппроксимации экспериментальных дан-
ных выбранной функцией оценивается коэффициентом детерми- нации (R
2 ). Таким образом, если есть несколько подходящих ва-
риантов типов аппроксимирующих функций, можно выбрать функцию с большим коэффициентом детерминации (стремящим- ся к 1).
67
Для осуществления аппроксимации на диаграмме экспери-
ментальных данных необходимо щелчком правой кнопки мыши вызвать выплывающее контекстное меню и выбрать пункт Доба- вить линию тренда. В появившемся диалоговом окне Линия тренда на вкладке Тип выбирается вид аппроксимирующей функции, а на вкладке Параметры задаются дополнительные па- раметры, влияющие на отображение аппроксимирующей кривой.
Пример. Исследовать характер изменения с течением времени уровня производства некоторой продукции и подобрать аппрок- симирующую функцию, располагая следующими данными:
Решение
1. Для построения диаграммы, прежде всего, необходимо
ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Год.Затем в ячейки А2:Аб последовательно вводим годы, начи- ная с 1997. Далее в ячейку В1 заносим слово Продукцияи уста- навливаем табличный курсор в ячейку В2. Здесь должно оказать- ся значение 17,1соответствующее значению года в ячейке А2. Аналогично заполняем ячейки ВЗ:Вб.
2. Далее по введенным в рабочую таблицу данным необхо-
димо построить диаграмму. Поскольку здесь необходимо строить динамику изменений производства продукции, не привязываясь к конкретному году, а от отвлеченных переменных — выберем диаграмму График.
Щелчком указателя мыши на кнопке на панели инструмен-
тов вызываем Мастер диаграмм. В появившемся диалоговом ок- не выбираем тип диаграммы График, вид — левый средний. По- сле нажатия кнопки Далее указываем диапазон данных — В1:Вб (с помощью мыши). Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим
68
диапазон подписей оси X: А2:А6.Нажав кнопку Далее, вводим название диаграммы — Производство продукции,название осей X и У: Годыи Условные единицы,соответственно. Нажимаем кнопку Готово.
Получен график экспериментальных данных.
3. Осуществим аппроксимацию полученной кривой поли-
номиальной функцией второго порядка, поскольку кривая до- вольно гладкая и не сильно отличается от прямой линии. Для это- го указатель мыши устанавливаем на одну из точек графика и щелкаем правой кнопкой. В появившемся контекстном меню вы- бираем пункт Добавить линию тренда. Появляется диалоговое окно Линия тренда (рис. 24).
Рис. 24. Вкладка Тип диалогового окна Линия тренда
В этом окне на вкладке Тип выбираем тип линии тренда—
Полиномиальная и устанавливаем степень — 2.Затем открываем вкладку Параметры (рис. 25) и устанавливаем флажки в поля по-
69
казывать уравнение на диаграмме и поместить на диаграмму ве- личину достоверности аппроксимации (R
^ 2). После чего нужно
щелкнуть на кнопке ОК.
В результате получим на диаграмме аппроксимирующую
кривую (рис. 26).
Как видно из рисунка 26, уравнение наилучшей полиноми-
альной аппроксимирующей функции для некоторых отвлеченных значений х(1, 2, 3, ...) выглядит как
y = − 0 , 14 x 2 + 1 , 5 x + 15 , 66 .
При этом точность аппроксимации достаточно высока
— R 2 = 0,986.
Рис. 25. Вкладка Параметры диалогового окна Линия тренда
70
Рис.26. Экспериментальные данные, аппроксимированные
полиномиальной кривой
4. Попробуем улучшить качество аппроксимации выбором
другого типа функции (возможно более адекватного). Здесь воз- можным вариантом представляется логарифмическая функция. Для этого повторяем операции п. 3. за исключением того, что в окне Линия тренда на вкладке Тип выбираем тип линии тренда — Логарифмическая.
В результате получим другой вариант аппроксимации —
логарифмической кривой (рис. 27).
Как можно видеть из рисунка 27, уравнение наилучшей ло-
гарифмической аппроксимирующей функции несколько уступает по точности аппроксимации полиномиальной кривой — R
2 -
0,9716 < 0,986. Поэтому если нет каких-либо теоретических со- ображений, то можно считать, что наилучшей аппроксимацией является аппроксимация полиномиальной функцией второй сте- пени (из двух рассмотренных вариантов).
71
Рис.27. Экспериментальные данные, аппроксимированные
логарифмической кривой
Пример.После выброса ядовитого вещества его концентрация (мг/л) в водоеме изменялась в соответствии со следующей табли- цей:
Определить вид функциональной зависимости изменения
концентрации вещества от времени и оценить его концентрацию в водоеме в момент выброса.
Решение
1. Для построения диаграммы, прежде всего, необходимо
ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Время.Затем в ячейки А2:А5 последовательно вводим время: 1,3,5, 8.Далее в ячейку В1 заносим слово Концентрацияи в диа- пазон В2:В5 вводим соответствующие концентрации вещества.
2. Далее по введенным в рабочую таблицу данным необхо-
димо построить диаграмму. Поскольку здесь необходимо строить динамику изменений концентрации вещества в соответствии с изменениями времени — будем строить диаграмму Точечная.
72
Щелчком указателя мыши на кнопке на панели инструмен-
тов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы Точечная, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В5 (с помощью мыши). Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А5.Нажав кнопку Далее, вводим название диаграммы — Концентрация вещества,название осей X и У: Времяи Концентрация,соответственно. Нажимаем кноп- ку Готово.
Получен график экспериментальных данных.
3. Осуществим аппроксимацию полученной кривой. По-
скольку кривая напоминает экспоненту и из теоретических сооб- ражений наиболее вероятный закон изменения — экспоненци- альный, целесообразно аппроксимировать кривую .изменения концентрации экспоненциальной функцией. Для этого указатель мыши устанавливаем на одну из точек графика и щелкаем правой кнопкой. В появившемся контекстном меню выбираем пункт До- бавить линию тренда. Появляется диалоговое окно Линия тренда.
В этом окне на вкладке Тип выбираем тип линии тренда —
Экспоненциальная. Затем открываем вкладку Параметры и уста- навливаем флажки в поля показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R
A 2), Кроме этого, для того, чтобы оценить концентрацию веще-
ства в водоеме в момент выброса в поле Прогноз назад на уста- навливаем 1 периодов.После чего щелкаем на кнопке ОК. В ре- зультате получим на диаграмме аппроксимирующую кривую (рис. 28).
73
Рис.28. Экспериментальные данные, аппроксимированные
экспоненциальной функцией
Как видно из рисунка 28, уравнение наилучшей экспонен-
циальной аппроксимирующей функции для зависимости концен- трации от времени выглядит как
y = 11 , 844 e − 0 , 4695 x (3.12)
При этом точность аппроксимации очень высокая —
R 1 = 0,9951, что позволяет считать описание процесса изменения
концентрации вещества в водоеме экспоненциальной функцией адекватным. Расчетная оценка концентрации вещества в момент выброса, как видно из графика, составляет около 12 мг/л. Более точные цифры могут быть получены из уравнения (3.12) при х =0 (y
= 11,84 мг/л).
0
УПРАЖНЕНИЯ
1 Построить функцию, наилучшим образом отражающую
данную зависимость:
2. В 80-е годы уровень дефицита бюджета в СССР и США
складывался следующим образом:
74
Построить функции, наилучшим образом отражающие зави-
симости дефицита бюджета от времени в обеих странах. 3. Количество вложенных в производство средств и полу- ченная в результате прибыль соотносятся следующим образом:
Запишите аналитическую зависимость между хи у.Проана-
лизируйте полученный ответ. Каковы перспективы предприятия? Какая будет прибыль, если вложить 10,0 единиц?
Сколько надо вложить средств, чтобы получить прибыль
100,0 единиц?
Несколько независимых переменных
В тех случаях, когда аппроксимируемая переменная узави-
сит от нескольких независимых переменных х , х , ..., х ,
1 2 n
y=f(х ,x ,...,x ),
1 2 n
подход с построением линии тренда не дает решения. Здесь
могут быть использованы следующие специальные функции MS Excel:
ЛИНЕЙН и ТЕНДЕНЦИЯ для аппроксимации линейных
функций вида:
y = a 0 + a x + a x + ... + a x (3.13)
1 1 2 2 n n
ЛГРФПРИБЛ и РОСТ для аппроксимации показательных
функций вида:
y = a a x 1 a x 2 ... a xn (3.14)
0 1 2 n
Функции ЛИНЕЙН и ЛГРФПРИБЛ служат для вычисления
неизвестных коэффициентов a , a ,..., a ,в выражениях (3.13) и
0 1 n
(3.14) соответственно, а также коэффициентов детерминации (R
2 ), значений критерия Фишера, стандартных ошибок коэф-
фициентов я, и ряда других показателей.
Обе функции имеют одинаковые параметры:
ЛИНЕЙН (известные_значения_у; известные_значения_х;конст; статистика)
ЛГРФПРИБЛ(известные_значения_у;известные_значения_х;конс т;статистика)Здесь:
- известные_значения_у —множество наблюдаемых зна-
чений уиз выражений (3.13), (3.14);
75
- известные_значения_х— множество наблюдаемых зна-
чений x x , ..., х .Причем, если массив известные_значения_у
1, 2 п
имеет один столбец, то каждый столбец массива извест- ные__значения_хинтерпретируется как отдельная переменная, а если массив известные__значения_уимеет одну строку, то тогда каждая строка массива известные_значеиия_хинтерпретируется как отдельная переменная;
- конст —логическое значение, которое указывает, требу-
ется ли, чтобы константа а была равна 0 (для функции ЛИНЕЙН)
0
или 1 (для функции ЛГРФПРИБЛ).
При этом, если констимеет значение ИСТИНА или опу-
щено, то я вычисляется обычным образом, а если констимеет
0
значение ЛОЖЬ, то а полагается равным 0 или 1;
0
- статистика— логическое значение, которое указывает,
требуется ли вычислять дополнительную статистику по регрес- сии, если введено значение ИСТИНА, то дополнительные пара- метры вычисляются, если ЛОЖЬ, то — нет (рис. 29).
Рис.29.Пример заполнения диалогового окна функции
Функции ТЕНДЕНЦИЯ и РОСТ позволяют находить точки,
лежащие на аппроксимирующих кривых (3.13) и (3.14), соответ- ственно, для значений коэффициентов а
, а , ...,а ,найденных
0 1 п
функциями ЛИНЕЙН и ЛГРФПРИБЛ.
Обе функции имеют одинаковые аргументы:
ТЕНДЕНЦИЯ (известные_значения_у;известные_значения_х;новые_ значения_х;конст;
РОСТ(известные_значения_у;известные_значения_х;новые
_значения_х;конст).
76
Здесь:
известные _значения _у —множество значений у;
известные _значения_х —множество значений х;
новые _значения_х— те значения х,для которых необходи-
мо определить соответствующие аппроксимирующие или пред- сказанные значения у. Новые_значения_хдолжны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_х.Если аргумент новые _значеиия_хопу- щен, то предполагается, что он совпадает с аргументом извест- ные _значения_х;
конст— логическое значение, которое указывает, требуется
ли, чтобы константа а была равна 0 (для функции ТЕНДЕНЦИЯ)
0
или 1 (для функции РОСТ). При этом, если констимеет значение ИСТИНА или опущено, то а
вычисляется обычным образом, а
0
если констимеет значение ЛОЖЬ, то а полагается равным 0 или
0
1 (рис. 30).
Пример.Источник радиоактивного излучения помещен в жид- кость. Датчики расположены на расстоянии (х
)20, 50 и 100 см от
1
источника. Измерения интенсивности излучения (у,мРн) прово- дились через 1, 5 и 10 суток (х
)после установки источника. Ре-
2
зультаты измерений (у)приведены в таблице:
Необходимо аппроксимировать данные уравнением вида
(3.14) и найти неизвестные параметры.
77
Рис.30. Пример заполнения диалогового окна функции ТЕНДЕНЦИЯ
Решение
1. Введем данные в рабочую таблицу: в ячейку А1 — сим-
вол х , в ячейку В1 — х ,в ячейку С1 — у.В диапазон ячеек А2:
1 2
А10 внесем значения х в диапазон В2:В10 — значения х и в
1; 2
диапазон С2:С10 — значения у(рис. 31).
Рис. 31. Исходные данные из примера
2. Выделяем блок ячеек D1:F5 под массив результатов.
3. Поскольку уравнение для вычисления интенсивности из-
лучения имеет степенной характер (3.14), вызываем функцию ЛГРФПРИБЛ (панель инструментов Стандартная, кнопка Встав- ка функции, рабочее поле Категория тип Статистические, рабочее поле Функция вид ЛГРФПРИБЛ).
78
4. Заполняем рабочие поля: Изв_знач_у — С2:С10,
Изв_знач_х — А2:В10,Стат-1(рис. 3.14). Нажимаем сочетание клавиш CTRL+SHIFT+ENTER.
5. В результате в диапазоне D1:F5 получим следующие
данные:
Здесь первая строка — значения коэффициентов а , а , а ,
2 1 0
соответственно, вторая строка — стандартные ошибки этих ко- эффициентов, третья строка — коэффициент детерминации R
2 и
стандартная ошибка у,четвертая строка — значение критерия Фишера и число степеней свободы и нижняя строка — сумма квадратов регрессии и остаточная сумма квадратов.
Таким образом, искомое аппроксимирующее уравнение
имеет вид:
y = 99 , 7 * 0 , 98 x 1 * 0 , 92 x 2
Причем точность аппроксимации очень высокая — R 2 =
0,99998.
Пример. В бассейне проводится ежедневная частичная смена во- ды. Имеются данные семидневных наблюдений изменения уров- ня воды в бассейне (у) от продолжительности заполнения водой (и времени выпуска воды (х
).
2
Необходимо найти значения уровня воды в бассейне в за-
висимости от длительностей заполнения x l ∈ [100; 130] и выпуска
воды х 2 ∈ [15; 25] с шагом Δ = 5 минут. Построить поверхность.
79
Решение
1. Введем данные в рабочую таблицу: в ячейку А1 —
символ х ,в ячейку В1 — х ,в ячейку С1 —у.В диапазон ячеек
1 2
А2:А8 внесем значениях х , в диапазон В2:В8 — значения х и в
1 2
диапазон С2:С8 — значения у.
2. Введем значения x и х для получения расчетных зна-
1 2
чений у всоответствии с заданием: x l ∈ [100; 130] в диапазон
А10:АЗО, а х 2 ∈ [15; 25] в диапазон В10:ВЗО (рис. 3.17).
3. Выделим блок ячеек С10:СЗ0 под массив расчетных
(предсказанных) значений у.
4. Поскольку уравнение для вычисления уровня воды ли-
нейное (3.13), вызываем функцию ТЕНДЕНЦИЯ (панель инстру- ментов Стандартная, кнопка Вставка функции, рабочее поле Ка- тегория тип Статистические, рабочее поле Функция вид ТЕН- ДЕНЦИЯ).
5. Заполняем рабочие поля: Изв_знач_у —- С2:С8,
Изв_знач_х —А2:В8,Нов_знач_х — А10:ВЗО(рис. 30). Нажима- ем сочетание клавиш Ctrl+Shift+Enter.
6. В результате в диапазоне С10:СЗО получим предска-
занные значения у(рис. 3.17).
7. Формируем блок данных для построения диаграммы.
Для этого введем значения переменной х в столбец Е. Для этого
1
в ячейку Е1 вводим символ х.В диапазон ячеек Е2:Е8 — значе- ния х
1 ∈ [100; 130] с шагом Д = 5 минут. В диапазон Fl:HI вводят-
ся значения х 15,20,30.Затем диапазон F2:H8 заполняется соот-
2
ветствующими расчетными значениями у.
80
Рис. 32. Расчетные значения у и соответствующие им значения х, и Х
2
из примера
В результате должна быть получена следующая таблица
(рис. 33).
Рис. 33. Данные из примера, подготовленные для построения
плоскости
Для построения диаграммы на панели инструментов Стан-
дартная необходимо нажать кнопку Мастер диаграмм. В появив- шемся диалоговом окне Мастер диаграмм (шаг! из 4): тип диа- граммы указываем тип диаграммы — Поверхность, и вид— Про- волочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.
9. В появившемся диалоговом окне Мастер диаграмм (шаг 2
из 4):источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных F2:H8.
81
Далее необходимо указать в строках или столбцах располо-
жены ряды данных. Это определит ориентацию осей Xи Y.В примере переключатель Ряды в с помощью указателя мыши ус- тановим в положение столбцах.
10. Выбираем вкладку Ряд и в поле Подписи оси X указы-
ваем диапазон подписей. Для этого следует активизировать поле Подписи оси X, щелкнув в нем указателем мыши, и ввести диапа- зон подписей оси X — Е2:Е8.
11. Вводим значения подписей оси Y.Для этого в рабочем
поле Ряд указываем первую запись Ряд1 и в рабочее поле Имя, активизировав его указателем мыши, вводим первое значение пе- ременной Y (x
) — 15.Затем в поле Ряд указываем вторую запись
2
Ряд2 и в рабочее поле Имя вводим второе значение переменной х
— 20.Аналогично указываем х = 25 вместо записи Ряд 3.По-
2 2
сле появления требуемых записей необходимо нажать кнопку Далее.
12. В третьем окне требуется ввести заголовок диаграммы
и названия осей. Для этого необходимо выбрать вкладку Заголов- ки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле указателем мыши, ввести с клавиатуры в поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие на- звания: х
,х и у.
1 2
13. Нажимаем кнопку Готово, и после небольшого редак-
тирования будет получена диаграмма изменения уровня воды в бассейне (рис. 34).
Рис.34. Диаграмма изменения уровня воды в бассейне в зависимости от
соотношения времени заполнения и выпуска воды
82
УПРАЖНЕНИЯ
1. В условиях примера 3.8 найти параметры аппроксимирующего уравнения и оценить его точность.
2. В условиях примера 3.7 найти расчетные значения интенсив- ности излучения для следующих значений х,и х
:
2
3. Застройщик оценивает стоимость группы небольших офисных
зданий в традиционном деловом районе. Оценку цены офисного здания в заданном районе застройщик предполагает осуществ- лять на основе следующих переменных: у —оценочная цена зда- ния под офис, х
—общая площадь в квадратных метрах, х —ко-
1 2
личество офисов, х — количество входов, х —время эксплуата-
3 4
ции здания в годах. Предполагается, что существует линейная за- висимость между каждой независимой переменной (х
, х , х и х )
1 2 3 4
и зависимой переменной (у),то есть ценой здания под офис в данном районе. Застройщик наугад выбирает 11 зданий из имеющихся 1500 и получает следующие данные:
Здесь «полвхода» (1/2) означает вход только для доставки
корреспонденции. Найти параметры аппроксимирующего урав- нения.
4. В условиях упражнения 14 с помощью функции ТЕН-
ДЕНЦИЯ определить оценочную стоимость здания под офис в том же районе, которое имеет площадь 2500 квадратных метров, три офиса, два входа, зданию 25 лет.
83
84
PDF created with FinePrint pdfFactory Pro trial version www.pdffactory.com
Работы, похожие на: Excel в математических и статистических расчетах
Решение математической задачи с помощью математических исследований и помощью специального офисного приложения MS Excel
Решение задачи с помощью математической модели и средств MS Excel
Решение математических задач средствами Excel
Решение математических задач средствами Excel
Решение задачи с помощью математической модели и средств MS Excel
Решение математических задач в среде Excel
Решение математических задач в среде Excel
Использование Excel для решения статистических задач
Применение встроенных функций табличного редактора excel для решения прикладных статистических задач
Применение MS Excel для решения статистических задач

комментариев еще никто не писал, будьте первым