Наклон линии регрессии — это мера крутизны линии.
Это числовое значение, которое говорит нам, как связаны две переменные. Оно показывает, насколько изменится зависимая переменная в случае изменения независимой переменной.
Есть два способа найти наклон линии регрессии для заданного набора переменных в Excel:
- Использование функции НАКЛОН
- Использование точечной диаграммы Excel
В этом уроке я покажу вам, как рассчитать уклон, используя каждый из этих методов.
Что такое наклон? Обзор
Наклон — это значение, которое сообщает нам, как два значения (обычно называемые значениями x и y) связаны друг с другом.
Простой пример
Если у вас есть данные о росте и годовом доходе некоторых людей, и вы рассчитываете наклон для этих данных, он покажет, существует ли положительная или отрицательная корреляция между этими точками данных.
Значение наклона может быть положительным или отрицательным. В нашем примере, если значение наклона равно 138, это означает, что существует положительная корреляция между ростом и доходом людей — если рост увеличится на 1 сантиметр, доход, скорее всего, увеличится на 138 долларов.
Помимо наклона, ещё одна важная величина — это точка пересечения. Уравнение выглядит так:
Y = наклон * X + точка пересечения
В этом уравнении мы уже вычислили наклон, но чтобы точно знать, каким будет значение Y для данного значения X, вам также нужно знать точку пересечения. К счастью, в Excel есть формула и для этого.
Метод 1: Использование функции НАКЛОН
Самый простой способ рассчитать уклон в Excel — использовать встроенную функцию НАКЛОН.
Она находит значение наклона заданного набора координат x-y за один шаг. Хотя вычисление уклона вручную может быть трудным, с функцией НАКЛОН вам просто нужно указать значения x и y, и она выполняет всю работу.
Синтаксис функции НАКЛОН
=НАКЛОН(известные_y; известные_x)
Здесь известные_y и известные_x — это массив или диапазон ячеек с числовыми зависимыми значениями данных.
Предположим, у вас есть набор данных, где рост (в см) — значения X, а средний годовой доход (в долларах) — значения Y.
Ниже приведена формула для расчёта уклона с использованием этого набора данных:
=НАКЛОН(B2:B11;A2:A11)
Результат говорит, что при увеличении роста на 1 см доход увеличится на 138,58 долларов США.
Ещё одна распространённая величина, которую часто вычисляют при работе с уклоном, — значение точки пересечения. Это легко сделать с помощью функции ОТРЕЗОК:
=ОТРЕЗОК(B2:B11;A2:A11)
С учётом этого наше уравнение для данного набора данных становится:
Y = 138,56 * X + 65803,2
Так, если спросить, каким будет доход человека ростом 165 см, можно легко рассчитать: Y = 138,56 * 165 + 65803,2. Значения наклона и точки пересечения могут быть как положительными, так и отрицательными.
Что следует помнить при использовании функции НАКЛОН
- Аргументы функции НАКЛОН должны быть числовыми (значения дат также принимаются). Если ячейка пуста или содержит текстовую строку, она будет проигнорирована. Если в ячейке стоит «0», оно будет использовано в вычислении.
- Должно быть равное количество значений x и y. Если диапазоны разного размера, вы получите ошибку #Н/Д.
- Должно быть несколько наборов точек, иначе функция НАКЛОН вернёт ошибку #ДЕЛ/0!.
Метод 2: Использование точечной диаграммы для получения значения наклона
Если вы предпочитаете визуализировать данные и линию регрессии, можно нанести данные на диаграмму рассеяния и использовать её, чтобы найти наклон и точку пересечения линии тренда (линии наилучшего соответствия).
Ниже приведены шаги для этого:
Выберите точки данных X и Y (в нашем примере — столбцы роста и дохода).
Перейдите на вкладку «Вставка» на ленте.
Щёлкните раскрывающееся меню «Вставить точечную диаграмму» (в группе «Диаграммы»).
В появившемся списке выберите вариант «Точечная диаграмма».
Это вставит диаграмму, отображающую ваши значения x-y в виде точек рассеяния.
Щёлкните правой кнопкой мыши одну из точек и выберите «Добавить линию тренда». Линия тренда будет вставлена, а справа откроется панель «Формат линии тренда».
На панели «Формат линии тренда» в разделе «Параметры линии тренда» установите флажок «Показывать уравнение на диаграмме».
Закройте панель «Формат линии тренда».
Приведённые выше шаги позволят вставить диаграмму рассеяния с линией тренда, и линия тренда также будет иметь уравнение наклона и пересечения.
В нашем примере мы получаем следующее уравнение:
y = 138,56x + 65803
Здесь 138,56 — наклон линии регрессии, а 65803 — точка пересечения линии регрессии. Можно сравнить это со значениями, которые мы получили от функций НАКЛОН и ОТРЕЗОК (значения совпадают).
Если значение наклона положительное, линия тренда идёт вверх, а если отрицательное — вниз. Крутизна склона будет зависеть от значения уклона.
Итак, это два действительно простых способа, которые вы можете использовать для вычисления наклона и значения пересечения набора данных в Excel.
Надеюсь, вы нашли этот урок полезным.
Хотите освоить Excel глубже?
Больше полезных формул, приёмов и готовых решений — на Vip Excel