Как сделать регрессионный анализ в Excel 2013?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нажимаем «Ок». Анализ произведён, и в новом листе мы увидим результаты.

Наиболее существенные для нас значения отмечены на рисунке ниже.  
Множественный R – это коэффициент детерминации. Он имеет сложную формулу расчета и показывает, насколько можно доверять нашему коэффициенту корреляции. Соответственно, чем больше это значение, тем больше доверия, тем удачнее наша модель в целом.

Y-пересечение и Пересечение X1 – это коэффициенты нашей регрессии. Как уже было сказано, регрессия – это функция, и у неё есть определённые коэффициенты. Таким образом, наша функция будет иметь вид: Y = 0,64*X-2,84.

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

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

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

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

Заключение

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

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

Archie Goodwin

Научимся строить линейную регрессионную модель с несколькими влияющими факторами в Эксель всего в несколько кликов с помощью встроенного Пакета анализа.

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

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

Общий вид модели линейной регрессии:

Y=a0+a1x1+…+akxk

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

Исходные данные

Среди исходных данных нам необходим некий набор данных, который бы представлял из себя несколько последовательных или связанных между собой величин итогового параметра Y (например, ВВП) и такое же количество величин показателей, влияние которых мы изучаем (например, иностранные инвестиции).

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

Пакет анализа

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

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

Активируем Пакет анализа

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

В эксель, слева вверху, активируем вкладку Файл, в открывшемся меню ищем пункт Параметры и кликаем на него.

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

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

Инструкция по поиску параметров линейной регрессии с помощью Пакета анализа

После активации надстройки Пакета анализа она будет всегда доступна во вкладке главного меню Данные под ссылкой Анализ данных

В активном окошке инструмента Анализа данных из списка возможностей ищем и выбираем Регрессия

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

После того как выбрали исходные данные и нажали кнопочку ОК, Excel выдает расчеты на новом листе активной книги (если в настройках не было выставлено иначе), эти расчеты имеют следующий вид:

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

Итак, 0,865 — это R2 — коэффициент детерминации, показывающий что на 86,5% расчетные параметры модели, то есть сама модель, объясняют зависимость и изменения изучаемого параметра — Y от исследуемых факторов — иксов.

Если утрировано, то это показатель качества модели и чем он выше тем лучше. Понятное дело, что он не может быть больше 1 и считается неплохо, когда R2 выше 0,8, а если меньше 0,5, то резонность такой модели можно смело ставить под большой вопрос.

Теперь перейдем к коэффициентам модели:
2079,85 — это a0 — коэффициент который показывает какой будет Y в случае, если все используемые в модели факторы будут равны 0, подразумевается что это зависимость от других неописанных в модели факторов;
-0,0056 — a1 — коэффициент, который показывает весомость влияния фактора x1 на Y, то есть количество предприятий в пределах данной модели влияет на показатель экономически активного населения с весом всего -0,0056 (довольно маленькая степень влияния). Знак минус показывает что это влияние отрицательно, то есть чем больше предприятий, тем меньше экономически активного населения, как бы это ни было парадоксальным по смыслу;
-0,0026 — a2 — коэффициент влияния объема инвестиций в капитал на величину экономически активного населения, согласно модели, это влияние также отрицательно; 0,0028 — a3— коэффициент влияния доходов населения на величину экономически активного населения, здесь влияние позитивное, то есть согласно модели увеличение доходов будет способствовать увеличению величины экономически активного населения.

1. Соберем рассчитанные коэффициенты в модель:
2. Y = 2079,85 — 0,0056×1 — 0,0026×2 + 0,0028×3
3. Собственно, это и есть линейная регрессионная модель, которая для исходных данных, используемых в примере, выглядит именно так.

Расчетные значения модели и прогноз

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

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

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

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

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

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

Источник: http://archie-goodwin.net/load/specializirovannye_blogi/ms_office/linejnaja_regressija_v_excel_cherez_analiz_dannykh/28-1-0-391

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

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

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

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

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

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

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

{source}

{/source}

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

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

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

Примeр 1

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

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

