3. Определить в отобранных моделях наиболее приемлемые формы связи между изучаемым показателем и факторами. Форма связи может быть определена путем:
а) построения диаграммы (эмпирической линии регрессии) с помощью Мастера диаграмм;
Для построения диаграммы
активизировать иконку Мастер диаграмм;
выделить «Точечная», нажать «группировка данных по столбцам»;
ввести диапазон ячеек X и диапазон ячеек У (через точку с запятой) путем выделения соответствующего диапазона на рабочем листе с исходными данными для корреляции.
нажать «Далее» и подписать название графика, а также осей X и У.
нажать «Готово».
Для построения графиков зависимости У от других X следует предварительно выделить курсором другую ячейку (т.к. график записывается в определенную ячейку) и повторить описанные выше операции.
Исходя из разброса точек на графике, определить форму связи У с соответствующим X. В данной лабораторной работе должны быть отобраны только факторы с линейной связью У от выбранного X. Если связь не линейная вернуться к пункту 1:
б) вычисление коэффициентов корреляции;
активизировать иконку «Мастер функций»;
выбрать категорию «Статистические», а в ней выбрать КОРРЕЛ;
нажать «ОК»;
в окно «массив 1» вводят выделенный столбец У, в окно «массив 2» - выделенный столбец X;
нажать «ОК».
В ячейке, где находился курсор, появится значение парного коэффициента корреляции между значениями исследуемого диапазона У и изучаемого диапазона X.
аналогично осуществить расчеты по всем другим X;
проанализировать вычисленные коэффициенты корреляции (Ккор) путем составления табл.3;
Таблица 3 - Коэффициенты оценки степени корреляционной связи
Результативный показатель |
Фактор |
Коэффициент корреляции |
Вывод о степени связи |
1 |
2 |
3 |
4 |
У |
Х1 |
|
|
У |
|
|
|
У |
Хп |
|
|
Ккор ≤ 0,5 - связь слабая (фактор исключается из исследования);
0,5 < Ккор ≤ 0,7 - связь средняя (можно исследовать влияние фактора дальше);
Ккор > 0,7 - связь существенная (фактор обязательно включают в дальнейшее исследование).
- вывод о степени связи в кол. 4 табл.2.3. автоматизировать путем использования функции ЕСЛИ (...):
в ячейку, где должен находиться вывод о степени связи вводится без пробелов: =ЕСЛИ(АВS (здесь будет адрес ячейки с коэффициентом корреляции) > 0,7; "сильная зависимость"; ЕСЛИ(АВS (здесь будет адрес ячейки с коэффициентом корреляции) >0,5; "средняя зависимость"; "слабая зависимость")).
4. Построить эконометрическую модель (уравнение регрессии). Построение эконометрической модели заключается в расчете параметров модели у = f (Xj…..Хn) и осуществляется с помощью функции ЛИНЕЙН (...).
- выделить место (блок), в котором будут размещены результаты вычислений, для n независимых переменных размер блока равняется (n+1)∙5
Вычисленные значения должны быть размещены в табл.4
Таблица 4 - Результаты вычислений функции ЛИНЕЙН (...)
1 |
Коэффициенты; уравнения регрессии |
ап |
aп-1 |
aп-1 |
. . . |
a1 |
b |
2 |
Стандартная ошибка для коэффициентов |
Se aп |
Se an.1 |
Se an.2 |
. . . |
Se a1 |
Se b |
3 |
Коэффициент детерминации |
R2 |
Scy |
Стандартная ошибка для У |
|||
4 |
Расчетное значение критерия Фишера |
Fp |
Дf |
Степень свободы |
|||
5 |
Регрессионная сумма квадратов |
Srcg |
Sost |
Остаточная сумма квадратов |
|||
активизировать «Мастер функций», выбрать «Статистические», в ней ЛИНЕЙН;
нажать «ОК»;
задать аргументы функции ЛИНЕЙН (...), как диапазоны соответствующих ячеек и логических значений:
изв_знач_у - это известные значения зависимой переменной У;
изв_знач_х - это известные значения показателя-фактора или нескольких факторов;
константа — 1;
стат — 1.
- нажать «ОК».
В результате завершения работы мастера функций, будет вычислено только одно значение в первой ячейке выделенного блока. Для получения всех значений, которые считаются автоматически, необходимо нажать F2, а потом CTRL + SHIFT + ENTER. Знак "+" символизирует одновременный нажим трех клавиш.
Данный этап исследований дает коэффициенты уравнения и регрессионную статистику, что наглядно видно на рисунке 1.
Коэффициент в уравнении |
|
1,917252 |
4261,293 |
|
Свободный член b |
Стандартная ошибка для a (t - критерий) |
|
0,429215 |
15705,731 |
|
Стандартная ошибка для b |
Коэффициент детерминации (Я2ф) |
|
0,799622 |
32622,604 |
|
Стандартная ошибка для У |
Критерий Фишера (Фф) |
|
19,95293 |
5 |
|
Степень свободы (Дf) |
Регрессионная сумма • квадратов |
|
21234596 |
5321171606 |
|
Остаточная сумма квадратов |
Рисунок 1 - Коэффициенты уравнения и регрессионная статистика
построить трендовую модель (учитывает влияние фактора времени на динамику У), в которой вместо независимого фактора использовать порядковый номер каждого значения зависимой переменной У;
построить таблицу коэффициентов трендовой модели.