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

Обновлено: 08.07.2024

Табличный процессор Эксель способен реализовывать большое число математических операций. Из статьи вы узнаете, как используется метод наименьших квадратов, который применяется для осуществления решения разнообразных задач.

Первоначальные настройки: включение параметра «Поиск решения»

Изначально необходимо включить «Поиск решения», так как по умолчанию он находится в выключенном состоянии. Пошаговое руководство выглядит следующим образом:

Переходим в раздел «Файл», который находится в левом верхнем углу интерфейса табличного процессора.

На экране отобразилось новое окошко. Здесь, в левой колонке, необходимо кликнуть на элемент «Параметры».

В появившемся окне выбираем раздел «Надстройки». В правой части окошка находим надпись «Управление:» и раскрываем список. В списке выбираем пункт «Надстройки Excel». Щёлкаем кнопку «ОК».

Появилось еще одно окошко с названием «Надстройки». Около элемента «Поиск решения» ставим галочку. После проведения всех манипуляций кликаем «ОК».

Готово! Параметр включился, и теперь мы можем приступить к разбору метода наименьших квадратов.

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

Для начала вспомним, что метод наименьших квадратов — математический метод, используемый для решения задач, основанный на минимизации суммы квадратов отклонений некоторых функций от изначальных значений.

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

Шаг первый: исходные данные решаемой задачи

На конкретном примере начнем разбор метода наименьших квадратов. К примеру, у нас есть 2 колонки – X и Y:

Эта зависимость описывается уравнением: Y=A+NX.

Мы прекрасно знаем, что если Х равен нулю, то и Y равен нулю. Следовательно, это уравнение можно привести к следующему виду: Y=NX. Начнём реализацию этой задачи. Нам нужно найти сумму квадратов разности.

Шаг второй: решение задачки с использованием МНК

Пошаговое руководство выглядит следующим образом:

Столбику, который располагается левее показателя Х, даем название N и прописываем единицу рядом с 1-м показателем колонки Х. Единица – это примерное значение 1-го коэффициента N.

Столбику, который располагается правее показателя Y, даем наименование NX.
В ячейку D1, находящуюся около первых показателей X и Y, прописываем специальную формулу произведения коэффициента N на показатель из столбика Х. Сама формула выглядит следующим образом: =$А$2*В2. Стоит заметить, что адрес ячейки с коэффициентом необходимо привести к абсолютному типу. Абсолютный тип в табличном процессоре позволяет зафиксировать строчку или столбик (или все вместе одновременно). Иными словами, при копировании значений в другие ячейки абсолютные ссылки не будут меняться. После проведения всех манипуляций жмем клавишу «Enter».

Перемещаем курсор мышки на сектор с выведенным результатом. Наведя указатель в правый верхний уголок, он превратится в маленький плюсик темного цвета, который называется маркером заполнения. Зажимаем ЛКМ и перетягиваем маркер в самый низ до крайней строчки табличных данных.

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

Далее переходим к подсчету суммы разностей квадратов показателей Y и NX. Перемещаемся в ячейку E1, располагающуюся правее от показателя NX, нажимаем на элемент «Вставить функцию», который находится рядом со строкой для ввода формул, и имеет внешний вид «fx».

Первым делом, находим надпись «Категории:» и раскрываем список, находящийся рядом. В обширном списке выбираем элемент «Математические». В блоке «Выберите функцию:» отыскиваем функцию «СУММКВРАЗН» и выбираем ее. После проведения всех манипуляций жмем на кнопку «ОК».

Открылось небольшое окошко «Аргументы функции». Для дальнейшей работы необходимо заполнить все строки формы. В строчку «Массив_х» вводим адрес диапазона ячеек столбика Y. Координаты можно вписать самостоятельно ручным вводом, используя клавиатуру, или же путем их выбора в самих табличных данных.
В строчку «Массив_у» вводим адрес диапазона ячеек столбика NX. После проведения всех манипуляций щёлкаем клавишу «Enter».

Перемещаемся в раздел «Данные», который находится в верхней части интерфейса табличного процессора. Находим блок команд под названием «Анализ» и выбираем элемент «Поиск решения».

На экране отобразилось окошко с огромным количеством настроек, которые необходимо заполнить. В строчку «Оптимизировать целевую функцию» вводим ссылку на ячейку с оператором «СУММКВРАЗН». Реализовать это действие можно самостоятельно ручным вводом, используя клавиатуру, или же выбрав сектор в самой табличке. Ставим отметку около надписи «Минимум», которая располагается в блоке «До:». В строчку «Изменяя ячейки переменных» вбиваем адрес сектора, в котором располагается показатель коэффициента N – $A$2. После проведения всех манипуляций щелкаем на «Найти решение».

После того, как «Поиск решения» будет выполнен, на экране компьютера отобразится окошко с выведенными результатами поиска решения, а затем реализуется замена числовых значений в столбике N. Отображенный результат – это наименьший квадрат функции. Если выведенный результат удовлетворяет тому, что нужно было найти, то нажимаем «ОК».

