Как построить график регрессии в excel

Как выполнить простую линейную регрессию в Excel

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

В этом руководстве объясняется, как выполнить простую линейную регрессию в Excel.

Пример: простая линейная регрессия в Excel

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

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

Выполните следующие шаги в Excel, чтобы провести простую линейную регрессию.

Шаг 1: Введите данные.

Введите следующие данные о количестве часов обучения и экзаменационном балле, полученном для 20 студентов:

Шаг 2: Визуализируйте данные.

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

Выделите данные в столбцах A и B. В верхней ленте Excel перейдите на вкладку « Вставка ». В группе « Диаграммы » нажмите « Вставить разброс» (X, Y) и выберите первый вариант под названием « Разброс ». Это автоматически создаст следующую диаграмму рассеяния:

Количество часов обучения показано на оси x, а баллы за экзамены показаны на оси y. Мы видим, что между двумя переменными существует линейная зависимость: большее количество часов обучения связано с более высокими баллами на экзаменах.

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

Шаг 3: Выполните простую линейную регрессию.

В верхней ленте Excel перейдите на вкладку « Данные » и нажмите « Анализ данных».Если вы не видите эту опцию, вам необходимо сначала установить бесплатный пакет инструментов анализа .

Как только вы нажмете « Анализ данных», появится новое окно. Выберите «Регрессия» и нажмите «ОК».

Для Input Y Range заполните массив значений для переменной ответа. Для Input X Range заполните массив значений для независимой переменной.

Установите флажок рядом с Метки , чтобы Excel знал, что мы включили имена переменных во входные диапазоны.

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

Затем нажмите ОК .

Автоматически появится следующий вывод:

Шаг 4: Интерпретируйте вывод.

Вот как интерпретировать наиболее релевантные числа в выводе:

R-квадрат: 0,7273.Это известно как коэффициент детерминации. Это доля дисперсии переменной отклика, которая может быть объяснена объясняющей переменной. В этом примере 72,73 % различий в баллах за экзамены можно объяснить количеством часов обучения.

Стандартная ошибка: 5.2805.Это среднее расстояние, на которое наблюдаемые значения отходят от линии регрессии. В этом примере наблюдаемые значения отклоняются от линии регрессии в среднем на 5,2805 единиц.

Ф: 47,9952.Это общая F-статистика для регрессионной модели, рассчитанная как MS регрессии / остаточная MS.

Значение F: 0,0000.Это p-значение, связанное с общей статистикой F. Он говорит нам, является ли регрессионная модель статистически значимой. Другими словами, он говорит нам, имеет ли независимая переменная статистически значимую связь с переменной отклика. В этом случае p-значение меньше 0,05, что указывает на наличие статистически значимой связи между отработанными часами и полученными экзаменационными баллами.

Коэффициенты: коэффициенты дают нам числа, необходимые для написания оценочного уравнения регрессии. В этом примере оцененное уравнение регрессии:

экзаменационный балл = 67,16 + 5,2503*(часов)

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

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

Например, ожидается, что студент, который занимается три часа, получит на экзамене 82,91 балла:

экзаменационный балл = 67,16 + 5,2503*(3) = 82,91

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Линейный регрессионный анализ в Excel

Представьте себе следующее: вам предоставили множество различных данных и попросили спрогнозировать показатели продаж вашей компании в следующем году. Вы обнаружили десятки, возможно, даже сотни факторов, которые могут повлиять на показатели. Но как узнать, какие из них действительно важны? Запустите регрессионный анализ в Excel. Он даст вам ответ на этот и многие другие вопросы: какие факторыНасколько тесно эти факторы связаны друг с другом? И насколько вы можете быть уверены в прогнозах?

Регрессионный анализ в Excel — основы

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

Зависимая переменная (aka критерий переменная) — это основной фактор, который вы пытаетесь понять и предсказать.

Независимые переменные (aka пояснительная переменные, или предикторы ) — это факторы, которые могут повлиять на зависимую переменную.

Смотрите также: Как выделить дублирующийся текст / слова в ячейке Excel

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

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

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

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

Уравнение линейной регрессии

Математически линейная регрессия определяется этим уравнением:

• x является независимой переменной.
• y является зависимой переменной.
• a это Y-интерцепт что является ожидаемым средним значением y когда все x переменные равны 0. На графике регрессии это точка, где линия пересекает ось Y.
• b — это наклон линии регрессии, которая является скоростью изменения для y в качестве x изменения.
• ε случайный член ошибки, который представляет собой разницу между фактическим значением зависимой переменной и ее прогнозируемым значением.

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

