Построение модели в электронной таблице

На электронном листе модель определенную структуру (рис.3.2.5).

Рис.3.2.5. Модель задачи линейного программирования

Модель состоит из 2 таблиц: Переменные и Ограничения.

Таблица Переменные имеет строки: Значение и Коэффициенты критерия.

Столбцы называются именами искомыми переменных: Кухни и Кофеварки.

На пересечении строки Значение со столбцами Кухни и Кофеварки ячейки оставляются пустыми. В них будут размещены искомые количества Кухонь (x1) и Кофеварок (x2) шт. после работы модели.

Коэффициенты для критерия взяты из рис.3.2.3, а в яч. D23 записана формула (3.2.2).

В нижней таблице Ограничения строки имеют названия производственных участков (рис.3.2.4). На пересечении строк со столбцами Кухонь и Кофеварок в ячейках пишутся доли времени, затрачиваемые на изготовление единицы продукции (рис.3.2.4).

В столбце Правые части указываются значения правых частей (3.2.1). В столбце Левые части пишутся формулы Левых частей (3.2.1).

Запись формул производится с помощью Мастера функций½Математические функции½СУММПРОИЗВ. Указываются адреса яч. x1, x2 и яч. Коэффициентов критерия.

Рис.3.2.6. Построение формулы СУМПРОИЗВ для вычисления критерия

Знак доллара ($) ставится перед цифрами в окне Массив1, чтобы использовать абсолютные адреса x1 и x2 при копировании формулы в таблицу Ограничений.

Построенную формулу копируют в таблицу Ограничения в ячейки Левые части (рис.3.2.7).

Рис.3.2.7. Построение формул для вычисления Критерия и Левых частей

Далее производится подготовка модели к пуску. Открывается окно Сервис ½Поиск решения (рис.3.2.8).

Рис.3.2.8. Установка ограничений с помощью окна Поиск решения

В верхней строке экранного меню указывается адрес целевой функции. Ниже отмечается значение критерия - Максимальное. В следующей строке указываются адреса искомых переменных x1 и x2, представляющие план выпуска кухонь и кофеварок. Для заполнения строк Ограничения нажимается кнопка Добавить и используется экранное меню (рис.3.2.9).

Рис.3.2.9. Меню для записи ограничений

На рис.3.2.9 показан пример записи ограничений для переменных х₁, х₂ >=0. Аналогично записываются другие ограничения.

После записи ограничений производится настройка модели на решение. Для этого нажимается кнопка Параметры. В открывшемся экранном меню (рис.3.2.10) имеются стандартные установки (максимальное время, предельное число итераций и т.д.), которые подходят для большинства задач, поэтому они не изменяются.

Рис.3.2.10. Настройка параметров поиска решения

Для решения задачи линейного программирования установим флажок Линейная модель. ОК! Возвращается экранное меню (рис.3.2.8).

Все необходимые настройки параметров модели сделаны и модель готова к пуску. Нажимается Выполнить. На рис.3.2.11 показано сообщение о результатах моделирования.

Рис.3.2.11. Сообщение о решении задачи и выбор вида отчетности

Экранная форма Результаты поиска решения позволяет выполнить три вида отчетности:

· по Результатам,

· по Устойчивости,

· по Пределам.

Результаты моделирования также печатаются на рабочем листе (рис.3.2.12).

Рис.3.2.12. Результаты моделирования. Оптимальный план производства:

- кухонь – 20363 шт., - кофеварок – 6495 шт.

В верхней таблице в яч.. В5:С5 напечатан оптимальный план производства. В нижней таблице в Левых частях (яч. D12:D15) показана занятость участков. Участки Штамповки, Отделки загружены на 100%, участки Сборки А и В загружены на 90 и 43%.

На рис.3.2.13 показан отчет по Результатам.

Рис.3.2.13. Отчет по результатам

Отчет состоит из 3-х таблиц. В 1-ой таблице приводятся сведения о целевой функции. Во 2-ой – значения переменных x1 и x2. Найденный оптимальный план производства предполагает еженедельный выпуск 20363 кухонь и 6485 кофеварок, от реализации которых может быть получен суммарный доход в размере 386511 у.е. (20363*15=305447 у.е. - от продаж кухонь и 6485*12,5=81064 у.е. - от продаж кофеварок).

В 3-ей таблице показываются результаты оптимального решения для ограничений и граничных условий. В столбце Формула показаны формулы, введенные при настройке модели. В столбце Значение приведены величины использованного ресурса. В столбце Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в столбце Статус указывается связанное, в противном случае указывается, что ресурс не связан.

Аналогично открываются и анализируются другие Отчеты.

Рис.3.2.14. План выпускаемой продукции и ожидаемые доходы

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

Последующий анализ отчета по Результатам (рис.3.2.13) показывает, что производительности линий Штамповки и Отделки исчерпаны. А производительности участков Сборки A и B используются не полностью, соответственно на 90% и 43%. Отсюда может быть сделано предложение о повышении доходов путем повышения производительности линий Штамповки и Отделки, либо снижения себестоимости производства путем сокращения оборудования на участках Сборки A и B.

Не нашли, что искали? Воспользуйтесь поиском: