Уравнение регрессии как сделать в excel

Уравнение регрессии как сделать в excel

Что объясняет регрессия?

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

Будем надеяться, вы знакомы с понятием функции из математики. Функция – это взаимосвязь двух переменных. При изменении одной переменной что-то происходит с другой. Изменяем X, меняется и Y, соответственно. Функциями описываются различные законы. Зная функцию, мы можем подставлять произвольные значения X и смотреть на то, как при этом изменится Y.

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

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

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

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

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

Выбор модели мы делаем в зависимости от того, какая регрессия будет соответствовать конкретно нашему случаю, какая модель будет максимально близка к нашей корреляции. Рассмотрим это на примере задачи и решим её в MS Excel.

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

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

  • Жмём на кнопку «Файл»;
  • Выбираем пункт «Параметры»;
  • Жмём по предпоследней вкладке «Надстройки» с левой стороны;

Уравнение регрессии как сделать в excel

  • Снизу увидим Надпись «Управление» и кнопку «Перейти». Жмём по ней;
  • Ставим галочку на «Пакет анализа»;
  • Жмём «ок».

Уравнение регрессии как сделать в excel

Пример задачи

Функция пакетного анализа активирована. Решим следующую задачу. У нас есть выборка данных за несколько лет о числе ЧП на территории предприятия и количестве трудоустроенных работников.

Нам необходимо выявить взаимосвязь между этими двумя переменными. Есть объясняющая переменная X – это число рабочих и объясняемая переменная – Y – это число чрезвычайных происшествий.

Распределим исходные данные в два столбца.  

Уравнение регрессии как сделать в excel  

Перейдём во вкладку «данные» и выберем «Анализ данных»

Уравнение регрессии как сделать в excelУравнение регрессии как сделать в excel  
Нажимаем «Ок». Анализ произведён, и в новом листе мы увидим результаты.
 
Наиболее существенные для нас значения отмечены на рисунке ниже.  Уравнение регрессии как сделать в excel  
Множественный R – это коэффициент детерминации. Он имеет сложную формулу расчета и показывает, насколько можно доверять нашему коэффициенту корреляции. Соответственно, чем больше это значение, тем больше доверия, тем удачнее наша модель в целом.
 
Y-пересечение и Пересечение X1 – это коэффициенты нашей регрессии. Как уже было сказано, регрессия – это функция, и у неё есть определённые коэффициенты. Таким образом, наша функция будет иметь вид: Y = 0,64*X-2,84.
 

Что нам это даёт? Это даёт нам возможность составить прогноз. Допустим, мы хотим нанять на предприятие 25 работников и нам нужно примерно представить, каким при этом будет количество чрезвычайных происшествий. Подставляем в нашу функцию данное значение и получаем результат Y = 0,64 * 25 – 2,84. Примерно 13 ЧП у нас будет происходить.

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

Уравнение регрессии как сделать в excel  
Вы так же можете построить поле корреляции, выделив область игреков и иксов, нажав на вкладку «вставку» и выбрав точечную диаграмму.
 Уравнение регрессии как сделать в excel  

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

Уравнение регрессии как сделать в excel  

Заключение

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

Источник: https://Reshatel.org/kontrolnye-raboty/ekonometrika-linejnaya-regressiya-v-ms-excel/

Множественная линейная регрессия в excel пример. Уравнение регрессии как сделать в excel

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

Для построения модели регрессии необходимо выбрать пункт СервисАнализ данныхРегрессия (в Excel 2007 этот режим находится в блоке Данные/Анализ данных/Регрессия).

Затем полученные результаты скопировать в блок для анализа.

Уравнение регрессии как сделать в excel

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

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

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

Иными словами, изменение значения x не влияет на значение y.

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

Уравнение регрессии как сделать в excel

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

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

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

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

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

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

Найти ее можно, перейдя по вкладке Файл –> Параметры
(2007+), в появившемся диалоговом окне Параметры
Excel
переходим во вкладку Надстройки.

В поле Управление
выбираем Надстройки
Excel
и щелкаем Перейти.
В появившемся окне ставим галочку напротив Пакет анализа,
жмем ОК.

Уравнение регрессии как сделать в excel

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

Уравнение регрессии как сделать в excel

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

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

Уравнение регрессии как сделать в excel

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

Уравнение регрессии как сделать в excel

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

Уравнение регрессии как сделать в excel

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

Линия регрессии является графическим отражением взаимосвязи между явлениями. Очень наглядно можно построить линию регрессии в программе Excel.

Для этого необходимо:

1.Открыть программу Excel

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

  1. 1 столбик — № испытуемого
  2. 2 столбик — агрессивность
    в баллах
  3. 3 столбик — неуверенность в себе
    в баллах

3.Затем необходимо выделить оба столбика (без названия столбика), нажать вкладку вставка

,
выбрать точечная

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

.

4.Итак у нас получилась заготовка для линии регрессии — так называемая — диаграмма рассеяния
. Для перехода к линии регрессии нужно щёлкнуть на получившийся рисунок, нажать вкладку конструктор,

найти на панели макеты диаграмм

и выбрать Ма
кет9

, на нем ещё написано f(x)

Читайте также:  Как сделать синусоиду в Excel?

5.Итак, у нас получилась линия регрессии. На графике также указано её уравнение и квадрат коэффициента корреляции

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

, затем сетка

). Основные изменения и настройки производятся во вкладке Макет

Линия регрессии построена в MS Excel. Теперь её можно добавить в текст работы.

Регрессионный анализ в Microsoft Excel – наиболее полное руководств по использованию MS Excel для решения задач регрессионного анализа в области бизнес-аналитики.

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

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

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

Конрад Карлберг. Регрессионный анализ в Microsoft Excel. – М.: Диалектика, 2017. – 400 с.

Скачать заметку в формате или , примеры в формате

Глава 1. Оценка изменчивости данных

В распоряжении статистиков имеется множество показателей вариации (изменчивости). Один из них – сумма квадратов отклонений индивидуальных значений от среднего. В Excel для него используется функция КВАДРОТКЛ().

Но чаще используется дисперсия. Дисперсия — это среднее квадратов отклонений.

Дисперсия нечувствительна к количеству значений в исследуемом наборе данных (в то время как сумма квадратов отклонений растет с числом измерений).

Программа Excel предлагает две функции, возвращающие дисперсию: ДИСП.Г() и ДИСП.В():

  • Используйте функцию ДИСП.Г(), если подлежащие обработке значения образуют генеральную совокупность. Т.е., значения, содержащиеся в диапазоне, являются единственными значениями, которые вас интересуют.
  • Используйте функцию ДИСП.В(), если подлежащие обработке значения образуют выборку из совокупности большего объема. Предполагается, что имеются дополнительные значения, дисперсию которых вы также можете оценить.

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

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

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

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

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

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

Средняя сумма квадратов, определенная для выборки, дает нижнюю оценку дисперсии генеральной совокупности. Вычисленную таким способом дисперсию называют смещенной
оценкой. Оказывается, чтобы исключить смещение и получить несмещенную оценку, достаточно разделить сумму квадратов отклонений не на n
, где n
— размер выборки, а на n – 1
.

Величина n – 1
называется количеством (числом) степеней свободы. Существуют разные способы расчета этой величины, хотя все они включают либо вычитание некоторого числа из размера выборки, либо подсчет количества категорий, в которые попадают наблюдения.

Суть различия между функциями ДИСП.Г() и ДИСП.В() состоит в следующем:

  • В функции ДИСП.Г() сумма квадратов делится на количество наблюдений и, следовательно, представляет смещенную оценку дисперсии, истинное среднее.
  • В функции ДИСП.В() сумма квадратов делится на количество наблюдений минус 1, т.е. на количество степеней свободы, что дает более точную, несмещенную оценку дисперсии генеральной совокупности, из которой была извлечена данная выборка.

Стандартное отклонение (англ. standard deviation
, SD) – есть квадратный корень из дисперсии:

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

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

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

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

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

Исходя из этого, вы могли бы рассчитать их стандартное отклонение, которое и является стандартной ошибкой среднего
. Рис. 1. позволяет сравнить распределение 1250 исходных индивидуальных значений (данные о росте 25 мужчин по каждому из 50 штатов) с распределением средних значений 50 штатов.

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

  • где – стандартная ошибка среднего; s
    – стандартное отклонение исходных наблюдений; n
    – количество наблюдений в выборке.

Рис. 1. Вариация средних значений от штата к штату значительно меньше вариации индивидуальных результатов наблюдений

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

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

Среднее по генеральной совокупности обозначается греческой буквой μ. Однако для представления выборочного среднего традиционно используется символ X̅.

z-оценка
выражает положение наблюдения в распределении в единицах стандартного отклонения. Например, z = 1,5 означает, что наблюдение отстоит от среднего на 1,5 стандартного отклонения в сторону больших значений. Термин z-оценка

Источник: https://erfa.ru/mnozhestvennaya-lineinaya-regressiya-v-excel-primer-uravnenie-regressii-kak-sdelat.html

Регрессия в Excel: уравнение, примеры. Линейная регрессия

Рeгрeссиoнный aнaлиз — этo стaтистичeский мeтoд исслeдoвaния, пoзвoляющий пoкaзaть зaвисимoсть тoгo или инoгo пaрaмeтрa oт oднoй либo нeскoльких нeзaвисимых пeрeмeнных.

В дoкoмпьютeрную эру eгo примeнeниe былo дoстaтoчнo зaтруднитeльнo, oсoбeннo eсли рeчь шлa o

Рeгрeссиoнный aнaлиз — этo стaтистичeский мeтoд исслeдoвaния, пoзвoляющий пoкaзaть зaвисимoсть тoгo или инoгo пaрaмeтрa oт oднoй либo нeскoльких нeзaвисимых пeрeмeнных.

В дoкoмпьютeрную эру eгo примeнeниe былo дoстaтoчнo зaтруднитeльнo, oсoбeннo eсли рeчь шлa o бoльших oбъeмaх дaнных. Сeгoдня, узнaв кaк пoстрoить рeгрeссию в Excel, мoжнo рeшaть слoжныe стaтистичeскиe зaдaчи буквaльнo зa пaру минут.

Нижe прeдстaвлeны кoнкрeтныe примeры из oблaсти экoнoмики.

Клaссичeский рaсчeт:

{source}

{/source}

Виды рeгрeссии

Сaмo этo пoнятиe былo ввeдeнo в мaтeмaтику Фрэнсисoм Гaльтoнoм в 1886 гoду. Рeгрeссия бывaeт:

  • линeйнoй;
  • пaрaбoличeскoй;
  • стeпeннoй;
  • экспoнeнциaльнoй;
  • гипeрбoличeскoй;
  • пoкaзaтeльнoй;
  • лoгaрифмичeскoй.

Примeр 1

Рaссмoтрим зaдaчу oпрeдeлeния зaвисимoсти кoличeствa увoлившихся члeнoв кoллeктивa oт срeднeй зaрплaты нa 6 прoмышлeнных прeдприятиях.

Зaдaчa. Нa шeсти прeдприятиях прoaнaлизирoвaли срeднeмeсячную зaрaбoтную плaту и кoличeствo сoтрудникoв, кoтoрыe увoлились пo сoбствeннoму жeлaнию. В тaбличнoй фoрмe имeeм:

  • A
  • B
  • C
  • 1
  • Х
  • Кoличeствo увoлившихся
  • Зaрплaтa
  • 2
  • y
  • 30000 рублeй
  • 3
  • 1
  • 60
  • 35000 рублeй
  • 4
  • 2
  • 35
  • 40000 рублeй
  • 5
  • 3
  • 20
  • 45000 рублeй
  • 6
  • 4
  • 20
  • 50000 рублeй
  • 7
  • 5
  • 15
  • 55000 рублeй
  • 8
  • 6
  • 15
  • 60000 рублeй
  • Для зaдaчи oпрeдeлeния зaвисимoсти кoличeствa увoлившихся рaбoтникoв oт срeднeй зaрплaты нa 6 прeдприятиях мoдeль рeгрeссии имeeт вид урaвнeния Y = a0 + a1x1 +…+akxk, гдe хi — влияющиe пeрeмeнныe, ai — кoэффициeнты рeгрeссии, a k — числo фaктoрoв.
  • Для дaннoй зaдaчи Y — этo пoкaзaтeль увoлившихся сoтрудникoв, a влияющий фaктoр — зaрплaтa, кoтoрую oбoзнaчaeм X.
Читайте также:  Как сделать утопленный текст в word 2010?

Испoльзoвaниe вoзмoжнoстeй тaбличнoгo прoцeссoрa «Эксeль»

aнaлизу рeгрeссии в Excel дoлжнo прeдшeствoвaть примeнeниe к имeющимся тaбличным дaнным встрoeнных функций. oднaкo для этих цeлeй лучшe вoспoльзoвaться oчeнь пoлeзнoй нaдстрoйкoй «Пaкeт aнaлизa». Для eгo aктивaции нужнo:

  • с вклaдки «Фaйл» пeрeйти в рaздeл «Пaрaмeтры»;
  • в oткрывшeмся oкнe выбрaть стрoку «Нaдстрoйки»;
  • щeлкнуть пo кнoпкe «Пeрeйти», рaспoлoжeннoй внизу, спрaвa oт стрoки «Упрaвлeниe»;
  • пoстaвить гaлoчку рядoм с нaзвaниeм «Пaкeт aнaлизa» и пoдтвeрдить свoи дeйствия, нaжaв «oк».

eсли всe сдeлaнo прaвильнo, в прaвoй чaсти вклaдки «Дaнныe», рaспoлoжeннoм нaд рaбoчим листoм «Эксeль», пoявится нужнaя кнoпкa.

Линeйнaя рeгрeссия в Excel

Тeпeрь, кoгдa пoд рукoй eсть всe нeoбхoдимыe виртуaльныe инструмeнты для oсущeствлeния экoнoмeтричeских рaсчeтoв, мoжeм приступить к рeшeнию нaшeй зaдaчи. Для этoгo:

  • щeлкaeм пo кнoпкe «aнaлиз дaнных»;
  • в oткрывшeмся oкнe нaжимaeм нa кнoпку «Рeгрeссия»;
  • в пoявившуюся вклaдку ввoдим диaпaзoн знaчeний для Y (кoличeствo увoлившихся рaбoтникoв) и для X (их зaрплaты);
  • пoдтвeрждaeм свoи дeйствия нaжaтиeм кнoпки «Ok».

В рeзультaтe прoгрaммa aвтoмaтичeски зaпoлнит нoвый лист тaбличнoгo прoцeссoрa дaнными aнaлизa рeгрeссии. oбрaтитe внимaниe! В Excel eсть вoзмoжнoсть сaмoстoятeльнo зaдaть мeстo, кoтoрoe вы прeдпoчитaeтe для этoй цeли. Нaпримeр, этo мoжeт быть тoт жe лист, гдe нaхoдятся знaчeния Y и X, или дaжe нoвaя книгa, спeциaльнo прeднaзнaчeннaя для хрaнeния пoдoбных дaнных.

aнaлиз рeзультaтoв рeгрeссии для R-квaдрaтa

В Excel дaнныe пoлучeнныe в хoдe oбрaбoтки дaнных рaссмaтривaeмoгo примeрa имeют вид:

Уравнение регрессии как сделать в excel

Прeждe всeгo, слeдуeт oбрaтить внимaниe нa знaчeниe R-квaдрaтa. oн прeдстaвляeт сoбoй кoэффициeнт дeтeрминaции. В дaннoм примeрe R-квaдрaт = 0,755 (75,5%), т. e. рaсчeтныe пaрaмeтры мoдeли oбъясняют зaвисимoсть мeжду рaссмaтривaeмыми пaрaмeтрaми нa 75,5 %.

Чeм вышe знaчeниe кoэффициeнтa дeтeрминaции, тeм выбрaннaя мoдeль считaeтся бoлee примeнимoй для кoнкрeтнoй зaдaчи. Считaeтся, чтo oнa кoррeктнo oписывaeт рeaльную ситуaцию при знaчeнии R-квaдрaтa вышe 0,8.

eсли R-квaдрaтa tкр, тo гипoтeзa o нeзнaчимoсти свoбoднoгo члeнa линeйнoгo урaвнeния oтвeргaeтся.

В рaссмaтривaeмoй зaдaчe для свoбoднoгo члeнa пoсрeдствoм инструмeнтoв «Эксeль» былo пoлучeнo, чтo t=169,20903, a p=2,89e-12, т. e.

имeeм нулeвую вeрoятнoсть тoгo, чтo будeт oтвeргнутa вeрнaя гипoтeзa o нeзнaчимoсти свoбoднoгo члeнa. Для кoэффициeнтa при нeизвeстнoй t=5,79405, a p=0,001158.

Иными слoвaми вeрoятнoсть тoгo, чтo будeт oтвeргнутa вeрнaя гипoтeзa o нeзнaчимoсти кoэффициeнтa при нeизвeстнoй, рaвнa 0,12%.

Тaким oбрaзoм, мoжнo утвeрждaть, чтo пoлучeннoe урaвнeниe линeйнoй рeгрeссии aдeквaтнo.

Зaдaчa o цeлeсooбрaзнoсти пoкупки пaкeтa aкций

Мнoжeствeннaя рeгрeссия в Excel выпoлняeтся с испoльзoвaниeм всe тoгo жe инструмeнтa «aнaлиз дaнных». Рaссмoтрим кoнкрeтную приклaдную зaдaчу.

Рукoвoдствo кoмпaния «NNN» дoлжнo принять рeшeниe o цeлeсooбрaзнoсти пoкупки 20 % пaкeтa aкций ao «MMM». Стoимoсть пaкeтa (СП) сoстaвляeт 70 млн aмeрикaнских дoллaрoв. Спeциaлистaми «NNN» сoбрaны дaнныe oб aнaлoгичных сдeлкaх. Былo принятo рeшeниe oцeнивaть стoимoсть пaкeтa aкций пo тaким пaрaмeтрaм, вырaжeнным в миллиoнaх aмeрикaнских дoллaрoв, кaк:

  • крeдитoрскaя зaдoлжeннoсть (VK);
  • oбъeм гoдoвoгo oбoрoтa (VO);
  • дeбитoрскaя зaдoлжeннoсть (VD);
  • стoимoсть oснoвных фoндoв (СoФ).

Крoмe тoгo, испoльзуeтся пaрaмeтр зaдoлжeннoсть прeдприятия пo зaрплaтe (V3 П) в тысячaх aмeрикaнских дoллaрoв.

Рeшeниe срeдствaми тaбличнoгo прoцeссoрa Excel

Прeждe всeгo, нeoбхoдимo сoстaвить тaблицу исхoдных дaнных. oнa имeeт слeдующий вид:

Уравнение регрессии как сделать в excel

Дaлee:

  • вызывaют oкнo «aнaлиз дaнных»;
  • выбирaют рaздeл «Рeгрeссия»;
  • в oкoшкo «Вхoднoй интeрвaл Y» ввoдят диaпaзoн знaчeний зaвисимых пeрeмeнных из стoлбцa G;
  • щeлкaют пo икoнкe с крaснoй стрeлкoй спрaвa oт oкнa «Вхoднoй интeрвaл X» и выдeляют нa листe диaпaзoн всeх знaчeний из стoлбцoв B,C, D, F.

oтмeчaют пункт «Нoвый рaбoчий лист» и нaжимaют «Ok».

Пoлучaют aнaлиз рeгрeссии для дaннoй зaдaчи.

Уравнение регрессии как сделать в excel

Изучeниe рeзультaтoв и вывoды

  1. «Сoбирaeм» из oкруглeнных дaнных, прeдстaвлeнных вышe нa листe тaбличнoгo прoцeссoрa Excel, урaвнeниe рeгрeссии:
  2. СП = 0,103*СoФ + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

  3. В бoлee привычнoм мaтeмaтичeскoм видe eгo мoжнo зaписaть, кaк:
  4. y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844
  5. Дaнныe для ao «MMM» прeдстaвлeны в тaблицe:
  6. СoФ, USD
  7. VO, USD
  8. VK, USD
  9. VD, USD
  10. VZP, USD
  11. СП, USD
  12. 102,5
  13. 535,5
  14. 45,2
  15. 41,5
  16. 21,55
  17. 64,72

Пoдстaвив их в урaвнeниe рeгрeссии, пoлучaют цифру в 64,72 млн aмeрикaнских дoллaрoв. Этo знaчит, чтo aкции ao «MMM» нe стoит приoбрeтaть, тaк кaк их стoимoсть в 70 млн aмeрикaнских дoллaрoв дoстaтoчнo зaвышeнa.

Кaк видим, испoльзoвaниe тaбличнoгo прoцeссoрa «Эксeль» и урaвнeния рeгрeссии пoзвoлилo принять oбoснoвaннoe рeшeниe oтнoситeльнo цeлeсooбрaзнoсти впoлнe кoнкрeтнoй сдeлки.

Тeпeрь вы знaeтe, чтo тaкoe рeгрeссия. Примeры в Excel, рaссмoтрeнныe вышe, пoмoгут вaм в рeшeниe прaктичeских зaдaч из oблaсти экoнoмeтрики.

Источник: https://xroom.su/regressiia-v-excel-yravnenie-primery-lineinaia-regressiia/

Регрессия в Excel: уравнение, примеры. Линейная регрессия

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

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

Ниже представлены конкретные примеры из области экономики.

Рассмотрим задачу определения зависимости количества уволившихся членов коллектива от средней зарплаты на 6 промышленных предприятиях.

  • B
  • C
  • 1
  • Х
  • Количество уволившихся
  • Зарплата
  • 2
  • y
  • 30000 рублей
  • 3
  • 1
  • 60
  • 35000 рублей
  • 4
  • 2
  • 35
  • 40000 рублей
  • 5
  • 3
  • 20
  • 45000 рублей
  • 6
  • 4
  • 20
  • 50000 рублей
  • 7
  • 5
  • 15
  • 55000 рублей
  • 8
  • 6
  • 15
  • 60000 рублей
  • Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а0 + а1×1 +…+аkxk, где хi — влияющие переменные, ai — коэффициенты регрессии, a k — число факторов.
  • Для данной задачи Y — это показатель уволившихся сотрудников, а влияющий фактор — зарплата, которую обозначаем X.

Использование возможностей табличного процессора «Эксель»

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

  • с вкладки «Файл» перейти в раздел «Параметры»;
  • в открывшемся окне выбрать строку «Надстройки»;
  • щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
  • поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

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

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

  • щелкаем по кнопке «Анализ данных»;
  • в открывшемся окне нажимаем на кнопку «Регрессия»;
  • в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
  • подтверждаем свои действия нажатием кнопки «Ok».

В результате программа автоматически заполнит новый лист табличного процессора данными анализа регрессии. Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

Уравнение регрессии как сделать в excel

Прежде всего, следует обратить внимание на значение R-квадрата. Он представляет собой коэффициент детерминации. В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %.

Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8.

Если R-квадрата tкр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е.

имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158.

Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

  • кредиторская задолженность (VK);
  • объем годового оборота (VO);
  • дебиторская задолженность (VD);
  • стоимость основных фондов (СОФ).
Читайте также:  Как сделать красивую презентацию в powerpoint 2010 с анимацией?

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

Решение средствами табличного процессора Excel

Прежде всего, необходимо составить таблицу исходных данных. Она имеет следующий вид:

Уравнение регрессии как сделать в excel

Далее:

  • вызывают окно «Анализ данных»;
  • выбирают раздел «Регрессия»;
  • в окошко «Входной интервал Y» вводят диапазон значений зависимых переменных из столбца G;
  • щелкают по иконке с красной стрелкой справа от окна «Входной интервал X» и выделяют на листе диапазон всех значений из столбцов B,C, D, F.

Отмечают пункт «Новый рабочий лист» и нажимают «Ok».

Получают анализ регрессии для данной задачи.

Уравнение регрессии как сделать в excel

Изучение результатов и выводы

  • «Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:
  • СП = 0,103*СОФ + 0,541*VO – 0,031*VK +0,40 VD +0,691*VZP – 265,844.
  • В более привычном математическом виде его можно записать, как:
  • y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,40 x4 +0,691*x5 – 265,844
  • Данные для АО «MMM» представлены в таблице:
СОФ, USD VO, USD VK, USD VD, USD VZP, USD СП, USD
102,5 535,5 45,2 41,5 21,55 64,72

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

Как видим, использование табличного процессора «Эксель» и уравнения регрессии позволило принять обоснованное решение относительно целесообразности вполне конкретной сделки.

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

Источник: https://autogear.ru/article/322/644/regressiya-v-excel-uravnenie-primeryi-lineynaya-regressiya/

Построение регрессии в excel. Уравнение регрессии как сделать в excel

Регрессионный анализ в Microsoft Excel – наиболее полное руководств по использованию MS Excel для решения задач регрессионного анализа в области бизнес-аналитики.

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

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

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

Конрад Карлберг. Регрессионный анализ в Microsoft Excel. – М.: Диалектика, 2017. – 400 с.

Скачать заметку в формате или , примеры в формате

Глава 1. Оценка изменчивости данных

В распоряжении статистиков имеется множество показателей вариации (изменчивости). Один из них – сумма квадратов отклонений индивидуальных значений от среднего. В Excel для него используется функция КВАДРОТКЛ().

Но чаще используется дисперсия. Дисперсия — это среднее квадратов отклонений.

Дисперсия нечувствительна к количеству значений в исследуемом наборе данных (в то время как сумма квадратов отклонений растет с числом измерений).

Программа Excel предлагает две функции, возвращающие дисперсию: ДИСП.Г() и ДИСП.В():

  • Используйте функцию ДИСП.Г(), если подлежащие обработке значения образуют генеральную совокупность. Т.е., значения, содержащиеся в диапазоне, являются единственными значениями, которые вас интересуют.
  • Используйте функцию ДИСП.В(), если подлежащие обработке значения образуют выборку из совокупности большего объема. Предполагается, что имеются дополнительные значения, дисперсию которых вы также можете оценить.

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

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

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

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

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

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

Средняя сумма квадратов, определенная для выборки, дает нижнюю оценку дисперсии генеральной совокупности. Вычисленную таким способом дисперсию называют смещенной
оценкой. Оказывается, чтобы исключить смещение и получить несмещенную оценку, достаточно разделить сумму квадратов отклонений не на n
, где n
— размер выборки, а на n – 1
.

Величина n – 1
называется количеством (числом) степеней свободы. Существуют разные способы расчета этой величины, хотя все они включают либо вычитание некоторого числа из размера выборки, либо подсчет количества категорий, в которые попадают наблюдения.

Суть различия между функциями ДИСП.Г() и ДИСП.В() состоит в следующем:

  • В функции ДИСП.Г() сумма квадратов делится на количество наблюдений и, следовательно, представляет смещенную оценку дисперсии, истинное среднее.
  • В функции ДИСП.В() сумма квадратов делится на количество наблюдений минус 1, т.е. на количество степеней свободы, что дает более точную, несмещенную оценку дисперсии генеральной совокупности, из которой была извлечена данная выборка.

Стандартное отклонение (англ. standard deviation
, SD) – есть квадратный корень из дисперсии:

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

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

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

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

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

Исходя из этого, вы могли бы рассчитать их стандартное отклонение, которое и является стандартной ошибкой среднего
. Рис. 1. позволяет сравнить распределение 1250 исходных индивидуальных значений (данные о росте 25 мужчин по каждому из 50 штатов) с распределением средних значений 50 штатов.

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

Уравнение регрессии как сделать в excel

где – стандартная ошибка среднего; s
– стандартное отклонение исходных наблюдений; n
– количество наблюдений в выборке.

Уравнение регрессии как сделать в excel

Рис. 1. Вариация средних значений от штата к штату значительно меньше вариации индивидуальных результатов наблюдений

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

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

Среднее по генеральной совокупности обозначается греческой буквой μ. Однако для представления выборочного среднего традиционно используется символ X̅.

z-оценка
выражает положение наблюдения в распределении в единицах стандартного отклонения. Например, z = 1,5 означает, что наблюдение отстоит от среднего на 1,5 стандартного отклонения в сторону больших значений. Термин z-оценка

Источник: https://nc1.ru/vitamins-and-dietary-supplements/postroenie-regressii-v-excel-uravnenie-regressii-kak-sdelat-v-excel/

Ссылка на основную публикацию
Adblock
detector