a  Содержание

 

a 8.3 Поиск решений 

    Традиционными задачами, решаемыми с помощью настройки «Поиск решений» являются:

  1. Ассортимент продукции. Сколько можно выпустить определенного товара при ограничении сырья.
  2. Штатное расписание. Как составить штатное расписание для достижения лучшего результата и при наименьших расходах.
  3. Планирование перевозок. Как минимизировать затраты на перевозки.
  4. Составление смеси. Как составить смесь заданного качества при наименьших затратах.

    Все эти задачи имеют три общих свойства:

  1. У них имеется только одна цель: или минимизировать затраты, или максимизировать прибыль.
  2. Имеются ограничения на сырьё, время и т.д.
  3. Имеется набор входных значений, влияющий на ограничения

    Как же строятся ограничения в задачах? Под ограничениями понимают соотношения вида А1<=B1, A1=A2, A1>=0, по крайней мере одна из ячеек в соотношении должна зависеть от переменных, в противном случае это соотношение не влияет на решение задачи.

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

    Ограничения имеют тот же синтаксис, что и формулы, но воспринимаются надстройкой Поиск решения иначе: формулы выполняются точно, а ограничения с некоторой погрешностью.

    Для того чтобы надстройка Поиск решения загружалось сразу при запуске Excel:

  1. В пункте меню Сервис выберете пункт Надстройки.

  2. Выйдет диалоговое окно Надстройки. В списке Доступные надстройки установите флажок напротив надстройки Поиск решений.

  3. Нажмите кнопку Ok.

    Составим таблицу вида:

    Для этого:

  1. В ячейки C3:G5 вводим константы.

  2. В ячейку B3 вводим формулу: =СУММ(C3:G3).

  3. В ячейку B4 вводим формулу: =СУММ(C4:G4).

  4. В ячейку B5 вводим формулу: =СУММ(C5:G5).

  5. В ячейку C7 вводим формулу: =СУММ(C3:C5).

  6. В ячейку D7 вводим формулу: =СУММ(D3:D5).

  7. В ячейку E7 вводим формулу: =СУММ(E3:E5).

  8. В ячейку F7 вводим формулу: =СУММ(F3:F5).

  9. В ячейку G7 вводим формулу: =СУММ(G3:G5).

  10. В ячейки C8:G8 вводим константы.

  11. В ячейки B10:B12 вводим константы.

  12. В ячейки C10:G12 вводим константы.

  13. В ячейку C14 вводим формулу: =C3*C10+C4*C11+C5*C12.

  14. В ячейку D14 вводим формулу: =D3*D10+D4*D11+D5*D12.

  15. В ячейку E14 вводим формулу: =E3*E10+E4*E11+E5*E12.

  16. В ячейку F14 вводим формулу: =F3*F10+F4*F11+F5*F12.

  17. В ячейку G14 вводим формулу: =G3*G10+G4*G11+G5*G12.

  18. В ячейку B14 вводим формулу: =СУММ(C14:G14).

  19. В следующие ячейки мы введем ограничения:

    1. В ячейку С25 введем ограничение: B3:B5<=B10:B12. Общее количество доставленных товаров с завода не должно превышать его производственной мощности.
    2. В ячейку C28 введем ограничение: C7:G7>=C8:G8. Количество товаров на складе должно удовлетворять потребностям соответствующего рынка.
    3. В ячейку C31 введем ограничение: C3:G5>=0. Количество поставляемых товаров неотрицательно.

    Теперь надо найти оптимальное решение:

  1. Выделите оптимизируемую ячейку. (В данном примере это В14).

  2. В пункте меню Сервис выберете пункт Поиск решения. Откроется диалоговое окно Поиск решения.

  3. В поле Установить целевую ячейку будет находиться имя оптимизируемой ячейки. При необходимости имя ячейки можно изменить.

  4. Установите флажок около какого-нибудь значения в поле Равной (Максимальному значению – поиск максимального значения для целевой ячейки, минимальному значению – поиск минимального значения для целевой ячейки, значению – поиск заданного значения для целевой ячейки).

  5. В поле Изменяя ячейки определите диапазон ячеек, которые изменяются.

  6. Определите ограничения. Около поля Ограничения нажмите кнопку Добавить. Откроется диалоговое окно Добавление ограничения, в которое вводятся неравенства ограничений. Если хотите ввести несколько ограничений, после введения первого ограничения, в том же окне, нажмите кнопку Добавить. После введения всех ограничений нажмите кнопку Ок. Снова выведется окно Поиск решения.

  7. Нажмите кнопку Выполнить. По окончанию поиска решения откроется диалоговое окно Результаты поиска решения.

  8. Выберете переключатель Сохранить найденное значение или Восстановить исходные значения.

  9. Нажмите кнопку Ok.

    При решении какой-либо задачи, иногда требуется экспериментировать с различными ограничениями. При помощи надстройки Поиск решения это можно осуществить:

  1. В пункте меню Сервис выберете пункт Поиск решения.

  2. В диалоговом окне Поиск решения, в списке имеющихся ограничений выделите нужное ограничение и нажмите кнопку Изменить.

  3. Откроется диалоговое окно Изменение ограничений. Измените ограничения и нажмите кнопку Ok.

  4. Снова откроется диалоговое окно Поиск решения. Нажмите кнопку Выполнить.

    Чтобы удалить ограничение, надо выделить неравенство и нажать кнопку Удалить.