Решение задач линейного программирования с помощью excel

Последовательность необходимых работ, выполняемых при решении задач линейного программирования с помощью Excel, приведена на блок-

схеме (рис.3.1).

3.Запуск табличного процессора (ТП) Excel в среде Windows осуществляется двумя способами:

А) двойным щелчком левой кнопки мыши на ярлыке рабочего стола;

Б) путем последовательного выполнения следующих операций: ПУСК→ВСЕ ПРОГРАММЫ→MICROSOFT EXCEL.

На экране открывается лист 1, разбитый на ячейки. В этих ячейках могут вводиться либо численные значения величин, либо математические формулы.

Для ввода формул используется Мастер функций, который запускается путем нажатия кнопки Мастер функций в верхней панели окна ТП Excel.

На экране появляется диалоговое окно Мастер функций – шаг 1 из 2(рис.3.2), в котором можно выбрать в окне Категориясоответствующую категорию, а в окне Функция– нужную функцию. После выбора и нажатия кнопки ОКпоявляется диалоговое окно Аргументы функции (в ранних версиях заголовок отсутствует, указывается вид функции:СУММ) (рис.3.3).

.

Рис.3.1. Блок-схема решения задачи линейного программирования

Рис.3.2.

В этом окне вводятся в окна Число 1 и Число 2 адреса ячеек, которые суммируются. Нажатие кнопки ОКосуществляет выполнение функции.

Рис.3.3.

Функция СУММПРОИЗВсуммирует произведения выбранных ячеек, адреса которых вводятся в окна массив 1и массив 2 и т.д. (рис.3.4).

Рис.3.4.

Диалоговое окно Поиск решения позволяет осуществить решение уравнений и задач оптимизации. Это окно запускается путем нажатия кнопки Сервис в верхней панели Excel. После развертывания меню выбрать курсором команду Поиск решения. Если в меню эта команда отсутствует, то нужно произвести ее установку по такому алгоритму: СЕРВИС→НАДСТРОЙКИ, после появления на экране окна (рис.3.5) установить флажок в окно ПОИСК РЕШЕНИЯ и нажать ОК.

Рис.3.5.

В окне Поиск решения(рис.3.6) есть возможность ввести адрес ячейки целевой функции, задаться предполагаемым значением целевой функции, ввести адреса изменяемых ячеек (объемов перевозок) и ограничений.

Рис.3.6.

Ввод ограничений производится путем нажатия кнопки Добавитьв открывающемся окне Добавление ограничения(рис.3.7).

Рис.3.7.

Для решения транспортной задачи необходимо нажать кнопку Параметрыи в открывшемся окне Параметры поиска решения (рис. 3.8) установить нужные параметры поиска решения и выбрать линейную модель, которая обеспечивает применение симплекс-метода.

Рис.3.8.

После выполнения команды Выполнитьв окне Поиск решенияExcel выдает на экран информацию о результатах решения задачи в окне Результаты поиска решения в виде сообщений: Ре­шение найдено. Все ограничения и условия оптимальности выполнены(рис.3.9); Поиск не может найти подходящего решения (если условия задачи несовместны) (рис.3.10); Значения целевой ячейки не сходятся (если целевая функция не ограничена) (рис.3.11).

Рис.3.9. Рис.3.10.

Рис.3.11.

Результаты решения задачи можно сохранить в виде отчетов по результатам, по устойчивости и по пределам. Для этого в поле Тип отчета выделяется курсором необходимый отчет и сохраняется на том же листе Excel.

Для наглядного представления результатов решения используются графики, которые выполняются при помощи Мастера диаграмм.

При построении диаграммы осуществляется следующая последовательность команд:

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

2) На верхней панели ТП нажать кнопку (Мастер диаграмм).

3) На экране диалоговое окно Мастер диаграмм (шаг 1 из 4):тип диаграммы. Верхние панели этого окна позволяют выбрать либо стандартные, либо нестандартные диаграммы. В поле «Тип» устанавливается тип диаграммы, а в поле «Вид» – необходимая форма диаграммы (рис.3.12).

1) Нажать кнопку «Далее».

2) На экране диалоговое окно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы, которое позволяет выбрать диапазон построения диаграммы (рис.3.13).

3) Нажать кнопку «Далее».

4) На экране диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграмм(рис.3.14).

Рис.3.12. Рис.3.13.

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

5) Нажать кнопку «Далее».

6) На экране диалоговое окно Мастер диаграмм (шаг 4 из 4): размещение диаграммы(рис.3.15).

7) Установить флажок в окно в зависимости от места расположения диаграммы (на отдельном листе или на имеющемся) и нажать кнопку «Готово».

Рис.3.14. Рис.3.15.

ПРИЛОЖЕНИЕ 2