Комп'ютерна підтримка кореляційного аналізу

Комп'ютерну підтримку кореляційного аналізу в межах пакету Excel можна ефективно здійснювати як за допомогою відповідних інструментів вбудованого пакету статистичного аналізу даних, так і шляхом застосування відповідних вбудованих функцій. В першому випадку менш жорсткими є вимоги по представленню вихідної інформації, в другому – суттєво більш економічним є сам процес обробки даних. Розглянемо використання вбудованих функцій і пакету статистичного аналізу на прикладах.

4.1. Комп'ютерна підтримка побудови кореляційного поля

(діаграми розсіювання)

Для побудови діаграми розсіювання за допомогою пакету Excel достатньо ввести результати досліджень у таблицю таким чином, щоб, параметри, між якими досліджується кореляція, були розташовані у суміжних стовпцях або рядках. Створення діаграми виконується наступним чином:

1) виділяють комірки, які містять дані, що підлягають відбиттю на діаграмі. Якщо в діаграмі бажано відбити назви стовпців або рядків, то їх також виділяють;

2) натискають кнопку “Мастер диаграмм”

3) виконують послідовність кроків згідно до інструкцій Майстра. Застосування Майстра діаграм дозволяє навіть непідготовленому користувачеві отримати бажаний результат майже без зусиль. Використання Майстра задає стандартну послідовність побудови діаграми з 4-х кроків:

а) вибір типу діаграми;

б)визначення джерела діаграми, тобто діапазону даних, які відбиваються у вигляді діаграми;

в) визначення параметрів діаграми;

г) визначення місцерозташування діаграми.

4) При натисканні кнопки “Мастер диаграмм” на головній панелі інструментів розкривається вікно зі списком типів діаграм, які можуть бути побудовані на основі досліджуваних даних. При виставленні курсору на певну позицію списку в правій частині вікна висвітлюються варіанти діаграми цього типу у вигляді відповідних малюнків-взірців; при виведенні курсору в область взірцевого малюнку в нижній частині вікна з'являється підпис, що роз'яснює тип діаграми на малюнку. Оскільки діаграма розсіювання є точковою діаграмою, то при її побудові обирають позицію списку типів діаграм “Точечная” (точкова) із переліку Стандартних діаграм (до нестандартних відноситься, наприклад, діаграма, в якій частина даних виведена як графік, а частина – як гістограма). Цій позиції відповідають 5 підтипів діаграм: а) точкова, на якій дані представлені точками-маркерами; б) точкова з даними - маркерами, поєднаними згладжуючими лініями; в) точкова з даними, поєднаними згладжуючими лініями, на якій позначки точкових даних у вигляді маркерів відсутні; г) точкова з даними - маркерами, поєднаними відрізками прямих ліній; д) точкова з даними, поєднаними відрізками прямих ліній, на якій позначки точкових даних у вигляді маркерів відсутні. При побудові діаграми розсіювання найдоцільніше вибрати варіант а) (класична точкова діаграма) або б) (точкова з поєднанням даних згладжуючими лініями). Остаточний вибір типу діаграми здійснюють натисканням на вікно взірця обраного типу з подальшим натисканням кнопки “Далее”. Після цього відбувається перехід на другий крок побудови діаграми за допомогою Майстра з відкриванням на екрані вікна, що відповідає кроку 2;

5) на кроці 2 остаточно визначають діапазон даних, що виводяться на графік і розташування даних (у стовпцях чи рядках) шляхом заповнення відповідних полів вікна кроку 2 (діапазон; ряди; розташування). В верхній частині вікна виводиться перегляд діаграми, що будується, що значно спрощує корегування. Якщо попередньо діапазон даних був відмічений вірно, то на цьому кроку достатньо лише підтвердити розташування даних і натиснути кнопку “Далее”, що еквівалентно переходу до третього кроку побудови діаграми за допомогою Майстра з відкриванням на екрані вікна, що відповідає кроку 3;

6) на кроку 3 (“Параметри діаграми”) задають параметри діаграми, що остаточно визначать її зовнішній вигляд. До цих параметрів відносяться: наголовки (самої діаграми, окремих осей – їх вводять, при необхідності їх розташування в полі діаграми, у відповідні вікна); осі (задають основні осі); лінії сітки; легенда, підписи даних. Параметр “Лінії сітки” дозволяє, шляхом виставлення прапорців у відповідних вікнах, виводити на діаграму координатну сітку по двох, або одній з осей з наданням відповідного інтервалу; останній може вибиратися і автоматично. Параметр “Легенда” дозволяє вивести на поле діаграми розшифровку назв даних, наведених у наголовках стовпців чи рядків вихідної таблиці, і вибрати місцерозташування цього напису. Параметр “Підписи даних” встановленням відповідного прапорця дозволяє поруч з маркером кожного даного вивести на діаграмі його чисельне значення. Під час побудови діаграми розсіювання доцільно ввести відповідні назви осей, лишивши їх стандартними і відмовитися (зняттям прапорців) від легенди і підписів даних, які в цьому випадку будуть лишень затьмарювати малюнок. Щодо параметру “Лінії сітки”, то виведення на екран координатної сітки шляхом встановлення відповідних прапорців доцільно лише у випадку побудови кореляційної таблиці як окремого етапу кореляційного аналізу. Враховуючи, що подальші операції обробки даних здійснюватимуться за допомогою вбудованих функцій або інструментів пакету аналізу, це є зайвим;

7) після встановлення всіх параметрів діаграми натисканням на кнопку “Далее” переходять до останнього кроку – визначення місцерозташування діаграми шляхом встановлення відповідного прапорця. Можна вибрати виведення діаграми на новий аркуш чи на існуючий, поруч з табличними даними. Переважно вибирають останнє. Якщо при цьому діаграма закриє частину потрібної інформації або матиме незадовільний розмір, її положення можна буде змінити по закінченні роботи з Майстром діаграм;

8) закінчують побудову діаграми натисканням кнопки “Готово”;

9) остаточно визначають положення діаграми на аркуші шляхом стандартних операцій “перетягування / розтягування”.

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

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

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

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

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

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

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

Таблиця 3

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

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

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

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

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

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

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

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

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

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

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

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

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

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