Заключение

Таким образом, мы на конкретном примере рассмотрели способ использования метода наименьших квадратов в табличном процессоре Microsoft Excel и ознакомились со стандартным алгоритмом действий при решении похожих задач.

Метод наименьших квадратов представляет собой математическую процедуру построения линейного уравнения, которое бы наиболее точно соответствовало набору двух рядов чисел. Целью применения данного способа является минимизация общей квадратичной ошибки. В программе Excel имеются инструменты, с помощью которых можно применять данный метод при вычислениях. Давайте разберемся, как это делается.

Использование метода в Экселе

Метод наименьших квадратов (МНК) является математическим описанием зависимости одной переменной от второй. Его можно использовать при прогнозировании.

Теперь функция Поиск решения в Excel активирована, а её инструменты появились на ленте.

Условия задачи

Опишем применение МНК на конкретном примере. Имеем два ряда чисел x и y, последовательность которых представлена на изображении ниже.

Наиболее точно данную зависимость может описать функция:

При этом, известно что при x=0 y тоже равно 0. Поэтому данное уравнение можно описать зависимостью y=nx.

Нам предстоит найти минимальную сумму квадратов разности.

Решение

Перейдем к описанию непосредственного применения метода.

Как видим, применение метода наименьших квадратов довольно сложная математическая процедура. Мы показали её в действии на простейшем примере, а существуют гораздо более сложные случаи. Впрочем, инструментарий Microsoft Excel призван максимально упростить производимые вычисления.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + b .

Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным.

Рассмотрим приближение функциями, зависящими только от одной переменной:

Линейная: y=ax+b (эта статья)
Логарифмическая : y=a*Ln(x)+b
Степенная : y=a*x m
Экспоненциальная : y=a*EXP(b*x)+с
Квадратичная : y=ax 2 +bx+c

Примечание : Случаи приближения полиномом с 3-й до 6-й степени рассмотрены в этой статье. Приближение тригонометрическим полиномом рассмотрено здесь.

Линейная зависимость

Нас интересует связь 2-х переменных х и y . Имеется предположение, что y зависит от х по линейному закону y = ax + b . Чтобы определить параметры этой взаимосвязи исследователь провел наблюдения: для каждого значения х _i произведено измерение y _i (см. файл примера ). Соответственно, пусть имеется 20 пар значений (х _i ; y _i ).

Для наглядности рекомендуется построить диаграмму рассеяния.

Примечание: Если шаг изменения по х постоянен, то для построения диаграммы рассеяния можно использовать тип График , если нет, то необходимо использовать тип диаграммы Точечная .

Из диаграммы очевидно, что связь между переменными близка к линейной. Чтобы понять какая из множества прямых линий наиболее «правильно» описывает зависимость между переменными, необходимо определить критерий, по которому будут сравниваться линии.

В качестве такого критерия используем выражение:

Вышеуказанное выражение представляет собой сумму квадратов расстояний между наблюденными значениями y _i и ŷ _i и часто обозначается как SSE ( Sum of Squared Errors ( Residuals ), сумма квадратов ошибок (остатков) ) .

Метод наименьших квадратов заключается в подборе такой линии ŷ = ax + b , для которой вышеуказанное выражение принимает минимальное значение.

Примечание: Любая линия в двухмерном пространстве однозначно определяется значениями 2-х параметров: a (наклон) и b (сдвиг).

Считается, что чем меньше сумма квадратов расстояний, тем соответствующая линия лучше аппроксимирует имеющиеся данные и может быть в дальнейшем использована для прогнозирования значений y от переменной х. Понятно, что даже если в действительности никакой взаимосвязи между переменными нет или связь нелинейная, то МНК все равно подберет «наилучшую» линию. Таким образом, МНК ничего не говорит о наличии реальной взаимосвязи переменных, метод просто позволяет подобрать такие параметры функции a и b , для которых вышеуказанное выражение минимально.

Проделав не очень сложные математические операции (подробнее см. статью про квадратичную зависимость ), можно вычислить параметры a и b :

Как видно из формулы, параметр a представляет собой отношение ковариации и дисперсии , поэтому в MS EXCEL для вычисления параметра а можно использовать следующие формулы (см. файл примера лист Линейная ):

= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45) или

Также для вычисления параметра а можно использовать формулу = НАКЛОН(C26:C45;B26:B45) . Для параметра b используйте формулу = ОТРЕЗОК(C26:C45;B26:B45) .

И наконец, функция ЛИНЕЙН() позволяет вычислить сразу оба параметра. Для ввода формулы ЛИНЕЙН(C26:C45;B26:B45) необходимо выделить в строке 2 ячейки и нажать CTRL + SHIFT + ENTER (см. статью про формулы массива, возвращающими несколько значений ). В левой ячейке будет возвращено значение а , в правой – b .

