Работа в диалоговом окне «Поиск решения»

1. Выберите последовательно опции меню Сервис, Поиск решения. На экране появится соответствующее окно (рисунок 7).

Рисунок 7 – Окно «Поиск решения»

Установить целевую ячейку- определяет целевую ячейку, значение которой необходимо максимизировать или минимизировать, или сделать равным конкретному значению.

Изменяя ячейки- определяет изменяемые ячейки. Изменяемая ячейка- это ячейка, которая может быть изменена в процессе Поиска Решения для достижения нужного результата в ячейке из окна Установить целевую ячейку с удовлетворением поставленных ограничений.

Предположить- отыскивает все неформульные ячейки, прямо или непрямо зависящие от формулы в окне Установить целевую ячейку, и помещает их ссылки в окно Изменяя ячейки.

Ограничения- перечисляет текущие ограничения в данной проблеме.

Добавить- выводит окно диалога «Добавить ограничение», в котором можно добавить ограничения к текущей проблеме.

Изменить- выводит окно диалога «Изменить ограничение», в котором можно модифицировать имеющиеся ограничения.

Удалить- удалить выделенное ограничение.

Выполнить- запускает процесс решения определенной проблемы.

Закрыть- закрывает окно диалога, не решая проблемы. Сохраняются лишь изменения, сделанные при помощи кнопок Параметры, Добавить, Изменить и Удалить. Не сохраняются изменения, произведенные после использования данных кнопок.

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

Восстановить- очищает все текущие установки проблемы и возвращает все параметры к их значениям по умолчанию.

Курсор ввода с клавиатуры установлен в поле Установить целевую ячейку. Сюда необходимо внести адрес ячейки, содержащей целевую функцию. Для того чтобы сделать это щелкните мышью на той ячейке рабочего листа, где содержится ЦФ (F6). Вокруг F6 появился движущийся пунктирный контур, а в поле окна- соответствующий адрес. Следует отметить, что подобным способом можно вносить все остальные необходимые данные, это удобнее, чем вводить их с клавиатуры.

2. В поле Равной выберите флажок Максимальному значению.

3. Введите адреса искомых переменных, для этого выделите мышью область таблицы B3:E3.

4. Ввод ограничений задачи. Щелкните на кнопке Добавить. На экране появилось окно «Добавление ограничения» (рисунок 8). Excel воспринимает ограничения в виде ссылок на ячейки в которых содержатся соответствующие формулы, при этом левая часть ограничения представляет собой, как правило, ссылку на формулу, а правая- значение: число или ссылку на ячейку, содержащую значение. Адреса ячеек должны содержать символ «$». Если определяется интервал ячеек, то он должен быть той же формы и тех же размеров, что и интервал в окне Ссылка на ячейку.

Рисунок 8 – Добавление ограничения

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

Ограничение- определяет условие, налагаемое на содержимое окна Ссылка на ячейку. Выберите из списка отношение, которое нужно установить между ячейкой или интервалом и ограничением, которое нужно ввести в окне справа от списка. Можно выбрать <=, =, >=, или «цел». Если Вы выбрали «цел» для указания на то. что переменная должна быть целочисленной, то слово «Целое» появляется в окне справа от списка.

Добавить- в окне диалога «Добавить ограничение» можно добавить новое ограничение без возврата в диалог «Параметры поиска решений».

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

5. Установка параметров решения. Щелкните мышью по кнопке Параметры. На экране появится окно «Параметры поиска решения» (рисунок 9).

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

Рисунок 9 – Параметры поиска решения

Максимальное время- ограничивает время, требующееся для процесса отыскания решения. Это значение должно быть положительным целым числом. Значение по умолчанию равно 100 (секунд), что вполне годится для большинства малых задач, хотя Вы можете ввести любое значение до 32767.

Число итераций- ограничивает время, требующееся для процесса отыскания решения, путем ограничения числа промежуточных вычислений. Это значение должно быть положительным целым числом до 32767.

Точность- контролирует точность ответов, получаемых при поиске решений. Число, вводимое в поле Точность:

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

· должно быть дробным числом от 0 до 1 (не включая концы).

· имеет значение по умолчанию равно 0,000001.

указывает на меньшую точность, если число введено с меньшим количеством десятичных знаков; например, 0,0001.

Вообще говоря, чем большая точность определяется (чем меньше число), тем больше времени понадобится для поиска решения. Методы, используемые Поиском Решения, позволяют существенно ускорить поиск, если установить исходное значение, достаточно близкое к искомому решению.

