Коэффициентов корреляции и линейной регрессии.

Построение прямой линейной регрессии

Получены данные о числе работников магазинов (величины x_i) и объем розничного товарооборота в млн. руб. (величины y_i):

x_i
y_i	0,5	0,7	0,9	1,1	1,4	1,4	1,7	1,9

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

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

Для выполнения этого задания проделайте следующие пункты.

1. Наберите исходные данные в два столбца, в заголовке которых наберите буквы X и Y, соответственно, в ячейки А1 и В1. Тогда данные займут диапазон А2:А9, а данные займут диапазон ячеек В2:В9.

2. Сначала вычислите основные корреляционные характеристики с помощью стандартных функций Excel. В ячейку А10 наберите: «m_х_y=» (используя для набора m шрифт Symbol и команду Формат®Ячейки®Шрифт®Нижний индекс для набора нижних индексов, предварительно выделив их). Содержание этой ячейки выровняйте по правому краю. В ячейку В10 наберите формулу для вычисления ковариации (4.1): =КОВАР(А2:А9; В2:В9) и нажмите Enter. Содержание этой ячейки выровняйте по левому краю. Должно получиться: m_х_y=10,475.

3. Вычислите коэффициент корреляции (4.3). Наберите в ячейку А11: «r=», выделите ее курсивом и выровняйте по правому краю. В ячейку В11 наберите формулу для вычисления коэффициента корреляции (4.3): =КОРРЕЛ(А2:А9; В2:В9) и нажмите Enter. Выровняйте содержание этой ячейки по левому краю. Должно получиться: r=0,9855.

4. Вычислите теперь эту же величину с помощью другой функции ПИРСОН в следующей строке. Оформление результата произведите так же, как и в пункте 3, обозначив коэффициент корреляции через r_p. Убедитесь, что результат действия функций ПИРСОН и КОРРЕЛ одинаковый.

5. Вычислите квадрат коэффициента корреляции в следующей строке с помощью функции КВПИРСОН. Оформление содержания этих ячеек произведите так же, как и в пункте 3. В результате выполнения в ячейках А13 и В13 должно получиться: r²=0,9712.

6. Поскольку величина коэффициента корреляции близка к единице, то есть основания предполагать наличие зависимости между величинами Х и Y. Вычислите коэффициент линейной регрессии Y на Х . В ячейку А14 наберите: « =». В ячейку В14 наберите формулу: =НАКЛОН(В2:В9; А2:А9). Содержание ячеек А14 и В14 оформите по аналогии с пунктом 3. Должно получиться: =0,0192. Коэффициент регрессии =0,019 показывает, что увеличение численности работников магазина на одного человека приводит к увеличению товарооборота в среднем на 19 тысяч рублей. Это своего рода эмпирический норматив приростной эффективности использования работников данной группы магазинов. Если увеличение численности на одного работника приводит к меньшему росту объема товарооборота, то прием его на работу необоснован.

7. Вычислите второй коэффициент регрессии Y на Х. В ячейку А15 наберите: « =». В ячейку В15 наберите формулу: =ОТРЕЗОК(В2:В9; А2:А9). Должно получиться: =-0,9739. Это означает, что уравнение линейной регрессии Y на Х имеет вид:

8. Теперь вычислите коэффициент линейной регрессии Х на Y. Для этого в ячейку А16 наберите: « =». В ячейку В16 наберите формулу для его вычисления: =НАКЛОН(А2:А9; В2:В9). Должно получиться: =50,482.

9. В ячейку А17 наберите: « =». В ячейку В17 наберите формулу: =ОТРЕЗОК(А2:А9; В2:В9). Должно получиться: =52,422. Это означает, что уравнение линейной регрессии Х на Y имеет вид:

10. Определите, какой объем розничного товарооборота в млн. руб. можно ожидать при увеличении числа работников магазина до 170. Для этого в ячейку А18 наберите: «Y(170)=». Затем в ячейку В18 наберите формулу: =ПРЕДСКАЗ(170; В2:В9; А2:А9) и нажмите Enter. В результате должно получиться: Y(170)=2,2966. Это означает, что при наличии в магазине 170 работников объем розничного товарооборота составит примерно 2,3 млн. руб.

11. Теперь постройте график линейной регрессии Y на Х с помощью Пакета анализа. Выполните команду Сервис®Анализ данных и выберите инструмент анализа Регрессия.

12. В появившемся диалоговом окне Регрессия:

· в поле Входной интервал Y: наберите: В2:В9 (или выделите этот интервал мышью, тогда его адрес появится в поле, где курсор);

· в поле Входной интервал Х: наберите: А2:А9 (или выделите его мышью);

· активизируйте переключатель Уровень надежности (по умолчанию установлено 95%);

· активизируйте переключатель Выходной интервал и в ставшее активным (белым) поле поместите адрес ячейки D3;

· активизируйте переключатель График подбора и нажмите ОК. Не сбрасывая выделение, сразу выполните команду Формат®Столбец®Автоподбор.

13. В результате вычислений появится несколько таблиц под названием Вывод итогов и график подбора. Первая таблица Регрессионная статистика должна иметь вид:

Регрессионная статистика
Множественный R	0,985475971
R-квадрат	0,971162888
Нормированный R-квадрат	0,966356703
Стандартная ошибка	0,089321148
Наблюдения

Она занимает интервал: D5:Е10. Убедитесь, что в ячейке Е5 содержится вычисление значения коэффициента корреляции, в ячейке Е6 – его квадрат, в ячейке Е10 – количество парных наблюдений.

14. Следующая таблица называется Дисперсионный анализ, она занимает диапазон D12:I16. Ее структуру мы не рассматриваем.

15. Следующая таблица занимает диапазон D18:L20 и содержит коэффициенты регрессии, их стандартные ошибки и границы доверительных интервалов. Фрагмент этой таблицы имеет вид:

	Коэффициенты	Нижние 95%	Верхние 95%
Y-пересечение	-0,973875115	-1,35597	-0,59178
Переменная X1	0,019237833	0,015926	0,022549

Строка Y-пересечение содержит характеристику коэффициента , а строка Переменная X1 содержит характеристику коэффициента регрессии . Убедитесь, что содержание ячейки Е19 совпадает с вычисленным ранее коэффициентом , а содержание ячейки Е20 – с вычисленным ранее . Доверительные интервалы (с 95% надежностью) для этих коэффициентов оказались следующими: 0,0159< <0,0225 и –1,3559< <-0,5917. Это означает, что можно указать статистическую погрешность коэффициентов: d_b= - , где - нижняя граница интервала, и d_r= - , где - нижняя граница соответствующего доверительного интервала. Вычислите их самостоятельно.

Результаты регрессионного анализа принято записывать в виде:

=0,0192

0,0033;

=-0,9739

0,3821. Погрешность для второго коэффициента

оказалась больше, чем погрешность для

. Однако величина достоверности аппроксимации, которая совпадает с квадратом коэффициента корреляции для линейной регрессии, близка к единице (r²=0,9711), что позволяет с достаточной степенью точности утверждать о хорошей согласованности теоретической зависимости (уравнение линейной регрессии) с наблюдаемыми данными.

16. Последняя таблица с результатами вычислений инструмента анализа Регрессия называется Вывод остатка и имеет вид:

Наблюдение	Предсказанное Y	Остатки
	0,430486685	0,069513315
	0,66134068	0,03865932
	0,988383838	-0,088383838
	1,238475666	-0,138475666
	1,373140496	0,026859504
	1,450091827	-0,050091827
	1,60399449	0,09600551
	1,854086318	0,045913682

Она занимает диапазон D24:F34. Столбец Предсказанное Y содержит вычисленные по полученной формуле значения y_i для каждого наблюдаемого значения . В столбце Остатки приведены разности между наблюдаемыми значениями из диапазона В2:В9 и рассчитанными по формуле линейной регрессии значениями из соседнего столбца Предсказанное Y.

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

18. Теперь постройте график линейной регрессии быстрым способом. Для этого выделите исходные данные (интервал ячеек А2:В9) и вызовите Мастер диаграмм. Выберите Тип диаграммы – Точечная и нажмите Готово.

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

20. В появившемся диалоговом окне Линия тренда: во вкладке Тип выберите Линейная; во вкладке Параметры активизируйте переключатели Показывать уравнения на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2), а затем нажмите ОК.

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

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

4.2. Построение линии параболической регрессии

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

Месяцы лактации (х_i)
Удой, ц (y_i)	18,2	20,1	23,4	24,6	25,6	25,9	23,6	22,7	19,2

Найти уравнение линии регрессии Y на Х.

Для выполнения этого задания проделайте следующие пункты.

1. Перейдите на следующий рабочий лист.

2. Наберите исходные данные либо в две строки, либо в два столбца, как удобно. Но обязательно, либо первая строка, либо первый столбец, должны содержать данные .

3. Выделите интервал ячеек с этими данными и вызовите Мастер диаграмм. Выберите Тип диаграммы – Точечная и нажмите Готово.

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

5. В появившемся диалоговом окне Линия тренда:

· во вкладке Тип выберите Полиномиальная и убедитесь, что установлена Степень: 2;

· во вкладке Параметры активизируйте переключатели Показывать уравнения на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2) и нажмите ОК.

6. В результате на диаграмме появится парабола, ближе всего к которой лежат экспериментальные точки, уравнение этой параболы и величина R²=0,9675. Поскольку величина R²достаточно близка к единице, то полученное уравнение параболической регрессии хорошо описывает экспериментальную зависимость.

7. Самостоятельно приведите график к следующему виду:

4.3. Построение линии степенной регрессии

В результате наблюдений получена зависимость двух величин:

x_i
y_i

Найти уравнение степенной регрессий Y на X и построить график этой зависимости и эмпирических точек.

Для выполнения этого задания проделайте следующие пункты.

1. Перейдите на следующий рабочий лист.

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

5. В появившемся диалоговом окне Линия тренда:

· во вкладке Тип выберите Степенная;