Для нашего примера уравнение линейной регрессии имеет следующий вид:

Проданные зонтики = b * количество осадков + a

Существует несколько различных способов найти a и b Три основных метода проведения линейного регрессионного анализа в Excel:

• Инструмент регрессии, включенный в пакет Analysis ToolPak
• Диаграмма разброса с линией тренда
• Формула линейной регрессии

Ниже приведены подробные инструкции по использованию каждого метода.

Как выполнить линейную регрессию в Excel с помощью Analysis ToolPak

В этом примере показано, как запустить регрессию в Excel с помощью специального инструмента, входящего в состав надстройки Analysis ToolPak.

Включите надстройку Analysis ToolPak

Analysis ToolPak доступен во всех версиях Excel 365 до 2003, но по умолчанию он не включен. Поэтому его нужно включить вручную. Вот как это сделать:

1. В Excel нажмите кнопку Файл > Опции .
2. В Параметры Excel в диалоговом окне выберите Дополнения на левой боковой панели, убедитесь, что Надстройки Excel выбран в Управляйте и нажмите кнопку Перейти .
3. В Дополнения диалогового окна, поставьте галочку Пакет инструментов для анализа , и нажмите OK :

Это добавит Анализ данных инструменты для Данные на вкладке ленты Excel.

Провести регрессионный анализ

В этом примере мы собираемся провести простую линейную регрессию в Excel. У нас есть список среднемесячных осадков за последние 24 месяца в столбце B, который является нашей независимой переменной (предиктором), и количество проданных зонтов в столбце C, который является зависимой переменной. Конечно, существует множество других факторов, которые могут повлиять на продажи, но пока мы сосредоточимся только на этих двух переменных:

При включенном Analysis Toolpak, выполните следующие шаги для проведения регрессионного анализа в Excel:

1. На Данные во вкладке Анализ группу, нажмите кнопку Анализ данных кнопка.
2. Выберите Регрессия и нажмите OK .
3. В Регрессия диалоговое окно, настройте следующие параметры:
   • Выберите Входной диапазон Y , который является вашим зависимая переменная В нашем случае это продажа зонтиков (C1:C25).
   • Выберите Вход X Диапазон , т.е. ваш независимая переменная В данном примере это среднемесячное количество осадков (B1:B25).

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

• Проверьте Коробка с этикетками если есть заголовки в верхней части диапазонов X и Y.
• Выберите предпочтительный Возможность вывода, в нашем случае — новый рабочий лист.
• По желанию выберите Остатки флажок, чтобы получить разницу между прогнозируемым и фактическим значениями.

Нажмите OK и проследите за выводом регрессионного анализа, созданного Excel.

Интерпретировать результаты регрессионного анализа

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

Смотрите также: Формула диапазона дат Excel SUMIFS — суммировать, если между двумя датами

Выходные данные регрессионного анализа: Сводный выход

Эта часть говорит о том, насколько хорошо рассчитанное уравнение линейной регрессии соответствует вашим исходным данным.

Вот что означает каждая часть информации:

Множественные R Это С Коэффициент корреляции Коэффициент корреляции может иметь любое значение от -1 до 1, а его абсолютное значение указывает на силу связи. Чем больше абсолютное значение, тем сильнее связь:

• 1 означает сильную положительную связь
• -1 означает сильную отрицательную связь
• 0 означает полное отсутствие отношений

R-квадрат . Это Коэффициент детерминации R2 рассчитывается из общей суммы квадратов, точнее, это сумма квадратов отклонений исходных данных от среднего значения.

В нашем примере R2 равен 0,91 (округлим до двузначного числа), что является хорошим показателем. Это означает, что 91% наших значений соответствуют модели регрессионного анализа. Другими словами, 91% зависимых переменных (значения y) объясняются независимыми переменными (значениями x). Обычно хорошим показателем считается R Squared, равный 95% или более.

Скорректированный квадрат R . Это Площадь с поправкой на количество независимых переменных в модели. Вы захотите использовать это значение вместо Площадь для множественного регрессионного анализа.

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

Наблюдения Это просто количество наблюдений в вашей модели.

Выход регрессионного анализа: ANOVA

Вторая часть вывода — дисперсионный анализ (ANOVA):

