Статистичні методи обробки даних

Лабораторна робота № 1
СТАТИСТИЧНІ МЕТОДИ ОБРОБКИ ДОСВІДЧЕНИХ ДАНИХ. ЧИСЛОВІ ХАРАКТЕРИСТИКИ ВБОРКІ
Мета: Навчитися основним методам обробки даних, представлених вибіркою. Вивчити графічні представлення даних. Оволодіти навичками розрахунку за допомогою ЕОМ основних числових характеристик вибірки.
Основним об'єктом дослідження в економетрики є вибірка. Вибіркою об'єму n називаються числа х _1. Х ₂ .... Х _n одержувані на практиці при n - короткому повторенні експерименту в незмінних умовах. На практиці вибірку найчастіше представляють статистичними поруч. Для цього вся числова вісь, на якій лежать значення вибірки, розбивається на k інтервалів (це число вибирається довільно від 5 до 10), які зазвичай рівні, обчислюються середини інтервалів z _n і вважається число елементів вибірки, що потрапляють в кожен інтервал n _1. статистичними поруч називається послідовність пар (z _1. n _1). Розглянемо рішення задачі на ЕОМ і ППП EXCEL на наступному прикладі.
ПРИКЛАД. Дана вибірка чисел виручки магазину за 30 днів:
Побудуємо статистичний ряд, полігон, гістограму і кумулятивну криву.
Відкриємо книгу програми EXCEL. Введемо в перший стовпець (комірки А1-А30) вихідні дані. Визначимо область чисел, на якій лежать дані. Для цього знайдемо максимальний та мінімальний елементи вибірки. Введемо в В1 «Максимум», а в В2 "мінімум", а в сусідніх комірках С1 і С2 визначимо функції «МАХ» і «мin», в якості аргументів яких (у графі «число») обведемо область даних (комірки А1-А30 ). Результатом будуть 64 і 81. видно, що всі дані укладаються на відрізку [64; 81]. Розділимо його на 9 (вибирається довільно від 5 до 10) інтервалів:
64-66; 66-68: 68-70: 70-72: 72-74, 74-76, 76-78, 78-80, 80-82. у клітинці D1-D10 вводимо верхні межі інтегралів угруповання - числа 66, 68, 70, 72, 74, 76, 78, 80, 82. Для обчислення частот n ₁ використовують функцію ЧАСТОТА, що знаходиться в категорії «Статистичні». Введемо її в комірку Е1. у рядку "Масив даних» введемо діапазон вибірки (комірки А1-А30). У рядку «Двійковий масив» введемо діапазон верхніх меж інтервалів групування (комірки D1-D9). Результат функції є масивом і виводиться в осередках Е1-Е9. для повного вибору (не тільки першого числа в Е1) потрібно виділити комірки Е1-Е9, обвівши їх мишею, і натиснути F2, а далі одночасно CTRL + SHIFT + ENTER. Результат - частоти інтервалів 2,2,5,7,3,7,3,0,1.
Для побудови гістограми потрібно вибрати ВСТАВКА / ДІАГРАМА або натиснути на відповідний значок на основній панелі (при цьому курсор повинен стояти у вільній клітинці) далі вибрати тип: гістограма, вид за вибіркою, натиснути «ДАЛІ», у рядку «ПІДПИСИ ОСІ Х» ввести інтервали осередках D1-D5, натиснути «ДАЛІ» ввести назву «Гістограма», підписи осей «ІНТЕВАЛИ» і «ЧАСТОТА», натиснути «ГОТОВО». Для створення полігону зробити те ж саме, тільки замість типу діаграми «Гістограма», вибрати «ГРАФІК». Для побудови кумулятивної кривої потрібно порахувати накопичені частоти. Для цього в комірку F1 вводимо «= Е1», в F2 - вводимо «= F1 + Е2» і автозаповненням перетаскиваем цю клітинку до F9. далі будуємо графік як і у випадку полігону, але в рядку «ДІАПАЗОН» вводимо накопичені частоти, посилаючись на F1-F9, а на вкладці «РЯД», у рядку «ПІДПИСИ ОСІ Х» вводимо інтервали в осередках D1-D9.
Знаходимо основні числові характеристики вибірки. Для їх введення виділяємо два стовпці, наприклад G і ​​H, у першому вводимо назву характеристики, у другому - функцію, в якій в якості масиву даних (рядок »число1»), вказати посилання на А1-А30
Існує інший спосіб обчислення числових характеристик вибірки. Для цього ставимо курсор у вільну комірку (наприклад D11). Потім викликаємо в меню «Сервіс» підменю «Аналіз даних». Якщо в меню «Сервіс» відсутній цей пункт, то в меню «Сервіс» потрібно вибрати пункт «Надбудови» м в ньому поставити прапорець навпроти пункту «Пакет аналізу». У вікні «Аналіз даних» потрібно вибрати пункт «Описова статистика». У вікні в полі «Вхідний інтервал» робимо посилання на вибірку А1-А23. Залишаємо групування «За стовпцями» в розділі «Параметри виводу» ставимо прапорець на "Вихідний інтервал» і в сусідньому полі створюємо посилання на верхню ліву клітинку області виводу (наприклад D11), ставимо прапорець навпроти «Описова статистика», натискаємо «ОК». результат - основні характеристики вибірки (зробіть ширше стовпчиків D, перемістивши його кордон у заголовок).





