Корреляционно-регрессионное моделирование в среде ms excel при ниРиОкр

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

Для выполнения работы необходимо решить следующие задачи:

1. Построить эконометрическую модель (уравнение регрессии: парные

и множественную).

2. Оценить параметры полученного уравнения и его приемлемость для

практического использования.

3. Построить графики полученных моделей.

Методика выполнения лабораторной работы

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

Непосредственное выполнение работы целесообразно проводить в следующей последовательности.

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

2. Построить таблицу исходной информации для проведения

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

Таблица 2.1 - Исходная информация для корреляционного регрессионного анализа

Период (или пред- приятие)	Фактическое значение результативного показателя, У	Показатели-факторы (их фактическое значение),
		X
		Х1	Х2	Х3	….	Хn
1
2 …
k

Примечание, п - количество факторов, которые вводятся в модель;

k - количество объектов исследования.

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

а) в меню Сервис выбрать Надстройки, а в них активизировать Пакет анализа.

б) в меню Сервис выбрать Анализ данных, выбрать Корреляция, нажать ОК

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

Таблица 2.2 - Матрица парных коэффициентов корреляции

	У	X1	Х2		Xj
У	1
Х1	КкорУ1	1
Х2	Ккору2	Ккор 12	1
…	…	…	…	1
Xj	Ккоруj	Ккор1j	Kкop2j	…	1

Примечание. У - результативный признак;

X₁,X₂, ...Xj-факторы, влияющие на результат;

Ккор - коэффициенты парной корреляции.

В многофакторную модель включают только те факторы X, коэффициенты корреляции между которыми меньше, чем коэффициенты

корреляции каждого из них с У.

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

3. Определить в отобранных моделях наиболее приемлемые формы связи между изучаемым показателем и факторами. Форма связи может быть определена путем:

а) построения диаграммы (эмпирической линии регрессии) с помощью Мастера диаграмм;

Для построения диаграммы

• активизировать иконку Мастер диаграмм;

• выделить «Точечная», нажать «группировка данных по столбцам»;

• ввести диапазон ячеек X и диапазон ячеек У (через точку с запятой) путем выделения соответствующего диапазона на рабочем листе с исходными данными для корреляции.

• нажать «Далее» и подписать название графика, а также осей X и У.

• нажать «Готово».

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

Исходя из разброса точек на графике, определить форму связи У с соответствующим X. В данной лабораторной работе должны быть отобраны только факторы с линейной связью У от выбранного X. Если связь не линейная вернуться к пункту 1.

б) вычисление коэффициентов корреляции;

• активизировать иконку «Мастер функций»;

• выбрать категорию «Статистические», а в ней выбрать КОРРЕЛ;

• нажать «ОК»;

• в окно «массив 1» вводят выделенный столбец У, в окно «массив 2» - выделенный столбец X;

• нажать «ОК».

В ячейке, где находился курсор, появится значение парного коэффициента корреляции между значениями исследуемого диапазона У и изучаемого диапазона X.

• аналогично осуществить расчеты по всем другим X;

проанализировать вычисленные коэффициенты корреляции (Ккор) путем составления табл.2.3;

Таблица 2.3 - Коэффициенты оценки степени корреляционной связи
Результативный показатель	Фактор	Коэффициент корреляции	Вывод о степени связи
У	X1
У	…
У	Хп

Ккор< 0,5 - связь слабая (фактор исключается из исследования);

0,5 < Ккор < 0,7 - связь средняя (можно исследовать влияние фактора

дальше);

Ккор > 0,7 - связь существенная (фактор обязательно включают в

дальнейшее исследование).

- вывод о степени связи в кол. 4 табл.2.3. автоматизировать путем использования функций ЕСЛИ (...):

в ячейку, где должен находиться вывод о степени связи вводится без пробелов: =ЕСЛИ(АВS(здесь будет адрес ячейки с коэффициентом корреляции) > 0,7; " сильная зависимость"; ЕСЛИ(АВS(здесь будет адрес ячейки с коэффициентом корреляции) >0,5; "средняя зависимость"; "слабая зависимость")).

4. Построить эконометрическую модель (уравнение регрессии). Построение эконометрической модели заключается в расчете параметров

модели y = f(X_1…Х_n) и осуществляется с помощью функции ЛИНЕЙН (...).

- выделить место (блок), в котором будут размещены результаты вычислений, для п независимых переменных размер блока равняется (п+1)х5;

Вычисленные значения должны быть размещены в табл.2.4

Таблица 2.4 - Результаты вычислений функции ЛИНЕЙН (...)
1	Коэффициенты уравнения регрессии	an	an-1	an-2	…	a1	b
2	Стандартная ошибка для коэффициентов	Sean	Se аn-1	Se аn-2		Sea1	S e b
3	Коэффициент детерминации	R^2	Scy	Стандартная ошибка для У
4	Расчетное значение критерия Фишера	fp	Df	С тепень свободы
5	Регрессионная сумма квадратов	Sreg	Sost	Остаточная сумма квадратов

В ыделяемый блок

• активизировать «Мастер функций», выбрать «Статистические», в ней ЛИНЕЙН;

• нажать «ОК»;

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

изв_знач_у — это известные значения зависимой переменной У;

изв_знач_х – это известные значения показателя-фактора или нескольких факторов;

константа — 1;

стат - 1.

- нажать «ОК».

В результате завершения работы мастера функций, будет вычислено только одно значение в первой ячейке выделенного блока. Для получения всех значений, которые считаются автоматически, необходимо нажать F2, а потом CTRL + SHIFT + ENTER. Знак "+" символизирует одновременный нажим трех клавиш.

Данный этап исследований дает коэффициенты уравнения и регрессионную статистику, что наглядно видно на рисунке 2.1.

Рисунок 2.1 - Коэффициенты уравнения и регрессионная статистика

• построить трендовую модель (учитывает влияние фактора времени на динамику У), в которой вместо независимого фактора использовать порядковый номер каждого значения зависимой переменной У;

• построить таблицу коэффициентов трендовой модели.

5. Провести экспертизу эконометрической модели.

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

первоначальную таблицу коэффициентов. Копировать фактические данные следует в табл.2.5;

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

- сопоставить критерий Фишера (Фф) с его табличным значением (Фт). Для вычисления табличного значения Фт активизировать «Мастер функций», выбрать «Статистические», а в ней функцию FРАСПОБР;

нажать «ОК»;

задать аргументы этой функции:

вероятность - вероятность случайного получения высокого

значения R², принимается (0,05) или меньше; степени_св1 количество независимых переменных в

уравнении регрессии;

степени_св2 - рассчитанные степени свободы в результативном

массиве (Дf).

сделать вывод о случайности (Фт>Фф) или неслучайности (Фт<Фф) полученного значения коэффициента детерминации, используя логическую функцию ЕСЛИ (..).

Таблица 2.5 - Исследование полученных коэффициентов

Название коэффициента	Значение		Выводы
	фактическое	табличное
Коэффициент R^2 детерминации	К2ф	-	= ЕСЛИ (К2ф <=0,5; "слабая"; ЕСЛИ (К2ф <=0,7; "средняя"; "сильная"))
критерии Фишера	Фф	Фт	= ЕСЛИ (Фф > Фт; "связь не случайная"; "связь случайная")
Название коэффициента	Значение		Выводы
	фактическое	табличное
t критерий для а1	1а1ф	t а1 т	= ЕСЛИ (t а, ф > t а, т; "связь не случайная"; "связь случайная")
t критерий для а2	1а2ф	t а2т	= ЕСЛИ (t а2 ф > t a2 т; "связь не случайная"; "связь случайная")
…	…	…
t критерий для b	1bф	tbT	= ЕСЛИ (t b ф > t b т; "связь не случайная"; "связь случайная")

– проверить значимость коэффициентов уравнения регрессии (a_1,a₂,…) и свободного члена уравнения (b) путем сопоставления t – статистик фактических с табличными t – статистиками.

t – статистики табличные рассчитать с помощью функции «СТЬЮДРАСПОБР»:

активизировать «Мастер функций», выбрать «Статистические», а в ней «СТЬЮДРАСПОБР» задать аргументы этой функции:

вероятность - вероятность случайного получения высокого

значения R², принимается (0,05);

степени_св2 - рассчитанные степени свободы в

результативном массиве (Дf).

сделать вывод о значимости или незначимости любого из коэффициентов уравнения регрессии проводится путем сопоставления tф и tт с помощью логической функции ЕСЛИ (...).

6. Построить графическую иллюстрацию полученных моделей с помощью экстраполяции зависимых данных путем использования функции ТЕНДЕНЦИЯ (...)

• выделить место (блок), в котором будут размещены результаты вычислений. Размер блока должен быть равен 1 х m, где m – количество необходимых прогнозных значений в результативном столбце;

• активизировать «Мастер функций», выбрать «Статистические», а в ней ТЕНДЕНЦИЯ;

• нажать «ОК»;

• задать аргументы функции ТЕНДЕНЦИЯ (...):

изв__значь_у - фактические значения зависимой переменной У;

изв_значь_х - фактические значения независимых переменных X;

нов__значъ_х - для экстраполяции - те же самые фактические значения независимых переменных;

константа — 1.

• нажать «ОК»;

• нажать комбинацию клавиш: F2, а потом CTRL + SHIFT + ENTER, для получения всех расчетных значений.

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

–активизировать «Мастер диаграмм», выбрать «График», построить и подписать требуемые графические линии. В качестве модели, наиболее приемлемой для прогнозно-аналитической работы выбирают ту, график которой наиболее близок к графику фактического разброса У.