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

Опубликованно 03.10.2017 03:00

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

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

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

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

Предположим, у нас есть Таблица, по данным для n магазинов.

X

x1

x2

...

xn

Y

y1

y2

...

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+ ... en2).

В математической записи имеет вид:

Поскольку изначально решение о аппроксимировании с четным, таким образом, мы имеем:

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

Это требует равна нулю частные производные которой ставят в новых переменных a и b, и при рудиментарных систему, состоящую из двух уравнений с 2-мя неизвестными вида:

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

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

В Excel имеется функция для расчета значений в МНК. Она имеет следующий вид: «ТРЕНД» (страна происхождения. значения Y; страна происхождения. Значения X; новые значения X конст;.). Формула расчета применяется МНК в Excel к нашей таблице.

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

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

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

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

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

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

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

Категория: Мобильная техника