Автор: к.т.н., доц. Скороход О.М

Тема. Статистичний аналіз даних засобами програми Excel

Резюме. Засоби статистичного аналізу даних у програмі Excel. Основні поняття регресійного аналізу. Використання статистичних функцій для інтерполяції та екстраполяції даних та інше

Ø Засоби статистичного аналізу даних у програмі Excel

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

Для активації пакету аналізу необхідно виконати наступну команду Сервис/Анализ данных. (При відсутності команди Анализ данных необхідно активувати відповідну надстройку, для чого: виконати команду Сервис/Надстройки; у діалоговому вікні Надстройки установити флажок поряд з командою Пакет анализа; натиснути кнопку ОК).

В пакет аналіза входять наступні засоби:

1. Однофакторный дисперсионный анализ (однофакторний дисперсійний аналіз). Однофакторний дисперсійний аналіз використовується для перевірки гіпотези про подібність середніх значень двох або більш вибірок, що належать до однієї генеральної сукупності. Цей метод поширюється також на тести для двох середніх (до яких відноситься, наприклад, t- критерій).

2. Двухфакторный дисперсионный анализ с повторениями (двофакторний дисперсійний аналіз із повтореннями). Являє собою більш складний варіант однофакторного аналізу з декількома вибірками для кожної групи даних.

3. Двухфакторный дисперсионный анализ без повторений (двофакторний дисперсійний аналіз без повторення). Являє собою двофакторний аналіз дисперсії, що не включає більш однієї вибірки на групу. Використовується для перевірки гіпотези про те, що середні значення двох або декількох вибірок однакові (вибірки належать одній генеральній сукупності). Цей метод поширюється також на тести для двох середніх, такі як t-критерій.

4. Корреляция (кореляція). Застосовується для кореляційного аналізу, а саме кількісної оцінки взаємозв'язку двох наборів даних, представлених у безрозмірному виді. Показником тісноти зв’язку між наборами даних є коефіцієнт кореляції вибірки, який показує відношення коваріації двох наборів даних до добутку їх стандартних відхилень і розраховується за наступною формулою:

де ,

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

Примітка. Для обчислення коефіцієнта кореляції можна також скористатися статистичною функцією КОРРЕЛ.

5. Ковариация (коваріація). Застосовується для коваріаційного аналізу. Коваріація являється мірою зв'язку між двома діапазонами даних. Вона використовується для обчислення середнього добутку відхилень точок даних від відносних середніх по наступній формулі:

()

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

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

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

7. Экспоненциальное сглаживание (експонентне згладжування). Застосовується для прогнозування значення на основі прогнозу для попереднього періоду, скоректованого з урахуванням погрішностей у цьому прогнозі. При аналізі використовується константа згладжування α, по величині якої визначається ступінь впливу на прогнози погрішностей у попередньому прогнозі.

Примітка. Для константи згладжування найбільш підходящими є значення від 0,2 до 0,3. Ці значення показують, що помилка поточного прогнозу встановлена на рівні від 20 до 30 відсотків помилки попереднього прогнозу. Більш високі значення константи прискорюють відгук, але можуть привести до непередбачених викидів. Низькі значення константи можуть привести до більших проміжків між прогнозованими значеннями.

8. Двухвыборочный F-тест для дисперсии (двовибірковий F-тест для дисперсії). Застосовується для порівняння дисперсій двох генеральних сукупностей.

9. Анализ Фурье (аналіз Фур’є). Використовується для розв’язку задач в лінійних системах і аналізу періодичних даних на основі методу швидкого перетворення Фур’є.

10. Гистограмма (гістограма). Використовується для обчислення вибіркових і інтегральних частот попадання в указанні інтервали значень. При цьому розраховуються числа попадань для заданого діапазону клітинок.

11. Скользящее среднее (ковзне середнє). Ковзне середнє використовується для розрахунків значень у прогнозованому періоді на основі середнього значення змінної для зазначеного числа попередніх періодів. Ковзне середнє, на відміну від простого середнього для всієї вибірки, містить відомості про тенденції зміни даних. Цей метод може використовуватися для прогнозу збуту, запасів і інших процесів. Розрахунки прогнозованих значень виконується за наступною формулою:

де N — число попередніх періодів, які входять в ковзне середнє; де A_j — фактичне значення в момент часу j; F_j — прогнозне значення в момент часу j.

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

Наприклад, можна використовувати нормальний розподіл для моделювання сукупності даних по рості індивідуумів, або використовувати розподіл Бернуллі для двох імовірних наслідків, щоб описати сукупність результатів кидання монети.

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

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

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

15. Выборка (вибірка). Створює вибірку з генеральної сукупності, розглядаючи вхідний діапазон як генеральну сукупність. Якщо сукупність занадто велика для обробки або побудови діаграми, можна використовувати представницьку вибірку. Крім того, якщо передбачається періодичність вхідних даних, то можна створити вибірку, що містить значення тільки з окремої частини циклу.

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

16. Парный двухвыборочный t-тест для средних (парний двовибірковий t-тест для середніх). Він використовується для перевірки гіпотези про відмінність середніх для двох вибірок даних. У ньому не передбачається рівність дисперсій генеральних сукупностей, з яких вибрані дані. Парний тест використовується, коли є природня парність спостережень у вибірках, наприклад, коли генеральна сукупність тестується двічі - до і після експерименту.

Примітка. Одним з результатів тесту є сукупна дисперсія (сукупний міра розподілу даних навколо середнього значення), що обчислюється по наступній формулі:

17. Двухвыборочный t-тест с одинаковыми дисперсиями (двовибірковий t-тест із однаковими дисперсіями). Вінслужить для перевірки гіпотези про рівність середніх для двох вибірок. Ця форма t-тесту припускає збіг дисперсій генеральних сукупностей і звичайно називається гомоскедастичним t-тестом.

18. Двухвыборочный t-тест с различными дисперсиями (двовибірковий t-тест із різними дисперсіями). Він використовується для перевірки гіпотези про рівність середніх для двох вибірок даних з різних генеральних сукупностей. Ця форма t-тесту припускає розбіжність дисперсій генеральних сукупностей і звичайно називається гетероскедастичним t-тестом. Якщо тестується одна й та ж сама генеральна сукупність, необхідно використовувати парний тест.

Для визначення тестової величини t використовується наступна формула:

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

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

19. Двухвыборочный Z-тест для средних (двовибірковий Z-тест для середніх). Використовується для перевірки гіпотези про відмінність між середніми двох генеральних сукупностей.

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

Для прикладу розглянемо засіб аналізу даних – Регрессия. Для роботи з цим інструментом необхідно дотримуватись наступних основних правил:

· масиви даних розміщуються у стовпцях;

· недопускається порожніх рядків і стовців;

· перший рядок – назви показників;

· перший стовпець даних – масив Y (результативний фактор);

· наступні стовпці – масиви X (масиви факторів-ознак).

Приклад [ Ошибка! Источник ссылки не найден. ]. Провести регресійний аналіз валового прибутку підприємства.

Розв’язок:

· за умови, що робоча книга вже стоворена, розмістити на Листі 1 вхідні дані (рис.):

· виконати команду Сервис/Анализ данных та у діалоговому вікні Анализ данных вибрати інструмент аналізу Регрессия; натиснути ОК.

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

· у діалоговому вікні Регрессия установити параметри згідно із рис. наведеним нижче.

Зауваження. Слідкуйте, щоб діапазон клітинок, зображений на рис. співпав з діапазоном клітинок в які Ви ввели вхідні дані.

Після виконання вище зазначених дій на окремому Листі повинен з’явитись наступний результат:

Аналізуючи одержані результати регресійного аналізу наведеного прикладу, можна зробити наступні висновки:

· наявний високий зв’язок між результативним показником Y і факторами Х, про що свідчить коефіцієнт детермінації, на рисунку це R-квадрат, який дорівнює 0,994275983;

· на основі даних, зображених на фрагменту попереднього рис. можна описати рівняння залежності валового прибутку від зазначених факторів:

Y=2613.76+0.0080X₁ -69.0702X₂-28.0527X_3,

отримане рівняння можна використати для прогнозних розрахунків;

· витрати на 1 грн. Реалізації і матеріаловіддача мають зворотній зв’язок з валовим прибутком, тому що коефіцієнти рівняння – від’ємні.

Ø Статистичні функції

До засобів статистичного аналізу даних, окрім перерахованих інструментів пакету аналіза, відносяться і статистичні функції. Розглянемо деякі із них.

Поиск по сайту: