Використання надбудови ''Пакет аналізу'' для виконання аналізу складних даних

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

Функції аналізу даних можна використовувати одночасно тільки на одному аркуші. Під час виконання аналізу даних на згрупованих аркушах результати відображаються на першому аркуші і пусті форматовані таблиці відобразяться на решті аркушів. Щоб виконати аналіз даних на решті аркушів перерахуйте засіб аналізу на кожному аркуші.

Пакет аналізу містить описані нижче засоби. Щоб скористатися цими засобами, виберіть команду Аналіз даних у групі Аналіз на вкладці Дані. Якщо команда Аналіз даних недоступна, необхідно завантажити надбудову ''Пакет аналізу''.

  1. На вкладці Файл виберіть пункт Параметри, а потім – категорію Надбудови.

    Якщо ви використовуєте Excel 2007, натисніть кнопку Microsoft Office Зображення кнопки Office , а потім – Параметри Excel.

  2. У полі Керування виберіть пункт Надбудови Excel і натисніть кнопку Перейти.

    Якщо ви використовуєте Excel для Mac, у меню File (Файл) виберіть Tools (Знаряддя) > Excel Add-ins (Надбудови Excel).

  3. У вікні Надбудови встановіть прапорець для надбудови Пакет аналізу й натисніть кнопку OK.

    • Якщо надбудова Пакет аналізу відсутня у списку Наявні надбудови, натисніть кнопку Огляд, щоб її знайти.

    • Якщо з’явиться повідомлення, що надбудову ''Пакет аналізу'' ще не інстальовано на вашому комп’ютері, натисніть кнопку Так, щоб інсталювати її.

Примітка : Щоб додати до пакета аналізу функції Visual Basic for Application (VBA), завантажте надбудову ''Пакет аналізу – VBA'' так само, як ви завантажували власне надбудову ''Пакет аналізу''. У полі Наявні надбудови встановіть прапорець Пакет аналізу – VBA.

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

Дисперсійний аналіз: однофакторний

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

Дисперсійний аналіз: двофакторний із повторенням

Цей засіб аналізу корисний, якщо дані можна систематизувати за двома параметрами. Наприклад, в експерименті з вимірювання зростання рослини обробляли добривами різних виробників (наприклад, А, В, С) та утримували за різної температури (наприклад, низької та високої). Таким чином, для кожної з 6 можливих пар умов {добриво, температура} можна отримати однаковий набір спостережень за зростанням рослин. За допомогою засобу дисперсійного аналізу можна перевірити:

  • Чи дані про ріст рослин для різних марок добрив здобуто з однієї загальної сукупності незалежно від температури.

  • Чи дані про ріст рослин для різних рівнів температури здобуто з однієї загальної сукупності незалежно від марки добрива.

Чи 6 зразків, які представляють усі пари значень {добриво, температура} і використовуються для оцінки впливу різних марок добрив (крок 1) та рівнів температури (крок 2), витягнуто з однієї загальної сукупності. Альтернативна гіпотеза припускає, що вплив конкретних пар {добриво, температура} перевищує вплив окремо добрива та окремо температури.

Діапазон вхідних даних для засобу дисперсійного аналізу

Дисперсійний аналіз: двофакторний без повторення

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

Функції аркуша CORREL і PEARSON обчислюють коефіцієнт кореляції між двома змінними вимірювання, коли вимірювання для кожної змінної спостерігається для кожного з N елементів (відсутність якогось спостереження для елемента призводить до ігнорування цього елемента в аналізі). Кореляційний аналіз призначений до застосування в разі наявності більше двох змінних вимірювання для кожного з N елементів. У результаті створюється таблиця (кореляційна матриця), у якій відображено значення функції CORREL (або PEARSON) для кожної можливої пари змінних вимірювання.

Коефіцієнт кореляції, як і коваріаційний аналіз, характеризує область, у якій два виміри ''змінюються разом''. На відміну від коваріаційного аналізу, коефіцієнт кореляції масштабується таким чином, що його значення не залежить від одиниць, у яких виражені змінні двох вимірів (наприклад, якщо два виміри – це вага й висота, значення коефіцієнта кореляції не зміниться після переведення значення ваги з фунтів у кілограми). Будь-яке значення коефіцієнта кореляції має належати діапазону від -1 до +1 включно.

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

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

Коваріаційний аналіз обчислює значення функції аркуша COVARIANCE.P для кожної пари змінних вимірювань (пряме використання функції COVAR замість засобу коваріаційного аналізу слушне тільки за наявності двох змінних вимірювань, тобто якщо N=2). Елемент по діагоналі таблиці, яка повертається після проведення коваріаційного аналізу, в рядку i, стовпці i є коваріаційним аналізом i-ої змінної вимірювання із самою собою; це лише дисперсія загальної сукупності для цієї змінної, яка обчислюється функцією аркуша VAR.P.

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

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

Засіб аналізу ''Експоненційне згладжування'' застосовується для передбачення значення на основі прогнозу для попереднього періоду, скорегованого з урахуванням похибок у цьому прогнозі. Під час аналізу застосовується константа згладжування a, за величиною якої визначається ступінь впливу похибок на прогноз у попередньому прогнозі.

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

Метод Фішера для двовибіркових дисперсій застосовується для порівняння дисперсій двох сукупностей.

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

За допомогою цього засобу обчислюється значення f F-статистики (або F-коефіцієнт). Значення f, близьке до 1, вказує, що дисперсії генеральної сукупності рівні. У таблиці вихідних даних, якщо f < 1, ''P(F <= f) однобічне'' дає можливість спостереження значення F-статистики, меншого від f за рівних дисперсій генеральної сукупності, а ''F критичне однобічне'' видає критичне значення, менше від 1, для вибраного рівня значності альфа. Якщо f > 1, ''P(F <= f) однобічне'' дає можливість спостереження значення F-статистики, більшого від f за рівних дисперсій генеральної сукупності, а ''F критичне однобічне'' видає критичне значення, більше від 1 для альфа.

Цей засіб застосовується для розв’язання задач у лінійних системах та аналізу періодичних даних на основі методу швидкого перетворення Фур’є. Засіб також підтримує зворотні перетворення, в цьому разі інвертування перетворених даних повертає вихідні дані.

Вхідний і вихідний діапазони даних для аналізу Фур’є

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

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

Порада : В Excel 2016 тепер можна створювати гістограми та діаграми Парето.

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

Формула для обчислення змінних середніх

де:

  • N – кількість попередніх періодів, які потрібно долучити до змінного середнього;

  • A j фактичне значення на момент часу j

  • F j прогнозоване значення на момент часу j

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

Використовується для створення таблиці, яка містить порядковий та відсотковий ранги для кожного значення у сукупності даних. Цей метод можна застосувати для аналізу відносного взаємного розташування даних у сукупності. Засіб використовує функції аркуша RANK.EQ і PERCENTRANK.INC. Якщо потрібно врахувати пов’язані значення, скористайтеся функцією RANK.EQ, яка присвоює пов’язаним значенням однаковий ранг, або використайте функцію RANK.AVG, яка повертає середній ранг пов’язаних значень.

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

Засіб регресії використовує функцію LINEST.

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

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

Для всіх трьох засобів, наведених нижче, значення t-статистики – t – обчислюється та відображається як ''t Stat'' у таблицях результатів. Залежно від даних це значення t може бути від’ємним або невід’ємним. Якщо припустити, що середні значення генеральної сукупності рівні, при t < 0, ''P(T <= t) однобічне'' дає імовірність того, що спостережуване значення t-статистики буде більш від’ємним, ніж t. При t >=0, ''P(T <= t) однобічне'' робить можливим спостереження значення t-статистики, яке буде більш додатнім, ніж t. ''t Критичне однобічне'' видає граничне значення, тому імовірність спостереження значення t-статистики більшого або рівного ''t критичне однобічне'' дорівнює альфа.

''P(T <= t) двобічне'' дає імовірність спостереження значення t-статистики за абсолютним значенням, більшим від t. ''P критичне двобічне'' видає граничне значення, тому значення імовірності спостереження значення t- статистики за абсолютним значенням більшого ''P критичне двобічне'' дорівнює альфа.

Тест Ст’юдента: парний двовибірковий t-тест для середніх

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

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

Формула обчислення сукупної дисперсії

Тест Ст’юдента: двовибірковий t-тест із рівними дисперсіями

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

Тест Ст’юдента: двовибірковий t-тест із нерівними дисперсіями

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

Для визначення статистичного значення t використовується наведена формула.

Формула для обчислення значення t

Ця формула використовується для обчислення ступеня свободи – значення df. Оскільки результат обчислення зазвичай не буває цілим числом, значення df округлюється до цілого для отримання граничного значення з t-таблиці. Функція аркуша Excel T.TEST використовує обчислені значення без округлення для обчислення значення T.TEST з нецілим значенням df. Через різність підходів до визначення ступенів свободи, результати функцій T.TEST і t-test відрізнятимуться у випадку з різними дисперсіями.

Формула наближеного обчислення ступеня свободи

Засіб виконує двовибірковий z-тест для середніх із відомими дисперсіями. Цей тест використовується для перевірки нульової гіпотези про відсутність різниці між середніми двох генеральних сукупностей відносно до односторонньої або двосторонньої альтеративних гіпотез. Якщо значення дисперсій невідомі, слід використовувати функцію Z.TEST.

Під час використання засобу ''Зета-тест'' слід уважно переглядати результат. ''P(Z <= z) однобічне'' насправді є P(Z >= ABS(z)), імовірність z-значення, віддаленого від 0 у тому самому напрямку, що і спостережуване z-значення за однакових середніх значень генеральної сукупності. ''P(Z <= z) двобічне'' є насправді P(Z >= ABS(z) або Z <= -ABS(z)), імовірність z-значення, віддаленого від 0 у тому самому напрямку, що і спостережуване z-значення за однакових середніх значень генеральної сукупності. Двобічний результат є однобічним результатом, помноженим на 2. Засіб ''Зета-тест'' можна застосовувати для нульової гіпотези про особливе ненульове значення різниці між двома середніми генеральних сукупностей. Наприклад, цей метод можна використовувати для визначення різниці між характеристиками двох моделей автомобілів.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Див. також

Створення гістограми в Excel 2016

Створення діаграми Парето в Excel 2016

Інсталяція й активація надбудов "Пакет аналізу" та "Пошук розв’язання" (відео)

Технічні функції (довідка)

Статистичні функції (довідка)

Огляд формул в Excel

Способи уникнення недійсних формул

Виявлення помилок у формулах

Сполучення клавіш і функціональні клавіші в Excel для Windows

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×