Как добавить линейную регрессию в графики Excel

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

Добавление линии тренда линейной регрессии на график

Сначала откройте пустую электронную таблицу Excel, выберите ячейку D3 и введите «Месяц» в качестве заголовка столбца, который будет переменной x. Затем щелкните ячейку E3 и введите «Значение Y» в качестве заголовка столбца переменной y. Это в основном таблица с записанным рядом значений данных за месяцы январь-май. Поэтому введите месяцы в ячейки от D4 до D8 и значения данных для них в ячейки от E4 до E8, как показано на снимке непосредственно ниже.

Теперь вы можете настроить график рассеяния для этой таблицы. Выделите все ячейки в таблице с помощью курсора. Нажмите вкладку Вставка и выберите рассеивать > Скаттер только с маркерами добавить график в электронную таблицу, как показано ниже. Кроме того, вы можете нажать горячую клавишу Alt + F1, чтобы вставить гистограмму. Затем вы должны щелкнуть правой кнопкой мыши по графику и выбрать Изменить тип диаграммы > X Y (разброс) > Скаттер только с маркерами,

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

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

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

Форматирование линии тренда линейной регрессии

Чтобы отформатировать линию тренда, щелкните ее правой кнопкой мыши и выберите Формат Trendline, Это снова откроет окно Format Trendline, из которого вы можете нажать Цвет линии, Выбрать Сплошная линия и нажмите цвет поле, чтобы открыть палитру, из которой вы можете выбрать альтернативный цвет для линии тренда.

Чтобы настроить стиль линии, перейдите на вкладку «Стиль линии». Затем вы можете настроить ширину стрелки и настроить параметры стрелки. нажмите Настройки стрелки кнопки для добавления стрелок в линию.

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

Прогнозирование значений с помощью линейной регрессии

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

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

Чтобы добавить значение квадрата r на график, щелкните Отображение значения R-квадрата на графике флажок Это добавляет г квадрат на график чуть ниже уравнения, как на снимке ниже. Вы можете перетащить поле уравнения и корреляции, чтобы изменить его положение на графике рассеяния.

Функции линейной регрессии

Excel также включает функции линейной регрессии, с помощью которых вы можете найти значения наклона, точки пересечения и r квадрата для массивов данных y и x. Выберите ячейку электронной таблицы, чтобы добавить одну из этих функций, а затем нажмите Вставить функцию кнопка. Функции линейной регрессии являются статистическими, поэтому выберите статистическая из выпадающего меню категории. Затем вы можете выбрать RSQ, SLOPE или INTERCEPT чтобы открыть их окна функций, как показано ниже.

Окна RSQ, SLOPE и INTERCEPT практически одинаковы. Они включают в себя поля Known_y и Known_x, которые можно выбрать для добавления значений переменных y и x из вашей таблицы. Обратите внимание, что ячейки должны содержать только цифры, поэтому замените месяцы в таблице соответствующими цифрами, такими как 1 для января, 2 для февраля и т. Д. Затем нажмите Хорошо закрыть окно и добавить функцию в электронную таблицу.

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