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

Статистичний аналіз даних засобами програми Excel Автор: к. Засоби статистичного аналізу даних у програмі Excel. Використання статистичних функцій для інтерполяції та екстраполяції даних та інше Засоби статистичного аналізу даних у програмі Excel До складу Microsoft Excel входить набір засобів аналізу даних так званий пакет аналізу призначений...

2015-01-19

411.31 KB

27 чел.


Поделитесь работой в социальных сетях

Если эта работа Вам не подошла внизу страницы есть список похожих работ. Так же Вы можете воспользоваться кнопкой поиск


PAGE  16

Лекція&Лабораторна робота №

Тема. Статистичний аналіз даних засобами програми 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 — число попередніх періодів, які входять в ковзне середнє; де Aj — фактичне значення в момент часу j; Fj — прогнозне значення в момент часу 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.0080X1 -69.0702X2-28.0527X3,

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

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

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

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

Функції прогнозування

Для прогнозування використовується ряд функцій - ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ, ЛИНЕЙН, ЛГРФПРИБЛР та ін. Розглянемо деякі із них:

1. Функція ПРЕДСКАЗ обчислює або прогнозує майбутнє значення за існуючим значенням.

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

ПРЕДСКАЗ (X; Известные значения Y; Известные значения X),

де X – це точка даних, для якої прогнозується значення; відомі значення Y – це залежний масив або інтервал даних; відомі значення X – це незалежний масив або інтервал даних. Рівняння для цієї функції має вид - Y=a+bx.

2. Функція РОСТ. Вона розраховує прогнозований експонентний ріст на підставі наявних даних. Функція РОСТ повертає значення Y для послідовності нових значень X, що задаються за допомогою існуючих Х- і Y-значень. Функція робочого значення РОСТ може застосовуватися також для апроксимації існуючих Х- і Y-значень експонентної кривої.

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

РОСТ(Известные значения Y; Известные значения X; Новые значения X; Конст),

де Известные значения Y – це множина значень Y, які вже відомі для співвідношення Y =b*m^X; Известные значения X – це необов'язкова множина значень Х, які вже відомі для співвідношення Y =b*m^X; Новые значения X – це нові значення X, для яких РОСТ повертає відповідні значення Y; Конст – це логічне значення, що вказує, потрібно чи ні, щоб константа b дорівнювала 1.

3. Функція ТЕНДЕНЦИЯ. Вона розраховує значення у відповідності із лінійною апроксимацією за методом найменших квадратів.

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

ТЕНДЕНЦИЯ(Известные значения Y; Известные значения X; Новые значения X; Конст),

де Известные значения Y множина значень  Y, яка вже відома для співвідношення y = mx + b; Известные значения X – необов’язкова множина значень X, які вже відомі для співідношення y = mx + b; Новые значения X.- нові значення  X для яких функція ТЕНДЕНЦИЯ розраховує відповідні значення Y; Конст – логічне значення, яке вказує, потрібно чи ні, щоб константа в була рівною 0.

Приклад: Виконати розрахунок очікуваного прибутку підприємства на 2011 рік на основі даних про отриманий прибуток за попередній період – 2005-2010 рр за допомогою функцій ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ.

Рішення:

  •  за умови, що робоча книга вже створенна, ввести на Лист 1 наступні дані:

  •  установити курсор в клітинку В10 та виконати команду Вставка/Функция; у діалоговому вікні Мастер функций – шаг 1 из 2 вибрати категорію Статистические та функцію ПРЕДСКАЗ; у діалаговому вікні Аргументы функции ввести відповідні аргументи функції, як зображено на рис. та натиснути ОК.

  •  установити курсор в клітинку В11 та виконати команду Вставка/Функция; у діалоговому вікні Мастер функций – шаг 1 из 2 вибрати категорію Статистические та функцію РОСТ; у діалаговому вікні Аргументы функции ввести відповідні аргументи функції, як зображено на рис. та натиснути ОК.

  •  установити курсор в клітинку В12 та виконати команду Вставка/Функция; у діалоговому вікні Мастер функций – шаг 1 из 2 вибрати категорію Статистические та функцію ТЕНДЕНЦИЯ; у діалаговому вікні Аргументы функции ввести відповідні аргументи функції, як зображено на рис. та натиснути ОК.

