Поиск решения – ЧАСТЬ 2

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

Выделив одно из ограничений в списке Ограничения и нажав кнопку Изменить, пользователь получает возможность изменить данное ограничение. Изменение ограничения будет выполняться в открывающемся окне Изменение ограничения.

При нажатии кнопки Удалить происходит удаление выделенного ограничения.

Нажатие кнопки Закрыть закрывает диалоговое окно Поиск решения, при этом не начинаются вычисления. При закрытии окна сохраняются все сделанные настройки параметров.

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

Кнопка Параметры служит для перехода в диалоговое окно Параметры поиска решения, в котором можно задать дополнительные параметры.

Для вызова справочной системы Excel следует нажать кнопку Справка. Аналогичные действия  выполняют  кнопки Справка в других диалоговых  окнах надстройки  Поиск решения.

Нажатие  кнопки  Выполнить  вызывает  запуск  вычислений. При  необходимости пользователь может прервать  работу, нажав клавишу Esc. В этом случае рабочий лист пересчитывается с теми значениями изменяемых ячеек, которые имели место во время последней прерванной  итерации.

Диалоговое окно Параметры поиска решения

При необходимости задать дополнительные параметры  процесса поиска решения  следует  открыть  диалоговое  окно Параметры поиска  решения (кнопка  Параметры), показанное  на рис. 2.53.

Рис. 2.53. Диалоговое окно Параметры поиска решения

В поле ввода Максимальное время задается  максимальное время решения  задачи (в секундах). Значение, принятое по умолчанию — 100 секунд. Такого интервала времени достаточно для решения большинства несложных задач. Максимальное значение,  которое можно ввести в это поле, составляет  32 767 секунд. Если решение не будет найдено в течение указанного  времени, то появится диалоговое  окно Текущее состояние поиска  решения, предлагающее  два варианта  дальнейших действий: остановка или продолжение работы в течение еще одного интервала времени.

В поле ввода Предельное число  итераций  задается  максимальное число  итераций,  выполняемых при поиске решения. Максимально допустимое  число итераций  — 32 767. Заданного по умолчанию значения (100 итераций) достаточно для решения несложных задач. Если решение не будет найдено в течение заданного количества  итераций,  открывается диалоговое  окно Текущее состояние поиска решения. В этом окне пользователю предлагается два варианта  дальнейших действий:  остановка вычислений или продолжение вычислений в течение  еще такого же количества  итераций.

В поле  ввода  Относительная  погрешность  пользователь может  задать  точность выполнения ограничений.  Ограничение считается  выполненным в виде равенства, если абсолютные разности  значений  правой  и левой частей ограничения не  выходят  за интервал,  определяемый параметром  Относительная  погрешность. Величина  относительной погрешности  в этом поле должна быть дробным числом в интервале  от 0 до 1. Чем меньше заданная  величина  относительной погрешности, тем точнее полученный результат. При задании  большей величины относительной погрешности  решение будет найдено быстрее. При выполнении обычных  расчетов  значение  параметра Относительная погрешность  принимается в пределах от 0,0001 до 0,000001.

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

Данный  параметр  может  использоваться для  ускорения  вычислений. Задание большего значения в этом поле снижает время вычислений, но одновременно с этим снижается и точность найденного решения. По умолчанию принимается значение допустимого отклонения равного 5 %.

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

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

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

ВНИМАНИЕ

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

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

СОВЕТ

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

При установке  флажка  Показывать результаты итераций вычисления приостанавливаются  после выполнения очередной  итерации. После этого на экран выводится диалоговое окно Текущее состояние поиска решения с результатами данной итерации.  Нажатие кнопок Продолжить или Стоп в окне Текущее состояние поиска решения позво-

ляет соответственно продолжить или остановить вычисления. Нажатие кнопки Сохранить сценарий позволяет  сохранить текущее решение в именованном  сценарии.

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

✓                    Переключатель линейная установлен  по умолчанию.  Этот параметр  устанав-

ливается  при решении линейной  задачи. При установке этого переключателя

вычисления выполняются быстрее, чем при установленном переключателе

квадратичная, однако точность решения понижается.

✓                    Установка переключателя квадратичная означает использование квадратичной

экстраполяции, которая дает более точные результаты  при решении нелиней-

ных задач.

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

✓                    Переключатель прямые установлен  по умолчанию.  Этот вариант  настройки

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

носительно невысока.

✓                    При  положении  переключателя центральные  в вычислениях  используются

центральные разности, применимые к функциям, имеющим разрывные  про-

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

Переключатели в области Метод поиска предназначены для выбора алгоритма  оптимизации для решения нелинейной задачи.

✓                    При  выборе  переключателя Ньютона  при вычислениях  используется моди-

фицированный метод Ньютона.  При этом для вычислений требуется  больше

оперативной памяти, но выполняется меньше итераций.

✓                    При установке  переключателя сопряженных градиентов используется метод со-

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

достаточно сложна, необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.

При нажатии кнопки ОК происходит сохранение значений параметров диалогового  окна Параметры поиска решения и возврат в диалоговое окно Поиск решения.

Нажатие  кнопки Отмена не сохраняет  сделанные установки значений  параметров  диалогового окна Параметры поиска решения, при этом происходит возврат в диалоговое окно Поиск решения.

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

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

СОВЕТ

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

Диалоговое окно Результаты поиска решения

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

Рис. 2.54. Диалоговое окно Результаты поиска решения

Если решение найдено, то в диалоговом окне Результаты поиска решения может отобразиться  одно из двух следующих сообщений.

✓                    Решение найдено. Все ограничения и условия  оптимальности выполнены.

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

✓                    Поиск свелся к текущему решению. Все ограничения выполнены.

Это сообщение может появиться только при решении нелинейных задач.

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

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

✓                    При установке переключателя Сохранить найденное решение пользователь при-

нимает найденное решение, и Excel помещает в изменяемые ячейки значения,

составляющие найденное решение.

✓                    При выборе переключателя Восстановить исходные значения происходит восста-

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

✓                    Если в списке Тип отчета выбрать нужные типы отчетов с результатами поиска

решения, Excel создаст такие отчеты на отдельных листах рабочей книги.

✓                    Нажатие кнопки ОК используется для того, чтобы принять установки, сделанные

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

✓                    Нажатие кнопки Отмена используется для восстановления исходных значений

в изменяемых ячейках.

✓                    Нажатие кнопки Сохранить сценарий открывает диалоговое окно Диспетчер сцена-

риев, позволяющее сохранить значения изменяемых ячеек в качестве сценария

Excel.

Диалоговое окно Текущее состояние поиска решения

Диалоговое  окно Текущее состояние поиска решения, показанное  на рис. 2.55, выводится в следующих случаях:

✓                    в диалоговом  окне Параметры поиска  решения установлен флажок  Показывать

результаты итераций;

✓                    достигнут временной предел вычислений, установленный в поле Максимальное

время в диалоговом окне Параметры поиска решения;

✓                    выполнено  предельное  количество  итераций,  заданное в диалоговом  окне Па-

раметры поиска решения.

Рис. 2.55. Диалоговое окно Текущее состояние поиска решения

В этом окне могут быть выведены несколько вариантов сообщений.

✓                    Поиск  решения  приостановлен. Текущие  значения  показаны  на рабочем

листе.

Это сообщение выводится,  когда в диалоговом  окне Параметры поиска решения

установлен  флажок Показывать результаты итераций.

✓                    Время, установленное для поиска решения, истекло. Продолжить?

Данное  сообщение  выводится,  когда время  вычислений достигло  значения,  установленного в поле Максимальное время диалогового  окна Параметры поиска решения.

✓                    Достигнуто максимальное число итераций. Продолжить?

Это сообщение появляется при выполнении максимального количества  итераций, установленого в поле Предельное число итераций диалогового окна Параметры поиска решения.

Нажатие кнопки Стоп в диалоговом окне Текущее состояние поиска решения завершает вычисления и отображает диалоговое окно Результаты поиска решения. Если нажать

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

Отчеты надстройки Поиск решения

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

✓                    В отчете Результаты  выводятся исходные  и полученные  в результате  поиска

решения значения изменяемых ячеек и целевой функции, а также сведения об

ограничениях задачи.

✓                    Отчет Устойчивость дает основную информацию для анализа чувствительности

линейных и нелинейных моделей. Этот анализ показывает, насколько чувстви-

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

✓                    Отчет Пределы представляет собой ограниченный вариант отчета Устойчивость.

Здесь показаны наименьшее и наибольшее значения, которые могут принимать

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

Для  создания  отчетов  в диалоговом  окне Результаты  поиска  решения  (рис.  2.54) в списке Тип отчета следует выбрать  один или несколько  типов отчетов и нажать кнопку  ОК. Соответствующие отчеты будут созданы на новых листах в текущей рабочей книге, каждый отчет на отдельном рабочем листе.

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

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

Отчет Результаты

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

На рис. 2.56 показан пример отчета по результатам.

Рис. 2.58. Лист Отчет по пределам при поиске решения

✓                    В первой таблице приведены:

•                    адрес целевой ячейки;

•                    ее имя (созданное заранее или составленное из заголовков строк и столбцов, на пересечении  которых находится  целевая ячейка);  если имени нет, то это поле остается пустым;

•                    значение в целевой ячейке после выполнения вычислений.

✓                    Во второй таблице приведены:

•                     адреса изменяемых ячеек;

•                     их имена (созданные заранее или составленные из заголовков строк и столбцов, на пересечении которых находятся изменяемые ячейки); если имен нет, то это поле остается пустым;

•                     значение в изменяемых ячейках после выполнения вычислений;

•                     нижняя граница  значений  изменяемой ячейки,  при которой  сохраняется текущее значение целевой функции;

•                     значение целевой функции при значении  изменяемой ячейки, равном нижней границе;

•                     верхняя  граница  значений  изменяемой ячейки,  при которой  сохраняется текущее значение целевой функции;

•                     значение целевой функции при значении изменяемой ячейки, равном верхней границе.

Если  на значения  изменяемой ячейки  не налагаются ограничения, задающие  ее верхнюю (или нижнюю) границы, то в столбцах Верхний предел и Целевой результат (или Нижний предел и Целевой результат) для этой переменной будут стоять значения ошибки #Н/Д.

Источник: Трусов А. Ф. Excel 2007 для менеджеров и экономистов: логистические,  производственные и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий