Часть 2

Определим такое количество каждого из наборов, чтобы доход после их реализации был максимальным.

На рабочем листе Наборы 5х3 подготовлено расчетная таблица. После ввода

формул она приобретает вид:

	B	C	D	E	F	G
3	Товары, имеющиеся на складе		Будет взято со склада:	Количество
4				Набор №1	Набор №2	Набор №3
5	Код:	Кол-во:
6	111		0	0	0	0
7	222		0	0	0	0
8	333		0	0	0	0
9	444		0	0	0	0
10	555		0	0	0	0
11		Прибыль по наборам (грн):		0,00	0,00	0,00
12		Прибыль всего (грн):		0,00

Расшифровка формул для колонки Будет взято со склада:

Будет взято со склада	Формулы в колонке "Будет взято со склада" суммируются произведение количество товара на число наборов. Программа сравнивает это значение с записанным в колонку "Количество на складе" Здесь выполняется одно из ограничений.
=$E$5*E6+$F$5*F6+$G$5*G6
=$E$5*E7+$F$5*F7+$G$5*G7
=$E$5*E8$+F$5*F8+$G$5*G8
=$E$5*E9+$F$5*F9+$G$5*G9
=$E$5*E10+$F$5*F10+$G$5*G10

Расшифровка формул в колонках Е,F,G.

Таблица связана формулами с таблицей на листе Цена, поэтому в блок ячеек Е6:G10 данные будут автоматически переносится из таблиц с листа Цена.

	E	F	G
5	набор №1	набор №2	набор №3
6	=Цена!D4	=Цена!F4	=Цена!Н4
7	=Цена!D5	=Цена!F5	=Цена!Н5
8	=Цена!D6	=Цена!F6	=Цена!Н6
9	=Цена!D7	=Цена!F7	=Цена!Н7
10	=Цена!D8	=Цена!F8	=Цена!Н8

Расшифровка формул в строке Прибыль по наборам:

=75*МАКС(Е5;0)^$I$10

=50*МАКС(F5;0)^$I$10

=40*МАКС(G5;0)^$I$10

Формула в строке Прибыль по наборам: на первом месте стоит цена набора, затем функция МАКС, которая выбирает максимальное значение из списка аргументов, в данном случае из тех чисел , которые в процессе пересчета появляются в ячейках Е5,F5,G5.

Результат умножения возводится в степень равное числу, которое стоит в ячейке I10, тем самым выполняются требования: при увеличении числа наборов выше некоторого значения, прибыль начнет уменьшаться. Так учитываются трудность реализации очень большого количества набора.

Формула в строке прибыль всего суммируют прибыли по каждому набору

=СУММ(Е12:G12)

Блок ячее С6:С10 необходимо заполнить в соответствии с условием задачи.

Вид расчетной таблицы после ввода данных:

B		C	D	E	F	G
3	Товары, имеющиеся на складе:		Будут взяты со склада:	Количество
4				набор №1	набор №2	набор №3
5	Код: Количество:
6	111	300		15	10	7
7	222	300		22	16	10
8	333	300		49	35	20
9	444	300		28	19	23
10	555	300		31	12	8
11		Прибыль по набору (грн):
12		Прибыль всего (грн):

Для автоматического заполнения ячеек таблицы расчетными данными необходимо:

• вызвать команду Поиск решение

• в диалоговом окне указать:

* адрес целевой ячейки - Е13,

* установить метку возле опции Максимальное значение,

* указать блок изменяемых ячеек – Е5:G5,

• наложить на изменяемые ячейки ограничение.

1-е ограничение Е5: G5 = целое, так как количество набора должно

быть целое число;

2-е ограничение С6:С10 > D6:D10, количества товара на складе; должно быть больше количества товара, которое будет взято со склада

3-е ограничение Е5:G5 > 2, этим сообщается программе, что она из всех возможных решений выбрана такое, которое обеспечит количество каждого набора равным или большим двух.

Запустить программу на выполнение.

Если решение для данных условий существует, программа сообщит об этом и

заполнит свободные ячейки расчетными данными.

Вид таблицы на листе Наборы 5х3 после действия команды Поиск решение.

B		C	D	E	F	G
3	Товары, имеющиеся		Будут взяты со склада:	Количество
4	на складе			набор №1	набор №2	набор №3
5	Код:	Количество:		2	2	7
6	111	300	96	15	10	7
7	222	300	142	22	16	10
8	333	300	300	49	35	20
9	444	300	246	28	19	23
10	555	300	139	31	12	8
11		Прибыль по набору (грн):		139,95	93,30	191,27
12		Прибыль всего (грн):		424,53

Из таблицы видно, что на большую прибыль- 424, 53 грн. от реализации наборов можно получить, если количество наборов будет следующее (ячейки Е5:G5): два набора №1, два набора №2 и семь наборов №3.

З а д а н и е для самостоятельной работы:

На листах рабочей книги: Условия 20х3, Цена 75, Цена 50, Цена 40, Заказы 20x3 подготовлены таблицы для решения задачи типа «двадцать товаров – три наборов». Используя методику решения предыдущие задачи, найти решение этой задачи.

120