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

7.1. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессионный анализ позволяет установить функциональную зависимость между некоторой случайной величиной Y и некоторыми влияющими на Y величинами X . Такая зависимость получила название уравнения регрессии. Различают простую (y=m*x+b ) и множественную (y=m 1 *x 1 +m 2 *x 2 +... + m k *x k +b ) регрессию линейного и нелинейного типа.
Для оценки степени связи между величинами используется коэффициент множественной корреляции R Пирсона (корреляционное отношение), который может принимать значения от 0 до 1. R =0, если между величинами нет никакой связи, и R =1, если между величинами имеется функциональная связь. В большинстве случаев R принимает промежуточные значения от 0 до 1. Величина R 2 называется коэффициентом детерминации .
Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели множественной линейной регрессии, при котором коэффициент R принимает максимальное значение.
Для оценки значимости R применяется F-критерий Фишера , вычисляемый по формуле:

Где n – количество экспериментов; k – число коэффициентов модели. Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной.

7.2. Инструмент Регрессия из Пакета анализа позволяет вычислить следующие данные:

· коэффициенты линейной функции регрессии – методом наименьших квадратов; вид функции регрессии определяется структурой исходных данных;

· коэффициент детерминации и связанные с ним величины (таблица Регрессионная статистика );

· дисперсионную таблицу и критериальную статистику для проверки значимости регрессии (таблица Дисперсионный анализ );

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

· значения функции регрессии и остатки – разности между исходными значениями переменной Y и вычисленными значениями функции регрессии (таблица Вывод остатка );

· вероятности, соответствующие упорядоченным по возрастанию значениям переменной Y (таблица Вывод вероятности ).

7.3. Вызовите инструмент создания выборки через Данные> Анализ данных> Регрессия .

7.4. В поле Входной интервал Y вводится адрес диапазона, содержащего значения зависимой переменной Y. Диапазон должен состоять из одного столбца.
В поле Входной интервал X вводится адрес диапазона, содержащего значения переменной X. Диапазон должен состоять из одного или нескольких столбцов, но не более чем из 16 столбцов. Если указанные в полях Входной интервал Y и Входной интервал X диапазоны включают заголовки столбцов, то необходимо установить флажок опции Метки – эти заголовки будут использованы в выходных таблицах, сгенерированных инструментом Регрессия .
Флажок опции Константа - ноль следует установить, если в уравнении регрессии константа b принудительно полагается равной нулю.
Опция Уровень надежности устанавливается тогда, когда необходимо построить доверительные интервалы для коэффициентов регрессии с доверительным уровнем, отличным от 0.95, который используется по умолчанию. После установки флажка опции Уровень надежности становится доступным поле ввода, в котором вводится новое значение доверительного уровня.
В области Остатки имеются четыре опции: Остатки , Стандартизованные остатки , График остатков и График подбора . Если установлена хотя бы одна из них, то в выходных результатах появится таблица Вывод остатка , в которой будут выведены значения функции регрессии и остатки – разности между исходными значениями переменной Y и вычисленными значениями функции регрессии. В области Нормальная вероятность имеется одна опция – ; ее установка порождает в выходных результатах таблицу Вывод вероятности и приводит к построению соответствующего графика.

7.5. Установите параметры в соответствии с рисунком. Проверьте, что в качестве величины Y указана первая переменная (включая ячейку с названием), и в качестве величины X указаны две остальные переменные (включая ячейки с названиями). Нажмите OK .

7.6. В таблице Регрессионная статистика приводятся следующие данные.

Множественный R – корень из коэффициента детерминации R 2 , приведенного в следующей строке. Другое название этого показателя – индекс корреляции, или множественный коэффициент корреляции.

R-квадрат – коэффициент детерминации R 2 ; вычисляется как отношение регрессионной суммы квадратов (ячейка С12) к полной сумме квадратов (ячейка С14).

Нормированный R-квадрат вычисляется по формуле

где n – количество значений переменной Y, k – количество столбцов во входном интервале переменной X.

Стандартная ошибка – корень из остаточной дисперсии (ячейка D13).

Наблюдения – количество значений переменной Y.

