поле корреляции в excel

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

Корреляцию в Excel можно найти по формуле:

Результат показан ниже

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

затем переходим на область графика

и выбираем данные из диапазона B3:C11, затем Ок. В итоги получаем график поля корреляции по точкам

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

Выбираем корреляцию и жмём Ок.

Такой же выбираем диапазон данных, как и ранее делали

В результате получаем отчёт

Аналитически, корреляция определяется по формуле:

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка 4.6 / 5. Количество оценок: 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. Результаты регрессионного анализа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Основы регрессионного анализа для инвесторов. Построение модели в Excel

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

Что такое регрессия

Регрессионный анализ является статистическим методом исследования. Он позволяет оценить зависимость одной (зависимой) переменной от других (независимых) переменных. Самой простой является линейная регрессия. Ее формула такова:

Y = a0 + a1x1 + … + anxn

где Y — зависимая переменная,
x — независимые переменные, влияющие на нее,
a — коэффициенты регрессии.

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

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

Если фактическая цена бумаги заметно отклоняется от расчетной, появляется повод для дополнительного анализа. Стоит также смотреть на техническую картину, мультипликаторы, общерыночную ситуацию. Существуют также методы финансового моделирования, носящие фундаментальный подход, в частности, модели дисконтирования денежных потоков (DCF) и модели дисконтирования дивидендов (DDM).

Пример расчетов в Excel и выводы

В качестве примера возьмем акции американского нефтегазового гиганта Exxon Mobil (XOM). Модель будет упрощенной и учебной и не является рекомендацией для осуществления операций с бумагами, ситуацию нужно смотреть в комплексе.

Независимыми переменными у нас выступят фьючерсы на американскую нефть WTI (склеенные фронтальные контракты) и индекс S&P 500. Логика проста — бизнес компании зависит от цен на нефть, а поведение акций в теории должно быть связано в общерыночной ситуацией.

Шаг 1. Выкачиваем в Excel котировки XOM, SPX и CL1. Данные возьмем за пять лет. Так как на более длительных периодах наблюдалась разная структурная ситуация на нефтяном рынке. Возьмем статистику в недельной разбивке, будет 262 наблюдения.

Шаг 2. Активируем настройку регрессионного анализа. Открываем раздел Файл. Переходим на вкладку Параметры Excel — Надстройки. Внизу появившегося окна будет вкладка Управление, где стоит параметр Надстройки Excel, жмем — Перейти.

Выбираем опцию Пакет анализа.

Готово. Результат появится в разделе Данные — Анализ данных.

Шаг 3. Строим регрессию. При клике на Анализ данных появится меню с опциями функционала для анализа. Выбираем Регрессия.

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

На выходе получаем вот такие данные.

Шаг 4. Интерпретация. Статистических показателей много. Не вдаваясь в теорию, наиболее интересными являются значения коэффициентов регрессии и показатель R2.

Наша модель будет иметь следующий вид:

R — квадрат равен 0,61. Показатель показывает, насколько значение зависимой переменной определяется значениями независимых переменных. Речь идет о статистической значимости модели. Модель является очень хорошей, если R2 превышает 0,8, и при этом сама модель имеет экономическое обоснование. В нашем случае все не настолько идеально, но все же выше 0,5, поэтому модель можно использовать.

Отмечу, что в процессе подготовки материала делались расчеты не только за пять лет, но и за 10, и за три года, также WTI заменялась на Brent. Итоговый вариант был выбран в связи с наибольшим значением R2.

Шаг 5. Применение. Рассчитаем в Excel теоретические значения акций Exxon за весь использовавшийся для построения модели период (5 лет).

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

Дополним нашу регрессию корреляционным анализом. Корреляция означает зависимость одного показателя от другого. Коэффициент корреляции — показатель взаимосвязи (в нашем случае финансовых активов).

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

Так что Exxon — это преимущественно нефтяная история, зачастую не совпадающая по динамике с широким рынком. Это можно заметить на графике трех активов с 2010 г. Ситуация стала такой с 2014 г., когда рынок нефти обвалился из-за структурных сдвигов. На нашей выборке за 5 лет корреляция между WTI и S&P 500 равна 0,13, то есть несущественна.

Построение графика простой регрессии

Расскажем об еще одном регрессионном функционале Excel. Программа позволяет построить график линейной регрессии. Правда доступно это лишь при наличии одной независимой переменной. В нашем случае ею будет нефть, так как она в большей мере объясняет движения акций Exxon — коэффициент регрессии равен 0,28 против (-0,01) у S&P 500.

Строим точечную диаграмму по XOM и WTI за 5 лет. Получаем поле корреляции. Щелкаем по любой из точек на диаграмме и меню левой кнопки мыши выбираем Добавить линию тренда.

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

В итоге получим такую схему зависимости Exxon (y) от WTI (x). В нашем случае модель не является статистически значимой — R-квадрат равен лишь 0,3.

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

В архивах раздела Обучение БКС Экспресс есть материалы на эту тему.

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

БКС Брокер

пополни брокерский счёт без комиссии

Последние новости

Рекомендованные новости

Итоги торгов. Сильный рост нефти не смог перевесить опасения по поводу открытия в понедельник

ЧТПЗ. Перспективная дивидендная фишка в третьем эшелоне

Разбор падения рынков по Эллиотту

Россия согласилась на новую сделку по снижению добычи нефти

Набиуллина. О влиянии карантина на экономику РФ и новых мерах поддержки

Рубль ускорил рост на фоне ралли нефтяных цен

Ежедневный обзор рынка акций США

Декларация о сотрудничестве ОПЕК+ ожидается 6 апреля

Адрес для вопросов и предложений по сайту: website4@bcs.ru

* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.

Информация не может рассматриваться как публичная оферта, предложение или приглашение приобрести, или продать какие-либо ценные бумаги, иные финансовые инструменты, совершить с ними сделки. Информация не может рассматриваться в качестве гарантий или обещаний в будущем доходности вложений, уровня риска, размера издержек, безубыточности инвестиций. Результат инвестирования в прошлом не определяет дохода в будущем. Не является рекламой ценных бумаг. Перед принятием инвестиционного решения Инвестору необходимо самостоятельно оценить экономические риски и выгоды, налоговые, юридические, бухгалтерские последствия заключения сделки, свою готовность и возможность принять такие риски. Клиент также несет расходы на оплату брокерских и депозитарных услуг, подачи поручений по телефону, иные расходы, подлежащие оплате клиентом. Полный список тарифов ООО «Компания БКС» приведен в приложении № 11 к Регламенту оказания услуг на рынке ценных бумаг ООО «Компания БКС». Перед совершением сделок вам также необходимо ознакомиться с: уведомлением о рисках, связанных с осуществлением операций на рынке ценных бумаг; информацией о рисках клиента, связанных с совершением сделок с неполным покрытием, возникновением непокрытых позиций, временно непокрытых позиций; заявлением, раскрывающим риски, связанные с проведением операций на рынке фьючерсных контрактов, форвардных контрактов и опционов; декларацией о рисках, связанных с приобретением иностранных ценных бумаг.

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

Знания в формате 4 и 5

Меню навигации

Пользовательские ссылки

Информация о пользователе

Вы здесь » Знания в формате 4 и 5 » 11 класс (Семакин И.Г.) » П/р № 3.18. Расчет корреляционных зависимостей в Microsoft Excel

П/р № 3.18. Расчет корреляционных зависимостей в Microsoft Excel

Сообщений 1 страница 1 из 1

Поделиться12013-02-07 09:07:31

Цель работы: получение представления о корреляционной зависимости величин; освоение способа вычисления коэффициента корреляции с помощью функции KOPPEЛ.
Используемое программное обеспечение: табличный процессор Microsoft Excel.

Задание 1

Требуется выполнить расчеты корреляционной зависимости успеваемости учащихся от хозяйственных расходов школы, описанные в § 38 учебника.
1. Заполнить электронную таблицу следующими данными:

2. Построить точечную диаграмму зависимости величин.

3. Выполнить статистическую функцию KOPPEЛ, указав в диалоговом окне диапазоны значений: В2:В21 и С2:С21.
4. Выписать значение коэффициента корреляции.

Задание 2

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

Задание для самостоятельного выполнения по теме «Корреляционные зависимости»

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

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

Источник