Статистична обробкаданих може також проводитися за допомогою надбудови ПАКЕТ АНАЛІЗУ(Рис. 62).

Із запропонованих пунктів вибирає пункт « РЕГРЕСІЯі клацаємо на ньому лівою кнопкою миші. Далі натискаємо ОК.

З'явиться вікно, показане на рис. 63.

Інструмент аналізу « РЕГРЕСІЯ» застосовується для підбору графіка для набору спостережень за допомогою методу найменших квадратів. Регресія використовується для аналізу впливу на окрему залежну змінну значень однієї або кількох незалежних змінних. Наприклад, на спортивні якості атлета впливають кілька факторів, включаючи вік, зростання та вагу. Можна обчислити ступінь впливу кожного з цих трьох факторів за результатами виступу спортсмена, а потім використовувати отримані дані для передбачення виступу спортсмена.

Інструмент «Регресія» використовує функцію Лінейн.

Діалогове вікно «РЕГРЕСІЯ»

Мітки Встановіть прапорець, якщо перший рядок або перший стовпець вхідного діапазону містить заголовки. Зніміть цей прапорець, якщо заголовки відсутні. У цьому випадку відповідні заголовки для даних вихідної таблиці будуть створені автоматично.

Рівень надійності Встановіть прапорець, щоб увімкнути додатковий рівень у вихідну таблицю підсумків. У відповідне поле введіть рівень надійності, який слід застосувати додатково до рівня 95%, що застосовується за умовчанням.

Константа – нуль Встановіть прапорець, щоб лінія регресії пройшла через початок координат.

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

Новий робочий лист Встановіть перемикач у це положення, щоб відкрити новий аркуш у книзі та вставити результати аналізу, починаючи з комірки A1. За потреби введіть ім'я нового аркуша в полі, розташованому навпроти відповідного положення перемикача.

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

Залишки Встановіть прапорець, щоб увімкнути залишки у вихідну таблицю.

Стандартизовані залишки Встановіть прапорець, щоб увімкнути стандартизовані залишки у вихідну таблицю.

Графік залишків Встановіть прапорець для побудови графіка залишків для кожної незалежної змінної.

Графік підбору Встановіть прапорець для побудови графіка залежності передбачуваних значень від спостережуваних.

Графік нормальної ймовірностіВстановіть прапорець, щоб побудувати графік нормальної ймовірності.

Функція Лінейн

Для проведення розрахунків виділяємо курсором комірку, в якій хочемо відобразити середнє значення та натискаємо на клавіатурі клавішу =. Далі в полі Ім'я вказуємо необхідну функцію, наприклад Відмінник(Рис. 22).

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

Рівняння для прямої лінії має такий вигляд:

y=m 1 x 1 +m 2 x 2 +…+b (у разі кількох діапазонів значень x),

де залежне значення y – функція незалежного значення x, значення m – коефіцієнти, що відповідають кожній незалежній змінній x, а b – постійна. Зверніть увагу, що y, x та m можуть бути векторами. Функція Лінейнповертає масив (mn; mn-1; ...; m 1; b). Лінейнможе повертати додаткову регресійну статистику.

Лінейн(відомі_значення_y; відомі_значення_x; конст; статистика)

Відомі_значення_y - безліч значень y, які відомі для співвідношення y=mx+b.

Якщо масив відомі_значення_y має один стовпець, то кожен стовпець масиву відомі_значення_x інтерпретується як окрема змінна.

Якщо масив відомі_значення_y має один рядок, то кожен рядок масиву відомі_значення_x інтерпретується як окрема змінна.

Відомі_значення_x - необов'язкова множина значень x, які вже відомі для співвідношення y=mx+b.

Масив відомі_значення_x може містити одну або кілька множин змінних. Якщо використовується лише одна змінна, то масиви_відомі_значення_y та відомі_значення_x можуть мати будь-яку форму - за умови, що вони мають однакову розмірність. Якщо використовується більше однієї змінної, то відомі_значення_y повинні бути вектором (тобто інтервалом заввишки один рядок або шириною в один стовпець).