Результат:

Функції порівняння

СЧЁТ(значение1; значение2; ...) - визначає кількість чисел у списку аргументів.

Приклад: В клітинки А1,А2,АЗ,А4,А5 введена інформація: А1=«Маша», А2=20, А3=87, А4=800, А5=. Потрібно підрахувати кількість клітинок із числовими значеннями в діапазоні А1:А5, результат отримати в клітинці А6. Для цього необхідно в клітинку А6 ввести формулу: =СЧЕТ(А1:А5). Результат розрахунку: число 3.

СЧЁТЗ(значение1; значение2; ...) визначає кількість непустих значень у списку аргументів, підраховуються значення будь-якого типу.

Приклад. В клітинки А1,А2,АЗ,А4,А5 введена інформація: А1=«Маша», А2=20, А3=87, А4=, А5=. Потрібно підрахувати кількість непустих клітинок  в діапазоні А1:А5, результат отримати в клітинці А6. Для цього необхідно в клітинку А6 ввести формулу: =СЧЕТЗ(А1:А5). Результат розрахунку: число 3.

СЧИТАТЬПУСТОТЫ(диапазон) –. підраховує кількість порожніх клітинок у діапазоні, діапазон може бути довільного типу.

Приклад. Використаємо дані попереднього прикладу А1=«Маша», А2=20, А3=87, А4=, А5=. Потрібно підрахувати кількість пустих клітинок у цьому діапазоні. Для цього – введемо в клітинку А6 формулу: =СЧИТАТЬПУСТОТЫ(А1:А5). Результат розрахунку: число 2.

МАКС(ДИАПАЗОН1;ДИАПАЗОН2;...)визначає максимальне число у заданому діапазоні.

Приклад. Вхідні дані візьмемо попереднього прикладу. Визначимо максимальне значення цього діапазону. Введемо у клітинку А4 формулу: =МАКС(А1:А3). Результат: число 25.

МИН(ДИАПАЗОН1;ДИАПАЗОН2;...)визначає мінімальне число у заданому діапазоні.

Приклад. Вхідні дані -  В1=69, В2=89, В3=-2, визначити мінімальне число. Для цього у клітинку В4 ввести формулу: =МИН(В1:В3). Результат: число -2.

Функції середніх

СРЗНАЧ(диапазон1;діапазон2;...)визначає середнє арифметичне множини чисел - .

Приклад. Маємо наступні вхідні дані: А1=10, А2=10, А3=25, підрахувати середнє арифметичне множини вхідних даних. Введемо в клітинку А4 формулу: =СРЗНАЧ(А1:А3). Результат : число 15.

СРГЕОМ(число1;число2; ...) – визначає середнє геометричне множини додатних чисел - .

Приклад. Маємо наступні вхідні дані: А1=10, А2=10, А3=25, підрахувати середнє геометричне  множини вхідних даних. Введемо в клітинку А4 формулу: =СРГЕОМ(А1:А3). Результат : число 13,57.


Лекція&Лабораторна робота: статистичний аналіз даних засобами програми Excel


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



 

Другие похожие работы, которые могут вас заинтересовать.
4777. ВІКНО ПРОГРАМИ EXCEL 146.22 KB
  Операції з листами Основні операції що виконуються з робочими листами зібрані в контекстному меню яке відкривається після клацання правою кнопкою миші вкладки аркуша. Воно містить команди: Додати Insert Видалити Delete Перейменувати Renme Переместіть ськопіровать Move or Copy Виділити всі листи Select ll Sheets і так далі Наприклад аби задати типа аркуша що вставляється: Аркуш Worksheet Діаграма Chrt Макрос MS Excel. Вибір типа аркуша що вставляється.
3253. Економіко-статистичний аналіз фінансових результатів реалізації молока 146.75 KB
  Метою курсової роботи є аналіз фінансових результатів реалізації молока в сільськогосподарському підприємстві і виявлення шляхів їх підвищення. У ході роботи визначено такі завдання: Теоритичні основи економіко-статистичного аналізу фінансових результатів від реалізації молока - Сучасний стан реалізації молока в аграрному секторі
7922. Табличний процесор MS Excel: формати даних, робота з формулами 13.78 KB
  Встановлення формату комірки можна здійснювати і перед введенням даних у комірку. У цьому випадку табличний процесор для виділеної комірки або діапазону комірок введену інформацію буде сприймати як текст. Введене число автоматично вирівнюється по правому краю комірки. За замовчуванням всі комірки нової робочої книги мають формат Общий.
4354. Забезпечення працівників спецодягом, іншими засобами індивідуального захисту, мийними та знешкоджувальними засобами 6.92 KB
  Забезпечення працівників спецодягом іншими засобами індивідуального захисту мийними та знешкоджувальними засобами На роботах із шкідливими і небезпечними умовами праці а також роботах пов'язаних із забрудненням або несприятливими метеорологічними умовами працівникам видаються безоплатно за встановленими нормами спеціальний одяг спеціальне взуття та інші засоби індивідуального захисту а також мийні та знешкоджувальні засоби. Роботодавець зобов'язаний забезпечити за свій рахунок...
8763. Реляційна модель даних. Структура реляційних даних 19.59 KB
  Структура реляційних даних Мета: знати призначення реляційної моделі даних та головні її поняття. Література Бази даних. Бази даних: основи проектування використання Малихіна М.
10633. Електронні таблиці. Табличний процесор, запуск, відкриття й збереження документа. Копіювання, переміщення й видалення даних, форматування даних 101.89 KB
  Електронні таблиці програми для обробки даних у вигляді таблиці. Для запуску програми слід виконати команду Пуск Програми Microsoft Office Microsoft Office Excel або іншим стандартним способом запуску наприклад з ярлику на Робочому столі.для 2003 версії Стовпці позначаються латинськими літерами в алфавітному порядку B C D. Для редактирования данных в ячейке необходимо сделать её текущей установить на неё табличный курсор и нажать клавишу F2 либо два раза левой клавишей мышки в ячейке появится текстовый курсор вносим...
8765. Моделі даних. Ієрархічна модель даних 17.26 KB
  Ієрархічна модель даних Мета: засвоєння поняття модель даних її призначення види моделей даних властивості ієрархічної моделі даних. Література Бази даних. Бази даних: основи проектування використання Малихіна М.
3710. ФІЗИКО-СТАТИСТИЧНИЙ МЕТОД ВИЗНАЧЕННЯ НАДІЙНОСТІ ВИРОБІВ ТВЕРДОТІЛЬНОЇ ЕЛЕКТРОНІКИ 322.5 KB
  Більш ефективне рішення проблеми оцінки надійності може бути знайдене шляхом поєднання імовірнісних, статистичних концепцій теорії і практики надійності з нерівноважною термодинамікою, яка описує процеси деградації матеріалів виробів. І в зв’язку з тим
10922. Облік операцій з основними засобами 41.3 KB
  Загальна характеристика рахунків для обліку основних засобів банків 10.Облік основних засобів на різних стадіях їх життєвого циклу 10.Інвентаризація основних засобів і відображення в обліку її результатів 10.Документальне оформлення операцій з обліку основних засобів 10.
8591. Облік окремих операцій з основними засобами 29.83 KB
  Порядок проведення технічного обслуговування і ремонту дорожніх транспортних засобів визначає Положення № 102. Проведення ремонту орендованого основного засобу також повинно передбачатися в договорі. Від обумовлених умов ремонту залежатимуть особливості відображення операцій з його проведення у податковому і бухгалтерському обліку. Щоб уникнути подальших непорозумінь згоду на проведення ремонту краще отримати у письмовому вигляді оскільки вартість поліпшень зроблених орендарем без згоди орендодавця які не можна відокремити не...
© "REFLEADER" http://refleader.ru/
Все права на сайт и размещенные работы
защищены законом об авторском праве.