По сути, он разделяет сумму квадратов на отдельные компоненты, которые дают информацию об уровнях изменчивости в вашей регрессионной модели:

• df число степеней свободы, связанных с источниками дисперсии.
• SS Чем меньше остаточный SS по сравнению с общим SS, тем лучше ваша модель соответствует данным.
• MS средний квадрат.
• F F-статистика, или F-тест для нулевой гипотезы. Используется для проверки общей значимости модели.
• Значимость F P-значение F.

Часть ANOVA редко используется для простого линейного регрессионного анализа в Excel, но вы обязательно должны внимательно изучить последний компонент. The Значимость F дает представление о том, насколько надежны (статистически значимы) ваши результаты. Если значение Significance F меньше 0,05 (5%), ваша модель в порядке. Если оно больше 0,05, вам, вероятно, лучше выбрать другую независимую переменную.

Результаты регрессионного анализа: коэффициенты

В этом разделе представлена конкретная информация о компонентах вашего анализа:

Наиболее полезным компонентом в этом разделе является Коэффициенты Она позволяет построить уравнение линейной регрессии в Excel:

Для нашего набора данных, где y это количество проданных зонтиков, а x — среднемесячное количество осадков, наша формула линейной регрессии выглядит следующим образом:

Y = Коэффициент осадков * x + Перехват

При использовании значений a и b, округленных до трех знаков после запятой, получается:

Например, при среднемесячном количестве осадков, равном 82 мм, объем продаж зонтов составит приблизительно 17,8:

Аналогичным образом можно узнать, сколько зонтиков будет продано при любом другом заданном вами месячном количестве осадков (переменная x).

Выходные данные регрессионного анализа: остатки

Если сравнить расчетное и фактическое количество проданных зонтов, соответствующее месячному количеству осадков в 82 мм, то можно увидеть, что эти цифры немного отличаются:

• Предполагаемый: 17,8 (рассчитано выше)
• Фактически: 15 (строка 2 исходных данных)

Почему разница? Потому что независимые переменные никогда не являются идеальными предсказателями зависимых переменных. А остатки могут помочь вам понять, насколько далеки фактические значения от предсказанных:

Для первой точки данных (количество осадков 82 мм) остаток составляет примерно -2,8. Прибавляем это число к предсказанному значению и получаем фактическое значение: 17,8 — 2,8 = 15.

Как построить график линейной регрессии в Excel

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

1. Выберите два столбца с данными, включая заголовки.
2. На Вставка во вкладке Чаты группу, нажмите кнопку Диаграмма рассеяния значок и выберите Разброс миниатюра (первая): Это позволит вставить в рабочий лист диаграмму рассеяния, которая будет похожа на эту:
3. Теперь нам нужно построить линию регрессии наименьших квадратов. Чтобы сделать это, щелкните правой кнопкой мыши на любой точке и выберите Добавить линию тренда. из контекстного меню.
4. На правой панели выберите Линейный форму линии тренда и, по желанию, проверить Отображение уравнения на графике чтобы получить формулу регрессии: Как вы можете заметить, уравнение регрессии, которое Excel создал для нас, совпадает с формулой линейной регрессии, которую мы построили на основе вывода Coefficients.
5. Переключитесь на Заполнение & линия и настройте линию по своему вкусу. Например, вы можете выбрать другой цвет линии и использовать сплошную линию вместо пунктирной (выберите Сплошная линия в меню Тип тире коробка):

На данном этапе ваш график уже выглядит как приличный график регрессии:

Тем не менее, вы можете захотеть сделать еще несколько улучшений:

• Перетащите уравнение туда, куда считаете нужным.
• Добавьте заголовки осей ( Элементы диаграммы кнопка> Осевые титулы ).
• Если точки данных начинаются в середине горизонтальной и/или вертикальной оси, как в данном примере, возможно, вам захочется избавиться от лишнего белого пространства. Следующий совет объясняет, как это сделать: масштабируйте оси графика, чтобы уменьшить белое пространство. А вот так выглядит наш улучшенный график регрессии: Важное замечание! На графике регрессии независимая переменная всегда должна находиться на оси X, а зависимая — на оси Y. Если ваш график построен в обратном порядке, поменяйте местами столбцы в рабочем листе, а затем постройте график заново. Если вам не разрешено переставлять исходные данные, то вы можете поменять местами оси X и Y непосредственно на графике.

Как сделать регрессию в Excel с помощью формул