Якщо масив_відомі_значення_x опущений, то передбачається, що цей масив (1;2;3;...) має такий самий розмір, як і масив_відомі_значення_y.

Конст - логічне значення, яке вказує, чи потрібно, щоб константа b дорівнювала 0.

Якщо аргумент "конст" має значення ІСТИНА або опущений, то константа b обчислюється звичайним чином.

Якщо аргумент «конст» має значення брехня, то значення b належить рівним 0 і значення m підбираються таким чином, щоб виконувалося співвідношення y=mx.

Статистика – логічне значення, яке вказує, чи потрібно повернути додаткову статистику щодо регресії.

Якщо аргумент статистика має значення ІСТИНА, функція Лінейн повертає додаткову регресійну статистику. Повертається масив матиме такий вигляд: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Якщо аргумент «статистика» має значення брехня або опущений, функція Лінейн повертає лише коефіцієнти m і постійну b.

Додаткова регресійна статистика. (Табл.17)

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

Зауваження:

Будь-яку пряму можна описати її нахилом та перетином з віссю y:

Нахил (m): щоб визначити нахил прямий, який зазвичай позначається через m, потрібно взяти дві точки прямий (x 1 , y 1) і (x 2 , y 2); нахил дорівнюватиме (y 2 -y 1)/(x 2 -x 1).

Y-перетин (b): Y-перетином прямий, що зазвичай позначається через b, є значення y для точки, в якій пряма перетинає вісь y.

Рівняння прямої має вигляд y=mx+b. Якщо відомі значення m і b, можна обчислити будь-яку точку на прямий, підставляючи значення y чи x у рівняння. Також можна скористатися функцією ТЕНДЕНЦІЯ.

Якщо є тільки одна незалежна змінна x, можна отримати нахил та y-перетин безпосередньо, скориставшись такими формулами:

Нахил: ІНДЕКС (ЛІНЕЙН(відомі_значення_y; відомі_значення_x); 1)

Y-перетин: ІНДЕКС (ЛІНЕЙН (відомі_значення_y; відомі_значення_x); 2)

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

де x та y – вибіркові середні значення, наприклад x = СРЗНАЧ (відомі_значення_x), а y = СРЗНАЧ (відомі_значення_y).

Функції апроксимації ЛІНІЙН і ЛГРФПРИБЛ можуть обчислити пряму або експоненційну криву, що найкраще описує дані. Однак вони не дають відповіді на питання, який із двох результатів більше підходить для вирішення поставленого завдання. Можна також обчислити функцію ТЕНДЕНЦІЯ (відомі_значення_y; відомі_значення_x) для прямої або функцію РОСТ(відомі_значення_y; відомі_значення_x) для експоненційної кривої. Ці функції, якщо не задавати аргумент нові_значення_x, повертають масив обчислених значень y для фактичних значень x відповідно до прямої чи кривої. Після цього можна порівняти обчислені значення із фактичними значеннями. Також можна побудувати діаграми для візуального порівняння.

Проводячи регресійний аналіз, Microsoft Excelобчислює кожної точки квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів різниць називається залишковою сумою квадратів (ssresid). Потім Microsoft Excel підраховує загальну суму квадратів (sstotal). Якщо конст = ІСТИНА або значення цього аргументу не вказано, Загальна сумаквадратів дорівнюватиме сумі квадратів різниць дійсних значень y і середніх значень y. При конст = брехня загальна сума квадратів дорівнюватиме сумі квадратів дійсних значень y (без віднімання середнього значення y з приватного значення y). Після цього регресійну суму квадратів можна обчислити так: ssreg = sstotal - ssresid. Чим менша залишкова сума квадратів, тим більше значення коефіцієнта детермінованості r2, який показує, наскільки добре рівняння, отримане за допомогою регресійного аналізупояснює взаємозв'язки між змінними. Коефіцієнт r2 дорівнює ssreg/stotal.

У деяких випадках один або більше стовпців X (нехай значення Y та X знаходяться в стовпцях) не має додаткового предикативного значення в інших стовпцях X. Іншими словами, видалення одного або більше стовпців X може призвести до значень Y, обчислених з однаковою точністю. У цьому випадку надлишкові стовпці X будуть виключені з моделі регресії. Цей феномен називається «колінеарністю», оскільки надлишкові стовпці X можуть бути представлені у вигляді суми кількох надлишкових стовпців. Функція Лінейн перевіряє на колінеарність і видаляє з моделі регресії всі надлишкові стовпці X, якщо їх виявляє. Видалені стовпці X можна визначити у вихідних даних ЛІНІЙН за коефіцієнтом, що дорівнює 0, і за значенням se, що дорівнює 0. Видалення одного або більше стовпців як надлишкових змінює величину df, оскільки вона залежить від кількості стовпців X, що насправді використовуються для предикативних цілей. Докладніше про обчислення величини df див. нижче у прикладі 4. При зміні df внаслідок видалення надлишкових стовпців значення sey і F також змінюються. Часто використовувати колінеарність не рекомендується. Однак її слід застосовувати, якщо деякі стовпці X містять 0 або 1 як індикатор вказівника, чи входить предмет експерименту в окрему групу. Якщо конст = ІСТИНА або значення цього аргументу не вказано, функція ЛІНІЙН вставляє додатковий стовпець X для моделювання точки перетину. Якщо є стовпець зі значеннями 1 для вказівки чоловіків і 0 - для жінок, а також є стовпець зі значеннями 1 для вказівки жінок і 0 - для чоловіків, то останній стовпець видаляється, оскільки його значення можна отримати зі стовпця з індикатором чоловічої статі.

Обчислення df для випадків, коли стовпці X не видаляються з моделі внаслідок колінеарності відбувається таким чином: якщо існує k стовпців відомих_значень_x і значення конст = ІСТИНА або не вказано, то df = n – k – 1. Якщо конст = БРЕХНЯ, то df = n - k. В обох випадках видалення стовпців X внаслідок колінеарності збільшує значення df на 1.

Формули, які повертають масиви, мають бути введені як формули масиву.

При введенні масиву констант як, наприклад, аргументу відомі_значення_x слід використовувати точку з комою для розділення значень в одному рядку і двокрапка для розділення рядків. Розділювачі можуть відрізнятися залежно від параметрів, заданих у вікні «Мова та стандарти» на панелі керування.

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

Основний алгоритм, що використовується у функції Лінейн, відрізняється від основного алгоритму функцій Нахилі ВІДРІЗОК. Різниця між алгоритмами може призвести до різних результатів за невизначених і колінеарних даних. Наприклад, якщо точки даних аргументу відомі_значення_y дорівнюють 0, а точки даних аргументу відомі_значення_x дорівнюють 1, то:

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

Функції НАКЛОН та ВІДРІЗОК повертають помилку #СПРАВ/0!. Алгоритм функцій НАКЛОН та ВІДРІЗОК використовується для пошуку тільки однієї відповіді, а в даному випадку їх може бути декілька.

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

ЛІНІЙН(значення_y, значення_x^Стовпець($A:$C))

працює за наявності одного стовпця значень Y та одного стовпця значень Х для обчислення апроксимації куба (багаточлен 3-го ступеня) наступної форми:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

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

Тема: КОРЕЛЯЦІЙНИЙ І РЕГРЕСІЙНИЙ АНАЛІЗ ВEXCEL

ЛАБОРАТОРНА РОБОТА №1

1. ВИЗНАЧЕННЯ КОЕФІЦІЄНТА ПАРНОЇ КОРРЕЛЯЦІЇ У ПРОГРАМІEXCEL

Кореляційний зв'язок- це неповна, імовірнісна залежність між показниками, яка проявляється лише у масі спостережень.

Парна кореляція- це зв'язок між двома показниками, один із яких є факторним, а інший – результативним.

Множинна кореляціявиникає від взаємодії кількох факторів із результативним показником.

Необхідні умови застосування кореляційного аналізу:

1. Наявність досить великої кількості спостережень про величину досліджуваних факторних та результативних показників.

2. Досліджувані фактори повинні мати кількісний вимір та відображення у тих чи інших джерелах інформації.

Застосування кореляційного аналізу дозволяє вирішити такі завдання:

1.Визначити зміну результативного показника під впливом одного чи кількох факторів.

2. Встановити відносний рівень залежності результативного показника від кожного фактора.

Завдання 1.

Є дані щодо 20 сільськогосподарських господарств. Знайти коефіцієнт кореляціїміж величинами врожайності зернових культур та якістю землі та оцінити його значимість. Дані наведені у таблиці.

Таблиця. Залежність урожайності зернових культур від якості землі

Для знаходження коефіцієнта кореляції використовувати функцію Корел.

Значимість коефіцієнта кореляції перевіряється за критерієм Стьюдента.

Для прикладу r=0,99, n=18.

Для знаходження квантилю розподілу Стьюдента використовується функція СТЬЮДРАСПОБР з наступними аргументами: Ймовірність –0,05, Ступені свободи –18.

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

ПОБУДУВАННЯ РЕГРЕСІЙНОЇ МОДЕЛІ ЗВ'ЯЗКУ МІЖ ДВОМА ВЕЛИЧИНАМИ

Завдання 2.

За даними завдання 1:

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

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

1 - ий спосіб.

1. На аркуші Excel виділити масив вільних осередків із п'яти рядків та двох стовпців.

2. Викликати функцію Лінейн.

