[Список тем]

Лабораторная работа №3

Решение задач линейного программирования с помощью решателя EXCEL (Calc).

Цель работы:

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

Рекомендация

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

1. Написать на листе бумаги систему ограничений для задачи своего варианта и целевую функцию.
2. Войти в программу Excel (Calc)
3. Скопировать через буфер обмена условия задачи из таблицы исходных данных:
отдельно вставить заголовок таблицы в ячейки, начиная с А1, затем данные своего варианта в ячейки, начиная с A3.
4. Определить ячейки, содержащие значения искомых переменных (в нашем случае - это х1 и х2), например, пусть это будут ячейки А6 и А7. Окрасте их фон желтым цветом.
5. Ввести в ячейки таблицы формулы неравенств ограничений, ссылаясь на ячейки с переменными х1 и х2. Формулы всегда следует располагать под ячейками с соответствующими переменными. В данном случае это будут ячейки С6,D6,Е6, которые будут соответствовать общему количеству перевезенных мешков, посылок и контейнеров, соответственно. Окрасте их фон зеленым цветом. До работы решателя в этих ячейках будут нулевые значения.
6. Определить целевую ячейку, в которую ввести формулу целевой функции, ссылаясь на ячейки с переменными х1 и х2. Формулы должны содержать адреса, а не имена переменных, иначе это будут ссылки на пустые ячейки X1, X2.
Фон ячейки с целевой функцией сделайте красным.
7. Установить курсор на красную ячейку. Войти в "Поиск решения" меню "Сервис".
8. Задать параметры решения: При этом все ссылки на ячейки должны иметь абсолютные адреса. Задавать ссылки на ячейки и их диапазоны удобнее с помощью мыши.
9. Войти в "Параметры" решателя. Указать вид задачи "линейная", неотрицательные значения. Задать (изменить) точность и количество итераций.
Используя решатель всегда меняйте неточность по умолчанию 5% на 1%, иначе машина выдаст вам неоптимальное решение
Подтвердить изменения.
10. Нажать мышью кнопку решателя "Выполнить". При возникновении проблем уточнить содержимое ячеек, проверить и откорректировать ограничения и параметры решения.
11. Вторая задача решается аналогично на другом листе электронной таблицы. Если использовать тот же лист, могут возникнуть проблемы с решателем.
12. Предъявить полученные решения преподавателю. Заполнить в тетради отчет.
Содержание отчета.
1.Название и цель работы.
2.Условия решаемой задачи: cистема ограничений и целевая функция.
3.Полученное решение: оптимальное значение целевой функции и переменных.
Приложения:
Задача 1
Для перевозки контейнеров, посылок и мешков используются автомашины двух типов А и Б.
Машина каждого типа оборудована для перевозки определенного количества контейнеров, посылок и мешков одновременно. Перевозка другого вида груза на месте оборудованном например для транспортировки контейнеров (или др.) не допускается.
Данные об общем количестве перевозимого груза, емкостях машин за один рейс, максимальном количестве рейсов и стоимости перевозки за один рейс приведены в таблице Вашего варианта.
Определите необходимое количество рейсов машин А и Б, чтобы обеспечить перевозку всего имеющегося груза так чтобы затраты на перевозку были минимальными
Вариант Тип машин Емкость машин за 1 рейс Максимальное количество рейсов Затраты на перевозку за 1 рейс
Мешки Посылки Контейнеры
1 А
Б		 12
24		 24
12		 18
0		 12
20		 3,6
2,4
Общее кол-во грузов 192 240 90

2 А
Б		 14
28		 28
14		 21
0		 12
15		 4,2
2,8
Общее кол-во грузов 252 280 105

3 А
Б		 16
32		 32
16		 24
0		 12
16		 4,8
3,2
Общее кол-во грузов 256 288 120

4 А
Б		 44
11		 22
33		 0
11		 12
8 		2,2
1,1
Общее кол-во грузов 308 330 66

5 А
Б		 52
13		 26
39		 0
13		 18
8		 2,6
1,3
Общее кол-во грузов 156 312 52

6 А
Б		 24
6 		12
18		 0
6		 16
20		 1,2
0,6
Общее кол-во грузов 120 180 18

7 А
Б		 42
84		 84
42		 63
0		 12
18		 12,6
8,4
Общее кол-во грузов 336 420 126

8 А
Б		 18
36		 36
18		 27
0		 12
20		 5,4
3,6
Общее кол-во грузов 180 216 108

9 А
Б		 48
12		 24
36		 0
12		 15
8 		2,4
1,2
Общее кол-во грузов 144 216 60

10 А
Б		 56
14		 28
42		 0
14		 20
16		 2,8
1,4
Общее кол-во грузов 420 630 84

11 А
Б		 12
24		 24
12		 18
0		 12
20		 7,2
4,8
Общее кол-во грузов 192 240 90

12 А
Б		 14
28		 28
14		 21
0		 12
15		 8,4
5,6
Общее кол-во грузов 252 280 105

13 А
Б		 16
32		 32
16		 24
0		 12
16		 9,6
6,4
Общее кол-во грузов 256 288 120

14 А
Б		 44
11		 22
33		 0
11		 12
8 		4,4
2,2
Общее кол-во грузов 308 330 66

15 А
Б		 52
13		 26
39		 0
13		 18
8		 5,2
2,6
Общее кол-во грузов 156 312 52

16 А
Б		 24
6 		12
18		 0
6		 16
20		 2,4
1,2
Общее кол-во грузов 120 180 18


Задача 2
На производстве изготовляются изделия двух видов из ограниченного объема материала трех сорторазмеров. Исходные данные по расходу материала на единицу продукции, имеющимся фондам материалов и прибыли на единицу продукции сведены в таблицу.
Вариант Виды продукции Норма расхода материала на единицу продукции Имеющийся фонд материала по сорторазмерам Прибыль на единицу продукции
1 2 3 1 2 3
1 А
Б		 0,5
0,4		 0
0,2		 0,1
0,3		 4 0,8 1,5 1
2
2 А
Б		 3
1,7		 1
1		 0
1		 24 17 8 2
3
3 А
Б		 1
2		 0
2		 5
2		 50 36 150 5
30
4 А
Б		 1
1		 0,5
1		 0
1		 18 12 9 4
6
5 А
Б		 1
7		 2
5		 4
0		 30 40 64 3
8
6 А
Б		 8
5		 5
5		 2
0		 400 300 80 2
4
7 А
Б		 2
3		 5
2		 10
0		 6 10 15 1
6
8 А
Б		 2
5		 8
5		 5
6		 20 40 30 50
40
9 А
Б		 3
5		 4
0		 5
2		 15 6 10 3
1
10 А
Б		 3
1		 1
2		 5
4		 9 8 20 2
3
11 А
Б		 0,5
0,4		 0
0,2		 0,1
0,3		 4 0,8 1,5 2
4
12 А
Б		 3
1,7		 1
1		 0
1		 24 17 8 4
6
13 А
Б		 1
2		 0
2		 5
2		 50 36 150 10
60
14 А
Б		 1
1		 0,5
1		 0
1		 18 12 9 8
12
15 А
Б		 1
7		 2
5		 4
0		 30 40 64 6
16
16 А
Б		 8
5		 5
5		 2
0		 400 300 80 4
8
Тест вызывается за 15 минут до конца занятия.
При вызове теста выбирайте кнопку "Запустить" вместо "Сохранить" и
"ОК" при предупреждении системы безопасности
Тест №04

[Список тем] [В начало страницы]