Метод наименьших квадратов в Excel. Регрессионный анализ

Опубликованно 24.09.2017 15:00

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

Предположим, что вы имеете двух показателей X и Y. И Y зависит от X. в То время, как ТРАНСНАЦИОНАЛЬНЫЕ нас интересует с точки зрения регрессионного анализа (в Excel, что его методы реализуются с помощью встроенных функций), стоит сразу же перейти к рассмотрению конкретной задачи.

Итак, пусть X — поверхность в магазине продукты питания, выраженная в квадратных метрах, и В годовой доход, определяемый в миллионы рублей.

Вы хотите сделать прогноз, доход (Y) магазина, если один или другой поверхности. Очевидно, что функция Y = f (X) возрастает, так что гипермаркет продает больше продуктов, чем срыв.Несколько слов о точности исходных данных, используемых для прогнозирования

Говорим, что у нас есть таблица построена на основе данных из n магазинов.

X

x1

x2

...

xn

В

в1

в2

...

yn

Согласно математической статистике, результаты будут более или менее правильно, если исследуются данные, по крайней мере, 5-6 объектов. Кроме того, вы не можете использовать «аномальные» результаты. В частности, элиты небольшой магазин может иметь продаж раз больше, чем оборот крупных точек продажи класса «масмаркет». Суть метода

Данные таблицы можно изобразить на декартовой плоскости в виде точек M1 (x1, y1), ..., Mn (xn, yn). Теперь решение задачи сводится к выбору функции y = f (x), в том числе календарь, проходя как можно ближе точки M1, M2, ..Mn.

Конечно, вы можете использовать полином высокой степени, но этот вариант не только труднореализуем, но все это не является действительным, так как он не будет отражать тенденции, которые вы должны открыть. Самым разумным решением является поиск справа y = ax + b, которая наилучшим образом приблизить экспериментальные данные, в частности, коэффициенты a и b.

Оценка точности

Любое приближение имеет важное значение для оценки его точности. Обозначим через ei разности (отклонения) между функциональными и экспериментальных значений для точки xi, т. е. ei = yi - f (xi).

Очевидно, что для оценки качества настройки, вы можете использовать сумму отклонений, т. е. при прямой выбор для сближая изображения по X Y нужно отдавать предпочтение тому, кто наименьшее значение суммы еi во всех вопросах, обсуждавшихся. Однако, все не так просто, потому что с развитием положительной разницы практически будет присутствовать и отрицательными.

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

В Excel, как известно, существует встроенная функция автосуммы, можно вычислить все значения, находящиеся в выборе. Таким образом, ничто не мешает нам вычислить значение выражения (e12 + e22 + e32+ ... e(n2).

В области письменной формы:

Так как изначально было решено аппроксимировании с помощью прямой линии, мы имеем:

Таким образом, задача нахождения прямой, которая наилучшим образом описывает конкретные зависимость значений X и Y, сводится к вычислению минимума функции двух переменных:

Для этого необходимо приравнять нулю частные производные по новым переменным a и b, и решить примитивную систему, состоящую из двух уравнений с 2 неизвестными жанр:

После простых преобразований, в том числе деление 2 и манипуляции, мы получаем:

Решив, например, методом Крамера, мы получим неподвижную точку с некоторых коэффициентов a* и b*. Это минимум, т. е. для прогнозирования, какой доход будет от магазина на некоторое место, идеально подходит справа y = a*x + b*представляет собой модель регрессии для данного примера. Конечно, она не позволит найти точный результат, но и поможет вам иметь идею, платить, если покупки в магазине кредит.Как реализоавать метод наименьших квадратов в Excel

В microsoft Excel имеется функция для расчета значения МНК. Она имеет следующий вид: «ТЕНДЕНЦИЯ» (известный. значение Y; lanc. значения X; новые значения X; пересечения.). Применяется формула расчета МНК в Excel на нашем столе.

Для этого в ячейку, где должен отображаться результат вычислений по методу наименьших квадратов в Excel, введем знак «=» и выбрать функцию «ТЕНДЕНЦИЯ». В открывшемся окне заполните соответствующие поля, подчеркивая:диапазон известных значений для Y (в этом случае, прибыль);серии x1, ...xn, т. е. величина торговых площадей;и известные, и значения x, для которых необходимо определить размер выручки (с информацией на их расположение на рабочем листе ниже.

Кроме того, формула содержит логическую переменную «Перехват». Если вы введете в поле 1, это означает, что мы должны выполнять вычисления, учитывая, что b = 0.

Если вы хотите узнать прогноз для нескольких значений x, то после ввода формулы нажмите клавишу «Enter», вы должны ввести комбинацию клавиш «Shift» + «Control»+ «Ввод» (Enter).Некоторые особенности

Регрессионный анализ может быть доступно даже чайникам. Формула Excel для прогнозирования значений массива неизвестных переменных, — «ТЕНДЕНЦИЯ» — может быть, даже те, кто никогда не слышали о методе наименьших квадратов. Достаточно просто знать некоторые особенности его работы. В частности:Если вы разместите диапазон известных значений переменной y в одной строке или столбце, в каждой строке (столбце) с известными значениями x, будет восприниматься программой, как переменную.Если в окне «ТЕНДЕНЦИЯ» не указан диапазон с известными x, в случае использования функции в Excel, программа будет рассматривать его как массив, содержащий целые числа, количество которых соответствует диапазону значений переменной y.Чтобы получить на выходе таблицу «предсказанных» значений, выражение для расчета тенденции нужно ввести как формулу массива.Если вы не указываете новых значений х, то функция «ТЕНДЕНЦИЯ», считает их равна известна. Если они не указаны, в качестве аргумента принимает массив 1; 2; 3; 4;..., который соразмерен пляжа с уже заданными параметрами y.Диапазон, содержащий новые значения x должен быть составлен из одной или более строк или столбцов, такие как диапазон значений y. Другими словами, он должен быть соразмерным независимых переменных.В таблице с известными значениями x может содержать несколько переменных. Однако, если это просто необходимо, для диапазонов значений x и y были пропорциональны. В случае нескольких переменных, нужно, чтобы диапазон со значениями y вмещался в одном столбце или в одной строке.

Функция «ПРЕДСКАЗ»

Регрессионный анализ в Excel осуществляется с помощью нескольких функций. Один из них называется «ПРЕДСКАЗ». Он похож на «ТЕНДЕНЦИЯ», т. е. дает результат вычислений по методу наименьших квадратов. Но только для X, для которого значение неизвестно Y.

Теперь вы знаете, формулы в Excel для "чайников", позволяющий значение прогнозирования будущего значения показателя в зависимости от движения тренда.

Категория: Hi-Tech