4.2. Комп'ютерна підтримка розрахунку коефіцієнту кореляції

Для проведення кореляційного аналізу необхідно ввести аналізовані дані з таблиці на аркуш Excel, згрупувати їх в рядки або стовпці і провести відповідну обробку шляхом:

а) проведення розрахунку за наведеними у табл.1, 2 формулами з використанням відповідних вбудованих статистичних функцій КОРРЕЛ або ПИРСОН, розрахунку t-критерія значущості коефіцієнту кореляції за допомогою вищенаведеної формули для t-критерія і порівняння отриманих результатів з табличними значеннями критерію Стьюдента, отриманими за допомогою функції СТЬЮДРАСПОБР для заданої похибки (найчастіше 0,05) і (n-2) степенів свободи. Обмеженням такого варіанту є можливість визначення кореляції лише попарно, між двома параметрами;

б) безпосереднього використання інструменту “Кореляція” пакету статистичного аналізу Аналіз даних. В цьому випадку можна визначати факт наявності і оцінювати силу множинної кореляції.

в) проведення кореляційного аналізу з використанням вбудованих статистичних функцій .

Приклад. При дослідженні залежності точності виготовлення деталі від діаметру зерна абразивну при ультразвуковій обробці отримані наступні значення (табл. 3.) відхилень розміру деталі і діаметра зерна абразивну від номінальних значень. Визначити наявність кореляції між цими параметрами.

Таблиця 3

Результати експериментального дослідження процесу УЗ обробки

Хі (діаметр зерна абразиву)	0,9	1,22	1,32	0,77	1,3	1,2	1,32	0,95	0,45
Уі (відхилення розміру деталі від номіналу)	-0,3	0,1	0,7	-0,28	0,25	0,02	0,37	-0,7	0,55
Хі (діаметр зерна абразиву)	1,3	1,2	0,96	1,12	0,64	1,28	1,12	1,14	0,5
Уі (відхилення розміру деталі від номіналу)	0,35	0,32	-0,65	-0,2	0,02	0,3	-0,11	-0,1	0,22

Обробка результатів експерименту. Дані заносимо на аркуш Excel (рис. 3, табл. 1) і за алгоритмом побудови діаграми розсіювання будуємо її (діаграма 1 або 2, в залежності від вибраного типу). Як видно з діаграми розсіювання є дві області з кореляцією різного типу, тобто бажаною є стратифікація. Для коректного визначення інтервалів транспонуємо дані табл. 1 (рис.3) і проводимо сортування за значеннями Х (рис.3, табл. 2). Інтервали ( діаметр зерна абразивну від 0,45 до 0,96 і від 1,12 до 1,32 ) стають очевидними. Для кожного з цих інтервалів визначаємо коефіцієнт кореляції або шляхом використання вбудованої функції КОРРЕЛ або ПИРСОН з відповідним заданням діапазонів, або шляхом використання інструменту “Кореляція” пакету Аналіз даних.

При розрахунку коефіцієнта кореляції за допомогою вбудованих функцій активація потрібної функції проводиться її вибором (позиція КОРРЕЛ або ПИРСОН) в підменю Статистичні функції меню Майстра функцій. Обидві ці функції повертають значення коефіцієнта лінійної кореляції у комірках вибраного діапазону, розраховані за формулами, що співпадають з формулами п. 6 таблиці 1 . Синтаксис функції КОРРЕЛ: КОРРЕЛ(масив1; масив2) може бути введений і з клавіатури; при цьому Масив1 - це комірки інтервалу значень одної з аналізованих змінних; Масив2 - це другий інтервал комірок зі значеннями другої змінної.

Для виділення діапазону значень кожної з змінних після вибору функції КОРРЕЛ достатньо або ввести ці діапазони вручну у поля Массив1 і Массив2, як адреси крайньої лівої верхньої і крайньої правої нижньої комірки, розділені двокрапкою, або, послідовно натискаючи на кнопку вибору діапазону в куті кожного з полів, відмітити потрібні діапазони мишею. Так, якщо активувати комірку E61, вибрати функцію КОРРЕЛ, відмітити діапазон B43:B49 для поля Массив1 і C43:C49 для поля Массив2 і клацнути мишею ОК, то в E61 отримаємо коефіцієнт кореляції між значеннями в стовпцях B43:B49 і B43:B49, що відповідатиме коефіцієнту кореляції для експериментальних даних у першому з стратифікованих діапазонів. Аналогічно в комірці Е65 розраховуємо коефіцієнт кореляції для експериментальних даних у другому з стратифікованих діапазонів.

