Как построить гиперболическую регрессию в excel

Гиперболическая регрессия и оценка ее параметров с помощью линеаризации. Методика оценки параметров уравнения гиперболической регрессии в MS Excel

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

Общий вид уравнения парной гиперболической регрессии:

где у — фактическое значение результативного признака; а, b — параметры модели; х — факторный признак; s— ошибка модели.

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

Если сделать замену z = —, то в результате получим линейное уравнение

регрессии у = а + bz_> параметры которого а и b можно определить с помощью встроенной функции MS Excel = ЛИНЕЙН (массиву; массив z).

Понятие среднего и точечного коэффициента эластичности. Коэффициенты эластичности для параболической и гиперболической регрессий

Точечный коэффициент эластичности показывает, на сколько процентов изменится результативный признак у при изменении факторного признака на 1 % от данного значения факторного признака. Формула расчета точечного коэффициента эластичности имеет вид:

где /(*) — первая производная результативной переменной по факторному признаку.

Коэффициент эластичности обычно рассчитывается для среднего значения факторного признака х по формуле:

Средний коэффициент эластичности Э(х) характеризует процентное изменение результативного признака у относительно своего среднего значения при изменении факторного признака на 1 % относительно его среднего х .

Напомним, что для линейной зависимости у, =а + Ьх средний коэффициент эластичности определяется следующим образом:

Для параболы y = a+bx+cx 2 средний коэффициент эластичности определяется по формуле:

Для гиперболы у = а+Ь— средний коэффициент эластичности равен:

Оценка параметров моделей степенной и показательной регрессий путем их линеаризации с помощью логарифмирования. Реализация в MS Excel

НИЯ. РЕАЛИЗАЦИЯ В MS EXCEL

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

Для определения параметров степенной функции у = ах ь е (случайная ошибка мультипликативно связана с факторным признаком) с помощью метода наименьших квадратов необходимо привести ее к линейному виду путем логарифмирования обеих частей уравнения: In у = 1па+61пл: + 1п?

Пусть У = 1п;у; Д = 1па; X=hx‘, E = hs, тогда уравнение степенной функции примет вид линейного уравнения Y = А+ЬХ +Е или Y = A+bX, параметры которого А и b можно определить с помощью функции = ЛИНЕЙН, задавая в качестве объясняющей переменной X = In *, а в качестве объясняемой переменной — Y = In у, рассчитанные заранее по исходным данным хи у.

После применения команды =ЛИНЕЙН(массив Y; массив X) получаем в первой из двух выделенных ячеек параметр Ь, во второй ячейке будет содержаться значение А. Параметр а находим из выражения А = Ъа согласно определению логарифма а = е А . В MS Excel параметр а находится с помощью команды = ЕХР(А), где А — показатель степени, в которую возводится основание натурального логарифма е = 2,71828.

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

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

где Р — объем производства, результативный признак; L — затраты труда, факторный признак; С — затраты капитала, факторный признак; А, а, /? — параметры модели; s— мультипликативная ошибка модели.

Американские ученые математик и экономист Чарльз Кобб (1875-1949) и экономист Пол Дуглас (1892-1976) в 1928 г. издали работу «Теория производства», в которой смоделировали рост американской экономики с 1899 по 1922 гг. Они предложили следующую степенную зависимость:

где Р — расчетное (теоретическое) значение индекса физического объема производства; L — базисный индекс затрат на рабочую силу; С — базисный индекс основного капитала.

Показательная функция y = ab x s, в которой ошибка е мультипликативно связана с факторным признаком, также может быть подвергнута линеаризации. Для этого необходимо прологарифмировать обе части уравнения, получим: 1пу = 1па + л:1п6 + ]п?.

Произведем следующие замены У = In j; A = ha B = hb E = he, тогда уравнение примет линейный вид: Y = А+Вх + Е, а параметры уравнения А и В могут быть определены с помощью функции MS Excel = ЛИНЕЙН, аргументами которой являются рассчитанные значения Y = In у и исходные значения х.

Параметры уравнения показательной функции а и b определяются следующим образом: а = е А и Ь = е в . В MS 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. Жмем «Закрыть».

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как провести регрессионный анализ в Excel

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

Что такое регрессионный анализ?

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

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

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

Как подключить пакет анализа?

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

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

Кнопка появится во вкладке «Данные», справа – «Анализ данных». Перезагрузка программы не требуется.

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

Чтобы подробно объяснить схему работы, в Excel была создана таблица с указанием определенных данных. Цель – попытка обнаружить связь между температурой и числом посетителей торговой точки. Запустить процесс подсчета и регрессии необходимо с помощью кнопки «Анализ данных».

Поиск в Эксель с использование функции «ПОИСКПОЗ» и «ИНДЕКС»

Откроется диалоговое окошко, из представленного списка выбирают пункт «Регрессия», клавиша «Ок». В полях «Входной интервал Y» и «Входной интервал X» – для первого указывают список ячеек переменного параметра (в примере – покупатели), для второго диапазон по температуре.

Внимание! Пункт «Параметры вывода» осуществляет сохранение результата разными способами – на новом листе, книге и т.д. Удобнее будет переставить значок и получить ответы на той же странице, что и начальная таблица.

Запуска процесса – кнопка «Ок». После необходимо правильно прочитать результат.

Расшифровка результата – анализ данных

Ответы по анализу помещаются в небольшую таблицу «Вывод итогов». Качество показывает R-квадрат – в данном примере 0,70, что является приемлемым. Y-пересечение указывает на уровень переменной, при остальных данных равных «0». Остальные характеристики указывают на взаимосвязь исходников.

Другие виды регрессии

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

Множественная

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

Степенная

Для этой модели формула расчета выглядит так: y = a*x˄b. Выбросы для данного метода вычисляются автоматически. Используется, если уровень достоверности техники выше остальных – графа R˄2.

Нелинейная

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

Дополнительным элементом является относительная ошибка. Характеристика должна находиться в пределах от 8% до 10% – значит, что расчеты точные и результаты можно использовать дальше.

Как и где используют функцию «Подбор параметра» в Excel

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

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

Задать свои вопросы или оставить замечания можно внизу страницы в разделе Disqus .
Можно также оставить заявку на помощь в решении своих задач у наших проверенных партнеров (здесь или здесь).

Поиск

Задать свои вопросы или оставить замечания можно внизу страницы в разделе Disqus .
Можно также оставить заявку на помощь в решении своих задач у наших проверенных партнеров (здесь или здесь).