7.7. В Дисперсионной таблице в столбце SS приводятся суммы квадратов, в столбце df – число степеней свободы. в столбце MS – дисперсии. В строке Регрессия в столбце f вычислено значение критериальной статистики для проверки значимости регрессии. Это значение вычисляется как отношение регрессионной дисперсии к остаточной (ячейки D12 и D13). В столбце Значимость F вычисляется вероятность полученного значения критериальной статистики. Если эта вероятность меньше, например, 0.05 (заданного уровня значимости), то гипотеза о незначимости регрессии (т.е. гипотеза о том, что все коэффициенты функции регрессии равны нулю) отвергается и считается, что регрессия значима. В данном примере регрессия незначима.

7.8. В следующей таблице, в столбце Коэффициенты , записаны вычисленные значения коэффициентов функции регрессии, при этом в строке Y-пересечение записано значение свободного члена b . В столбце Стандартная ошибка вычислены среднеквадратические отклонения коэффициентов.
В столбце t-статистика записаны отношения значений коэффициентов к их среднеквадратическим отклонениям. Это значения критериальных статистик для проверки гипотез о значимости коэффициентов регрессии.
В столбце P-Значение вычисляются уровни значимости, соответствующие значениям критериальных статистик. Если вычисленный уровень значимости меньше заданного уровня значимости (например, 0.05). то принимается гипотеза о значимом отличии коэффициента от нуля; в противном случае принимается гипотеза о незначимом отличии коэффициента от нуля. В данном примере только коэффициент b значимо отличается от нуля, остальные – незначимо.
В столбцах Нижние 95% и Верхние 95% приводятся границы доверительных интервалов с доверительным уровнем 0.95. Эти границы вычисляются по формулам
Нижние 95% = Коэффициент - Стандартная ошибка * t α ;
Верхние 95% = Коэффициент + Стандартная ошибка * t α .
Здесь t α – квантиль порядка α распределения Стьюдента с (n-k-1) степенью свободы. В данном случае α = 0.95. Аналогично вычисляются границы доверительных интервалов в столбцах Нижние 90.0% и Верхние 90.0% .

7.9. Рассмотрим таблицу Вывод остатка из выходных результатов. Эта таблица появляется в выходных результатах только тогда, когда установлена хотя бы одна опция в области Остатки диалогового окна Регрессия .

В столбце Наблюдение приводятся порядковые номера значений переменной Y .
В столбце Предсказанное Y вычисляются значения функции регрессии у i = f(х i) для тех значений переменной X , которым соответствует порядковый номер i в столбце Наблюдение .
В столбце Остатки содержатся разности (остатки) ε i =Y-у i , а в столбце Стандартные остатки – нормированные остатки, которые вычисляются как отношения ε i / s ε . где s ε – среднеквадратическое отклонение остатков. Квадрат величины s ε вычисляется по формуле

где – среднее остатков. Величину можно вычислить как отношение двух значений из дисперсионной таблицы: суммы квадратов остатков (ячейка С13) и степени свободы из строки Итого (ячейка В14).

7.10. По значениям таблицы Вывод остатка строятся два типа графиков: графики остатков и графики подбора (если установлены соответствующие опции в области Остатки диалогового окна Регрессия ). Они строятся для каждого компонента переменной X в отдельности.

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

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

7.11. Последней таблицей выходных результатов является таблица Вывод вероятности . Она появляется, если в диалоговом окне Регрессия установлена опция График нормальной вероятности .
Значения в столбце Персентиль вычисляются следующим образом. Вычисляется шаг h = (1/n)*100% , первое значение равно h/2 , последнее равно 100-h/2 . Начиная со второго значения каждое последующее значение равно предыдущему, к которому прибавлен шаг h .
В столбце Y приведены значения переменной Y , упорядоченные по возрастанию. По данным этой таблицы строится так называемый график нормального распределения . Он позволяет визуально оценить степень линейности зависимости между переменными X и Y .

8. Дисперсионный анализ

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

8.2. Однофакторный дисперсионный анализ

8.2.1. Подготовим данные для анализа. Создайте новый лист и скопируйте на него колонки A, B, C, D . Удалите первые две строки. Подготовленные данные можно использовать для проведения Однофакторного дисперсионного анализа.

8.2.2. Вызовите инструмент создания выборки через Данные> Анализ данных> Однофакторный дисперсионный анализ. Заполните в соответствии с рисунком. Нажмите OK .

8.2.3. Рассмотрим таблицу Итоги : Счет – число повторений, Сумма – сумма значений показателя по строкам, Дисперсия – частная дисперсия показателя.