Примечание : Чтобы не связываться с вводом формул массива потребуется дополнительно использовать функцию ИНДЕКС() . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);1) или просто = ЛИНЕЙН(C26:C45;B26:B45) вернет параметр, отвечающий за наклон линии, т.е. а . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);2) вернет параметр, отвечающий за пересечение линии с осью Y, т.е. b .

Вычислив параметры, на диаграмме рассеяния можно построить соответствующую линию.

Инструмент диаграммы Линия тренда

Еще одним способом построения прямой линии по методу наименьших квадратов является инструмент диаграммы Линия тренда . Для этого выделите диаграмму, в меню выберите вкладку Макет , в группе Анализ нажмите Линия тренда , затем Линейное приближение .

Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме.

Примечание : Для того, чтобы параметры совпадали необходимо, чтобы тип у диаграммы был Точечная, а не График . Дело в том, что при построении диаграммы График значения по оси Х не могут быть заданы пользователем (пользователь может указать только подписи, которые не влияют на расположение точек). Вместо значений Х используется последовательность 1; 2; 3; … (для нумерации категорий). Поэтому, если строить линию тренда на диаграмме типа График , то вместо фактических значений Х будут использованы значения этой последовательности, что приведет к неверному результату (если, конечно, фактические значения Х не совпадают с последовательностью 1; 2; 3; …).

СОВЕТ : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .

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

Подготовительный этап: активируем надстройку “Поиск Решения”

Прежде, чем приступить к решению основной задачи, потребуется активировать надстройку “Поиск решения” в программе.

Этап 1: исходные данные

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

Данная зависимость может быть описана уравнением ниже:

Также, мы знаем, что если X=0, то и Y=0. А значит, данное уравнение можно записать так:

Приступим к выполнению нашей задачи, которая заключается в нахождении суммы квадратов разности.

Этап 2: решаем задачу с применением МНК

в поле “Массив_x” указываем координаты диапазона ячеек столбца Y (без шапки). Адреса ячеек можно указать как вручную, напечатав их с клавиатуры, так и путем выделения с помощью зажатой левой кнопки мыши в самой таблице.
в поле “Массив_y” указываем диапазон ячеек столбца NX.
жмем Enter, когда все готово.

в поле “Оптимизировать целевую функцию” следует указать ссылку на ячейку с функцией “СУММКВРАЗН”. Сделать это можно вручную или выбрав элемент в таблице.
для опции “До” выбираем вариант – “Минимум”.
в поле “Изменяя ячейки переменных” нужно указать координаты ячейки, в которой находится соответствующее значение коэф. N.
по готовности нажимаем “Найти решение”.

Заключение

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Метод наименьших квадратов — это математическая процедура составления линейного уравнения, максимально соответствующего набору упорядоченных пар, путем нахождения значений для a и b, коэффициентов в уравнении прямой. Цель метода наименьших квадратов состоит в минимизации общей квадратичной ошибки между значениями y и ŷ. Если для каждой точки мы определяем ошибку ŷ, метод наименьших квадратов минимизирует:

где n = число упорядоченных пар вокруг линии. максимально соответствующей данным.

Это понятие проиллюстрировано на рисунке

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

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

Поскольку своей целью мы определили задачу узнать, увеличивается ли со временем число предметов, «Месяц» будет независимой переменной, а «Число предметов» — зависимой.

С помощью метода наименьших квадратов определяем уравнение, максимально соответствующее данным, путем вычисления значений a, отрезка на оси y, и b, наклона линии:

В таблице ниже суммированы необходимые для этих уравнений вычисления.

Кривая эффекта для нашего примера с ванной будет определяться следующим уравнением:

Поскольку наше уравнение имеет положительный наклон — 0.976, парень имеет доказательство того, что число предметов на столике со временем увеличивается со средней скоростью 1 предмет в месяц. На графике представлена кривая эффекта с упорядоченными парами.

Ожидание в отношении числа предметов в течение следующего полугода (месяца 16) будет вычисляться так:

ŷ = 5.13 + 0.976x = 5.13 + 0.976(16)

20.7 = 21 предмет

Так что, пора нашему герою предпринимать какие-нибудь действия.

Функция ТЕНДЕНЦИЯ в Excel

Как вы уже, наверное, догадались в Excel имеется функция для расчета значения по методу наименьших квадратов. Это функция называется ТЕНДЕНЦИЯ. Синтаксис у нее следующий:

ТЕНДЕНЦИЯ (известные значения Y; известные значения X; новые значения X; конст)

известные значения Y – массив зависимых переменных, в нашем случае, количество предметов на столике

известные значения X – массив независимых переменных, в нашем случае это месяц

новые значения X – новые значения X (месяца) для которого функция ТЕНДЕНЦИЯ возвращает ожидаемое значение зависимых переменных (количество предметов)

Например, на рисунке показана функция ТЕНДЕНЦИЯ, используемая для определения ожидаемого количества предметов на столике в ванной для 16-го месяца.

Читайте также: