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

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции ЛИНЕЙН в Microsoft Excel. Ссылки на дополнительную информацию о построении диаграмм и проведении регрессионного анализа можно найти в разделе «См. также».

Описание

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

Уравнение для линии:

y = m1x1 + m2x2 + . + б

если существует несколько диапазонов значений x, где зависимые значения y являются функцией независимых значений x. Значения m — это коэффициенты, соответствующие каждому значению x, а b — постоянное значение. Обратите внимание, что y, x и m могут быть векторами. Массив, который возвращает функция ЛИНЕЙН, имеет вид . ЛИНЕЙН также может возвращать дополнительную статистику регрессии.

Синтаксис

ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])

Синтаксис функции ЛИНЕЙН имеет следующие аргументы:

Синтаксис

known_y's Требуется. Набор уже известных вам значений y в отношении y = mx + b.

Если диапазон known_y находится в одном столбце, каждый столбец known_x интерпретируется как отдельная переменная.

Если диапазон known_y содержится в одной строке, каждая строка known_x интерпретируется как отдельная переменная.

Необязательный параметр known_x. Набор значений x, которые вы, возможно, уже знаете в отношении y = mx + b.

Диапазон known_x может включать один или несколько наборов переменных. Если используется только одна переменная, known_y и known_x могут быть диапазонами любой формы, если они имеют одинаковые размеры. Если используется более одной переменной, known_y's должен быть вектором (то есть диапазоном с высотой в одну строку или шириной в один столбец).

Если known_x не указан, предполагается, что это массив того же размера, что и known_y.

константа Необязательный. Логическое значение, указывающее, следует ли принудительно приравнять константу b к 0.

Если const имеет значение TRUE или опущено, b вычисляется обычным образом.

Если const имеет значение FALSE, b устанавливается равным 0, а значения m корректируются так, чтобы соответствовать y = mx.

статистика Необязательно. Логическое значение, указывающее, следует ли возвращать дополнительную статистику регрессии.

Если параметр stats имеет значение TRUE, функция ЛИНЕЙН возвращает дополнительную статистику регрессии; в результате возвращается массив .

Если параметр stats имеет значение FALSE или опущен, функция ЛИНЕЙН возвращает только m-коэффициенты и константу b.

Дополнительная статистика регрессии выглядит следующим образом.

Описание

Значения стандартной ошибки для коэффициентов m1,m2. мин.

Коэффициент детерминации. Сравнивает оценочные и фактические значения y, значения варьируются от 0 до 1. Если это 1, то в выборке существует идеальная корреляция — нет никакой разницы между оценочным значением y и фактическим значением y. С другой стороны, если коэффициент детерминации равен 0, уравнение регрессии бесполезно для прогнозирования значения y. Сведения о вычислении r 2 см. в разделе "Примечания" далее в этом разделе.

Стандартная ошибка для оценки y.

Статистика F или наблюдаемое значение F. Используйте статистику F, чтобы определить, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной.

Степени свободы. Используйте степени свободы, чтобы найти F-критические значения в статистической таблице. Сравните значения, которые вы найдете в таблице, со статистикой F, возвращенной функцией ЛИНЕЙН, чтобы определить уровень достоверности для модели. Сведения о том, как вычисляется df, см. в разделе «Примечания» далее в этом разделе. В примере 4 показано использование F и df.

Сумма квадратов регрессии.

Остаточная сумма квадратов. Сведения о том, как рассчитываются ssreg и ssresid, см. в разделе "Примечания" далее в этом разделе.

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

Примечания

Вы можете описать любую прямую линию с наклоном и точкой пересечения по оси Y:

Наклон (m):
Чтобы найти наклон линии, часто обозначаемой как m, возьмите две точки на линии (x1,y1) и (x2,y2); наклон равен (y2 - y1)/(x2 - x1).

Отрезок по оси Y (b):
Отрезок по оси Y линии, часто обозначаемый буквой b, представляет собой значение y в точке, где линия пересекает ось y.

Уравнение прямой линии: y = mx + b. Как только вы узнаете значения m и b, вы можете вычислить любую точку на линии, подставив значение y или x в это уравнение. Вы также можете использовать функцию TREND.

Если у вас есть только одна независимая переменная x, вы можете получить значения наклона и точки пересечения y напрямую, используя следующие формулы:

Точность линии, рассчитанной функцией ЛИНЕЙН, зависит от степени разброса ваших данных. Чем линейнее данные, тем точнее модель ЛИНЕЙН. ЛИНЕЙН использует метод наименьших квадратов для определения наилучшего соответствия данных. Если у вас есть только одна независимая переменная x, расчеты m и b основаны на следующих формулах:

