Регрессионный анализ Excel (Содержание)
- Регрессионный анализ в Excel
- Объяснение регрессии математически
- Как выполнить линейную регрессию в Excel?
- # 1 - инструмент регрессии с использованием Analysis ToolPak в Excel
- # 2 - Регрессионный анализ с использованием диаграммы рассеяния с Trendline в Excel
Регрессионный анализ в Excel
Линейная регрессия - это статистический метод, который исследует линейные отношения между зависимой переменной и одной или несколькими независимыми переменными.
- Зависимая переменная (она же переменная ответа / результата): представляет собой переменную, которая вас интересует и которую вы хотели бы предсказать на основе доступной информации о независимых переменных.
- Независимая переменная (она же переменная объяснения / предиктора): является / является переменной (переменными), от которой зависит переменная ответа. Это означает, что это переменные, с помощью которых можно предсказать переменную ответа.
Линейная связь означает, что изменение независимой переменной (переменных) вызывает изменение зависимой переменной.
Есть в основном два типа линейных отношений.
- Положительные линейные отношения: когда увеличивается независимая переменная, увеличивается и зависимая переменная.
- Отрицательные линейные отношения: когда независимая переменная увеличивается, зависимая переменная уменьшается.
Это были некоторые предварительные условия, прежде чем вы приступите к регрессионному анализу в Excel.
Существует два основных способа выполнения линейной регрессии в Excel с использованием:
- Инструмент регрессии с помощью Analysis ToolPak
- Точечная диаграмма с линией тренда
На самом деле есть еще один метод, который использует ручные формулы для расчета линейной регрессии. Но почему вы должны пойти на это, когда Excel делает расчеты для вас?
Поэтому мы будем говорить только о двух методах, описанных выше.
Предположим, у вас есть данные о росте и весе 10 человек. Если вы нанесете эту информацию на график, давайте посмотрим, что она дает.
Как показано на скриншоте выше, линейные отношения можно найти в Росте и Весе через график. Не стоит сейчас сильно увлекаться графикой, мы все равно собираемся углубиться во вторую часть этой статьи.
Объяснение регрессии математически
У нас есть математическое выражение для линейной регрессии, как показано ниже:
Y = aX + b + ε
Где,
- Y является зависимой переменной или переменной ответа.
- X является независимой переменной или предиктором.
- а - наклон линии регрессии. Что означает, что при изменении X происходит изменение Y на единицы «а».
- б перехватывает. Это значение Y принимает, когда значение X равно нулю.
- ε - член случайной ошибки. Происходит потому, что прогнозируемое значение Y никогда не будет точно таким же, как фактическое значение для данного X. Об этом термине ошибки нам не нужно беспокоиться. Поскольку есть некоторые программы, которые делают подсчет этого термина ошибки в бэкэнде для вас. Excel является одним из этого программного обеспечения.
В этом случае уравнение становится
Y = aX + b
Который может быть представлен как:
Вес = а * рост + б
Мы попытаемся выяснить значения этих a и b, используя методы, которые мы обсуждали выше.
Как выполнить линейную регрессию в Excel?
В следующей статье объясняются основы регрессионного анализа в Excel и показаны несколько различных способов выполнения линейной регрессии в Excel.
Вы можете скачать этот шаблон Excel для анализа регрессии здесь - Шаблон Excel для анализа регрессии# 1 - инструмент регрессии с использованием Analysis ToolPak в Excel
В нашем примере мы попытаемся подогнать регрессию для значений веса (которая является зависимой переменной) с помощью значений высоты (которая является независимой переменной).
- В электронной таблице Excel нажмите « Анализ данных» (присутствует в разделе «Группа анализа ») в разделе « Данные».
- Ищите регрессию . Выберите его и нажмите ОК.
- Используйте следующие входы в области регрессии, которая открывается.
- Диапазон ввода Y : выберите ячейки, содержащие вашу зависимую переменную (в этом примере B1: B11)
- Диапазон ввода X : выберите ячейки, содержащие вашу независимую переменную (в этом примере A1: A11).
- Установите флажок « Метки», если у ваших данных есть имена столбцов (в этом примере у нас есть имена столбцов).
- Уровень достоверности установлен на 95% по умолчанию, который может быть изменен в соответствии с требованиями пользователей.
- В разделе « Параметры вывода» вы можете указать, где вы хотите видеть результаты регрессионного анализа в Excel. В этом случае мы хотим видеть вывод на том же листе. Следовательно, данный диапазон соответственно.
- Под опцией Residuals у вас есть дополнительные входные данные, такие как Residuals, Residual Plots, Standardized Residuals, Line Fit Plot, которые вы можете выбрать в соответствии со своими потребностями. В этом случае установите флажок « Остатки», чтобы мы могли видеть дисперсию между прогнозируемыми и фактическими значениями.
- В разделе « Нормальная вероятность» вы можете выбрать « Нормальные вероятностные графики», которые помогут вам проверить нормальность предикторов. Нажмите на ОК .
- Excel вычислит для вас регрессионный анализ за доли секунды.
До этого это было легко и не так логично. Однако интерпретировать этот вывод и сделать ценные выводы из него - непростая задача.
Одна важная часть всего этого вывода - R Square / Скорректированная R Square под таблицей SUMMARY OUTPUT. Который предоставляет информацию, насколько хорошо подходит наша модель. В этом случае значение R Square составляет 0, 9547. Что объясняет, что модель имеет точность 95, 47% (хорошо подходит). Или на другом языке, информация о переменной Y объясняется 95, 47% переменной X.
Другая важная часть всего вывода - это таблица коэффициентов. Он дает значения коэффициентов, которые можно использовать для построения модели будущих прогнозов.
Теперь наше уравнение регрессии для предсказания становится:
Вес = 0, 6746 * Высота - 38, 45508 (значение наклона для Роста составляет 0, 6746…, а перехват –38, 45508…)
Вы получили то, что вы определили? Вы определили функцию, в которой вам просто нужно указать значение высоты, и вы получите значение веса.
# 2 - Регрессионный анализ с использованием диаграммы рассеяния с Trendline в Excel
Теперь мы увидим, как в Excel мы можем подогнать уравнение регрессии на самой диаграмме рассеяния.
- Выберите все ваши данные с двумя столбцами (включая заголовки).
- Нажмите Вставить и выберите Scatter Plot в разделе графиков, как показано на рисунке ниже.
- Смотрите выходной график.
- Теперь нам нужно иметь линию регрессии с наименьшим квадратом на этом графике. Чтобы добавить эту линию, щелкните правой кнопкой мыши любую из точек данных на графике и выберите опцию Добавить линию тренда .
- Это позволит вам иметь линию тренда наименьшего квадрата регрессии, как показано ниже.
- Под опцией Format Trendline установите флажок Отображать уравнение на графике.
- Это позволяет увидеть уравнение линии наименьших квадратов регрессии на графике.
Это уравнение, с помощью которого мы можем предсказать значения веса для любого заданного набора значений высоты.
Что нужно помнить об регрессионном анализе в Excel
- Вы можете изменить макет линии тренда в разделе «Формат линии тренда» на точечной диаграмме.
- Всегда рекомендуется взглянуть на остаточные графики во время регрессионного анализа с использованием Data Analysis ToolPak в Excel. Это дает вам лучшее понимание разброса фактических значений Y и оценочных значений X.
- Простая линейная регрессия в Excel не требует проверки ANOVA и Скорректированного квадрата R. Эти особенности могут быть приняты во внимание для множественной линейной регрессии. Что выходит за рамки этой статьи.
Рекомендуемые статьи
Это было руководство по регрессионному анализу в Excel. Здесь мы обсудим, как сделать анализ регрессии в Excel, а также примеры Excel и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
- Инструмент Excel для анализа данных
- Рассчитать ANOVA в Excel
- Как найти Скользящие средние Excel
- Z TEST Примеры в Excel