Лабораторная работа 14. Обработка и оценка результатов исследования

Лабораторная работа 14. Обработка и оценка результатов исследования

Цель работы – научиться использовать возможности MS Excel для проведения корреляционного и регрессионного анализа данных.

Порядок выполнения работы

1 Выполнить задание 1 в соответствии с номером варианта.

Задание 1

По выборочным данным вычислить линейный коэффициент корреляции тремя способами:

по формуле (1); с помощью стандартной функции КОРРЕЛ; с помощью инструмента «Корреляция» из Пакета анализа Excel.

Сделать вывод о степени тесноты связи между факторным и результативным признаками. Оценить значимость линейного коэффициента корреляции с помощью t-критерия Стьюдента с доверительной вероятностью 0,95 (α=0.05). Найти вид уравнения линейной регрессии y=ax+b разными способами:

по формулам (2); графическим способом, построив линию тренда на диаграмме с показом уравнения регрессии; использовать встроенную функцию ЛИНЕЙН; использовать встроенные функции НАКЛОН (вычисляет коэффициент a) и ОТРЕЗОК (вычисляет коэффициент b); использовать инструмент «Регрессия» из Пакета анализа.

Краткие теоретические сведения

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

Корреляционный анализ состоит в определении степени связи между двумя случайными величинами X и Y. В качестве меры такой связи используется коэффициент корреляции. Коэффициент корреляции оценивается по выборке объема n связанных пар наблюдений (xi, yi) из совместной генеральной совокупности X и Y. Существует несколько типов коэффициентов корреляции, применение которых зависит от измерения (способа шкалирования) величин X и Y.

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

  (1)

где х – наблюдаемые значения выборки X,

  y – наблюдаемые значения выборки Y;

Коэффициент корреляции изменяется от –1 до 1. Знак коэффициента корреляции важен для интерпретации полученной связи (прямая или обратная связь).

Цель регрессионного анализа – определить количественные связи между зависимыми случайными величинами. Одна из этих величин полагается зависимой и называется откликом, другие – независимые, называются факторами. Для установления степени зависимости между откликом и факторами используется коэффициент корреляции.

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

В случае парной регрессии уравнение линейной регрессии имеет вид Y = a+bX, где

                                       (2)

В среде MS Excel для нахождения модели регрессии (т. е. коэффициентов a и b) можно использовать несколько способов:

использовать встроенную функцию ЛИНЕЙН; графический способ – построение линии тренда на диаграмме с показом уравнения регрессии; инструмент Регрессия из Пакета анализа; использовать встроенные функции НАКЛОН (вычисляет коэффициент a) и ОТРЕЗОК (вычисляет коэффициент b).

Пример.  По 10 Интернет-магазинам были определены затраты на рекламную раскрутку сайтов и количество покупателей, воспользовавшихся после ее проведения услугами каждого магазина. Определить коэффициент корреляции между исследуемыми признаками. Оценить значимость линейного коэффициента корреляции с помощью t-критерия Стьюдента с доверительной вероятностью 0,95 (α=0.05). Построить уравнение регрессии.

Ход выполнения задания

Создадим таблицу согласно рис. 1.

Рис. 1

Рассчитаем коэффициент корреляции, используя формулу (1), функцию КОРРЕЛ из категории Статистические, инструмент Корреляция из Пакета анализа Excel (рис. 2).

Рис. 2

Общий вид функцииКОРЕЛЛ:

КОРРЕЛ (<массив 1>;<массив 2>),

где

<массив 1> – ссылка на диапазон ячеек первой выборки (X);

<массив 2> – ссылка на диапазон ячеек второй выборки (Y).

Вычислим значение t-критерия Стьюдента. Определим табличное значение t-критерия Стьюдента, используя стандартную функцию СТЬЮДЕНТ. ОБР.2Х.

Поскольку вычисленное значение оказалось больше критического, с вероятностью 0,95 можно говорить о тесной связи между исследуемыми признаками.