где x и y — выборочные средние значения; то есть x = СРЗНАЧ (известные x) и y = СРЗНАЧ (известные_y).

Функции подбора линий и кривых ЛИНЕЙН и ЛИНЕЙН позволяют рассчитать наилучшую прямую линию или экспоненциальную кривую, соответствующую вашим данным. Однако вы должны решить, какой из двух результатов лучше всего соответствует вашим данным. Вы можете рассчитать TREND(известные_y, известные_x) для прямой линии или РОСТ(известные_y, известные_x) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, предсказанных вдоль этой линии или кривой в ваших фактических точках данных. Затем вы можете сравнить прогнозируемые значения с фактическими значениями. Вы можете нанести их на карту для визуального сравнения.

В регрессионном анализе Excel вычисляет для каждой точки квадрат разницы между расчетным значением y для этой точки и ее фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов, ssresid. Затем Excel вычисляет общую сумму квадратов, sstotal. Когда аргумент const = TRUE или опущен, общая сумма квадратов представляет собой сумму квадратов разностей между фактическими значениями y и средним значением значений y. Когда аргумент const = FALSE, общая сумма квадратов представляет собой сумму квадратов фактических значений y (без вычитания среднего значения y из каждого отдельного значения y). Тогда сумма квадратов регрессии, ssreg, может быть найдена из: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминации r 2 , который является показателем того, насколько хорошо уравнение, полученное в результате регрессионного анализа, объясняет взаимосвязь между переменными. . Значение r 2 равно ssreg/sstotal.

В некоторых случаях один или несколько столбцов X (предположим, что Y и X находятся в столбцах) могут не иметь дополнительной прогностической ценности при наличии других столбцов X. Другими словами, удаление одного или нескольких столбцов X может привести к тому, что предсказанные значения Y будут одинаково точными. В этом случае эти избыточные столбцы X следует исключить из регрессионной модели. Это явление называется «коллинеарностью», потому что любой избыточный столбец X может быть выражен как сумма кратных неизбыточных столбцов X. Функция ЛИНЕЙН проверяет коллинеарность и удаляет любые избыточные столбцы X из регрессионной модели, когда она их идентифицирует. Удаленные столбцы X могут быть распознаны в выводе ЛИНЕЙН как имеющие 0 коэффициентов в дополнение к 0 значениям se. Если один или несколько столбцов удаляются как избыточные, это влияет на df, поскольку df зависит от количества X столбцов, фактически используемых для целей прогнозирования. Подробнее о вычислении df см. в примере 4. Если df изменяется из-за удаления избыточных столбцов X, это также влияет на значения sey и F. Коллинеарность должна быть относительно редкой на практике. Однако один случай, когда это более вероятно, - это когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли субъект в эксперименте членом определенной группы. Если const = TRUE или опущено, функция ЛИНЕЙН эффективно вставляет дополнительный столбец X со всеми 1 значениями для моделирования пересечения. Если у вас есть столбец с 1 для каждого субъекта, если он мужчина, или 0, если нет, и у вас также есть столбец с 1 для каждого субъекта, если женщина, или 0, если нет, этот последний столбец является избыточным, поскольку записи в нем могут быть получается путем вычитания записи в столбце «мужской показатель» из записи в дополнительном столбце всех 1 значений, добавленных функцией ЛИНЕЙН.

Значение df вычисляется следующим образом, когда из модели не удаляются столбцы X из-за коллинеарности: если есть k столбцов known_x и const = TRUE или опущен, df = n – k – 1. Если const = FALSE, df = n – k. В обоих случаях каждый столбец X, удаленный из-за коллинеарности, увеличивает значение df на 1.

При вводе константы массива (например, known_x) в качестве аргумента используйте запятые для разделения значений, содержащихся в одной строке, и точку с запятой для разделения строк.Символы-разделители могут отличаться в зависимости от региональных настроек.

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

Алгоритм, используемый в функции ЛИНЕЙН, отличается от алгоритма, используемого в функциях НАКЛОН и ОТРЕЗОК. Разница между этими алгоритмами может привести к разным результатам, когда данные не определены и коллинеарны. Например, если точки данных аргумента known_y's равны 0, а точки данных аргумента known_x равны 1:

ЛИНЕЙН возвращает значение 0. Алгоритм функции ЛИНЕЙН предназначен для возврата разумных результатов для коллинеарных данных, и в этом случае можно найти хотя бы один ответ.

