Таблицы подстановки

Технология таблицы подстановки используется для изучения влияния значений параметра на результат несколько функций либо значений двух параметров на результат одной функции. Эта технология обеспечивает решение задач анализа типа «ЧТО ЕСЛИ», ограничивая число од­новременно изменяемых параметров модели. Значения параметра (параметров) и результирующей функции представлены на листе в явном виде. Можно про­сто изменять значения параметра (параметров) и автоматически вычислять зна­чения функции.

Таблица подстановок для одного параметра. Параметр одновременно может использоваться для вычисления различных функций. На листе рабочей книги готовится массив значений параметра в виде век­тор -строки или вектор - столбца. Вводятся формулы различных функций, кото­рые используют данный параметр. Для вычисления этих функций создается массив формул.

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

Поиск решения

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

Дополнительные настройки оптимизации выполняются в диалоговом окне «Па­раметр поиска решения». В частности, можно ограничить время выполнения по­иска решения и выполнения промежуточных вычислений, максимальное вре­мя — не более 9 часов, точность, с которой найденное решение соответствует целевому значению, допустимое отклонение для переменных от оптимального значения. Для задач с нелинейной целевой функцией задается параметр сходи­мости, который влияет на прекращение поиска. Если относительные изменения значения целевой функции за последние пять итераций меньше указанного чис­ла, поиск прекращается. Выполняется установка типа модели — линейная, если целевая функция линейная. Можно выводить результаты итераций, выполнять автоматическое масштабирование параметров модели.

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

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

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