Метод наименьших квадратов в excel

Некоторые особенности

Регрессионный анализ может быть доступен даже чайникам. Формула Excel для предсказания значения массива неизвестных переменных — «ТЕНДЕНЦИЯ» — может использоваться даже теми, кто никогда не слышал о методе наименьших квадратов. Достаточно просто знать некоторые особенности ее работы. В частности:

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

Способ 2: интерполяция графика с помощью его настроек

• выделяют плоскость, на которой находится график;
• в контекстном меню выбирают кнопку «Выбрать данные…»;
• в окне «Выбор источника данных» в правом блоке нажимают «Изменить»;
• нажимают на иконку с красной стрелкой справа от поля «Диапазон подписей осей»;
• выделяют диапазон А2:А11;
• нажимают на кнопку «OK»;
• вновь вызывают окно «Выбор источника данных»;
• нажимают на кнопку «Скрытые и пустые ячейки» в нижнем левом углу;
• в строке «Показывать пустые ячейки» переключатель переставляют в позицию «Линия» и нажимают «OK»;
• подтверждают эти действия тем же способом.

Реализация алгоритма интерполяции начинается, как и при ручных вычислениях с записи формул для вычисления коэффициентов qi На рис. 9 приведена столбцы таблицы с заданными значениями аргумента, интерполируемой функции и коэффициентовqi. Справа от этой таблицы приведены формулы, записываемые в ячейки столбцаС для вычисления значений коэффициентовqi.

Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами

3.1. Зачем нужна аппроксимация функций?

В окружающем нас мире все взаимосвязано, поэтому одной из наиболее часто встречающихся задач является установление характера зависимости между различными величинами, что позволяет по значению одной величины определить значение другой. Математической моделью зависимости одной величины от другой является понятие функции
y=f(x)
.

В практике расчетов, связанных с обработкой экспериментальных данных, вычислением
f(x),
разработкой вычислительных методов, встречаются следующие две ситуации:

1. Как установить вид функции
y=f(x),
если она неизвестна? Предполагается при этом, что задана таблица ее значений, которая получена либо из экспериментальных измерений, либо из сложных расчетов.

2. Как упростить вычисление известной функции
f(x)
или же ее характеристик, если
f(x)
слишком сложная?

Ответы на эти вопросы даются теорией аппроксимации функций,
основная задача
которой состоит в нахождении функции
y=

(x)
, близкой (т.е. аппроксимирующей) в некотором нормированном пространстве к исходной функции
y=f(x).
Функцию

(x)
при этом выбирают такой, чтобы она была максимально удобной для последующих расчетов.

Основной подход
к решению этой задачи заключается в том, что

(x)
выбирается зависящей от нескольких свободных параметров, т.е. , значения которых подбираются из некоторого условия близости
f(x)
и

(x)
.

Обоснование способов нахождения удачного вида функциональной зависимости и подбора параметров составляет задачу
теории аппроксимации функций
.

В зависимости от способа подбора параметров получают различные
методы аппроксимации;
наибольшее распространение среди них получили
интерполяция
и
среднеквадратичное приближение,
частным случаем которого является
метод наименьших квадратов.

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

. (3.1)

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

Важно, чтобы система базисных функций была
полной
, т.е. обеспечивающей аппроксимацию
f(x)
многочленом (3.1) с заданной точностью при.

Приведем хорошо известные и часто используемые системы. При интерполяции обычно используется система линейно независимых функций. Для среднеквадратичной аппроксимации удобнее в качестве брать ортогональные на интервале многочлены Лежандра:

Заметим, что если функция задана на отрезке
,
то при использовании этой системы необходимо предварительно осуществить преобразование координат, приводящее интервал к интервалу.

Для аппроксимации периодических функций используют ортогональную на систему тригонометрических функций. В этом случае обобщенный многочлен (3.1) записывается в виде.

Метод наименьших квадратов | Реальная статистика с использованием Excel

В разделе «Корреляция» мы изучаем линейную корреляцию между двумя случайными величинами x и у. Теперь посмотрим на линию на плоскости x y, которая лучше всего соответствует данным ( x ₁ , y ₁ ), …, ( x _n , y _{n ).}

Напомним, что уравнение для прямой имеет вид y = bx + a , где

b = наклон прямой a = y-пересечение, т.е. значение y в месте пересечения прямой с ось Y

Для наших целей мы запишем уравнение линии наилучшего соответствия как

and so the y-intercept is

For each i , we define ŷ _i as the y-value of x _i on this line, and so

Линия наилучшего соответствия — это линия, для которой сумма расстояний между каждой из n точек данных и линией как можно меньше. Таким образом, математически полезный подход состоит в том, чтобы найти линию со свойством, что сумма следующих квадратов минимальна.

Теорема 1 : наилучшая посадка для точек ( x ₁ , Y ₁ ),… ( x _N , Y _{n n n} n n n n y _.

где

Щелкните здесь для доказательства теоремы 1. Даны два доказательства, одно из которых не использует исчисление.

Определение 1 : Линия наилучшего соответствия называется линией регрессии .

Наблюдение : Теорема показывает, что линия регрессии проходит через точку ( x̄ , ȳ) и имеет уравнение

, где наклон равен

, а точка пересечения по оси Y равна

cov x var x n a b x

a = INTERCEPT(R1, R2) = AVERAGE(R1) – b * AVERAGE(R2)

Свойство 1 : 

Доказательство корреляции: по определению, 2

и, таким образом, по приведенному выше наблюдению мы имеем

Функции Excel : Excel предоставляет следующие функции для прогнозирования значения y для любого x на основе линии регрессии. Здесь R1 = массив значений данных y, а R2 = массив значений данных x :

НАКЛОН (R1, R2) = наклон линии регрессии, как описано выше

ПЕРЕСЕЧЕНИЕ (R1, R2) = y-пересечение линии регрессии, как описано выше0161 х . Таким образом, ПРОГНОЗ (x, R1, R2) = a + b * x , где a = ПЕРЕСЕЧЕНИЕ (R1, R2) и b = НАКЛОН (R1, R2).

TREND (R1, R2) = функция массива, которая создает массив предсказанных значений y, соответствующих x значениям, хранящимся в массиве R2, на основе линии регрессии, вычисленной из x значений, хранящихся в массиве R2, и значений y хранится в массиве R1.

TREND (R1, R2, R3) = функция массива, которая предсказывает значения y, соответствующие 9Значения 0161 x в R3 на основе линии регрессии на основе значений x , хранящихся в массиве R2, и значений y, хранящихся в массиве R1.

Чтобы использовать TREND(R1, R2), выделите диапазон, в котором вы хотите сохранить предсказанные значения y. Затем введите TREND и левую скобку. Затем выделите массив наблюдаемых значений для y (массив R1), введите запятую и выделите массив наблюдаемых значений для x (массив R2), за которым следует правая скобка. Наконец нажмите Ctrl-Shft-Enter .

Чтобы использовать TREND(R1, R2, R3), выделите диапазон, в котором вы хотите сохранить предсказанные значения y. Затем введите TREND и левую скобку. Затем выделите массив наблюдаемых значений для y (массив R1), введите запятую и выделите массив наблюдаемых значений для x (массив R2), затем еще одну запятую и выделите массив R3, содержащий значения для x , для которых вы хотите предсказать значения y на основе линии регрессии. Теперь введите правую скобку и нажмите Ctrl-Shft-Enter .

Функция Excel 2016 : В Excel 2016 представлена ​​новая функция ПРОГНОЗ.ЛИНЕЙНЫЙ , эквивалентная ПРОГНОЗУ.

Пример 1 : Рассчитайте линию регрессии для данных в Примере 1 проверки гипотезы одной выборки на предмет корреляции и нанесите результаты на график.

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

При любой аппроксимации особую важность приобретает оценка ее точности. Обозначим через e i разность (отклонение) между функциональными и экспериментальными значениями для точки x i , т

е. e i = y i — f (x i).

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

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

Постановка задачи на конкретном примере

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

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

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

Графический метод: подготовка

Интерполяция в Excel в таком случае начинается с построения графика. Для этого:

• во вкладке «Вставка» выделяют табличный диапазон;
• в блоке инструментов «Диаграммы» выбирают значок «График»;
• в появившемся списке выбирают тот, который лучше подходит для решения конкретной задачи.

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

Интерполяция функции нескольких переменных
Выполнив формирование таблицы, приведённой на рис. 8, по формулам (17) и (11) можно вычислить значение интерполируемой функции для любого значения аргумента Х. Например, дляХ=1 вычисляем значенияli(1) (i=0,1,2,3):

МНК: Приближение полиномом в EXCEL

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

В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +…+b ₆ x 6

Примечание : В инструменте MS EXCEL Линия тренда , который доступен для диаграмм типа Точечная и График , можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.

Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.

Как известно, квадратичная зависимость y=b +b ₁ x+b ₂ x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL , является частным случаем полиномиальной y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.

Примечание : Существует еще один метод вычисления коэффициентов – замена переменных, который рассмотрен в конце статьи.

Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.

Примечание : Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).

