[Список тем]
Лабораторная работа №6
Решение матричных задач с помощью решателя EXCEL.
Цель работы:
1. Научиться решать матричные задачи в среде Excel.
Пояснение.
Решение задач линейного программирования, условия которых заданы в
матричной форме, рассмотрим на примере задачи на максимизацию вероятности
поражения группы целей (см. приложение).
Поиск оптимума сводится к решению соответствующей закрытой транспортной
задачи, в которой единичная поставка от каждого "поставщика" передается
каждому "потребителю" (в общем виде при наличии нескольких однотипных ракет
или целей единица заменяется их количеством).
Порядок выполнения работы.
Войти в программу Excel.
На строке 1 и столбце А рабочего листа оставить место для контрольных
сумм по столбцу и строке. Незаполненное поле (в данной задаче 5строк на 5
столбцов) оставить для искомых " поставок" (изменяемые клетки при поиске
решения).
Ввести сумму изменяемых ячеек столбца для первого "потребителя" в ячейку
B1. Заполнить формулой 5 клеток вправо до конца таблицы. Пока содержимое этих
ячеек равно 0. После решения задачи оно должно иметь значение1. Эти условия
необходимо будет задать решателю в системе ограничений.
Ввести сумму изменяемых ячеек строки для первого "поставщика" в ячейку A2.
Заполнить формулой клетки вниз до конца таблицы. Пока содержимое этих ячеек
равно 0. После решения задачи оно должно соответствовать заданным мощностям
"поставщиков"(=1). Эти условия необходимо будет задать решателю в системе
ограничений.
Ввести матрицу вероятностей поражения целей в клетки соответствующих
столбцов расположенные ниже изменяемых клеток (оставленных для поиска
оптимального плана ) на число ячеек равное количеству "поставщиков".
Ввести сумму произведений вероятностей на содержимое ячеек плана для
столбца B в ячейку расположенную ниже заполненных клеток
(B12=B2*B7+B3*B8:произведение клеток с вероятностями и незаполненных пока
ячеек плана "поставок") и, используя заполнитель скопировать формулу для всех
целей.
Ввести сумму ячеек (целевую функцию) в свободную ячейку таблицы
(G12=B12+C12...).
Войти в "поиск решения" меню "сервис". Задать вид задачи - "на поиск
максимума".
Задать диапазон изменяемых ячеек (B2:F6) от левого верхнего до правого
нижнего угла поля изменяемых клеток.
Задать систему ограничений в соответствии с исходной таблицей:
(B1=1,C1=1:A2=1..., а так же неотрицательность и целочисленность значений
диапазона изменяемых ячеек).
Изменить параметры решателя.
Получить оптимальный план поражения целей. Если ставилась бы задача
поражения целей с нанесением максимального ущерба по стоимости объектов,
стратегическому значению и т.п. эта величина вычислялась бы иначе.
Сохранить полученное оптимальное решение.
Математическое ожидание числа пораженных целей предъявить преподавателю.
Для решения задачи 2 скопируйте задачу 1 на 2й лист и повторите решение с
меньшим числом ограничений.
Для решения задачи 3 стоимость можно учесть в слагаемых целевой функции.
Приложения:
Задача 1
Имеется 5 ракет и 5 целей. Вероятность поражения цели каждой из ракет задана в
таблице в соответствии с вариантом. Распределить ракеты по целям так (ракета по каждой цели), чтобы математическое ожидание числа попаданий было
максимальным.
Задача 2
В задаче 1 допустим, что цели одной ракетой не поражаются и обстрел необходимо
вести руководствуясь только попаданием максимального количества ракет в цели.
Задача 3
В задаче 1 допустим, что цели имеют ценность пропорциональную их номеру:
1я - 1ед., 2я - 2ед,:5я - 5ед. Сохраняя требование "ракета по каждой цели",
составить план атаки, которая нанесет противнику максимальный материальный
урон.
Вариант 1
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
Ракеты |
Цели |
1 | 2 | 3 | 4 | 5 |
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
[Список тем]
[В начало страницы]