В Microsoft Excel

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

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

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

Пакет анализа - это надстройка, которая представляет широкие возможности для проведения статистического анализа маркетинговых исследований.

Установка средств Пакета анализа:

1. выберите команду Сервис =>надстройки.

2. В диалоговом окне надстройки установите флажок Пакета анализа (рис. 10).

3. Щелкните на кнопке ОК.

Рис. 10. Диалоговом окно надстройки.

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

Таблица 5

Исходные данные

Престижность специальности (баллы), х	Количество студентов, выбирающих специальность (чел), у
15	80

Регрессионный анализ заключается в определении аналитического выражения между факторным признаком (х) и результативным признака (у). В результате работы инструмента Регрессия производится расчет параметров а₀ и а₁ уравнения линейной регрессии у = а₀ + а₁х и проверка его адекватности исследуемым фактическим данным.

В Microsoft Excel необходимо перенести данные таблицы 5, далее запустить инструмент Регрессия:

1. Сервис – Анализ данных – Регрессия – ОК. Появляется окно «Регрессия» (рис. 11).

Рис. 11. Окно «Регрессия»

2. Входной интервал У – диапазон ячеек таблицы со значениями признака У (В3:В7).

3. Входной интервал Х – диапазон ячеек таблицы со значениями Х (А3:А7).

4. Метки – не активизировать.

5. Уровень надежности – 95%.

6. Константа – ноль – не активизировать.

7. Выходной интервал – ячейка с параметрами А9.

8. Новый рабочий лист / Новая рабочая книга – не активизировать.

9. График остатков – не активизировать.

10. График подбора – активизировать.

11. График нормальной вероятности – не активизировать (рис. 12). ОК.

Рис. 12. Окно «Регрессия» с заданными параметрами

В результате указанных действий осуществляется вывод в заданный диапазон рабочего листа четырех выходных таблиц 6, 7 и одного графика - рис. 13.

Таблица 6

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,912292
R-квадрат	0,832277
Нормированный R-квадрат	0,776369
Стандартная ошибка	6,228965
Наблюдения

Таблица 7

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		577,6	577,6	14,8866	0,030768
Остаток		116,4	38,8
Итого
	Коэффици-енты	Станда-ртная ошибка	t-стати-стика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	-6,8	6,532993	-1,04087	0,374445	-27,5909	13,99092	-27,5909	13,99092
Переменная X 1	7,6	1,969772	3,858315	0,030768	1,331302	13,8687	1,331302	13,8687

Таблица 8

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки
	0,8	3,2
	8,4	0,6
		-4
	23,6	-6,6
	31,2	6,8

Рис. 13. График уравнения регрессии

Интерпретация параметров инструмента Регрессия:

Множественный R – линейный коэффициент корреляции (r);

R-квадрат – коэффициент детерминации (R²);

Стандартная ошибка – среднее квадратическое отклонение расчетных значений от фактических ();

Наблюдени я – число наблюдений (n);

Df – число степеней свободы;

SS – сумма квадратов;

F – критерий Фишера;

МS – дисперсия факторная и остаточная ();

У-пересечение - свободный член регрессии (а₀);

Переменная Х 1 – коэффициент регрессии (а₁);

Коэффициенты – значения коэффициентов уравнения регрессии;

Нижние 95% и Верхние 95% - соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии, рассчитанные для уравнения надежности Р=0,95;

Нижние 68,3% и Верхние 68,3% - соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии, рассчитанные для уравнения надежности Р=0,683;

Пересеченное У – расчетные значения результативного признака (у`_i);

Остатки – отклонение расчетных значений от фактических (у_i – у`_i).

Дата добавления: 2015-07-08