В файле примера создана универсальная форма для вычисления коэффициентов полиномов.

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

Примечание: При использовании полиномов высокой степени необходимо следить за тем, чтобы количество пар значений (х _i ; y _i ) превышало степень полинома хотя бы на несколько значений (для обеспечения точности аппроксимации). Кроме того, график функции полинома степени m имеет m-1 точку перегиба. Понятно, что точек данных должно быть гораздо больше, чем точек перегиба, чтобы такой изменчивый тренд стал очевидным (если утрировать, то бессмысленно строить по двум точкам параболу, логичнее построить прямую).

Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.

С помощью замены переменных x i =x _i полиномиальную зависимость y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных x _i . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .

Наборы данных

Метод наименьших квадратов используется для обработки набора данных и прогнозирования будущих значений. Пусть у нас есть массивы данных X = {10, 12, 14, 16, 18, 20} и Y = {18, 22, 24, 26, 27, 28}, при этом значение Y зависит от X. Придадим этим массивам смысл. К примеру, массив X ​– это мощность паровой машины парохода, а Y — его ходовая скорость в узлах. Это означает, что при мощности энергетической установки в 10 тысяч лошадиных сил, пароход развивает скорость на уровне 18 морских миль в час, и так далее, так как каждое значение игрека соответствует своему иксу.

Эти данные можно представить в виде точек на декартовой плоскости, например как V1(X1, Y1), V2(X2, Y2) и так далее. Если соединить эти точки, то мы получим некую кривую, которую можем описать соответствующим уравнением y = f(x). Данное уравнение должно быть достаточно простым, но при этом максимально близко описывать полученную зависимость.

Получив кривую, мы можем продлить ее в любую сторону и узнать приблизительное значение игреков для любых иксов или наоборот. Например, аппроксимировав данные нашего примера, мы сможем узнать, какая мощность установки требуется для достижения скорости в 15 узлов. Или какую мы получим скорость, установив на борт установку мощностью в 22 тысячи лошадиных сил. Для того чтобы определить эту волшебную y = f(x), нам и необходим метод наименьших квадратов.

Метод наименьших квадратов

В Excel, как известно, существует встроенная функция автосуммы, позволяющая вычислить значения всех значений, расположенных в выделенном диапазоне. Таким образом, ничто не помешает нам рассчитать значение выражения (e 1 2 + e 2 2 + e 3 2 + … e n 2).

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

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

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

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

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

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