• Для зaдaчи oпрeдeлeния зaвисимoсти кoличeствa увoлившихся рaбoтникoв oт срeднeй зaрплaты нa 6 прeдприятиях мoдeль рeгрeссии имeeт вид урaвнeния Y = a0 + a1x1 +…+akxk, гдe хi — влияющиe пeрeмeнныe, ai — кoэффициeнты рeгрeссии, a k — числo фaктoрoв.
• Для дaннoй зaдaчи Y — этo пoкaзaтeль увoлившихся сoтрудникoв, a влияющий фaктoр — зaрплaтa, кoтoрую oбoзнaчaeм X.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дaлee:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а0 + а1×1 +…+аkxk, где хi — влияющие переменные, ai — коэффициенты регрессии, a k — число факторов.
• Для данной задачи Y — это показатель уволившихся сотрудников, а влияющий фактор — зарплата, которую обозначаем X.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее:

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

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

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

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

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

СОФ, USD	VO, USD	VK, USD	VD, USD	VZP, USD	СП, USD
102,5	535,5	45,2	41,5	21,55	64,72

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

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

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

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

Формула логистической регрессии в экселе. Регрессия в программе Excel

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

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

Ниже также предоставлена краткая, но понятная инструкция построения регрессионной модели.

Основные задачи и виды регрессии

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

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

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

Основные задачи построения регрессионной модели заключаются в следующем:

1. Отбор значимых независимых переменных (Х1, Х2, …, Xk).
2. Выбор вида функции.
3. Построение оценок для коэффициентов.
4. Построение доверительных интервалов и функции регрессии.
5. Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

• парный (1 зависимая и 1 независимая переменные);
• множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

1. Линейные, иллюстрирующие строгую линейную связь между переменными.
2. Нелинейные — уравнения, которые могут включать степени, дроби и тригонометрические функции.

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: https://www.trikolo.ru/personal-account/formula-logisticheskoi-regressii-v-eksele-regressiya-v-programme-excel/

Основы регрессионного анализа для инвесторов. Построение модели в 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.

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

Цена акций 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 — это преимущественно нефтяная история, зачастую не совпадающая по динамике с широким рынком. Это можно заметить на графике трех активов с 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.Как еще использовать корреляционно-регрессионный анализ

1. В архивах раздела Обучение БКС Экспресс есть материалы на эту тему.
2. Корреляционная икселька — «Составление инвестиционного портфеля по Марковицу для чайников»

Оценка коэффициентов альфа и бета. Теория — «Коэффициенты альфа и бета. Выбираем акции в портфель «по науке», практика — «Как коэффициент бета помогает портфельному инвестору».

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

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

Источник: https://bcs-express.ru/novosti-i-analitika/osnovy-regressionnogo-analiza-dlia-investorov-postroenie-modeli-v-excel

Основные задачи регрессии в Excel: пример построения модели

Регрессия в Excel позволяет проводить анализ последующего поведения данных

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

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

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

Основные задачи и виды регрессии

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

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

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

Основные задачи построения регрессионной модели заключаются в следующем:

1. Отбор значимых независимых переменных (Х1, Х2, …, Xk).
2. Выбор вида функции.
3. Построение оценок для коэффициентов.
4. Построение доверительных интервалов и функции регрессии.
5. Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

• парный (1 зависимая и 1 независимая переменные);
• множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

1. Линейные, иллюстрирующие строгую линейную связь между переменными.
2. Нелинейные — уравнения, которые могут включать степени, дроби и тригонометрические функции.

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

1. В меню необходимо выбрать «Сервис» — «Надстройки» — «Пакет анализа». Затем снова заходим в «Сервис» и Окно параметров регрессии Excel выбираем «Анализ данных» — «Регрессия». После установки на панели Excel появится раздел «Данные», где необходимо будет выбрать «Анализ данных» — «Регрессия» (фото 1).
2. Во входной интервал Y следует ввести ссылку на диапазон зависимых переменных (1 столбец), а во входной интервал Х — ввести ссылку диапазона независимых переменных (до 16 чисел). При этом при наличии заголовков следует поставить в окошке «Метки» флажок.
3. В пункте «Константа — ноль» устанавливается флажок, а также в поле «Уровень надежности» задается 95% по умолчанию.
4. Отмечаем, чтобы вывод данных производился на отдельный лист.
5. Нажимаем «ОК», и на отдельном листе или в другом заданном месте появятся коэффициенты при соответствующих переменных.

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

При выполнении регрессионного анализа Microsoft Excel определяет для каждой точки квадрат разности между прогнозируемым значением Y и фактическим значением Y.

Источник: https://itguides.ru/soft/excel/regressiya-v-excel.html