Итак, сформулировали три требования, которые обеспечат принадлежность каждого пункта к одной из групп.

Лабораторная работа 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