2. Решение задачи с помощью инструмента Excel Поиск решения.

Алгоритм решения задачи состоит из нескольких этапов:

1. Ввод исходных данных.

	А	В	С	D	E	F	G
1		Переменные
2	имя	Товар А	Товар В	Товар С
3	значение
4					ЦФ	направление
5	коэффициент в ЦФ	3	5	4		макс
6			Ограничения
7	вид	Товар А	Товар В	Товар С	левая часть	знак	правая часть
8	Рабочее время, чел.-час.	0,1	0,2	0,4		<	1100
9	Площадь торговых залов, м^2	0,05	0,02	0,02		<	120
10	Издержки обращения, ден. ед.	3	1	2		<	8000

Рисунок 1 - Постановка условий задачи

2) Ввод зависимости из математической модели.

Ввести зависимость для целевой функции:

• курсор в ячейку Е5;

• курсор на кнопку Мастер функций;

• на экране диалоговое окно Мастер функций – шаг 1 из 2;

• курсор в окно Категория на категорию Математические;

• курсор в окно Функции на СУММПРОИЗВ;

• на экране диалоговое окно (рис. 2);

• в массив 1 ввести В$3:D$3;

• в массив 2 ввести B5:D5;

• курсор на кнопку ОК;

• на экране (рис. 3) в ячейке Е5 введены значения целевой функции.

Ввести зависимости для левых частей ограничений:

• курсор в ячейку Е5;

• курсор на кнопку Копировать;

• курсор в ячейку Е8;

• курсор на кнопку Вставить;

• скопировать в Е9:Е10;

- на экране (рис. 3) в ячейки Е15:Е17 введены функции.

Рисунок 2 - Мастер функций

	А	В	С	D	E	F	G
1		Переменные
2	имя	Товар А	Товар В	Товар С
3	значение
4					ЦФ	направление
5	коэффициент в ЦБ	3	5	4	=СУММПРОИЗВ (В$3:D$3;B5:D5)	макс
6		Ограничения
7	вид	Товар А	Товар В	Товар С	левая часть	знак	правая часть
8	Рабочее время, чел.-час.	0,1	0,2	0,4	=СУММПРОИЗВ (В$3:D$3;B8:D8)	<	1100
9	Площадь торговых залов, м^2	0,05	0,02	0,02	=СУММПРОИЗВ (В$3:D$3;B9:D9)	<	120
10	Издержки обращения, ден. ед.	3	1	2	=СУММПРОИЗВ (В$3:D$3;B10:D10)	<	8000

Рисунок 3 - Ввод зависимостей

3) Решение задачи.

Ввести данные в окно поиска решения. Для этого:

• выполнить команду Сервис → Поиск решения;

• на экране диалоговое окно Поиск решения (рис. 4);

• в окне Поиск решения в поле Установить целевую ячейку ввести ссылку $E$5, щелкнув по ячейке с целевой функцией Е5 на рабочем листе;

• в группе Равной установить опцию Максимальному значению;

• в поле Изменяя ячейки ввести диапазон ячеек $B$3:$D$3, выделив их на рабочем листе;

• нажать на кнопку Добавить для ввода ограничений;

• в окне Добавление ограничения в поле Ссылка на ячейку ввести ссылку на ячейку, содержащую формулу левой части текущего ограничения, например $E$8, щелкнув по этой ячейке на рабочем листе. Затем выбрать из раскрывающегося списка в средней части поля нужное ограничение (<=). В правую часть поля ввести значение правой части ограничения (число 1100 или адрес G8 с этим числом). Далее нажать на кнопку Добавить, чтобы ввести следующие ограничения. После добавления всех ограничений нажать на кнопку ОК для возвращения в окно Поиск решения (рис. 4);

Рисунок 4 - Диалоговое окно «Поиск решения»

• если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений, то надо щелкнуть на кнопку Изменить или Удалить;

• в окне Поиск решения щелкнуть на кнопку Параметры и установить параметры поиска решения (рис. 5), т.е. установить флажок на Линейная модель и нажать на кнопку ОК;

Рисунок 5 - Диалоговое окно «Параметры поиска решения»

• в окне Поиск решения нажать на кнопку Выполнить;

• на экране появится диалоговое окно Результаты поиска решения (рис. 6);

• нажать на кнопку ОК.

Рисунок 6 - Диалоговое окно «Результаты поиска решения»

Результат оптимального решения приведен в таблице (рис. 7).

	А	В	С		D	E	F	G
1		Переменные
2	имя	Товар А		Товар В	Товар С
3	значение	250		5375	0
4						ЦФ	напр
5	коэффициент в ЦФ	3		5	4	27625	max
6				Ограничения
7	вид	Товар А		Товар В	Товар С	лев. часть	знак	прав. часть
8	Рабочее время, чел.-час.	0,1		0,2	0,4	1100	<=	1100
9	Площадь торговых залов, кв.м.	0,05		0,02	0,02	120	<=	120
10	Издержки обращения, ден. ед.	3		1	2	6125	<=	8000

Рисунок 7 - Результаты расчета