Решение задач оптимизации в Excel

Рассмотрим решение на примере задачи 1.

Фирма производит два вида продукции: А и В. Прибыль на единицу продукции А и В составляет соответственно 20 и 30 рублей. Технология производства требует работы 3 машин. Время работы машин I, II, III в минутах, необходимое для производства единицы продукции каждого вида, приведено в таблице.

Ежедневный резерв времени работы машин равен соответственно 48, 32 и 16 часов в день. Определите ежедневные нормы выпуска продукции А и В, максимизирующие прибыль.

1. Открываем Excel.

Нажмите на квадратик между строкой с буквами и столбцом с чиселками.

Рисунок 1 До нажатия

Рисунок 2 После нажатия

Теперь, когда все ячейки выделены поставьте 14 шрифт, полужирное выделение и ориентацию текста по центру. Это важно, поверьте.

2. Записываем условия задачи.

Копируем таблицу из этого файла и вставляем в ячейку, скажем, А22. Полученную таблицу опять копируем, выделяем ячейку А1, жмем на ней правой кнопкой мыши и выбираем «специальную вставку». В открывшемся окошке, в нижнем право углу, будет чекбокс «транспонировать», ставим туда галочку и жмем ок.

Дальше заполняем таблицу, как показано на рисунке.

Коэффициенты прибыли – это прибыль на единицу продукции.

В ресурсах пишем в ячейку D1 =48*60 – это норма работы первой машины в минутах, для D2 и D3 формулы аналогичные.

3. Продолжаем заполнять табличку.

Выделяем ячейку Е1, жмем на значок функций в строке формул и выбираем функцию СУММПРОИЗВ. В качестве первого массива выбираем В2:С2, в качестве второго В8:С8. После этого нужно поставить значок $ во втором массиве, как показано на рисунке.

Жмем ОК и растягиваем ячейку до Е4.

В ячейке Е8 вычислите значение функции СУММПРОИЗВ как показано на рисунке.

Жмем ОК.

4. Поиск решений.

Выделяем ячейку Е8 и вызываем «поиск решений» через «сервис».

Вводим следующие значения.

Чтобы добавить ограничение, жмем кнопочку «добавить», в левом поле для ограничений выделяем Е2-Е4, знак оставляем меньше или равно, в правом поле – D2-D4, жмем ОК.

Прежде, чем нажать «выполнить» зайдите в параметры и поставьте галочки напротив «линейная модель» и «неотрицательные значения», потом ОК.

Теперь нажимаем выполнить.

Выделите типы отчета, если это необходимо и нажмите ОК (если только у вас правильное решение, должно быть как на рисунке внизу).

Раскрасьте все это, как-нибудь.

Транспортная задача.

1. Условия.

Для начала, повторите пункт 1 задачи 1.

Копируем на новый лист эту таблицу в ячейку А1, не транспонируя ее. Приведите ее в божеский вид, как на картинке. Скопируйте эту же таблицу из екселя на одну строку ниже.

В ячейку Е10 вводим функцию СУММ, в поле число 1 вводим A10:D10, жмем ОК. Растягиваем Е4 до ячейки Е10.

Теперь, в ячейке А15 так же вызываем функцию СУММ, в поле число 1 вводим A10:A14, жмем ОК и растягиваем вправо до D15.

2. Баланс.

В ячейку G2 пишем «Произведено», в G3 – «Требуется». В H1 вызываем функцию СУММ, число 1 - E2:E6.

Для Н2 аналогично, число 1 - A7:D7.

В G3 пишем «Баланс», в Н3 пишем формулу =H1-H2. Должен получиться ноль.

Если не получился, то значит сумма строки «Объемы потребления» отличается от суммы столбца «Объемы производства». В бонусной задаче, которую мы решали на лабораторной, меньше были «объемы производства». Чтобы это исправить, нужно еще одну строку в каждую таблицу (фиктивный производитель) и в получившейся пустой ячейке в столбце «Объемы производства» ввести такое число, чтобы баланс стал равен нулю. Дальше решение ничем от обычной задачи не отличается, только дополнительную строку (или столбец) нужно выделить каким-нибудь цветом.

3. Поиск решений.

Для начал сформулируем целевую функцию. В ячейке Е18 вызываем функцию СУММПРОИЗВ и вводим аргументы, как показано на рисунке.

Жмем ОК.

В ячейке Е18 вызываем поиск решений. Вводим аргументы, как на рисунке.

В параметрах опять отмечаем «линейная модель» и неотрицательные значения. Жмем выполнить. Ура!

Раскрасьте все теперь это.

Поиск по сайту: