Как построить поле корреляции в excel - Новости с мира ПК
Vvmebel.com

Новости с мира ПК
22 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как построить поле корреляции в excel

Глава 5. Построение регрессионных моделей с использованием табличного процессора EXCEL

5.1. Построение парного уравнения регрессии с помощью «Мастера диаграмм»

С помощью программной надстройки «Мастер диаграмм» табличного процессора EXCEL можно изобразить поле корреляции и построить линейную, степенную, логарифмическую, экспоненциальную и полиноминальную (до шестой степени) уравнения парной регрессии. Мастер диаграмм вызывается с помощью пункта «Диаграмма…» меню «Вставка»либо нажатием соответствующей кнопки на панели инструментов. Предварительно следует выделить исходные данные вместе с именами переменных. Ряды значений переменных можно расположить либо горизонтально, либо вертикально. В первом случае значения независимой переменной X следует разместить выше значений зависимой переменной Y. Во втором случае ряд X располагается в левом столбце (рис. 5.1).

рис. 5.1. Исходные данные на рабочем листе EXCEL

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

рис. 5.2. Панели «Мастера диаграмм»

рис. 5.3. Поле корреляции

Для построения линии регрессии выбирается команда «Добавить линию тренда…» меню «Диаграмма», в результате чего появляется панель «Линия тренда», в которой выбирается форма модели (например, «Линейная»), и устанавливается вывод на диаграмму уравнения регрессии и коэффициента детерминации R 2 (рис. 5.4). В результате на графике появятся линия регрессии, ее уравнение и R 2 (рис. 5.5). Аналогично строятся и нелинейные модели.

рис. 5.4. Построение линии регрессии

рис. 5.5. Линия и уравнение регрессии

5.2. Проведение корреляционного анализа с помощью надстройки «Анализ данных»

В состав табличного процессора EXCEL входит набор средств анализа данных (так называемый «Пакет анализа»), предназначенный для эффективного решения многих статистических задач. Надстройка «Пакет анализа» вызывается выбором пункта «Анализ данных…» меню «Сервис» (рис. 5.6).

рис. 5.6. Вызов надстройки «Анализ данных»

Если пункт «Анализ данных…» отсутствует, то следует активизировать надстройку, для чего выбирается пункт «Надстройки» меню «Сервис» и в подвившейся панели «Надстройки» устанавливается флажок в поле «Пакет анализа» (рис. 5.7). После этого в меню «Сервис» должен появиться пункт «Анализ данных…». Если же пункта «Пакет анализа» нет среди доступных надстроек, то эту надстройку нужно установить с инсталляционного диска «Microsoft Office».

рис. 5.7. Активизация надстройки «Пакет анализа»

Как указывалось в § 3.4, перед построением многофакторной регрессионной модели следует проверить исходные данные на коллинеарность факторов, для чего строится матрица парных коэффициентов корреляции. В EXCEL корреляционный анализ может быть проведен с помощью «Пакета анализа». После запуска настройки выбирается инструмент анализа «Корреляция» (рис. 5.8) и заполняются поля появившейся панели (рис. 5.9).

рис. 5.8. Вызов панели корреляционного анализа

рис. 5.9. Панель корреляционного анализа

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

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

рис. 5.10. Результаты корреляционного анализа

5.3. Построение многофакторной линейной модели регрессии с помощью надстройки «Анализ данных»

Настройка «Пакет анализа» может использоваться и для построения многофакторного уравнения регрессии. После запуска «Пакета анализа» выбирается инструмент анализа «Регрессия» и заполняются поля в появившейся панели (рис. 5.11). Ряды значений переменных должны располагаться на рабочем листе вертикально. Хотя порядок расположения переменных здесь не играет принципиальной роли, однако ряды факторов должны находиться в смежных столбцах (см. рис. 5.10).

В панели регрессионного анализа указываются отдельно интервал значений результата Y и отдельно интервал значений сразу всех факторов. Во входные интервалы исходных данных целесообразно включить и имена переменных, а в поле «Метки» установить флажок. Вывести результаты регрессионного анализа можно как на лист с исходными данными, так и на новый рабочий лист. Одновременно можно задать флажками и вывод предсказанных уравнением регрессии значений результата Y, остатков, их графиков и стандартизированных остатков (рис. 5.12).

