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

Министерство сельского хозяйства РФ

Федеральное государственное образовательное учреждение высшего профессионального образования

«Мичуринский государственный аграрный университет»

Кафедра информатики

__________________________________________________________________________

Утверждено Протокол № 6 экономического факультета от 14 февраля 2007

ПРАКТИКУМ
для самостоятельной работы студентов Часть 1

Методическое пособие разработано старшим преподавателем кафедры информатики М.А. Ильченко
, старшим лаборантом Л.С. Струковой
на основании государственных образовательных стандартов высшего профессионального образования. Методическое пособие рекомендовано студентам, изучающим математику, преподавателям, а также аспирантам, сталкивающимся с необходимостью математической обработки данных.

Рецензент:

Г.Б. Ширяева
ст. преподаватель кафедры математического моделирования экономических систем

Рассмотрено на заседании кафедры

Протокол № 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

Содержание

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

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

a
+ b
+ c
= 1 ,

а,b,с — отрезки, отсекаемые плоскостью на осях координат

с учетом знака.

2. Уравнение плоскости, проходящей через заданную точку M(x1
,y1
,z2
): a
( x
− x
1
) + b
( y
− y
1
) + c
( z
− z
1
) = 0 .

3. Уравнение плоскости, проходящей через три точки

M(x1
,y1
,z1), M(x2
,y2
,z2
), M(x3
,y3
,z3
): x
− x
1
y
− y
1
z
− z
1
x
− x
1
y
2
− y
1
z
2
− z
1
= 0.

x
− x
1 y
3 − y
1 z
3 − z
1

В MS Excel Мастер диаграмм может быть также использован и для построения плоскостей. Необходимо ввести точки плоскости в рабочую таблицу, вызвать Мастер диаграмм, задать тип диаграммы, диапазоны данных и подписей оси x
, ввести названия осей.

Пример.
Рассмотрим построение плоскости в Excel на примере уравнения 2x
+ 4 y
− 2z
+ 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).

Рис. 1. Пример подготовки данных для построения плоскости

На панели инструментов Стандартная
необходимо нажать кнопку Мастер диаграмм
. В появившемся диалоговом окне Мастер диаграмм
(шаг 1 из 4): тип диаграммы указываем тип диаграммы — Поверхность, и вид — Проволочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.

В появившемся диалоговом окне Мастер диаграмм
(шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных В2:Н14.

Далее необходимо указать в строках или столбцах расположены ряды данных. Это определит ориентацию осей х
и у
. В примере переключатель Ряды в с помощью указателя мыши установим в положение столбцах.

Выбираем вкладку Ряд и в поле Подписи оси X указываем диапазон подписей. Для этого следует активизировать поле Подписи оси X, щелкнув в нем указателем мыши, и ввести диапазон подписей оси х — А2:А14.

Далее вводим значения подписей оси у. Для этого в рабочем поле Ряд указываем первую запись Ряд У и в рабочее Поле имя, активировав его указателем мыши, вводим первое значение переменной у — 0
. Затем в поле Ряд указываем вторую запись Ряд 2 и в рабочее поле Имя вводим второе значение переменной у — 1
.

Повторяем таким образом до последней записи — Ряд 7. В результате вкладка Ряд будет иметь следующий вид (рис. 2).

Рис. 2. Пример заполнения вкладки Ряд

После появления требуемых записей необходимо нажать кнопку Далее.

В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле Название диаграммы, ввести с клавиатуры в данное поле название: Плоскость. Затем аналогичным образом ввести в рабочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: x, y
и z
.

Далее нажать кнопку Готово, и после небольшого редактирования будет получена следующая диаграмма (рис. 3).

Рис 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. Построить плоскость, проходящую через точки М1
(3,3,1), М2
(2,3, 2), М3
(1,1,3). Диапазоны изменения переменных х
и у
: х
∈ [-1;4] с шагом ∆ = 0,5,y ∈ [-1;3] с шагом ∆ = 1.

Поверхности второго порядка в пространстве.

Общее уравнение поверхностей второго порядка имеет вид уравнения второй степени:

Ах2
+ By2

+ Cz2
+2Dxy + 2Eyz
+ 2Fzx + 2Gx + 2Hy + 2Kz + L = 0
(1)

Причем коэффициенты А, В, С, D, E, F не могут быть равны нулю одновременно.

Частными случаями уравнения (1) являются основные поверхности второго порядка: эллипсоид, гиперболоид и параболоид.

Эллипсоид

Эллипсоидом называется поверхность, которая в некоторой системе декартовых прямоугольных координат определяется уравнением:

x
2 y
2 z
2

a
2 +
b
2
+
c
2
= 1 . (2)

Это уравнение называется каноническим уравнением эллипсоида.

Эллипсоид, представляет собой замкнутую овальную поверхность, обладающую тремя взаимно перпендикулярными плоскостями симметрии.

Для построения эллипсоида в Excel каноническое уравнение (2) необходимо разрешить относительно переменной z (представить в виде z=ƒ(x,y)).

Пример.
Рассмотрим построение эллипсоида на примере уравнения:

x
2 y
2 2

9 + 4 + z
= 1 .

Пусть необходимо построить верхнюю часть эллипсоида, лежащую в диапазонах: х ∈ [-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 вводится второе значение переменной — левая граница диапазона плюс шаг построения (-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): тип диаграммы указываем тип диаграммы — Поверхность, и вид — Проволочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.

В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать, вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных B2:J14.

Далее необходимо указать в сороках или столбцах расположены ряды данных. Это определит ориентацию осей х и у. В примере переключатель Ряды в с помощью указателя мыши установим в положение столбцах.

Выбираем вкладку Ряд ив поле Подписи оси X указываем диапазон подписей. Для этого щелкните в нем указателей мыши и введите диапазон подписей оси х —А2:А14.

Вводим значения подписей оси у. Для этого в рабочем поле Ряд указываем первую запись Ряд 1 и в рабочее поле Имя, активизировав его указателем мыши, вводим первое значение переменной у ─ ─ 2
. Затем в поле Ряд указываем вторую запись Ряд 2 ив рабочее поле Имя вводим второе значение переменной у ─
1,5.

Повторяем таким образом до последней записи — Ряд 9
.

После появления требуемых записей необходимо нажать кнопку Далее.

В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле Название диаграммы указателем мыши, ввести с клавиатуры в поле название: Эллипсоид. Затем аналогичным образом ввести в рабочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: х, у и z.

Далее следует нажать кнопку Готово, и после небольшого редактирования будет получена следующая диаграмма (рис. 5).

Рис. 5. Верхняя часть эллипсоида из примера

Гиперболоид

Существует два вида гиперболоидов: однополостные и двуполостные.

Однополостным гиперболоидом называется поверхность, которая в некоторой системе декартовых прямоугольных координат определяется уравнением x
2 y
2 z
2

a
2 +
b
2
−
c
2
= 1 . (3)

Однополостный гиперболоид имеет вид бесконечной трубки, расширяющейся в обе стороны от горловины.

Двуполостным гиперболоидом называется поверхность, определяемая уравнением x
2 y
2 z
2

a
2 + b
2
+ c
2
= − 1 . (4)

Двуполостный гиперболоид представляет собой поверхность, состоящую из двух отдельных полостей, каждая из которых имеет вид бесконечной выпуклой чаши. Уравнения (3) и (4) называются каноническими уравнениями гиперболоидов.

Для построения гиперболоида в Excel канонические уравнения (3) или (4), как и в случае с эллипсоидом, необходимо разрешить относительно переменной z (представить в виде z = ƒ(x, y)).

Пример.
Рассмотрим построение двуполостного гиперболоида вида.

x
2 y
2 2

9 + 4 − z
= 1 .

Пусть необходимо построить верхнюю часть гиперболоида, лежащую в диапазонах: х ∈ [-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 и на панели инструментов Стандартная нажать кнопку Вставка функции (fx
). В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория выбираем Математические. Справа в поле Функция выбираем функцию Корень. Нажимаем кнопку ОК
. Появляется диалоговое окно Корень. В рабочее поле вводим подкоренное выражение: 1 + $А2^2/9 + В$1^2/4
. Нажимаем кнопку ОК
. В ячейке В2 появляется 1,732051
. Теперь необходимо скопировать функцию из ячейки В2. Для этого автозаполнением (протягиванием вправо) копируем эту формулу вначале в диапазон B2:J2, после чего (протягиванием вниз) — в диапазон ВЗ:J4.

В результате должна быть получена следующая таблица

(рис. 6).

Рис. 6. Результаты вычислений точек гиперболоида из примера

В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных В2:J4.

Выбираем вкладку Ряд и в поле Подписи оси X указываем диапазон подписей. Для этого следует активизировать данное поле, щелкнув в нем указателем мыши, и ввести в него диапазон подписей оси х — А2:А14
.

Вводим значения подписей оси у
. Для этого в рабочем поле Ряд выбираем первую запись Ряд 1 ив рабочее поле Имя, активизировав его указателем мыши, вводим первое значение переменной у 2. Затем в поле Ряд выбираем вторую запись — Ряд 2 и в рабочее поле Имя вводим второе значение переменной у 1,5. Повторяем таким образом до последней записи — Ряд 9
.

После появления требуемых записей необходимо нажать кнопку Далее.

В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Далее щелкнув в рабочем поле Название диаграммы, ввести в него с клавиатуры название: Двуполостной гиперболоид. Затем аналогичным образом ввести в рабочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: x,y и z.

Далее следует нажать кнопку Готово, и после небольшого редактирования будет получена следующая диаграмма (рис. 7).

Двухполостный гиперболоид

Рис. 7. Верхняя часть двуполостного гиперболоида

Параболоид

Существует два вида параболоидов: эллиптические и гиперболические.

Эллиптическим параболоидом называется поверхность, которая в некоторой системе декартовых прямоугольных координат определяется уравнением x
2 y
2

+ = 2z
. (5) p q

Эллиптический параболоид имеет вид бесконечной выпуклой чаши. Он обладает двумя взаимно перпендикулярными плоскостями симметрии. Точка, с которой совмещено начало координат, называется вершиной эллиптического параболоида; числа р и q называются его параметрами.

Гиперболическим параболоидом называется поверхность, определяемая уравнением x
2 y
2

p
− q
= 2z
.

Гиперболический параболоид имеет форму седла. Он обладает двумя взаимно перпендикулярными плоскостями симметрии. Точка, с которой совмещено начало координат, называется вершиной гиперболического параболоида; числа р
и q
называются его параметрами.

Пример.
Рассмотрим построение гиперболического параболоида вида x
2 y
2

− = 2z
.

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. Для этого автозаполнением (протягиванием вправо) копируем эту формулу вначале в диапазон 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).

Рис 8. Гиперболический параболоид

Конус второго порядка

Конусом второго порядка называется поверхность, которая в некоторой системе декартовых прямоугольных координат определяется уравнением

x
2 y
2 z
2 a
2 + b
2 − c
2 = 0 .

Конус образован прямыми линиями (образующими), проходящими через начало координат (вершина конуса). Сечение конуса плоскостью, не проходящей через начало координат, дает эллипс.

В Excel построение конуса второго порядка аналогично построению других поверхностей, рассмотренных ранее.

УПРАЖНЕНИЯ

1. Построить верхнюю часть эллипсоида: x
2 y
2 z
2

4 + 9 + 4 = 1 .

Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом ∆ = 0,5, у ∈ [-3; 3] с шагом ∆ = 1.

2. Построить верхнюю часть однополостного гиперболоида: x
2 y
2 z
2

4 + 9 − 4 = 1 .

Диапазоны изменения переменных х и у: х ∈ [-3; 3] с шагом ∆ = 0,5, у ∈ [-4; 4] с шагом ∆ =1.

3. Построить эллиптический параболоид: x
2 y
2

+ = 2z
.

9 4

Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом ∆ = 0,5, у ∈ [-3; 3] с шагом ∆ =1.

4. Построить верхнюю часть конуса x
2 y
2 z
2

4 + 9 − 4 = 0 .

Диапазоны изменения переменных х и у: х ∈ [-2; 2] с шагом ∆ = 0,5, y ∈ [-3;3] с шагом ∆ = 1.

2. ЛИНЕЙНАЯ АЛГЕБРА

Средства MS Excel оказываются весьма полезны в линейной алгебре, прежде всего для операций с матрицами и решения систем линейных уравнений.

Значительная часть математических моделей различных объектов и процессов записывается в достаточно простой и компактной матричной форме. В частности, при решении линейных уравнений мы имеем дело с матрицами и арифметическими дей-

Матрицы

ствиями с ними. Что же такое матрица? Как выполняются действия с матрицами?

Матрицей размера m
× n
называется прямоугольная таблица чисел, содержащая m строк и n столбцов. Матрицы обозначаются прописными (заглавными) буквами латинского алфавита. Числа, составляющие матрицу, называются элементами матрицы и обозначаются строчными буквами с двойной индексацией: aij
, где i
— номер строки, j
— номер столбца. Например, матрица А размера m
× n
может быть представлена в виде:

a
11 a
12 K a
1n


A
= a
21 a
22 K a
2n

 = (a
ij
),

K KK K

 

a
m
1 a
m
2K a
3n


где i=1, …, m; j=1, …, n.

Две матрицы А и В одного размера называются равными, если они совпадают поэлементно, то есть a = bij
для любых i = 1,2,..., m; j = 1,2,..., n
. Матрица, состоящая из одной строки, называется матрицей (вектором) - строкой:

A
= (a
11 a
12 K a
1n
),

а из одного столбца — матрицей (вектором) - столбцом: b
11
 B
=b
21  .

 

b
m
1

Если число строк матрицы равно числу столбцов и равно и, то такую матрицу называют квадратной n -
го порядка. Например, квадратная матрица 2-го порядка:

1 3 

A
=4 5 .

Если у элемента матрицы aij
номер столбца равен номеру строки (i = j
), то такой элемент называется диагональным. Диагональные элементы образуют главную диагональ матрицы.

Квадратная матрица с равными нулю всеми недиагональными элементами называется диагональной.

Квадратная матрица называется единичной, если она диагональная, и все диагональные элементы равны единице. Единичная матрица имеет следующий вид:

1 0 K K K 0 


0 1 0 K K 0 


0 0 1 0 K 0

E
=  .

0 0 0 1 0 0

KKK K KK

 

0 KK 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
KKKK  .

 

0 0 K 0

Операции с матрицами

Как и над числами, над матрицами можно проводить ряд операций, причем в случае с матрицами некоторые из операций являются специфическими.

Транспонирование

Транспонированной называется матрица (AT
), в которой столбцы исходной матрицы (А) заменяются строками с соответствующими номерами.

В сокращенной записи, если А = (aij
),
то (AT
) = (аji
).

3 − 7 11

A
= 
18 19 39
 ; − 5 91 87

3 18 − 5 

A
T
= 
− 7 19 91
 11 39 87

Для обозначения транспонированной матрицы иногда используют символ «’» (А’). Транспонированием называется операция перехода от исходной матрицы (А) к транспонированной

(AT
).

Из определения транспонированной матрицы следует, что если исходная матрица А имеет размерm
× n
, то транспонированная матрица (AT
) имеет размер m
× n
. Для осуществления транспонирования в Excel используется функция ТРАНСП, которая позволяет поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот.

Функция имеет вид ТРАНСП (массив
). Здесь массив – это транспонируемый массив или диапазон ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и т.д.

Пример.
Предположим, что в диапазон ячеек А1:Е2 введена матрица размера 2×5

1 2 3 4 5 

A
= 6 7 8 9 0 .

Необходимо получить транспонированную матрицу.

Решение

1. Выделите (указателем мыши при нажатой левой кнопке) блок ячеек под транспонированную матрицу 2×5.Например, А4:В8.

2. Нажмите на панели инструментов Стандартная кнопку Вставка функции.

3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Функция — имя функции ТРАНСП (рис. 9). После этого щелкните на кнопке ОК.

Рис. 9. Пример выбора вида функции в диалоговом окне

Мастер функций

4. Появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:Е2 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После чего нажмите сочетание клавиш CTRL+SHIFT+ENTER (рис. 10).

Рис. 10. Пример заполнения диалогового окна ТРАНСП

5. Если транспонированная матрица не появилась в диапазоне А4:В8, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER. В результате в диапазоне А4:В8 появится транспонированная матрица:

1 6

2 7

A
= 3 8 .

 

4 9

5 0

Вычисление определителя матрицы

Важной характеристикой квадратных матриц является их определитель. Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Определитель матрицы А обозначается как A
или ∆.

Определителем матрицы первого порядка А = (aij
),
или определителем первого порядка, называется элемент aij
.

Определителем матрицы второго порядка А = (aij
)
или определителем второго порядка, называется число» которое вычисляется по формуле:

a
11 a
12 

∆2 = A
=a
21 a
22 =a
11a
22 −a
12a
21.



Произведения a
11
a
22
и a
12
a
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. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МОПРЕД. После этого щелкните на кнопке ОК.

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
 .

Тогда обратная матрица вычисляется следующим образом:

−
1
=
cd
/(/(bcad
−−adbc
))ab
/(/(adbc
−−adbc
))

.

В 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. Пример заполнения диалогового окна МОБР

5. Если обратная матрица не появилась в диапазоне А5:С7, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне А5:С7 появится обратная матрица:

 1 −1 0 


 0,5 0 − 0,5 
 .

− 0,333330,333330,33333

Сложение и вычитание матриц

Складывать (вычитать) можно матрицы одного размера. Суммой матриц А = (aij
)
и B= (bij
)
размера m
× n
называется матрица С = А + В, элементы которой сij
= aij
+ bij
, для i = 1,2, ..., m; j = 1, 2, ..., n
(то есть матрицы складываются поэлементно). Например, если:

1 2 7 

A
=9 −1 13; (1)



0 − 4 −3 

В
= 5 19 31; (2) то 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
(предварительно установив английскую раскладку клавиатуры).

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
, элементы которой bij
= kaij
для i - 1, 2, ..., m; j = 1,2,..., п
. Иначе говоря, при умножении матрицы на постоянную каждый элемент этой матрицы умножается на эту постоянную: k*Aij
= (k*aij
).

Например, для матриц A
и B
из предыдущего параграфа:

1 0,5 1 3,5 

A
= 4,5 − 0,5 6,5;

 0 8 6 

− 2∗ B
= −10 − 38 − 62.

В частности, произведений матрицы А на Число 0 есть нулевая матрица, то есть 0хЛ = 0.

В MS Excel для выполнения операции умножения матрицы на число могут быть использованы формулы, вводимые в соответствующие ячейки.

Пример.
Пусть, как и в предыдущем параграфе матрица A
введена в диапазоны А1:С2. Необходимо получить матрицу С = 3
х A
.

Решение

1. Табличный курсор поставьте в левый верхний угол результирующей матрицы, например в El
.

2. Введите формулу для вычисления первого элемента результирующей матрицы =3*А1 (предварительно установив английскую раскладку клавиатуры).

3. Скопируйте введенную формулу в остальные ячейки результирующей матрицы: поставьте табличный курсор в ячейку Е1; наведите указатель мыши на точку в правом нижнем углу ячейки, так чтобы указатель мыши принял вид тонкого крестика; при нажатой левой кнопке мыши протяните указатель до ячейки G1; таким же образом протяните указатель мыши до ячейки G2
.

В результате в ячейках E1:G2
появится матрица, равная исходной матрице, умноженной на постоянную —3.

3 6 21

3∗ A
=27 − 33 9 .

Умножение матриц

Произведение матриц определено, если число столбцов первой матрицы равно числу строк второй.

Пусть A = (aij
)
m
× n , B= (bij
)
n
× p ,
тогда размерность произведения А
х В
равна m
× p
.

При этом матрица С (размера m
× p
) называется произведением матриц A и В, если каждый ее элемент сij
, равен сумме произведений элементов i-й
строки матрицы А
на соответствующие элементы j-гo
столбца матрицы В
:

c
ij
= a
i
1b
1j
+ a
i
2b
2 j
+K+ a
ip
b
pj
=∑p
a
ik
b
kj
, i
=1,2,K,m
; j
=1,2,K,n
.

k
=1

Таким образом, перемножение матриц осуществляется по следующему правилу:

1стр
∗1стб


2стр
∗1стб

C
= A
∗B
= KKKKK 
∗1стб

mстр

1стр
∗ 2стб
K 1стр
∗ pстб
 2стр
∗ 2стб
K 2стр
∗ pстб

 .

KKKKKKKKKK  mстр
∗2стб
Kmстр
∗ 2стб



Пусть, например,

1 3 

C
= A
∗B
=
13 32 40 −21
∗2 2 =

10 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 раз

Для нахождения произведения двух матриц в Excel используется функция МУМНОЖ, которая вычисляет произведение матриц (матрицы хранятся в массивах).

Функция имеет вид МУМНОЖ(массив1,массив2
). Здесь массив1
и массив2
— это перемножаемые массивы. При этом количество столбцов аргумента массив 1 должно быть таким же, как количество строк аргумента массив2, и оба массива должны содержать только числа. Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Массив С
, который является произведением двух массивов А и В, определяется следующим образом:C
= (
∑a
ij
b
ij
), где i
— номер строки, 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.

Рис. 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 (указателем мыши при нажатой левой кнопке).

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

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 ;

 

2

D
=[2 1 1 0].

9. Вычислите: (А х В) х С; А х (В х С).

1 0

; B

A
=13 21 20 =1 1; C
=13 01 −21 10.

  2 −1

10. Покажите вычислением, что для указанных матриц верно утверждение: (A + B)C = AC + BC.

0 3 1 0

A
=
1−112
; B
=
1 1
; C
=
1
3 0
1 −12
01

.

2 −1

11. Вычислите:

1 2 

1) A
* B
= 3 46 * 10 − 32 24;

5

1 2  0 − 2 4 
3 4
.

2) B
* A
= 1 3 2 * 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 

Решение систем линейных уравнений

Многие прикладные задачи в технике, экономике и других областях сводятся к решению системы линейных уравнений, поэтому особенно важно уметь их решать.

Система n линейных уравнений с n неизвестными.

Две системы уравнений являются равносильными или эквивалентными
, если они имеют одно и то же множество решений. Система, равносильная данной может быть получена с помощью элементарных преобразований системы.

Систему можно также записать в виде матричного уравнения;

А
х Х = В,

где А
— матрица коэффициентов при переменных, или мат-

рица системы:

a
11 a
12 K a
1n


A
= Ka
21 a
K22 KK Ka
2n
;

 

a
m
1 a
m
2K a
nn


X — матрица-столбец (вектор) неизвестных:

x
1


X
= Kx
2;

 

x
n


В — матрица-столбец (вектор) свободных членов: b
1
 B
= b
2 .

 

b
n


В развернутом виде систему можно представить следующим образом:

a
11 a
12 K a
1n
 x
1  b
1 

a
21
a
22
K a
2n
 * x
2
 = b
2
.



K KK K  K K

     

a
m
1 a
m
2 K a
nn
 x
n
 b
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) необходимо найти обратную матрицу коэффициентов и умножить ее справа на вектор свободных членов.

Пример.
Пусть необходимо решить систему

3x
+ 2y
= 7


4x
− 5y
= 40. Решение

1. Введите матрицу А (в данном случае размера 2 х 2) в диапазон А1:В2

3 2

A
=4 − 5.

Вектор В = (7 40) введите в диапазон С1:С2.

2. Найдите обратную матрицу А-1
. Для этого:

• выделите блок ячеек под обратную матрицу. Например, выделите блок АЗ:В4 (указателем мыши при нажатой левой кнопке);

• нажмите на панели инструментов Стандартная кнопку

Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МОБР. После этого щелкните на кнопке 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.

В результате в диапазоне СЗ:С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	Расход ингредиентов на 1 день (у. е.)
S1	5	3	4	2700
S2	2	1	1	800
S3	3	2	2	1600

Нужно найти ежедневный объем выпуска фирменных блюд каждого вида.

Решение.
Пусть ежедневно ресторан выпускает х1
блюд вида В1, х2
блюд вида В2 и х3
— блюд вида ВЗ. Тогда в соответствии с расходом ингредиентов каждого типа имеем систему:

3729;5x
1
+ 3x
2
+ 4 x
3
= 2700 ,

2 x
1 + x
2 + x
3 = 900 ,


3x
1
+ 2 x
2
+ 2 x
3
= 1600 .

Решаем систему:

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 (указателем мыши при нажатой левой кнопке);

• нажмите на панели инструментов Стандартная кнопку Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите пункт Математические, а в рабочем поле Функция — имя функции МУМНОЖ. Щелкните на кнопке 0К;

• появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходных матриц и введите диапазон обратной матрицы А'1 — АФ.С6 в рабочее поле Массив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы В — D1.D3 введите в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

• если вектор X не появился в диапазоне D4:D6, следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне D4:D6 появится вектор X (рис. 14).

Рис. 14. Результаты вычислений из предыдущего примера

Причем х1
= 200
будет находиться в ячейке D4, х2
= 300
— в ячейке D5, а х3
= 200
в ячейке D6.

Рекомендуется сделать проверку, подставив найденные значения в уравнение системы. Для этого найденный вектор X необходимо подставить в исходное матричное уравнение А
х X = В
. При проверке используется тот же способ, что и для решения предыдущего примера.

УПРАЖНЕНИЯ

Решите самостоятельно следующие примеры.

x
1
−x
2
+x
3
=3

1. 2x
1x
++x
2x
2++2xx
33 ==118

x
1
+ 2x
2
+ 3x
3
− 2x
4
= 6 
2x
1
+ 4x
2
− 2x
3
− 3x
4
=18



2. 3x
1
+ 2x
2
−x
3
+ 2x
4
= 4

2x
1
−3x
2
+ 2x
3
+ x
4
=−8

2x
− 3y
+ z
− 2 = 0



3. x
+ 5y
− 4z
+ 5 = 0


4x
+ y
− 3z
+ 4 = 0

4. 
32xx
+++23yyy
+++34zzz
===1511 

32xxx
−−+223yyy
+−−354zzz
===6620

2x



4x
− 3y
+ 2z
= 9 x
+ y
+ 2z
=−1

5. 2x
+ 5y
− 3z
= 4 2x
− y
+ 2z
=−4 
5x
+ 6y
− 2z
=18 
4x
+ y
+ 4z
=−2

2x
− y
−z
= 4 3x
+ 4y
+ 2z
=8

6. 3x
+ 4y
− 2z
=11 2x
− y
− 3z
=−4


3x
− 2y
+ 4z
=11 
x
+ 5y
+ z
= 0

x
+ y
− z
= 1 x
1
− 4x
2
− 2x
3
=−3



7. 8x
+ 3y
− 6z
= 2 3x
1 + x
2 + x
3 = 5


4x
+ y
− 3z
= 3 
3x
1
−5x
2
− 6x
3
=−9



4x
1
+7x
2
−3x
3
=−10 −3x
1
+ 6x
2
+8x
3
=−7

8. 2x
1 +9x
2 − x
3 =8 9x
1 −11x
2 −14x
3 =−15


x
1
−6x
2
+3x
3
=−3 
18x
1
− 22x
2
−30x
3
=−20

Система m линейных уравнений с n неизвестными

Система m линейных уравнений с n неизвестными имеет вид:

a
11x
1 +a
12x
2 +K+a
1n
x
n
=b
1 a
21x
1 +a
22x
2 +K+a
2n
x
n
=b
2 a
m
1x
1 +a
m
2x
2 +K+a
mn
x
n
=b
m

Она может быть представлена в матричном виде: A
x X = B
.

Однако при применении компьютера удобнее использовать более общий подход — метод наименьших квадратов. Для этого обе части матричного уравнения системы умножаем слева на транспонированную матрицу системы АТ
.

AT
AX = AT
B.

Затем обе части уравнения умножаем слева на матрицу (АТ
А-1
) Если эта матрица существует, то система определена. С учетом того, что (АТ
А-1
) * (АТ
А) = Е, получаем X = (AT
A )-1
AT
B
.

Матричное уравнение является решением системы m линейных уравнений с n неизвестными при m > n.

Пример.
Пусть необходимо решить систему

3x
+ 2y
= 7


4x
− 5y
= 40 
3x
+ 3y
= 3. Решение

1. Введите матрицу А
(в данном случае размера 3 х 2) в диапазон А1:ВЗ

3 2

A
= 
4 − 5
 ,

3 3 

Вектор В = (7 40 3) введите в диапазон С1:СЗ.

2. Найдите транспонированную матрицу АТ
. Для этого:

• выделите блок ячеек под транспонированную матрицу. Его размер в примере будет 2x3. Например, выделите блок А4:С5

(указателем мыши при нажатой левой кнопке);

• нажмите на панели инструментов Стандартная кнопку

Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Функция — имя функции ТРАНСП. Щелкните на кнопке О

К;

• появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:ВЗ в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

если обратная матрица не появилась в диапазоне АЗ:В4, то следует щелкнуть указателем мыши в Строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне А4:С5 появится транспонированная матрица АТ
.

3. Найдите произведение AT
х В. Для этого:

• выделите блок ячеек под результирующую матрицу (под вектор AT
В). Ее размерность будет n х 1, в данном примере 2x1. Например, выделите блок ячеек Е4:Е5 (указателем мыши при нажатой левой кнопке);

• нажмите на панели инструментов Стандартная кнопку

Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Математические, а в рабочем поле Функция — имя функции МУМНОЖ. Щелкните на кнопке 0К;

• появившееся диалоговое окно МУМНОЖ мышью отодвиньте в сторону от исходных матриц и введите диапазон транспонированной матрицы AT
— А4:С5 в рабочее поле Массив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы В — С1:СЗ — в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

• если вектор АТВ не появился в диапазоне Е4:Е5, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне Е4:Е5 появится вектор

T
190 

A B
= − 177 .

Аналогично находим произведение AT
х А:

• выделите блок ячеек под результирующую матрицу AT
А. Ее размерность будет n
×n
, в данном примере 2x2. Например, выделите блок ячеек А7:В8
(указателем мыши при нажатой левой кнопке);

• далее действуйте так, как описано в пункте 3, указывая соответствующие диапазоны.

В результате в диапазоне А7:В8
появится матрица AT
А:

34 −5

−5 38.

4. Находим обратную матрицу (AT
А)-1
. Для этого необходимо:

выделить блок ячеек под обратную матрицу. Например, блок А1О:В11
(указателем мыши при нажатой левой кнопке);

• нажать на панели инструментов Стандартная кнопку

Вставка функции;

• в появившемся диалоговом окне Мастер функций в рабочем поле Категория выбрать пункт Математические, а в рабочем поле Функция — имя функции МОБР. Затем щелкнуть на кнопке ОК;

• появившееся диалоговое окно МОБР мышью отодвинуть в сторону от исходной матрицы и ввести диапазон исходной матрицы AT
А — А7:В8 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После этого нажать сочетание клавиш CTRL+SHIFT+ENTER;

• если обратная матрица не появилась в диапазоне А1О:В11
, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне А10:В11
появится обратная матрица (AT
А)-1

0,029992 0,003946


0,003946 0,026835
.

5. Теперь умножением обратной матрицы (AT
А)-1
на вектор AT
В находим вектор X.

Для этого:

• выделите блок ячеек под результирующую матрицу (под вектор X). Ее размерность будет n х 1, в данном примере 2x1. Например, выделите блок ячеек Dl:D2 (указателем мыши при нажатой левой кнопке);

• нажмите на панели инструментов Стандартная кнопку

Вставка функции;

• появившееся диалоговое окно МУМ НОЖ мышью отодвиньте в сторону от исходных матриц и введите диапазон обратной матрицы (АТА)~1 — А10.В11 в рабочее поле Массив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы АТВ — Е4.Е5 — в рабочее поле Массив2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

если вектор X не появился в диапазоне D1:D2, то следует щелкнуть указателем мыши в Строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне D1:D2 появится вектор X .Причем х = 5
будет находиться в ячейке D1, а у = - 4
— в ячейке D2.

Рис. 15. Результаты вычислений из предыдущего примера

Можно осуществить проверку найденного решения. Для этого найденный вектор X необходимо подставить в исходное матричное уравнение A
x X = B.

Причем достаточно подставить X в любые n уравнений.

УПРАЖНЕНИЯ

1. Решите систему:

8x
+ 5y
=10


5x
+ 2y
= 4


7x
+ 4y
= 8

2. Решите систему:

2x
− 4y
+ 3 =1


3x
− y
+ 5z
= 2


x
− 2y
+ 4z
= 3

6x
− 2y
=10z
= 4

3. Решите систему:

2x
− y
− z
=1


x
+ 2y
− 3x
= 5


4x
− 2y
− 2z
= 2

x
+ 3y
+ 4z
= 6

3. ЗАДАЧИ ОПТИМИЗАЦИИ

Задачи оптимизации называют экстремальными задачами. Их решение сопряжено с большим количеством вычислений. Рассмотрим некоторые типы задач оптимизации: решение уравнений с одним неизвестным, задачи линейного программирования и аппроксимацию функций.

Постановка задачи оптимизации

В задачах оптимизации требуется найти значения параметров или функций, реализующих максимум или минимум некоторой зависящей от них величины, например:

z=f(xl
,x2
,...,xn
),
(3.1) часто при дополнительных условиях-неравенствах:

j
i
(x
1
,x
2
,...,x
n
)≤ 0
(i
=1,2,...,m
) (3.2)

В инженерных и экономических задачах желательно найти максимум меры выполнения или минимум стоимости.

Другим приложением задач оптимизации является получение приближенных решений выбором неизвестных значений параметров или функций так, чтобы они давали минимум ошибки.

В простейшем случае одной независимой переменной х
локальные максимум и минимум функции определяются следующим образом. Действительная функция f(x),
определенная при х= а,
имеет в точке а
(локальный) минимум или (локальный) максимум f
(а), если существует такое положительное число d
, что при всех ∆x
= x
− a,
для которых выполняются неравенства

0〈∆x
〈d
и существует значение f
(a
+∆x
), соответственно

∆f
≡ f
(a
+∆x
)− f
(a
)〈0 или

∆f
≡ f
(a
+∆x
)− f
(a
)〉0

Максимум и минимум функции - это экстремум функции
. Определение локальный подчеркивает тот факт, что понятие экстремума связано лишь с достаточно малой окрестностью точки а. При решении оптимизационных задач важно нахождение не локальных экстремумов, а глобального максимума или глобального минимума (наибольшего или наименьшего значений) функции на промежутке X.

Для поиска экстремумов существуют различные методы. Часто случается, что при поиске максимумов и минимумов функций многих переменных получают сложную систему уравнений, в этих случаях экстремумы находятся численными методами, то есть при помощи последовательного применения метода проб. При этом применение компьютера является практически единственным способом решения задачи.

Решение уравнений с одним неизвестным

Одним из приложений задач оптимизации является численное решение систем уравнений с одним или несколькими неизвестными вида:

f
(x)= 0. ( 3.3)

Нахождение корней уравнения вида (3.3) даже в случае алгебраических уравнений выше третьей степени представляет достаточно сложную задачу. Трансцендентные же уравнения чаще всего вообще не имеют аналитического решения. В этих случаях единственным путем является получение приближенных решений, выбором неизвестных значений параметров так, чтобы они давали минимум ошибки некоторой целевой функции (как правило, квадратичной). Обычно используются итерационные методы, когда вначале выбирают некоторое начальное приближение х0
затем вычисляют последовательные приближения x
[j
+1
]
= j
(x
[j
]
)(j
= 0,1,2,...)

Итерационные методы обеспечивают сходимость таких приближений к искомому значению х.

В MS Excel для решения уравнений вида (3.3) используется удобный и простой для понимания инструмент Подбор параметра
. Он реализует алгоритм численного решения уравнения, зависящего от одной переменной.

Процесс решения с помощью процедуры Подбор параметра
распадается на два этапа:

1 . Задание на рабочем листе ячейки, содержащей переменную решаемого уравнения (так называемой влияющей ячейки), и ячейки содержащей формулу уравнения (зависящей или целевой ячейки).

2. Ввод адресов влияющей и целевой ячеек в диалоговом окне Подбор параметра
и получение ответа (или сообщения о его отсутствии или невозможности нахождения, поскольку уравнение может не иметь решений или алгоритм решения (оптимизации) может оказаться расходящимся в конкретных условиях).

С помощью процедуры Подбор параметра
могут быть решены только некоторые типы уравнений. Пример.
Найти решение уравнения ln x
= 0

Решение Первый этап

1. Открываем новый рабочий лист (команда Вставка >
Лист).

2. Заносим в ячейку А1 ориентировочное значение корня, например, 3.

3. Заносим в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Для этого нажимаем на панели инструментов Стандартная
кнопку Вставка функции
; в появившемся диалоговом окне Мастер функций
в рабочем поле Категория
выбираем Математические
, а в рабочем поле Функция
имя функции LN. После чего щелкаем на кнопке ОК; появившееся диалоговое окно LN мышью отодвигаем в сторону от ячейки А1 и в рабочее поле Число
щелчком мыши на ячейке А1 вводим ее адрес. После чего нажимаем на кнопку ОК.

В ячейке В1 появляется число 1,098612.

Второй этап

1. Вызываем процедуру Подбор параметра
(команда Сервис > Подбор параметра).

2. В поле Установить
в ячейке мышью указываем В1, в поле Значение с клавиатуры задаем 0
(правая часть уравнения), в поле Изменяя
значение ячейки мышью указываем Л У (рис. 16).

Рис. 16. Пример заполнения диалогового окна Подбор параметра

3. Щелкаем на кнопке ОК и получаем результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкаем на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции. В ячейке А1 получаем приближенное значение х
= 0,999872
(рис. 17). При этом погрешность решения (значение правой части уравнения) — вместо 0
в ячейке В1 получаем -0,00013.

Рис. 17. Результаты вычислений

Таким образом, при значении х =
0,999872 правая часть уравнения lnx
= 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 получаем приближенное значение xi
= 2,000048.
При этом точность решения (значение правой части уравнения) — вместо 0
в ячейке В1 получаем 4,85Е-05 (0,0000485).

6. Повторяем расчет для второго корня х2
,
задавая в ячейке А1 другое начальное значение, например —3.
Получаем значение второго корня уравнения х2
= 0,9996.

УПРАЖНЕНИЯ

1. Решить уравнение cosx
= 0 в диапазоне х
є [0; 2].

2. Решить уравнение2x
2
−3x
2
+1 = 0 3. Решить уравнение х3
- Зх2
+ х =
0.

Линейное программирование

В случае, когда оптимизируемая целевая функция (3.1) и ограничения (3.2) линейны, задача оптимизации решается методами линейного программирования и обычно называется задачей линейного программирования. Задача линейного про-

граммирования заключается в нахождении г переменных xl
, x2
..., х
1
,
минимизирующих данную линейную функцию (целевую функцию):

Z
=f
(x
1
,x
2
,...,x
r
)≡c
1
x
1
+c
2
x
2
+...+c
r
x
r
(3.4)

(или максимизирующую — Z) при линейных ограничениях-равенствах:

a
i
1
x
1
+ a
i
2
+ x
2
+...+ a
ir
x
r
= A
i
, где i
=1,2,...,n
(3.5)

и линейных ограничениях-неравенствах:

A
j
1
x
1
+ A
j
2
x
2
+...+ A
jr
x
r
≥ B
j
,где j=1,2,…,m
(3.6)

Задачу линейного программирования (3.4-3.6) сводят путем введения вспомогательных переменных к стандартной форме (основной задаче линейного программирования). При этом требуется минимизировать целевую функцию:

Z
= f
(x
1
,x
2
,...,x
n
) ≡ c
1
x
1
+ c
2
x
2
+...+ c
n
x
n
(3.7)

при т < п
линейных ограничениях-равенствах

ai
1
x
1
+ai
2
x
2
+...+ain
xn
=bi
, где
i
=1,2,...,m
(3.8)

и n
линейных ограничениях-неравенствах

x
k
≥ 0 , где k
=1,2, ...,п.
(3.9) Допустимым решением (планом) задачи линейного программирования является упорядоченное множество чисел (
x
1
,
x
2
, ...,
x
n
),
удовлетворяющих ограничениям (3.8) и (3.9). Это точка в n
-мерном пространстве. Допустимое решение, минимизирующее целевую функцию (3.7), называется оптимальным решением (оптимальным планом).

Чаще всего оптимальное решение, если оно существует, является и единственным. Однако возможны случаи, когда оптимальных решений бесчисленное множество.

Процесс решения задачи линейного программирования обычно состоит из ряда этапов:

1-й этап

: осмысление задачи, выделение наиболее важных качеств, свойств, величин, параметров. Это можно делать, составляя схемы, таблицы, графики и т.п.;

2-й этап

: введение обозначений (неизвестных). Желательно ограничиваться как можно меньшим количеством неизвестных, выражая по возможности одни величины через другие;

3-й этап

: создание целевой функции. Обычно в качестве цели могут выступать максимальная стоимость всего объема продукции, максимальная прибыль, минимальные затраты и т. п. Целевая функция записывается в виде (3.4) или (3.7).

4-й этап

: составление системы ограничений, которым должны удовлетворять введенные величины (3.5), (3.6) или (3.8),

(3.9).

5-й этап

: решение задачи на компьютере.

Инструментом для поиска решений задач оптимизации в Excel служит процедура Поиск решения (Сервис >
Поиск решения). При этом открывается диалоговое окно Поиск решения.

Оно содержит следующие рабочие поля:

Установить целевую ячейку
— служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу:

Равной
— служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, необходимо ввести его в поле;

Изменяя ячейки
— служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку;

Предположить
— используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки;

Ограничения
— служит для отображения списка граничных условий поставленной задачи;

Добавить
— используется для отображения диалогового окна Добавить ограничение;

Изменить
— применяется для отображения диалогового окна Изменить ограничение;

Удалить
— служит для снятия указанного ограничения;

Выполнить
— используется для запуска поиска решения поставленной задачи;

Закрыть
— служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки, сделанные в окнах диа-. лога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить;

Параметры
— применяется для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения;

Восстановить
— служит для очистки полей окна диалога и восстановления значений параметров поиска решения, используемых по умолчанию.

Рассмотрим примеры решения задач оптимизации.

Пример.
В ресторане готовятся фирменные блюда трех видов (блюдо А, блюдо В и блюдо С) с использованием при приготовлении ингредиентов трех видов (ингредиент 1, ингредиент 2 и ингредиент 3). Расход ингредиентов в граммах на блюдо задается следующей таблицей:

Вид ингредиента	Блюдо А Блюдо В Блюдо С
Ингредиент 1	20 50 10
Ингредиент 2	20 0 40
Ингредиент 3	20 10 10

Стоимость приготовления блюд одинакова (100 руб.).

Ежедневно в ресторан поступает 5 кг ингредиента 1 и по 4 кг ингредиентов видов 2 и 3. Каково оптимальное соотношение дневного производства блюд различного вида, если производственные мощности ресторана позволяют использовать весь запас поступивших продуктов?

Решение.
Для решения задачи введем обозначения: пусть xl
—
дневной выпуск блюда А; х2
—
дневной выпуск блюда В; х3
—
дневной выпуск блюда С.

Составим целевую функцию — она заключается в стоимости выпущенных рестораном блюд:

Z= 100 × x1
+
100×х2
+
100 ×х3
.

Определим имеющиеся ограничения (руководствуясь таблицей):

1. 20×x1
+ 50×х2

+10×х3
≤ 5000; 2. 20×x1
+ 0×х2

+40×х3
≤ 4000;

3. 20×x1
+ 10×х2

+10×х3
≤ 4000.

Кроме того, поскольку нельзя реализовать часть блюда и количество блюд не может быть отрицательным, добавим еще ряд ограничений:

1. x ≥ 0;

;

3. x ≥ 0; целое;

5. х2
—
целое;

6. х3
—
целое.

Теперь можно приступить к решению задачи на компьютере.

1. Откроем новый рабочий лист (Вставка > Лист).

2. В ячейки А2, A3 и А4 занесем дневной запас продуктов — числа 5000,4000
и 4000
соответственно.

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. Пример заполнения диалогового окна Поиск решения

9. В поле Изменяя ячейки мышью зададим диапазон подбираемых параметров (неизвестных x
i
)
— С1:Е1.

10. Чтобы определить набор ограничений, щелкнем на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку мышью укажем диапазон В2:В4.
В качестве условия зададим <=. В поле Ограничение мышью зададим диапазон А2:А4
(рис. 20). Это условие указывает, что дневной расход ингредиентов не должен превосходить запасов. Щелкнем на кнопке ОК.

Рис.20. Пример заполнения диалогового окна Добавление ограничения

11. Снова щелкнем на кнопке Добавить. В поле Ссылка на ячейку укажем диапазон С1:Е1.
В качестве условия зададим >=. В поле Ограничение зададим число 0.
Это условие указывает, что число приготавливаемых блюд неотрицательно. Щелкнем на кнопке ОК.

12. Снова щелкнем на кнопке Добавить. В поле Ссылка на ячейку укажем диапазон С1:Е1.
В качестве условия выберем пункт цел. Это условие не позволяет производить доли блюд.

Щелкнем на кнопке ОК.

13. Щелкнем на кнопке Выполнить. По завершении оптимизации откроется диалоговое окно Результаты поиска решения.

14. Установим переключатель Значения параметров в положение Сохранить найденное решение, после чего щелкнем на кнопке ОК.

В результате получится оптимальный набор переменных (оптимальное количество приготавливаемых фирменных блюд) при данных ограничениях (при данном количестве ингредиентов): блюда А — 184 порции (х1
),
блюда В — 24 порции (х2
)
и блюда С — 8 порций (х3
).
При этом общая стоимость блюд (Z) будет максимальной и равной 21 600 руб. При этом останутся неизрасходованными 40 г первого ингредиента (рис. 21).

Рис. 21. Результат вычислений из примера

Проанализируем полученное решение. Проверить его оптимальность можно, экспериментируя со значениями ячеек С1:Е1. Например, допустим, что решили приготовить количества блюд, соответственно 184, 23, 9. Тогда при той же общей стоимости блюд будет перерасход второго ингредиента на 40 г, что, естественно, недопустимо. Можно рассмотреть и другие варианты. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения.

Пример.
Туристская фирма заключила контракт с двумя турбазами: в г. Сухуми и в окрестных городах, рассчитанных, соответственно, на 200 и 150 человек.

Туристам для осмотра предлагается обезьяний питомник в городе, ботанический сад и поход в горы. Составьте маршрут движения туристов так, чтобы это обошлось возможно дешевле, если:

Обезьяний питомник принимает в день 70 человек, ботанический сад — 180 человек, а в горы в один день могут пойти 110 человек;

Стоимость одного посещения выражается таблицей:

Турбаза ОП БС Поход

1 5 6 20

2 10 12 5

Решение.
Для решения задачи введем обозначения: пусть x1
— число туристов из турбазы в Сухуми, посещающих обезьяний питомник; х2
—
число туристов из турбазы в Сухуми, посещающих ботанический сад; х3
—
число туристов из турбазы в Сухуми, отправляющихся в поход; x
4
— число туристов из окрестной турбазы, посещающих обезьяний питомник; х5
—
число туристов из окрестной турбазы, посещающих ботанический сад; х6
—
число туристов из окрестной турбазы, отправляющихся в поход. Составим целевую функцию — она заключается в минимизации стоимости дневных мероприятий турфирмы:

Z=5 xl
+ 6×х2
+
20×x
3
+ 10 ×x
4
+ 12 ×х5
+
5 ×х6
.

Определим имеющиеся ограничения (руководствуясь условиями задачи):

1.x
1
+ x
4
≤ 70

2.x
2
+ x
5
≤ 180

3.x
3
+ x
6
≤ 110

4.x
1
+ x
2
+ x
3
= 200

5.x
4
+ x
5
+ x
6
= 150

Кроме того, поскольку турист неделим и количество туристов, участвующих в каждом мероприятии, не может быть отрицательным, добавим еще ряд ограничений:

1)x
1
≥ 0 ;

2)
х
≥ 0 ;

3)
0;

4)
х4
≥
0;

5)
х5
≥ 0;

6)
х6
≥ 0;

7)
xl
—
целое;

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
(нули) — в дальнейшем значения этих ячеек будут подобраны автоматически.

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.
Это условие указывает, что дневное количество посетителей мероприятий должно быть равно количеству туристов.

Щелкните на кнопке ОК.

13. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Н1.
В качестве условия задайте >=. В поле Ограничение задайте число 0.
Это условие указывает, что число участников мероприятий неотрицательно. Щелкните на кнопке ОК.

14. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Н1,
В качестве условия выберите пункт цел. Это условие указывает, что турист неделим. Щелкните на кнопке ОК.

15. Щелкните на кнопке Выполнить. По завершении оптимизации откроется диалоговое окно Результаты поиска решения.

16. Установите переключатель Сохранить найденное решение, после чего щелкните на кнопке ОК.

В результате получится оптимальный набор переменных (оптимальное количество туристов для участия в каждом мероприятии из каждой гостиницы) при данных ограничениях (при заданных возможностях мероприятий): число туристов из турбазы в Сухуми, посещающих обезьяний питомник (xl

= 30), ботанический сад (х2
=
170) и отправляющихся в поход (х3
= 0); число туристов из окрестной турбазы, посещающих обезьяний питомник (х4

= 40), посещающих ботанический сад (х5

= 0) и отправляющихся в поход (хъ
-
110). При этом суммарные расходы турфирмы (Z) составят 2120 руб. и будут минимальными.

Можно проанализировать полученное решение. Его оптимальность проверяется путем эксперимента со значениями ячеек С1:Н1. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения.

Другими типовыми примерами задач линейного программирования являются задачи:

1. О рационе питания.

2. Об оптимальных перевозках.

3. Об оптимальном плане пошивочной мастерской.

4. О рациональном использовании сырья.

Рассмотрим эти задачи в качестве упражнений.

УПРАЖНЕНИЯ

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 единицы сырья; 4 единицы материалов и 1 человеко-день; второй продукции — соответственно, 2, 3 и 3. Общие объемы ресурсов:

• фонд рабочего времени — 12;

• фонд сырья — 16;

• фонд материалов — 9;

• цена 1 единицы сырья —-1 денежная единица;

• цена материалов — 3 денежных единицы.

Проанализируйте математическую постановку этой задачи; как увеличить стоимость всей продукции, если можно привлечь дополнительные ресурсы, лишние продавать?

5. Составьте оптимальный план производства, чтобы стоимость всей продукции была максимальной, если:

Продукция	Стоимость 1 ед. продукции	Норма расходов ресурсов
Продукция	Стоимость 1 ед. продукции	Трудовых	Сырьевых	Материалов
1	40	6	8	6
2	30	5	7	5

Общие объемы ресурсов:

• трудовых — 48;

• сырьевых — 56;

• материалов — 72;

• цена одной единицы сырья — 2 денежные единицы;

• материалов — 1,5 денежные единицы.

Проанализируйте составленный оптимальный план: как можно увеличить стоимость всей продукции, если исходить из возможности свободно распоряжаться ресурсами.

Аппроксимация экспериментальных данных

Аппроксимацией

называется процесс подбора эмпирической формулы φ(х)
для установленной из опыта функциональной зависимости y=f(x).
Эмпирические формулы служат для аналитического представления опытных данных.

Одна независимая переменная

Обычно задача аппроксимации распадается на две части:

1. Устанавливают вид зависимости y=f(x)
и вид эмпирической формулы, то есть решают, является ли она линейной, квадратичной, логарифмической или какой-либо другой.

2. Определяются численные значения неизвестных параметров выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим.

Если нет каких-либо теоретических соображений для подбора вида формулы, обычно выбирают функциональную зависимость из числа наиболее простых, сравнивая их графики с графиком заданной функции. После выбора вида формулы определяют ее параметры. Для наилучшего выбора параметров задают меру близости аппроксимации экспериментальных данных. Если функция f(x)
задана графиком или таблицей (на дискретном множестве точек), для оценки степени приближения рассматривают разности f(х1
)
- φ(хi
) для точек х0,
х1,…,
хn
.
Существуют различные меры близости и, соответственно, способы решения этой задачи. Некоторые из них очень просты, быстро приводят к результату, но результат этот является сильно приближенным. Другие более точные, но и более сложные. Обычно определение параметров при известном виде зависимости осуществляют по методу наименьших квадратов

. При этом функция φ(x
) считается наилучшим приближением к f(х),
если для нее сумма квадратов невязок δi
, или отклонений «теоретических» значений φ(хi
), найденных по эмпирической формуле, от соответствующих опытных значений yi

Z
=∑n
[f
(x
i
) −j
(x
i
)
]2
→min (3.10)

i
=0

имеет наименьшее значение по сравнению с другими функ-

циями, из числа которых выбирается искомое приближение.

Используя методы дифференциального исчисления, метод наименьших квадратов формулирует аналитические условия достижения суммой квадратов отклонений (3.10) своего наименьшего значения. Так, если функция φ(х
) вполне определяется своими параметрами k, l, т, ...,
то наилучшие (в указанном смысле (3.10)) значения этих параметров находятся из решения системы уравнений. Например, в простейшем случае, когда функция φ(х
)представлена линейным уравнением у = ах + b,
система имеет вид:

 n n n

a
i
=1 i
=1 i
=1 x
i
* y
i


a
*∑i
=n
1 x
i
+b
*n
=
∑i
=n
1 y
i
(3.11)

В простейшем случае задача аппроксимации экспериментальных данных выглядит следующим образом.

Пусть есть какие-то данные, полученные практическим путем (в ходе эксперимента или наблюдения), которые можно представить парами чисел (х; у).
Зависимость между ними отражает таблица:

X x1
…… xn

Y y1 …… yn

На основе этих данных требуется подобрать функцию у
= φ(х
), которая наилучшим образом сглаживала бы экспериментальную зависимость между переменными и по возможности точно отражала общую тенденцию зависимости между х
и у,
исключая погрешности измерений и случайные отклонения. Это значит, что отклонения yi
- yi
(хi
) в каком-то смысле были бы наименьшими. Например, в смысле (3.10).

Выяснить вид функции можно либо из теоретических соображений, либо анализируя расположение точек (хn
;yn
) на координатной плоскости.

Например, пусть точки расположены так, как показано на рис. 22, учитывая то, что практические данные получены с некоторой погрешностью, обусловленной неточностью измерений, необходимостью округления результатов и т. п., естественно предположить, что здесь имеет место линейная зависимость у = ах + b.

Рис.22. Возможный вариант расположения экспериментальных точек

Чтобы функция приняла конкретный вид, необходимо каким-то образом вычислить а
и b.
Для этого можно решить систему (3.11).

Расположение экспериментальных точек в виде кривой на рис. 23 наводит на мысль, что зависимость обратно пропорциональна и функцию φ(х
)нужно подбирать в виде у = а + b/х.
Здесь также необходимо вычислить параметры а
и b.

Рис. 23. Другой вариант расположения экспериментальных точек

Таким образом, расположение экспериментальных точек может иметь самый различный вид, и каждому соответствует конкретный тип функции. Построение эмпирической функции сводится к вычислению входящих в нее параметров, так чтобы из всех функций такого вида выбрать ту, которая лучше других описывает зависимость между изучаемыми величинами. То есть сумма квадратов разности между табличными значениями функции в некоторых точках и значениями, вычисленными по полученной формуле, должна быть минимальна.

В MS Excel аппроксимация экспериментальных данных осуществляется путем построения их графика (х
— отвлеченные величины) или точечного графика (х —
имеет конкретные значения) с последующим подбором подходящей аппроксимирующей функции (линии тренда). Возможны следующие варианты функций:

1. Линейная
— у = ах+b.
Обычно применяется в простейших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью.

2. Полиномиальная
— y
= a
0
+ a
1
x
+ a
2
x
2
+...+ a
n
x
n
, где до шестого порядка включительно (п
≤6), аi

— константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой. Полином второй степени может описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени — не более трех экстремумов и т. д.

3. Логарифмическая
— у
= a
lnx + b,
где а
и b
— константы, In —функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются.

4. Степенная
— y
=bxa
,
где а
и b —
константы. Аппроксимация степенной функцией используется для экспериментальных данных с постоянно увеличивающейся (или убывающей) скоростью роста. Данные не должны иметь нулевых или отрицательных значений.

5. Экспоненциальная
—
y
=be
ax
,
где а
и b —
константы, е
— основание натурального логарифма. Применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются. Часто ее использование вытекает из теоретических соображений.

Степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом детерминации (R2
). Таким образом, если есть несколько подходящих вариантов типов аппроксимирующих функций, можно выбрать функцию с большим коэффициентом детерминации (стремящимся к 1).

Для осуществления аппроксимации на диаграмме экспериментальных данных необходимо щелчком правой кнопки мыши вызвать выплывающее контекстное меню и выбрать пункт Добавить линию тренда
. В появившемся диалоговом окне Линия тренда
на вкладке Тип
выбирается вид аппроксимирующей функции, а на вкладке Параметры
задаются дополнительные параметры, влияющие на отображение аппроксимирующей кривой. Пример.
Исследовать характер изменения с течением времени уровня производства некоторой продукции и подобрать аппроксимирующую функцию, располагая следующими данными:

Решение

1. Для построения диаграммы, прежде всего, необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Год.
Затем в ячейки А2:Аб последовательно вводим годы, начиная с 1997. Далее в ячейку В1 заносим слово Продукция
и устанавливаем табличный курсор в ячейку В2. Здесь должно оказаться значение 17,1
соответствующее значению года в ячейке А2.

Аналогично заполняем ячейки ВЗ:Вб.

2. Далее по введенным в рабочую таблицу данным необходимо построить диаграмму. Поскольку здесь необходимо строить динамику изменений производства продукции, не привязываясь к конкретному году, а от отвлеченных переменных — выберем диаграмму График.

Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм
. В появившемся диалоговом окне выбираем тип диаграммы График, вид — левый средний. После нажатия кнопки Далее указываем диапазон данных — В1:Вб (с помощью мыши). Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А6.
Нажав кнопку Далее, вводим название диаграммы — Производство продукции,
название осей X и У: Годы
и Условные единицы,
соответственно. Нажимаем кнопку Готово.

Получен график экспериментальных данных.

3. Осуществим аппроксимацию полученной кривой полиномиальной функцией второго порядка, поскольку кривая довольно гладкая и не сильно отличается от прямой линии. Для этого указатель мыши устанавливаем на одну из точек графика и щелкаем правой кнопкой. В появившемся контекстном меню выбираем пункт Добавить линию тренда. Появляется диалоговое окно Линия тренда (рис. 24).

Рис. 24. Вкладка Тип диалогового окна Линия тренда

В этом окне на вкладке Тип
выбираем тип линии тренда— Полиномиальная
и устанавливаем степень — 2.
Затем открываем вкладку Параметры (рис. 25) и устанавливаем флажки в поля показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^
2). После чего нужно щелкнуть на кнопке ОК.

В результате получим на диаграмме аппроксимирующую кривую (рис. 26).

Как видно из рисунка 26, уравнение наилучшей полиномиальной аппроксимирующей функции для некоторых отвлеченных значений х
(1, 2, 3, ...) выглядит как y
= −0,14x
2
+1,5x
+15,66.

При этом точность аппроксимации достаточно высока — R2

= 0,986.

Рис. 25. Вкладка Параметры диалогового окна Линия тренда

Рис.26. Экспериментальные данные, аппроксимированные полиномиальной кривой

4. Попробуем улучшить качество аппроксимации выбором другого типа функции (возможно более адекватного). Здесь возможным вариантом представляется логарифмическая функция. Для этого повторяем операции п. 3. за исключением того, что в окне Линия тренда на вкладке Тип выбираем тип линии тренда — Логарифмическая.

В результате получим другой вариант аппроксимации — логарифмической кривой (рис. 27).

Как можно видеть из рисунка 27, уравнение наилучшей логарифмической аппроксимирующей функции несколько уступает по точности аппроксимации полиномиальной кривой — R2
-
0,9716 < 0,986. Поэтому если нет каких-либо теоретических соображений, то можно считать, что наилучшей аппроксимацией является аппроксимация полиномиальной функцией второй степени (из двух рассмотренных вариантов).

Рис.27. Экспериментальные данные, аппроксимированные логарифмической кривой

Пример.
После выброса ядовитого вещества его концентрация (мг/л) в водоеме изменялась в соответствии со следующей таблицей:

Определить вид функциональной зависимости изменения концентрации вещества от времени и оценить его концентрацию в водоеме в момент выброса.

Решение

1. Для построения диаграммы, прежде всего, необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Время.
Затем в ячейки А2:А5 последовательно вводим время: 1,3,5, 8.
Далее в ячейку В1 заносим слово Концентрация
и в диапазон В2:В5 вводим соответствующие концентрации вещества.

2. Далее по введенным в рабочую таблицу данным необходимо построить диаграмму. Поскольку здесь необходимо строить динамику изменений концентрации вещества в соответствии с изменениями времени — будем строить диаграмму Точечная.

Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы Точечная, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В5 (с помощью мыши). Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А5.
Нажав кнопку Далее, вводим название диаграммы — Концентрация вещества,
название осей X и У: Время
и Концентрация,
соответственно. Нажимаем кнопку Готово.

Получен график экспериментальных данных.

3. Осуществим аппроксимацию полученной кривой. Поскольку кривая напоминает экспоненту и из теоретических соображений наиболее вероятный закон изменения — экспоненциальный, целесообразно аппроксимировать кривую .изменения концентрации экспоненциальной функцией. Для этого указатель мыши устанавливаем на одну из точек графика и щелкаем правой кнопкой. В появившемся контекстном меню выбираем пункт Добавить линию тренда. Появляется диалоговое окно Линия тренда.

В этом окне на вкладке Тип выбираем тип линии тренда — Экспоненциальная. Затем открываем вкладку Параметры и устанавливаем флажки в поля показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (RA
2), Кроме этого, для того, чтобы оценить концентрацию вещества в водоеме в момент выброса в поле Прогноз назад на устанавливаем 1 периодов.
После чего щелкаем на кнопке ОК. В результате получим на диаграмме аппроксимирующую кривую

(рис. 28).

Рис.28. Экспериментальные данные, аппроксимированные экспоненциальной функцией

Как видно из рисунка 28, уравнение наилучшей экспоненциальной аппроксимирующей функции для зависимости концентрации от времени выглядит как

y
=11,844e
−
0,4695x
(3.12)

При этом точность аппроксимации очень высокая — R1

= 0,9951, что позволяет считать описание процесса изменения концентрации вещества в водоеме экспоненциальной функцией адекватным. Расчетная оценка концентрации вещества в момент выброса, как видно из графика, составляет около 12 мг/л. Более точные цифры могут быть получены из уравнения (3.12) при х =
0 (y0
= 11,84 мг/л).

УПРАЖНЕНИЯ

1 Построить функцию, наилучшим образом отражающую данную зависимость:

2. В 80-е годы уровень дефицита бюджета в СССР и США складывался следующим образом:

Построить функции, наилучшим образом отражающие зависимости дефицита бюджета от времени в обеих странах.

3. Количество вложенных в производство средств и полученная в результате прибыль соотносятся следующим образом:

Запишите аналитическую зависимость между х
и у.
Проанализируйте полученный ответ. Каковы перспективы предприятия? Какая будет прибыль, если вложить 10,0 единиц?

Сколько надо вложить средств, чтобы получить прибыль 100,0 единиц?

Несколько независимых переменных

В тех случаях, когда аппроксимируемая переменная у
зависит от нескольких независимых переменных х1
, х2
, ...,
хn
,

y=f(х1
,x2
,...,xn
),

подход с построением линии тренда не дает решения. Здесь могут быть использованы следующие специальные функции MS

Excel:

ЛИНЕЙН и ТЕНДЕНЦИЯ для аппроксимации линейных функций вида:

y
=a
0
+a
1
x
1
+a
2
x
2
+...+a
n
x
n
(3.13)

