Коврижных А.Ю., Конончук Е.А. и др. Прикладное программное обеспечение для решения экономических задач
Подождите немного. Документ загружается.
51
13. Снова щелкнем на кнопке Добавить. В поле Ссылка на ячейку укажем
диапазон С1:Н1. В качестве условия зададим >=. В поле Ограничение зададим
число 0. Это условие указывает, что число участников мероприятий
неотрицательно. Щелкнем на кнопке ОК.
14. Снова щелкнем на кнопке Добавить. В поле Ссылка на ячейку укажите
диапазон С1:Н1. В качестве условия выберем цел. Это условие указывает, что
турист неделим. Щелкните на кнопке ОК.
Рис.4.6
15. Щелкнем на кнопке Выполнить. По завершении оптимизации откроется
диалоговое окно Результаты поиска решения.
16. Установим переключатель Сохранить найденное решение, после чего
щелкнем на кнопке ОК.
Рис.4.7
В результате получится набор оптимальных значений переменных
(оптимальное количество туристов для участия в каждом мероприятии из
каждой турбазы) при данных ограничениях (при заданных возможностях
мероприятий): число туристов из турбазы в Сухуми, посещающих обезьяний
52
питомник (х
1
= 30), ботанический сад (х
2
= 170) и отправляющихся в поход (x
3
= 0); число туристов из окрестной турбазы, посещающих обезьяний питомник
(x
4
= 40), посещающих ботанический сад (х
5
= 0) и отправляющихся в поход
(х
6
= 110). При этом суммарные расходы турфирмы (Z) составят 2120 руб. и
будут минимальными.
Другими типовыми примерами задач линейного программирования являются
задачи:
¾ О рационе питания.
¾ Об оптимальных перевозках.
¾ Об оптимальном плане пошивочной мастерской.
¾ О рациональном использовании сырья.
Их можно выполнить в качестве лабораторных работ.
5. Aппроксимация экспериментальных данных
На практике часто приходится сталкиваться с задачей о сглаживании
экспериментальных зависимостей или задачей аппроксимации.
Аппроксимацией называется процесс подбора эмпирической формулы φ(х) для
установленной из опыта функциональной зависимости y = f(x). Эмпирические
формулы служат для аналитического представления опытных данных.
Функция одной переменной.
Обычно задача аппроксимации распадается на две части. Сначала
устанавливают примерный вид зависимости y = f(x) и, соответственно, вид
эмпирической формулы, то есть решают, является ли она линейной,
квадратичной, логарифмической или какой-либо другой. После этого
определяются численные значения неизвестных параметров выбранной
эмпирической формулы, для которых приближение к заданной функции
оказывается наилучшим. Если нет каких-либо теоретических соображений для
подбора вида формулы, обычно выбирают функциональную зависимость из
числа наиболее простых, сравнивая их графики с графиком заданной функции.
53
После выбора вида формулы определяют ее параметры. Для наилучшего
выбора параметров задают меру близости аппроксимации экспериментальных
данных. Существуют различные меры близости и, соответственно, способы
решения этой задачи. Обычно определение параметров при известном виде
зависимости осуществляют по методу наименьших квадратов. При этом
функция φ(х) считается наилучшим приближением к f(x), если для нее сумма
квадратов невязок имеет наименьшее значение.
min)]()([
2
0
→−
∑
=
i
n
i
i
xxf
ϕ
(5.1)
Используя методы дифференциального исчисления, метод наименьших
квадратов формулирует аналитические условия достижения суммой квадратов
отклонений своего наименьшего значения.
В простейшем случае задача аппроксимации экспериментальных данных
выглядит следующим образом. Пусть есть какие-то данные, полученные
практическим путем (в ходе эксперимента или наблюдения), которые можно
представить парами чисел (x,у). Зависимость между ними отражает таблица:
x x
1
x
2
. . . . . x
n
y y
1
y
2
. . . . . y
n
На основе этих данных требуется подобрать функцию у = φ(х), которая
наилучшим образом сглаживала бы экспериментальную зависимость между
переменными и, по возможности, точно отражала общую тенденцию
зависимости между x и у, исключая погрешности измерений и случайные
отклонения.
В MS Excel аппроксимация экспериментальных данных осуществляется путем
построения их графика (x – отвлеченные величины) или точечного графика (x
имеет конкретные лечения) с последующим подбором подходящей
аппроксимирующей функции (линии Тренда). Возможны следующие
варианты функции:
54
1. Линейная : у = а·х+ b. Обычно применяется в простейших случаях, когда
экспериментальные данные возрастают или убывают с постоянной
скоростью.
2. Полиномиальная : у = a
0
+ a
1
x + a
2
x
2
+… + a
n
x
n
,
где n ≤ 6, a
i
, — константы.
Используется для описания экспериментальных данных, попеременно
возрастающих и убывающих. Степень полинома определяется количеством
экстремумов (максимумов или минимумов) кривой. Полином второй степени
может описать только один максимум или минимум, полином третьей степени
может иметь один или два экстремума, четвертой степени — не более трех
экстремумов и т. д.
3. Логарифмическая : у = a·lnx + b, где а и b — константы.
Функция применяется для описания экспериментальных данных, которые
вначале быстро растут или убывают, а затем постепенно стабилизируются.
4. Степенная: у = bx
a
, где а и b — константы.
Аппроксимация степенной функцией используется для экспериментальных
данных с постоянно увеличивающейся (или убывающей) скоростью роста.
Данные не должны иметь нулевых или отрицательных значений.
5. Экспоненциальная: у = bе
ax
, где а и b — константы, е — основание
натурального логарифма.
Применяется для описания экспериментальных данных, которые быстро
растут или убывают, а затем постепенно стабилизируются. Часто ее
использование вытекает из теоретических соображений.
Степень близости аппроксимации экспериментальных данных выбранной
функцией оценивается коэффициентом детерминации (R
2
). Таким образом,
если есть несколько подходящих вариантов аппроксимирующих функций,
можно выбрать функцию с большим коэффициентом детерминации (его
значение наиболее близко к 1).
Для осуществления аппроксимации на диаграмме экспериментальных данных
необходимо щелчком правой кнопки мыши вызвать выплывающее
55
контекстное меню и выбрать пункт Добавить линию тренда. В появившемся
диалоговом окне Линия тренда на вкладке Тип выбирается вид
аппроксимирующей функции, а на вкладке Параметры задаются
дополнительные параметры, влияющие на отображение аппроксимирующей
кривой.
Пример 5.1.
Исследовать характер изменения уровня производства некоторой продукции с
течением времени и подобрать аппроксимирующую функцию, располагая
следующими данными:
Год Производство продукции
1997 17,1
1998 18,0
1999 18,9
2000 19,7
2001 19,7
Решение
1) Для построения диаграммы, прежде всего, необходимо ввести данные в
рабочую таблицу. Вводим в ячейку А1 слово Год. Затем в ячейки А2:А6
последовательно вводим годы, начиная с 1997. Далее в ячейку В1 заносим
слово Продукция и устанавливаем табличный курсор в ячейку В2. Здесь
должно оказаться значение 17,1 соответствующее значению года в ячейке А2.
Аналогично заполняем ячейки В3:В6.
2) Далее по введенным в рабочую таблицу данным необходимо построить
диаграмму. Выделим исходные данные – диапазон B1:B6. Щелчком указателя
мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В
появившемся диалоговом окне выбираем тип диаграммы и ее вид. Выберем
диаграмму График, т.к. необходимо отобразить динамику изменений
производства, не привязываясь к конкретному году. Выберем вкладку Ряд и с
помощью мыши вводим диапазон подписей по оси x. Далее, вводим название
56
диаграммы — Производство продукции, названия осей. Нажимаем кнопку
Готово.
Получен график экспериментальных данных.
3) Осуществим аппроксимацию полученной кривой полиномиальной
функцией второго порядка, поскольку кривая довольно гладкая. Для этого
указатель мыши устанавливаем на одну из точек графика и щелкаем правой
кнопкой. В появившемся контекстном меню выбираем пункт Добавить
линию тренда.
Рис. 5.1.
Появляется диалоговое окно Линия тренда (Рис.5.2).
Рис.5.2 Рис.5.3
57
В этом окне на вкладке Тип выбираем тип линии тренда — Полиномиальная
и устанавливаем степень — 2, Затем открываем вкладку Параметры (рис. 5.3)
и устанавливаем флажки в поля Показывать уравнение на диаграмме и
Поместить на диаграмму величину достоверности аппроксимации (R
^
2).
После чего нужно щелкнуть на кнопке ОК.
В результате получим на диаграмме аппроксимирующую кривую (рис.5.4).
Производство продукции
y = -0,1357x
2
+ 1,5043x + 15,66
R
2
= 0,9864
15,5
16
16,5
17
17,5
18
18,5
19
19,5
20
1997 1998 1999 2000 2001
Условные единицы
Годы
Продукция
Полиномиальный
(Продукция)
Рис.5.4
Как видно, уравнение наилучшей полиномиальной аппроксимирующей
функции для некоторых отвлеченных значений х (1, 2, 3, . ..) выглядит как
y = -0,1357x
2
+ 1,5043x + 15,66
При этом точность аппроксимации достаточно высока — R
2
= 0,986.
4) Попробуем улучшить качество аппроксимации выбором другого типа
функции. Здесь возможным вариантом представляется логарифмическая
функция. Для этого в окне Линия тренда на вкладке выбираем тип линии
тренда — Логарифмическая.
В результате получим другой вариант аппроксимации — логарифмической
кривой (рис. 5.5)
58
Рис.5.5
Как можно видеть на рисунке, уравнение наилучшей логарифмической
аппроксимирующей функции несколько уступает по точности аппроксимации
полиномиальной кривой — R
2
= 0,9716 < 0,986. Поэтому если нет каких-либо
теоретических соображений, то можно считать, что наилучшей
аппроксимацией является аппроксимация полиномиальной функцией второй
степени (из двух рассмотренных вариантов).
Пример 5.2. После выброса ядовитого вещества его концентрация (мг/л) в
водоеме изменялась в соответствии со следующей таблицей:
Время после выброса (час) Концентрация вещества
(мг/л)
1 8,0
3 2,8
5 1,0
8 0,3
Определить вид функциональной зависимости изменения концентрации
вещества от времени и оценить его концентрацию в водоеме в момент
выброса.
59
Решение.
1) Для построения диаграммы, прежде всего, необходимо ввести данные в
рабочую таблицу. Вводим в ячейку А1 слово Время. Затем в ячейки
А2:А5 последовательно вводим время: 1, 3, 5, 8. Далее в ячейку В1
заносим слово Концентрация и в диапазон В2:В5 вводим
соответствующие концентрации вещества.
2) Далее по введенным в рабочую таблицу данным необходимо построить
диаграмму.
Выделяем диапазон исходных данных – A1:B5. Поскольку здесь
необходимо строить динамику изменений концентрации вещества в
соответствии с изменениями времени — будем строить диаграмму
Точечная.
Щелчком указателя мыши на кнопке на панели инструментов вызываем
Мастер диаграмм. В появившемся диалоговом окне выбираем тип
диаграммы Точечная, вид — левый верхний. Нажав кнопку Далее, вводим
название диаграммы — Концентрация вещества, название осей X и У:
Время и Концентрация, соответственно. Нажимаем кнопку Готово.
Получен график экспериментальных данных.
3) Осуществим аппроксимацию полученной кривой. Поскольку кривая
напоминает экспоненту и из теоретических соображений наиболее вероятный
закон изменения — экспоненциальный, целесообразно аппроксимировать
кривую изменения концентрации экспоненциальной функцией. Для этого
указатель мыши устанавливаем на одну из точек графика и щелкаем правой
кнопкой. В появившемся контекстном меню выбираем пункт Добавить
линию
тренда. Появляется диалоговое окно Линия тренда.
В этом окне на вкладке Тип выбираем тип линии тренда —
Экспоненциальная. Затем открываем вкладку Параметры и устанавливаем
флажки в поля показывать уравнение на диаграмме и поместить на диаграмму
величину достоверности аппроксимации (R
^
2)
.
Кроме этого, для того, чтобы
оценить концентрацию вещества в водоеме в момент выброса в поле Прогноз
60
назад на устанавливаем 1 период. После чего щелкаем на кнопке ОК. В
результате получим на диаграмме аппроксимирующую кривую (рис. 5.6).
Рис. 5.6.
Как видно из рисунка, уравнение наилучшей экспоненциальной
аппроксимирующей функции для зависимости концентрации от времени
выглядит как
y = 11,844e
-0,4695x
При этом точность аппроксимации очень высокая — R
2
= 0,9951, что
позволяет считать описание процесса изменения концентрации вещества в
водоеме экспоненциальной функцией адекватным. Расчетная оценка
концентрации вещества в момент выброса, как видно из графика, составляет
около 12 мг/л. Более точные цифры могут быть получены из уравнения при х =
0 (у
0
= 11,84 мг/л).
Несколько независимых переменных
В тех случаях, когда аппроксимируемая переменная у зависит от нескольких
независимых переменных х
1
х
2
,…, x
n ,
подход с построением линии тренда не
дает решения. Здесь могут быть использованы следующие специальные
функции MS Excel:
ЛИНЕЙН и ТЕНДЕНЦИЯ для аппроксимации линейных функций вида:
у = а
0
+ а
1
х
1
+ а
2
х
2
+... + а
п
х
п
(5.2)