Допустимое отклонение- проблемы, связанные с изменяемыми ячейками, которые должны содержать целые значения, могут требовать большого количества времени, так как при этом необходимо решать несколько подпроблем, каждая из которых есть задача для Поиска Решений с целочисленными ограничениями. Можно подобрать величину отклонения, которая представляет процент допустимого отклонения от оптимального решения при целочисленных ограничениях для всех элементов задачи. Чем выше отклонение (допустимое отклонение в процентах), тем быстрее процесс решения. Установка отклонения не играет роли, если не введены целочисленные ограничения.

Линейная модель- ускоряет процесс отыскания решения. Команда может быть использована только, если все связи в модели линейны.

Показать результаты итераций- прерывает Поиск Решения и показывает результаты после каждой итерации.

Автоматический масштаб- включает автоматический масштаб. Это полезно, когда параметры ввода (Изменяя ячейки) и вывода (Установить целевую ячейку и Ограничения) сильно различаются по величине; например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Оценка- эти флажки определяют подход, используемый для получения исходных оценок основных переменных в каждом одномерном поиске.

· линейная- использует линейную экстраполяцию вдоль касательного вектора.

· квадратичная- использует квадратичную экстраполяцию; это дает лучшие результаты для нелинейных проблем.

Производная- параметры группы Производная определяют способ вычисления производной при оценке частных производных целевых и ограничивающих функций. Эти варианты существенно отличаются своим действием на функциях, чье графическое представление недостаточно гладко или непрерывно. Для таких функций следует использовать вариант Центральная.

· прямая- такой способ дифференцирования установлен по умолчанию.

· центральная- этот способ требует больше вычислений на рабочем листе, но он может помочь в тех случаях, когда Вы получаете сообщение о том, что Поиск Решений не может улучшить решение.

Метод- параметры метод определяют, какой алгоритм поиска используется при каждой итерации для направления поиска. Нужно указать либо метод Ньютона, либо метод сопряженного градиента.

· метод Ньютона- это метод поиска по умолчанию, использующий квази-ньютоновский подход. Этот метод обычно требует больше памяти, чем метод сопряженного градиента, но меньшее количество итераций.

· метод сопряженного градиента- поиск методом сопряженного градиента требует меньше памяти, чем ньютоновский метод, но обычно большее число итераций для достижения конкретного уровня точности. Если проблема достаточно велика и важно экономное использование памяти, то стоит применить этот метод. Он также особенно полезен, если Вы видите, что последовательные итерации дают слишком малое отличие последовательных приближений.

Загрузить модель- выводит окно диалога «Загрузить Модель», в котором можно указать, какую именно модель нужно загрузить.

Сохранить модель- выводит окно диалога «Сохранить Модель», в котором можно указать, где именно нужно сохранить данную модель. Используйте кнопку Сохранить модель только в том случае, если нужно сохранить более, чем одну модель Поиска Решения вместе с данным рабочим листом. Первая модель Поиска Решений автоматически сохраняется вместе с рабочим листом.

Установите флажок Линейная модель, остальные параметры будем использовать по умолчанию.

6). Нажмите OK, затем кнопку Выполнить в окне «Поиск решения». Через некоторое время на экране появится окно «Результаты поиска решения» (рисунок 10).

Рисунок 10 – Результаты поиска решения

Окно диалога «Результаты поиска решения» выводит результаты последнего вычисления, используя значения ячеек, наиболее близкие к нужному решению.

Когда Поиск Решения завершает попытки отыскания решения, то на экран в верху окна диалога «Результаты поиска решений» выводится сообщение о завершении.

Сохранить найденное решение- принимает решение, найденное Поиском Решения, и подставляет найденные значения в соответствующие ячейки.

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

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

Отчеты- создает указанный тип отчета. Каждый отчет появляется на отдельном листе рабочей книги.

· Результаты- перечисляет изменяемые ячейки и ячейку в окне Установить целевую ячейку вместе с исходным и конечным значением. Также показывает ограничения и информацию о них.

· Устойчивость- предоставляет информацию о том, насколько чувствительно решение к малым изменениям в формуле окна Установить целевую ячейку или ограничениях. Для нелинейных моделей, отчет предоставляет двойственные значения (нормированные градиенты и множители Лагранжа). Для линейных моделей отчет включает редуцированную стоимость, теневые цены, objective coefficient (с допустимыми отклонениями в обе стороны), и ограничения на изменение правой стороны равенства.

· Пределы- перечисляет изменяемые ячейки вместе с соответствующими значениями, ячейку в окне Установить целевую ячейку, верхние и нижние пределы и целевые значения. Нижний предел есть наименьшее значение, которое может находиться в изменяемой ячейке, если фиксировать остальные ячейки и удовлетворить все ограничения. Верхний предел есть наибольшее значение. Целевое значение есть значение ячейки в окне Установить целевую ячейку, когда значение изменяемой ячейки достигает наименьшего или наибольшего предела.

