Решение прикладных задач в Excel
       

Задачи оптимизации


В предыдущей главе мы рассмотрели задачу поиска значения параметра, позволяющего достичь конкретной цели.

Решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый наперед заданный результат.

Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?

Такие задачи в Excel решают с помощью Поиска решения.

7.2.1 Задача линейного программирования

Если математическая модель исследуемого процесса и ограничения на значения ее параметров линейны, то задача достижения цели является задачей линейного программирования.

Познакомимся с решением этих задач на следующем примере.

7.2.1.1 Составление штатного расписания

Усложним рассмотренную в предыдущей главе задачу. Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8-10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии, что оклад санитарки не должен быть меньше прожиточного минимума 80 грн.

В качестве модели решения этой задачи возьмем, как и в первой главе, линейную. Запишем ее так:

N1*A1*C+N2*(A2*C+B2)+...+N8*(A8*C+B8) = Минимум.

В этом уравнении нам не известно число санитарок (N1), медсестер (N2), врачей (N3) и оклад санитарки (С).

Используя Поиск решения, найдем их.

    Откройте созданный в предыдущей главе файл hospital.xls.

    В меню Сервис активизируйте команду Поиск решения.

    В окне Установить целевую ячейку укажите ячейку F12, содержащую модель.

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

    Используя кнопку Добавить, опишите ограничения задачи.

    Окончательно окно Поиска решения будет выглядеть так:

    Опишите Параметры поиска, как показано на рис. 7.1.

    Щелкните на кнопке ОК, а затем - Выполнить.


    Решение приведено на рис. 7.2. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.
    Автор специально привел здесь эту задачу, чтобы читателю было легче освоить новый материал.
    Для закрепления пройденного материала решим следующую задачу.
    7.2.1.2 План выгодного производства
    Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10-и килограмм конфет "А" дает прибыль 9 грн., "В" - 10 грн. и "С" - 16 грн.



    Рисунок 7. 1 - Описание параметров поиска решения

    Рисунок 7. 2 - Решение задачи линейного программирования
    Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.
    Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже.

    СырьеНормы расхода сырьяЗапас сырья АВС 
    Какао 18 15 12 360
    Сахар 6 4 8 192
    Наполнитель 5 3 3 180
    Прибыль 9 10 16 


      Введите исходные данные и формулы в электронную таблицу, как указано ниже.

      В меню Сервис активизируйте команду Поиск решения и опишите его параметры, как указано на рис 7.3.
      Не забудьте указать в Параметрах на Линейность модели.
      Запустите Поиск решения. Если Вы сделали все верно, то решение будет таким, как на рис 7.4.
      Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет "В" и 20 кг конфет "С". Конфеты "А" производить не стоит. Полученная Вами прибыль составит 400 грн.
      7.2.1.3 Задачи книги Solvsamp.xls
      Книга Solvsamp.xls, входящая в состав Excel, в папке Examples\Solver содержит более сложные примеры использования средств процедуры Поиска решения.

      Рисунок 7. 3 - Описание параметров поиска решения

      Рисунок 7. 4 - План выгодного производства
      Листы с примерами расчетов из этой книги можно использовать как образцы решения Ваших задач оптимизации. Чтобы изучить листы с задачами линейного программирования "Перевозка грузов"1), "График работы" и "Оборотный капитал", откройте книгу, перейдите на нужный лист, затем выполните команду Поиск решения из меню Сервис. Целевые ячейки, влияющие ячейки и ограничения на листах уже заданы.


      7.2.2 Нахождение экстремума без ограничений
      Книга Solverex.xls, входящая в состав Excel, содержит пример решения типичной задачи маркетинга: определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной. Решение этой задачи позволяет определить: "Стоит ли вкладывать дополнительные средства в рекламу, чтобы увеличить прибыль?".
      Задачи, подобные приведенной, демонстрируют использование процедуры Поиска решения для подбора таких значений параметров, которые максимизируют значение нелинейной функции.
      Прежде чем знакомиться с примером этого листа, рассмотрим упрощенный вариант этой задачи: поиск максимума функции при изменении одного параметра.
      Исходные данные задачи состоят из 2-х блоков: "Планируемые показатели" и "Данные о продукции". Так, в 1-м квартале запланированы следующие показатели реализации:
    • сезонный фактор (ячейка В2) - равным 0.92);
    • затраты на заработную плату персонала (ячейка В9) - 8 тыс. грн;
    • затраты на рекламу (ячейка В10) - 10 тыс. грн.

      • Данные о продукции:
    • цена реализации (ячейка В17) - 40 грн;
    • себестоимость (ячейка В18) - 25 грн.

      • Расчет планируемых показателей производят так:

      • объем сбыта продукции3) (ячейка В4) нелинейно зависит от сезонного фактора и затрат на рекламу

        • =35*B2*(B10+3000)^0,5
      • доход с оборота определяется как ожидаемое количество проданных единиц продукции (ячейка В4), умноженное на себестоимость продукции, поэтому в ячейку В5 введем формулу

        • =B4*B17
      • фраза "себестоимость реализованной продукции" на языке математики выглядит как

        • B6=B4*B18
      • очевидно, что валовая прибыль, имеющая в электронной таблице адрес В7, определяется как

        • =B5-B6
      • накладные расходы фирмы будем исчислять в объеме 15% дохода с оборота, то есть в ячейку B11 введем формулу

        • =0,15*B5
      • валовые издержки рассчитаем как сумму затрат на заработную плату персонала, рекламу и накладные расходы, то есть

        • B12=СУММ(B9:B11)
      • прибыль от продукции определим как валовую прибыль минус валовые издержки, то есть

        • B14=B7-B12
      • рентабельность

        • B15=B14/B5


        Если вышеперечисленные данные и формулы ввести в таблицу, то результаты вычислений будут такими, как показано на рис. 7.5.

          Активизируем Сервис=>Поиск решения и опишем условия решения задачи:
        • задайте ячейку B14, как содержащую целевую функцию4);
        • определите цель оптимизации - Максимальное значение;
        • укажите ячейку, значение которой будет изменяться при поиске наилучшего решения5) - B10;
        • при назначении параметров укажите на нелинейность модели. Для остальных параметров используйте установки по умолчанию, которые подходят для решения большинства задач.

          • Щелкнув на кнопке Выполнить, Вы увидите следующий результат оптимизации (рис. 7.6).
          Как видно из полученного решения при, затратах на рекламу 17 093 грн. прибыль от реализации товара будет максимальной. Однако следует отметить, что максимизация прибыли не обязательно соответствует наивысшему значению рентабельности. Ее значение уменьшилось до 8%.

          Рисунок 7. 5 - Данные для поиска экстремума в задаче маркетинга

            Контрольное задание 7.2
            Покажите, что функция
            f = (x-1)2 + y2 - 0,5cos(2z)
            имеет минимальное значение -0,5 при x=1; y=-7,9E-07 и z=3,14159.

              Указание
              В качестве исходных данных для поиска минимума примите x=1, y=2 и z=3.

              Рисунок 7. 6 - Результат оптимизации
              7.2.3 Задача нелинейного программирования

                Если математическая модель исследуемого процесса или ограничения на значения ее параметров нелинейны, то задача достижения цели является задачей нелинейного программирования.
                Решите предыдущую задачу при условии, что затраты на рекламу не могут превышать 12 000 грн.
                Решение задачи - 14 722 грн.
                Отметим, что в этом случае с каждой вложенной гривны Вы получите 10 коп. дохода.

                  Контрольное задание 7.3
                  Покажите, что при ограничениях x + 2y <= 8, 2x - y <= 12, x >= 0, y >= 0 функция
                  f = x(2 - x) + 2y(2 - y)
                  имеет максимальное значение 3 при x=1 и y=1.

                    Указание
                    В качестве исходных данных для поиска минимума примите x=0 и y=0.
                    1) Эту задачу иногда называют "Транспортная".
                    2) Сезонный фактор отражает колебания спроса на товар в зависимости от времени года. Например, зимой чаще покупают теплые вещи.
                    3) Объем сбыта определяется количеством (штуками) проданной продукции.
                    4) Помните, что в адресе должна указываться ячейка, в которой содержится формула, а не числовое значение, дата, или текст.
                    5) Помните, что переменные - это числовые значения, а не даты, формулы или текст.
                    Назад | Содержание | Вперед

                    Содержание раздела