1. Цель лабораторной работы

Научиться составлять математическую модель и находить оптимальное решение.

2. Задание (пример) к лабораторной работе

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

1. Создать форму для ввода условий задачи.

2. Ввести исходные данные в виде таблицы.

3. Ввести в отдельные ячейки таблицы зависимости из математической модели.

4. Найти оптимальное решение с помощью команды Поиск решения/меню Сервис.

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

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

2.1 Исходные данные

Выпускается продукция четырех типов: продукт 1, продукт 2, продукт 3, продукт 4.

• Для выпуска требуется 3 вида ресурсов: труд, сырье, финансы.

Известно:

• Нормы расхода (количество ресурса каждого вида), необходимые для выпуска единицы продукции данного типа.

• Сколько ресурса имеется в наличии.

• Прибыль, получаемая от реализации единицы каждого типа продукции.

Требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье, финансы.

Оформим данные в виде таблицы 1:

Таблица 1 - Таблица представления результатов

Ресурс	Продукт 1	Продукт 2	Продукт 3	Продукт 4	Знак	Наличие
Прибыль	60	70	120	130	мах
Трудовые	1	1	1	1	<=	16
Сырьевые	6	5	4	3	<=	110
Финансовые	4	6	10	13	<=	100

2. Постановка математической задачи

Введем обозначения:

Xj- количество выпускаемой продукции j- го типа (j=1…4); Bi- количество имеющегося в наличие ресурса i-го вида (i=1…3) ; Aij- норма расхода i- го ресурса для выпуска единицы продукции j-го типа; Сij- прибыль, получаемая от реализации продукции j- го типа.

F=60x₁+70x₂+120x₃+130x₄ max

x₁+x₂+x₃+x₄<=16

6x₁+5x₂+4x₃+3x₄<=110

4x₁+6x₂+10x₃+13x₄<=100

хj >= 0, j=1,2,…,4.

3. Ход выполнения лабораторной работы

1 На рабочем листе EXCEL подготовим форму для ввода условий задачи: Для этого в ячейку D1 введем слово Переменные, в D7 – Ограничения.

Далее в диапазон клеток B2:E3 ввести соответственно: Продукция 1, Продукция 2, Продукция 3, Продукция 4 (введите в ячейку B2 Прод1 и щелкнув мышкой по прямоугольнику внизу ячейки протяните до Е3. Вводим в ячейку A2 Имя, в А3- значение, А4 – нижняя граница, А5 – верхняя граница, А6 – коэффициенты в целевой функции и т. д., как указано в таблице 1.

Весь этот текст является комментарием и на решение задачи не влияет.

Рисунок 1 – Компьютерный эквивалент задачи

2 Введите исходные данные в форму.

3 Далее введите зависимости для целевой функции и левой части ограничений:

- Курсор ставим в клетку F6, выбираем команду Мастер функций на панели инструментов и щелкаем левой кнопкой мыши. На экране появляется диалоговое окно Мастер функций шаг 1 из 2. Устанавливаем курсор в окно Категория на категорию Математические функции, щелкаем левой кнопкой мыши и выбираем курсором в окне Функции Суммпроизв. Щелкаем на кнопку мыши.

Нажимаем на команду Далее. Открывается диалоговое окно Мастер функций, где в строке массив 1 вводим диапазон ячеек B$3:E$3. Следует заметить, что во все диалоговые окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести.

В массив 2 ввести B6:E6. Затем щелкаем на кнопку ОК.