Работа 3.6 ГДЗ Семакин 11 класс (Информатика)

Цель работы: получение представления о построении оптимального плана методом линейного программирования; практическое освоение раздела Microsoft Excel Поиск решения для построения оптимального плана.
Используемое программное обеспечение: табличный процессор Microsoft Excel.
С правой пая информация
Средство, которое используется в данной работе, называется Поиск решения. Соответствующая команда находится в меню Сервис —» Поиск решения — одно из самых мощных средств табличного процессора Excel. Покажем на простейшем примере («пирожки и пирожные», см. § 20), как воспользоваться указанным средством.
Задание 1
Реализовать поиск оптимального решения для задачи планирования работы школьного кондитерского цеха, описанной в § 20 учебника.
1. Подготовить таблицу к решению задачи оптимального планирования.
В режиме отображения формул таблица показана на рисунке. Ячейки В5 и С5 зарезервированы соответственно для значений х (план по изготовлению пирожков) и у (план по изготовлению пирожных). Ниже представлена система неравенств, определяющая ограничения на искомые решения. Неравенства разделены на левую часть (столбец В) и правую часть (столбец D). Знаки неравенств в столбце С имеют чисто оформительское значение. Целевая функция занесена в ячейку В15.
2. Вызвать программу оптимизации и сообщить ей, где расположены данные. Для этого выполнить команду Сервис —» Поиск решения. На экране откроется соответствующая форма (рис. II.6).
3. Выполнить следующий алгоритм:
= > ввести адрес ячейки с целевой функцией. В нашем случае это В15 (заметим, что если перед этим установить указатель мыши на ячейку В15, то ввод произойдет автоматически); = > поставить отметку максимальному значению, т. е. сообщить программе, что нас интересует нахождение максимума целевой функции;
= > в поле И вменяя ячейки ввести В5:С5, т. е. сообщить, какое место отведено под значения переменных — плановых показателей;
= > в поле Ограничения ввести неравенства-ограничения, которые имеют вид: B10 < =D10; B11 < =D11; B12 > =D12;
B13 > =D13. Ограничения вводятся следующим образом:
> щелкнуть на кнопке Добавить;
> в появившемся диалоговом окне Добавление ограничения ввести ссылку на ячейку В10, выбрать из меню знак неравенства < = и ввести ссылку на ячейку D10; > снова щелкнуть на кнопке Добавить и аналогично ввести второе ограничение B11 < =D11 и т. д.; > в конце щелкнуть на кнопке ОК;
= > закрыть диалоговое окно Добавление ограничения. Перед нами снова форма Поиск решения (рис. П.7).
= > установить флажок линейная модель. Остальная информация на форме Параметры поиска решения чисто служебная, автоматически устанавливаемые значения нас устраивают, и вникать в их смысл не будем. Щелкнуть на кнопке ОК. Снова откроется форма Поиск решения;
= > щелкнуть на кнопке Выполнить — в ячейках В5 и С5 появится оптимальное решение.
В результате применения инструмента Поиск решения получен следующий оптимальный план дневного производства кондитерского цеха: нужно выпускать 600 пирожков и 100 пирожных. Эти плановые показатели соответствуют положению точки В на рис. 3.10 в учебнике. В этой точке значение целевой функции /(600, 100) = 800. Если один пирожок стоит 5 руб., то полученная выручка составит 4000 руб.
Задание 2
Требуется решить задачу поиска оптимального плана производства школьного кондитерского цеха с измененными условиями.
Представьте себе, что в школе учатся неисправимые сладкоежки. И кроме всех прочих ограничений перед кондитерским цехом ставится обязательное условие: число пирожных должно быть не меньше числа пирожков. При такой постановке задачи система неравенств (см. § 20) примет вид.
1. Внести соответствующие изменения в электронную таблицу, построенную при выполнении предыдущего задания.
2. Получить оптимальный план с помощью средства Поиск решения.
3. Проанализировать полученные результаты. Сопоставить их с результатами задания 1.

В таблице Excel задаём переменные:

• $$x$$ — число пирожков;
• $$y$$ — число пирожных.

Целевая функция, которую нужно максимизировать, равна:

$$f(x,y)=5x+8y$$

Ограничения задачи:

$$\begin{cases} x+y \le 700,\\ 2x+y \le 1300,\\ x+2y \ge 500,\\ x \ge 0,\\ y \ge 0. \end{cases}$$

Для задания 1 в Excel в ячейку с целевой функцией вводим формулу, а в «Поиске решения» указываем:

• целевую ячейку — B15;
• максимум;
• изменяемые ячейки — B5:C5;
• ограничения — B10<=D10, B11<=D11, B12>=D12, B13>=D13;
• флажок Линейная модель.

После запуска поиска решения получаем оптимальный план:

$$x=600,\quad y=100.$$

Значение целевой функции:

$$f(600,100)=5\cdot 600+8\cdot 100=3000+800=3800.$$

Если в условии учебника целевая функция задана как $$f(x,y)=5x+8y$$, то выручка составит $$3800$$ руб. Если в таблице используется другая формула из готового шаблона, нужно ориентироваться на неё.

Для задания 2 добавляем новое ограничение:

$$y \ge x.$$

После изменения системы ограничений и повторного запуска «Поиска решения» получаем новый оптимальный план. При дополнительном условии число пирожных должно быть не меньше числа пирожков, поэтому оптимум смещается в область, где $$y \ge x$$. Значения переменных и целевой функции нужно взять из результата, полученного в Excel после пересчёта.

Ответ: для задания 1 оптимальный план — $$x=600$$, $$y=100$$; для задания 2 к ограничениям добавляется условие $$y \ge x$$, после чего оптимальный план находится заново средствами Excel.