Методические указания к выполнению 4 лабораторной работы

Задание 1. Поиск решения

Для решения создать таблицу с начальными данными:

Количество ставок по должностям вначале принимает значение 0.

Зарплата каждого сотрудника зависит от разряда и количества ставок. Поэтому в ячейку I3 надо занести формулу:

=ПРОСМОТР(H3;$B$3:$B$11;$C$3:$C$11)*G3

В остальные ячейки столбца " Зар.плата " скопировать эту формулу.

В ячейку G7 занести формулу суммирования: =СУММ(G3:G6)

В ячейку I8 занести формулу:=СУММ(I3:I6)

Используя специальную возможность Excel " Поиск решения " (Если такая функция отсутствует в меню " Параметры Excel ", следует выбрать " Надстройки", далее установить галочку напротив позиции " Поиск решения"), найти решение задачи.

Окно запроса " Поиск решения " предлагает установить целевую ячейку, задать изменяемые ячейки и ограничения.

Для данной задачи надо задать целевую ячейку I8 – общая сумма зарплат. В нашей задаче общая сумма зарплат не должна превышать 13000, поэтому выбрать надо переключатель " максимальное значение " и задать условие отбора (" Ограничения ")

$I$8<=13000

Изменять надо ячейки, содержащие количество ставок, - G3:G6.

Задать ограничения можно, используя кнопку " Добавить "

Все значения ячеек G3:G6 – должны быть целочисленными:

В ячейках G3 и G4 должно быть число 1.

Значение в ячейке G5 должно быть больше и равным 1 и меньшим или равным 5.

Значение в ячейке G6 должно быть больше и равным 2 и меньшим или равным 10.

На следующем рисунке приведено окно запроса " Поиск решения " с установленными параметрами задачи.

Параметры задачи

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

Решение найдено

Результат решения задачи

Задание 2. (10 баллов) Моделирование задачи линейного программирования

Математическая модель задачи

Параметрами, значения которых требуется определить, являются количества листов материала, которые будут раскроены различными способами:

X₁ – количество листов, раскроенное способом 1;

X₂ – количество листов, раскроенное способом 2;

X₃ – количество листов, раскроенное способом 3;

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

F=X₁ + X₂ + X₃

Ограничения определяются значениями требуемых количеств заготовок типа А и Б, тогда с учетом количеств заготовок, получаемых различными способами, должны выполняться два равенства:

6X₁ + 4X₂ + 10X₃ = 600

2X₁ + 5X₂ + 3X₃ = 400

Кроме того, количества листов не могут быть отрицательными!

Таким образом, необходимо найти удовлетворяющие ограничениям значения параметров, при которых целевая функция принимает минимальное значение.

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

Построим проект таблицы:

Проект таблицы

В ячейки B2, C2, D2 занести число 0, т.к. количество листов не может быть отрицательным, но может отсутствовать в заготовке. В ячейки B3, B4 занести ограничения на количество заготовок типа А (600) и типа В (400), заданные по условию задачи.

Записать формулы для расчета.

Ячейка	Вид формулы в Excel	Примечание
B5	=6*B2+4*C2+10*D2	ограничение на заготовки типа А
B6	=2*B2+5*C2+3*D2	ограничение на заготовки типа В
E2	=B2+C2+D2	целевая функция

Задать условия решения задачи с помощью инструмента Поиск решения:

- Установить целевую ячейку E2, определить ее значение как минимальное.

- Изменяться должны ячейки B2, C2, D2.

- Ограничения:

~ изменяемые ячейки должны быть целыми и положительными,

~ значение ячейки B5 должно быть равным количеству заготовок типа А, т.е. равным значению ячейки B3,

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

Получить решение, дав команду " Выполнить ".

Задание условий для поиска решения

Дав команду " Выполнить ", сохранив решение и построив диаграмму, получаем решение задачи:

Решение задачи

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

Далее можно проводить эксперименты. Например, если задать значения А=700 и В=300, то решение задачи будет следующим:

Проводить эксперименты следует на новых листах. Для этого лист с исходной задачей можно целиком скопировать, используя контекстно-зависимое меню на ярлыках листов и выбирая команду " Переместить/скопировать…".

Следующее действие – поставить галочку в поле " Создавать копию ".

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

Задание 4. (15 баллов) Расчет стипендии учебной группы

По материалам лекции

Задание 5. (5 баллов) Расчет стипендии учебной группы на 10 человек

По материалам лекции. Самостоятельно.

Дата добавления: 2014-11-29