Расчет коэффициента детерминации в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Расчет коэффициента детерминации в Microsoft Excel

Коэффициент детерминации в Excel (Эксель)

Для статистических моделей во многих случаях необходимо определить точность прогноза. Это производится с помощью специальных расчётов в Microsoft Excel, а использоваться будет коэффициент детерминации. Он обозначается как R^2.

Статистические модели можно разделить на качественные уровни в зависимости от коэффициента. От 0.8 до 1 относятся модели хорошего качества, модели достаточного качества имеют уровень от 0.5 до 0.8, а плохое качество имеет диапазон от 0 до 0.5.

Способ определения точности с помощью функции КВПИРСОН

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

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

После этого откроется специальное окно. Категорию нужно выбрать “Статистические” и выбираем КВПИРСОН. Эта функция позволяет определить коэффициент корреляции касательно функции Пирсона, соответственно квадратное значение коэффициента корреляции = коэффициенту детерминации.

После подтверждения действия, появится окно в котором нужно в полях выставить “Известные значения Х” и “Известные значения Y”. Нажимаем мышкой поле “Известные значения Y” и в рабочем окне выделяем данные столбца Y. Аналогичное действие делаем и с другим полем выбирая данные уже с таблицы Х.

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

Определение коэффициента детерминации если функция не является линейной.

Если функция нелинейная, то инструментарий Excel также позволяет рассчитать коэффициент с помощью инструмента “Регрессия”. Его можно найти в пакете анализа данных. Но для начала нужно активировать этот пакет, перейдя в раздел “Файл” и в списке открыть “Параметры”.

После этого можно увидеть новое окно, в котором нужно в меню выбрать “Надстройки”, а в специальном поле по управлению надстройками выбираем “Надстройки Excel” и переходим к ним.

После перехода в надстройки Excel появится новое окно. В нём можно увидеть доступные для пользователя надстройки. Ставим галочку возле “Пакет анализа” и подтверждаем действие.

Найти его можно в разделе “Данные”, после перехода в который нажимаем на “Анализ данных” в правой части экрана.

После его открытия, в списке выбираем “Регрессия”и подтверждаем действие.

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

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

Способ определения коэффициента детерминации для линии тренда

Имея созданную таблицу с соответствующими значение, создаем график. Чтобы провести на нём линию тренда надо нажать на график, а именно на область где строится линия. Сверху в панели инструментов выбрать раздел “Макет”, а в нём выбрать “Линия тренда”. После этого в контексте данного примера в списке выбираем “Экспоненциальное приближение”.

Линия тренда будет отображена на графике как кривая с черным цветом.

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

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

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

Если пользователю нужен другой типаж линии тренда, то в окне “Формат линии тренда” можно выбрать его. Не забыв задать его ранее при создании линии тренда в разделе “Макет” или в контекстном меню. Также не забываем ставить флажок для функции R^2.

Как результат можно увидеть изменение линии тренда и число достоверности.

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

Так было рассмотрено несколько способов по нахождению коэффициента детерминации. Пользователь может выбрать наиболее оптимальный для своих целей.

Корреляционно-регрессионный анализ в Excel: инструкция выполнения

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.

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

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

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

  • линейной (у = а + bx);
  • параболической (y = a + bx + cx 2 );
  • экспоненциальной (y = a * exp(bx));
  • степенной (y = a*x^b);
  • гиперболической (y = b/x + a);
  • логарифмической (y = b * 1n(x) + a);
  • показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

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

Модель линейной регрессии имеет следующий вид:

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

  1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
  2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
  3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

  1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
  2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
  3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Читайте также:  Открытие таблиц ODS в Microsoft Excel

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Корреляционный анализ в Excel

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

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

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

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

Ставим курсор в любую ячейку и нажимаем кнопку fx.

  1. В категории «Статистические» выбираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» – первый диапазон значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» – второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:

Корреляционно-регрессионный анализ

На практике эти две методики часто применяются вместе.

  1. Строим корреляционное поле: «Вставка» – «Диаграмма» – «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
  2. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
  3. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
  4. Жмем «Закрыть».

Теперь стали видны и данные регрессионного анализа.

