Поиск решения. 9.3.1.Подбор параметра в ячейке

9.3.1. Подбор параметра в ячейке

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

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

9.3.2. Поиск оптимального значения в ячейке

Поиск решения является надстройкой Excel, которая подключается на вкладке Разработчик командой Надстройки. Эта надстройка применяется для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинации переменных, которые оптимизируют значение в одной ячейке, называемой целевой.

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

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

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

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

· максимальное время поиска;

· предельное число итераций и относительная погрешность;

· параметры процедур линейной оптимизации и метод поиска;

· возможность сохранения и последующего применения параметров поиска.

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

Примеры решения сложных задач оптимизации находятся в книге SOLVSAMP, которая по умолчанию устанавливается в папку C:\Program Files\Microsoft Office\Office14\SAMPLES.