АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция

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

Читайте также:
  1. E. Використання антисептичних засобів
  2. IІІ. Проведення перевірок суб’єктів господарювання та органів влади та інших підконтрольних об’єктів органами Держтехногенбезпеки України
  3. Microsoft Antivirus
  4. Microsoft Business Solutions – Axapta
  5. Microsoft Excel
  6. Microsoft Excel 8.0 Отчет по устойчивости
  7. Microsoft Excel 8.0 Отчет по устойчивости
  8. Microsoft Internet Explorer
  9. Microsoft Internet Explorer
  10. Microsoft Internet Explorer
  11. Microsoft Word для Windows
  12. V. Робочий час і його використання

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

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

 

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

 

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

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

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

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

§ стандартне відхилення 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, натискаємо «ОК». Таким самим чином вводимо інші функції.

 

Характеристика Функція
Об'єм вибірки СЧЕТ(масив даних)
Вибіркове середнє СРЗНАЧ(масив даних)
Дисперсія ДИСП (масив даних)
Стандартне відхилення СТАНДОТКЛОН(масив даних)
Медіана МЕДІАНА(масив даних)
Мода МОДА(масив даних)
Коефіцієнт ексцесу ЕКСЦЕС(масив даних)
Коефіцієнт асиметрії СКІС(масив даних)
Перцентиль 40% ПЕРСЕНТИЛЬ(масив даних; 0,4)
Перцентиль 80% ПЕРСЕНТИЛЬ(масив даних; 0,8)

 