рис. 5.11. Панель регрессионного анализа

рис. 5.12. Результаты регрессионного анализа

Глава 5. Построение регрессионных моделей с использованием табличного процессора EXCEL

5.1. Построение парного уравнения регрессии с помощью «Мастера диаграмм»

С помощью программной надстройки «Мастер диаграмм» табличного процессора EXCEL можно изобразить поле корреляции и построить линейную, степенную, логарифмическую, экспоненциальную и полиноминальную (до шестой степени) уравнения парной регрессии. Мастер диаграмм вызывается с помощью пункта «Диаграмма…» меню «Вставка»либо нажатием соответствующей кнопки на панели инструментов. Предварительно следует выделить исходные данные вместе с именами переменных. Ряды значений переменных можно расположить либо горизонтально, либо вертикально. В первом случае значения независимой переменной X следует разместить выше значений зависимой переменной Y. Во втором случае ряд X располагается в левом столбце (рис. 5.1).

Читать еще:  Два если в excel

рис. 5.1. Исходные данные на рабочем листе EXCEL

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

рис. 5.2. Панели «Мастера диаграмм»

рис. 5.3. Поле корреляции

Для построения линии регрессии выбирается команда «Добавить линию тренда…» меню «Диаграмма», в результате чего появляется панель «Линия тренда», в которой выбирается форма модели (например, «Линейная»), и устанавливается вывод на диаграмму уравнения регрессии и коэффициента детерминации R 2 (рис. 5.4). В результате на графике появятся линия регрессии, ее уравнение и R 2 (рис. 5.5). Аналогично строятся и нелинейные модели.

рис. 5.4. Построение линии регрессии

рис. 5.5. Линия и уравнение регрессии

5.2. Проведение корреляционного анализа с помощью надстройки «Анализ данных»

В состав табличного процессора EXCEL входит набор средств анализа данных (так называемый «Пакет анализа»), предназначенный для эффективного решения многих статистических задач. Надстройка «Пакет анализа» вызывается выбором пункта «Анализ данных…» меню «Сервис» (рис. 5.6).

рис. 5.6. Вызов надстройки «Анализ данных»

Если пункт «Анализ данных…» отсутствует, то следует активизировать надстройку, для чего выбирается пункт «Надстройки» меню «Сервис» и в подвившейся панели «Надстройки» устанавливается флажок в поле «Пакет анализа» (рис. 5.7). После этого в меню «Сервис» должен появиться пункт «Анализ данных…». Если же пункта «Пакет анализа» нет среди доступных надстроек, то эту надстройку нужно установить с инсталляционного диска «Microsoft Office».

рис. 5.7. Активизация надстройки «Пакет анализа»

Как указывалось в § 3.4, перед построением многофакторной регрессионной модели следует проверить исходные данные на коллинеарность факторов, для чего строится матрица парных коэффициентов корреляции. В EXCEL корреляционный анализ может быть проведен с помощью «Пакета анализа». После запуска настройки выбирается инструмент анализа «Корреляция» (рис. 5.8) и заполняются поля появившейся панели (рис. 5.9).

рис. 5.8. Вызов панели корреляционного анализа

рис. 5.9. Панель корреляционного анализа

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

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

рис. 5.10. Результаты корреляционного анализа

5.3. Построение многофакторной линейной модели регрессии с помощью надстройки «Анализ данных»

Настройка «Пакет анализа» может использоваться и для построения многофакторного уравнения регрессии. После запуска «Пакета анализа» выбирается инструмент анализа «Регрессия» и заполняются поля в появившейся панели (рис. 5.11). Ряды значений переменных должны располагаться на рабочем листе вертикально. Хотя порядок расположения переменных здесь не играет принципиальной роли, однако ряды факторов должны находиться в смежных столбцах (см. рис. 5.10).

