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

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

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

Включение инструмента «Поиск решения» Инструмент «Поиск решения» располагается на ленте «Данные». Для включения инструмента “Поиск решения» необходимо щелкнуть по левой верхней пиктограмме «кнопка 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.

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

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

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

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

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

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

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

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