8.2.4. Таблица Дисперсионный анализ : первая колонка Источник вариации содержит наименование дисперсий, SS – сумма квадратов отклонений, df – степень свободы, MS – средний квадрат, F-критерий фактического F распределения. P-значение – вероятность того, что дисперсия, воспроизводимая уравнением, равна дисперсии остатков. Оно устанавливает вероятность того, что полученная количественная определенность взаимосвязи между факторами и результатом может считаться случайной. F-критическое – это значение F теоретического, которое впоследствии сравнивается с F фактическим.

8.2.5. Нулевая гипотеза о равенстве математических ожиданий всех выборок принимается, если выполняется неравенство F-критерий < F-критическое . эту гипотезу следует отвергнуть. В данном случае средние значения выборок – значимо различаются.

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

Виды регрессии

Само это понятие было введено в математику в 1886 году. Регрессия бывает:

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

Пример 1

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

Задача. На шести предприятиях проанализировали среднемесячную заработную плату и количество сотрудников, которые уволились по собственному желанию. В табличной форме имеем:


		Количество уволившихся	Зарплата
			30000 рублей
			35000 рублей
			40000 рублей
			45000 рублей
			50000 рублей
			55000 рублей
			60000 рублей

Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а 0 + а 1 x 1 +…+а k x k , где х i — влияющие переменные, a i — коэффициенты регрессии, 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-квадрата<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Анализ коэффициентов

Число 64,1428 показывает, каким будет значение Y, если все переменные xi в рассматриваемой нами модели обнулятся. Иными словами можно утверждать, что на значение анализируемого параметра оказывают влияние и другие факторы, не описанные в конкретной модели.

Следующий коэффициент -0,16285, расположенный в ячейке B18, показывает весомость влияния переменной Х на Y. Это значит, что среднемесячная зарплата сотрудников в пределах рассматриваемой модели влияет на число уволившихся с весом -0,16285, т. е. степень ее влияния совсем небольшая. Знак «-» указывает на то, что коэффициент имеет отрицательное значение. Это очевидно, так как всем известно, что чем больше зарплата на предприятии, тем меньше людей выражают желание расторгнуть трудовой договор или увольняется.

Множественная регрессия

Под таким термином понимается уравнение связи с несколькими независимыми переменными вида:

y=f(x 1 +x 2 +…x m) + ε, где y — это результативный признак (зависимая переменная), а x 1 , x 2 , …x m — это признаки-факторы (независимые переменные).

Оценка параметров

Для множественной регрессии (МР) ее осуществляют, используя метод наименьших квадратов (МНК). Для линейных уравнений вида Y = a + b 1 x 1 +…+b m x m + ε строим систему нормальных уравнений (см. ниже)

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

Отсюда получаем:

где σ — это дисперсия соответствующего признака, отраженного в индексе.

МНК применим к уравнению МР в стандартизируемом масштабе. В таком случае получаем уравнение:

в котором t y , t x 1, … t xm — стандартизируемые переменные, для которых средние значения равны 0; β i — стандартизированные коэффициенты регрессии, а среднеквадратическое отклонение — 1.

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

Задача с использованием уравнения линейной регрессии

Предположим, имеется таблица динамики цены конкретного товара N в течение последних 8 месяцев. Необходимо принять решение о целесообразности приобретения его партии по цене 1850 руб./т.


номер месяца	название месяца	цена товара N
		1750 рублей за тонну
		1755 рублей за тонну
		1767 рублей за тонну
		1760 рублей за тонну
		1770 рублей за тонну
		1790 рублей за тонну
		1810 рублей за тонну
		1840 рублей за тонну

Для решения этой задачи в табличном процессоре «Эксель» требуется задействовать уже известный по представленному выше примеру инструмент «Анализ данных». Далее выбирают раздел «Регрессия» и задают параметры. Нужно помнить, что в поле «Входной интервал Y» должен вводиться диапазон значений для зависимой переменной (в данном случае цены на товар в конкретные месяцы года), а в «Входной интервал X» — для независимой (номер месяца). Подтверждаем действия нажатием «Ok». На новом листе (если так было указано) получаем данные для регрессии.

Строим по ним линейное уравнение вида y=ax+b, где в качестве параметров a и b выступают коэффициенты строки с наименованием номера месяца и коэффициенты и строки «Y-пересечение» из листа с результатами регрессионного анализа. Таким образом, линейное уравнение регрессии (УР) для задачи 3 записывается в виде:

Цена на товар N = 11,714* номер месяца + 1727,54.

или в алгебраических обозначениях

y = 11,714 x + 1727,54

Анализ результатов

Чтобы решить, адекватно ли полученное уравнения линейной регрессии, используются коэффициенты множественной корреляции (КМК) и детерминации, а также критерий Фишера и критерий Стьюдента. В таблице «Эксель» с результатами регрессии они выступают под названиями множественный R, R-квадрат, F-статистика и t-статистика соответственно.

КМК R дает возможность оценить тесноту вероятностной связи между независимой и зависимой переменными. Ее высокое значение свидетельствует о достаточно сильной связи между переменными «Номер месяца» и «Цена товара N в рублях за 1 тонну». Однако, характер этой связи остается неизвестным.

Квадрат коэффициента детерминации R 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

F-статистика, называемая также критерием Фишера, используется для оценки значимости линейной зависимости, опровергая или подтверждая гипотезу о ее существовании.

(критерий Стьюдента) помогает оценивать значимость коэффициента при неизвестной либо свободного члена линейной зависимости. Если значение t-критерия > 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,405*VD +0,691*VZP - 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Данные для АО «MMM» представлены в таблице:

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

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

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

ВЫВОД ИТОГОВ

Таблица 8.3а. Регрессионная статистика

Регрессионная статистика
Множественный R	0,998364
R-квадрат	0,99673
Нормированный R-квадрат	0,996321
Стандартная ошибка	0,42405
Наблюдения	10

Сначала рассмотрим верхнюю часть расчетов, представленную в таблице 8.3а , - регрессионную статистику.

Величина R-квадрат , называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала .

В большинстве случаев значение R-квадрат находится между этими значениями, называемыми экстремальными, т.е. между нулем и единицей.

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

В нашем примере мера определенности равна 0,99673, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным.

Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y).

Множественный R равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы.

В простом линейном регрессионном анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона из предыдущего примера (0,998364).

Таблица 8.3б. Коэффициенты регрессии

	Коэффициенты	Стандартная ошибка	t-статистика
Y-пересечение	2,694545455	0,33176878	8,121757129
Переменная X 1	2,305454545	0,04668634	49,38177965
* Приведен усеченный вариант расчетов

Теперь рассмотрим среднюю часть расчетов, представленную в таблице 8.3б . Здесь даны коэффициент регрессии b (2,305454545) и смещение по оси ординат, т.е. константа a (2,694545455).

Исходя из расчетов, можем записать уравнение регрессии таким образом:

Y= x*2,305454545+2,694545455

Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициентов регрессии (коэффициента b).

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

Если знак при коэффициенте регрессии - отрицательный, связь зависимой переменной с независимой является отрицательной (обратной).

В таблице 8.3в . представлены результаты вывода остатков . Для того чтобы эти результаты появились в отчете, необходимо при запуске инструмента "Регрессия" активировать чекбокс "Остатки".

ВЫВОД ОСТАТКА

Таблица 8.3в. Остатки

Наблюдение	Предсказанное Y	Остатки	Стандартные остатки
1	9,610909091	-0,610909091	-1,528044662
2	7,305454545	-0,305454545	-0,764022331
3	11,91636364	0,083636364	0,209196591
4	14,22181818	0,778181818	1,946437843
5	16,52727273	0,472727273	1,182415512
6	18,83272727	0,167272727	0,418393181
7	21,13818182	-0,138181818	-0,34562915
8	23,44363636	-0,043636364	-0,109146047
9	25,74909091	-0,149090909	-0,372915662
10	28,05454545	-0,254545455	-0,636685276

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

Оцените качество построенной модели. Улучшилось ли качество модели по сравнению с однофакторной моделью? Дайте оценку влияния значимых факторов на результат с помощью коэффициентов эластичности, - и -коэффициентов.

Для оценки качества выбранной множественной модели (6) , аналогично п.1.4 данной задачи, используем коэффициент детерминации R - квадрат, среднюю относительную ошибку аппроксимации и F -критерий Фишера.

Коэффициент детерминации R -квадрат возьмем из итогов «Регрессии» (таблица «Регрессионная статистика» для модели (6)).

Следовательно, вариация (изменение) цены квартиры Y на 76,77% объясняется по данному уравнению вариацией города области Х 1 , числа комнат в квартире Х 2 и жилой площади Х 4 .

Используем исходные данные Y i и найденные инструментом «Регрессия» остатки (таблица «Вывод остатка» для модели (6)). Рассчитаем относительные погрешности и найдем среднее значение
.

ВЫВОД ОСТАТКА

Наблюдение	Предсказанное Y	Остатки	Отн. погрешность
1	45,95089273	-7,95089273	20,92340192
2	86,10296493	-23,90296493	38,42920407
3	94,84442678	30,15557322	24,12445858
4	84,17648426	-23,07648426	37,76838667
5	40,2537216	26,7462784	39,91981851
6	68,70572376	24,29427624	26,12287768
7	143,7464899	-25,7464899	21,81905923
8	106,0907598	25,90924022	19,62821228
9	135,357993	-42,85799303	46,33296544
10	114,4792566	-9,47925665	9,027863476
11	41,48765602	0,512343975	1,219866607
12	103,2329236	21,76707636	17,41366109
13	130,3567798	39,64322022	23,3195413
14	35,41901876	2,580981242	6,7920559
15	155,4129693	-24,91296925	19,0903979
16	84,32108188	0,678918123	0,798727204
17	98,0552279	-0,055227902	0,056355002
18	144,2104618	-16,21046182	12,66442329
19	122,8677535	-37,86775351	44,55029825
20	100,0221225	59,97787748	37,48617343
21	53,27196558	6,728034423	11,21339071
22	35,06605378	5,933946225	14,47303957
23	114,4792566	-24,47925665	27,19917406
24	113,1343153	-30,13431529	36,30640396
25	40,43190991	4,568090093	10,15131132
26	39,34427892	-0,344278918	0,882766457
27	144,4794501	-57,57945009	66,25943623
28	56,4827667	-16,4827667	41,20691675
29	95,38240332	-15,38240332	19,22800415
30	228,6988826	-1,698882564	0,748406416
31	222,8067278	12,19327221	5,188626473
32	38,81483144	1,185168555	2,962921389
33	48,36325811	18,63674189	27,81603267
34	126,6080021	-3,608002113	2,933335051
35	84,85052935	15,14947065	15,14947065
36	116,7991162	-11,79911625	11,23725357
37	84,17648426	-13,87648426	19,73895342
38	113,9412801	-31,94128011	38,95278062
39	215,494184	64,50581599	23,03779142
40	141,7795953	58,22040472	29,11020236
Среднее	101,2375		22,51770962

По столбцу относительных погрешностей найдем среднее значение =22.51% (с помощью функции СРЗНАЧ).

Сравнение показывает, что 22.51%>7%. Следовательно, точность модели неудовлетворительная.

С помощью F – критерия Фишера проверим значимость модели в целом. Для этого выпишем из итогов применения инструмента «Регрессия» (таблица «дисперсионный анализ» для модели (6)) F = 39,6702.

С помощью функции FРАСПОБР найдем значение F кр =3.252 для уровня значимости α = 5% , и чисел степеней свободы k 1 = 2 , k 2 = 37 .

F > F кр , следовательно, уравнение модели (6) является значимым, его использование целесообразно, зависимая переменная Y достаточно хорошо описывается включенными в модель (6) факторными переменными Х 1 , Х 2 . и Х 4 .

Дополнительно с помощью t –критерия Стьюдента проверим значимость отдельных коэффициентов модели.

t –статистики для коэффициентов уравнения регрессии приведены в итогах инструмента «Регрессия». Получены следующие значения для выбранной модели (6) :

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-5,643572321	12,07285417	-0,46745966	0,642988	-30,1285	18,84131	-30,1285	18,84131
X4	2,591405557	0,461440597	5,61590284	2,27E-06	1,655561	3,52725	1,655561	3,52725
X1	6,85963077	9,185748512	0,74676884	0,460053	-11,7699	25,48919	-11,7699	25,48919
X2	-1,985156991	7,795346067	-0,25465925	0,800435	-17,7949	13,82454	-17,7949	13,82454

Критическое значение t кр найдено для уровня значимости α=5% и числа степеней свободы k =40–2–1=37 . t кр =2.026 (функция СТЬЮДРАСПОБР).

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

Для коэффициента регрессии β 1 =6.859 определена статистика
, β 1 не является значимым, его и фактор города области можно удалить из модели.

Для коэффициента регрессии β 2 =-1,985 определена статистика
, t кр , следовательно, коэффициент регрессии β 2 не является значимым, его и фактор числа комнат в квартире можно исключить из модели.

Для коэффициента регрессии β 4 =2.591 определена статистика
, >t кр, следовательно, коэффициент регрессии β 4 является значимым, его и фактор жилой площади квартиры можно сохранить в модели.

Выводы о значимости коэффициентов модели сделаны на уровне значимости α=5% . Рассматривая столбец «P-значение», отметим, что свободный коэффициент α можно считать значимым на уровне 0.64 = 64%; коэффициент регрессии β 1 – на уровне 0,46 = 46%; коэффициент регрессии β 2 – на уровне 0,8 = 80%; а коэффициент регрессии β 4 – на уровне 2,27E-06= 2,26691790951854E-06 = 0,0000002%.

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

Таким образом, при добавлении в уравнение регрессии фактора «город области» Х 1 и фактора «число комнат в квартире» Х 2 качество модели ухудшилось, что говорит в пользу удаления факторов Х 1 и Х 2 из модели.

Проведем дальнейшие расчеты.

Средние коэффициенты эластичности в случае линейной модели определяются формулами
.

С помощью функции СРЗНАЧ найдем: S Y , при увеличении только фактора Х 4 на одно его стандартное отклонение – увеличивается на 0,914 S Y

Дельта-коэффициенты определяются формулами
.

Найдем коэффициенты парной корреляции с использованием инструмента «Корреляция» пакета «Анализ данных» в Excel.

	Y	X1	X2	X4
Y	1
X1	-0,01126	1
X2	0,751061	-0,0341	1
X4	0,874012	-0,0798	0,868524	1

Коэффициент детерминации был определен ранее и равен 0.7677.

Вычислим дельта-коэффициенты:

;

Поскольку Δ 1 1 и Х 2 выбрана неудачно, и их нужно удалить из модели. Значит, по уравнению полученной линейной трехфакторной модели изменение результирующего фактора Y (цены квартиры) на 104% объясняется воздействием фактора Х 4 (жилой площадью квартиры), на 4% воздействием фактора Х 2 (число комнат), на 0,0859% воздействием фактора Х 1 (город области).

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

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

многомерные регрессионные модели,
имитационное моделирование.

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

1.4.1 Многомерные регрессионные модели

Многомерная регрессионная модель – это уравнение с несколькими независимыми переменными.

Для построения многомерной регрессионной модели могут быть использованы различные функции, наибольшее распространение получили линейная и степенная зависимости:

В линейной модели параметры (b 1 , b 2 , … b n) интерпретируются как влияние каждой из независимых переменных на прогнозируемую величину, если все другие независимые переменные равны нулю.

В степенной модели параметры являются коэффициентами эластичности. Они показывают, на сколько процентов изменится в среднем результат (y) с изменением соответствующего фактора на 1% при неизменности действия других факторов. Для расчета параметров уравнений множественной регрессии также используется метод наименьших квадратов.

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

Во-первых, данные должны быть наблюдаемыми , т.е. получены в результате замера, а не расчета.

Во-вторых, из массива данных необходимо исключить повторяющиеся и сильно отличающиеся данные . Чем больше неповторяющихся данных и чем однороднее совокупность, тем лучше будет уравнение. Под сильно отличающимися значениями понимается наблюдения исключительно не вписывающиеся в общий ряд. Например, данные о зарплате рабочих выражены четырех- и пятизначными числами (7 000, 10 000, 15 000), но обнаружено одно шестизначное число (250 000). Очевидно, что это ошибка.

Третье правило (требование) – это достаточно большой объем данных . Мнения статистиков относительно того, сколько необходимо данных для построения хорошего уравнения расходятся. По мнению одних, данных необходимо в 4-6 раз больше числа факторов. Другие утверждают, что не менее чем в 10 раз больше числа факторов, тогда закон больших чисел, действуя в полную силу, обеспечивает эффективное погашение случайных отклонений от закономерного характера связи.

Построение многомерной регрессионной модели в MS Excel
В электронных таблицах Excel имеется возможность построения только лишь линейной многомерной регрессионной модели.
, (1.19)
Для этого необходимо выбрать пункт «Анализ данных», а затем в появившемся окне - инструмент «регрессия»

Рисунок 1.45 – Диалоговое окно инструмента «Регрессия»
В появившемся окне необходимо заполнить ряд полей, в том числе:

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

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

По умолчанию применяется уровень надежности 95%. Если хотите установить другой уровень, установите флажок и в поле рядом введите желаемый уровень надежности.

Флажок «Константа-ноль» необходимо пометить только в том случае, если вы хотите получить уравнение регрессии без свободного члена а , так чтобы линия регрессии прошла через начала координат.
Вывод результатов расчетов может быть организован 3 способами:

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

Установка флажков «Остатки» и «Стандартизированные остатки» заказывает их включение в выходной диапазон.
Чтобы построить график остатков для каждой независимой переменной, установите флажок «График остатков». Остатки иначе называют ошибками прогнозирования. Они определяются как разность между фактическими и прогнозируемыми значениями Y.
Интерпретация графиков остатков
В графиках остатков не должно быть закономерности. Если закономерность прослеживается, то это значит, что в модель не включен какой-то не известный нам, но закономерно действующий фактор, о которых нет данных.

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

Интерпретация графиков подбора
В Excel на графиках подбора красными точками обозначаются теоретические значения Y , синими точками - исходные данные. Если красные точки хорошо накладываются на синие точки, то это визуально свидетельствует об удачном уравнении регрессии.
Необходимым этапом прогнозирования на основе многомерных регрессионных моделей является оценка статистической значимости уравнения регрессии, т.е. пригодности построенного уравнения регрессии для использования в целях прогнозирования. Для решения этой задачи в MS Excel рассчитывается ряд коэффициентов. А именно:

Множественный коэффициент корреляции

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

Множественный коэффициент детерминации ( R 2 ).

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

Нормированный коэффициент детерминации R 2

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

Нормированный рассчитывается по формуле:

, (1.21)

где - нормированный множественный коэффициент детерминации,

Множественный коэффициент детерминации,

Объем совокупности,

Количество факторных переменных.

Стандартная ошибка регрессии указывает приблизительную величину ошибки прогнозирования. Используется в качестве основной величины для измерения качества оцениваемой модели. Рассчитывается по формуле:

, (1.22)

где - сумма квадратов остатков,

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

ВЫВОД ИТОГОВ

Регрессионная статистика
Множественный R	0.973101
R-квадрат	0.946926
Нормированный R-квадрат	0.940682
Стандартная ошибка	0.59867
Наблюдения	20

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	2	108.7071	54.35355	151.6535	1.45E-11
Остаток	17	6.092905	0.358406
Итого	19	114.8

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95.0%	Верхние 95.0%
Y-пересечение	1.835307	0.471065	3.89608	0.001162	0.841445	2.829169	0.841445	2.829169
x1	0.945948	0.212576	4.449917	0.000351	0.49745	1.394446	0.49745	1.394446
x2	0.085618	0.060483	1.415561	0.174964	-0.04199	0.213227	-0.04199	0.213227

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

объясненную регрессией (или факторную),
остаточную.

, (1.2 3)
Пригодность регрессионной модели для прогнозирования зависит от того, какая часть общей вариации признака y приходится на вариацию объясненную регрессией. Очевидно, что если сумма квадратов отклонений объясненная регрессией будет больше остаточной, то делают вывод о статистической значимости уравнения регрессии. Это равносильно тому, что коэффициент детерминации приближается к единице.
Обозначения в таблице «Дисперсионный анализ»:
Второй столбец таблицы называется и означает число степеней свободы. Для общей дисперсии число степеней свободы равно: , для факторной дисперсии (или дисперсии, объясненной регрессией) , для остаточной дисперсии .

где n – это кол-во наблюдений,

m – кол-во факторных переменных модели.
Третий столбец таблицы называется . В нем представлена сумма квадратов отклонений. Общая сумма квадратов отклонений определяется по формуле:

, (1.24)
Факторная сумма квадратов:

, (1.26)
Четвертый столбец называется - среднее значение квадратов отклонений. Определяется по формуле:

С помощью F-критерия Фишера определяется статистическая значимость коэффициента детерминации уравнения регрессии. Для этого выдвигается нулевая гипотеза, которая утверждает, что между результирующей и факторными переменными связь отсутствует . Это возможно лишь в том случае, когда все параметры уравнения множественной линейной регрессии и коэффициент корреляции равны нулю.

Для проверки этой гипотезы необходимо рассчитать фактическое значение F-критерия Фишера и сравнить его с табличным. Фактическое значение F-критерия рассчитывается по формуле:

, (1.28)

Выбирается из специальных статистических таблиц по:

заданному уровню значимости () и
числу степеней свободы.

В MS Excel табличное значение F-критерия может быть определено с помощью функции: =FРАСПОБР(вероятность; степени свободы1; степени свободы2)

Например: =FРАСПОБР(0,05;df1;df2)
Уровень значимости 1 выбирается на тот же, на котором вычислялись параметры регрессионной модели. По умолчанию установлено 95%.

Если , то выдвинутая гипотеза отклоняется и признается статистическая значимость уравнения регрессии. В случае особо важных прогнозов табличное значение F-критерия рекомендуется увеличить в 4 раза, то есть проверяется условие:
=151.65; = 3.59
Расчетное значение значительно превышает табличное значение. Это значит, что коэффициент детерминации значимо отличается от нуля, поэтому гипотезу об отсутствии регрессионной зависимости следует отклонить.
Теперь оценим значимость коэффициентов регрессии на основе t -критериия Стьюдента. Он позволяет определить, какие из факторных переменных (х) оказывают наибольшее влияние на результирующую переменную (y).

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

Рассчитывается по формуле:

, (1.29)

где - СКО для результирующей переменной,

СКО для признака ,

Коэффициент детерминации для уравнения множественной

регрессии,

Коэффициент детерминации для зависимости фактора со

всеми другими факторами уравнения.

Число степеней свободы для остаточной суммы квадратов

отклонений.
В MS Excel стандартные ошибки рассчитываются автоматически (располагаются в 3-ем столбце 3-ей таблицы).
Фактическое значение t -критерия Стьюдента в MS Excel располагается в 4-ом столбце 3-ей таблицы и называется t-статистика.
(4 столбец) = (2 столбец) / (3 столбец)

t-статистика = Коэффициенты/ Стандартная ошибка
Табличное значение t -критерия Стьюдента зависит от принятого уровня значимости (обычно ; 0,05; 0,01) и числа степеней свободы .

где n – число единиц совокупности,

m – число факторов в уравнении.
В MS Excel табличное значение критерия Стьюдента может быть определено с помощью функции:

СТЬЮДРАСПОБР(вероятность; число степеней свободы)
Например: =СТЬЮДРАСПОБР(0,05;7)
Если , то делается вывод, что коэффициент уравнения регрессии является статистически значимым (надежным) и его можно включать в модель и использовать для прогнозирования.

1.4.2 Метод имитационного моделирования Монте-Карло

Метод имитационного моделирования получил свое название в честь города Монте-Карло, расположенного в княжестве Монако, одного из самых маленьких государств мира, расположенного на берегу Средиземного моря, около границы Франции и Италии.

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

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

Инструмент «генерация случайных чисел» доступен пользователям MS Excel 2007 после активизации надстройки Пакет анализа . Порядок активизации надстройки описан выше (см. стр.10, рис.1.5-1.8). Для выполнения имитационного моделирования в меню ДАННЫЕ необходимо выбрать пункт «Анализ данных» , в появившемся диалоговом окне из списка выбрать инструмент «Генерация случайных чисел» и щелкнуть ОК.

Рисунок 1.46 - Интерфейс меню анализа данных
В появившемся диалоговом окне необходимо для каждой стохастической переменной выбрать тип вероятностного распределения и задать соответствующие входные параметры.

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

Нормальное распределение (или закон Муавра-Гаусса-Лапласа) предполагает, что варианты прогнозируемого параметра тяготеют к среднему значению. Значения переменной, существенно отличающиеся от среднего, то есть находящиеся в «хвостах» распределения, имеют малую вероятность.

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

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

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

Вид вероятностного распределения	Входные параметры
1 Нормальное распределение	среднее значение; стандартное отклонение;
2 Треугольное распределение	среднее значение;
3 Равномерное распределение	пределы возможного диапазона значений;
4 Дискретное распределение	конкретные значения переменной; соответствующие данным значениям вероятности.

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

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

среднее значение;
среднеквадратическое отклонение;
дисперсию;
минимальное и максимальное значение;
размах колебаний;
коэффициент асимметрии;
эксцесс.

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

Рисунок 1.48 - Гистограмма значений прогнозируемого показателя

Реализация указанных этапов позволит получить вероятностную оценку значений прогнозируемого показателя (интервальный прогноз).

КАТЕГОРИИ