6. В результате на диаграмме появится кривая степенной регрессии, ее уравнение , величина R²=0,9221. Поскольку R² достаточно близко к единице, то полученное уравнение регрессии хорошо описывает экспериментальную зависимость.

7. Самостоятельно приведите график к следующему виду:

4.4. Построение линии гиперболической регрессии

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

Животные	Время удвоения массы новорождённых, дни	Содержание белков в молоке матери, %
Лошадь		2,4
Корова		2,6
Коза		3,3
Овца		4,8
Мул		5,6
Свинья		7,5

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

Для выполнения этого задания проделайте следующие пункты.

1. Перейдите на следующий рабочий лист.

2. Поскольку в Excel нет возможности быстрого построения с помощью стандартных процедур линии гиперболической регрессии, то для нахождения ее коэффициентов придется использовать формулы (4.16-4.17). Содержащиеся в этих формулах суммы удобнее всего вычислить с помощью вспомогательной расчетной таблицы.

3. Наберите в заголовки столбцов расчетной таблицы: в ячейку А1 – ; в ячейку В1 – ; в ячейку С1 – 1/ ; в ячейку D1 – / ; в ячейку Е1 – .

4. В интервал ячеек А2:А7 наберите данные о времени удвоения массы новорожденных; а в интервал ячеек В2:В7 наберите данные о содержании белков в молоке матерей.

5. В ячейку С2 наберите формулу: =1/А2 и распространите ее с помощью Маркера заполнения на интервал ячеек С2:С7.

6. В ячейку D2 наберите формулу: =В2*С2 (или =В2/А2, что тоже самое) и распространите ее на интервал ячеек D2:D7 с помощью Маркера заполнения.

7. В ячейку Е2 наберите формулу: =С2^2 (или 1/A2^2, что тоже самое) и распространите ее с помощью Маркера заполнения на интервал ячеек Е2:Е7.

8. Выделите ячейку А8 и нажмите на кнопку Автосумма на панели инструментов. Тогда в этой ячейке появится сумма чисел диапазона А2:А7, т.е. .

9. Распространите полученную формулу Маркером заполнения на ячейки диапазона А8:Е8. Тогда:

· в ячейке В8 будет сумма ;

· в ячейке С8 - ;

· в ячейке D8 - ;

· в ячейке Е8 - .

10. Поскольку именно эти суммы входят в формулы (4.17), то по ним легко найти величины D_а и D_b. Для этого в ячейку А9 наберите: D =; в ячейку А10 наберите: «D_а=»; в ячейку А11: «D_b=». (Греческую заглавную букву D - «дельта» можно набрать, если выбрать шрифт Symbol и нажать одновременно клавиши Shift и D.)

11. Теперь в ячейку В9 наберите формулу: =С8^2-6*Е8; в ячейку В10 наберите формулу: =С8*В8-6*D8; в ячейку В11 наберите формулу: =С8*D8-В8*Е8.

12. Теперь по формулам (4.16) осталось найти коэффициенты гиперболической регрессии. Для этого в ячейку С9 наберите: «а=»; в ячейку С10: «b=». Оформите содержание этих ячеек курсивом и выровняйте их по правому краю.

13. Далее, в ячейку D9 наберите формулу: =В10/В9; в ячейку D10 наберите формулу: =В11/В9. Должно получиться, что а=75,008;

b=1,512. Это означает, что уравнение гиперболической регрессии имеет вид: .

14. Чтобы построить график линии регрессии следует протабулировать полученную зависимость. Для этого в ячейку F1 наберите заголовок нового столбца: «Y_i». Он будет содержать вычисленные по полученной формуле гиперболической регрессии значения Y для каждого Х.

15. В ячейку F2 наберите формулу: =$D$9/A2+$D$10 и распространите ее с помощью Маркера заполнения на диапазон ячеек F2:F7.

16. Окончательный вид полученной расчетной таблицы:

x_i	y_i	1/x_i	y_i/x_i	1/x_i²	Y_i
	2,4	0,013	0,032	0,0002	2,512
	2,6	0,017	0,043	0,0003	2,762
	3,3	0,023	0,077	0,0005	3,256
	4,8	0,037	0,178	0,0014	4,29
	5,6	0,067	0,373	0,0044	6,512
	7,5	0,071	0,536	0,0051	6,869
	26,2	0,228	1,239	0,0119
D=	-0,019	a=	75,008
D_a=	-1,450	b=	1,512
D_b=	-0,029

17. Выделите мышью интервал ячеек А2:В7 и F2:F7, прижимая Ctrl. Затем вызовите Мастер диаграмм, в котором выберите Тип диаграммы – Точечная и нажмите Готово.

18. Отредактируйте диаграмму: удалите легенду, линии сетки, сделайте фон белым.

19. Щелкните мышью по любому розовому маркеру (они отмечают предсказанные значения Y_i). В появившемся диалоговом окне Формат ряда данных:

· в группе Линия активизируйте переключатели Обычная и Сглаженная линия;

· в группе Маркер активизируйте переключатель Отсутствует и нажмите ОК.

20. Самостоятельно добавьте надписи на осях и графике и приведите его к следующему виду: