Вставьте новый лист Поиск решения,на котором решите приведенную ниже задачу
Лабораторная работа № 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,3846154419,38 и . x2=7,2307697,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 грн. и будут минимальными. Ответ запишите в тетрадь.