5.4.2. Построение линейной эмпирической формулы с использованием встроенных функций линейн и тенденция
Для построения линейной эмпирической формулы в приложении Excel предусмотрены встроенные функции ЛИНЕЙН и ТЕНДЕНЦИЯ из категории Статистические.
ЛИНЕЙН(<известное Y>;<известное X>) – вычисляет коэффициенты линеного уравнения регрессии для множества значений независимой переменной Х и зависимой переменной Y Результаты выводятся в две смежные ячейки – сначала коэффициент при х, затем свободный член.
Поскольку X и Y являются массивами, то функция вводится как функция обработки массивов:
выделяются две смежные ячейки для результатов;
вводится функция;
одновременно нажимаются клавиши Ctrl+Shift+Enter.
ТЕНДЕНЦИЯ(<известное Y>;<известное X>;<новое Х>) – вычисляет ожидаемое новое значение у для нового х, если известны некоторые опытные значения Х и Y.
Ввод этой функции аналогичен вводу функции ЛИНЕЙН.
Пример. Для исходных данных, заданных табл. 5.1, найдем коэффициенты линейной эмпирической формулы y=a+bx, используя функцию ЛИНЕЙН.
Последовательность действий
Таблица исходных данных приведена рис.5.7.
Выделим ячейки E4:F4 и введем формулу: =ЛИНЕЙН($C$6:$С$15;$В$6:$В$15), используя Мастер функции.
Результаты в ячейках E4 и F4 можно интерпретировать как коэффициенты линейного уравнения y=1,8+0,64 x.
Рис. 5.7. Определение коэффициентов линейной регрессии
с помощью функции ЛИНЕЙН
Пример. Для тех же исходных данных, приведенных на рис.5.7, вычислим ожидаемое новое значение у для нового значения х, используя функцию ТЕНДЕНЦИЯ.
Последовательность действий
Вычисления производятся в предположении, что х и у зависят линейно.
Новые значения х запишем в ячейках E8:E15.
Результаты вычислений новых значений у будем записывать в ячейки F8:F15.
Выделим ячейку F8 и с помощью Мастера функций введем формулу:
=ТЕНДЕНЦИЯ($C$6:$C$15;$B$6:$B$15;F8).
Одновременно нажмем клавиши Ctrl+Shift+Enter.
Скопируем формулу вниз для всех новых значений х.
Контрольные вопросы
Понятие численного эксперимента, пример такого эксперимента.
Что понимается под термином аппроксимации (приближения). Когда возникают задачи аппроксимации.
Как ставится задача интерполирования функций.
Среднеквадратичное приближение. Суть метода наименьших квадратов (МНК).
Среднее квадратичное отклонение. Выбор «наилучшего» приближения.
Геометрический смысл точности аппроксимации исследуемого процесса. Метод выравнивания
Аппроксимация с помощью эмпирической формулы с двумя параметрами.
Глава 6. Численные методы оптимизации
Необходимость принятия наилучших решений так же стара, как само человечество.
Термин «оптимизация» в литературе обозначает процесс отыскания наилучшего или оптимального решения. На практике под оптимизацией понимается стремление к совершенству, которое, возможно, не будет достигнуто по ряду объективных причин.
С оптимизацией явно или неявно мы встречаемся в любой сфере человеческой деятельности, в том числе и в строительстве. При решении организационно-управленческих задач методы оптимизации позволяют получить наилучшее распределение ресурсов или оптимальные объемы выпуска продукции с целью получения максимальной прибыли; в задачах технического проектирования – выбрать наилучший вариант конструкции или сооружения и т.д.
Проблема оптимизации имеет два основных аспекта:
разработка математической модели поставленной задачи в виде математических выражений, формализовав при этом понятие «оптимальный»,
решение математически сформулированной задачи выбранным подходящим численным методом..
Постановка задачи оптимизации является наиболее сложным и ответственным этапом. Она осуществляется с учетом назначения реального объекта, целей проектирования (управления) и конкретных условий реализации задачи.
Далее на примерах наиболее типичных задач экономики, управления и проектирования мы рассмотрим приемы и особенности построения математических моделей задач оптимизации, а также методы их решения.
Но прежде рассмотрим, что представляет собой математическая модель задачи оптимизации, и введем ряд понятий и определений, используемых в этих задачах.