Так как значение коэффициента корреляции близко к 1, можно использовать линейную модель парной регрессии Y = a+bX.

Определим значения коэффициентов a и b разными способами (рис. 3).

Рис. 3

5.1. В ячейках H3 и I3 выполнен расчет коэффициентов по формулам (2).

5.2. В ячейках H4 и I4 выполнен расчет коэффициентов с помощью функции ЛИНЕЙН. Для ввода формул нужно выделить обе ячейки, выбрать функцию ЛИНЕЙН из категории Статистические, указать аргументы функции (столбцы значений X и Y), закрыть окно ввода аргументов нажатием клавиш SHIFT+CTRL+ENTER. Таким образом формула вводится как формула массива.

5.3. В ячейках H5 и I5 выполнен расчет коэффициентов с помощью функций ОТРЕЗОК и НАКЛОН. Изучите синтаксис функций с помощью справочной системы.

5.4.  Для получения уравнения регрессии графическим методом построим корреляционное поле переменных X (затраты на продвижение) и Y (количество покупателей).

Выделим диапазон ячеек В2:С11, запустим мастер диаграмм и выберем тип диаграммы – Точечная. Добавим линию тренда на точечный график. Для этого необходимо открыть контекстное меню щелчком правой кнопки мыши по любому из маркеров точечной диаграммы и выбрать пункт «Добавить линию тренда».  Линия тренда – графическое представление направления изменения ряда данных. Выбираем тип тренда «Линейный», который используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением: Y = a+ bX, где a – угол наклона (в радианах) и b – координата пересечения оси  Y. На вкладке Параметры устанавливаем флажки «Показать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации R2». Щелкаем по кнопке ОК.

R2 – это число от 0 до 1, которое отражает близость линии тренда к фактическим данным. Линия тренда наиболее соответствует действительности, когда значение близко к 1.

5.5. Используем инструмент «Регрессия» из Пакета анализа. Диалоговое окно с заданием параметров применения инструмента показано на рис. 4.

Рис. 4

Результаты выводятся в виде таблицы (рис. 5). Коэффициенты уравнения регрессии выделены на рисунке жирным шрифтом.

Рис. 5

Варианты заданий

Задание 2

Изучить основные формулы и трактовку результатов вычисления по формулам, используя краткие теоретические сведения. Рассмотреть пример оценивания тесноты связи между признаками на основе эмпирического корреляционного отношения. Выполнить задание для самостоятельного выполнения.

Краткие теоретические сведения

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

Общая дисперсия измеряет вариацию признака по всей совокупности под влиянием всех факторов, обусловивших эту вариацию. Она определяется по формулам как простая или взвешенная дисперсия:

= ,  = ,                 

где - общая средняя для всей изучаемой совокупности.

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

= ,         = ,

где – численность отдельных групп; – средняя в группах.

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

= ,  = . 

Средняя из внутригрупповых дисперсий определяется на основании внутригрупповых дисперсий по каждой группе:

=  

Общая дисперсия равна сумме величин межгрупповой дисперсии и средней из внутригрупповой дисперсии:

= + . 

Это правило имеет большую практическую значимость, так как позволяет выявить зависимость результатов от определяющих факторов.

Чем больше доля межгрупповой дисперсии в общей дисперсии, тем сильнее влияние группировочного признака на изучаемый признак.

Поэтому в статистическом анализе широко используется такой показатель, как эмпирический коэффициент детерминации:

з2 = . 

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

Эмпирический коэффициент детерминации изменяется в пределах 0 ≤ ≤ 1.

Эмпирическое корреляционное отношение показывает тесноту связи между группировочным и результативным признаками:

з =  

Эмпирическое корреляционное отношение также изменяется в пределах 0 ≤ ≤ 1.

Для качественной оценки тесноты связи на основе показателя эмпирического корреляционного отношения можно воспользоваться соотношениями Чэддока (табл. 1).

Таблица 1

Соотношения Чэддока

