Цель работы: получить практические навыки использования функций “Поиск решения” в электронной таблице Excel.

В результате выполнения лабораторной работы студент должен:
• получить навыки решения оптимизационных задач, приводимых к табличным формам;
• научиться использовать средства электронной таблицы в задачах поиска нужного решения при условии изменения только одного параметра некоторой функции.

Использование функции “Поиск решения” при решении “Транспортной задачи”.

Постановка задачи:
Классическая формулировка задачи состоит в следующем. Имеется несколько пунктов производства и пунктов потребления некоторого продукта. Для каждого из пунктов производства задан объем производства, а для каждого пункта потребления – объем потребления. Известна стоимость перевозки из каждого пункта производства в каждый пункт потребления единицы продукта. Требуется составить план перевозок продукта, в котором все пункты потребления были бы обеспечены необходимыми продуктами, ни из какого пункта производства не вывозилось бы продуктов больше, чем там производится, а стоимость перевозки была бы минимальной.

Рис. 1. Таблица для решения “Транспортной задачи”.

В построенной при помощи Microsoft Excel модели представлена такая задача (см. рис.1). Товары могут доставляться из пункта производства (Белоруссия, Урал, Украина) в любой пункт потребления (Казань, Рига, Воронеж, Курск, Москва). Очевидно, что стоимость доставки на большее расстояние будет большей. Требуется определить объемы перевозок между каждым пунктом производства и пунктом потребления в соответствии с потребностями пунктов потребления и производственными возможностями пунктов производства, при которых транспортные расходы минимальны. Таким образом, цель задачи – уменьшение всех транспортных расходов.

Порядок выполнения.

Первый этап - ввод исходных данных:
1. Ввести на рабочем листе необходимые исходные данные и определить их взаимосвязи с результирующими данными:

1.1. Построить таблицы для ввода количества перевозок, цены перевозки и стоимости перевозки из пункта производства “Х” в пункт потребления “Y”, как показано на Рис.1 (количество перевозок для каждого пункта в начале решения задачи будет равно 0).

1.2. Ввести в ячейки С14-G14 потребности складов в товаре, а в ячейки В16-В18 – производственные возможности пунктов производства.

1.3. Ввести в ячейки С16-G18 цены на перевозку товара из пункта производства Х в пункт потребления Y.

2. Ввести формулы в вычисляемые ячейки:

2.1. В ячейки В8:В10 ввести формулы вычисления общего количества перевезенного товара для каждого из пунктов производства (например, формула для ячейки В8=СУММ(С8:G8), т.е. количество перевезенного товара для Белоруссии).

2.2. В ячейки С12:G12 ввести формулы вычисления общего количества перевезенного товара в каждый из пунктов потребления (например, формула для ячейки С12=СУММ(С8:С10), т.е. количество перевезенного товара в Казань).

2.3. В ячейки С20:G22 ввести формулы вычисления общей цены за перевозку товара из каждого пункта производства в каждый пункт потребления, умножив цену перевозки единицы товара (ячейки С16-G18) на общее количество перевезенного товара (ячейки С8-G10) (например, формула для ячейки С20 – общая цена перевозки товара из Белоруссии в Казань – =С8*С16).

2.4. В ячейки С24:G24 ввести формулы вычисления стоимости всех перевозок по каждому из пунктов потребления (например, для Казани в ячейку С24 вводится формула =СУММ(С20:С22)).

2.5. В ячейку В24 ввести формулу подсчета всей стоимости перевозок – результат суммирования значений ячеек С24:G24.

3. Выполнить форматирование ячеек рабочего листа, и выделить ячейки с результатами и изменяемыми данными – синим цветом, а ячейки с исходными данными – красным цветом.

Второй этап – поиск решения:
1. При помощи команды “Сервис” – “Поиск решения…” вызвать диалоговое окно задания данных для решения задачи (Рис.2).

2. Задать целевую ячейку

В качестве целевой ячейки выбрать ячейку (аналогичную ячейке В24 на рис. 1), в которой будет подсчитана общая цена всех перевозок.

По условию задачи целевую ячейку следует установить равной минимальному значению.

Рис. 2. Диалоговое окно ввода данных для решения задачи.

3. Задать изменяемые ячейки

Минимальное значение целевой ячейки будет определяться путем изменения данных в ячейках, задающих объемы перевозок от каждого из пунктов производства к каждому пункту потребления (ячейки C8:G10 на рис. 1).

4. Наложить требования (ограничения), которые будут предъявляться к результатам задачи:

4.1. Количество перевезенных грузов не может превышать производственных возможностей заводов (на рис. 1 значения ячеек B8:B10 должны быть меньше или равны значениям ячеек B16:B18).

4.2. Количество доставляемых грузов должно быть равно потребностям складов (т.е. на рис. 1 значения ячеек C12:G12 должны быть равны значениям ячеек С14:G14).

4.3. Число перевозок не может быть отрицательным и не целым (т.е. на рис. 1 значения ячеек C8:G10 должны быть больше или равны нулю и должны быть целыми).

5. Ввести значения в окно “Поиск решения”. Для ввода значений в диалоговое окно “Поиск решений” можно использовать выделение ячеек и интервалов мышью (при заполнении соответствующих полей ввода). Кроме того, в некоторых случаях удобно пользоваться для определения изменяемых ячеек кнопкой “Предположить” – в этом случае в качестве изменяемых ячеек предлагается использовать все влияющие ячейки для ранее определенной целевой ячейки.

Для ввода ограничений необходимо нажать кнопку “Добавить”.

На экране появится диалоговое окно, показанное на Рис.3.

Рис. 3. Окно ввода ограничений.

При помощи этого диалогового окна ввести ранее заданные ограничения. Для ввода значений в области “Ссылка на ячейку” и “Ограничение” можно также пользоваться возможностями Microsoft Excel по выделению интервалов мышью.

6. Инициировать "Поиск решения"

Решение задачи начинается после нажатия кнопки “Выполнить” в диалоговом окне “Поиск решения”. После того, как вычисления закончатся, открывается диалоговое окно “Результаты поиска решения” (Рис.4), в котором выводится сообщение о том, найдено или нет решение поставленной задачи. Если найденное решение устраивает пользователя, он может сохранить его на рабочем листе, нажав кнопку "ОК".

Можно также сохранить найденное решение в качестве сценария с помощью кнопки “Сохранить сценарий” (обычно так поступают в том случае, когда требуется сохранить результаты нескольких различных решений, полученных при изменении нескольких ограничений).

Рис. 4. Окно “Результаты поиска решения”.

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

7. Составить отчет о проделанной работе.

Рис.5. Результаты вычислений.

Отправить его преподавателю по электронной почте в виде вложенного файла или переписать файл преподавателю со своего носителя (флэш-накопителя, компакт-диска) или сдать работу преподавателю в распечатанном и скреплённом виде.