[Список тем]

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

Решение матричных задач с помощью решателя EXCEL.

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

  1. Войти в программу Excel.

  2. На строке 1 и столбце А рабочего листа оставить место для контрольных сумм по столбцу и строке. Незаполненное поле (в данной задаче 5строк на 5 столбцов) оставить для искомых " поставок" (изменяемые клетки при поиске решения).

  3. Ввести сумму изменяемых ячеек столбца для первого "потребителя" в ячейку B1. Заполнить формулой 5 клеток вправо до конца таблицы. Пока содержимое этих ячеек равно 0. После решения задачи оно должно иметь значение1. Эти условия необходимо будет задать решателю в системе ограничений.

  4. Ввести сумму изменяемых ячеек строки для первого "поставщика" в ячейку A2. Заполнить формулой клетки вниз до конца таблицы. Пока содержимое этих ячеек равно 0. После решения задачи оно должно соответствовать заданным мощностям "поставщиков"(=1). Эти условия необходимо будет задать решателю в системе ограничений.

  5. Ввести матрицу вероятностей поражения целей в клетки соответствующих столбцов расположенные ниже изменяемых клеток (оставленных для поиска оптимального плана ) на число ячеек равное количеству "поставщиков".

  6. Ввести сумму произведений вероятностей на содержимое ячеек плана для столбца B в ячейку расположенную ниже заполненных клеток (B12=B2*B7+B3*B8:произведение клеток с вероятностями и незаполненных пока ячеек плана "поставок") и, используя заполнитель скопировать формулу для всех целей.

  7. Ввести сумму ячеек (целевую функцию) в свободную ячейку таблицы (G12=B12+C12...).

  8. Войти в "поиск решения" меню "сервис". Задать вид задачи - "на поиск максимума".

  9. Задать диапазон изменяемых ячеек (B2:F6) от левого верхнего до правого нижнего угла поля изменяемых клеток.

  10. Задать систему ограничений в соответствии с исходной таблицей: (B1=1,C1=1:A2=1..., а так же неотрицательность и целочисленность значений диапазона изменяемых ячеек).

  11. Изменить параметры решателя.

  12. Получить оптимальный план поражения целей. Если ставилась бы задача поражения целей с нанесением максимального ущерба по стоимости объектов, стратегическому значению и т.п. эта величина вычислялась бы иначе.

  13. Сохранить полученное оптимальное решение.

  14. Математическое ожидание числа пораженных целей предъявить преподавателю.

  15. Для решения задачи 2 скопируйте задачу 1 на 2й лист и повторите решение с меньшим числом ограничений.

  16. Для решения задачи 3 стоимость можно учесть в слагаемых целевой функции.

Приложения:
Задача 1
Имеется 5 ракет и 5 целей. Вероятность поражения цели каждой из ракет задана в таблице в соответствии с вариантом. Распределить ракеты по целям так (ракета по каждой цели), чтобы математическое ожидание числа попаданий было максимальным.
Задача 2
В задаче 1 допустим, что цели одной ракетой не поражаются и обстрел необходимо вести руководствуясь только попаданием максимального количества ракет в цели.
Задача 3
В задаче 1 допустим, что цели имеют ценность пропорциональную их номеру: 1я - 1ед., 2я - 2ед,:5я - 5ед. Сохраняя требование "ракета по каждой цели", составить план атаки, которая нанесет противнику максимальный материальный урон.
Вариант 1
Ракеты Цели
12345
1,12,2,5,43,15
2,71,18,81,5,26
3,84,76,26,37,52
4,22,45,833,81,65
5,49,2,5,26,27
Вариант 2
Ракеты Цели
12345
1,2,12,5,43,15
2,18,71,81,5,26
3,76,84,26,37,52
4,45,22,833,81,65
5,2,49,5,26,27
Вариант 3
Ракеты Цели
12345
1,5,2,12,43,15
2,81,18,71,5,26
3,26,76,84,37,52
4,833,45,22,81,65
5,5,2,49,26,27
Вариант 4
Ракеты Цели
12345
1,43,5,2,12,15
2,5,81,18,71,26
3,37,26,76,84,52
4,81,833,45,22,65
5,26,5,2,49,27
Вариант 5
Ракеты Цели
12345
1,15,43,5,2,12
2,26,5,81,18,71
3,52,37,26,76,84
4,65,81,833,45,22
5,27,26,5,2,49
Вариант 6
Ракеты Цели
12345
1,71,18,81,5,26
2,84,76,26,37,52
3,22,45,833,81,65
4,49,2,5,26,27
5,12,2,5,43,15
Вариант 7
Ракеты Цели
12345
1,84,76,26,37,52
2,22,45,833,81,65
3,49,2,5,26,27
4,12,2,5,43,15
5,71,18,81,5,26
Вариант 8
Ракеты Цели
12345
1,22,45,833,81,65
2,49,2,5,26,27
3,12,2,5,43,15
4,71,18,81,5,26
5,84,76,26,37,52
Вариант 9
Ракеты Цели
12345
1,49,2,5,26,27
2,12,2,5,43,15
3,71,18,81,5,26
4,84,76,26,37,52
5,22,45,833,81,65
Вариант 10
Ракеты Цели
12345
1,5,2,15,43,12
2,833,45,65,81,22
3,5,2,27,26,49
4,26,76,52,37,84
5,81,18,26,5,71
Вариант 11
Ракеты Цели
12345
1,12,2,5,43,15
2,71,18,81,5,26
3,84,76,26,37,52
4,22,45,833,81,65
5,49,2,5,26,27
Вариант 12
Ракеты Цели
12345
1,2,12,5,43,15
2,18,71,81,5,26
3,76,84,26,37,52
4,45,22,833,81,65
5,2,49,5,26,27
Вариант 13
Ракеты Цели
12345
1,5,2,12,43,15
2,81,18,71,5,26
3,26,76,84,37,52
4,833,45,22,81,65
5,5,2,49,26,27
Вариант 14
Ракеты Цели
12345
1,43,5,2,12,15
2,5,81,18,71,26
3,37,26,76,84,52
4,81,833,45,22,65
5,26,5,2,49,27
Вариант 15
Ракеты Цели
12345
1,15,43,5,2,12
2,26,5,81,18,71
3,52,37,26,76,84
4,65,81,833,45,22
5,27,26,5,2,49
Вариант 16
Ракеты Цели
12345
1,71,18,81,5,26
2,84,76,26,37,52
3,22,45,833,81,65
4,49,2,5,26,27
5,12,2,5,43,15
Приложение.
Тест вызывается за 15 минут до конца занятия.
При вызове теста выбирайте кнопку "Запустить" вместо "Сохранить" и
"ОК" при предупреждении системы безопасности
Тест №8

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