Помимо использования ЛИНЕЙН для расчета статистики для других типов регрессии, вы можете использовать ЛИНЕЙН для расчета диапазона других типов регрессии, вводя функции переменных x и y в качестве рядов x и y для ЛИНЕЙН. Например, следующая формула:

работает, когда у вас есть один столбец значений y и один столбец значений x для вычисления кубического (многочленного порядка 3) приближения формы:

y = m1*x + m2*x^2 + m3*x^3 + b

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

Значение F-критерия, возвращаемое функцией ЛИНЕЙН, отличается от значения F-критерия, возвращаемого функцией FTEST. ЛИНЕЙН возвращает статистику F, а ФТЕСТ возвращает вероятность.

Примеры

Пример 1. Наклон и точка пересечения по оси Y

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.

В разделе "Корреляция" мы изучаем линейную корреляцию между двумя случайными величинами x и y. Теперь посмотрим на линию в плоскости xy, которая лучше всего соответствует данным (x1, y1 ), …, (xn, yn).

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

b = наклон линии
a = y-пересечение, то есть значение y, где линия пересекается с осью y

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

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

Для каждого i мы определяем ŷi как значение y для xi в этой строке и так далее

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

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

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

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

где уклон

и точка пересечения с осью –

Обратите внимание, что b = cov(x,y)/var(x< /эм>). Поскольку условия, включающие n, сокращаются, это можно рассматривать либо как ковариацию и дисперсию генеральной совокупности, либо как ковариацию и дисперсию выборки. Таким образом, a и b можно вычислить в Excel следующим образом, где R1 = массив значений y, а R2 = массив значений x:< /p>

b = НАКЛОН(R1, R2) = КОВАР(R1, R2) / ДИСП(R2)

a = INTERCEPT(R1, R2) = СРЕДНЕЕ(R1) – b * СРЕДНЕЕ(R2)

Свойство 1:

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

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

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

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

INTERCEPT(R1, R2) = точка пересечения по оси y линии регрессии, как описано выше

ПРОГНОЗ(x, R1, R2) вычисляет прогнозируемое значение y для заданного значения x. Таким образом, ПРОГНОЗ(x, R1, R2) = a + b * x, где a = INTERCEPT(R1, R2) и b = НАКЛОН( R1, R2).

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

TREND(R1, R2, R3) = функция массива, которая прогнозирует значения y, соответствующие значениям 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 проверки гипотезы на предмет корреляции по одной выборке и нанесите результаты на график.

Рисунок 1. Подгонка линии регрессии к данным примера 1

Используя теорему 1 и следующее за ней наблюдение, мы можем вычислить наклон b и точку пересечения по оси y a линии регрессии, которая лучше всего соответствует данным, как на рисунке 1. над. Используя возможности Excel для построения диаграмм, мы можем построить диаграмму рассеяния для данных в столбцах A и B выше, а затем выбрать «Макет»> «Анализ | Линия тренда» и выбрать «Линейная линия тренда» из списка параметров. Это отобразит линию регрессии, заданную уравнением y = bx + a (см. рис. 1).

42 мысли о «Методе наименьших квадратов»

Спасибо, Чарльз, за ​​этот превосходный сайт. Функция Excel ЛИНЕЙН — еще одна очень полезная функция для выполнения регрессионного анализа. Это позволяет аналитику фиксировать те же переменные регрессии, которые рассчитываются и/или отображаются в уравнении линии тренда диаграммы в ячейках рабочего листа. ЛИНЕЙН также может отображать определенные статистические данные, которые пакет инструментов анализа будет генерировать в ячейках рабочего листа.

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

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


Подгонка данных с помощью уравнения.

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

Подбор простых линейных уравнений

Excel предоставляет нам несколько инструментов для выполнения вычислений методом наименьших квадратов, но все они сосредоточены вокруг более простых функций: простых линейных функций формы
y=a.x+b, ya.exp(bx ), y=ax^b и так далее. С помощью некоторых приемов вы также можете выполнять LS на полиномах с помощью Excel.

Инструменты регрессии в надстройке Analysis Toolpak

Активируйте пакет инструментов анализа в списке надстроек (кнопка "Файл" или кнопка "Office", "Параметры Excel", вкладка "Надстройки", нажмите "Перейти"):


Список надстроек Excel с активированным пакетом анализа

Это добавит кнопку "Анализ данных" на вашу ленту на вкладке "Данные" в группе "Анализ" (это также место, где вы можете найти кнопку "Решатель", упомянутую позже):


Лента с кнопкой анализа данных

Нажмите эту кнопку, чтобы узнать, какие инструменты регрессии доступны.

Функции рабочего листа