В панели регрессионного анализа указываются отдельно интервал значений результата Y и отдельно интервал значений сразу всех факторов. Во входные интервалы исходных данных целесообразно включить и имена переменных, а в поле «Метки» установить флажок. Вывести результаты регрессионного анализа можно как на лист с исходными данными, так и на новый рабочий лист. Одновременно можно задать флажками и вывод предсказанных уравнением регрессии значений результата Y, остатков, их графиков и стандартизированных остатков (рис. 5.12).

рис. 5.11. Панель регрессионного анализа

рис. 5.12. Результаты регрессионного анализа

Как строить поле корреляции в excel. Построение уравнения множественной регрессии в Excel

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе ТипТочечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

Читать еще:  Как посчитать сумму столбца в excel

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

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

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

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

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

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).

Добрый день, уважаемые читатели блога! Сегодня мы поговорим о нелинейных регрессиях. Решение линейных регрессий можно посмотреть по ССЫЛКЕ .

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

Основными типами нелинейных регрессий являются:

  • полиномиальные (квадратичная, кубическая);
  • гиперболическая;
  • степенная;
  • показательная;
  • логарифмическая.

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

В прогнозировании с помощью нелинейных регрессий главное выяснить коэффициент корреляции, который покажет нам есть ли тесная взаимосвязь меду двумя параметрами или нет. Как правило, если коэффициент корреляции близок к 1, значит связь есть, и прогноз будет довольно точен. Ещё одним важным элементом нелинейных регрессий является средняя относительная ошибка (А ), если она находится в промежутке F f , то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию F РАСП со следующими аргументами: X – значение статистики F , Степени_свободы_1 –1, Степени_свободы_2 – 18. Если расчетный уровень значимости P

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

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL

1. Создайте файл исходных данных в MS Excel (например, таблица 2)

2. Построение корреляционного поля

Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма. В появившемся диалоговом окне выберите тип диаграммы: Точечная; вид: Точечная диаграмма, позволяющая сравнить пары значений (Рис. 22).

Рисунок 22 – Выбор типа диаграммы

Нажимаем кнопку Далее>. В появившемся диалоговом окне (Рис. 23) указываем диапазон значений, в нашем примере = Лист1!A2:B26 и указываем расположение данных: в столбцах.

Нажимаем кнопку Далее>. В следующем диалоговом окне (рис. 24) указываем название диаграммы, наименование осей. Нажимаем кнопку Далее>, и Готово.

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

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

2. В контекстном меню выбираем команду Добавить линию тренда.

3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.

Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности

Аналогично строим корреляционное поле зависимости производительности труда от коэффициента сменности оборудования. (рисунок 28).

от коэффициента сменности оборудования

3. Построение корреляционной матрицы.

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

С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки. Установите флажок Пакет анализа (Рисунок 29)

Читать еще:  Как переносить в excel

В диалоговом окне Анализ данных выбираем Корреляция (Рисунок 30).

После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Результат расчетов представлен в таблице 4.

Как строить поле корреляции в excel. Построение уравнения множественной регрессии в Excel

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе ТипТочечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

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

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

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

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

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

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).

Добрый день, уважаемые читатели блога! Сегодня мы поговорим о нелинейных регрессиях. Решение линейных регрессий можно посмотреть по ССЫЛКЕ .

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

Основными типами нелинейных регрессий являются:

  • полиномиальные (квадратичная, кубическая);
  • гиперболическая;
  • степенная;
  • показательная;
  • логарифмическая.

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

В прогнозировании с помощью нелинейных регрессий главное выяснить коэффициент корреляции, который покажет нам есть ли тесная взаимосвязь меду двумя параметрами или нет. Как правило, если коэффициент корреляции близок к 1, значит связь есть, и прогноз будет довольно точен. Ещё одним важным элементом нелинейных регрессий является средняя относительная ошибка (А ), если она находится в промежутке F f , то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию F РАСП со следующими аргументами: X – значение статистики F , Степени_свободы_1 –1, Степени_свободы_2 – 18. Если расчетный уровень значимости P

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector