Тема 8. Використання табличного процессору Microsoft Excel для проведення статистичних розрахунків

8.1 Точкове й інтервальне оцінювання параметрів розподілів

Для дослідження основних властивостей явища або об'єкта, представленого вибіркою обчислюють точкові та інтервальні оцінки.

8.1.1. Точкове оцінювання

Точкові оцінки параметрів розподілу це оцінки, отримані по вибірці й приблизно рівні оцінюваним параметрам. Основними точковими оцінками є:

§ об'єм вибірки п - кількість елементів у вибірці.

§ вибіркове середнє х - оцінка математичного очікування, середньоарифметичне елементів вибірки.

§ вибіркова дисперсія S² - середнє квадратів відхилення елементів вибірки від вибіркового середнього, є оцінкою дисперсії, характеризує розкид вибіркових значень.

§ стандартне відхилення S - корінь із дисперсії.

§ медіана h - середній елемент варіаційного ряду або напівсума двох середніх елементів, якщо об'єм вибірки парний.

§ мода d - найбільше часто повторюваний елемент.

§ коефіцієнт ексцесу δ - характеризує «островершинність» гістограми або полігона в порівнянні із кривій Гауса нормального розподілу.

§ коефіцієнт асиметрії γ - характеризує ступінь симетричності гістограми або полігона.

§ перцентиль на рівні р - значення tp, менше якого р * 100% елементів вибірки.

Розбір вирішення задачі

Є вибірка ваги двотижневих курчат, г: 43, 38, 34, 51, 47, 45, 41, 52, 50, 38, 43, 44, 39, 46, 49, 42, 42, 38, 53, 55, 48, 45, 41, 49, 47. Знайти основні числові характеристики вибірки.

Запускаємо програму EXCEL, перший аркуш. Уводимо вихідні дані в комірки А1-А25. Знаходимо числові характеристики. Для введення функцій виділяємо два стовпці, наприклад В й С, у першому вводимо назву характеристики, у другому - функцію. У комірки B1-B11 уводимо підписи числових характеристик, тобто вписуємо в ці комірки перший стовпець таблиці наведеної нижче. В C1 уводимо текст «Функція» і нижче визначаємо функції, відповідні до назви (із другого стовпчика таблиці). Усі функції викликаються натисканням на кнопку <fx> (ліворуч від рядка введення), перебувають у категорії «Статистичні» і в якості масиву даних (поле «ЧИСЛО 1»), вказується посилання на А1-А25. Наприклад, для введення першої з них ставимо курсор у З2, натискаємо <fx>, вибираємо категорію «Статистичні» і функцію «СЧЕТ», у вікні, що відкрилося, ставимо курсор у поле «Число 1» і обводимо курсором комірки А1-А25, натискаємо «ОК». Таким самим чином вводимо інші функції.

Існує інший спосіб обчислення числових характеристик вибірки. Для цього ставимо курсор у вільну комірку (наприклад, D1). Потім викликаємо в меню «Сервіс» підміню «Аналіз даних» (Data Analysis). Якщо в меню «Сервіс» відсутній цей пункт, то в меню «Сервіс» потрібно вибрати пункт «Надбудови» і в ньому поставити прапорець напроти пункту «Пакет аналізу» (Analysis Toolpak). Після цього в меню «Сервіс» з'явиться «Аналіз даних» (Data Analysis). У вікні «Аналіз даних» потрібно вибрати пункт «Описова статистика» (Descriptive Statistics). У вікні, що з'явилося, у полі «Вхідний інтервал» (Input Range) робимо посилання на вибірку А1-А25, поміщаючи курсор у поле й обводячи ці комірки. Залишаємо групування «По стовпцях» (Columns). У розділі «Параметри висновку» (Output Options) ставимо прапорець на «Вихідний інтервал» (Output Range) і в сусідньому полі задаємо посилання на верхнє ліве комірка області висновку (наприклад D1), ставимо прапорець напроти «Описова статистика» (Summary Statistics), натискаємо «ОК». Результат - основні характеристики вибірки (зробіть ширше стовпець D, перемістивши його границю в заголовку).

8.1.2. Інтервальне оцінювання

Розглянемо тепер методи інтервального оцінювання. Довірчим інтервалом називається інтервал (a; b), у який із заданою ймовірністю р попадає оцінюваний параметр. Імовірність р називається довірчої. Замість неї часто задають величину α = 1 - р, називану рівнем значущості. Якщо вибірка об'єму m представляє випадкову величину, розподілену нормально, то довірчі інтервали для математичного очікування й дисперсії рівні.

де tp(n) і χр(n) - квантилы розподілу Ст’юдента та хі-квадрат, α = 1 - р.

Вертаємося на аркуш 1 електронної таблиці з даними прикладу й для них обчислимо довірчі інтервали при р= 0,05. Уводимо дані згідно з малюнком:

Для обчислення величини

слугує функція «ДОВЕРИТ» категорії «Статистичні» із трьома параметрами «Альфа» - рівень значимості α = 1 – р, «Станд. откл» – середньоквадратичне відхилення (СКО) S, «Розмір» - об'єм вибірки n. Таким чином, уводимо в Н3 функцію:

=СРЗНАЧ(А1:А25)-ДОВЕРИТ(І1;СТАНДОТКЛОН(А1:А25);25)

а в комірку І3 функцію:

=СРЗНАЧ(А1:А25)+ДОВЕРИТ(І1;СТАНДОТКЛОН(А1:А25);25)

Для обчислення довірчого інтервалу для дисперсії слід зазначити, що функція обчислення квантилі розподілу хі-квадрат (зворотного розподілу хі-квадрат) називається «ХИ2ОБР» (категорія «Статистичні») і має два параметри: перший «Імовірність» містить довірчу ймовірність р, другий - ступінь свободи n–1. Уводимо відповідно до даних умов і формулою для довірчого інтервалу в комірка Н4 запис: =ДИСП(А1:А25)*24/ХИ2ОБР(0,025;24), а в комірку І4 запис: =ДИСП(А1:А25)*24/ХИ2ОБР(0,975;24). Одержуємо значення границь довірчих інтервалів.

8.2 Перевірка статистичних гіпотез про вид розподілу

Методи перевірки статистичних гіпотез займають центральне місце в дослідженнях математичної статистики. Однією з найважливіших груп критеріїв перевірки статистичних гипотез є критерії перевірки про вид розподілів (критерії згоди). Вони за вибірковим даними перевіряють припущення про приналежність генеральної сукупності до того чи іншого виду розподілів. Одним з найбільш потужних критеріїв згоди є критерій Пірсона, називаний ще критерієм хі-квадрат. Його суть полягає в порівнянні теоретичних частот елементів вибірки ni (для дискретних розподілів) з теоретичними частотамиn’i = npi, де pi - імовірність прийняти це значення, розраховане за досліджуваним законом розподілу. Якщо розподіл безперервний, то будується групований статистичний ряд з k інтервалів і Pi = F(bі) - F(ai) існує ймовірність потрапити в i-й інтервал угруповання (тут F(x) - функція розподілу закону, що перевіряється). Статистикою критерію є величина

Критичне значення критерію дорівнює зворотному розподілу хі-квадрат зі ступенями волі (k-r-1)

де r - число оцінюваних параметрів закону розподілу. Розподіл можна вважати відповідним теоретичному якщо виконується умова χ² < χ² _kr. Розглянемо розв'язок даної задачі на прикладі.

Розбір вирішення задачі

Є вибірка довгі тіла 40 особин молоди плотви, мм. Необхідно перевірити статистичну гіпотезу про те, що довга тіла молоди плотви в даній вибірці розподілена за нормальним законом розподілу. Побрати рівень значимості α=0,05.

Для перевірки гіпотези про приналежність генеральної сукупності нормальному виду розподілів необхідно побудувати групований статистичний ряд, тому що нормальний розподіл є безперервним. Для цього потрібно знати розмах вибірки, який дорівнює різниці між максимальним і мінімальним елементами вибірки. Крім того, потрібно розрахувати крапкові оцінки математичного очікування та СКО. Відкриваємо електронну таблицю й уводимо дані вибірки в комірки А2-А41, робимо підписи для розрахункових параметрів відповідно до малюнка:

Обчислюємо параметри по вибірці. Для цього вводимо в комірку В3: =РАХУНОК(А2:А41). В В5 уводимо: =МАКС(А2:А41), в В7: =МІН(А2:А41), в В9: =СРЗНАЧ(А2:А41), в B11: =СТАНДОТКЛОН(А2:А41).

Видно, що весь діапазон значень елементів лежить на інтервалі від 47 до 88. Розіб'ємо цей інтервал на інтервали: [0; 50], (50; 55], (55; 60], (60; 65], (65; 70], (70; 75], (75; 80], (80; 85], (85; 90]. Для цього вводимо в комірки С2-С11 границі інтервалів:

Для обчислення частот n використовуємо функцію ЧАСТОТА. Для цього в D3 уводимо формулу =ЧАСТОТА(А2:А41;СЗ:З11). Потім обводимо курсором комірки D3-D11, виділяючи їх і натискаємо F2, а потім одночасно Ctrl+Shift+Enter. У результаті в комірких D3-D11 виявляться значення частот.

Для розрахунків теоретичної ймовірності pi =F(bi)-F ( ai ) уводимо в комірку Е3 різницю між функціями нормального розподілу (функція HOPMPАСП категорії «Статистичні») з параметрами: «X» - значення границі інтервалу, «Середнє» - посилання на комірку В9, «Стандартне відхілення» - посилання на B11, «Інтегральна» - 1. У результаті в Е3 буде формула: =HOPMPАСП(СЗ; $В$9; $В$11;1)-НОРМРАСП(З2; $В$9; $В$ 11; 1) Автозаповненням водимо цю формулу у комірки Е3-Е10 переміщаючи нижній правий кут Е3 до комірки Е10. В останньому гнізді стовпця E11 для дотримання умови нормування вводимо доповнення попередніх імовірностей до одиниці. Для цього вводимо в E11: = 1-СУМ(Е3:Е10)

Для розрахунків теоретичної частоти n’i = npi уводимо в F3 формулу: =ЕЗ*$В$3, та за допомогою автозаповнення копіюємо її на F3-F11.

Для обчислення елементів суми

критерію Пірсона вводимо в G3 значення =(D3-F3)*(D3-F3)/F3 і автозаповнюєм його на діапазон G3-G11.

Знаходимо значення критерію χ2 і критичне значення χ2kr. Для цього вводимо в F12 підпис «Сума», а в F13 підпис «Критич.». Уводимо в сусідні комірки формули - в G12: =CYMM(G3:G11), а в G13: =ХИ2ОБР(0,05;6), тут параметр α=0,05 узятий з умови, a ступінь свободи(k–r—1)=(9–2 –1)=6, тому що k=9 число інтервалів, а r=2, тому що були оцінені два параметри нормального розподілу: математичне очікування й СКО. Видно, що χ2 < χ2kr, тобто можна вважати, що довга тіла особин плотви в даній вибірці розподілена за нормальним законом розподілу.

Перевіримо це, побудувавши графіки щільностей емпіричного й теоретичного розподілів. Ставимо курсор у будь-яку вільну комірку й викликаємо майстер діаграм (Вставка/Діаграма). Вибираємо тип діаграми «Графік» і вид «Графік з маркерами» самий лівий у другому рядку, натискаємо «Далі». Ставимо курсор у поле «Діапазон» і втримуючи кнопку CTRL обводимо мишею область гнізд D3-D11 а потім F3-F11. Переходимо на закладку «Ряд» і в поле «Підпису осі X» обводимо область С3-С11. Натискаємо «Готово». Видно, що графіки досить добре збігаються, що свідчить про відповідність даних нормальному закону.

8.3 Перевірка гіпотез про рівність дисперсій і математичних очікувань

8.3.1. Критерій Фишера для порівняння дисперсій

Використовується у випадку, якщо потрібно перевірити чи відрізняється розкид даних (дисперсії) двох вибірок. Для перевірки статистичної гіпотези про рівність дисперсій служить F- критерій Фішера. Основною характеристикою критерію є рівень значущості α, який має змісту ймовірності помилитися, припускаючи, що дисперсії й, отже, точність, різняться. Замість а в задачах також іноді задають довірчу ймовірність р = 1 – α, що має зміст імовірності того, що дисперсії й насправді рівні. Звичайно вибирають критичне значення рівня значимості, наприклад 0,05 або 0,1, і якщо α більше критичного значення, то дисперсії вважаються рівними, а якщо ні, то, різні. При цьому критерій може бути однобічним, коли потрібно перевірити, що дисперсія конкретної виділеної вибірки більше, чим в інший, і двостороннім, коли просто потрібно показати, що дисперсії не рівні. Існує два способи перевірки таких гіпотез. Розглянемо їх на прикладах.

Розбір вирішення задачі

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

За умовою задачі критерій двосторонній, тому що потрібно перевірити відмінність дисперсій. Довірча ймовірність задана на рівні р=0,95, отже, рівень значущості α = l – p = 1 – 0,95 = 0,05. Уводимо дані вибірок (без підписів) у два рядки в комірки A1-L1 і A2-L2 відповідно. Для обчислення рівня значущості двостороннього критерію служить функція ФТЕСТ(масив1;масив2). Уводимо в А4 підпис «Рівень значцщості», а в В4 функцію ФТЕСТ, аргументами якої повинні бути посилання на комірки A1-L1 і A2-L2 відповідно. Результат 0,011591293 говорить про те, що ймовірність помилитися, прийнявши гіпотезу про відмінність дисперсій, близько 0,01, що менше критичного значення, заданого в умові задачі 0,05. Отже, можна говорити що досвідчені дані з великою ймовірністю підтверджують припущення про те, що дисперсії різні й довга кінцівок в особин із двох популяцій озерної жаби різна.

Інший спосіб розв'язку задачі - використовувати надбудову «Аналіз даних» (Data Analysis). Для її підключення потрібно в меню «СЕРВІС» вибрати «НАДБУДОВИ» і поставити прапорець напроти «Пакет аналізу» (Analysis Toolpak). Після цього в меню «СЕРВІС» з'явиться пункт «АНАЛІЗ ДАНИХ» (Data Analysis). Викликавши його, відкриється вікно, у якім потрібно вибрати «Двухвивірковий F-Тест для дисперсій» (F-test Two-Sample for Variances). У вікні, що відкрилося, у полях «Інтервал змінної 1» (Variable 1 Range) і «Інтервал змінної 2» (Variable 1 Range) уводять посилання на дані (A1-L1 і А2- L2, відповідно), якщо є підписи даних, то ставлять прапорець у напису «Мітки» (Label) (у нас вони відсутні, тому прапорець не ставиться). Далі вводять рівень значимості в поле «Альфа» (Alpha) (по умови це 0,05, і дане значення вже зазначене за замовчуванням). У розділі «Параметри висновку» (Output Options) ставлять мітку близько «Вихідний інтервал» (Output Range) і помістивши курсор в, що з'явилося поле напроти напису, клацають лівою кнопкою в гнізді В7. Висновок результату буде здійснюватися починаючи із цього комірки. Нажавши на «ОК» з'являється таблиця результату. Зруште границю між стовпцями В и С, С и D, D і Е, збільшивши ширину стовпчиків В, С и D так, щоб уміщалися всі написи. У таблиці зазначені середні та дисперсії кожної вибірки, значення F-критерію, однобічний критичний рівень значимості в рядку «P(F<=f) однобічне» («P(F<=f) one-tail») і критичне значення F-критерію (F critical one tail). Якщо значення F- критерію ближче до одиниці, чому F-Критичне, то із заданою ймовірністю можна вважати, що дисперсії рівні. Про це ж говорить і те, що критичний рівень значимості «P(F<=f) однобічне» більше заданого значення а. У нашім випадку F-Критерій рівний 5,128330184 а F-Критичне 2,817927225, тобто F-Критерій далі від одиниці, чому критичне значення. Це говорить про те, що дисперсії різні й жаби із двох популяцій дійсно мають різну довжину передніх кінцівок.

8.3.2. Критерій Ст’юдента порівняння середніх

Використовується для перевірки припущення про те, що середні значення двох показників, представлених вибірками, суттєво різняться. Існує три різновиди критерію: один — для зв'язаних вибірок, і два для незв'язаних вибірок (з однаковими й різними дисперсіями). Якщо вибірки не зв'язані, то попередньо потрібно перевірити гіпотезу про рівність дисперсій, щоб визначити, який із критеріїв використовувати. Так само як і у випадку порівняння дисперсій є 2 способу розв'язку задачі, які розглянемо на прикладі.

Розбір вирішення задачі

Є дані про врожайність сільськогосподарської культури (ц/га) без використання та із використанням стимулятора росту.

Чи можна з імовірністю 0,99 уважати, що застосування стимулятора росту привело до середнього збільшення врожайності культури?

За умовою р= 0,99, α=0,01, вибірки не зв'язані, критерій однобічний, тому що потрібно показати, що середні показника, представленого другий вибіркою, більше чому в першої. Уводимо в комірки A1-M1 і A2-L2 вихідні дані. Т.к. вибірки незв'язані, те попередньо порівнюємо дисперсії. У результаті перевірки дисперсії виявляються рівними.

Перший спосіб розв'язку задачі, як і у випадку дисперсій, використовувати стандартну функцію. Нею є ТТЕСТ(масив1;масив2;хвости;тип), що вирішує задачу по t- критерію Ст’юдента. У комірку В4 уводимо підпис «t-критерій», а в сусідню З4 функцію TTECT (категорія «Статистичні») Аргументи функції:

§ масив1, масив2 - вихідні дані (посилання на Al-Ml і A2-L2);

§ хвости - вид критерію: якщо 1 - однобічний критерій, якщо 2 - двосторонній (у нашім випадку ставиться одиниця);

§ тип - тип критерію: якщо вибірки зв'язані, те 1, для незв'язаних вибірок з рівними дисперсіями - ставимо 2, для незв'язаних вибірок з нерівними дисперсіями ставимо 3. У нашім випадку дисперсії рівні, тому вибираємо 2.

Функція повертає критичне значення рівня значимості, що має зміст помилитися, прийнявши гіпотезу про відмінність середніх. Якщо критичне значення більше заданого, то середні потрібно вважати рівними. Результат у нашому випадку 0,0476828 більше заданого α = 0,01. Отже, застосування стимулятора росту не привело до середнього збільшення врожайності й зміни врожайності, найімовірніше, пов'язане з якимись випадковими факторами.

Другий спосіб - використовувати пакет «Аналіз даних» (Data Analysis). Спосіб виклику й підключення його був описаний у п.2. Залежно від типу критерію вибирається один із трьох: «Парний двовибірковий t-тест для середніх» (t-teat: Paired Two Sample for Means) — для зв'язаних вибірок, і «Двовибірковий t-тест із однаковими дисперсіями» (t-teat: Two Sample Assuming Equal Variances) або «Двовибірковий t-тест із різними дисперсіями» (t-teat: Two Sample Assuming Unequal Variances) - для незв'язаних вибірок. Викличте тест із однаковими дисперсіями, у вікні, що відкрилося, у полях «Інтервал змінної 1» (Variable 1 Range) і «Інтервал змінної 2» (Variable 2 Range) уводять посилання на дані (А 1-М1 і A2-L2, відповідно), якщо є підписи даних, то ставлять прапорець у напису «Мітки» (Label) (у нас їх ні, тому прапорець не ставиться). Далі вводять рівень значимості в поле «Альфа» (Alpha) - 0,01. Поле «Гіпотетична середня різниця» (Hypothesized Mean Difference) залишають порожнім. У розділі «Параметри висновку» (Output Options) ставлять мітку близько «Вихідний інтервал» (Output Range) і помістивши курсор в, що з'явилося поле напроти напису, клацають лівою кнопкою в гнізді В7. Висновок результату буде здійснюватися починаючи із цього комірки. Нажавши на «ОК» з'являється таблиця результату. Зруште границю між стовпцями В и С, С и D, D і Е, збільшивши ширину стовпців В, С и D так, щоб уміщалися всі написи. Процедура виводить основні характеристики вибірок, t-статистику (t-stat), критичні значення цих статистик і критичні рівні значимості «P(T<=t) однобічне» (P(T<=t) one-tail) і «P(T<=t) двостороннє» (P(T<=t) two-tail). Якщо по модулю t-статистика менше критичного, то середні показники із заданою ймовірністю рівні. У нашім випадку -1,739215668 < 2,499873517, отже, середнє число продажів значиме не збільшилося. Слід зазначити, що якщо обрати рівень значущості α=0,05, то результати дослідження будуть зовсім іншими.

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

Рівняння регресії будується для аналізу статистичних залежностей між двома або більш показників. Якщо показників два, то регресія називається парний. Якщо залежність між показниками X и Y пропорційна, то регресія буде лінійної й описується рівнянням виду у = ах + b. Розглянемо методику побудови регресійного рівняння на прикладі.

Розбір вирішення задачі

ДОДАТКИ

Таблиця 1.

Поиск по сайту: