Статистичний аналіз даних за допомогою середовища MS Exsel. Варіант №12

 

Міністерство освіти і  науки, молоді та спорту України


Дніпродзержинський  державний технічний університет

Кафедра «Прикладна математика»

 

 

 

КУРСОВА РОБОТА

«Статистичний аналіз даних за допомогою  середовища MS Exsel. Варіант №12»

 

 

 

 

Виконав                                     _______                          \

 

 

 

Перевірив                                  _______                        

              

 

 

 

Дніпродзержинськ

2012

Реферат

 

Курсова робота містить:

Мета  роботи:

      1. Повести кореляційний аналіз підприємств

 

 

 

Метод дослідження: середовища MS Exsel.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Зміст

 

 Вступ………………………………………………………………………………4

1. Теоретичні відомості…………………………………………………………...7

1.1 Контроль вхідної інформації на наявність грубих помилок і                викидів …………………………..……………..…………………….7

          1.2 Перевірка відповідності досліджуваних ознак нормальному закону    розподілу та статистична обробка багатовимірної вибірки…………9

          1.3 Методи прогнозування……………………………………………….9

                      1.3.1 Прогнозування з використанням «Ковзкого середнього»…9

                       1.3.2 «Експоненціальне згладжування»…………………..……10

         1.4 Основи кореляційного аналізу……………………………………….11

          1.5 Кореляційний аналіз статичних даних………………...…………….12

         1.6 Регресійний аналіз статичних даних………………………………14

1.6.1 Парний лінійний регресійний аналіз…………………..15

1.6.2 Критерій оцінки якості моделі регресії…………………17

1.6.3 Парний нелінійний регресивний аналіз……………….19

1.6.4 Багатовимірний регресійний аналіз…………………..20

3 Завдання до виконання курсової роботи………………………………….…

4 Практична частина……………………………………………………………

Висновки…………………………………………………………….…….…..…29

Перелік посилань……………………………………………………….….….....30

 

 

 

 

Вступ

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

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

     В даній  курсовій роботі використовується  методика проведення кореляційно-регресивного  аналізу на основі прогнозування з використанням «ковзкого середнього», прогнозування шляхом «експоненціального середнього» тощо із застосуванням реалізації цих методів в середовищі електронних таблиць Excel.

 

 

 

 

 

 

 

1 ТЕОРЕТИЧНІ ВІДОМОСТІ

1.1 Контроль  вхідної інформації на наявність грубих помилок і викидів

     Методика, що включає логічну перевірку даних, тобто виключення «нелогічних» даних и арифметичну погодженість. Враховуючи, що рівняння регресії звичайно використовують для прогнозу, то для їх одержання необхідно мати достовірну інформацію. Із цією метою необхідно виконати аналіз вихідних даних і виключити з багатовимірної вибірки рядки,що містять сумнівну інформацію, або провести додаткове уточнення інформації. Можливо, що фахівці різних підприємств використовують при цьому різні формули для розрахунку показників виробничо-господарської діяльності, наприклад, індексу зниження собівартості продукції (діапазон значень цього показника від 13.6 до 598.1) або допускають арифметичні помилки в розрахунках. Крім того,можуть бути просто описки,тобто грубі помилки. Тому після введення інформації в пам'ять комп’ютера необхідно порядково переглянути багатовимірну вибірку й видалити рядки з грубими помилками й викидами (викид – занадто велике й занадто мале значення ознаки).

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

  • Переглянути багатовимірну вибірку й скопіювати ознаки з екстремальним значенням і вільний стовпець поруч з вибіркою.
  • Значення скопійованої ознаки вибірки впорядкувати в порядку зростання або убування.
  • Екстремальне значення позначити – х1.
  • Для вибірки малого об’єму по формулі визначити розрахункове значення критерію М-Мрозр і зрівняти його з критичним значенням М.
  • Для вибірки , що містить більше 25 елементів необхідно використовувати S критерій.

Таблиця 1- Критичні значення М критерію.

Об’єм вибірки

Рівень значимості

Мрозр

α=0,1

α=0,05

α=0,01

3

0,886

0,941

0,988

  

4

0,679

0,765

0,889

5

0,557

0,642

0,780

6

0,482

0,560

0,698

7

0,434

0,507

0,637

8

0,479

0,554

0,683

  

9

0,441

0,523

0,635

10

0,409

0,477

0,597

11

0,517

0,576

0,679

  

12

0,490

0,546

0,612

13

0,467

0,512

0,615

14

0,492

0,546

0,641

  

15

0,472

0,525

0,616

16

0,454

0,507

0,595

17

0,438

0,490

0,577

  

18

0,424

0,475

0,561

19

0,412

0,462

0,547

20

0,401

0,450

0,535

21

0,391

0,440

0,524

22

0,382

0,430

0,514

23

0,374

0,421

0,505

24

0,367

0,413

0,497

25

0,360

0,406

0,489


 

     Якщо Мрозр<М, то екстремальне значення х1 з ймовірністю 1-α не є викидом, тобто його не можна виключати з вибірки. Якщо Мрозр>М, то виключається з багатовимірної вибірки рядок з екстремальним значенням ознаки.     

     При об’ємі вибірки n>25 екстремальні значення можуть бути перевірені за критерієм S: ,де - вибіркове середнє й σ*- стандартне відхилення, що визначені для всієї вибірки,х1-пербачуваний викид. Критичне значення критерію S визначається по таблиці 2.

     Якщо Sрозр<S то екстремальне значення х1 не є викидом. При Sрозр>S з багатовимірної вибірки виключається рядок з екстремальним значенням.

Таблиця  2. Критерій значення критерію S

Об’єм вибірки

Рівень значимості

α=0,05

α=0,01

30

2,929

3,402

50

3,082

3,539

100

3,283

3,718

1000

3,884

4,264


 

 

 

 

1.2 Перевірка відповідності досліджуваних ознак нормальному закону розподілу та статистична обробка багатовимірної вибірки.

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

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

  1. близькі за значенням оцінки вибіркового середнього, моди й медіани.
  2. оцінки асиметричності й ексцесу незначно відрізняються від нуля.

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

 

1.3 Методи прогнозування

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

1.3.1 Прогнозування з використанням «Ковзкого середнього»

     При використанні  цієї методики основне припущення  полягає в тому, що часовий  ряд є стійким у тому розумінні,  що його члени у1 є реалізаціями наступного випадкового процесу:

 

де b-невідомий постійний параметр, що оцінюється  на основі представленої інформації, εt – випадковий компонент(або шум) у момент часу t. Передбачається , що випадкова помилка εt має нульове математичне очікування й постійну дисперсію. Крім того,передбачається, що дані для різних періодів часу не корельовані.

     Метод використання  «ковзкого середнього» припускає,  що останні n  спостережень є рівнозначно важливими для оцінки параметра b. Інакше кажучи, якщо в даний момент часу t останніми n спостереженнями є , тоді оцінюване значення для моменту t+1 обчислюється за формулою

 

     Не існує чіткого правилу для вибору числа n – бази методу, що використовує «ковзке середнє». Якщо є вагомі підстави вважати,що спостереження протягом досить тривалого часу задовольняють моделі , то рекомендується вибирати більші значення n. Якщо ж спостережувані значення задовольняють наведеній моделі протягом коротких періодів часу, то може бути прийнято і мале значення  n . На практиці величина n звичайно приймається в межах від 2 до 10.

1.3.2 «Експоненціальне згладжування»

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

     Визначимо  величину α(0<α<1) як константу згладжування, і припустимо, що відомі значення тимчасового ряду минулих t моментів часу .Тоді оцінка для моменту часу t+1 обчислюється за формулою

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

     Формулу обчислення  можна привести до наступного(більш простого) виду:

 

     Таким чином,  значення  можна обчислити рекурентно на підставі значення .  Обчислення по цих рекурентних рівняннях починаючи з того , що припускається оцінка для t=1 і як оцінка для t=2 приймається спостережена величина для t=1,тобто . Насправді, для початку розрахунку можна використати будь-яку процедуру. Наприклад, часто за оцінку береться усереднене значення yt по прийнятому числу періодів на початку тимчасового ряду.

     Вибір константи  згладжування α є вирішальним  моментом при обчисленні значення  прогнозованої величини. Більше  значення  α приписує більшу  вагу останнім спостереженням . На  практиці значення  α беруть  у межах від 0,01 до 0,30.

1.4 Основи кореляційного аналізу

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

     Важливим для  практики є питання про оцінку  ступеня зв’язку між вимірюваними  величинами. Кількістю ступінь зв’язку  характеризується коефіцієнтом  кореляції Rxy, що може обчислюватися за формулою :

                                                     (1.4.1)

де  є сумою даних .

     Коефіцієнтом  кореляції, як відомо, не може  перевищувати по модулю одиницю.  Чим ближче модуль коефіцієнта  кореляції до одиниці, тим тіснище  кореляційно зв’язані розглянуті  величини х й у. Якщо =1,то говорять про функціональний(а не кореляційний зв'язок),при <0.3 можна говорити про відсутність кореляційного зв’язку. Якщо коефіцієнт значимо відрізняється від нуля, то можна одержати рівняння залежності однієї змінної від іншої. Такі рівняння називають регресії однієї змінної на іншу. Припустимо, що рівняння залежності є рівнянням прямої лінії. Тоді рівняння регресії х на у має вигляд ,а коефіцієнти в рівнянні прямої регресії знаходяться за формулами :

                                                                       (1.4.2)

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

                                                                       (1.4.3)

1.5 Кореляційний аналіз статичних даних

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

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

     До задачі  кореляційного аналізу відносяться  :

  • встановлення виду, ознаки(кількісна або якісна ознака);
  • встановлення наявності зв’язку ;
  • встановлення напрямку дії й виду форми зв’язку ;
  • вибір статичної значимості тісноти зв’язку й вибір факторів, щ роблять найбільш істотне значення на результативну ознаку.

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

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

  1. прямий(позитивний), при якому зі збільшенням(зменшенням)значення факторної ознаки збільшується (зменшується) значення результативної ознаки й коефіцієнт кореляції позитивний;
  2. зворотній (негативний) при якому зі збільшенням(зменшенням) значення факторної ознаки зменшується (збільшується) значення результативної ознаки й коефіцієнт кореляції позитивний.

По виду форми зв’язку  розрізняють кореляційний зв'язок :

  1. лінійний (прямолінійний), при якому рівномірній зміні значення факторної ознаки відповідає рівномірна зміна значення результативної ознаки
  2. нелінійний (криволінійний), при якому зі збільшенням значення факторної ознаки відбувається не рівномірне збільшення(або зменшення) значення результативної ознаки.

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

  1. розкидані по всьому полю, то залежності між досліджуваними ознаками немає;
  2. концентруються навколо прямої лінії, що йде від нижнього лівого кута у верхній правий,то є пряма залежність;
  3. концентруються навколо прямої лінії, що йде від верхнього лівого кута у нижній правий,то є зворотна лінійна залежність;
  4. концентрується навколо деякої кривої,тоді є нелінійна залежність.

    Коефіцієнт кореляції  характеризує тісноту зв’язку  між ознаками Х та У у  випадку лінійної залежності, його  значення визначається за формулою :

                                                                                                          (1.5.1)

Де  - кореляційний момент випадкових величин (ВВ) Х та У : - середньоквадратичне відхилення ВВ Х та У.

     Кореляційний  момент – це математичне очікування  добутку центрових ВВ  : . Для незалежних ВВ, ,отже в інших випадках .

     Інструмент  «Кореляція» пакета аналізу дозволяє  обчислити кореляційну матрицю,  що містить коефіцієнти парної  кореляції багатовимірної вибірки.

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

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

     Якщо , то зв'язок між ВВ Х і У досить ймовірний.

     Для практичного  застосування можна використати  спрощені критерії оцінки тісного  зв’язку, представлені в таблиці  3.

     При аналізі  тісноти зв’язку використовують, ще і, коефіцієнт детермінації, що при лінійному зв’язку дорівнює квадрату коефіцієнта кореляції .

Таблиця 4 – кількісні критерії оцінки тісноти зв’язку.

 

Характерна зв’язку

До 0,3

практично відсутня,слабка

Від 0,3 до 0,5

помірна

Від 0,5 до 0,7

помітна

Від 0,7 до 0,9

сильна

Від 0,9 до 0,99

дуже сильна


     Коефіцієнт  детермінації показує частку  зміни(варіації) результативної ознаки  під впливом факторної ознаки. Коефіцієнт детермінації може  приймати значення від 0 до 1. Більше  докладні відомості про коефіцієнт  детермінації наведені в розділі  «Парний регресійний аналіз».

1.6 Регресійний аналіз статичних даних

    До задачі регресійного  аналізу відносяться:

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

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

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

    По типу з’єднування  явищ розрізняють :

  1. безпосередньо регресію, коли причина впливає на наслідок,тобто результативна й факторна ознака зв’язні безпосередньо між собою;
  2. напрямку регресію – факторна ознака діє через якийсь третій або ряд інших факторних ознак на результативну ознаку;
  3. нонсенс – регресія (помилкова або абсурдна регресія) виникає при формальному підході до досліджуваних явищ, у результаті чого приходять до встановлення помилкових і навіть безглуздих залежностей.

    

          1. Парний лінійний регресійний аналіз

     Введемо позначення - умовне середнє значення ВВ У (за умови, що значення ВВ Х фіксоване); - умовне середнє значення ВВ Х. Як розглянути умовні середні значення при всіх значеннях ВВ Х, то одержимо функцію регресії величини У на  величину . Аналогічно, функцію регресії величину Х на величину .

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

      Залежність  результативної ознаки У від  факторної ознаки Х можна представити  у вигляді:

                                                                                                 (1.6.1.1)

де  – функція, що виражає об’єктивну закономірність між результативною ознакою У й факторною ознакою Х : е – випадкова величина, що виражає вплив неконтрольованих і неврахованих, а також помилок виміру.

     У випадку  парної лінійної регресії залежність (5) приймається у вигляді:

                                                                                                    (1.6.1.2)

Тоді 

                                                                                                     (1.6.1.3)

Тобто випадкова величина е характеризує відхилення змінної  у від середньої величини

                                                                                                (1.6.1.4)

Рівняння (1.6.1.4) називають моделлю регресії або рівнянням регресії.

     Параметри  а0,а1 називають коефіцієнти регресії. Параметр а0 виражає силу зв’язку : чим більше його значення, тим зв'язок між ВВ Х і У сильніше. Коефіцієнти регресії повинні бути підібрані таким чином, щоб ліня регресії проходила якнайближче до всіх точок кореляційного поля, тобто практично через його центр.

     В 1806 р. французький  математик Лежандр показав, що  найкращим чином буде відображати зв'язок між змінними, лінія, для якої виконується умова

                 (1.6.1.5)

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

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

  • статичних функцій майстра функцій;
  • формули масиву ЛИНЕЙН майстра функцій;
  • інструмента РЕГРЕСІЯ пакету аналізу;
  • ліній тренда.
          1. Критерій оцінки якості моделі регресії

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