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

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

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

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

  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.Перевірка. У більш ніж двох зразках немає зручного узагальнення т.Перевірката відповіді на них можна викликати натомість.

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

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

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

  • Чи є висота рослин для різних рівнів температур, накреслених з одних і тих самих основних популяцій. Для цього аналізу ігноруються бренди мінеральних добрив.

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

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

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

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

Функції аркуша CORREL і Пірсона – обчислення коефіцієнта кореляції між двома змінними вимірюваннями, коли вимірювання для кожної змінної спостерігаються для кожної з суб'єктів N. (Будь-яке відсутнє спостереження за будь-якими темами, які можуть бути пропущені в аналізі); Засіб аналізу кореляції особливо корисний, якщо для кожної з них є більше двох змінних вимірювань для кожної з N суб'єктів. Вона містить таблицю вихідної матриці, яка відображає значення CORREL (або Пірсона), застосованого до кожної можливої пари змінних вимірювань.

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

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

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

Інструмент "Коваріація" обчислює значення коваріацію функцій аркуша. P для кожної пари змінних вимірювань. (Безпосереднє використання КОВАРІАЦІЙ. P, а не інструмент "Коваріація" – це розумна альтернатива, якщо є тільки дві змінні вимірювань, тобто N = 2.) Запис у діагональній таблиці, що міститься в рядку, у стовпці i-th, стовпець я – це Коваріація 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

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

Засіб аналізу "ранг" і "процентиль" створює таблицю, яка містить порядкове та відсоткове звання кожного значення в сукупності даних. Ви можете проаналізувати відносну кількість значень у сукупності даних. Цей засіб використовує функції аркуша, що мають ранг. ЕКВАЛАЙЗЕР і PERCENTRANK. INC. Якщо потрібно враховувати пов'язані значення, використовуйте ранг. Функція еквалайзер , яка обробляє зв'язані значення, що мають однаковий ранг, або використовуйте ранг.Функція 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-Test студента. Ця форма t-Test припускає, що два набори даних отримано з дистрибутивів з такими самими дисперсіями. Це називається гомоскедастичний t-Test. Ви можете використовувати цей t-тест, щоб визначити, чи є два зразки, ймовірно, надходять із дистрибутивів за допомогою рівних засобів сукупності.

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

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

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

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

Наведена нижче формула використовується для обчислення степенів вільності, DF. Оскільки результат обчислення зазвичай не ціле число, значення df округлюється до найближчого цілого числа, щоб отримати критичне значення з таблиці t. Функція аркуша Excel ( T. )Перевірка використовує обчислюваний значення df без округлення, тому що можна обчислити значення для T.Перевірка за допомогою нецілого числа DF. Через ці різні підходи для визначення степенів вільності, результати T.Перевірка і цей засіб t-Test відрізнятиметься в разі нерівних дисперсій.

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

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

Під час використання засобу ''Зета-тест'' слід уважно переглядати результат. ''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

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

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

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

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

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

Передплата для максимальної ефективності

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

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

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

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

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

×