Лабораторная работа 4. Вторая часть.
Решение задач оптимизации.
6.1 Открыть нужную надстройку в Excel:
Файл → Параметры → Надстройки →
Внизу окна:
Управление:
Щелкнуть
Появилось окно «Надстройки»: поставить «галочку» в Поиск решения → OK
Появилось:
Данные → Поиск решения (крайняя опция справа)
6.2 Постановка задачи и решение:
Дано: F(x1,x2, … xn) - так называемая, целевая функция.
x1,x2, … xn заданы каждый в определенной ограниченной области.
Найти: max, min или любое заданное значение F и соответствующие значения параметров x1,x2, … xn.
Решение:
1) Ввести данные:
| X1 | F | = | |
| X2 | |||
| ― • ― | ― • ― | ― • ― | ― • ― |
| Xn |
← ввести целевую функцию, поименовать F
↑
Поименовать соответственно x1,x2, … xn каждую ячейку
2) Открыть диалоговое окно «Параметры поиска решения» и заполнить:
 |
Оптимизировать целевую функцию:
До: max min Значения:
(выбрать нужное)
Изменяя ячейки переменных:
 |
В соответствии с ограничениями:
 |
И т.д.
Выбираем «Добавить», получаем диалоговое окно, позволяющее ввести все неравенства.
Выбираем метод решения: линейные (или нелинейные) задачи.
 |
Пример 1
Найти максимальное значение F(x,y) = 2*x + 2*y и соответствующие значения x и y в области
y ≤ 3/2*x + 3
y ≥ -3*x + 3
X ≤ 3
1)
A B C D
 x
| F | = | |
| y |
Поименовали B1 → x, B2 → y, D1 → F
В D1 ввели формулу = 2*x + 2*y
2) Оптимизировать целевую функцию:
До: max min Значения:
(выбрать max)
Изменяя ячейки переменных:
 |
В соответствии с ограничениями:
Метод решений:
(полученный ответ: x=3, y=7,5, F=21)
Пример 2
Дано: набор данных из 8 пунктов.
Задача: разбить на две равные группы, чтобы суммы значений были примерно равны.
Решение:
I. Заполняем таблицу и формулируем требования.
1) Первые два столбца заполняем наименованием пункта и его значением. Это – дано.
2) В следующих 2-х столбцах – информация о принадлежности к группе А или группе В, в виде 1 или 0. (1 – принадлежит, 0 – не принадлежит) Заполнение этих столбцов произойдет в результате решения задачи оптимизации.
Остальные столбцы заполняются так, чтобы сформулировать требования, обеспечивающие правильное заполнение столбцов гр.А и гр.В.
Во-первых, в них должны быть только 0 или 1. Если потребовать, чтобы столбцы гр.А и гр.В были
· Целые - 1-ое ограничение
· Неотрицательные - 2-ое ограничение
· Сумма их значений для каждого пункта была равна 1- 3-е ограничение
3) Для этого появился 5-ый столбец. В пятый столбец вводим формулу =СУММ(грА;грВ). Зададим требование, чтобы этот столбец заполнился единицами.
Итак, сформулировали три требования, которые обеспечат принадлежность каждого пункта к одной из групп.
Имеем 5 столбцов:
| пункт | значение | Гр.А | Гр.В | проверка |
| a | 2.3 | |||
| b | 6.2 | |||
| c | 7.1 | |||
| d | 9.1 | |||
| e | 3.5 | |||
| f | 5.9 | |||
| g | 4.8 | |||
| h | 7.5 | |||
| =СУММ | =СУММ |
Следующее условие: группы должны быть одинаковыми. Т.е. количества единиц в каждом столбце одинаковое. Для этого посчитаем суммы гр.А и гр.В. Эти суммы должны быть равны. Для этого в ячейку (любую! Назову ее АВ) введем их разность. Появилось 4-ое требование:
· Значение в ячейке АВ=0 - 4-ое ограничение
4) Вспомним главное условие задачи: суммы значений в каждой группе равны. Чтобы поставить это требование, надо добавить два столбца «значения в гр.А» и «значения в гр.В», которые для каждого пункта или 0 – если пункт не входит в группу, или равно соответствующему значению пункта, если пункт входит.
Вводим в столбец «значения в гр.А»: = «значение»* «Гр.А» и в столбец «значения в гр.В»: = «значение»* «Гр.В»
| пункт | значение | Гр.А | Гр.В | проверка | Значения в гр.А | Значения в гр.В |
| a | 2.3 | |||||
| b | 6.2 | |||||
| c | 7.1 | |||||
| d | 9.1 | |||||
| e | 3.5 | |||||
| f | 5.9 | |||||
| g | 4.8 | |||||
| h | 7.5 | |||||
| =СУММ | =СУММ | =СУММ | =СУММ |
Суммарные значения в каждой группе должны быть равны. Находим суммы.
(Формулы – Автосумма – сумма.)
Целевая функция – это разность этих сумм. Она должна быть минимальной.
В любую ячейку вводим формулу – модуль разности этих сумм. Поименуем ячейку F. Это – целевая функция.
II. Заполнение окна «Параметры поиска решения».
Оптимизируем (ячейку F)
по минимуму
Изменяя ячейки (столбцы гр.А и гр.В)
В соответствии с ограничениями
(1-ое,3-е и 4-ое)
2-ое ограничение – это галочка «сделать переменные без ограничений неотрицательными»
Найти решение.
В результате получился ответ: сумма А=23.сумма В=22.8
