Линейная и экспоненциальная аппроксимация данных

Статистический анализ и прогноз рядов наблюдений

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

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

Линейная и экспоненциальная аппроксимация данных

Для вычислений коэффициентов уравнения прямой линии, которая наилучшим образом аппроксимирует данные наблюдений используется функция ЛИНЕЙН(). Уравнение для прямой линии имеет вид

Y=m1*x1+m2*x2+.....b или Y=m*x+b

Для вычисления коэффициентов уравнения экспоненциальной кривой, используемой для сглаживания данных наблюдения используется функция ЛГРФПРИБЛ(). Уравнение кривой имеет вид

Y = (b*(m1^x1)*(m2^x2)*.....) или Y =b*m^x

где Y (зависимое значение) является функцией независимого значения х.

Значения m - это коэффициенты, соответствующие каждой независимой переменной х, а b - это постоянная величина (кривая может не проходить через начало координат)

Функции аппроксимации ЛИНЕЙН() и ЛГРФПРИБЛ() позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую Ваши данные наблюдений. Однако, Вам самим необходимо решить, какой из полученных результатов Вас удовлетворяет в большей степени.

Проводя регрессионный анализ, Excel вычисляет для каждой точки

· квадрат разности между рассчитанным и фактическим значением Y. Сумма квадратов этих разностей называется остаточной суммой квадратов.

· сумму квадратов разностей между фактическими значениями Y и средним значением Y, которая называется общей суммой квадратов (регрессионная сумма квадратов + остаточная сумма квадратов)

FЧем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности R², который показывает насколько полученное регрессионное уравнение соответствует фактическим наблюдениям.

функция ЛИНЕЙН()

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

Создание формулы массива [2]

· Выделите диапазон ячеек, в которые следует ввести формулу.

· Наберите формулу.

· Нажмите клавиши CTRL+SHIFT+ENTER.

Синтаксис функций

ЛИНЕЙН (известные значения Y; известные значения х; константа; статистика)

ЛГРФПРИБЛ (известные значения Y; известные значения х; константа; статистика)