ЛАБОРАТОРНАЯ РАБОТА № 8 Парный корреляционный анализ
Постановка задачи
На аукционе 1972 г. (США) для оптовой продажи выставлялись марочные вина, срок хранения которых характеризуется годами закладок. Для каждого года закладки даны сложившиеся оптовые цены (в долларах за бутылку). Исходные данные приведены в табл. 8.1.
Таблица 8.1
Годы
|
1890
|
1900
|
1920
|
1931
|
1934
|
1935
|
1940
|
1944
|
1948
|
1950
|
1955
|
1960
|
Цена
|
50
|
34,9
|
25
|
12
|
15
|
13
|
6,98
|
5,99
|
8,98
|
6,98
|
5,98
|
4,98
|
От годов закладки целесообразно перейти к сроку хранения, то есть выдержке. Так, например, вино закладки 1890 г. имело в 1972 г. выдержку 1972 – 1890 = 82 года. Аукционные цены, хотя и складывается случайным образом, но отражают общую тенденцию повышения качества вина в зависимости от его выдержки.
Требуется по данным аукциона установить статистическую зависимость цены на марочные вина (Y) от выдержки (X) и оценить тесноту связи. Построить диаграмму рассеяния.
Расчет выборочного коэффициента корреляции
1. Объедините ячейки диапазона А1:М1 и поместите в центре заголовок «Годы закладки и цены (за бутылку в долларах) – аукцион 1972 г.», показано на рис. 8.1.
Рис. 8.1
В диапазон ячеек А2:МЗ введите таблицу исходных данных в две строки. Установите разрядность для цен (диапазон ВЗ:МЗ) до двух знаков после запятой.
Для проведения расчетов скопируйте исходную матрицу в транспонированную. Для этого выделите диапазон числовых исходных данных В2:МЗ и щелкните на кнопке Копировать на Стандартной панели инструментов.
4. Выделите ячейку А7, определяющую левый верхний угол диапазона вывода транспонированной матрицы, и выберите команду: Правка→Специальная вставка...
В открывшемся диалоговом окне установите флажок транспонировать и щелкните на кнопке ОК.
Выделите диапазон цен В7:В18 и переместите этот столбец вправо в диапазон С7:С18.
В освободившемся столбце рассчитайте сроки хранения вин (выдержки). Для этого в ячейку В7 введите формулу: =1972-А7.
А затем с помощью маркера заполнения скопируйте формулу в диапазон ячеек В8:В18.
Задайте имена исследуемых переменных. Для этого в ячейку В6 введите имя переменной «X», а в ячейку С6 - имя переменной «Y». Выровняйте диапазон ячеек В6:С18 по центру как показано на рисунке.
Выделите ячейку Е6 и введите заголовок «Коэффициент корреляции».
Выделите ячейку Е7 и вызовите мастер функций fx.
Из категории Статистические выберите функцию КОРРЕЛ.
В диалоговом окне Аргументы функции в поле Массив 1 задайте диапазон В7:В18 переменной X, а в поле Массив 2 задайте диапазон С7:С18 переменной Y. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до четвертого знака после запятой.
Сделайте выводы о тесноте связи по рассчитанному коэффициенту корреляции.
Оценка значимости выборочного коэффициента корреляции
13. Выделите ячейку Е9 и введите имя переменной n. Выровняйте по центру.
14. Выделите ячейку Е10 и задайте имя переменой n, выполнив команду: Вставка→Имя→Присвоить...
В ячейку Е9 введите объем выборки n=12.
Выделите ячейку F9 и введите заголовок «t-статистика».
Выделите ячейку F10 и введите формулу вычисления t-статистики:
=Е7*КОРЕНЬ(n-2)/КОРЕНЬ(1-Е7^2).
Прочтите результат, уменьшив разрядность до четвертого знака после запятой.
Выделите ячейку Е12 и введите заголовок «Критическое значение».
Выделите ячейку Е13 и вызовите мастер функций fx.
Из категории Статистические выберите функцию СТЬЮДРАСПОБР и в диалоговом окне в поле Вероятность задайте значение уровня значимости α равным 0,05, а в поле Степени_свободы – число степеней свободы, равное n-2. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до четвертого знака после запятой.
Сделайте выводы о значимости коэффициента корреляции.
Построение диаграммы рассеяния (корреляционного поля)
Щелкните на инструменте Мастер диаграмм на Стандартной панели инструментов и выберите тип диаграммы Точечная. Щелкните на кнопке Далее.
Выделите диапазон данных вместе с заголовками столбцов В6:С18 и щелкните на кнопке Далее.
На вкладке Заголовки введите:
Название диаграммы: Зависимость цены на марочные вина от выдержки
Ось X (категорий): Выдержка (в годах)
Ось Y (значений): Цена на вина (в долларах за бутылку)
Щелкните на кнопке Далее.
Установите переключатель Поместить диаграмму на листе в положение имеющемся. Щелкните на кнопке Готово.
Установите указатель мыши на любую точку ряда Y и щелкните правой кнопкой.
В контекстном меню выберите команду: Добавить линию тренда... и выберите тип Линейная. Щелкните на кнопке ОК.
Отформатируйте диаграмму и расположите ее как показано на рис. 36.
Какие выводы можно сделать по построенной диаграмме рассеяния?
|
