Программа Поиск решения представляет собой мощный вспомогательный инструмент для выполнения сложных вычислений. Она позволяет по заданному значению результата находить множество значений переменных, удовлетворяющих некоторым указанным критериям. Пользователь может задать режим, при котором полученные значения переменных будут автоматически заноситься в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.
Исходные данные для программы Поиск решения должны быть представлены в виде таблицы, которая содержит формулы, отражающие зависимости между данными таблицы.
Продемонстрируем применение программы Поиск решения на примере. Для разнообразия перейдем из сферы торговли в область производства и составим оптимальный план загрузки оборудования.
Создадим приведенную на рис. 21 таблицу. В столбце В представлены значения издержек для единицы товара. Значения в столбце D получены путем перемножения соответствующих значений столбцов В и С. Маржа (норма прибыли) является "наценкой" на себестоимость товара. Значения в столбце F представляют собой произведение соответствующих значений в столбцах D и Е. Данные объемов продаж получены путем суммирования значений себестоимости и прибыли. Необходимо заметить, что на практике постановка задачи может быть совершенно другой.
|
|
Рис. 21. Таблица-пример
Перед предпринимателем, естественно, стоит задача организовать производственный процесс так, чтобы не допустить превышения определенной суммы общих издержек. Прежде чем перейти непосредственно к вычислению, определим два граничных условия:
- на данном, оборудовании может производиться только 35000 единиц товара;
- общие издержки не должны превышать 4,5 млн.
В качестве переменных будем использовать данные ячеек, содержащих текущие значения количества различных видов продукции. Задача формулируется следующим образом: найти такие значения общего количества единиц продукции и значения количества единиц продукции по каждому виду товара отдельно, чтобы при общих издержках в 4,5 млн предприятие выпускало не более 35000 единиц продукции. Теперь запустим программу Поиск решения путем выбора одноименной команды из меню Сервис.
Рис. 22. Диалоговое окно программы Поиск решения
Выполним постановку задачи. Для этого в открывшемся диалоговом окне Поиск решения необходимо задать параметры. В поле Установить целевую ячейку нужно указать адрес ячейки, значение которой используется в качестве критерия оптимизации. В нашем случае это ячейка D11, в которой представлены данные об общих издержках при текущей загрузке оборудования. Обратите внимание: указанная ячейка содержит формулу и связана с изменяемыми ячейками. Сначала нужно задать значение для целевой ячейки. В нашем примере это значение определено, поскольку для финансирования издержек производства в распоряжении имеется только 4,5 млн. Активизируем селекторную кнопку Равной значению и указываем в поле ввода 4500000.
|
|
Две другие селекторные кнопки используются в случаях, когда необходимо вычислить для целевой ячейки минимальное/максимальное значение (например, если требуется определить при заданных условиях значение максимальной прибыли).
В поле Изменяя ячейки необходимо указать диапазон ячеек, включающий ячейки, значения которых программа должна, изменить для получения оптимального результата. Активизируем указанное поле ввода и выделяем ячейки С4 — С9 (в случае необходимости следует переместить диалоговое окно или указать адрес диапазона ячеек вручную).
При нажатии кнопки Предположить Excel выделяет диапазон ячеек, на которые прямо или косвенно ссылается целевая ячейка. В нашем примере это был бы диапазон ячеек В4 — С9. Однако мы данной кнопкой не воспользуемся, поскольку издержки на единицу продукции являются неизменными.