3.Вказати для функції такі аргументи: Із_знач_y Врожайність, ц/га;Із_знач_x- стовпець значень показника Якість землі, бал; Константа -1, Стат - 1(дозволяє обчислити показники, що використовуються для перевірки адекватності моделі. Якщо Стат-0,то такі показники не обчислюватимуться.

4. Натиснути клавішу клавіш Ctrl- Shift- Enter.

У виділені осередки виводяться коефіцієнти моделі, і навіть показники, що дозволяють перевірити модель адекватність (таблиця 2).

a 1 , a 0 - Коефіцієнти моделі;

S e 1 S e 0 - Стандартні помилки коефіцієнтів. Чим точніше модель, тим менші ці величини.

R 2 - Коефіцієнт детермінації. Чим він більший, тим точніше модель.

F- Статистика для перевірки значущості моделі.

n- k-1 – число ступенів свободи (n-обсяг вибірки, k- кількість вхідних змінних; у цьому прикладі n=20, к=1)

Q R- Сума квадратів, обумовлена ​​регресією;

Q e- Сума квадратів помилок.

5. Для перевірки адекватності моделі знайти квантиль розподілу Фішера F f . за допомогою функції FРОЗКЛАД. Для цього в будь-якому вільному осередку ввести функцію FРОЗКЛАДз наступними аргументами: Ймовірність – 0,05, Ступені_свободи _1–1, Ступені_свободи _2–18. Якщо F> F f то модель адекватна вихідним даним

6. Перевірити адекватність збудованої моделі, використовуючи розрахунковий рівень значущості (P). Ввести функцію FРОЗПІЛз наступними аргументами: X– значення статистики F, Ступені_свободи_1–1, Ступені_свободи_2- 18. Якщо розрахунковий рівень значущості P<α =0,05, то модель адекватна исходным данным.

2-й спосіб.

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

Вибрати команду Сервіс/Аналіз даних/Регресія. У діалоговому вікні встановити: Вхідний інтервалY– значення показника Врожайність, ц/га,Вхідний інтервалX– значення показника Якість землі, бал.

Встановити прапорець Мітки. В області Параметри виводувибрати перемикач Вихідний інтервалі вказати комірку, з якої почнеться виведення результатів. Щоб отримати результати, натисніть кнопку ОК.

Інтерпретація результатів.

Коефіцієнти моделі, що шукаються, знаходяться в стовпці Коефіцієнти:

Для цього прикладу рівняння моделі має вигляд:

Y=2,53+0,5X

У цьому прикладі зі збільшенням якості ґрунту на один бал урожайність зернових культур підвищується в середньому на 0,5 ц/га.

Перевірка адекватності моделівиконується за розрахунковим рівнем значимості P, вказаним у стовпці ЗначимістьF. Якщо розрахунковий рівень значущості менший за заданий рівень значущості α =0,05, то модель адекватна.

Перевірка статистичної значущостікоефіцієнтів моделі виконується за розрахунковими рівнями значимості P, зазначеними у стовпці P-значення. Якщо розрахунковий рівень значущості менший за заданий рівень значущості α =0,05, то відповідний коефіцієнт моделі статистично значущий.

МножиннийR – коефіцієнт кореляції. Чим ближче його величина до 1, тим більше тісний зв'язок між показниками, що вивчаються. Для цього прикладу R= 0,99. Це дозволяє зробити висновок, що якість землі – один із основних факторів, від якого залежить врожайність зернових культур.

R-квадрат – коефіцієнт детермінації. Він виходить зведенням у квадрат коефіцієнта кореляції – R 2 =0,98. Він показує, що врожайність зернових культур на 98% залежить від якості ґрунту, а на частку інших факторів припадає 0,02%.

Третій метод. ГРАФІЧНИЙ СПОСІБ ПОБУДУВАННЯ МОДЕЛІ.

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

Здобути лінійну модель залежності врожайності зернових культур від якості землі.

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

Основні завдання та види регресії

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

Зазвичай регресія представлена ​​у вигляді простого рівняння, що розкриває залежності та силу зв'язку між двома групами змінних, де одна група є залежною чи ендогенною, а інша – незалежною чи екзогенною. За наявності групи взаємозалежних показників залежна змінна Y визначається з логіки міркувань, інші виступають у ролі незалежних Х-переменных.

Основні завдання побудови регресійної моделі полягають у наступному:

1. Відбір значних незалежних змінних (Х1, Х2, …, Xk).
2. Вибір типу функції.
3. Побудова оцінок коефіцієнтів.
4. Побудова довірчих інтервалів та функції регресії.
5. Перевірка значущості обчислених оцінок та побудованого рівняння регресії.

Регресійний аналіз буває кількох видів:

• парний (1 залежна та 1 незалежна змінні);
• множинний (кілька незалежних змінних).

Рівняння регресії буває двох видів:

1. Лінійні, що ілюструють суворий лінійний зв'язок між змінними.
2. Нелінійні - рівняння, які можуть включати ступеня, дроби та тригонометричні функції.

Інструкція побудови моделі

Щоб виконати задану побудову в Excel, необхідно дотримуватися вказівок:

Для подальшого обчислення слід використовувати функцію «Лінейн ()», вказуючи значення Y, значення Х, Конст та статистику. Після цього визначте безліч точок лінії регресії за допомогою функції «Тенденція» — Значення Y, Значення Х, Нові значення, Конст. За допомогою заданих параметрів обчисліть невідоме значення коефіцієнтів, спираючись на умови поставленої задачі.

28 жовтень

Доброго дня, шановні читачі блогу! Сьогодні ми поговоримо про нелінійні регресії. Рішення лінійних регресій можна переглянути за ПОСИЛАННЯМ.

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

Основними типами нелінійних регресій є:

• поліноміальні (квадратична, кубічна);
• гіперболічна;
• статечна;
• показова;
• логарифмічна.

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

У прогнозуванні за допомогою нелінійних регресій головне з'ясувати коефіцієнт кореляції, який покаже нам, чи є тісний взаємозв'язок меду двома параметрами чи ні. Як правило, якщо коефіцієнт кореляції близький до 1, отже зв'язок є, і прогноз буде досить точним. Ще одним важливим елементом нелінійних регресій є середня відносна помилка ( А ), якщо вона знаходиться в проміжку<8…10%, значит модель достаточно точна.

На цьому, мабуть, теоретичний блок ми закінчимо та перейдемо до практичних обчислень.

У нас є таблиця продажів автомобілів за проміжок 15 років (позначимо його X), кількість кроків вимірювань буде аргументом n, також є виручка за ці періоди (позначимо її Y), нам потрібно спрогнозувати яка буде виручка надалі. Побудуємо наступну таблицю:

Для дослідження нам потрібно буде вирішити рівняння (залежності Y від X): y=ax 2 +bx+c+e. Це парна квадратична регресія. Застосуємо у разі метод найменших квадратів, з'ясування невідомих аргументів — a, b, c. Він приведе до системи рівнянь алгебри виду:

Для вирішення цієї системи скористаємося, наприклад, способом Крамера. Бачимо, що суми, що входять до системи, є коефіцієнтами при невідомих. Для їх обчислення додамо в таблицю кілька стовпців (D,E,F,G,H) і підпишемо відповідно до змісту обчислень — у стовпці D зведемо x у квадрат, E у куб, F у 4 ступінь, у G перемножимо показники x і y, H зведемо x в квадрат і перемножимо з y.

Вийде заповнена потрібними на вирішення рівняння таблиця виду.

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

Тобто в осередок B21 ми повинні помістити суму стовпця, де зводили показник X у четвертий ступінь F17. Просто пошлемося на комірку — «=F17». Далі нам необхідна сума стовпця, де зводили X в куб - E17, далі йдемо строго по системі. Таким чином, нам потрібно буде заповнити всю матрицю.

Відповідно до алгоритму Крамера наберемо матрицю А1, подібну до А, в якій замість елементів першого стовпця повинні розміщуватися елементи правих частин рівнянь системи. Тобто сума стовпця X у квадраті помножена на Y, сума стовпця XY та сума стовпця Y.

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

Наслідуючи обраний алгоритм, нам потрібно буде обчислити значення визначників (детермінантів, D) отриманих матриць. Скористаємося формулою МОПРЕД. Результати розмістимо в осередках J21: K24.

Розрахунок коефіцієнтів рівняння по Крамеру будемо проводити в осередках навпроти відповідних детермінантів за формулою: a(В комірці M22) - "= K22 / K21"; b(В комірці M23) - "= K23 / K21"; з(В комірці M24) - "= K24 / K21".

Отримаємо наше шукане рівняння парної квадратичної регресії:

y=-0,074x 2 +2,151x+6,523

Оцінимо тісноту лінійного зв'язку індексом кореляції.

Для обчислення додамо до таблиці додатковий стовпець J (назвемо його y*). Розрахунку буде наступною (згідно з отриманим нами рівнянням регресії) — "=$m$22*B2*B2+$M$23*B2+$M$24".Помістимо її в комірку J2. Залишиться протягнути вниз маркер автозаповнення до осередку J16.

Для обчислення сум (Y-Y усереднене) 2 додамо таблицю стовпці K і L з відповідними формулами. Середнє по стовпцю Y порахуємо за допомогою функції СРЗНАЧ.

У осередку K25 розмістимо формулу підрахунку індексу кореляції - «=КОРІНЬ(1-(K17/L17))».

Бачимо, що значення 0,959 дуже близько до 1, отже, між продажами і роками є тісний нелінійний зв'язок.

Залишилося оцінити якість припасування отриманого квадратичного рівняння регресії (індекс детермінації). Він розраховується за формулою квадрата індексу кореляції. Тобто формула в осередку K26 буде дуже проста - = K25 * K25.

Коефіцієнт 0,920 близький до 1, що свідчить про високу якість припасування.

Останньою дією буде обчислення відносної помилки. Додамо стовпець і внесемо туди формулу: «ABS((C2-J2)/C2), ABS — модуль, абсолютне значення. Протягнемо маркером вниз і в комірці M18 виведемо середнє значення (СРЗНАЧ), призначимо коміркам відсотковий формат. Отриманий результат - 7,79% знаходиться в межах допустимих значень помилки<8…10%. Значит вычисления достаточно точны.

Якщо виникне потреба, за набутими значеннями ми можемо побудувати графік.

Файл з прикладом додається - ПОСИЛАННЯ!

Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.