Существует ряд функций рабочего листа, которые также можно использовать для проведения регрессионного анализа. Чтобы быстро получить к ним доступ, выберите пустую ячейку и нажмите Shift+F3, чтобы открыть мастер функций. В поле поиска введите «Регрессия» (разумеется, без кавычек). Excel отобразит соответствующие функции:


Мастер функций, показывающий функции регрессии

Выберите одну из них и нажмите ссылку "Справка по этой функции" в нижней части мастера функций, чтобы узнать больше о ее использовании.

Подгонка более сложных функций

Что делать, если вы хотите использовать более сложную функцию, например y=exp(a.x).sin(x) + b ?Как это можно сделать с помощью Excel?

Я разработал способ сделать это, который включает следующие шаги:

  • Создайте таблицу со значениями x и y.
  • Добавьте столбец с формулой функции модели, которая указывает на ваши x-es и на некоторые ячейки для констант(ы)
  • Имейте столбец, который вычисляет сумму квадратов.
  • Используйте Solver, чтобы найти константы, дающие наименьшую сумму квадратов.

Объяснение файла примера

Я создал пример файла, который вы можете использовать напрямую. Ниже вы найдете ссылку на файл и объяснение того, как он составлен.

Скачать

Скачать этот файл:

Как работает файл

Расчеты и данные сосредоточены на листе 1 файла. Наиболее важной областью является таблица, начинающаяся в ячейке A1:


Таблица данных в файле LS

В столбце A содержатся ваши значения x, а в столбце B — значения y. В третьем столбце содержится формула, которая вычисляет результат подобранного уравнения с использованием констант и значений x. В файле примера есть эта формула в столбце C:

Четвертый столбец таблицы используется для вычисления суммы квадратов. Формула:

Как вы, наверное, уже заметили, я использовал несколько названий диапазонов. Я объясню их ниже.

Имена диапазонов

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

Константы уравнения

Имена константных диапазонов указывают на вторую таблицу в файле:

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

Именно эту ячейку G11 мы пытаемся минимизировать с помощью надстройки Solver.

Использование решателя

Прежде всего необходимо установить надстройку Solver. Используйте диалоговое окно надстроек, которое я показал в верхней части этой статьи, и установите флажок рядом с «Надстройка Solver». Это добавит кнопку "Решатель" в то же место на ленте, что и кнопка "Анализ данных", которую я показывал ранее.

Убедившись, что формула модели правильно введена в столбец C и вычисления работают, нажмите кнопку "Решатель". Отображается диалоговое окно ниже:


Диалоговое окно "Решатель"

Убедитесь, что поле "Установить цель" указывает на ячейку, содержащую сумму квадратов. Выберите «Мин.» рядом с «До».

Поле «Изменение ячеек переменных» должно указывать ТОЛЬКО на ячейки, используемые вашей моделью, иначе расчет степеней свободы (на листе ANOVA) будет неправильным. Также убедитесь, что все неиспользуемые постоянные ячейки пусты, выделив их и нажав клавишу del.

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

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

Если вас устраивают текущие настройки Решателя, нажмите Решить. Через некоторое время откроется диалоговое окно «Результаты решения», предлагающее несколько вариантов дальнейших действий. Обратите внимание, что это также позволяет вам запросить несколько отчетов.

В примере файла показан конечный результат:

Анализ отклонений

На вкладке ANOVA вы можете найти таблицу дисперсионного анализа, которая выглядит следующим образом:


Таблица дисперсионного анализа

Самой важной ячейкой здесь является ячейка F2. Если значение в этой ячейке меньше 0,05, вероятность того, что ваша модель правильно соответствует данным, составляет 95 %. Так что меньше значит больше для этой ячейки, вы хотите, чтобы она оставалась ниже 0,05. Ячейка станет красной для значений выше 0,05.

Убедитесь, что значение в ячейке B2 ровно на единицу меньше количества констант, использованных вами для модели. Если нет, вернитесь на Лист1 и очистите ячейки, не используемые вашей моделью. Таким образом, если вы использовали const_a и const_b, то значение B2 (степени свободы модели) должно быть равно 1.

Заключение

Как вы уже убедились, подгонка сложных функций к вашим данным не очень сложна. Здесь на помощь приходит сочетание некоторых относительно простых формул и надстройки Solver.

Несколько советов, как один инженер другому; Будьте критичны, пожалуйста. Не верьте всему, что говорит вам Excel! Тщательно проанализируйте результаты, которые он возвращает, так как Solver может ошибаться и не давать вам наилучший результат!

Комментарии

Показаны последние 8 комментариев из 90 (Показать все комментарии):

Комментарий: Ян Карел Питерс (9-4-2018 08:39:19), глубокая ссылка на этот комментарий

Комментарий: Audu Innocent (9-4-2018 15:45:54), ссылка на этот комментарий

Пожалуйста, я не могу открыть этот файл linearls.zip
Мой Excel сказал, что он поврежден. Пожалуйста, помогите.
Спасибо

Комментарий: Ян Карел Питерс (9-4-2018 16:35:22) глубокая ссылка на этот комментарий

Странно, у меня файл работает нормально!
Может, попробовать загрузить файл еще раз?

Комментарий: Collin (25-7-2018 05:42:22) глубокая ссылка на этот комментарий

Здравствуйте,
Я изменил вашу таблицу, чтобы подогнать экспоненциально измененное распределение Гаусса к моим собственным данным, но кажется, что решение подходит только для const_a, что приводит к нежелательному совпадению. Есть ли способ изменить это?

Комментарий: Ян Карел Питерс (25-7-2018 09:52:51) глубокая ссылка на этот комментарий

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

Отличная работа. Большое спасибо! Это сработало для моих данных.

Как вы получили вкладку ANOVA? Был ли он запущен отдельно после того, как Solver опубликовал результаты? Если он запускался отдельно, укажите, как его создать.

Я создал вкладку ANOVA вручную, используя формулы. Таким образом, вы можете изучить формулы, чтобы увидеть, как они устроены.

Метод регрессии по методу наименьших квадратов — это форма регрессионного анализа, которая устанавливает связь между зависимой и независимой переменной с помощью линейной линии. Эта линия называется «линией наилучшего соответствия».

Регрессионный анализ — это статистический метод, с помощью которого можно оценить или предсказать неизвестные значения одной переменной по известным значениям другой переменной. Переменная, которая используется для прогнозирования переменной интереса, называется независимой или объясняющей переменной, а прогнозируемая переменная называется зависимой или объясняемой переменной.

Давайте рассмотрим две переменные, x и y. Они нанесены на график со значениями x по оси x и значениями y по оси y. Эти значения представлены точками на графике ниже. Через точки проводится прямая линия, называемая линией наилучшего соответствия.

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

Формула регрессии методом наименьших квадратов

Линия регрессии по методу наименьших квадратов рассчитывается по следующей формуле –

  • ŷ = зависимая переменная
  • x = независимая переменная
  • a = точка пересечения с осью y
  • b = наклон линии

Наклон линии b рассчитывается по следующей формуле –

Y-пересечение, ‘a’ рассчитывается по следующей формуле –

Линия наилучшего соответствия в регрессии методом наименьших квадратов

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

Давайте рассмотрим следующий график, на котором набор данных отложен по осям x и y. Эти точки данных представлены синими точками. Через эти точки проведены три линии – зеленая, красная и синяя. Зеленая линия проходит через одну точку, а красная линия проходит через три точки данных. Однако синяя линия проходит через четыре точки данных, и расстояние между остаточными точками до синей линии минимально по сравнению с двумя другими линиями.

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

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

Примеры линии регрессии методом наименьших квадратов

Давайте применим эти формулы в вопросе ниже –

Вы можете скачать этот шаблон Excel регрессии наименьших квадратов здесь — Шаблон Excel регрессии наименьших квадратов

Подробная информация об опыте работы технических специалистов в компании (в течение нескольких лет) и рейтинге их эффективности представлены в таблице ниже. Используя эти значения, оцените рейтинг производительности для технического специалиста с 20-летним опытом.

Наклон линии (b)

Y-пересечение (a)

Линия регрессии рассчитывается следующим образом:

Подставив 20 вместо значения x в формуле,

Рейтинг эффективности технического специалиста с 20-летним опытом работы оценивается в 92,3.

Уравнение регрессии методом наименьших квадратов с использованием Excel

Уравнение регрессии по методу наименьших квадратов можно вычислить с помощью Excel, выполнив следующие действия:

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

Преимущества

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

Недостатки

  • Метод наименьших квадратов основан на установлении наиболее близкой связи между заданным набором переменных. Механизм вычислений чувствителен к данным, и в случае каких-либо выбросов (исключительных данных) результаты могут существенно повлиять.
  • Этот тип расчета лучше всего подходит для линейных моделей. Для нелинейных уравнений применяются более исчерпывающие вычислительные механизмы.

Заключение

Рекомендуемые статьи

Это руководство по методу регрессии наименьших квадратов и его определению. Здесь мы обсуждаем формулу для расчета линии регрессии по методу наименьших квадратов вместе с примерами Excel. Вы можете узнать больше из следующих статей –

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