Лабораторная работа № 2.2. ОПТИМИЗАЦИЯ. ПОИСК РЕШЕНИЯ. РЕГРЕССИЯ

Цель работы:

- Освоить основные навыки работы с «Решателем» («Сервис/ Поиск решения»);

- Изучить операции работы со сценариями в электронных таблицах Microsoft Excel («Сервис/ Сценарии»);

- Научиться грамотно составлять системы уравнений для дальнейшего аналитического поиска решений в электронных таблицах Miсrosoft Excel.

- Освоить основные навыки работы «Поиска решения» при решении задач по регрессии;

- Изучить имеющиеся в электронных таблицах Microsoft Excel регрессионные функции;

- Научиться грамотно пользоваться регрессионными функциями и умело использовать их для проведения полного статистического анализа данных в электронных таблицах Miсrosoft Excel.

Часть I. Оптимизация, поиск решения

Задание:

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

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

3. Вызвать «Решатель» («Сервис/ Поиск решения»).

4. Занести в соответствующие поля диалогового окна табличные данные задачи.

5. Проверить установки и параметры окна «Параметры поиска решения».

6. После подготовки задачи оптимизации, выполнить расчет и сохранить результаты поиска решения.

7. По условию данной задачи создать различные сценарии («Сервис/ Сценарии»).

8. Сохранить все выполненные сценарии в отчете.

9. Представить полученные результаты и ответить на контрольные вопросы.

Решение одного варианта

Фирма производит две модели А и В книжных полок. Их производство ограничении наличием сырья (досок) и временем машинной обработки. Для каждого изделия модели А требуется 3м² досок, а для изделия модели В – 4м². Фирма получает от поставщиков до 1700м² досок в неделю. Для каждого изделия модели А требуется 12мин. машинного времени, а для изделия модели В – 30мин. В неделю можно использовать 160ч. машинного времени. Сколько изделий и какой модели следует выпустить фирме в неделю, если каждое изделие модели А приносит 200руб. прибыли, а каждое изделие модели В – 400руб. прибыли?

Решение. Составим математическую модель. Обозначим х – количество изделий модели А, выпускаемых в течении недели, у – количество изделий модели В. Прибыль от этих изделий равна руб. Эту прибыль нужно максимизировать. Беспредельному увеличению количества изделий препятствуют ограничения. Ограничения по материалу - . Ограничение машинного времени обработки - . Формально задача оптимизации записывается так:

Теперь решим эту задачу в Excel. Создадим новую рабочую книгу, введем в ячейки рабочего листа информацию как указано в таблице и вызовем «Решатель».

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

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

В диалоговом окне «Параметры поиска решения» установим флажки «Линейная модель» и «неотрицательные значения».

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

Варианты:

1. Фирма производит три вида продукции (А, В, С), для выпуска каждого требуется определенное время обработки на всех четырех устройствах.

	Вид продукции	Время обработки, ч.					Прибыль, долл.
		I	II	III	IV
A		1	3	1	2	3
B		6	1	3	3	6
C		3	3	2	4	4

Пусть время работы на устройствах соответственно 84, 42, 21 и 42 часа. Определите, какую продукцию и в каких количествах стоит производить для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен.)

2. Фирма производит два продукта А и В, рынок сбыта которых неограничен. Каждый продукт должен быть обработан каждой машиной I, II, III. Время обработки в часах для каждого из изделий А и В приведено ниже:

	I	II	III
A	0.5	0.4	0.2
B	0.25	0.3	0.4

Время работы машин I, II, Ш соответственно 40, 36 и 36 часов в неделю. Прибыль от изделий А и В составляет соответственно 5 и 3 доллара. Фирме надо определить недельные нормы выпуска изделий А и В, максимизирующие прибыль.

3. Требуется уголь с содержанием фосфора не более 0.03% и с примесью пепла не более 3.25%. Доступны три сорта угля А, В, С по следующим ценам (за одну тонну).

Сорт угля	Содержание примеси фосфора, %	Содержание примеси пепла, %	Цена, долл.
A	0.06	2.0	30
B	0.04	4.0	30
C	0.02	3.0	45

Как их следует смешать, чтобы удовлетворить ограничениям на применение и минимизировать цену?

4. Как составить диету, содержащую по крайней мере 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц витаминов. Как дешевле всего достичь этого при указанных ценах на 1кг пяти имеющихся продуктов.

	Хлеб	Соя	Сушенная рыба	Фрукты	Молоко
Белки	2	12	10	1	2
Углеводы	12	0	0	4	3
Жиры	1	8	3	0	4
Витамины	2	2	4	6	2
Цена	12	36	32	18	10

5. В контейнер упакованы комплектующие изделия трех типов. Стоимость и вес одного изделия составляют 400руб. и 12кг. для первого типа, 500руб. и 16кг. для второго типа, 600 руб. и 15кг. для третьего типа. Общий вес комплектующих равен 326кг. Определить максимальную и минимальную возможную суммарную стоимость находящихся в контейнере комплектующих изделий.

6. Хозяйство занимается разведением рыбы. Доход от каждого сазана составляет 40 руб., от каждого окуня – 20 руб. Каждый сазан потребляет 0,04кг. корма, а окунь – 0,03кг. В неделю хозяйство получает до 170кг. корма, Каждому сазану требуется 0,05м³ объема воды в пруду, а окуню – 0,02м³. Количество воды в пруду не более 160м³. Сколько рыбы и какого вида следует выловить из пруда для получения наибольшей прибыли.

7. В лесном хозяйстве производится промышленная вырубка ценных пород древесины. Вырубка леса на должна повлиять на кислородный баланс (не более 1000усл. ед.) и на воспроизводство деревьев (не более 500 стволов деревьев в день ). Какие породы деревьев и в каком количестве следует вырубить для получения максимальной прибыли с соблюдением экологических норм системы?

Порода дерева	Выработка О2	Воспроизводство леса	Стоимость 1м^3.
Дуб	6	5	10000
Бук	4	3	6000
Ясень	3	2	4000

8. Фермерскому хозяйству требуется удобрения с содержанием нитратов не более 0,03% и с примесью пестицидов не более 3,25% Доступны три сорта удобрений, по следующим ценам за 1кг.

Сорт удобрения	Содержание нитратов, %	Содержание примеси пестицидов, %	Цена, руб.
A	0.06	2.0	30
B	0.04	4.0	30
C	0.02	3.0	45

Как их следует смешать, чтоб удовлетворить ограничениям на применение и минимизировать цену?

9. Очистка воды на городской насосной станции производится двумя агрегатами. Количество воды не ограничено. В каждом агрегате очистка производится в три этапа. Время обработки в часах 100м³ каждого из агрегатов приведены в таблице. Время работы по этапам I, II, III соответственно: 40, 36, 35 часов в неделю

	I	II	III
A	0.5	0.4	0.2
B	0.25	0.3	0.4

Затраты на обслуживание агрегатов А и В соответственно составляют – 500руб. и 300руб. за 100м³. Требуется определить недельные нормы очистки агрегатами А и В, минимизирующие затраты.

10. Ботанический сад занимается выращиванием цветов розовых кустов и тюльпанов. Доход от каждой розы составляет 40руб., от каждого тюльпана – 20руб. Каждой розе необходимо 0,004кг. удобрений, а тюльпану – 0,003кг. В неделю фирма получает до 17кг. удобрений, Каждой розе требуется 0,05м² площади почвы, а тюльпану – 0,02м². Посевная площадь составляет не более 160м². Сколько цветов и какого вида следует вырастить для получения наибольшей прибыли?

11. Администрации района необходимо срочно возвести типовые жилые объекты для жителей поселка 1600 человек, пострадавших от стихийного бедствия. Объект I типа с общей жилой площадью вмещает до 20 человек, объект II типа – 50 человек. Каждому объекту I типа необходимо 30м³ древесины, объекту II типа – 40м³ соответственно. Поставки древесины составляют 170м³ в неделю. На возведение объекта I типа необходимо 20 дней, на строительство объекта II типа – 40 дней. Какие объекты и в каком количестве следует построить администрации в самые минимальные сроки.

12. В лесничестве для подкормки стада оленей необходимо, по крайней мере: 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц витаминов. На одну голову. Как дешевле всего достичь при указанных ценах на 1кг пяти имеющихся компонентов питания.

	Овес	Овощи	Комбикорм	Отруби	Сено
Белки	2	12	10	1	2
Углеводы	12	0	0	4	3
Жиры	1	8	3	0	4
Витамины	2	2	4	6	2
Цена	12	36	32	18	10

13. Ботанический сад занимается выращиванием саженцев деревьев для улиц города. Дерево вида А способно воспроизвести до 40у.е. количества кислорода в день, дерево вида В – 20у.е. Каждому саженцу вида А необходимо 4кг. удобрений, а саженцу вида В – 3кг. В неделю хозяйство получает до 170кг. удобрений. Каждому саженцу вида А необходимо 0,05м³ посевной площади, а саженцу вида В – 0,02м³. Общая посевная площадь сада составляет 160м³. Сколько саженцев и какого вида следует выращивать для получения наилучшего воспроизводства кислорода.

14. Переработкой городского мусора занимаются три станции по переработке отходов. Количество вредных веществ выбрасываемых в атмосферу не должно превышать допустимых значений примесей СО₂ -0,03% , содержание примеси тяжелых металлов 0,0325% . Какое количество мусора может переработать каждая станция при минимальных энергетических затратах.

№ Ст.	Количество вредных веществ выбрасываемых в атмосферу		Потребление КВт/ч.
	Примесь СО2, %	примеси тяжелых металлов, %
1	0,02	0,03	45
2	0,06	0,02	30
3	0,04	0,04	30

*15. Имеются три сплава. Первый сплав содержит 70% олова и 30% свинца, второй – 80% олова и 20% цинка, третий – 50% олова, 10% свинца, и 40% цинка. Из них необходимо изготовить новый сплав, содержащий 15% свинца. Какое наибольшее и наименьшее процентное содержание олова может быть в этом сплаве?

Контрольные вопросы:

1. Как установить в электронные таблицы Excel надстройку «Поиск решения»?

2. Что называется целевой функцией?

3. Какие ячейки называют изменяющимися?

4. Что называют ограничениями?

5. Что называют сценарием?

6. Как вызвать окно для создания сценария?

7. Чем отличается сценарий от поиска решений?

8. От чего зависит скорость поиска наилучшего решения?

9. Какие адресные ссылки используются по умолчанию, для обозначения ячеек с формулами в окне «Поиск решения»?

Часть II Регрессия

Задание:

Дан ряд значений (x_i ,y_i) экологических показателей согласно некоторым статистическим наблюдениям: (0,5), (1,4), (2,7), (3,8), (4,10), (5,7), (6,9), (7,6), (8,11), (9,9), (10,12), (11,15), (12,13), (13,14), (14,15), (15,17), (16,16), (17,18), (18,19), (19,20) (Студент вправе задать собственные значения показателей). Используя данные статистических наблюдений выполнить регрессионный анализ по следующему плану задания:

1. Найти коэффициенты m и b прямой линии y = mx+b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

2. Построить диаграмму с исходными данными и приближающим их графиком.

3. Сравнить коэффициенты m и b найденные с помощью «Поиска решения» со значениями полученными при помощи функций НАКЛОН и ОТРЕЗОК.

4. Используя функцию ЛИНЕЙН определить коэффициенты m и b , а также получить дополнительные статистические характеристики.

5. Самостоятельно, с помощью справочной информации, содержащейся в программе Microsoft Excel, изучить действие функций НАКЛОН, ОТРЕЗОК, ТЕНДЕНЦИЯ, ПРЕДСКАЗ.

6. Вычислить оценки откликов для старых и новых значений факторов используя функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ. Выяснить, в чем разница между этими двумя функциями.

Решение одного варианта

В различных областях науки и техники часто возникает задача подбора функциональной зависимости для двух наборов данных. Независимые переменные x_i называют факторами, а зависимые y_i – откликами. Функция y = f(x) позволяет предсказывать значение отклика для факторов, не входящих в исходную совокупность. Зависимость между откликами и факторами имеет вид y = f(x)+ε, где ε – некоторая случайная величина, из-за которой возникают ненулевые остатки. В гауссовской модели простой линейной регрессии предполагается, что ε_i – независимы и распределены по нормальному закону с нулевым среднем и одинаковой дисперсией.

Дан набор точек (x_i ,y_i) : (0,3), (1,1), (2,6), (3,3), (4,7). Найти коэффициенты m и b прямой линии y = mx+b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов:

.

Разместить координаты точек в диапазоне А2:В6, как показано на рисунке. В ячейках А8 и В8 поместим начальные

	A	B	C	D
1	0	3	0	3
2	1	1	0	1
3	2	6	0	6
4	3	3	0	3
5	4	7	0	7
6
7	m	b
8	0	0		104

значения коэффициентов m и b и дадим им имена. В С2:С6 вычислим y_i = mx_i+b. В D2:D6 вычислим остатки (например, в D2 формула = В2 – С2). Наконец, в D8 вычислим сумму квадратов остатков (таблица 1.).

	A	B	C	D
1	0	3	2	1
2	1	1	3	-2
3	2	6	4	2
4	3	3	5	-2
5	4	7	6	1
6
7	m	b
8	1	2		14

Теперь необходимо решить задачу оптимизации. Выделим ячейку D8, вызовем Решатель и поставим задачу минимизации D8 путем изменения А8:В8. Ограничений нет. Результат представлен в таблице 2.

Для построения диаграммы с исходными данными и приближающим их графиком необходимо перед вызовом Мастера диаграмм выделить А1:А7, для ряда С1:С7 указать пользовательские погрешности из D1:D7, Для ряда В1:В7 сделать линию невидимой.

Но регрессионный анализ – это не только метод наименьших квадратов. Относительно исходных данных делаются некоторые статистические предположения.

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

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

Контрольные вопросы:

1. В чем заключается метод наименьших квадратов?

2. Функция ЛИНЕЙН ее параметры.

3. Какие дополнительные статистические характеристики позволяет определить функция ЛИНЕЙН и каким образом?

4. Какие регрессионные показатели вычисляются функциями НАКЛОН и ОТРЕЗОК.

5. Функция ТЕНДЕНЦИЯ ее параметры.

6. Функция ПРЕДСКАЗ ее параметры.

7. В чем разница между двумя функциями ТЕНДЕНЦИЯ, ПРЕДСКАЗ?