4. Визначення коефіцієнтів кореляції і регресії

між кількісними ознаками для великої вибірки

Основні статистичні показники Х і Y визначимо за допомогою опції “Описова статистика” меню  . У випадку відсутності опції, потрібно підключити її, використовуючи меню рис.4.5 і в діалоговому вікні, що з'явилося, відзначитипункт Пакет анализа

Рис. 2.

Про засоби статистичного аналізу даних

Пакет аналізу. До складу Microsoft Excel входить набір засобів аналізу даних (так званий пакет аналізу), призначений для рішення складних статистичних і інженерних задач. Для проведення аналізу даних за допомогою цих інструментів варто указати вхідні дані і вибрати параметри; аналіз буде проведений за допомогою придатної статистичної чи інженерної макрофункції, а результат буде поміщений у вихідний діапазон. Інші засоби дозволяють подати результати аналізу в графічному вигляді.

Необхідні знання. Для успішного застосування процедур аналізу необхідні початкові знання в області статистичних і інженерних розрахунків, для яких ці інструменти були розроблені.

Описова статистика

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

Параметри діалогового вікна "Описательная статистика"(рис 4.6)

• Входной диапазон. Посилання на діапазон, що містить аналізовані дані. Посилання повинне складатися не менше, ніж із двох суміжних діапазонів даних, дані в яких розташовані в рядках чи в стовпцях.

Рис . 3

• Группирование. Встановіть перемикач у положенняПостовпцях чиПо рядкаху залежності від розташування даних у вхідному діапазоні.

• Метки в первой строке/Метки в первом столбце. Якщо перший рядок ви­хідного діапазону містить назви рядків або стовпців, установіть прапорецьМетки в первой строке. Якщо назви рядків знаходяться в першому стовпці вхідного діапазону, установіть прапорецьМетки в первом столбце. Якщо вхідний діапазон не містить міток, то необхідні заголовки у вихідному діапазоні будуть створені автоматично.

• Уровень надёжности.Встановіть прапорець, якщо у вихідну таблицю не­обхідно включити рядок для рівня надійності. У поле введіть необхідне значення. Наприклад, значення 95% обчислює рівень надійності середнього зі значимістю 0,05 (рис.4.).

Рис.4

• К-й наибольший. Встановіть прапорець, якщо у вихідну таблицю необхідно включити рядок для k-го найбільшого значення для кожного діапазону даних. У відповідному вікні введіть число k. Якщо k дорівнює 1, цей рядок буде містити максимум з набору даних.

• К-й наименьший. Встановіть прапорець, якщо у вихідну таблицю необ­хідно включити рядок для k-го найменшого значення для кожного діапазону даних. У відповідному вікні введіть число k. Якщо k дорівнює 1, цей рядок буде містити мінімум з набору даних.

• Выходной диапазон. Введіть посилання на ліву верхню чарунку вихідного діапазону. Цей інструмент аналізу виводить два стовпці зведень для кожного набору даних. Лівий стовпець містить мітки статистичних даних; правий стовпець містить статистичні дані. Діапазон статистичних даних, який складається із двох стовпців буде виведений для кожного стовпця чи для кожного рядка вхідного діапазону в залежності від положення перемикачаГруппирование.

• Новый лист. Встановіть прапорець, щоб відкрити новий лист у книзі і вставити результати аналізу, починаючи з чарунки A1. Якщо в цьому є необхідність, введіть ім'я нового листа в поле, розташоване напроти відповідного положення перемикача.

• Новая книга. Встановіть перемикач, щоб відкрити нову книгу і вставити результати аналізу в осередок A1 на першому листі в цій книзі.

• Итоговая статистика. Встановіть прапорець, якщо у вихідному діапазоні необхідно одержати по одному полю для кожного з наступних видів статистичних даних: Среднее, Стандартная ошибка (середнього), Медиана, Мода, Стандартное отклонение, Дисперсия виборки, Ексцес, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счёт, Наибольшее (#), Наименшее (#), Уровень надёжности.

Приклад 4.1.

Визначити тісноту зв’язку живої маси і висоту в холці свиноматок білої української степової породи за даними такої вибірки:

Х – жива маса, кг;

Y – висота в холці, см.

Дані задачі вводяться у вигляді масиву чисел, який складається з двох стовпців попарно (таблиця 4.1).

Рішення:

Коефіцієнт кореляціїзнаходимо за допомогою майстра функцій

КОРРЕЛ:

r=0,582926309.

Помилка коефіцієнта кореляції складе:

Розрахуємо критерій вірогідності розрахованого коефіцієнта кореляції:

Число ступенів свободи складе:

За таблицею 1 (див. додаток ) стандартні значення критерію t по Стьюденту при 98 ступенях свободи знаходимо t_st =2,0; 2,6; 3,4, що відповідає рівням імовірності: Р= 0,95; Р= 0,99; Р= 0,999.

Таблиця 4.1

Х	Y		X	Y		X	Y
200	78		229	80		208	78
248	81		263	81		214	80
251	85		252	80		231	80
233	79		213	79		249	80
229	80		205	80		318	82
228	80		192	70		263	81
226	80		234	80		226	72
291	83		229	80		228	79
225	75		227	80		210	74
243	80		230	80		229	81
190	75		285	85		257	86
187	75		245	80		266	80
228	80		213	79		235	80
253	82		235	82		236	78
226	80		223	75		236	79
265	82		228	80		225	80
230	81		204	75		212	79
210	79		213	73		222	80
227	78		221	78		225	80
208	77		213	79		238	80
206	91		186	75		261	80
237	80		225	72		213	74
225	81		279	85		232	78
213	82		228	72		246	81
221	80		212	78		240	80
204	84		272	90		213	80
250	82		267	80		251	82
258	85		213	72		235	80
199	77		250	80		260	80
210	78		239	80		235	80
278	88		183	76		235	80
242	82		246	80		213	79
210	78		230	82		273	86
260	86

Порівнюючи розрахований t_r з табличним, бачимо, що його значення більше табличної величини. Це означає, що коефіцієнт кореляції достовірний з імовірністю Р > 0,999. Тобто, якби дане дослідження повторили 1000 разів, то в 999 випадках спостерігалася б ця ж закономірність (зі збільшенням висоти збільшується жива маса) і лише в одному випадку могли одержати дані якогось іншого характеру. З урахуванням цих даних можна зробити висновок: зв’язок між живою масою свиноматок і висотою в холці позитивний, близький до тісного.

	Стовпець Х	Стовпець Y
1	2	3
Середнє	232,3	79,71
Стандартна помилка	2,4118185	0,356553
Медіана	229	80
Мода	213	80
Стандартне відхилення	24,118185	3,565534
Дисперсія вибірки	581,68686	12,71303
Ексцес	0,8998062	1,637441
Асиметричність	0,6315133	0,110604
Інтервал	135	21
Мінімум	183	70
Максимум	318	91
Сума	23230	7971
Рахунок	100	100
Найбільший	318	91
Найменший	183	70
Рівень надійності (95,0%)	4,785572093	0,70747936

Коефіцієнт регресії розраховується за формулами:

;

де _х і _y - середнє квадратичне відхилення (стандартне відхилення) відповідне X чи Y.

Висновок: При зміненні висоти свиноматок на 1 см їх жива маса зміниться в середньому на 3,94 кг, а при зміни живої маси свиноматок на 1 кг їх висота зміниться в середньому на 0,09 см.