Использование электронных таблиц для решения задач.

Качественная и глубокая проработка математических и алгоритмических возможностей современных табличных процессоров превратила их мощный математический инструмент подготовки и проведения прикладных и перспективных исследований экономических, финансовых, технических и иных систем. Идеологической основой подобных исследований является метод моделирования (см. подробнее раздел 5«Моделирование и формализация» в части 1 «Основы информатики»).

Цель моделирования — это изучение объектов познания через их модели, при условии, что между объектом исследования и моделью существует некоторое подобие.

Определение. Модель — это логическое или математическое описание компонентов и функций, отображающих существенные свойства моделируемого объекта.

Рассмотрим несколько примеров использования метода моделирования в современных табличных процессорах при решении прикладных задач.

9.8.1.Решение уравнений и задач оптимизации.[15]⁾

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

Примечание.

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

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

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

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

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

Примечание.

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