ЛГРФПРИБЛ и РОСТ для аппроксимации показательных функций вида:

y
= a
0
a
1
x
1
a
2
x
2
...a
n
xn
(3.14)

Функции ЛИНЕЙН и ЛГРФПРИБЛ служат для вычисления неизвестных коэффициентовa
0
,a
1
,...,a
n
,
в выражениях (3.13) и

(3.14) соответственно, а также коэффициентов детерминации (R2
), значений критерия Фишера, стандартных ошибок коэффициентов я, и ряда других показателей.

Обе функции имеют одинаковые параметры:

ЛИНЕЙН (известные_значения_у; известные_значения_х;конст; статистика)

ЛГРФПРИБЛ(известные_значения_у;известные_значения_х;конс т;статистика)
Здесь:

- известные_значения_у —
множество наблюдаемых значений у
из выражений (3.13), (3.14);

- известные_значения_х
— множество наблюдаемых значений x1,
x2
, ...,
хп
.
Причем, если массив известные_значения_у
имеет один столбец, то каждый столбец массива известные__значения_х
интерпретируется как отдельная переменная, а если массив известные__значения_у
имеет одну строку, то тогда каждая строка массива известные_значеиия_х
интерпретируется как отдельная переменная;

- конст —
логическое значение, которое указывает, требуется ли, чтобы константа а0
была равна 0 (для функции ЛИНЕЙН) или 1 (для функции ЛГРФПРИБЛ).

При этом, если конст
имеет значение ИСТИНА или опущено, то я0
вычисляется обычным образом, а если конст
имеет значение ЛОЖЬ, то а0
полагается равным 0 или 1;

- статистика
— логическое значение, которое указывает, требуется ли вычислять дополнительную статистику по регрессии, если введено значение ИСТИНА, то дополнительные параметры вычисляются, если ЛОЖЬ, то — нет (рис. 29).

Рис.29. Пример заполнения диалогового окна функции

Функции ТЕНДЕНЦИЯ и РОСТ позволяют находить точки, лежащие на аппроксимирующих кривых (3.13) и (3.14), соответственно, для значений коэффициентов а0
,
а1

, ...,ап
,
найденных функциями ЛИНЕЙН и ЛГРФПРИБЛ.

Обе функции имеют одинаковые аргументы:

ТЕНДЕНЦИЯ (известные_значения_у;известные_значения_х;новые_ значения_х;конст;

РОСТ(известные_значения_у;известные_значения_х;новые _значения_х;конст).

Здесь:

известные _значения _у —
множество значений у;
известные _значения_х —
множество значений х;
новые _значения_х
— те значения х,
для которых необходимо определить соответствующие аппроксимирующие или предсказанные значения у. Новые_значения_х
должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_х.
Если аргумент новые _значеиия_х
опущен, то предполагается, что он совпадает с аргументом известные _значения_х;
конст
— логическое значение, которое указывает, требуется ли, чтобы константа а0

была равна 0 (для функции ТЕНДЕНЦИЯ) или 1 (для функции РОСТ). При этом, если конст
имеет значение ИСТИНА или опущено, то а0
вычисляется обычным образом, а если конст
имеет значение ЛОЖЬ, то а0

полагается равным 0 или 1 (рис. 30).

Пример.
Источник радиоактивного излучения помещен в жидкость. Датчики расположены на расстоянии (х1
)
20, 50 и 100 см от источника. Измерения интенсивности излучения (у,
мРн) проводились через 1, 5 и 10 суток (х2
)
после установки источника. Результаты измерений (у)
приведены в таблице:

Необходимо аппроксимировать данные уравнением вида (3.14) и найти неизвестные параметры.

Рис.30. Пример заполнения диалогового окна функции ТЕНДЕНЦИЯ

Решение

1. Введем данные в рабочую таблицу: в ячейку А1 — символ х1
, в ячейку В1 — х2
,
в ячейку С1 — у.
В диапазон ячеек А2: А10 внесем значения х1
;
в диапазон В2:В10 — значения х2
и в диапазон С2:С10 — значения у
(рис. 31).

Рис. 31. Исходные данные из примера

2. Выделяем блок ячеек D1:F5 под массив результатов.

3. Поскольку уравнение для вычисления интенсивности излучения имеет степенной характер (3.14), вызываем функцию ЛГРФПРИБЛ (панель инструментов Стандартная, кнопка Вставка функции, рабочее поле Категория тип Статистические, рабочее поле Функция вид ЛГРФПРИБЛ).

4. Заполняем рабочие поля: Изв_знач_у — С2:С10,
Изв_знач_х — А2:В10,
Стат-1
(рис. 3.14). Нажимаем сочетание клавиш CTRL+SHIFT+ENTER.

5. В результате в диапазоне D1:F5 получим следующие данные:

Здесь первая строка — значения коэффициентов а2
,
а1

, а0
, соответственно, вторая строка — стандартные ошибки этих коэффициентов, третья строка — коэффициент детерминации R2

и стандартная ошибка у,
четвертая строка — значение критерия Фишера и число степеней свободы и нижняя строка — сумма квадратов регрессии и остаточная сумма квадратов.

Таким образом, искомое аппроксимирующее уравнение имеет вид:

y
= 99,7*0,98x
1
*0,92x
2

Причем точность аппроксимации очень высокая — R2
=
0,99998.

Пример.
В бассейне проводится ежедневная частичная смена воды. Имеются данные семидневных наблюдений изменения уровня воды в бассейне (у) от продолжительности заполнения водой (и времени выпуска воды (х2
).

Необходимо найти значения уровня воды в бассейне в зависимости от длительностей заполнения xl

∈ [100; 130] и выпуска воды х2

∈ [15; 25] с шагом Δ = 5 минут. Построить поверхность.

Решение

1. Введем данные в рабочую таблицу: в ячейку А1 — символ х1
,
в ячейку В1 — х2
,
в ячейку С1 —у.
В диапазон ячеек А2:А8 внесем значениях х1
, в диапазон В2:В8 — значения х2

и в диапазон С2:С8 — значения у.

2. Введем значения x1

и х2

для получения расчетных значений у в
соответствии с заданием: xl

∈ [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 вводятся значения х2
15,20,30.
Затем диапазон F2:H8 заполняется соответствующими расчетными значениями у.

Рис. 32. Расчетные значения у и соответствующие им значения х, и Х2
из примера

В результате должна быть получена следующая таблица

(рис. 33).

Рис. 33. Данные из примера, подготовленные для построения плоскости

Для построения диаграммы на панели инструментов Стандартная необходимо нажать кнопку Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг! из 4): тип диаграммы указываем тип диаграммы — Поверхность, и вид— Проволочная (прозрачная) поверхность (правую верхнюю диаграмму в правом окне). После чего нажимаем кнопку Далее в диалоговом окне.

9. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4):источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных F2:H8.

Далее необходимо указать в строках или столбцах расположены ряды данных. Это определит ориентацию осей X
и Y.
В примере переключатель Ряды в с помощью указателя мыши установим в положение столбцах.

10. Выбираем вкладку Ряд и в поле Подписи оси X указываем диапазон подписей. Для этого следует активизировать поле Подписи оси X, щелкнув в нем указателем мыши, и ввести диапазон подписей оси X — Е2:Е8.

11. Вводим значения подписей оси Y.
Для этого в рабочем поле Ряд указываем первую запись Ряд
1 и в рабочее поле Имя, активизировав его указателем мыши, вводим первое значение переменной Y (x2
) — 15.
Затем в поле Ряд указываем вторую запись Ряд
2 и в рабочее поле Имя вводим второе значение переменной х2

— 20.
Аналогично указываем х2

= 25 вместо записи Ряд 3.
После появления требуемых записей необходимо нажать кнопку Далее.

12. В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле указателем мыши, ввести с клавиатуры в поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: х1
,х2
и у.

13. Нажимаем кнопку Готово, и после небольшого редактирования будет получена диаграмма изменения уровня воды в бассейне (рис. 34).

Рис.34. Диаграмма изменения уровня воды в бассейне в зависимости от соотношения времени заполнения и выпуска воды

УПРАЖНЕНИЯ

1. В условиях примера 3.8 найти параметры аппроксимирующего уравнения и оценить его точность.

2. В условиях примера 3.7 найти расчетные значения интенсивности излучения для следующих значений х,
и х2
:

3. Застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе. Оценку цены офисного здания в заданном районе застройщик предполагает осуществлять на основе следующих переменных: у —
оценочная цена здания под офис, х1
—
общая площадь в квадратных метрах, х2
—
количество офисов, х3

— количество входов, х4
—
время эксплуатации здания в годах. Предполагается, что существует линейная зависимость между каждой независимой переменной (х1
, х2
, х3

и х4
)
и зависимой переменной (у),
то есть ценой здания под офис в данном районе. Застройщик наугад выбирает 11 зданий из имеющихся 1500 и получает следующие данные:

Здесь «полвхода» (1/2) означает вход только для доставки корреспонденции. Найти параметры аппроксимирующего уравнения.

4. В условиях упражнения 14 с помощью функции ТЕНДЕНЦИЯ определить оценочную стоимость здания под офис в том же районе, которое имеет площадь 2500 квадратных метров, три офиса, два входа, зданию 25 лет.

Слов:	19461
Символов:	157998
Размер:	308.59 Кб.

Название реферата: Excel в математических и статистических расчетах