Решение задачи с использованием функции «Поиск решения»

Условие задачи:

Компания имеет три склада и шесть магазинов. Заказы на перевозку поступают из магазинов. Необходимо удовлетворить потребность магазинов в товарах, находящихся на складах, сохранив общие расходы на перевозку на минимальном уровне. Исходные данные приведены в ходе решения задачи.

Включение инструмента «Поиск решения» Инструмент «Поиск решения» располагается на ленте «Данные». Для включения инструмента “Поиск решения» необходимо щелкнуть по левой верхней пиктограмме «кнопка Office», в диалоговом окне выбрать «Параметры Excel».

В левой части окна «Параметры Excel»выбрать элемент «Надстройки» и нажать кнопку «Перейти», расположенную в нижней части окна.

После выполнения этой команды должно появиться диалоговое окно «Надстройки», в котором необходимо установить флажок в строке «Поиск решения» и нажать ОК.

 

Создание исходной таблицы.

-введите стоимость перевозок в соответствии с рисунком:

-введите таблицу потребности в товаре для каждого розничного магазина в соответствии с рисунком:

В диапазоне ячеек D15:F21 будут находиться изменяемые значения, поэтому введите начальное значение, например, 0 в весь этот диапазон.

 

- введите таблицу товарных запасов складов в соответствии с рисунком:

В строке 26 содержится информация об общем количестве товаров, находящихся на каждом из складов.

 

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

Строка 31 содержит формулы, по которым вычисляется стоимость перевозок.

Ячейка D31 содержит следующую формулу: =СУММПРОИЗВ(D4:D10; D15:D21)

Эта формула дает возможность вычислить общую стоимость перевозок с каждого склада. В ячейке G31, подводится общая стоимость перевозок для всех заказов: =СУММ(D31:F31)

 

 

Процедура поиска решения находит такие значения диапазона ячеек D15:F21, при которых магазин будет получать желаемое количество товара, а общая стоимость перевозок будет минимальна, с учетом следующих ограничений:

1. Количество необходимого товара для каждого магазина должно равняться количеству перевезенного:

C15=G15 C17=G17 C19=G19 C21=G21

C16=G16 C18=G18 C20=G20

2. Значения в изменяемых ячейках не могут быть отрицательными:

D15: F 21>=0

3. Количество перевезенного товара с каждого склада должно равняться запасам на соответствующем складе:

D22= D26 E22= E26 F22= F26

 

После того, как были произведены все расчеты, выберите команду Данные/Поиск решения.

В окне этой команды нужно ввести следующие значения:

· В поле Установить целевую ячейку укажите адрес ячейки G31 (Общий итог).

· Выберите опцию Равной значение Минимальному значению.

· В поле Изменяя ячейки укажите D15:F21.

· Затем нажмите кнопку Добавить и введите все перечисленные выше ограничения.

· После ввода ограничений нажмите кнопку Выполнить.

· После решения задачи можно выбрать одну из следующих возможностей:

- Заменить исходные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи

- Восстановить исходные значения в изменяемых ячейках.

- Создать несколько отчетов о процедуре поиска решения

- Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в Диспетчере сценариев.

· Выберите первую опцию – Сохранить найденное решение.

 

Самостоятельная работа

Решите с помощью инструмента «Поиск решения».

Создайте таблицу, в которой вычисляется прибыль от продажи трех видов продукции:

 

Наименование товара	Кол-во	Прибыль/шт	Доход
Товар А		13р	=Кол-во*Прибыль/шт
Товар В		18р	=Кол-во*Прибыль/шт
Товар С		22р	=Кол-во*Прибыль/шт
Всего	=сумм(...)		=сумм(...)

 

В столбце В отображено количество товара по каждому виду, в столбце С – прибыль от продажи одного изделия. В столбце D содержатся формулы, по которым вычисляется прибыль от продажи каждого вида продукции путем умножения их количества на прибыль от продажи одного изделия. Определить наибольшую прибыль от продажи изделий при следующих ограничениях:

· Общий объем производства –всего 300 изделий в день.

· Компании нужно произвести 50 изделий А для выполнения существующего заказа

· Компании нужно произвести 40 изделий В для выполнения планового заказа.

· Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не более 40 единиц этого изделия.

 

Результат представьте преподавателю.

контрольные вопросы

1. Назначение функции Подбор параметра.

2. Отличия функции Поиск решения.

3. Основные действия при выполнении процедуры поиска решения.