Лабораторна робота № 2
Перевірки статистичних гіпотез
Мета: Ознайомитися з методом перевірки основних статистичних гіпотез, що використовуються в економіці, з допомогою ЕОМ.
1. ПЕРЕВІРКА ГІПОТЕЗИ ПРО ВІДПОВІДНІСТЬ (КРИТЕРІЙ ЗГОДИ)
Використовується для перевірки припущення про те, що отримані в результаті спостережень дані відповідають нормам. Розглядається гіпотеза про те, що відхилення від норм невеликі, і ними можна знехтувати. При цьому задається довірча ймовірність p яка має сенс імовірності не помилитися при прийнятті гіпотези. Розглянемо перевірку на прикладі.
ПРИКЛАД: 1. при виробництві мікросхем процесорів використовуються кристали кварцу. Стандартом передбачено, щоб 50% зразків не було виявлено жодного дефекту кристалічної структури, у 15% - один дефект, у 13% - 2 дефекту, у 12% - 3 дефекту, у 10% більше 3 дефектів. При аналізі вибіркової партії виявилося, що з 100 примірників розподіл за дефектами партії виявилося, що з 1000 примірників розподіл за дефектами наступного (варіант відповідає ЕОМ): Чи можна з вірогідністю 0,99 вважати, що партія відповідає стандарту?
Введемо в А1 заголовок «НОРМА» і нижче в А2-А6 показники - числа 500, 150, 130, 120, 100. в комірку В1 введемо заголовок «СПОСТЕРЕЖЕННЯ» і нижче в В2-В6 спостережувані показники 516, 148, 131, 110, 95. в третьому стовпці вводяться формули для критерію: С1 заголовок «КРИТЕРІЙ», в С2 формулу «= (А2-В2) * (А2-В2) / А2». Автозаповненням розмножимо цю формулу на С3-С6. в клітинку С7 запишемо загальне значення критерію - суму стовпця С2-С6. для цього поставимо курсор в С6 і викликавши функцію в категорії «Математичний» знайдемо СУМ і в аргументі «Число 1» зазначимо посилання на С2-С6. вийти результат критерію Z = 1,629692308. Для відповіді на питання, чи відповідають досвідчені показники нормам, Z порівнюють з критичним значенням Zkp. Вводимо в D1 текст "критичне значення» в Е1 вводимо функцію ХІ2ОБР (категорія «Статистичні») у якої два аргументи: «Імовірність» - вводимо рівень значимості α = 1-p і «Ступені свободи» - вводять число n-1, де n - число норм). Результат 13,27670414. видно, що критичне значення більше критерію, отже досвідчені дані відповідають стандартним і партія із заданою вірогідністю можна віднести як відповідну стандарту.

Норма	Спостереження	Критерій	Критичне значення	13,27670414
500	516	0,512
150	148	0,026666667
130	131	0,007692308
120	110	0,833333333
100	95	0,25
	1000	1,629692308

2. ПЕРЕВІРКА гіпотези про рівність ДИСПЕРСІЯ
Використовується у випадку, якщо потрібно перевірити чи розрізняється розкид даних (дисперсії) у двох виборів. Це може використовувати для порівняння точностей обробки деталей на двох верстатах, рівномірності продажів товару протягом деякого періоду в двох містах і т.д. Для перевірки статистичної гіпотези, про рівність дисперсій служить F - критерій Фішера. Основною характеристикою критерію є рівень значимості α, якій має сенсу ймовірності помилитися, припускаючи, що дисперсії і, отже, точність, різняться. Замість α в задачах так само іноді задають довірчу ймовірність p = 1 - α, що має сенс ймовірності того, що дисперсії і справді рівні. Зазвичай вибирають критичне значення рівня значимості, наприклад 0,05 або 0,1, і якщо α більше критичного значення, то дисперсії вважаються рівними, в іншому випадку, різні. При цьому критерій може бути одностороннім, коли потрібно перевірити, що дисперсія конкретної виділеної вибірки більше, ніж в іншої, і двостороннім, коли просто потрібно показати, що дисперсії не рівні. Існує два способи перевірки таких гіпотез. Розглянемо їх на прикладах.
ПРИКЛАД 2. Чотири верстата в цеху обробляють деталі. Для перевірки точності обробки, взяли вибірку розмірів деталей у кожного верстата. Необхідно порівняти за допомогою F-тесту попарно точності обробки всіх верстатів (розглянути пари 1-2, 1-3, 1-4, 2-3, 2-4, 3-4) і зробити висновок, для яких верстатів точності обробки (дисперсії ) рівні, для яких ні. Взяти рівень значимості α = 0,02.

1 верстат	29,1	26,2	30,7	33,8	33,6	35,2	23,4	29,3	33,3	26,7
2 верстат	29,0	28,9	34,0	29,7	39,4	28,5	35,9	32,6	37,1	28,0
3 верстат	25,7	27,5	25,4	28,9	29,9	30,1	29,0	36,6	24,8	27,8
4 верстат	32,1	31,0	27,2	29,3	30,4	31,7	30,4	27,3	35,7	31,5

Рівень значимості α = 0,02. вводимо дані вибірок (без підписів) в 4 рядки в осередку А1-J1 і А2-J2 і т.д. відповідно. Для обчислення ФТЕСТ (масив1; масив2). Вводимо А5 підпис А5 «Рівень значущості», а в В5 функцію, ФТЕСТ, аргументами якої повинні бути посилання на клітинку А1-J1 і А2-J2 відповідно. Результат 0,873340161 говорить про те, що ймовірність помилитися, прийнявши гіпотезу про відмінність дисперсій, близько 0,9, що більше критичного значення, заданого в умові завдання 0,02. отже, можна говорити що досвідчені дані з більшою ймовірністю підтверджують припущення про те, що дисперсії однакові і точність обробки верстатів однакова, такі ж результати показало порівняння інших пар. Слід зазначити, що функції ФТЕСТ виходить рівень значимості двостороннього критерію і якщо потрібно використовувати однобічний, то результат необхідно зменшити вдвічі.

29,1	26,2	30,7	33,8	33,6	35,2	23,4	29,3	33,3	26,7
29	28,9	34	29,7	39,4	28,5	35,9	32,6	37,1	28
25,7	27,5	25,4	28,9	29,9	30,1	29	36,6	24,8	27,8
32,1	31	27,2	29,3	30,4	31,7	30,4	27,3	35,7	31,5
Рівень значимості
1 - 2	0,873340161
1 - 3	0,688084317
1 - 4	0,190932274
2 - 3	0,575576041
2 - 4	0,144572063
3 - 4	0,357739717

3. ПЕРЕВІРКА гіпотези про рівність СЕРЕДНІХ
Використовується для перевірки пропозиції про те, що середнє значення двох показників, представлених вибірками, значимо різняться. Існує три різновиди критерію: один - для пов'язаних вибірок, і два для незв'язних вибірок (з однаковими та різними дисперсіями). Якщо вибірки не зв'язні, то попередньо потрібно перевірити гіпотезу про рівність дисперсій, щоб визначити, який з критеріїв використовувати. Так само як і у випадку порівняння дисперсій є 2 способи розв'язання задачі, які розглянемо на прикладі.
ПРИКЛАД 3. Є дані про кількість продажів товару в двох містах. Перевірити на рівні значущості 0,01 статистичну гіпотезу про те, що середня кількість продажів товару в містах різна.

23	25	23	22	23	24	28	16	18	23	29	26	31	19
22	28	26	26	35	20	27	28	28	26	22	29

Використовуємо пакет «Аналіз даних». У залежності від типу критерію вибирається один з трьох: «Парний двухвиборочний t-тест для середніх» - для зв'язних вибірок, і «Двухвиборочних t-тест з однаковими дисперсіями» або «Двухвиборочних t-тест з різними дисперсіями» - для незв'язних вибірок. Викличте тест з однаковими дисперсіями, у вікні, в полях "Інтервал змінної 1» і «Інтервал змінної 2» вводять посилання на дані (А1-N1 і А2-L2, відповідно), якщо є підписи даних, то ставлять прапорець у написи «Мітки »(у нас їх немає, тому прапорець не ставиться). Далі вводять рівень значимості в полі «Альфа» - 0,01. Поле «Гіпотетична середня різниця» залишають порожніми. У розділі «Параметри виводу» ставлять мітку біля «Вихідний інтервал» і помістивши курсор у полі, що з'явилося навпроти напису, клацають лівою кнопкою в комірці В7. висновок результату буде здійснюватися починаючи з цього осередку. Натиснувши на "ОК" з'являється таблиця результату. Посуньте кордон між стовпцями В і С, С і D, D і Е збільшивши ширину стовпців В, С і D так, щоб вміщалися всі написи. Процедура виводить основні характеристики вибірки, t-статистику, критичні значення цих статистик і критичні рівні значущості «Р (Т <= t) однобічне» і «Р (Т <= t) двостороннє». Якщо за модулем t-статистика менше критичного, то середні показники із заданою вірогідністю рівні. У нашому випадку │ -1,784242592 │ <2,492159469, отже, середнє число продажів значимо не відрізняється. Слід зазначити, що якщо взяти рівень значимості α = 0,05, то результати дослідження будуть зовсім іншими.

Лабораторна робота № 3
Парна лінійна РЕГРЕСІЇ
Мета: Опанувати методи побудови лінійного рівняння парної регресії за допомогою ЕОМ, навчитися отримувати і аналізувати основні характеристики регресійного рівняння.
Розглянемо методику побудови регресійного рівняння на прикладі.
ПРИКЛАД. Дано вибірки факторів х _i і у _i. За цим вибірках знайти рівняння лінійної регресії ỹ = ах + b. Знайти коефіцієнт парної кореляції. Перевірити на рівні значущості а = 0,05 регресійну модель на адекватність.

Для знаходження коефіцієнтів a і b рівняння регресії служать функції НАХИЛ і ОТРЕЗОК, категорії «Статистичні». Вводимо в А5 підпис «а =» а в сусідню комірку В5 вводимо функцію НАХИЛ, ставимо курсор в полі «Ізв_знач_у» задаємо посилання на клітинки В2-K2, обводячи їх мишею. Результат 0,14303. Знайдемо тепер коефіцієнт b. Вводимо в А6 підпис «b =», а в В6 функцію ОТРЕЗОК з тими ж параметрами, що й функції НАХИЛ. Результат 5,976364. отже, рівняння лінійної регресії є у ​​= 0,14303 х +5,976364.
Побудуємо графік рівняння регресії. Для цього в третій рядок таблиці введемо значення функції в заданих точках Х (перший рядок) - у (х _1). Для отримання цих значень використовуються функція ТЕНДЕНЦІЯ категорії «Статистичні». Вводимо в А3 підпис «Y (X) і, помістивши курсор у В3, викликаємо функцію ТЕНДЕНЦІЯ. У полях «Ізв_знач_у» і «Ізв_знач_х» даємо посилання на В2-K2 і В1-K1. в полі «Нов_знач_х» вводимо також посилання на В1-K1. в полі «Константа» вводять 1, якщо рівняння регресії має вигляд y = ax + b, і 0, якщо у = ах. У нашому випадку вводимо одиницю. Функція ТЕНДЕНЦІЯ є масивом, тому для виведення всіх її значень виділяємо область В3-K3 і натискаємо F2 і Ctrl + Shift + Enter. Результат - значення рівняння регресії в заданих точках. Будуємо графік. Ставимо курсор в будь-яку вільну клітину, викликаємо майстер діаграм, вибираємо категорію «Точена», вид графіка - лінія без точок (у нижньому правому куті), натискаємо «Далі», в полі «Діагноз» вводимо посилання на В3-K3. переходимо на закладку «Ряд» і в полі «Значення Х» вводимо посилання на В1-K1, натискаємо «Готово». Результат - пряма лінія регресії. Подивимося, як різняться графіки дослідних даних і рівняння регресії. Для цього ставимо курсор в будь-яку вільну комірку, викликаємо майстер діаграм, категорія «Графік», вид графіка - ламається лінія з точками (друга зверху ліва), натискаємо «Далі», в полі «Діапазон» вводимо посилання на другу і третю рядка В2- K3. переходимо на закладку «Ряд» і в полі «Підписи осі Х» вводимо посилання на В1-K1, натискаємо «Готово». Результат - дві лінії (Синя - вихідні, червона - рівняння регресії). Видно, що лінії мало різняться між собою.


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

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

Лабораторна робота № 4
Нелінійної регресії
Мета: освоїти методи побудови основних видів нелінійних рівнянь парної регресії за допомогою за допомогою ЕОМ (внутрішньо лінійні моделі), навчитися отримувати і аналізувати показники якості регресійних рівнянь.
Розглянемо випадок, коли нелінійні моделі за допомогою перетворення даних можна звести до лінійних (внутрішньо лінійні моделі).
ПРИКЛАД. Побудувати рівняння регресії у = f (х) для вибірки х _п у _п (f = 1,2, ..., 10). Як f (х) розглянути чотири типи функцій - лінійна, статечна, показова і гіперболу:
у = Ах + В; у = Ах ^В; у = Ае ^Вх; у = А / г + В.
Необхідно знайти їх коефіцієнти А і В, і порівнявши показники якості, вибрати функцію, яка найкращим чином описує залежність.
Введемо дані в таблицю разом з підписами (комірки A1-K2). Залишимо вільними три рядки нижче таблиці для введення перетворених даних, виділимо перші п'ять рядків, провівши по лівій сірої кордоні по числах від 1 до 5 і вибрати який-небудь колір (світлий - жовтий або рожевий) розфарбувати фон осередків. Далі, починаючи з A6, виводимо параметри лінійної регресії. Для цього в комірку A6 робимо підпис «Лінійна» і в сусідню клітинку B6 вводимо функцію ЛИНЕЙН. У полях «Ізв_знач_x» даємо посилання на B2-K2 і B1-K1, наступні два поля приймають значення по одиниці. Далі обводимо область нижче в 5 рядків і лівіше в 2 рядки і натискаємо F2 і Ctrl + Shift + Enter. Результат - таблиця з параметрами регресії, з яких найбільший інтерес представляє коефіцієнт детермінації в першому стовпці третій зверху. У нашому випадку він дорівнює R ₁ = 0,951262. Значення F-критерію, що дозволяє перевірити адекватність моделі F ₁ = 156,1439
(Четвертий рядок, стовпець). Рівняння регресії одно
y = 12,96 x +6,18 (коефіцієнти a і b наведені в осередках B6 і C6).
Визначимо аналогічні характеристики для інших регресій і в результаті порівняння коефіцієнтів детермінації знайдемо кращу регресійну модель. Розглянемо гіперболічний регресію. Для її отримання перетворимо дані. У третьому рядку в клітинку A3 введемо підпис «1 / x» а в комірку B3 введемо формулу «= 1/B2». Растянем автозаповненням дану комірку на область B3-K3. Отримаємо характеристики регресійної моделі. У комірку А12 введемо підпис «Гіпербола», а в сусідню функцію ЛИНЕЙН. У полях «Ізв_знач_ y» і «Ізв_знач_ x 2 даємо посилання на B1-K1 і перетворені дані аргументу x - B3-K3, наступні два поля приймають значення по одиниці. Далі обводимо область нижче 5 рядків і лівіше в 2 рядки і натискаємо F2 і Ctrl + Shift + Enter. Отримуємо таблицю параметрів регресії. Коефіцієнт детермінації в даному випадку дорівнює R ₂ = 0,475661, що набагато гірше, ніж у випадку лінійної регресії. F-статистика дорівнює F ₂ = 7,257293. Рівняння регресії одно y = -6,25453 x ^18,96772.
Розглянемо експоненційну регресію. Для її лінеаризації отримуємо рівняння , Де ỹ = ln y, ã = b, = Ln a. Видно, що треба зробити перетворення даних - y замінити на ln y. Ставимо курсор до комірки А4 і робимо заголовок «ln y». Ставимо курсор в В4 і вводимо формулу LN (категорія «Математичні»). В якості аргументу робимо посилання на В1. Автозаповненням поширюємо формулу на четвертий рядок на клітинки В4-K4. Далі у клітинці F6 задаємо підпис «Експонента» і в сусідній G6 вводимо функцію ЛИНЕЙН, аргументами якої будуть перетворені дані В4-K4 (у полі «Ізв_знач_ y »), а інші поля такі ж як і для випадку лінійної регресії (B2-K2, 1, 1). Далі обводимо осередку G6-H10 і натискаємо F2 і Ctrl + Shift + Enter. Результат R ₃ = 0,89079, F ₃ = 65,25304, що говорить про дуже гарну регресії. Для знаходження коефіцієнтів рівняння регресії b = ã; ставимо курсор в J6 і робимо заголовок «а =», а в сусідній К6 формулу «= ЕХР (Н6)», у J7 даємо заголовок «b =», а в К7 формулу «= G6». Рівняння регресії є y = 0,511707 · e ^{6,197909 x.}
Розглянемо ступеневу регресію. Для її лінеаризації отримуємо рівняння ỹ = ã , Де ỹ = ln y, = Ln x, ã = b, = Ln a. Видно, що треба зробити перетворення даних - y замінити на ln y і x замінити на ln x. Рядок з ln y у нас вже є. Перетворимо змінні х. У комірку А5 даємо підпис «ln x», а в В5 і вводимо формулу LN (категорія «Математичні»). В якості аргументу робимо посилання на В2. Автозаповненням поширюємо формулу на п'ятий рядок на клітинки B5-K5. Далі в комірці F12 задаємо підпис «Степенева» і в сусідній G12 вводимо функцію ЛИНЕЙН, аргументами якої будуть перетворені дані B4-K4 (у полі «Ізв_знач_ у"), і B5-K5 (у полі «Ізв_знач_ х»), інші поля - одиниці. Далі звільнимо осередку G12-H16 і натискаємо F2 і Ctrl + Shift + Enter. Результат R ₄ = 0,997716, F ₄ = 3494,117, що говорить про гарну регресії. Для знаходження коефіцієнтів рівняння регресії b = ã; ставимо курсор в J12 і робимо заголовок «а =», а в сусідній К12 формулу «= ЕХР (Н12)», у J13 даємо заголовок «b =», а в К13 формулу «= G12». Рівняння регресії є у = 4,90767 / г + 7,341268.
Перевіримо, чи всі рівняння адекватно описують дані. Для цього потрібно порівняти F-статистики кожного критерію з критичним значенням. Для його отримання вводимо в А21 підпис «F-критичне», а в В21 функцію FРАСПОБР, аргументами якої вводимо відповідно «0,05» (рівень значимості), «1» (число факторів Х в рядку «Рівень значимості 1») і « 8 »(ступінь свободи 2 = n - 2). Результат 5,317655. F - критичне більше F - статистики значить модель адекватна. Також адекватні і інші регресії. Для того, щоб визначити, яка модель найкращим чином описує дані, порівняємо індекси детермінації для кожної моделі R _1, R _2, R _3, R _4. Найбільшим є R ₄ = 0,997716. Значить досвідчені дані краще описувати у = 4,90767 / г + 7,341268.
Висновок: У ході роботи я освоїв методи побудови основних видів нелінійних рівнянь парної регресії за допомогою за допомогою ЕОМ (внутрішньо лінійні моделі), навчилася отримувати і аналізувати показники якості регресійних рівнянь.

Лабораторна робота № 5
Поліномінальної РЕГРЕСІЇ
Мета: За досвідченим даними побудувати рівняння регресії виду у = ах ² + b х + с.
ХІД РОБОТИ:
Розглядається залежність урожайності деякої культури у _i від кількості внесених у грунт мінеральних добрив х _i. Передбачається, що ця залежність квадратична. Необхідно знайти рівняння регресії виду ỹ = ах ² + bx + c.

Введемо ці дані в електронну таблицю разом з підписами в осередку А1-K2. Побудуємо графік. Для цього обведемо дані Y (комірки В2-K2), викликаємо майстер діаграм, вибираємо тип діаграми «Графік», вид діаграми - графік з точками (другий зверху лівий), натискаємо «Далі», переходимо на закладку «Ряд» і в полі « Підписи осі Х »робимо посилання на В2-K2, натискаємо« Готово ». Графік можна наблизити поліномом 2 ступеня у = ах ² + b х + с. Для знаходження коефіцієнтів a, b, c потрібно вирішити систему рівнянь:

Розрахуємо суми. Для цього в комірку А3 вводимо підпис «Х ^ 2», а в В3 вводимо формулу «= В1 * В1» і автозаповнення переносимо її на весь рядок В3-K3. У комірку А4 вводимо підпис «Х ^ 3», а в В4 формулу «= В1 * В3» і автозаповнення переносимо її на весь рядок В4-K4. У комірку А5 вводимо «Х ^ 4», а в В5 формулу «= В4 * В1», автозаполняем рядок. У осередок А6 вводимо «Х * Y», а в В8 формулу «= В2 * В1», автозаполняем рядок. У осередок А7 вводимо «Х ^ 2 * Y», а в В9 формулу «= В3 * В2", автозаполняем рядок. Тепер вважаємо суми. Виділяємо іншим кольором стовпець L, клацнувши по заголовку і вибравши колір. У осередок L1 поміщаємо курсор і клацнувши по кнопці Автосума із позначкою Σ, обчислюємо суму першого рядка. Автозаповненням переносимо формулу на клітинки L1-710.
Вирішуємо тепер систему рівнянь. Для цього вводимо основну матрицю системи. У комірку А13 вводимо підпис «А =», а в осередку матриці В13-D15 вводимо посилання, відображені в таблиці

Вводимо також праві частини системи рівнянь. У G13 вводимо підпис «В =», а в Н13-Н15 вводимо, відповідно посилання на клітинки «= L7», «= L6», «= L2». Вирішуємо систему матричним методом. З вищої математики відомо, що рішення однаково А ^-1 В. Знаходимо зворотну матрицю. Для цього в комірку J13 вводимо підпис «А обр.» І, поставивши курсор у K13 задаємо формулу МОБР (категорія «Математичні»). Як аргумент «Масив» даємо посилання на клітинки В13: D15. Результатом також повинна бути матриця розміром 4 × 4. Для її отримання обводимо осередку K13-М15 мишею, виділяючи їх і натискаємо F2 і Ctrl + Shift + Enter. Результат - матриця А ^-1. Знайдемо тепер твір цієї матриці на стовпець У (комірки Н13-Н15). Вводимо в клітинку А18 підпис «Коефіцієнти» і в В18 задаємо функцію МУМНОЖ (категорія «Математичні»). Аргументами функції «Масив 1» служить посилання на матрицю А ^-1 (осередку K13-М15), а в полі "Масив 2» даємо посилання на стовпець У (комірки Н13-Н16). Далі виділяємо В18-В20 і натискаємо F2 і Ctrl + Shift + Enter. Одержаний масив - коефіцієнти рівняння регресії a, b, c. У результаті отримуємо рівняння регресії виду: у = 1,201082 х ² - 5,619177 х + 78,48095.
Побудуємо графіки вихідних даних і отриманих на основі рівняння регресії. Для цього в комірку А8 вводимо підпис «Регресія» і в В8 вводимо формулу «= $ В $ 18 * В3 + $ В $ 19 * В1 + $ В $ 20». Автозаповненням переносимо формулу в комірки В8-K8. Для побудови графіка виділяємо комірки В8-K8 і, утримуючи клавішу Ctrl, виділяємо також осередки В2-М2. Викликаємо майстра діаграм, вибираємо тип діаграми «Графік», вид діаграми - графік з точками (другий зверху лівий), натискаємо «Далі», переходимо на закладку «Ряд» і в полі «Підписи осі Х» робимо посилання на В2-М2, натискаємо «Готово». Видно, що криві майже збігаються.
ВИСНОВОК: в процесі роботи я за дослідними даними навчився будувати рівняння регресії виду у = ах ² + bх + с.