В Microsoft Excel есть несколько статистических функций, которые могут помочь вам провести линейный регрессионный анализ, например, LINEST, SLOPE, INTERCEPT и CORREL.

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

Поскольку функция LINEST возвращает массив значений, вы должны ввести ее как формулу массива. Выделите две соседние ячейки в одной строке, E2:F2 в нашем случае, введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.

Формула возвращает b коэффициент (E1) и a константа (F1) для уже знакомого уравнения линейной регрессии:

y = bx + a

Если вы избегаете использования формул массивов в своих рабочих листах, вы можете вычислить a и b индивидуально с помощью обычных формул:

Получите Y-интерцепт (a):

Получите наклон (b):

Кроме того, вы можете найти коэффициент корреляции ( Множественные R в итоговом выводе регрессионного анализа), который показывает, насколько сильно две переменные связаны друг с другом:

На следующем снимке экрана показаны все эти формулы регрессии Excel в действии:

Совет. Если вы хотите получить дополнительную статистику для регрессионного анализа, используйте функцию LINEST с параметром s татуировки параметр установлен в TRUE, как показано в этом примере.

Вот как выполняется линейная регрессия в Excel. При этом следует помнить, что Microsoft Excel не является статистической программой. Если вам необходимо выполнить регрессионный анализ на профессиональном уровне, вам может понадобиться целевое программное обеспечение, такое как XLSTAT, RegressIt и т.д.

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

Практическая тетрадь

Регрессионный анализ в Excel — примеры (файл.xlsx)

Предыдущий пост Панель инструментов быстрого доступа в Excel: как настроить, переместить и сбросить
Следующий пост Как использовать функцию MIN в Excel

Michael Brown

Майкл Браун — увлеченный технологический энтузиаст, стремящийся упростить сложные процессы с помощью программных инструментов. Имея более чем десятилетний опыт работы в технологической отрасли, он отточил свои навыки в Microsoft Excel и Outlook, а также в Google Sheets и Docs. Блог Майкла посвящен тому, чтобы делиться своими знаниями и опытом с другими, предоставляя простые советы и учебные пособия для повышения производительности и эффективности. Являетесь ли вы опытным профессионалом или новичком, в блоге Майкла вы найдете ценную информацию и практические советы, которые помогут вам максимально эффективно использовать эти важные программные инструменты.

Регрессионный анализ в Excel. Подробная иллюстрированная инструкция

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

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

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

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

Как подключить пакет анализа в программе Excel

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

1. Открыть меню «Файл». Для этого нужно нажать на одноименную кнопку слева от вкладки «Главная».
2. Далее у нас откроется меню настроек файла. Нас интересует вкладка «Параметры».
3. Теперь у нас появляется возможность настроить параметры Excel. Затем переходим в меню надстроек, выставляем надстройки Excel в перечне, который находится внизу и нажимаем на «Перейти».
4. После этого появляется окошко, в котором можно управлять существующими надстройками. Нас интересует опция «Пакет анализа». Нужно поставить галочку возле нее и нажать на «ОК».

Теперь у нас на вкладке «Данные» добавился новый блок инструментов, в котором появилась кнопка «Анализ данных».

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

Какие бывают виды регрессионного анализа

Выделяют несколько видов регрессий:

1. Параболическая.
2. Степенная.
3. Логарифмическая.
4. Экспоненциальная.
5. Показательная.
6. Гиперболическая.
7. Линейная регрессия.

Давайте более подробно рассмотрим последнюю разновидность в программе для построения электронных таблиц Excel.

Линейная регрессия в Excel

Давайте приведем небольшой пример. Допустим, у нас есть файл с диапазоном данных, содержащим информацию о том, какая средняя температура воздуха за окном в определенный временной период и сколько было покупателей в этот же день. Для этого нужно использовать регрессионный анализ, разобравшись, каким именно способом климатические условия (то есть, температура воздуха) оказывают влияние на то, как это торговое заведение посещается. Для этого нам нужно составить уравнение регрессии, которое выглядит так: У = а0 + а1х1 +…+акхк. Давайте приведем небольшую расшифровку этих данных.

1. Y. Обозначает переменную, которая зависима от определенных факторов. Именно ее нам и нужно проанализировать. В нашем примере в качестве такой переменной выступает количество покупателей.
2. х – это совокупность факторов, которые способны изменить значение переменной. В данном случае ею выступает температура воздуха. Но могут включаться и другие значения, которые могут быть измерены математическими.
3. а – это коэффициент регрессии. Необходим для того, чтобы формула могла определить не только наличие самого фактора, но и степень его влияния на переменную Y.
4. k – это общее число всех факторов, которые имеются на текущий момент.

Чтобы осуществить анализ линейной регрессии, необходимо выполнить следующие шаги:

1. Сделать клик по кнопке «Анализ данных», появившейся после добавления соответствующей надстройки. Она располагается на вкладке «Данные» в группе «Анализ».
2. После этого появится крошечное диалоговое окно. Но несмотря на это, оно содержит достаточное количество информации о том, какие инструменты анализа можно использовать. Нас же интересует регрессия. Соответствующий пункт и нужно выбрать. После того, как он будет выделен, можно нажимать кнопку «ОК».
3. После этого нам нужно настроить регрессию. В соответствующем диалоговом окне необходимо обязательно заполнить входные интервалы X и Y. К оставшимся параметрам, если их не заполнять, будут применены настройки, запрограммированные по умолчанию. В поле с входным интервалом Y записываем тот диапазон, в котором находятся переменные, для которых мы пытаемся установить влияние имеющихся факторов. Простыми словами, общее число покупателей. Есть несколько способов ввода адреса: с клавиатуры или же непосредственное их выделение с помощью мыши. Естественно, проще первый вариант в большинстве случаев, но если человек владеет слепым методом печати и точно помнит адрес диапазона, то вручную ему будет все же проще.

Далее вводим факторы (точнее, содержащие информацию о них ячейки) в поле «Входной интервал X». Как указывалось ранее, перед нами стоит задача понять, как влияет температура воздуха на количество клиентов. Для этого необходимо записать адреса ячеек, входящих в столбик «Температура». Как это сделать? Та точно так же, как и с предыдущим полем: ввести вручную или выделить соответствующий диапазон мышью.

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

Как интерпретировать результаты анализа

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

Самое главное значение, на которое мы будем ориентироваться – это R-квадрат. В нем записывается качество используемой модели. Чем он выше, тем оно выше. Если оно меньше 0,5, то зависимость считается плохой, если выше – то уже лучше. Чем ближе к 1, тем лучше. Соответственно, максимальный коэффициент – 1.

Также нужно обратить внимание на еще один важный показатель. Его можно найти в ячейке, которая находится на стыке строки Y-пересечение и колонки «Коэффициенты». Здесь можно увидеть значение Y, которое будет равно нулю при определенных условиях. Также можно понять, насколько наша зависимая переменная является зависимой от факторов. Для этого нужно посмотреть, какая цифра стоит на пересечении граф Переменная X1» и «Коэффициенты». Чем коэффициент выше, тем лучше.

Видим, что программа Microsoft Excel открывает широкие возможности для регрессионного анализа. Но конечно, нужна дополнительная подготовка, чтобы читать эти результаты. Но если вы уже разбираетесь в статистике, то будет значительно проще. А теперь давайте приведем некоторые простые примеры, чтобы было более наглядно понятно, как линейная регрессия проводится на практике.

Пример регрессионного анализа №1

А теперь настало время разобрать практические кейсы, как можно использовать линейную регрессию. Допустим, у нас есть набор данных о расходах на ТВ-рекламу, интернет-продвижение и о том, сколько получилось реализовать товара в российской национальной валюте. Все эти данные упакованы в таблицу. Перед нами стоит задача – определить коэффициенты регрессии для независимых переменных (то есть, в нашем случае ими выступают расходы на рекламу по ТВ и в интернете, поскольку оба значения влияют на объем реализуемых товаров). Последовательность действий такая:

1. Открыть рабочий лист и ввести данные.
2. Активировать инструмент регрессия способом, описанным выше.
3. В появившемся диалоговом окне необходимо задать входной интервал X, Y, задать метки
4. Также не стоит забывать ввести выходной интервал. Для выполнения этой задачи необходимо также указать такие параметры, как «График нормальной вероятности» и «График остатков».

Видим, что для этого кейса нам не нужно принципиально отходить от схемы, описанной выше. Линейная регрессия в этом случае позволяет уменьшить расходы на рекламу и увеличить отдачу от неё. То есть, выражаясь маркетинговым языком, увеличить ROMI – коэффициент возвратности инвестиций на маркетинг.

Пример регрессионного анализа №2

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

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

