Якщо потрібно розробити складний статистичний або інженерний аналіз, ви можете заощадити час і кроки, використовуючи пакет аналізу. Ви надаєте дані та параметри для кожного аналізу, а засіб використовує усі потрібні статистичні або інженерні макрофункції для проведення підрахунку та відображає результати в таблиці результатів. Деякі засоби, окрім таблиць результатів, створюють ще й діаграми.
Функції аналізу даних можна використовувати одночасно тільки на одному аркуші. Під час виконання аналізу даних на згрупованих аркушах результати відображаються на першому аркуші і пусті форматовані таблиці відобразяться на решті аркушів. Щоб виконати аналіз даних на решті аркушів перерахуйте засіб аналізу на кожному аркуші.
Пакет аналізу містить описані нижче засоби. Щоб отримати доступ до цих засобів, виберіть аналіз даних на вкладці Дані . Якщо команда Аналіз даних недоступна, потрібно завантажити й активувати надбудову "Пакет аналізу ".
Завантаження й активація надбудови ''Пакет аналізу''
Щоб завантажити й активувати пакет аналізу, виконайте наведені нижче дії.
ВExcel для Mac у меню "Файл" перейдіть до розділу Знаряддя>надбудов Excel.
В Excel для Windows:
- Виберіть Файл, Параметри, а потім – Надбудови.
- У полі Керування виберіть пункт Надбудови Excel , а потім натисніть кнопку Перейти.
У полі Надбудови встановіть прапорець Пакет аналізу , а потім натисніть кнопку OK.
- Якщо надбудова Пакет аналізу відсутня у списку Наявні надбудови, натисніть кнопку Огляд, щоб її знайти.
- Якщо з'явиться запит на те, що пакет аналізу наразі не інстальовано на комп'ютері, натисніть кнопку Так , щоб інсталювати його.
Примітка.
Щоб додати до пакета аналізу функції Visual Basic for Application (VBA), завантажте надбудову ''Пакет аналізу – VBA'' так само, як ви завантажували власне надбудову ''Пакет аналізу''. У полі Наявні надбудови встановіть прапорець Пакет аналізу – VBA.
Дисперсійний аналіз
Засоби дисперсійного аналізу призначені для виконання різних видів дисперсійного аналізу. Вибір використовуваного засобу залежить від багатьох факторів та кількості вибірок із сукупностей, які потрібно проаналізувати.
Дисперсійний аналіз: однофакторний
Цей засіб виконує простий аналіз дисперсії даних для кількох зразків. Аналіз дає змогу перевірити гіпотезу, що кожна вибірка взята з одного базового розподілу ймовірності проти альтернативної гіпотези про те, що базові розподіли ймовірності не однакові для всіх вибірок. Якщо є лише два зразки, можна скористатися функцією аркуша T.TEST. З більш ніж двома зразками, немає зручного узагальнення T.TEST, і модель Single Factor Anova можна викликати замість цього.
Дисперсійний аналіз: двофакторний із повторенням
Цей засіб аналізу корисний, якщо дані можна систематизувати за двома параметрами. Наприклад, в експерименті з вимірювання зростання рослини обробляли добривами різних виробників (наприклад, А, В, С) та утримували за різної температури (наприклад, низької та високої). Таким чином, для кожної з 6 можливих пар умов {добриво, температура} можна отримати однаковий набір спостережень за зростанням рослин. За допомогою засобу дисперсійного аналізу можна перевірити:
- Чи висота рослин для різних марок добрив взята з однієї основної сукупності. Для цього аналізу ігноруються температури.
- Чи висота рослин для різних рівнів температури взята з однієї базової сукупності. Бренди добрив ігноруються для цього аналізу.
Чи 6 зразків, які представляють усі пари значень {добриво, температура} і використовуються для оцінки впливу різних марок добрив (крок 1) та рівнів температури (крок 2), витягнуто з однієї загальної сукупності. Альтернативна гіпотеза припускає, що вплив конкретних пар {добриво, температура} перевищує вплив окремо добрива та окремо температури.
Дисперсійний аналіз: двофакторний без повторення
Цей вид аналізу можна застосовувати, якщо дані класифіковано за двома вимірами. Однак для цього аналізу передбачено тільки одне спостереження для кожної пари (наприклад, для кожної пари {добриво, температура} в наведеному вище прикладі).
Кореляція
Функції аркуша CORREL і PEARSON обчислюють коефіцієнт кореляції між двома змінними вимірювання, коли для кожної з тем N спостерігаються вимірювання кожної змінної. (Будь-яке відсутнє спостереження для будь-якої теми призводить до ігнорування в аналізі.) Інструмент аналізу взаємозв'язку особливо корисний, якщо для кожного з суб'єктів N існує більше двох змінних вимірювання. Вона надає таблицю виводу – матрицю взаємозв'язку, яка відображає значення CORREL (або PEARSON), застосоване до кожної можливої пари змінних вимірювання.
Коефіцієнт кореляції, як і коваріація, є мірою, в якій дві змінні вимірювання "різняться разом". На відміну від коваріації, коефіцієнт кореляції масштабується так, що його значення не залежить від одиниць, у яких виражаються дві змінні вимірювання. (Наприклад, якщо дві змінні вимірювання мають вагу і висоту, значення коефіцієнта кореляції не зміниться, якщо товщина перетворюється з фунтів на кілограм.) Значення будь-якого коефіцієнта кореляції має бути в межах від -1 до +1 включно.
Кореляційний аналіз дає можливість установити, чи асоційовані набори даних за величиною, тобто більші значення з одного набору даних співвідносяться з більшими значеннями другого набору (позитивна кореляція), чи навпаки, малі значення одного набору даних співвідносяться з більшими значеннями другого набору (негативна кореляція), або дані двох діапазонів не співвідносяться (нульова кореляція).
Коваріація
Засоби "Взаємозв'язок" і "Коваріація" можна використовувати в одній настройці, якщо на наборі окремих користувачів спостерігається N різних змінних вимірювання. Усі засоби "Взаємозв'язок" і "коваріація" дають таблицю виводу – матрицю, яка відображає коефіцієнт кореляції або коваріацію відповідно між кожною парою змінних вимірювання. Різниця полягає в тому, що коефіцієнти кореляції масштабуються від -1 до +1 включно. Відповідні коварії не масштабуються. Коефіцієнт кореляції та коваріація – це показники в тій мірі, в якій дві змінні "різняться разом".
Засіб коваріація обчислює значення функції аркуша COVARIANCE. P для кожної пари змінних вимірювання. (Пряме використання КОВАРІАЦІЇ. P, а не коваріація інструмент є розумною альтернативою, якщо є тільки дві змінні вимірювання, тобто 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 – кількість попередніх періодів, які потрібно долучити до змінного середнього;
- Aj – це фактичне значення часу j
- Fj – це прогнозоване значення на час 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-test не припускається, що дисперсії обох сукупності рівні.
Примітка.
Одним із результатів тесту є сукупна дисперсія (сукупна міра розподілу даних навколо середнього значення), яка обчислюється за наведеною формулою.
Тест Ст’юдента: двовибірковий t-тест із рівними дисперсіями
Цей засіб аналізу виконує двовибірковий t-тест учня. У цій формі t-Test припускається, що два набори даних походили з розподілів з однаковими дисперсіями. Його називають гомоскедастичним t-тестом. За допомогою цього t-тесту можна визначити, чи ці дві вибірки, імовірно, отримано з розподілів із рівними засобами генеральної сукупності.
Тест Ст’юдента: двовибірковий t-тест із нерівними дисперсіями
Цей засіб аналізу виконує двовибірковий t-тест учня. У цій формі t-Test припускається, що два набори даних походили з розподілів із нерівними дисперсіями. Його називають гетероскедастичним t-тестом. Як і в попередньому випадку рівних відхилень, цей t-тест можна використовувати, щоб визначити, чи отримано дві вибірки з розподілів із рівними засобами генеральної сукупності. Ця перевірка використовується, якщо у двох зразках є окремі предмети. Використовуйте з'єднаний тест, описаний у прикладі нижче, коли існує один набір предметів, а два зразки представляють розміри для кожного предмета до та після обробки.
Для визначення статистичного значення t використовується наведена формула.
Наведена нижче формула використовується для обчислення ступенів свободи (df). Оскільки результат обчислення зазвичай не ціле, значення df округлюється до найближчого цілого числа, щоб отримати критичне значення з таблиці t. Функція аркуша Excel T.TEST використовує обчислюване значення DF без округлення, оскільки значення T.TEST можна обчислити за допомогою неінтегрової df. Через ці різні підходи до визначення ступенів свободи результати T.TEST і цього засобу t-Test будуть відрізнятися у випадку нерівних відхилень.
Зета-тест
Z-Test: Two Sample for Means analysis tool perform a two sample z-Test for means with known variances. Цей інструмент використовується для перевірки нульової гіпотези про відсутність різниці між двома засобами сукупності проти однобікних або двосторонніх альтернативних гіпотез. Якщо дисперсії не відомі, натомість слід використовувати функцію аркуша 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 2016
Створення діаграми Парето в програмі Excel 2016
Завантаження надбудови "Пакет аналізу" в Excel
Способи уникнення недійсних формул
Виявлення та виправлення помилок у формулах