Тема 4. Применение электронных таблиц при решении оптимизационных задач (на примере электронной таблицы Excell)
Цель работы: получить практические навыки использования функций «Поиск решения» и «Подбор параметра» эл.таблицы Excell
В результате выполнения лабораторной работы студент должен:
· получить навыки решения оптимизационных задач, приводимых к табличным формам;
· научиться использовать средства электронной таблицы в задачах отыскания нужного решения при условии изменения только одного параметра некоторой функции.
Часть 1. Использование функции «Поиск решения» при решении «Транспортной задачи»
Постановка задачи:
Классическая формулировка задачи состоит в следующем: имеется несколько пунктов производства и пунктов потребления некоторого продукта. Для каждого из пунктов производства задан объем производства, а для каждого пункта потребления — объем потребления. Известна также стоимость перевозки из каждого пункта производства в каждый пункт потребления единицы продукта. Требуется составить план перевозок продукта, в котором все пункты потребления были бы обеспечены необходимыми продуктами, ни из какого пункта производства не вывозилось бы продуктов больше, чем там производится, а стоимость перевозки была бы минимальной.
 |
Рис. 2. Таблица для решения “Транспортной задачи”
В построенной при помощи MicrosoftExcel модели представлена такая задача (см. рис.2). Товары могут доставляться из пункта производства (в данном случае это Белоруссия, Урал, Украина) в любой пункт потребления (Казань, Рига, Воронеж, Курск, Москва). Однако, очевидно, что стоимость доставки на большее расстояние будет большей. Требуется определить объемы перевозок между каждым пунктом производства и пунктом потребления в соответствии с потребностями пунктов потребления и производственными возможностями пунктов производства, при которых транспортные расходы минимальны. Таким образом, цель задачи — уменьшение всех транспортных расходов.
Порядок выполнения
Первый этап - ввод исходных данных:
1. Ввести на рабочий лист необходимые исходные данные и определить их взаимосвязи с результирующими данными:
- построить таблицы для ввода количества перевозок, цены перевозки и стоимости перевозки из пункта производства Х в пункт потребления Y, как это показано на Рис.2 (количество перевозок для каждого пункта в начале решения задачи будет равно 0);
- ввести в ячейки С14-G14 потребности складов в товаре, а в ячейки В16-В18 - производственные возможности пунктов производства;
- ввести в ячейки С16-G18 цены на перевозку товара из пункта производства Х в пункт потребления Y.
2. Ввести формулы в вычисляемые ячейки:
- в ячейки В8:В10 ввести формулы вычисления общего количества перевезенного товара для каждого из пунктов производства (например, формула для ячейки В8 =СУММ(С8:G8), т.е. количество перевезенного товара для Белоруссии);
- в ячейки С12:G12 ввести формулы вычисления общего количества перевезенного товара в каждый из пунктов потребления (например, формула для ячейки С12 =СУММ(С8:С10), т.е. количество перевезенного товара в Казань);
- в ячейки С20:G22 ввести формулы вычисления общей цены за перевозку товара из каждого пункта производства в каждый пункт потребления, умножив цену перевозки единицы товара (ячейки С16-G18) на общее количество перевезенного товара (ячейки С8-G10) (например, формула для ячейки С20 - общая цена перевозки товара из Белоруссии в Казань - =С8*С16);
- в ячейки С24: G24 ввести формулы вычисления стоимости всех перевозок по каждому из пунктов потребления (например, для Казани в ячейку С24 вводится формула =СУММ(С20:С22));
- в ячейку В24 ввести формулу подсчета всей стоимости перевозок - результат суммирования значений ячеек С24:G24.
3. Выполнить форматирование ячеек рабочего листа, и выделить ячейки с результатами и изменяемыми данными - синим цветом, а ячейки с исходными данными - красным цветом.
Второй этап – поиск решения
1. При помощи команды “Сервис” — “Поиск решения…” вызвать диалоговое окно задания данных для решения задачи (Рис.3).
2. Задать целевую ячейку
В качестве целевой ячейки выбрать ячейку (аналогичную ячейке В24 на рис. 2), в которой будет подсчитана общая цена всех перевозок. По условию задачи целевую ячейку следует установить равной минимальному значению.
 |
Рис. 3. Диалоговое окно ввода данных для решения задачи
3. Задать изменяемые ячейки
Минимальное значение целевой ячейки будет определяться путем изменения данных в ячейках, задающих объемы перевозок от каждого из пунктов производства к каждому пункту потребления (ячейки C8:G10 на рис. 2).
4. Наложить требования (ограничения), которые будут предъявляться к результатам задачи:
- количество перевезенных грузов не может превышать производственных возможностей заводов (на рис. 2 значения ячеек B8:B10 должны быть меньше или равны значениям ячеек B16:B18);
- количество доставляемых грузов должно быть равно потребностям складов (т.е. на рис. 2 значения ячеек C12:G12 должны быть равны значениям ячеек С14:G14);
- число перевозок не может быть отрицательным и не целым (т.е. на рис. 2 значения ячеек C8:G10 должны быть больше или равны нулю и должны быть целыми);
5. Ввести значения в окно “Поиск решения”
Для ввода значений в диалоговое окно “Поиск решений” можно использовать выделение ячеек и интервалов мышью (при заполнении соответствующих полей ввода). Кроме того, в некоторых случаях удобно пользоваться для определения изменяемых ячеек кнопкой “Предположить” — в этом случае в качестве изменяемых ячеек предлагается использовать все влияющие ячейки для ранее определенной целевой ячейки.
Для ввода ограничений необходимо нажать кнопку “Добавить”. На экране появится диалоговое окно, показанное на Рис.4.
 |
Рис. 4. Окно ввода ограничений
При помощи этого диалогового окна ввести ранее заданные ограничения. Для ввода значений в области “Ссылка на ячейку” и “Ограничение” можно также пользоваться возможностями MicrosoftExcel по выделению интервалов мышью.
6. Инициировать Поиск решения
Решение задачи начинается после нажатия кнопки “Выполнить” в диалоговом окне “Поиск решения”. После того, как вычисления закончатся, открывается диалоговое окно “Результаты поиска решения” (Рис.5), в котором выводится сообщение о том, найдено или нет решение поставленной задачи. Если найденное решение устраивает пользователя, он может сохранить его на рабочем листе, нажав кнопку ОК.
Можно также сохранить найденное решение в качестве сценария с помощью кнопки “Сохранить сценарий” (обычно так поступают в том случае, когда требуется сохранить результаты нескольких различных решений, полученных при изменении нескольких ограничений).
 |
Рис. 5. Окно “Результаты поиска решения”
Оптимальное количество поставок, которое приведет к минимизации транспортных расходов в соответствии с заданными исходными данных представлено в таблице на Рис.6.
 |
7. Составить отчет о проделанной работе.
Рис.6. Результаты вычислений
Часть 2. Использование функции «Подбор параметра»
Команда “Подбор параметра” (из опции меню “Сервис”) применяется в тех случаях, когда должны быть произведены необходимые итерационные вычисления для отыскания нужного решения при условии изменения только одного параметра некоторой функции. При этом в качестве целевой ячейки выступает ячейка с заданной функцией, а в качестве изменяемой – ячейка со значением параметра, которое необходимо подобрать так, чтобы выполнялись некоторые сформулированные условия.
Постановка задачи: необходимо определить размер ежегодных отчислений на валютный счет так, чтобы на нем за 5 лет накопилось 25000 р. при условии 9% годовых.
Порядок выполнения
1. Задать исходные значения для решения задачи: «Ставка» (процентная ставка начислений по вкладу) и «Кол-во периодов» (количество лет) (см. здесь и далее рис.7).
2. Определить ячейку «Плата» для подбора значения ежегодных отчислений, не вводя в нее конкретных данных.
3. Записать в ячейку «Размер вклада» финансовую функцию БЗ (возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки), используя в качестве параметров ячейки «Ставка», «Кол-во периодов» и «Плата».
Синтаксис использования функции: БЗ(ставка;кпер;плата; нз;тип)
Параметры:
ставка — процентная ставка за период;
кпер — общее число периодов выплат годовой ренты;
плата — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента нз;
нз —текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плата.
тип — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0 (0 - в конце периода; 1 - в начале периода).
4. Инициировать функцию «Подбор параметра» и заполнить диалоговое окно, указав в качестве целевой ячейки ячейку, в которой записана функция БЗ, а в качестве изменяемой – ячейку «Плата».
5. Сохранить полученное решение.
6. Составить отчет.
Рис. 7. Использование метода «Подбор параметра»
Контрольные вопросы:
1. Сформулируйте условия, при которых можно применять метод «Поиск решения»
2. Сформулируйте условия, при которых можно применять метод «Подбор параметра»
3. Перечислите этапы решения задач методом «Поиск решения»
4. Приведите примеры задач, которые могут быть решены с помощью метода «Поиск решения»
5. Приведите примеры задач, которые могут быть решены с помощью метода «Подбор параметра»
Вперед к разделу "Тема 5. Проектирование и создание БД “Документы”"