Что же собой являет инструмент «Поиск решения»? Это надстройка, позволяющая найти оптимальный способ решения определенной задачи. Она имеет два основных параметра: целевая функция и ограничения. Таким образом, пользователь может находить оптимальную сумму затрат для рекламу в определенных условиях. Это одно из главных преимуществ данного инструмента.

Точно также, как в случае с пакетом анализа, инструмент поиска решения требует наличия математической модели. В качестве неё и выступает целевая функция. В нашем случае она следующая: Y= 2102438,6 + 6,4004 X1 — 54,068 X2 > max. В качестве используемых ограничений используется следующее выражение: X1 + X2 = 0, X2 >=0.

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

1. Для начала нажать «Параметры Excel», после чего отправиться в категорию «Надстройки».
2. После этого в поле «Управление» найти «Надстройки Excel» и кликнуть по «Перейти».
3. После этого в списке надстроек активировать «Поиск решения».

После нажатия клавиши ОК надстройка успешно активирована. Далее достаточно просто нажимать на соответствующую кнопку на вкладке «Данные» в той же группе, что и пакет анализа и задать подходящие параметры. После этого программа все сделает самостоятельно. Таким образом, использование регрессии в Excel – очень простая штука. Значительно легче, чем может показаться на первый взгляд, поскольку большую часть действий выполняет программа. Достаточно просто вбить правильные настройки, и дальше можно расслабиться. И да, нужно еще интерпретировать результаты правильно. Но это не проблема. Успехов.

Exceltip

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

Как рассчитать регрессию в Excel

Опубликовано 14.01.2014 Автор Ренат Лотфуллин

Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (x, y). Уравнение для прямой линии, известное как линейное уравнение, представлено ниже:

ŷ — ожидаемое значение у при заданном значении х,

x — независимая переменная,

a — отрезок на оси y для прямой линии,

b — наклон прямой линии.

На рисунке ниже это понятие представлено графически:

На рисунке выше показана линия, описанная уравнением ŷ =2+0.5х. Отрезок на оси у — это точка пересечения линией оси у; в нашем случае а = 2. Наклон линии, b, отношение подъема линии к длине линии, имеет значение 0.5. Положительный наклон означает, что линия поднимается слева направо. Если b = 0, линия горизонтальна, а это значит, что между зависимой и независимой переменными нет никакой связи. Иными словами, изменение значения x не влияет на значение y.

Часто путают ŷ и у. На графике показаны 6 упорядоченных пар точек и линия, в соответствии с данным уравнением

На этом рисунке показана точка, соответствующая упорядоченной паре х = 2 и у = 4. Обратите внимание, что ожидаемое значение у в соответствии с линией при х = 2 является ŷ. Мы можем подтвердить это с помощью следу­ющего уравнения:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

Значение у представляет собой фактическую точку, а значение ŷ — это ожидаемое значение у с использованием линейного уравнения при заданном значении х.

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

Использование Excel для определения линейной регрессии

Для того, чтобы воспользоваться инструментом регрессионного анализа встроенного в Excel, необходимо активировать надстройку Пакет анализа. Найти ее можно, перейдя по вкладке Файл –> Параметры (2007+), в появившемся диалоговом окне Параметры Excel переходим во вкладку Надстройки. В поле Управление выбираем Надстройки Excel и щелкаем Перейти. В появившемся окне ставим галочку напротив Пакет анализа, жмем ОК.

Во вкладке Данные в группе Анализ появится новая кнопка Анализ данных.

Чтобы продемонстрировать работу надстройки, воспользуемся данными с предыдущей статьи, где парень и девушка делят столик в ванной. Введите данные нашего примера с ванной в столбцы А и В чистого листа.

Перейдите во вкладку Данные, в группе Анализ щелкните Анализ данных. В появившемся окне Анализ данных выберите Регрессия, как показано на рисунке, и щелкните ОК.

Установите необходимыe параметры регрессии в окне Рег­рессия, как показано на рисунке:

Щелкните ОК. На рисунке ниже показаны полученные результаты:

Эти результаты соответствуют тем, которые мы получили путем самостоя­тельных вычислений в предыдущей статье.

Вам также могут быть интересны следующие статьи

• Как построить график с нормальным распределением в Excel
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• Как расчитать дисперсию в Excel с помощью функции ДИСП.В
• Метод наименьших квадратов в Excel — использование функции ТЕНДЕНЦИЯ
• Как рассчитать коэффициент корреляции в Excel