|з|	0	0,1-0,3	0,3-0,5	0,5-0,7	0,7-0,9	0,9-0,99	1
Сила связи	Отсутствует	Слабая	Умеренная	Заметная	Тесная	Весьма тесная	Функциональная

Пример выполнения задания

Задание. По данным таблиц, показанных на рис. 6, сделать вывод о наличии связи между прохождением рабочими повышения квалификации и их выработкой. Вывод сделать на основе анализа значения эмпирического корреляционного отношения. При вычислении основных статистических характеристик использовать стандартные функции Excel.

Порядок выполнения задания

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

Рис. 6

Ход решения

Вычислим средние по каждой группе (по каждой таблице) и среднюю по всей совокупности значений.

Средняя выработка рабочих, не прошедших повышение квалификации, составляет 45,48 шт./смену. Расчёт выполняется с помощью функции СРЗНАЧ:

=СРЗНАЧ(B2:AO2).

Аналогично вычисляется Средняя выработка рабочих, прошедших повышение квалификации, она составляет 53,13 шт./смену.

Средняя выработка рабочих цеха составляет 50,07 шт./смену (=СРЗНАЧ(B2:AO2;B5:BI5)).

Определим дисперсии с помощью стандартных функций Microsoft Excel, используя функцию ДИСП. Г.

Дисперсия выработки рабочих, не прошедших повышение квалификации, составляет 10,00 (=ДИСП. Г(B2:AO2)).

Дисперсия выработки рабочих, прошедших повышение квалификации, составляет 24,92.

Общая дисперсия выработки всех рабочих равна 33,03.

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

= = 18,95 .

Определим межгрупповую дисперсию:

= = 14,08 .

Используя закон сложения дисперсий, проверим правильность расчетов:

= 14,08 + 18,95 = 33,03.

Найдём эмпирический коэффициент детерминации:

= 14,08 / 33,03 = 0,43.

Рассчитаем эмпирическое корреляционное отношение:

з = = 0,65.

Выводы

Средняя выработка рабочих,  не прошедших повышение квалификации, составляет 45,48 шт./см, прошедших повышение квалификации – 53,13 шт./см. Средняя выработка рабочих цеха равна 50,07 шт./см.

Доля вариации выработки рабочего, которая обусловлена прохождением рабочими повышения квалификации, равна 43 %.

Эмпирическое корреляционное отношение равно 0,65. Следовательно, связь между выработкой и прохождением рабочими повышения квалификации заметная.

Задание для самостоятельного выполнения

В таблице 2 представлены данные о размере среднедушевого месячного дохода для областей трёх округов РФ. Используя  эмпирическое корреляционное отношение, оценить тесноты связи между величиной среднедушевого дохода по области и принадлежностью области к тому или иному федеральному округу.

В расчётах использовать стандартные функции Excel (СЧЁТ, СРЗНАЧ, ДИСП. Г), а также – известные математические соотношения.

Таблица 2

Округа и области	Среднедушевые месячные доходы в 2011 г.
Северо-Западный федеральный округ:
Ленинградская область	15931,8
Мурманская область	25303,5
Новгородская область	16980,9
Псковская область	14185,3
г. Санкт-Петербург	25994,7
Южный федеральный округ:
Республика Калмыкия	8829,4
Краснодарский край	18796,0
Астраханская область	16032,0
Волгоградская область	14519,4
Ростовская область	16010,3
Дальневосточный федеральный округ:
Камчатский край	28964,9
Приморский край	19159,7
Хабаровский край	23766,2
Амурская область	17789,7
Сахалинская область	32268,2
Еврейская автономная область	16525,3
Чукотский автономный округ	43049,4

Порядок выполнения задания

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

В результате расчётов должны получиться следующие показатели:

Общая дисперсия  = 64339272,5

Межгрупповая дисперсия = 21669589,6

Коэффициент детерминации = 0,336802

Эмпирическое корреляционное отношение = 0,580346