 |
Главная Случайная страница Контакты | Мы поможем в написании вашей работы! | |
Поиск решения
|
|
Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которые приводили бы к точно заданному значению, либо минимально или максимально возможному значение. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы.
В этой главе вы узнаете, что такое Поиск решения, когда его целесообразно применять и как с ним работать. Затем предложенный ряд примеров и упражнений поможет закрепить практические навыки работы с этим средством. В последнем разделе главы описаны возможные проблемы и ошибки, которые могут проявиться при использовании средства Поиск решения.
Назначение средства «Поиск решения»
Если говорить упрощенно, то средство Поиск решения путем изменения значений в заданных ячейках (такие ячейки называются изменяемыми ячейками) добивается того, чтобы в ячейке с формулой (называется целевой ячейкой) было или определенное (заданное вами) значение, либо чтобы эта формула принимала минимально или максимально возможное значение. Можно также наложить ограничения на изменяемые значения, причем эти ограничения могут быть как прямыми (например, значения не должны выходить из определенного интервала), так и опосредованными, когда несколько изменяемых значений связаны каким-либо соотношением и ограничение налагается на это соотношение.
Для примера рассмотрим задачу вычисления оптимальных цен и количеств театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В главе 1 эту задачу мы уже пробовали решить с помощью средства Подбор параметра. Напомним, что в театре имеются билеты трех категорий: детские, для взрослых и льготные (рис. 4.1).
В целевой ячейке В6 подсчитывается сумма стоимостей билетов всех категорий (для каждой категории билетов их стоимость подсчитывается как произведение количества билетов и их цены).
Рис. 4.1. Рабочий лист для поиска оптимальной цены театральных билетов
В главе 1 при использовании средства Подбор параметра за один раз мы могли изменять значение только одной переменной: значение цены или количество билетов одной категории. Средство Поиск решения также может изменять значения только одной переменной (т.е. может работать в режиме подбора параметра). Однако, чтобы оценить гибкость и мощь этого средства, следует использовать его для изменения нескольких переменных. Кроме того, это средство может добиться от целевой ячейки (точнее, от формулы, содержащейся в этой ячейке) не только определенного заранее значения, но и максимально или минимально возможного (для этой формулы) значения.
Кроме того, подчеркнем еще раз, в отличие от средства Подбор параметра средство Поиск решения позволяет налагать разнообразные ограничения на значения изменяемых переменных. Например, Поиск решения может найти, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 24 тыс. руб., при выполнении следующих ограничений.
♦ Цена детских билетов постоянна и равна 60 руб.
♦ Цена билетов для взрослых постоянна и равна 100 руб.
♦ Цена льготных билетов постоянна и равна 90 руб.
♦ Билетов каждой категории можно продать не более 100 шт.
♦ В театре может быть аншлаг, но «лишних билетиков» нет.
Поиск решения немедленно найдет решение этой задачи: для того, чтобы в кассе оказалось ровно 24 тыс. руб., необходимо продать 100 детских, 90 для взрослых и 100 льготных билетов. Поиск решения по специальному алгоритму перебирает возможные комбинации значений количеств билетов разной категории для того, чтобы найти искомое решение.
Другой пример решения задачи с ограничениями показан на рис. 4.2. Здесь некий завод производит три вида изделий, но может производить в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпускаемых изделий таким образом, чтобы получить максимальный доход. Ответ, который дает Поиск решения, показан на том же рис. 4.2.
Рис. 4.2. Определение оптимальной структуры производства
Когда применяется «Поиск решения»
Средство Поиск решения предназначено для решения задач оптимизации. Это средство широко применяется для решения финансовых, деловых задач и задач управления ресурсами (понимая «ресурсы» в самом широком смысле: персонал, оборудование, материалы, время и т.д.). Средство Поиск решения также применяется в научных исследованиях для решения математических уравнений и выполнения самых разнообразных расчетов.
Ранее мы быстро «прошли» средство Подбор параметра, которое часто можно использовать в тех же ситуациях, что и средство Поиск решения. Однако, поскольку Поиск решения очень мощное и более «разнообразное» в применении средство, чем Подбор параметра, его изучение займет у нас значительно больше времени и потребует от вас определенных усилий. Так как оба средства, Поиск решения и Подбор параметра, решают примерно одинаковый круг задач, рассмотрим подробнее, в чем же состоят основные преимущества первого перед вторым.
♦ Подбор параметра может работать только с одной изменяемой ячейкой (где записано значение некоторой переменной), тогда как Поиск решения может работать с несколькими изменяемыми ячейками. Максимальное количество изменяемых ячеек, с которыми одновременно может работать Поиск решения, равно 200.
♦ Поиск решения может найти изменяемые значения, которые обеспечат не только определенное заданное значение целевой ячейки (как это делает Подбор параметра), но и минимально или максимально возможное значение целевой ячейки (что Подбор параметра делать не может даже для одной изменяемой переменной).
♦ Поиск решения позволяет налагать ограничения (условия) на изменяемые значения (Подбор параметра такого делать не позволяет). Средство Поиск решения позволяет задавать до 500 ограничений — по два простых односторонних ограничения на значения каждой из 200 возможных изменяемых переменных и дополнительно еще 100 ограничений. (Отметим, что если в диалоговом окне Параметры поиска решения установлен флажок Линейная модель, то в этом случае количество ограничений теоретически не ограничено.)
♦ Установки средства Подбор параметра в рабочей книге не сохраняются, тогда как установки для последнего выполнения средства Поиск решения сохраняются автоматически. Более того, можно сохранить установки для нескольких выполнений средства Поиск решения (такие установки этого средства называются моделью). В дальнейшем можно легко воспроизвести любую из сохраненных моделей и заново выполнить средство Поиск решения.
Средство Подбор параметра также имеет одно преимущество перед Поиск решения — Подбор параметра можно использовать тогда, когда изменяемая ячейка и целевая ячейка с формулой находятся на разных рабочих листах. Для средства Поиск решения изменяемые ячейки и целевая ячейка должны находиться на одном рабочем листе.
Теперь, заинтриговав читателя выдающимися свойствами средства Поиск решения, приступим к изучению того, как с ним работать и как его применить для решения своих задач.
Работа со средством «Поиск решения»
Прежде чем перейти к непосредственной работе со средством Поиск решения, изучим терминологию, которую необходимо знать
для квалифицированной работы с этим средством. В нелегком деле освоения новой терминологии нам поможет рис. 4.3, где показан рабочий лист для подсчета программистских «багов» (ошибок) в процессе выполнения некоторого проекта по разработке программного обеспечения.
♦ Целевая ячейка — ячейка с формулой, в которой Поиск решения установит заданное значение или для которой найдет минимально или максимально возможные значения. На рис. 4.3 любая ячейка из диапазонов B6:F7 и G2:H7 может быть целевой.
♦ Целевая функция — это термин из теории оптимизации, который описывает цель, которую мы хотим достичь, решая данную задачу (и используя для этого Поиск решения). Здесь «цель» заключается в том, чтобы формула в целевой ячейке достигла определенного значения. Например, выбирая ячейку G7 в качестве целевой ячейки (см. рис. 4.3), мы хотим, чтобы средство Поиск решения нашло такие значения в изменяемых ячейках, которые обеспечили бы в этой ячейке установление значения 25.
♦ Изменяемые ячейки — ячейки, значения в которых будет варьировать Поиск решения для того, чтобы достичь требуемого значения целевой функции. На рис. 4.3 изменяемыми ячейками могут быть любые ячейки из диапазона B2:F5.
♦ Ограничения — условия, налагаемые на возможные значения изменяемых ячеек. Для примера на рис. 4.3, если в качестве изменяемых ячеек задать диапазон ячеек B2:F5, то на значения этих ячеек можно наложить ограничения двух типов. Во-первых, эти значения не должны быть меньше 2. Во-вторых, эти значения должны быть целыми числами.
♦ Модель — совокупность адресов целевой и изменяемых ячеек, а также всех ограничений, используемых средством Поиск решения для решения текущей задачи, которые оно сохранило как единое целое.
Если применить средство Поиск решения к данным на рабочем листе (см. рис. 4.3), указав в качестве целевой ячейки ячейку G7 и значение 25 как значение целевой функции, указав в качестве изменяемых ячеек диапазон В2:В5, на значения которых налагаются ограничения: эти значения не должны быть меньше 2 и должны быть целыми числами, то Поиск решения найдет решение, которое показано на рис. 4.4.
Рис. 4.4. Решение, найденное средством Поиск решения для текущей модели
Установка средства «Поиск решения»
Поскольку средство Поиск решения не всегда устанавливается при инсталляции программы Excel, прежде чем использовать это средство, вы должны удостовериться, что оно для вас доступно, т.е. убедитесь, что в меню Сервис есть команда Поиск решения. Если в этом меню нет такой команды, выберите команду Сервис => Надстройки и в открывшемся диалоговом окне Надстройки в списке Доступные надстройки установите флажок Поиск решения. Затем щелкните на кнопке ОК — в меню Сервис должна появиться команда Поиск решения.
Если в списке Доступные надстройки диалогового окна Надстройки нет опции Поиск решения, необходимо переустановить саму программу Excel и в процессе ее переустановки выбрать Поиск решения в списке доступных надстроек. После завершения переустановки Excel в меню Сервис должна появиться команда Поиск решения.
Параметры диалогового окна «Поиск решения»
После выбора команды Сервис => Поиск решения открывается одноименное диалоговое окно, показанное на рис. 4.5.
Диалоговое окно Поиск решения содержит следующие элементы управления (перечисление идет сверху вниз и слева направо).
♦ В поле ввода Установить целевую ячейку вводится адрес ячейки рабочего листа, обязательно содержащей формулу, играющую роль целевой функции.
♦ Выбором одного из трех переключателей в области Равной вы указываете, какое значение должна принимать целевая функция.
• Выбор переключателя максимальному значению указывает, что целевая функция должна достичь своего наибольшего значения при условии выполнения всех ограничений.
• Выбор переключателя минимальному значению указывает, что целевая функция должна достичь своего наименьшего значения при условии выполнения всех ограничений.
• Выбор переключателя значению указывает, что целевая функция при условии выполнения всех ограничений должна достичь определенного значения, которое задается в соседнем поле ввода. По умолчанию это значение равно 0.
♦ В поле ввода Изменяя ячейки вводятся адреса изменяемых ячеек, значения которых Поиск решения будет варьировать при попытке установить в целевой ячейке заданное значение. Формула в целевой ячейке обязательно должна ссылаться, прямо или опосредованно (через другие промежуточные формулы), на эти ячейки.
♦ Кнопка Предположить используется для автоматического поиска ячеек, содержащих значения (не формулы) и влияющих на формулу в целевой ячейке.
♦ Список Ограничения содержит перечень всех ограничений, установленных для данной задачи.
♦ Щелчок на кнопке Добавить открывает диалоговое окно Добавление ограничения, где можно задать новое ограничение.
♦ Щелчок на кнопке Изменить открывает диалоговое окно Изменение ограничения, где можно изменить ограничение, предварительно выбранное в списке Ограничения.
♦ Щелчок на кнопке Удалить удаляет ограничение, выбранное в списке Ограничения.
♦ Щелчок на кнопке Выполнить — начало работы средства Поиск решения.
♦ Щелчок на кнопке Закрыть закрывает диалоговое окно Поиск решения, при этом данное средство не начинает работу.
После щелчка на кнопке Закрыть средство Поиск решения хотя не начинает свою работу, однако сохраняет все сделанные в диалоговом окне Поиск решения установки. Поэтому при следующем открытии данного окна вы их увидите снова, если, конечно, активным будет тот же самый рабочий лист.
♦ Щелчок на кнопке Параметры открывает диалоговое окно Параметры поиска решения, где можно задать дополнительные установки и параметры для решения данной задачи.
♦ Щелчок на кнопке Восстановить очищает диалоговое окно Поиск решения от всех сделанных установок и показывает его в первозданно чистом виде со значениями элементов управления, принятыми по умолчанию.
♦ Щелчок на кнопке Справка открывает тему справочной системы Excel, посвященную работе с этим диалоговым окном. (Аналогичные действия выполняют кнопки Справка в других диалоговых окнах средства Поиск решения. Поэтому данную кнопку при описании других диалоговых окон мы упоминать не будем.)
Итак, для того, чтобы заставить Поиск решения выполнить свое предназначение, следуйте инструкции.
1. Выберите команду Сервис =>Поиск решения. Откроется диалоговое окно Поиск решения.
2. Если вы хотите работать с чистым окном Поиск решения, щелкните на кнопке Восстановить.
3. Щелкните в поле ввода Установить целевую ячейку и введите адрес целевой ячейки (или просто щелкните на этой ячейке рабочего листа).
4. Установите один из переключателей в области Равной. Если вы установили переключатель значению, то введите соответствующее число.
5. Щелкните в поле ввода Изменяя ячейки и введите адреса или выделите на рабочем листе изменяемые ячейки.
6. Если необходимо ввести ограничения, щелкните на кнопке Добавить и в открывшемся диалоговом окне Добавление ограничения создайте ограничение. О том, как создавать ограничения, речь идет в следующем подразделе.
7. Щелкните на кнопке Выполнить.
8. По завершении работы средства Поиск решения откроется окно Результаты поиска решения, где щелкните на кнопке ОК.
В следующих разделах показано, как создавать и изменять ограничения, как работать с диалоговыми окнами Параметры поиска решения и Результаты поиска решения, как сохранять и загружать модели.
Дата публикования: 2015-01-10; Прочитано: 1349 | Нарушение авторского права страницы | Мы поможем в написании вашей работы!
