Ввод зависимостей для целевой функции

Цель работы

Приобретение навыков решения задач линейного программирования (ЛП) в табличном редакторе Microsoft Excel.

Порядок выполнения работы

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

 

Указания по решению одноиндексных задач линейного программирования

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

I. Ввести условие задачи:

1) создать экранную форму для ввода условия задачи:

· переменных,

· целевой функции,

· ограничений,

· граничных условий;

2) ввести исходные данные в экранную форму:

· коэффициенты целевой функции,

· коэффициенты при переменных в ограничениях,

· правые части ограничений;

3) ввести зависимости из математической модели в экранную форму:

· формулу для расчета целевой функции,

· формулы для расчета значений левых частей ограничений;

4) задать целевую функцию (в окне "Поиск решения"):

· целевую ячейку,

· направление оптимизации целевой функции;

5) ввести ограничения и граничные условия (в окне "Поиск решения"):

· ячейки со значениями переменных,

· граничные условия для допустимых значений переменных,

· соотношения между правыми и левыми частями ограничений.

II. Решить задачу:

1) установить параметры решения задачи (в окне "Поиск решения");

2) запустить задачу на решение (в окне "Поиск решения");

3) выбрать формат вывода решения (в окне "Результаты поиска решения").

Рассмотрим пример нахождения решения для следующей одноиндексной задачи ЛП:

(1.1)

Ввод исходных данных

Экранная форма для ввода условий задачи (1.1) вместе с введенными в нее исходными данными представлена на рисунке 1.1.

 

Рисунок 1.1 – Экранная форма задачи

В экранной форме на рисунке 1.1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка в Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи соответствуют ячейки B3 (), C3 (), D3 (), E3 (), коэффициентам целевой функции соответствуют ячейки B6 ( 130,5), C6 ( 20), D6 ( 56), E6 ( 87,8), правым частям ограничений соответствуют ячейки H10 ( 756), H11 ( 450), H12 ( 89) и т.д.

Ввод зависимостей для целевой функции

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

.

(1.2)

Данную формулу для расчета целевой функции можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов целевой функции (B6, C6, D6, E6), следующим образом:

.

(1.3)

Чтобы задать эту формулу необходимо в ячейку F6 ввести следующее выражение и нажать клавишу "Enter"

=СУММПРОИЗВ(B$3:E$3;B6:E6),

(1.4)

где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;

символ: означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6). После этого в целевой ячейке появится 0 (нулевое значение) (рисунок 1.2).

 

Рисунок 1.2 – Экранная форма задачи после ввода формул

Примечание 1.1. Существует другой способ задания функций в Excel с помощью режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки " "на стандартной панели инструментов. Так, например, формулу (1.4) можно задать следующим образом:

· курсор в поле F6;

· нажав кнопку " ",вызовите окно"Мастер функций – шаг 1 из 2";

· выберите в окне "Категория" категорию "Математические";

· в окне "Функция" выберите функцию СУММПРОИЗВ;

· в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B$3:E$3, а в строку "Массив 2" – выражение B6:E6;

· после ввода ячеек в строки "Массив 1" и "Массив 2" в окне "СУММПРОИЗВ" появятся числовые значения введенных массивов (рисунок 1.3), а в экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).

Рисунок 1.3 – Ввод формулы для расчета целевой функции

в окно "Мастер функций"