6. Математический аппарат,
применяемый при курсовом проектировании

6.4. Решение задачи аналитическим
методом с использованием Excel

Графический метод нагляден, позволяет выявить, с одной стороны, наиболее дефицитные ресурсы, а с другой – наиболее избыточные. Однако решение задачи не отличается точностью и не дает количественных оценок излишков ресурсов. Этих недостатков лишены аналитические методы и в частности, математический аппарат, заложенный в Excel.

Для решения данной задачи необходимо в ячейки В20-С25 записать коэффициенты математической модели, в F20-F25 правые части ограничений, а в В12 и С12 – коэффициенты целевой функции (рис. 6.10).

Рис. 6.10. Пример формирования исходных данных

Далее следует щелкнуть мышкой на Сервис, а затем Поиск решения. На экране монитора появляется окно, изображенное на рис. 6.11.

Рис. 6.11. Пример экрана Поиск решения

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

Рис. 6.12. Результаты поиска решения

После ОК на экране появляются результаты решения, пример которых приведен на рис. 6.13.

Рис. 6.13. Пример окончательного решения

Результаты расчетов Х1 и Х2 находятся в ячейках В8 и С8. Целевая функция – в ячейке D13. Столбец «Левая часть» характеризует фактическое использование ресурсов. «Остаток» определяется разностью между имеющимися запасами («Правая часть») и их фактическим использованием («Левая часть»).

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

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

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