Анализ статистических данных в MSExcel. Инструменты анализа: описательная статистика, корреляция.

В состав электронных таблиц MicrosoftExcel входит так называемый пакет анализа – набор инструментов, предназначенный для решения сложных статистических задач. Данный пакет производит анализ статистических данных с помощью макрофункций и позволяет, выполнив одно действие, получить на выходе большое количество результатов. В пакете анализа, имеющемся в Excel, среди прочих инструментов анализа имеется разделы «Описательная статистика» и «Корреляция».

Инструмент «Описательная статистика» позволяет нам получить значительный перечень рассчитанных статистических характеристик для большого количества числовых рядов. С помощью инструмента «Корреляция» мы получаем корреляционную матрицу, содержащую все возможные парные коэффициенты корреляции. Для k рядов будет получено k (k – 1)/2 коэффициентов корреляции.

Пакет анализа вызывается с помощью пункта меню Сервис – Анализ данных… Если этот пункт меню отсутствует, значит, пакет анализа не установлен. Для его установки надо вызвать пункт меню Сервис – Надстройки… и включить надстройку «Пакет анализа», ОК (см. рисунок 1).

 

 

Рисунок 1. Диалоговое окно включения/выключения надстроек

 

После включения надстройки «Пакет анализа» будет доступен пункт меню Сервис – Анализ данных… При его выборе появляется следующее диалоговое окно (рисунок 2).

 

 

Рисунок 2. Диалоговое окно выбора инструмента для анализа данных

 

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

 

 

Рисунок 3. Диалоговое окно инструмента «Описательная статистика»

 

Рассмотрим работу инструмента анализа «Описательная статистика» на следующем примере. В процессе обследования группы школьников (n = 21) измерялись следующие показатели: рост, масса тела, динамометрия правой и левой руки, жизненная емкость легких, проба Штанге и проба Генчи. Результаты были занесены в таблицу (рисунок 4).

Для получения статистических характеристик воспользуемся пакетом анализа, инструментом «Описательная статистика». В поле «Входной интервал» занесем диапазон ячеек В1:Н22. Так как выделенный входной интервал содержит заголовки столбцов, включаем флажок «Метки в первой строке». Для удобства работы в качестве места выхода результата выбираем «Новый рабочий лист». В качестве выводимых данных отметим флажками «Итоговая статистика» и «Уровень надежности: 95 %». Последний флажок позволит вывести параметры доверительного интервала с доверительной вероятностью 0,95. Полученный результат после небольшого форматирования будет выглядеть так, как показано на рисунке 5.

 

Рисунок 4. Результаты обследования группы школьников

 

 

Рисунок 5. Результат работы инструмента «Описательная статистика»

 

После выбора инструмента «Корреляция» и нажатия ОК в диалоговом окне «Анализ данных» (рисунки 2, 6) появится еще одно диалоговое окно (рисунок 7), требующее ввода входных данных и места вывода результатов. Здесь достаточно в поле «Входной интервал» ввести диапазон ячеек, содержащих исходные данные. Можно указать диапазон с заголовками столбцов, в этом случае потребуется включить флажок «Метки в первой строке». Для указания выходного интервала достаточо указать только левую верхнюю ячейку диапазона. Результаты вычисления автоматически займут требуемое количество строк и столбцов в таблице.

 

 

Рисунок 6. Диалоговое окно выбора инструмента для анализа данных

 

 

 

Рисунок 7. Диалоговое окно инструмента «Корреляция»

 

Рассмотрим работу инструмента анализа «Корреляция» на примере, представленном на рисунке 4.

Для получения корреляционной матрицы воспользуемся пакетом анализа, инструментом «Корреляция». В поле «Входной интервал» занесем диапазон ячеек В1:Н22. Так как выделенный входной интервал содержит заголовки столбцов, включаем флажок «Метки в первой строке». Для удобства работы в качестве места выхода результата выбираем «Новый рабочий лист». Полученный результат после небольшого форматирования будет выглядеть так, как показано на рисунке 8.

 

 

Рисунок 8. Корреляционная матрица

 

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

 

Основные понятия корреляционного анализа экспериментальных данных. Оценка коэффициента корреляции по экспериментальным данным.

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

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

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

Среди статистических взаимосвязей наиболее важны корреляционные. Корреляция – это статистическая зависимость между случайными величинами, при которой изменение одной из случайных величин приводит к изменению математического ожидания (среднего значения) другой. Например, толкание ядра 3 кг и 5 кг. Улучшение результатов толкания ядра 3 кг вызывает улучшение (в среднем) результата в толкании ядра весом 5 кг.

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

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

Для оценки взаимосвязи, когда измерения производят в шкале отношений или интервалов и форма взаимосвязи линейная, используется коэффициент корреляции Бравэ-Пирсона (коэффициенты корреляции для других шкал измерения в данном пособии не рассматриваются). Обозначается он латинской буквой – r. Вычисление значения r чаще всего производят по формуле:

,

где и – средние арифметические значения показателей x и y, и – средние квадратические отклонения, n – число измерений (испытуемых).

В некоторых случаях тесноту взаимосвязи определяют на основании коэффициента детерминации D, который вычисляется по формуле:

.

Этот коэффициент определяет часть общей вариации одного показателя, которая объясняется вариацией другого показателя. Например, коэффициент корреляции r = –0,677 (между результатами в беге на 30 м с ходу и тройном прыжке с места). Коэффициент детерминации равен:

.

Следовательно, 45,8 % рассеяния спортивного результата в тройном прыжке объясняется изменением результатов в беге на 30 м. Иными словами, на оба исследуемых признака действуют общие факторы, вызывающие варьирование этих признаков, и доля общих факторов составляет 45,8%. Остальные 100% – 45,8% = 54,2% приходятся на долю факторов, действующих на исследуемые признаки избирательно.

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

1. Выдвигаются статистические гипотезы. Гипотеза Н₀ предполагает отсутствие статистически значимой взаимосвязи между исследуемыми показателями (r_ген =0). Гипотеза Н₁ предполагает, что существует статистически достоверная взаимосвязь между показателями (r_ген >0).

2. Рассчитывается наблюдаемое значение коэффициента корреляции r_набл.

3. Находится по таблице критическое значение коэффициента корреляции r_крит в зависимости от объема выборки n, уровня значимости a и вида критической области (односторонняя или двусторонняя).

3. Сравнивается r_набл и r_крит.

Если r_набл < r_крит – статистически недостоверным (незначимым). Принимается гипотеза Н₀ Если r_набл ≥ r_крит, коэффициент корреляции считается статистически достоверным (значимым). Принимается гипотеза Н₁.