Как сделать вывод остатков в excel?

Корреляционно-регрессионный анализ в 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 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 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. Жмем «Закрыть».

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

Анализ регрессионных остатков в Microsoft Excel

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

  • 1. Ошибка должна иметь нормальное распределение.
  • 2. Вариация данных вокруг линии регрессии должна быть постоянной.
  • 3. Ошибки должны быть независимыми.

Первое предположение о нормальном распределении ошибок требует, чтобы при каждом значении переменной X ошибки линейной регрессии имели нормальное распределение (рис. 8.21). Как и t- и F-критерий дисперсионного анализа, регрессионный анализ довольно устойчив к нарушениям этого условия. Если распределение ошибок относительно линии регрессии при каждом значении X не слишком сильно отличается от нормального, выводы относительно линии регрессии и коэффициентов регрессии изменяются незначительно.

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

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

Рис. 8.21. Предположение о нормальном распределении ошибок

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

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

При необходимости есть возможность вывести таблицу стандартных и простых остатков, где для каждого значения ряда выводится предсказанное значение, с которым сопоставляется остаток, представляющий разность между прогнозным и реальным значением ряда. На рис. 8.22 представлены результаты вывода остатков. При помощи этой части отчета мы можем видеть отклонения каждой точки от построенной линии регрессии. Наибольшее абсолютное значение остатка в нашем случае -1,819077268, наименьшее — 0,055721022.

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

Стандартизованными называются нормированные остатки, которые не зависят от исходной единицы измерения. Стандартизованные остатки с величиной более 2 или менее (-2) являются потенциальными выбросами.

Предусмотрена следующая формула их вычисления:

В нашем примере значение первого стандартизованного остатка будет таким:

После выполнения Microsoft Excel получим рис. 8.22.

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

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

Рис. 8.22. Определение стандартных остатков

Рис. 8.23. Зависимость остатков от независимой переменной

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

Проверка условий. График остатков позволяет оценить вариации ошибок. На рис. 8.23 нет особых различий между ошибками, соответствующими разным значениям Xt. Следовательно, вариации ошибок при разных значениях Х< приблизительно одинаковы, что позволяет сделать заключение о постоянстве дисперсии ошибки уравнения (с увеличением значений зависимой переменной дисперсия не увеличивается). Это еще одно подтверждение адекватности анализируемой модели.

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

График нормальной вероятности. Последней таблицей выходных результатов средства Регрессия является таблица Вывод вероятности (рис. 8.24). Она появляется, если в диалоговом окне Регрессия установлена опция График нормальной вероятности (см. рис. 8.17). Значения в столбце Персен- тилъ вычисляются следующим образом. Вычисляется шаг h = (1/п) • 100 %, первое значение равно hi2, последнее равно (100 — hi2). Начиная со второго значения каждое последующее равно предыдущему, к которому прибавлен шаг h. В столбце У приведены значения переменной У, упорядоченные по возрастанию. По данным этой таблицы строится так называемый график нормального распределения (рис. 8.25). Он позволяет визуально оценить степень линейности зависимости между переменными X и У.

Последним графиком выходных результатов средства Регрессия является График подбора (рис. 8.26). Он появляется, если в диалоговом окне Регрессия установлена опция График подбора (см. рис. 8.17). Для лучшей интерпретации линейной регрессии воспользуемся графиком исходных данных и построенной линией регрессии, представленными на рис. 8.26. Как видим, линия регрессии хорошо «подогнана» под значения исходных данных.

Рис. 8.24. Таблица выходных результатов средства Регрессия

Рис. 8.25. Линейная зависимость между переменными Хи У

Рис. 8.26. Диаграмма разброса и линия регрессии (тренда)

Этот график можно построить вручную. В этом случае при построении графика регрессии используем Мастер диаграмм ППП Excel в следующем порядке.

  • 1. В окне Тип выберите Точечную диаграмму, позволяющую сравнить пары значений. Щелкните по кнопке Далее.
  • 2. Заполните диапазон данных и укажите, что ряды находятся в столбцах (строках). Щелкните по кнопке Далее.
  • 3. Заполните параметры диаграммы на разных закладках: названия диаграммы и осей, значения осей, линии сетки. Щелкните по кнопке Далее.
  • 4. Укажите место размещения диаграммы и нажмите кнопку Готово.

Для более наглядного представления результатов можно добавить

линию тренда. Для этого:

  • 1) выделите область построения диаграммы. В главном меню выберите Диаграмма /Добавить линию тренда;
  • 2) в поле Линия тренда на вкладке Тип выберите вид линии тренда и задайте соответствующие параметры. В качестве дополнительной информации на диаграмме можно отобразить уравнение регрессии и коэффициент детерминации, установив соответствующие флажки на закладке Параметры. Щелкните по кнопке ОК.

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

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

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

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

Y = a0 + a1x1 + … + anxn

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

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

Читать еще:  Как сделать разделение страниц в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Цена акций Exxon Mobil = $96,2 + 0,28*WTI — 0,01*S&P 500

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

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

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