Існує інший спосіб обчислення числових характеристик вибірки. Для цього ставимо курсор у вільну комірку (наприклад, 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 - число оцінюваних параметрів закону розподілу. Розподіл можна вважати відповідним теоретичному якщо виконується умова χ2 < χ2 kr. Розглянемо розв'язок даної задачі на прикладі.

 

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

 

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

 

Вибірка довжини тіла 40 особин молоді плотви, мм:  
                                       
                                       

 

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

 

Комірка С2 С3 С4 С5 С6 С7 С8 С9 C10 C11
Число                    

 

Для обчислення частот 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 вважати, що довжина кінцівок у них різна.

 

Популяція 1 47,5 52,9 51,3 48,1 52,6 49,4 48,0 52,3 45,9 52,6 46,8 49,0
Популяція 2 52.5 50,5 48,4 48,6 50,6 50,0 50,1 49,5 49,7 51,1 49,2 49,7

 

За умовою задачі критерій двосторонній, тому що потрібно перевірити відмінність дисперсій. Довірча ймовірність задана на рівні р=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-М2 електронної книги Excel. Переглянемо попередньо, як лежать точки на графіку і яке рівняння регресії краще вибрати. Для цього будуємо графік. Викликавши майстер діаграм і вибравши тип діаграми «Крапкова» натискаємо «Далі» і помістивши курсор у поле «Діапазон» обводимо курсором дані Y (комірки В2-М2). Переходимо на закладку «Ряд» і в поле «Значення X» робимо посилання на комірки В 1-М1, обводячи їх курсором. Натискаємо «Готово».

 

 

Як видно із графіка, точки добре укладаються на пряму лінію, тому будемо знаходити рівняння лінійної регресії виду y = ах + b.

Для знаходження коефіцієнтів а й b рівняння регресії служать функції НАКЛОН і ОТРЕЗОК, категорії «Статистичні». Уводимо в А5 підпис «а=» а в сусіднє комірка В5 уводимо функцію НАКЛОН, ставимо курсор у поле «Изв_знач_y» задаємо посилання на комірки В2-М2, обводячи їх мишею. Аналогічно в поле «Изв_знач_х» вводимо посилання на В1-М1. Результат 1,923921. Знайдемо тепер коефіцієнт b. Уводимо в А6 підпис «b=», а в В6 функцію ОТРЕЗОК з тими ж параметрами, що й у функції НАКЛОН. Результат 12,78151. Отже, рівняння лінійної регресії є y = 1,92х +12,78.

Побудуємо графік рівняння регресії. Для цього в третій рядок таблиці введемо значення функції регресії в заданих точках X (перший рядок) – y (xi). Для одержання цих значень використовується функція ТЕНДЕНЦІЯ категорії «Статистичні». Уводимо в A3 підпис Y(X) і, помістивши курсор у В3, викликаємо функцію ТЕНДЕНЦІЯ. У поля «Изв_знач_y» і «Изв_знач_х» вводимо посилання на В2-М2 і В1-М1. У поле «Нове_знач_х» уводимо також посилання на В1-М1. У поле «Константа» уводять 1, якщо рівняння регресії має вигляд у = ах + b, і 0, якщо у = ах. У нашому випадку вводимо одиницю. Функція ТЕНДЕНЦИЯ є масивом, тому для висновку всіх її значень виділяємо область В3-М3 і натискаємо F2 і Ctrl+Shift+Enter. Результат — значення рівняння регресії в заданих точках. Будуємо графік. Ставимо курсор у будь-яку вільну комірку, викликаємо майстер діаграм, вибираємо категорію «Точкова», вид графіка — лінія без точок (у нижньому правому куті), натискаємо «Далі», у поле «Діапазон» уводимо посилання на В3-М3. Переходимо на закладку «Ряд» і в поле «Значення X» уводимо посилання на B1-M1, натискаємо «Готово». Результат — пряма лінія регресії. Подивимося, як різняться графіки дослідних даних і рівняння регресії. Для цього ставимо курсор у будь-яке вільну комірку, викликаємо майстер діаграм, категорія «Графік», вид графіка — ламана лінія із крапками (друга зверху ліва), натискаємо «Далі», у поле «Діапазон» уводимо посилання на другу й третю рядки В2-М3. Переходимо на закладку «Ряд» і в поле «Підпису осі X» уводимо посилання на B1-M1, натискаємо «Готово». Результат - дві лінії (Синя - вихідні дані, червона - рівняння регресії). Видно що лінії мало різняться між собою.

Для обчислення коефіцієнта кореляції r служить функція ПИРСОН. Розміщаємо графіки так, щоб вони розташовувалися вище 25 рядки, і в А25 робимо підпис «Кореляція», в В25 викликаємо функцію ПИРСОН, у полях якої «Масив 1» і «Масив 2» уводимо посилання на вихідні дані B1-M1 і В2-М2. Результат 0,993821. Коефіцієнт детермінації R — це квадрат коефіцієнта кореляції r. В А26 робимо підпис «Детермінація», а в В26 – формулу =В25*В25. Результат 0,987681.

Однак, в Excel існує одна функція, яка розраховує всі основні характеристики лінійної регресії. Це функція ЛИНЕЙН. Ставимо курсор в В28 і викликаємо функцію ЛИНЕЙН, категорії «Статистичні». У полях «Изв_знач_у» і «Изв_знач_х» даємо посилання на В2-М2 і В1-М1. Поле «Константа» має той же зміст, що й у функції ТЕНДЕНЦІЯ, у нас вона рівна 1. Поле «Стат» повинне містити 1, якщо потрібно вивести повну статистику про регресію. У нашому випадку ставимо туди одиницю. Функція повертає масив розміром 2 стовпця й 5 рядків. Після введення виділяємо мишею комірки В28-С32 і натискаємо F2 і Ctrl+Shift+Enter. Результат - таблиця значень, числа в якій мають наступний сенс:

 

Коефіцієнт а Коефіцієнт b
Стандартна помилка тy Стандартна помилка тx
Коефіцієнт детермінації R Среднеквадратическое відхилення в
F - статистика Ступені свободи п-2
Регресійна сума квадратів S2b Залишкова сума квадратів S2a

 

Аналіз результату: у першому рядку - коефіцієнти рівняння регресії, зрівняєте їх з розрахованими функціями НАКЛОН і ОТРЕЗОК. Другий рядок - стандартні помилки коефіцієнтів. Якщо одна з них по модулю більше чому сам коефіцієнт, то коефіцієнт уважається нульовим. Коефіцієнт детермінації характеризує якість зв'язку між факторами. Отримане значення 0,987681 свідчить про дуже гарний зв'язок факторів. F - статистика перевіряє гіпотезу про адекватність регресійної моделі. Дане число потрібно зрівняти із критичним значенням, для його одержання вводимо в Е33 підпис «F-Критичне», а в F33 функцію FPAСПРОБP, аргументами якої вводимо відповідно «0,05» (рівень значимості), «1» (число факторів X) і «10» (ступені свободи). Видне, що F- статистика більше, чим F- критичне, значить я модель адекватна. В останньому рядку наведені регресійна сума квадратів

 

і залишкові суми квадратів

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

 


 

 

ДОДАТКИ

Таблиця 1.


1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |

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



Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Студалл.Орг (0.016 сек.)