Расчет коэффициента детерминации в excel. Коэффициент детерминации в Excel (Эксель)

Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (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.

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

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

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

1 столбик — № испытуемого

2 столбик — агрессивность в баллах

3 столбик — неуверенность в себе в баллах

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

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

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

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

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

Построение линейной регрессии, оценивание ее параметров и их значимости можно выполнить значительнее быстрей при использовании пакета анализа Excel (Регрессия). Рассмотрим интерпретацию полученных результатов в общем случае (k объясняющих переменных) по данным примера 3.6.

В таблице регрессионной статистики приводятся значения:

Множественный R – коэффициент множественной корреляции ;

R квадрат – коэффициент детерминации R 2 ;

Нормированный R квадрат – скорректированный R 2 с поправкой на число степеней свободы;

Стандартная ошибка – стандартная ошибка регрессии S ;

Наблюдения – число наблюдений n .

В таблице Дисперсионный анализ приведены:

Читайте также:  Способы абсолютной адресации в Microsoft Excel

1. Столбец df – число степеней свободы, равное

для строки Регрессия df = k ;

2. Столбец SS – сумма квадратов отклонений, равная

для строки Регрессия ;

3. Столбец MS дисперсии, определяемые по формуле MS = SS /df :

для строки Регрессия – факторная дисперсия;

для строкиОстаток – остаточная дисперсия.

4. Столбец F – расчетное значение F -критерия, вычисляемое по формуле

5. Столбец Значимость F –значение уровня значимости, соответствующее вычисленной F -статистике.

Значимость F = FРАСП(F- статистика, df (регрессия), df (остаток)).

Если значимость F Виды регрессионного анализа

Существует несколько видов регрессий:

  • параболическая;
  • степенная;
  • логарифмическая;
  • экспоненциальная;
  • показательная;
  • гиперболическая;
  • линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

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

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк. В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

Разбор результатов анализа

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

Одним из основных показателей является R-квадрат . В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Ещё один важный показатель расположен в ячейке на пересечении строки «Y-пересечение» и столбца «Коэффициенты» . Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.

Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае — это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.

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

3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel

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

Имеется выборка наблюдений доходности актива А и рыночного индекса (для примера ограничимся десятью значениями). Печатаем значения доходности актива в ячейках от А1 до А10, а индекса – от В1 до В10, как показано на рис. 3.15.

Определим вначале коэффициент /3 актива. Решение получим в ячейке А12, поэтому выделяем ее, т.е. наводим на нее курсор и щелкаем мышью. Открываем окно “Мастер функций”, т.е. наводим курсор на значок ?• на панели инструментов и щелкаем мышью. В поле “Категория” выбираем курсором строку “Статистические” и щелкаем мышью. В поле окна “Функция” выбираем курсором строку “НАКЛОН” и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно “НАКЛОН”. В окне две строки: “Изв_знач_у” и “Извзначх”. В первую строку заносим значения доходности актива А. Для этого наводим курсор на знак 51 с правой стороны первой строки и щелкаем мышью. Окно “НАКЛОН” свернулось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак 53 и щелкаем мышью. Появилось развернутое окно “НАКЛОН”. Заносим доходности рыночного индекса во вторую строку. Для этого наводим курсор на знак 3 во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу. Наводим курсор на кнопку 3 и щелкаем мышью. Появилось развернутое окно “НАКЛОН”. Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке А12 появилась цифра 1,029884.

(Получить значение коэффициента /? можно другим способом: выбираем курсором ячейку А12 и печатаем в ней формулу:

и нажимаем клавишу Enter.)

Рассчитаем теперь коэффициент у. Решение получим в ячейке В12, поэтому наводим на нее курсор и щелкаем мышью. Открываем окно “Мастер функций” , т.е. наводим курсор на значок * на панели инструментов и щелкаем мышью. В поле “Категория” выбираем курсором строку “Статистические” и щелкаем мышью. В поле окна “Функция” выбираем курсором строку “ОТРЕЗОК” и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно “ОТРЕЗОК”. В окне две строки: “Изв_знач_у” и “Изв_знач_х”. В первую строку заносим значения доходности актива А. Для этого наводим курсор на знак Э с правой стороны первой строки и щелкаем мышью. Окно “ОТРЕЗОК” свернулось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак !Щ и щелкаем мышью. Появилось развернутое окно “ОТРЕЗОК”. Заносим доходности рыночного индекса во вторую строку. Для этого наводим курсор на знак “Щ во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу. Наводим курсор на кнопку Э и щелкаем мышью. Появилось развернутое окно “ОТРЕЗОК”. Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке В12 появилась цифра 2,777476.

(Получить значение коэффициента (5 можно другим способом: выбираем курсором ячейку В12 и печатаем в ней формулу:

и нажимаем клавишу Enter.)

Рассчитать коэффициенты у и ft для данных примера 1 можно с помощью функции “ЛИНЕИН” “Мастера функций”. Решение получим в блоке ячеек А12 и В12. Поэтому выделяем их, т.е. наводим курсор на ячейку А12, нажимаем левую клавишу мыши и, удерживая ее, проводим курсор до ячейки В12, отпускаем клавишу. Открываем окно “Мастер функций”, т.е. наводим курсор на значок rj& на панели инструментов и щелкаем мышью. В поле “Категория” выбираем курсором строку “Статистические” и щелкаем мышью. В поле окна “Функция” выбираем курсором строку “ЛИНЕИН” и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно “ЛИНЕИН”. Оно представлено на рис. 3.16.

В строку “Изв_знач_у” заносим значения доходности актива А. Для этого наводим курсор на знак 3 в первой строке и щелкаем мышью. Окно “ЛИНЕИН” свернулось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак 3t и щелкаем мышью. Появилось развернутое окно “ЛИНЕИН”. Заносим доходности рыночного индекса в строку “Извзначх”. Для этого наводим курсор на знак Щ во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу. Наводим курсор на кнопку щ и щелкаем мышью. Появилось развернутое окно “ЛИНЕИН”. Одновременно нажимаем на клавиши Ctrl, Shift и Enter (удобно вначале нажать Ctrl и Shift и, удерживая их в нажатом положении, нажать Enter). В блоке ячеек А12 и В12 появились соответственно цифры 1,029884 и 2,777476 .

Читайте также:  Создание формул в программе Microsoft Excel

С помощью функции “ЛИНЕЙН” можно получить значение коэффициента детерминации. Для этого выделим блок из ячеек А12:В14, т.е. наводим курсор на ячейку А12, нажимаем левую клавишу мыши и, удерживая ее, доводим курсор до ячейки В14, отпускаем клавишу. После этого вводим значения доходно-стей актива А и рыночного индекса в строки “Изв_знач_у” и “Извзначх” окна “ЛИНЕЙН” как было сказано выше. Далее в открытом окне “ЛИНЕЙН” в строке “Стат” печатаем цифру 1 (Вместо цифры 1 также можно напечатать слово ИСТИНА) и одновременно нажимаем клавиши Ctrl, Shift и Enter. На экране получаем результат как показано на рис. 3.17.

В ячейках А12 и В12 представлены значения коэффициентов J3 и у. Значение коэффициента детерминации расположено в ячейке А14, это 0,627868.

Знаете ли Вы, что: Вы можете выиграть от $20 до $250 в конкурсе «Formula FX» от Альпари, заняв призовые места с 1-го по 20-е. Для участия необходим реальный счет, пополненный не менее чем на $20. Победитель может снять призовую сумму в любой момент времени без каких-либо ограничений.

Рассчитать коэффициенты у и Р и коэффициент детерминации для данных примера 1 можно с помощью пакета “Анализ данных”8. Для этого выбираем курсором меню “Сервис” и щелкаем мышью. Появилось выпадающее меню. Выбираем курсором строку “Анализ данных” и щелкаем мышью. Появилось окно” Анализ данных”. Выбираем курсором строку “Регрессия” и щелкаем мышью. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно “Регрессия” (см. рис. 3.18).

Наводим курсор на знак 3 справа от поля строки “Входной интервал Y” и щелкаем мышью. Окно “Регрессия” свернулось в поле строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак 31 и щелкаем мышью. Появилось развернутое окно “Регрессия”. Наводим курсор на знак 3i справа от поля строки “Входной интервал X” и щелкаем мышью. Окно “Регрессия” свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10 и отпускаем клавишу. Вновь наводим курсор на знак 3 и щелкаем мышью. Появилось развернутое окно “Регрессия”. Если в круглом окне слева от надписи “Выходной интервал” не стоит точка, то надо навести курсор на данную строку и щелкнуть мышью: в окне появится точка. После этого наводим курсор на знак 3 в правой части этой строки и щелкаем мышью. Окно “Регрессия” свернулось в поле строки.

Пример нахождения коэффициента детерминации

Коэффициент детерминации рассчитывается для оценки качества подбора уравнения регрессии. Для приемлемых моделей предполагается, что коэффициент детерминации должен быть хотя бы не меньше 50%. Модели с коэффициентом детерминации выше 80% можно признать достаточно хорошими. Значение коэффициента детерминации R 2 = 1 означает функциональную зависимость между переменными.

Для линейной зависимости коэффициент детерминации равен квадрату коэффициента корреляции rxy: R 2 = rxy 2 .
2 “>Рассчитать свое значение
Например, значение R 2 = 0.83, означает, что в 83% случаев изменения х приводят к изменению y . Другими словами, точность подбора уравнения регрессии – высокая.

В общем случае, коэффициент детерминации находится по формуле: или
В этой формуле указаны дисперсии:
,
где ∑(y- y ) – общая сумма квадратов отклонений;
– сумма квадратов отклонений, обусловленная регрессией («объясненная» или «факторная»);
– остаточная сумма квадратов отклонений.

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

Пример . Дано:

  • доля денежных доходов, направленных на прирост сбережений во вкладах, займах, сертификатах и в покупку валюты, в общей сумме среднедушевого денежного дохода, % (Y)
  • среднемесячная начисленная заработная плата, тыс. руб. (X)

Следует выполнить: 1. построить поле корреляции и сформировать гипотезу о возможной форме и направлении связи; 2. рассчитать параметры уравнений линейной и A1; 3. выполнить расчет прогнозного значения результата, предполагая, что прогнозные значения факторов составят B2 % от их среднего уровня; 4. оценить тесноту связи с помощью показателей корреляции и детерминации, проанализировать их значения; 5. Дать с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом; 6. Оценить с помощью средней ошибки аппроксимации качество уравнений; 7. Оценить надежность уравнений в целом через F-критерий Фишера для уровня значимости а = 0,05. По значениям характеристик, рассчитанных в пп. 5,6 и данном пункте, выберете лучшее уравнение регрессии и дайте его обоснование.

  • Решение онлайн
  • Видео решение

Уравнение имеет вид y = ax + b
1. Параметры уравнения регрессии.
Средние значения

Связь между признаком Y фактором X сильная и прямая.
Уравнение регрессии

Коэффициент детерминации для линейной регрессии равен квадрату коэффициента корреляции.
R 2 = 0.91 2 = 0.83, т.е. в 83% случаев изменения х приводят к изменению y. Другими словами – точность подбора уравнения регрессии – высокая

xyx 2y 2x ∙ yy(x)(y-y cp ) 2(y-y(x)) 2(x-x p ) 2
15.1255228.01650253850.5505.26527451.1762630.22420.25
17261289681214437549.38518772.0783161.41345.96
12293144858493516433.28473699.5319678.51556.96
10310100961003100386.84450587.755904.58655.36
741425547620306251054501872.88196906.672006001474.56
831985688939402251647552081.861007497.339381.62246.76
852549722564974012166652128.32457813.93176990.62440.36
812012656140481441629722035.421062428.38548.492061.16
221562484243984434364665.47337260.88803758.38184.96
103861001489963860386.84354332.480.71655.36
4383161466891532247.52357913.0318353.53998.56
14.1354.1198.81125386.814992.81482.04393327.5816368.87462.25
427.211775.127710.8219692405.81709494.3111775.18137990.811397376.912502.5
2. Оценка параметров уравнения регрессии
Значимость коэффициента корреляции

По таблице Стьюдента находим Tтабл
Tтабл (n-m-1;a) = (10;0.05) = 1.812
Поскольку Tнабл > Tтабл , то отклоняем гипотезу о равенстве 0 коэффициента корреляции. Другими словами, коэффициента корреляции статистически – значим

Анализ точности определения оценок коэффициентов регрессии

S a = 3.3432
Доверительные интервалы для зависимой переменной

Рассчитаем границы интервала, в котором будет сосредоточено 95% возможных значений Y при неограниченно большом числе наблюдений и X = 1
(-557.64;913.38)
Проверка гипотез относительно коэффициентов линейного уравнения регрессии
1) t-статистика

Статистическая значимость коэффициента регрессии a подтверждается

Статистическая значимость коэффициента регрессии b не подтверждается
Доверительный интервал для коэффициентов уравнения регрессии
Определим доверительные интервалы коэффициентов регрессии, которые с надежность 95% будут следующими:
(a – t a S a; a + t aS a)
(17.1616;29.2772)
(b – t b S b; b + t bS b)
(-136.4585;445.7528)

Fkp = 4.96
Поскольку F > Fkp, то коэффициент детерминации статистически значим

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