Построим линейную диаграмму, на которой будут представлены динамика фактической цены и расчетной цены акций. Заметно, что расхождения между двумя величинами редко носили слишком серьезный характер. По состоянию на 06.06.2019 фактическая цена акций составила $74,2, а теоретическая — $76,7. Исходя из этого, критерия бумаги вполне справедливо оценены рынком. Однако это только один, причем упрощенный подход. Ситуацию нужно рассматривать в комплексе. К примеру, медианный таргет аналитиков на 12 месяцев равен $84. Это усредненный показатель результатов моделей фундаментальной оценки, предполагающий заметный потенциал роста.

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

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

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

На выходе получаем корреляционную матрицу. На ней видно, что цена Exxon положительно связана с WTI (коэффициент корреляции = 0,55) и отрицательно зависит от динамики индекса S&P 500 (коэффициент корреляции = -0,48).

Так что 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.

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

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

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

БКС Брокер

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

  • С карты любого банка
  • Прямо на сайте
  • Без комиссии

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

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

Рынок США. Доллар укрепляется, акции взяли паузу

Куда уходят деньги…

Morgan Stanley оценил 3 сценария. Режиссер — коронавирус

Энел Россия. Что не понравилось инвесторам и когда бумаги станут интересными

Хочется жить красиво. И экологично, как Tiffany

Ход торгов. Индекс МосБиржи постепенно подбирается к ключевому сопротивлению

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

Индекс S&P 500. Приближаемся к 3400 п.

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

Читать еще:  Как сделать программу учета доходов и расходов в excel?

Copyright © 2008–2020. ООО «Компания БКС» . г. Москва, Проспект Мира, д. 69, стр. 1
Все права защищены. Любое использование материалов сайта без разрешения запрещено.
Лицензия на осуществление брокерской деятельности № 154-04434-100000 , выдана ФКЦБ РФ 10.01.2001 г.

Данные являются биржевой информацией, обладателем (собственником) которой является ПАО Московская Биржа. Распространение, трансляция или иное предоставление биржевой информации третьим лицам возможно исключительно в порядке и на условиях, предусмотренных порядком использования биржевой информации, предоставляемой ОАО Московская Биржа. ООО «Компания Брокеркредитсервис» , лицензия № 154-04434-100000 от 10.01.2001 на осуществление брокерской деятельности. Выдана ФСФР. Без ограничения срока действия.

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

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

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

РЕГРЕССИЯ И EXCEL

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

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

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel, нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel» внизу окна:

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

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

1) Входной интервал Y ¾ содержит ссылку на ячейки, которые содержат значения результативного признака y. Значения должны быть расположены в столбце;

2) Входной интервал X ¾ содержит ссылку на ячейки, которые содержат значения факторов . Значения должны быть расположены в столбцах;

3) Признак Метки ставится, если первые ячейки содержат пояснительный текст (подписи данных);

4) Уровень надежности ¾ это доверительная вероятность, которая по умолчанию считается равной 95%. Если это значение не устраивает, то нужно включить этот признак и ввести требуемое значение;

5) Признак Константа-ноль включается, если необходимо построить уравнение, в котором свободная переменная ;

6) Параметры вывода определяют, куда должны быть помещены результаты. По умолчанию строит режим Новый рабочий лист;

7) Блок Остатки позволяет включать вывод остатков и построение их графиков.

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

1. Регрессионная статистика:

множественный R определяется формулой (коэффициент корреляции Пирсона);

R-квадрат вычисляется по формуле (коэффициент детерминации);

Нормированный R-квадрат вычисляется по формуле (используется для множественной регрессии);

Стандартная ошибка S вычисляется по формуле ;

Наблюдения ¾ это количество данных n.

2. Дисперсионный анализ, строка Регрессия:

Параметр df равен m (количество наборов факторов x);

Параметр SS определяется формулой ;

Параметр MS определяется формулой ;

Статистика F определяется формулой ;

Значимость F. Если полученное число превышает , то принимается гипотеза (нет линейной взаимосвязи), иначе принимается гипотеза (есть линейная взаимосвязь).

3. Дисперсионный анализ, строка Остаток:

Параметр df равен ;

Параметр SS определяется формулой ;

Параметр MS определяется формулой .

4. Дисперсионный анализ, строка Итого содержит сумму первых двух столбцов.

5. Дисперсионный анализ, строка Y-пересечение содержит значение коэффициента , стандартной ошибки и t-статистики .

P-значение ¾ это значение уровней значимости, соответствующее вычисленным t-статистикам. Определяется функцией СТЬЮДРАСП(t-статистика; ). Если P-значение превышает , то соответствующая переменная статистически незначима и ее можно исключить из модели.

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

6. Дисперсионный анализ, строки содержат значения коэффициентов, стандартных ошибок, t-статистик, P-значений и доверительных интервалов для соответствующих .

7. Блок Вывод остатка содержит значения предсказанного y (в наших обозначениях это ) и остатки .

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