Пример. Лабораторная работа № 7

Тема: Решение задач оптимизации средствами MS Excel.

Цель: закрепление навыков работы с электронными таблицами; решение задач оптимизации средствами электронных таблиц.

Изучаемые вопросы:

1. Задача распределения ресурсов.

2. Задача линейного программирования.

3. Надстройка MS Excel «Поиск решения».

Задачи:

· Получить представление о задаче распределения ресурсов.

· Получить представление о задаче линейного программирования.

· Освоить надстройку MS Excel «Поиск решения» для решения задачи распределения ресурсов.

К началу изучения этой темы вы должны:

· Иметь навыки работы с ОС Windows в объеме лабораторной работы №1;

· Владеть основными приемами работы в табличном процессоре MS Excel в объеме лабораторной работы №5.

После изучения этой темы вы должны:

· Иметь представление о задаче распределения ресурсов;

· Иметь представление о задаче линейного программирования;

· Иметь навыки работы с надстройкой MS Excel «Поиск решения» для решения задачи распределения ресурсов.

Краткие сведения

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

Пример.

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

Целью фирмы является выбор такого ассортиментного набора продуктов, при котором достигается максимум прибыли.

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

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

Таблица 3

Вид ресурса	Нормы расхода	Наличие ресурса
Прод1	Прод2	Прод3	Прод4
Трудовые
Сырье
Финансы
Прибыль, получаемая от реализации единицы каждого типа продукции

Найти оптимальный ассортиментный набор продуктов, при котором будет достигаться максимальная прибыль.

Составим экономико-математическую модель задачи, для чего введем следующие обозначения:

- количество выпускаемой продукции j -го типа, .

Как видно из таблицы 1, для выпуска единицы Прод1 требуется 6 единиц сырья, значит, для выпуска всей продукции Прод1 требуется единиц сырья, где - количество выпускаемой продукции Прод1. С учетом того, что для других видов продукции зависимости аналогичны, ограничение по сырью будет иметь вид:

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

Аналогично можно составить ограничения для остальных ресурсов, при этом необходимо учесть, что количество выпускаемой продукции не может быть отрицательным:

Максимизируется прибыль F, где .

Таким образом, имеем следующую задачу линейного программирования:

Задание 1:

1. Создайте новую рабочую книгу в MS Excel. Ниже приведено изображение листа (рис.1).

Рис. 1

2. Откройте видеоролик optim.avi. Расположите окно видеоролика и окно рабочей книги слева направо. Запустите видеоролик (для этого нужно нажать кнопку в окне проигрывателя) и выполните действия, показанные в нем, в рабочей книге. Результат не сохраняйте.

Задание 2:

Составьте оптимальный план организации поездок студентов во время научно-исследовательской практики в следующей ситуации. Университет может профинансировать поездки студентов пяти факультетов (факультеты будем обозначать номерами) в три города X, Y, Z.

Количество студентов, которых следует отправить в поездки, дано в таблице (табл. 1).

Таблица 4

№ района
Количество практикантов

Количество студентов, которое отдел по научно-исследовательской практике может обеспечить путевками в каждый из трех городов, представлено в таблице (табл. 2).

Таблица 5

Город	X	Y	Z
Количество путевок

Стоимость поездки (в рублях) приведена в таблице (табл. 3).

Таблица 6

Город	№ факультета

X
Y
Z

Смысл чисел в таблице (табл. 3) таков: если в ячейке Y2 стоит 600, то это значит, что поездка одного студента факультета №2 в город Y обходится в 600 рублей.

Необходимо составить такой план прохождения практики, который бы:

· Позволил каждому из намеченных к поездке студентов побывать на практике;

· Удовлетворил условию об общей числе практикантов в каждый из городов;

· Обеспечил максимально низкие суммарные расходы финансирующей стороны.

Представим план проведения экскурсионных поездок школьников в таблице (табл. 4).

Таблица 7


X
Y
Z

Величины , , , i, j, k=1, 2, 3, 4, 5, стоящие в таблице (табл. 4), являются объектами поиска. Например, есть число студентов факультета №3, которые по разрабатываемому плану едут в город X.