Використання Microsoft Office Excel для перевірки статистичних гіпотез

МІНІСТЕРСТВО ОСВІТИ І НАУКИ, МОЛОДІ ТА СПОРТУ УКРАЇНИ

БЕРДЯНСЬКИЙ ДЕРЖАВНИЙ  ПЕДАГОГІЧНИЙ УНІВЕРСИТЕТ

 

Інститут соціально-педагогічної та колекційної освіти

 

 

 

 

 

 

 

 

РЕФЕРАТ

з дисципліни «_______________________»

на тему:

 «Використання Microsoft Office Excel  для перевірки статистичних гіпотез»

 

 

Реферат виконала

студентка ІІ курсу

202 (а) групи

ОСНЯЧ ЯНА

 

 

 

 

 

 

 

 

 

 

 

 

БЕРДЯНСЬК-2013

 

ЗМІСТ

 

ВСТУП……………………………………………………………………………..3

1. Поняття про статистичні  гіпотези……………………………………………..4

2. Перевірка гіпотези про вид закону розподілу досліджуваної величини……6

3. Перевірка гіпотези  про рівність генеральних дисперсій. F-критерій (Фішера)……………………………………………………………………………8

4. Перевірка статистичних  гіпотез із використанням Microsoft Excel………...9

5. Доведення статистичної гіпотези в середовищі EXCEL…………………...11

ВИСНОВКИ……………………………………………………………………..12

СПИСОК ВИКОРИСТАНОЇ ЛІТЕРАТУРИ…………………………………..13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ВСТУП

Розвиток сучасної науки  характеризується її математизацією, що виражається у використанні математичних методів і моделей не тільки у технічних та економічних дослідженнях, але й у менеджменті, соціології, педагогіці, біології, медицині. В соціальних науках використовуються різноманітні статистичні методи для перевірки висунутих гіпотез, побудови статистичних моделей соціальних та економічних об’єктів, явищ, закономірностей і процесів. Зазвичай застосовують пакета Excel під час перевірки статистичних гіпотез використовуючи методи статистичної обробки даних «Двох-вибірковий z-тест для середніх», “Двох-вибірковий t-тест с різноманітними дисперсіями», «Двох-вибірковий F-тест для дисперсії», які входять у Пакет аналізу, пункт Аналіз даних.

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

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

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

Усі книги-файли Excel мають  розширення .xls, .xlsm, .xlsb, .xlsx.

1. Поняття про статистичні гіпотези

 

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

За змістом статистичні  гіпотези можна віднести до таких  типів:

1) Гіпотези про вид  закону розподілу досліджуваної величини.

2) Гіпотези про числові  характеристики досліджуваної величини.

3) Гіпотези про рівність  числових характеристик досліджуваних  величин.

4) Гіпотези про належність  досліджуваних величин до одній  генеральної

сукупності.

5) Гіпотези про вид  моделі, що описує взаємозв’язок між досліджуваними величинами.

6) Гіпотези про належність  досліджуваних величин до одного  класу.

Статистичні гіпотези позначаються латинськими буквами Н₀, Н₁, і т.д. Гіпотеза Н₀ формулюється як основна в тому розумінні, що при перевірці бажано було б встановити її справедливість. Основній гіпотезі Н₀ протиставляються інші гіпотези Н₁, Н₂, …, які називаються альтернативними.

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

Якщо сформульовані  гіпотези Н₀ – основна та Н₁ альтернативна (конкуруюча) і обраний критерій перевірки справедливості основної гіпотези, то прийняття Н₀ означає відкидання Н₁, а відкидання Н₀ означає справедливість Н₁.

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

Ймовірність відкидання гіпотези Н₀, якщо вона справедлива, називається ймовірністю помилки першого роду або рівнем значущості і позначається α.

Величина 1− α є ймовірністю прийняття справедливої гіпотези і називається рівнем довіри. Ймовірність прийняття гіпотези Н₀, якщо вона не вірна, називається ймовірністю помилки другого роду і позначається β. Величина 1− β є ймовірністю відкидання невірної гіпотези і називається потужністю критерію.

Чим менше значення рівня  значущості, тим менша ймовірність  відкинути вірну гіпотезу. Зазвичай рівень значущості обирається дослідником рівним 0,1; 0,05; 0,01 або 0,001.

Якщо, наприклад, обраний  рівень значущості α = 0,01, то ризик відкинути вірну гіпотезу виникає в одному випадку із ста.

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

Перевірка статистичних гіпотез здійснюється за етапами, вказаним на рис. 1.

ЕТАПИ ПЕРЕВІРКИ СТАТИСТИЧНИХ ГІПОТЕЗ		1) Висунення  припущень про вид розподілу досліджуваної величини (величин) або про її числові характеристики.
		2) Формулювання  статистичних гіпотез.
		3) Вибір критерію  перевірки відповідно до змісту  гіпотез і статистичних даних.
		4) Вибір рівня  значущості залежно від вимог  до точності результатів дослідження.
		5) Розрахунок  значення обраного критерію за  статистичними даними.
		6) Порівняння  розрахованого значення критерію  з його критичним значенням і прийняття або відкидання основної гіпотези.

 

Рис.1. Етапи перевірки статистичних гіпотез

2. Перевірка гіпотези  про вид закону розподілу досліджуваної величини

 

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

Припустимо, що з деякої генеральної сукупності Х, яка розглядається як випадкова величина, обрана вибірка {х₁, х₂, …х_т}. За даними вибірки побудовано статистичний ряд (табл. 1), що містить варіанти х_i та відповідні частоти п_i, i 1,k , де k – кількість варіант у випадку дискретного ряду. У випадку інтервального ряду х_i – середини інтервалів, k – кількість інтервалів.

Таблиця 1

хi	х1	х2	…	хk
пi	п1	п2	…	пk

 

Отриманий на основі вибіркових даних статистичний ряд називається емпіричним законом розподілу величини Х.

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

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

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

Критерій Пірсона Х ² обчислюється за формулою:

                                     (2.1)

де  n´_і – частоти, отримані за теоретичним законом розподілу (теоретичні).

З формули (2.1) видно, що у  випадку, коли відповідні теоретичні та емпіричні частоти співпадають, χ² = 0. Тобто, чим ближче χ² до нуля, тим краще узгоджуються вибіркові дані та обраний теоретичний закон розподілу.

Розраховане значення критерія χ² порівнюється з його критичним значенням Х ²_{а, 1}, яке знаходиться за статистичними таблицями, або за допомогою вбудованої статистичної функції Excel ХИ2ОБР (а , l), або за допомогою описових статистик пакету програм SPSS. Параметрами функції ХИ2ОБР є:

α – рівень значущості;

l – степінь свободи, l = k – r – 1,

де k – кількість груп емпіричного розподілу,

r – кількість параметрів теоретичного розподілу (наприклад, для нормального розподілу r = 2, оскільки параметрів два – а і σ).

Якщо Х² < Х²_а,_l, то гіпотеза про закон розподілу приймається. У противному випадку гіпотеза відкидається.

У деяких статистичних таблицях критичне значення χ² надається залежно від рівня довіри γ, а γ =1− а.

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

1) З генеральної сукупності Х формується вибірка і будується статистичний ряд.

2) Висувається гіпотеза  про закон розподілу випадкової  величини Х.

3) Знаходяться вибіркові параметри обраного закону розподілу.

4) Розраховуються теоретичні  частоти.

5) Розраховується критерій  χ² за формулою (2.1).

6) Обирається рівень  значущості а (або рівень довіри γ ) і знаходиться критичне значення Х ² _а,l (або Х ² _γ,l).

7) Порівнюються розраховане і критичне значення критерію χ² і робиться висновок про справедливість запропонованої гіпотези.

 

 

 

3. Перевірка гіпотези  про рівність генеральних дисперсій. F-критерій (Фішера)

Перевірка гіпотези про  рівність генеральних дисперсій  здійснюється за F-критерієм (Фішера) тільки тоді, коли статистичні дані незалежні і розподілені за нормальним законом. Формулюються гіпотези:

Н₀ – дисперсії двох нормально розподілених генеральних сукупностей рівні, тобто ;

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

F-критерій (Фішера) розраховується за формулою:

,                                           (2.2)

Гіпотеза Н₀ приймається, якщо розраховане значення F менше критичного значення розподілу Фішера F_крит, взятого із рівнем значущості і ступенями волі l₁ та l₂ для чисельнику і знаменнику відповідно: l₁=п₁ – 1, l₂=п₂ – 1, де п₁, п₂ – об’єми вибірок. F_крит можна знайти за допомогою вбудованої статистичної функції Excel FРАСПОБР ( ; l₁; l₂).

Дисперсія у чисельнику дроби у формулі (2.2) повинна бути більше дисперсії у знаменнику, тобто  значення F-критерію повинно бути більше одиниці.

 

4. Перевірка статистичних гіпотез із використанням Microsoft Excel

Двох-вибірковий F-тест для дисперсій

 

Перевірку гіпотези про  рівність генеральних дисперсій  за F-критерієм

(Фішера) можна виконати  за допомогою пакета аналізу  Microsoft Excel. Для

використання пакета необхідно:

1) Вибрати в меню  послідовно пункти Сервіс – Аналіз даних, після чого з’явиться вікно для вибору інструмента аналізу (рис. 2).

 

 

 

 

 

 

Рис. 2. Діалогове вікно аналізу даних

2) Вибрати у діалоговому  вікні інструмент Двох-вибірковий F-тест для

дисперсії, після чого з’явиться вікно для вибору параметрів (рис. 2.3).

3) Задати всі необхідні  параметри і натиснути ОК.

 

 

 

 

 

 

 

 

 

Рис. 3. Вікно надання параметрів

Приклад і результат  роботи тесту продемонстровано на рис. 4.

 

 

 

 

 

 

 

 

 

 

Рис.4. Перевірка рівності генеральних дисперсій

 

Двох-вибірковий t-тест для середніх

Перевірку гіпотез про  рівність генеральних середніх за критерієм Стьюдента можна виконати за допомогою пакета аналізу даних Microsoft Excel.

Для здійснення перевірки  необхідно у вікні для вибору інструмента аналізу (рис. 2) виконати:

1) У випадку рівних  генеральних дисперсій – інструмент

Двох-вибірковий t-тест с однаковими дисперсіями;

2) У випадку різних  генеральних дисперсій – інструмент

Двох-вибірковий t-тест з різними дисперсіями;

3) У випадку залежних вибірок – Парний двох-вибірковий t-тест для середніх.

Після вибору інструмента  аналізу з’явиться вікно для  вибору параметрів аналізу, аналогічно зображеному на рис. 3, в якому необхідно задати масиви чарунок із вхідними вибірковими даними і рівень значущості (стандартний рівень – 0,05). Приклад і результат роботи тесту наведено на рис 5.

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 5. Перевірка рівності генеральних середніх

 

 

5. Доведення статистичної гіпотези в середовищі EXCEL

 

У процесі обробки  матеріалів дослідження психолог висуває гіпотези, які систематизують його припущення і представляють їх у чіткому і лаконічному вигляді. Статистичні гіпотези діляться на нульові  й альтернативні. Рівень значущості – це вірогідність відхилення нульової гіпотези, тоді як вона правильна. Іншими словами – це упевненість в прийнятті гіпотези. Тобто, чим вищий рівень значущості, тим вища упевненість в правильності прийняття гіпотези  і тим менша вірогідність зробити в процесі цього помилку. У практиці статистичних вимірів у психології прийнято вважати нижчим рівнем статистичної значущості 5% рівень; достатнім 1% рівень і вищим рівнем 0,1% .

Прийняття тієї або іншої  статистичної гіпотези здійснюється за допомогою статистичних критеріїв. Статистичний критерій – це вирішальне правило прийняття достеменної і відхилення помилкової гіпотези з високою ймовірністю. Критерії діляться на параметричні та непараметричні.

ВИСНОВКИ

 

У висновку можна зазначити, що засобами Excel можна повністю вирішити важливі задачі теорії імовірності, математичної статистики перевірити статистичні гіпотези.

Табличний процесор MS Excel надає можливість перевірки статистичних гіпотез щодо рівня середньої сукупності з нормальним законом розподілу за допомогою функції = ZTECT(), яка повертає значення 1-_ремп.

У якості аргументів функції виступають:

- вибірковий масив;

- математичне очікування;

- стандартне відхилення генеральної сукупності (у разі відсутності останньої використовується вибіркова статистика).

Алгоритми Excel корисні в статистичних обчисленнях: введення даних, редагування графіків за допомогою майстра діаграм, оформлення результатів та підготовка до друку.

Вибір електронної таблиці Excel (пакет Microsoft Office) в аспекті використання для перевірки статистичних гіпотез зумовлено тим, що вона широко поширена і, отже, доступна.

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

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

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

СПИСОК ВИКОРИСТАНОЇ ЛІТЕРАТУРИ

 

1. Агабекян Р. Л. Математические методы в социологии. Анализ данных и логика вывода в эмпирическом исследовании: учеб. пособ. для вузов/Р. Л. Агабекян, М. М. Кириченко, С. В. Усатиков. − Ростов н/Д: Феникс, 2005. − 192 с.
2. Барковський В.В., Барковська Н.В., Лопатін O.K. Теорія ймовірностей та математична статистика. - Київ: Центр навчальної літератури, 2006. - 424 с. 
3. Бутник О. М. Економіко-математичне моделювання перехідних процесів у соціально-економічних системах: [монографія]/Бутник О.М. – Х.: Видавничий Дім „ІНЖЕК”; СПД Лібуркіна Л. М., 2004. – 304 с.
4. Василенко О. А. Матемачно-статистичні методи аналізу у прикладних дослідженнях: навч. посіб. / О. А. Василенко, І. А. Сенча. – Одеса: ОНАЗ ім. О. С. Попова, 2011. – 166 с.
5. Гмурман В.Е. Теория вероятностей и математическая статистика. М. Высш. школа, 2003 г.
6. Додж. М., Кината К., Стинсон К. Эффективная работа с Microsoft Excel 97. – СПб.: Питер, 1998. – 1072 с.
7. Екимов С. В. Нетрадиционные подходы в экономико-математическом моделировании: [монография] / Екимов С. В. – Днепропетровск: Наука и образование, 2004. – 240 с.
8. Ермолаев О. Ю. Математическая статистика для психологов: [учебник] / Ермолаев О. Ю. – [2-е изд. испр.]. – М.: Московский психолого-социальный институт Флинта, 2003. – 336 с.
9. Карагодова О. О. Дослідження операцій: навч. посіб./ О.О. Карагодова, В. Р. Кігель, В. Д. Рожок. – К.: Центр учбової літератури,2007. – 256 с.
10. Макаренко Т. І. Моделювання та прогнозування у маркетингу: навч. посіб. / Макаренко Т. І. – К.: Центр навчальної літератури, 2005. – 160 с.
11. Минько А. А. Статистический анализ в MS Excel / Минько А. А. – M.: Изд. дом «Вильямс», 2004. – 448 с.