Результати розрахунку наведені на рис. 3, формули вбудованих функції розшифровані поруч з відповідними комірками. Далі за алгоритмом перевірки коефіцієнтів кореляції на значущість розраховуємо значення t-критерія Стьюдента за наведеними поруч з відповідними комірками формулами і порівнюємо його з табличним значенням для обраної імовірності, який розраховуємо за допомогою функції СТЬДАРАСПОБР для обраної імовірності, в нашому випадку 0,05. Для розрахунку табличне значення t-критерія для n-2 ступеней свободи активуємо у потрібній комірці вбудовану функцію СТЬДАРАСПОБР з підменю Статистичні функції меню Майстер функцій, яка повертає критичне значення t-критерія для визначених похибки (поле Альфа діалогового вікна функції), числа степеней свободи  (поле Степені свободи) і типу розподілу (поле Хвости, цифра 2 в ньому відповідає двобічному розподілу). Число степеней свободи n-2 розраховується з використанням вбудованої функції СЧЕТ, яка повертає кількість непорожніх комірок у вибраному діапазоні. Остаточний синтаксис функції СТЬЮДАРАСПОБР для нашого випадку наведений на рис. 3, поруч з відповідною коміркою. Порівняння розрахункових і табличних значень критеріїв Стьюдента дозволяє стверджувати, що в обох інтервалах кореляція є значущою: у першому інтервалі вона є від'ємною, в другому додатною. Фактично, виходячи з фізичного змісту задачі, це означає, що існує оптимальний діаметр зерна в діапазоні 0,9-1,2, при якому відхилення розміру деталі від номіналу є мінімальним. Відхилення розміру зерна абразивну в меншу сторону призводить до збільшення відхилення розміру деталі від номіналу в від'ємну область, в більшу – відповідно в більшу.

Б. Проведення кореляційного аналізу за допомогою пакету статистичного аналізу даних Аналіз даних.

Для проведення кореляційного аналізу необхідно ввести аналізовані дані на аркуш Excel, згрупувати їх в рядки або стовпці і запустити відповідний інструмент пакету Аналіз даних, процедура проведення якого співпадає з вибраною нами. Інструмент Кореляція є саме таким інструментом. Його застосування має декілька кроків:

1. відкриття меню інструментів пакету Аналіз даних шляхом вибору цієї позиції з меню Сервіс;

2. вибір з меню Аналіз даних позиції Кореляція;

3. виконання всіх кроків, передбачених роботою з відповідним діалоговим вікном. Це вікно має наступні поля, які слід заповнити:

4. поле Вхідний діапазон, в яке вводять посилання на комірки, що містять аналізовані дані (посилання мусить складатися не менш ніж з двох суміжних діапазонів даних, представлених рядками або стовпцями);

5. поле Вихідний діапазон, в яке вводять посилання на комірку, розташовану у лівому верхньому куті вихідного діапазону, тобто області, в якій бажано розташувати результати розрахунку (розміри вихідної області будуть розраховані автоматично, і відповідне повідомлення з'явиться на екрані в тому випадку, якщо вихідний діапазон займає місце даних, що вже існують, або його розміри перевищують розміри аркуша);

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

7. поле Нова книга, яке дозволяє вивести результати розрахунку в нову книгу. Працює так само, як і попередній;

8. поле Групування, яке фіксує положення даних у вихідному діапазоні. Перемикач встановлюють в положення "по стовпцях" або "по рядках" в залежності від розташування даних у вхідному діапазоні;

9. поле Мітки в першому рядку/ Мітки в першому стовпці, яке дозволяє вивести назви факторів, розташовані в наголовках таблиці, в таблицю результатів дисперсійного аналізу. Перемикач встановлюють в положення " Мітки в першому рядку ", якщо перший рядок у вхідному діапазоні містить назви стовпців. Встановити перемикач в положення "Мітки в першому стовпці", якщо назви рядків знаходяться в першому стовпці вхідного діапазону. Якщо вхідний діапазон не містить міток, то необхідні наголовки у вихідному діапазоні будуть створені автоматично у вигляді “Строка 1…п” або “Столбец 1… п;

10. поле Альфа, в яке ввести рівень значущості, необхідний для оцінки критичних параметрів F-статистики.