Результаты поиска появятся в таблице (рисунок 11).

Рисунок 11 – Результаты работы надстройки «Поиск решения»

На рисунке 5.11 видно, что в оптимальном решении Продукт1=B3=10; Продукт2=C3=0; Продукт3=D3=6; Продукт4=E3=0. При этом максимальная прибыль будет составлять F6=1320, а количество использованных ресурсов равно: трудовых=F9=16, сырья=F10=84, финансов=F11=100.

Таково оптимальное решение рассматриваемой задачи распределения ресурсов. Однако решение задачи находится не всегда. Если условия задачи несовместны, на экране появится сообщение о невозможности поиска подходящего результата. Если целевая функция неограничена, то на экране появится сообщение «Значения целевой ячейки не сходятся». В любом случае, придется перепроверить заданные параметры поиска.

Задание

1. Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице 2.

Таблица 2 – Нормы затрат каждого сырья на единицу продукции

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

2. Фирма «Корма» имеет возможность покупать 4 различных вида зерна(компонентов смеси) и изготавливать различные виды кормов. Разные зерновые культуры содержат разное количество питательных ингредиентов. Произведенный комбикорм должен удовлетворять некоторым минимальным требованиям с точки зрения питательности. Требуется определить, какая из возможных смесей является самой дешевой. Исходные данные приведены в таблице 3.

Таблица 3 – Исходные данные

3. Фермер в своем хозяйстве может одновременно откармливать до 500 уток и гусей. Откорм одной утки обходится (в среднем) 10 руб., а одного гуся – в 30 руб. Магазин согласен закупить откормленных птиц по следующим ценам: 30 руб. за утку, 70 руб. за гуся. Фермер может выделить на откорм птиц 9000 руб. В каком количестве следует откармливать уток и гусей, чтобы получить максимальную прибыль?

4. Для похода решили приобрести партию консервов. Все консервы должны содержать не менее 2 кг мяса, не менее 5 кг крупяных изделий и не менее 1 кг жиров. В магазине имелись в продаже консервы 2 видов.

а) Сколько нужно купить консервов каждого вида, чтобы при этом затратить меньшее количество денег?

б) Сколько нужно купить консервов каждого типа, чтобы их суммарный вес был минимальным?

5. Некий предприниматель заказывает телевизионной компании рекламу своей продукции. Для привлечения зрителей он хочет воспользоваться выступлением известного певца и популярного оркестра. На передачу отведено 30 минут. Певец соглашается петь не более 22 минут. Заказчик настаивает на том, чтобы передача рекламы продолжалась не менее 3 минут, а телевизионная компания требует, чтобы передача рекламы продолжалась не более 15 минут. Определить, при каком распределении времени между рекламой, певцом и оркестром стоимость передачи будет минимальной, если минута выступления певца стоит 200 долларов, оркестра - 250 долларов, минута рекламы - 50 долларов.

6. На звероферме могут выращиваться черно-бурые лисицы и песцы. Для обеспечения нормальных условий их выращивания используется три вида кормов. Количество корма каждого вида, которое должны получать ежедневно лисицы и песцы, приведено в таблице. В ней же указаны общее количество корма каждого вида, которое может быть использовано зверофермой, и прибыль от реализации одной шкурки лисицы или песца.

Определить, сколько лисиц и песцов следует выращивать на звероферме, чтобы прибыль от реализации их шкурок была максимальной.

7. Предприятие может закупить полуфабрикаты Bl, B2 и ВЗ в количествах 400, 250 и 350 т соответственно. Цена полуфабрикатов - 400, 800 и 500 у.е. за тонну. В результате смешивания полуфабрикатов получают три вида продукции: А1, А2, A3. Пропорции смешиваемых полуфабрикатов следующие: для А1- 3:5:2, для А2 - 1:2:1, для A3 - 2:2:1. Цена 1 т продукции А1 - 1200 долларов, А2 - 1000 долларов, A3 -1500 долларов.

Продукции А1 необходимо выпустить не менее 20 тонн. Продукция А2 и A3 должна выпускаться в соотношении 1:10. Составить план выпуска продукции, обеспечивающий предприятию максимальную прибыль.

8. Кондитерский цех выпускает конфеты, шоколад, пряники и печенье. Известны рецепты каждого изделия (количество сахара, масла, какао и муки для их приготовления), цена 1 кг изделия и запас каждого ресурса на складе. Найти объем (в кг) конфет, шоколада, пряников и печенья, который нужно производить, исходя из наличия ресурсов, для получения максимальной прибыли.