Постановка задачи и построение математической модели
Требуется определить план выпуска четырех видов продукции, обеспечивающий максимальную прибыль от ее реализации.
На изготовление этой продукции расходуются трудовые ресурсы, сырье и финансы. С учетом рыночного спроса и производственно-технологических возможностей заданы предельные границы выпуска каждого вида продукции.
Эти границы, наличие и нормы расхода ресурсов, а также маржинальная прибыль (разность между выручкой и переменными издержками) на единицу продукции приведены в таблице:
| Ресурсы | Продукт 1 | Продукт 2 | Продукт 3 | Продукт 4 | Наличие ресурса |
| Трудовые | 1 | 2 | 1 | 2 | 19 |
| Сырье | 7 | 4 | 5 | 4 | 80 |
| Финансы | 5 | 7 | 9 | 8 | 100 |
| Прибыль | 70 | 60 | 100 | 140 | - |
| Нижняя граница | 3 | 1 | 1 | 2 | |
| Верхняя граница | 5 | - | 3 | 4 |
Обозначив количество выпускаемых изделий через Х1, Х2, Х3,Х4, а целевую функцию (валовую маржинальную прибыль)— через F, построим математическую модель задачи:
Левые три неравенства будем в дальнейшем называть ограничениями,а правые четыре– граничными условиями(онипоказывают, в каких пределах могут изменяться значения переменных).
Решение с помощью программы Excel
Ввод числовых данных
Решим задачу, приведенную выше. Для этого на рабочем листе введем ее числовые данные (рис. 1.).
В первой строке таблицы находится заголовок, во второй — наименования продуктов.
|
|
|
Третья строка отведена для оптимального решения, которое после вычислений появится в ячейках ВЗ:ЕЗ (в жирной рамке).
В четвертой строке в ячейках В4:Е4 заданы коэффициенты целевой функции, а ячейка F4, в рамке, зарезервирована для вычисления значения целевой функции.
Строки с 6-й по 15-ю содержат коэффициенты, знаки и правые части ограничений.
В столбце Лев.часть после вычислений появятся левые части ограничений, а в столбце Разница – разность правых и левых частей.
Предложенная форма таблицы удобна не только для выполнения расчетов, но и для документирования результатов моделирования, а также обмена идеями и улучшения взаимопонимания при работе «в команде» – группе лиц, ответственных за принятие решений.
Наглядность таблицы обеспечивается использованием заголовков, полужирного шрифта, подчеркивания текста и центрирования.
Денежные величины следует представлять в денежном или финансовом формате (с указанием символа используемой валюты).
Рис. 1. Ввод данных для решения задачи линейного программирования
Оформление таблицы облегчают кнопки панели форматирования, в частности: Объединить и поместить в центре (для центрирования заголовка, занимающего несколько ячеек), Денежный формат (для задания финансового формата), Увеличить разрядность илиУменьшить разрядность(для задания нужного числа дробных знаков).
|
|
|
Чтобы отобразить знак > или <, наберите знак > или < и затем подчеркните его.
После составления таблицы подберите ширину ее столбцов в соответствии с содержимым ячеек. Для этого выделите весь лист, щелкнув прямоугольник в левом верхнем углу листа (на пересечении заголовков строк и столбцов). Затем дважды щелкните правую границу заголовка любого из столбцов.
Ввод формул
Формулы и ячейки, в которые их следует ввести, указаны в таблице:
| Ячейка | Формула | Копировать в диапазон ячеек |
| F4 | =СУММПРОИЗВ($B$3:$E$3;B4:E4) | F6:F15 |
| I6 | =H6-F6 | I7:I8; I13:I15 |
| I9 | =F9-H9 | I10:I12 |
Чтобы ввести, например, формулу для вычисления целевой функции:
1.Укажите ячейку F4.
2.В строке формул щелкните кнопку Изменить формулу (со знаком равенства). Раскроется панель формул.
3.В левой части строки формул раскройте список функций и щелкните имя функции СУММПРОИЗВ. Если его там нет, выберите в списке пункт Другие функции. В открывшемся окне в категории Математические найдите функцию СУММПРОИЗВ и дважды щелкните ее имя. Панель формул примет вид, позволяющий задать аргументы этой функции (рис. 2.).
|
|
|
4.Введите аргументы функции СУММПРОИЗВ.
5.Щелкните кнопку ОК.
Сразу после ввода формулы в ячейке F4 появится 0, так как формула вычисляется с нулевыми значениями переменных (ячейки ВЗ:ЕЗ пока пусты).
Рис. 2. Ввод формулы целевой функции задачи линейного программирования
Функций СУММПРОИЗВ позволяет вычислить сумму произведений двух массивов, первый из которых содержит значения переменных, а второй — коэффициенты целевой функции. Чтобы указать соответствующие диапазоны, можно воспользоваться кнопками свертывания, расположенными справа от полей ввода. Они позволяют временно убрать панель формул с экрана, чтобы удобнее было выделять диапазон на листе. Закончив выделение, щелкните кнопку снова для восстановления панели.
Ссылка на первый диапазон должна быть абсолютной, со знаками доллара перед каждой буквой и цифрой: $В$3:$Е$3 (чтобы изменить относительную ссылку на абсолютную, нажмите клавишу F4 непосредственно после ввода этой ссылки; если это не сделать сразу, то в дальнейшем ссылку сначала понадобится выделить и лишь затем нажать клавишу F4).
Ссылка на второй диапазон В4:Е4, напротив, должна быть относительной: что понадобится в дальнейшем при копировании формулы.
|
|
|
После ввода формулы для вычисления целевой функции необходимо задать формулы левых частей ограничений. С этой целью скопируйте формулу из ячейки F4 в ячейки F6:F15. Чтобы копировалась только сама формула (без формата ячейки, к которому относятся, например, рамка и символ используемой валюты) можно воспользоваться специальной вставкой.
Для этого щелкните ячейку F4 правой кнопкой мыши и в контекстном меню выберите командуКопировать, затем выделите диапазон F6:F15, щелкните его правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка. В группеВставить открывшегося окна выберите параметр формулы и щелкните кнопку ОК. Чтобы отменить движущуюся границу ячейки F4, нажмите клавишу Esc.
При копировании относительная ссылка В4:Е4 будет меняться, указывая на массивы коэффициентов соответствующих ограничений, а абсолютная — $В$3:$Е$3 — останется неизменной.
Для проверки формулы вячейке достаточно дважды щелкнуть эту ячейку. Формула будет отображена на экране, ссылки на ячейки — выделены цветом, а сами ячейки — обведены рамками соответствующих цветов (после просмотра нажмите клавишу Esc).
Чтобы отображались все введенные формулы, выберите вменю СервискомандуПараметрыи в открывшемся окне на вкладкеВидвгруппе Параметры окна установите флажок Формулы.
Если таблицу с формулами необходимо напечатать, подберите ширину ее столбцов в соответствии с содержимым ячеек, как это было описано ранее.
Аналогично, с помощью копирования, вводятся формулы в ячейки I6:I15 для вычисления разницы правых и левых частей ограничений. Для ограничений со знаком < из правой части ограничения вычитается левая, а для ограничений со знаком > — наоборот.
Таким образом, если ограничения выполняются и план допустим, то разница всегда неотрицательна.
После ввода формул вы можете подставлять в ячейки ВЗ:ЕЗ любые значения (количество выпускаемой продукции), получая соответствующую величину прибыли в ячейке F4 и объем израсходованных ресурсов в столбце Лев.часть.
Столбец Разница покажет, допустим ли соответствующий производственный план, какие ресурсы дефицитны (для них разница равна нулю) и каков остаток недефицитных ресурсов (он равен положительной разнице).
Дата добавления: 2018-05-12; просмотров: 328; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!