Вычисления в ms Excel

Пример. Известны следующие данные по одному из субъектов Российской Федерации:

Совокупные доходы физ. лиц, млн. руб.	14855,30	18745,10	20268,70	20319,30	20174,80	22524,50	21805,80
Вклады физ. лиц в банках, тыс. руб.	36 643	38 297	38 993	40 394	41 090	42 691	43 916
Совокупные доходы физ. лиц, млн. руб.	21571,30	22902,80	23928,40	23741,80	30271,90	30481,90	33088,00
Вклады физ. лиц в банках, тыс. руб.	43 988	44 684	43 721	44 198	46 465	47 481	48 438
Совокупные доходы физ. лиц, млн. руб.	32133,70	34915,70	33377,50	34923,40	32558,70	33149,40
Вклады физ. лиц в банках, тыс. руб.	49 632	53 506	52 559	53 461	49 484	48 387

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

Рис. 1.8. Исходные данные в MS Excel

Занесем исходные данные в MS Excel в виде таблицы, состоящей их двух столбцов, в первом расположены значения независимой переменной , а во втором – зависимой переменной(рис. 1.8). Чтобы определить характер зависимости – построим поле корреляции. Для этого выделяем оба столбца ((!) данныедолжны быть в первом столбце,– во втором), заходим в меню «Вкладка» и выбираем точечную диаграмму (рис. 1.9). Затем проходим все шаги построения диаграммы, заполняя графы с ее названием и подписями осей координат. Получаем поле корреляции, не очень удачно расположенное на диаграмме (рис. 1.10).

Мы видим, что точки близко расположены друг к другу и занимают малую часть поля диаграммы. Для этого изменим масштаб осей координат (рис. 1.11) следующим образом: подсвечиваем ось категорий (ось), с помощью правой кнопки мыши выбираем «формат оси» и устанавливаем минимальное значение 14000. Аналогично на оси значений (ось) задаем минимальное значение 35000. Далее, используя художественные способности, облагораживаем внешний вид поля корреляции (рис. 1.12).

Рис. 1.9. Построение поле корреляции с использованием точечной диаграммы

Рис. 1.10. Поле корреляции, требующее дополнительной художественной обработки

Рис. 1.11. Корректируем формат оси категорий ()

Следующим шагом наносим на поле корреляции прямую : в контекстном меню (правая кнопка мыши) выбираемпункт «добавить линию тренда» (предварительно подсветив график) (рис. 1.13). В появившемся окошке выделяем линейную модель, затем выбираем вкладку параметры и отмечаем галочкой «показывать уравнения на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации (R^2)».

После проведенной процедуры поле корреляции примет нужный для дальнейшего анализа вид (рис. 1.14).

Рис. 1.12. Измененное поле корреляции

Рис. 1.13. Добавление на диаграмму прямой

Рис. 1.14. Поле корреляции подготовлено для анализа

Проверка всех результатов расчетов проводится с использованием пакета анализа: меню Данные – Анализ данных – регрессия (рис. 1.23).

Рис. 1.23. Использование меню Анализа данных в MS Excel.

Результаты расчетов, проведенных с помощью Пакета анализа, представлены на рис. 1.24. Множественный R – это значение коэффициента корреляции, R-квадрат – коэффициент детерминации. В таблице Дисперсионный анализ F – это значение . Если ЗначимостьF меньше значения 0,05, то гипотеза об отсутствии линейной связи отклоняется. В следующей таблице в столбце Коэффициенты и строке Y-пересечение – это значение , в строкеxi – это . В столбце Стандартная ошибка стоят значенияисоответственно. В столбцеt-статистика – значения исоответственно. Если в столбцеP-значение стоит число меньшее, чем 0,05, то соответствующий коэффициент регрессии спастически значим с вероятностью 0,95. Нижние и верхние 95% – это границы доверительных интервалов при . В последней таблице Вывод остатка даны теоретические или предсказанные значение зависимой переменной, рассчитанные по построенной модели.

Рис. 1.24. Результаты расчетов линейной модели .