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

Лабораторная работа № 10.

Использование инструмента Поиск решения.

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

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

Запустите Excel и откройте Ваш файлФамилия9- матрицы.

2. Инструмент Поиск решения является надстройкой.

Для его запуска необходимо выполнить команды:

- Сервис®Надстройка;

- в открывшемся диалоговом окне Надстройки в поле Поиск решения установить флажок;

- нажать кнопку ОК.

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

Предприятие выпускает продукцию двух видов. Цена единицы продукции Вида 1 равна 250 грн., Вида 2 – 500 грн. Для изготовления продукции используются 3 вида сырья, запасы которого оцениваются в 37, 57.6 и 7 условных единиц. Коэффициенты расхода сырья по каждому виду продукции приведены в таблице.

Коэффициенты расхода по видам продукции	Запасы сырья (усл. ед.)
Вид 1	Вид 2
1,2	1,9
2,3	1,8	57,6
0,1	0,7

Необходимо определить оптимальный план выпуска продукции в условиях дефицита ресурсов.

Решение. Запишем в тетради математическую модель задачи. Обозначим количество произведенной продукции Вида 1 через x1, Вида 2 – x2. Тогда общая стоимость произведенной продукции (целевая функция) будет равна .

Найдем решение, которое должно обеспечить максимальное значение этой функции (т.е. максимальное количество произведенного продукта). Из условия задачи следует, что на переменные x1 и x2 должны быть наложены ограничения:

Таким образом, математическая модель задачи имеет вид:

Для решения задачи выполните действия:

- на листе Поиск решения подготовьте исходную таблицу вида:

- Выполнить команды Сервис® Поиск решения ;

- В раскрывшемся окне диалога Поиск решения указать:

a) В поле редактирования Установить целевую ячейку – адрес ячейки, в которой записана целевая функция – A6. При этом если до вызова окна диалога Поиск решения активной была ячейка с целевой функцией, то ссылка на нее в этом поле появится автоматически.

b) Переключатель Равной выставить в положение: максимальному значению.

c) В поле редактирования Изменяя ячейки – ссылки на ячейки, содержащие независимые переменные, которые в процессе поиска решения могут изменять свое содержимое. В данном примере – это A3:В3, значения переменных x1 и x2 соответственно.

- Нажать кнопку Добавить, чтобы ввести ограничения задачи;

- В окне диалога Добавление ограничения указать:

a) В левом поле редактирования Ссылка на ячейку – адрес ячейки или диапазона ячеек, на значения которых необходимо наложить ограничение. В нашем случае – это A9:А11.

b) В правом поле редактирования Ограничение – число, формулу, ссылку на ячейку или диапазон ячеек, задающие ограничения. В нашем случае – это B9:В11.

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

- Нажать кнопку Добавить, чтобы добавить ограничение и, не возвращаясь в окно диалога Поиск решения, приступить к формированию следующего:

a) Ссылка на ячейку – A12:А13.

b) Ограничение – B12:В13.

c) Тип соотношения – ≥.

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

При необходимости внести изменения в добавленные ограничения используют кнопки Удалить и Изменить.

- Нажать кнопку Параметры. В окне диалога Параметры поиска решения:

a) Выставить флажок Неотрицательные значения для того, чтобы учесть граничные условия задачи.

b) нажать кнопку OK.

- В окне Поиск решения нажать кнопку Выполнить для того, чтобы начать процесс поиска решения.

При необходимости процесс поиска решения можно прервать, нажав клавишу Esc.

- По окончании решения найденные значения переменных x1 и x2 будут занесены в ячейки A3:В3 исходной таблицы.

- Затем в появившемся окне диалога Результаты поиска решения необходимо:

a) Выбрать один из типов отчета (Устойчивость, Результаты, Пределы) для отображения полученных результатов; выберите Результаты.

b) Выставите переключатель в положение Сохранить найденное решение.

- Нажмите кнопку OK.

Перед листом Поиск решения будет вставлен новый лист Отчет по результатам 1. В ячейках A3:В3 отобразятся значения переменных, при которых достигается максимальное значение целевой функции, т. е x1= 19,38461544≈19,38 и. x2= 7,230769≈7,23. Запишите ответ в тетрадь.

- На листе Отчет по результатам 1 рассмотрите отчет MS Excel о решенной задаче.

Обратите внимание, что при объемах выпуска продукции 19,38 и 7,23 условных единиц ресурсы сырья оказываются использованными полностью.

4. Самостоятельно решить задачу:

Туристическая фирма заключила контракт с двумя турбазами А и В в г. Ялта рассчитанными, соответственно, на 200 и 150 человек. Туристам для осмотра предлагаются экскурсии на гору Ай-Петри, Никитский ботанический сад и конная прогулка в горы. Составьте маршрут движения туристов так, чтобы это обошлось возможно дешевле, если:

- канатная дорога на Ай-Петри пропускает в день 70 человек, Ботанический сад – 180 человек, а в горы в один день могут поехать 110 человек;

- стоимость одного посещения указана в таблице:

Турбаза Стоимость одного билета на экскурсию (грн.)

Ай-Петри НБС горы

А

В

Решение.

Для решения задачи введем обозначения: а1 – число туристов из турбазы А, посещающих Ай-Петри; а2 – число туристов из турбазы А, посещающих ботанический сад; а3 – число туристов из турбазы А, отправившихся в поход; b1 – число туристов из турбазы В, посещающих Ай-Петри; b2 – соответственно, число туристов посещающих ботанический сад; b3 – число туристов отправившихся в поход.

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

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

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

и

Запишите в тетради математическую модель задачи. Вставьте лист Задача, заполните исходную таблицу и, используя инструмент Поиск решения, получите ответ.

Если правильно выполнили все действия, то должны получиться значения: а1=70; а2=30; а3=100; b1=0; b2=150; b3=0. При этом суммарные расходы турфирмы (y) составят 7280 грн. и будут минимальными. Ответ запишите в тетрадь.