Множинна регресія в Excel - приклад з рішенням. Нелінійна регресія в Excel
Статистична обробка даних може проводитися за допомогою надбудови ПАКЕТ АНАЛІЗУ(Рис. 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)
| Величина | Опис |
| se1,se2,...,sen | Стандартні значення помилок коефіцієнтів m1,m2,...,mn. |
| seb | Стандартне значення помилки для постійної b (seb = #Н/Д, якщо аргумент «конст» має значення брехня). |
| r2 | Коефіцієнт детермінованості. Порівнюються фактичні значення y та значення, одержувані з рівняння прямої; за результатами порівняння обчислюється коефіцієнт детермінованості, нормований від 0 до 1. Якщо він дорівнює 1, має місце повна кореляція з моделлю, тобто відмінності між фактичним і оцінним значеннями y не існує. У протилежному випадку, якщо коефіцієнт детермінованості дорівнює 0, використовувати рівняння регресії для передбачення значень y немає сенсу. Для отримання додаткових відомостейпро способи обчислення r2 див. «Зауваження» в кінці цього розділу. |
| sey | Стандартна помилка оцінки y. |
| F | F-статистика або F-спостережуване значення. F-статистика використовується для визначення того, чи є випадковою взаємозв'язок між залежною і незалежною змінними. |
| df | Ступені свободи. Ступені свободи корисні для знаходження F-критичних значень у статистичній таблиці. Для визначення рівня надійності моделі необхідно порівняти значення таблиці з F-статистикою, повертається функцією ЛІНІЙН. Для отримання додаткових відомостей про обчислення величини df див. «Зауваження» наприкінці розділу. Далі у прикладі 4 показано використання величин F та df. |
| ssreg | Регресійна сума квадратів. |
| ssresid | Залишкова сума квадратів. Для отримання додаткових відомостей про розрахунок величин ssreg та ssresid див. «Зауваження» наприкінці цього розділу. |
На наведеному нижче малюнку показано, у порядку повертається додаткова регресійна статистика (рис. 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
Формула може бути змінена для розрахунків інших типів регресії, але окремих випадках потрібно коригування вихідних значень та інших статистичних даних.
Регресійний аналізу Microsoft Excel – найповніше посібників з використання MS Excel на вирішення завдань регресійного аналізу у сфері бізнес-аналітики. Конрад Карлберг доступно пояснює теоретичні питання, знання яких допоможе вам уникнути багатьох помилок як при самостійне проведеннярегресійного аналізу, і при оцінці результатів аналізу, виконаного іншими людьми. Весь матеріал, від простих кореляційі t-тестів до множинного коварійного аналізу, заснований на реальні прикладита супроводжується докладним описом відповідних покрокових процедур.
У книзі обговорюються особливості та протиріччя, пов'язані з функціями ExcelДля роботи з регресією, розглядаються наслідки використання кожної їх опції та кожного аргументу і пояснюється, як надійно застосовувати регресійні методи в самих різних областях, від медичних досліджень до фінансового аналізу
Конрад Карлберг. Регресійний аналіз у Microsoft Excel. - М.: Діалектика, 2017. - 400 с.
Завантажити нотатку у форматі або , приклади у форматі
Глава 1. Оцінка мінливості даних
У розпорядженні статистиків є безліч показників варіації (мінливості). Один із них – сума квадратів відхилень індивідуальних значень від середнього. У Excel йому використовується функція КВАДРОТКЛ(). Але найчастіше використовується дисперсія. Дисперсія – це середнє квадратів відхилень. Дисперсія нечутлива до кількості значень у досліджуваному наборі даних (тоді як сума квадратів відхилень зростає з числом вимірів).
Програма Excel пропонує дві функції, що повертають дисперсію: ДИСП.Г() та ДИСП.В():
- Використовуйте функцію ДИСП.Г(), якщо значення, що підлягають обробці, утворюють генеральну сукупність. Тобто значення, що містяться в діапазоні, є єдиними значеннями, які вас цікавлять.
- Використовуйте функцію ДИСП.В(), якщо значення, що підлягають обробці, утворюють вибірку із сукупності більшого обсягу. Передбачається, що є додаткові значення, дисперсію яких ви можете оцінити.
Якщо така величина, як середнє значення чи коефіцієнт кореляції, розраховується з урахуванням генеральної сукупності, вона називається параметром. Аналогічна величина, яка розраховується на основі вибірки, називається статистикою. Відраховуючи відхилення від середнього значенняу цьому наборі, ви отримаєте суму квадратів відхилень меншої величини, ніж якби відраховували їх від будь-якого іншого значення. Аналогічне твердження є справедливим і для дисперсії.
Чим більший обсяг вибірки, то точніше розраховане значення статистики. Але не існує жодної вибірки з обсягом меншим за обсяг генеральної сукупності, щодо якої ви могли б бути впевнені в тому, що значення статистики збігається зі значенням параметра.
Припустимо, у вас є набір зі 100 значень зростання, середнє яких відрізняється від середнього за генеральною сукупністю, хоч би якою малою була ця відмінність. Розрахувавши дисперсію для вибірки, ви отримаєте деяке її значення, скажімо, 4. Це значення менше будь-якого іншого, яке можна отримати, розраховуючи відхилення кожного зі 100 значень зростання щодо будь-якого значення, відмінного від середнього за вибіркою, в тому числі і щодо справжнього середнього генеральної сукупності. Тому обчислена дисперсія відрізнятиметься, причому у менший бік, від дисперсії, яку ви отримали б, якби якимось чином дізналися і використовували не вибіркове середнє, а параметр генеральної сукупності.
Середня сума квадратів, визначена вибірки, дає нижню оцінку дисперсії генеральної сукупності. Обчислену таким способом дисперсію називають зміщеноюоцінкою. Виявляється, щоб виключити зсув і отримати незміщену оцінку, достатньо розділити суму квадратів відхилень не так n, де n- розмір вибірки, але в n – 1.
Величина n – 1називається кількістю (числом) ступенів свободи. Існують різні способирозрахунку цієї величини, хоча всі вони включають або віднімання деякого числа з розміру вибірки, або підрахунок кількості категорій, які потрапляють спостереження.
Суть відмінності між функціями ДИСП.Г() та ДИСП.В() полягає в наступному:
- У функції ДИСП.Г() сума квадратів ділиться кількість спостережень і, отже, представляє зміщену оцінку дисперсії, справжнє середнє.
- У функції ДИСП.В() сума квадратів ділиться кількість спостережень мінус 1, тобто. на кількість ступенів свободи, що дає більш точну, незміщену оцінку дисперсії генеральної сукупності, з якої було вилучено цю вибірку.
Стандартне відхилення (англ. standard deviation, SD) – є квадратний корінь із дисперсії:
Зведення відхилень у квадрат переводить шкалу вимірів в іншу метрику, яка є квадратом вихідної: метри - в квадратні метри, долари - у квадратні долари і т.д. Стандартне відхилення - це квадратний корінь з дисперсії, і тому воно повертає нас до вихідних одиниць вимірювання. Що зручніше.
Часто доводиться розраховувати стандартне відхиленняпісля того, як дані були піддані деяким маніпуляціям. І хоча в цих випадках результати безперечно є стандартними відхиленнями, їх прийнято називати стандартними помилками. Існує кілька різновидів стандартних помилок, зокрема стандартна помилка виміру, стандартна помилка пропорції, стандартна помилка середнього.
Припустимо, ви зібрали дані про зростання 25 випадково вибраних дорослих чоловіків у кожному із 50 штатів. Далі ви обчислюєте середнє зростання дорослих чоловіків у кожному штаті. Отримані 50 середніх значень своєю чергою вважатимуться спостереженнями. Виходячи з цього, ви могли б розрахувати їхнє стандартне відхилення, яке і є стандартною помилкою середнього. Мал. 1. дозволяє порівняти розподіл 1250 вихідних індивідуальних значень (дані зростання 25 чоловіків у кожному з 50 штатів) з розподілом середніх значень 50 штатів. Формула з метою оцінки стандартної помилки середнього (тобто стандартного відхилення середніх значень, а чи не індивідуальних спостережень):
де – стандартна помилка середнього; s- Стандартне відхилення вихідних спостережень; n- Кількість спостережень у вибірці.
Мал. 1. Варіація середніх значень від штату до штату значно менша від варіації індивідуальних результатів спостережень
У статистиці існує угода щодо використання грецьких та латинських літер для позначення статистичних величин. Грецькими літерами заведено позначати параметри генеральної сукупності, латинськими - вибіркові статистики. Отже, якщо йдеться про стандартне відхилення генеральної сукупності, ми записуємо його як σ; якщо ж розглядається стандартне відхилення вибірки, використовуємо позначення s. Що ж до символів для позначення середніх, всі вони узгоджуються між собою менш вдало. Середня за генеральною сукупністю позначається грецькою літерою μ. Однак для подання вибіркового середнього традиційно використовується символ X.
z-оцінкавиражає положення спостереження у розподілі в одиницях стандартного відхилення. Наприклад, z = 1,5 означає, що спостереження відстоює від середнього на 1,5 стандартного відхилення у бік великих значень. Термін z-оцінкавикористовують із індивідуальних оцінок, тобто. для вимірювань, що приписуються окремим елементамвибірки. Щодо таких статистик (наприклад, середнє значення за штатом) використовують термін z-значення:
де X̅ – середнє значення вибірки, μ – середнє значення генеральної сукупності, – стандартна помилка середніх наборів вибірок:
де σ – стандартна помилка генеральної сукупності (індивідуальних вимірів), n- Розмір вибірки.
Припустимо, ви працюєте інструктором у гольф-клубі. Ви могли протягом тривалого часу вимірювати дальність ударів і знаєте, що її середнє значення становить 205 ярдів, а стандартне відхилення - 36 ярдів. Вам запропонували нову ключку, стверджуючи, що вона збільшить дальність удару на 10 ярдів. Ви просите кожного з наступних 81 відвідувачів клубу виконати пробний удар новою ключкою та записуєте його дальність удару. Виявилося, що середня дальність удару новою ключкою становить 215 ярдів. Якою є ймовірність того, що різниця в 10 ярдів (215 – 205) обумовлена виключно помилкою вибірки? Або інакше: яка ймовірність того, що при більш масштабному тестуванні нова ключка не продемонструє збільшення дальності удару в порівнянні з наявним довготривалим середнім показником 205 ярдів?
Ми можемо перевірити це, сформувавши z-значення. Стандартна помилка середнього:
Тоді z-значення:
Нам потрібно знайти ймовірність того, що середнє за вибіркою відстоятиме від середнього за генеральною сукупністю на 2,5? Якщо ймовірність буде маленькою, значить відмінності обумовлені не випадковістю, а якістю нової ключки. У Excel визначення ймовірності z-значення немає готової функції. Однак можна використовувати формулу =1-НОРМ.СТ.РАСП(z-значення;ІСТИНА), де функція НОРМ.СТ.РАСП() повертає площу під нормальною кривою зліва від z-значення (рис. 2).
Мал. 2. Функція НОРМ.СТ.РАСП() повертає площу під кривою зліва від z-значення; щоб збільшити зображення клацніть на ньому правою кнопкою миші та виберіть Відкрити картинку в новій вкладці
Другий аргумент функції НОРМ.СТ.РАСП() може набувати два значення: ІСТИНА – функція повертає площу області під кривою зліва від точки, заданої першим аргументом; Брехня - функція повертає висоту кривої в точці, заданої першим аргументом.
Якщо середнє значення (μ) та стандартне відхилення (σ) генеральної сукупності не відомі, використовується t-значення (докладніше див.). Структури z- та t-значення відрізняються тим, що для знаходження t-значення використовується стандартне відхилення s, отримане на основі вибіркових результатів, а не відоме значення параметра генеральної сукупності. Нормальна крива має єдину форму, а форма розподілу t-значень варіює в залежності від кількості ступенів свободи df (англ. degrees of freedom) вибірки, що воно представляє. Кількість ступенів свободи вибірки дорівнює n – 1, де n- Розмір вибірки (рис. 3).
Мал. 3. Форма t-розподілів, що виникають у тих випадках, коли параметр σ невідомий, відрізняється від форми нормального розподілу
В Excel є дві функції для t-розподілу, що також називається розподілом Стьюдента: СТЬЮДЕНТ.РАСП() повертає величину площі під кривою зліва від заданого t-значення, а СТЬЮДЕНТ.РАСП.ПХ() – праворуч.
Розділ 2. Кореляція
Кореляція – це міра залежності між елементами набору впорядкованих пар. Кореляція характеризується коефіцієнтам кореляції Пірсона- r. Коефіцієнт може набувати значень в інтервалі від –1,0 до +1,0.
де S xі S y– стандартні відхилення змінних Хі Y, S xy- Підступність:
У цій формулі коваріація поділяється на стандартні відхилення змінних Хі Y, тим самим видаляючи з підступу ефекти масштабування, пов'язані з одиницями виміру. В Excel використовується функція Корел (). У назві цієї функції відсутні уточнюючі елементи Г і В, які використовуються в назвах таких функцій, як СТАНДОТКЛОН(), ДИСП() або КОВАРІАЦІЯ(). Хоча коефіцієнт кореляції за вибіркою надається зміщену оцінку, проте причина зміщення інша, ніж у разі дисперсії або стандартного відхилення.
Залежно від величини генерального коефіцієнта кореляції (часто позначається грецькою літерою ρ ), коефіцієнт кореляції rдає зміщену оцінку, причому ефект усунення посилюється зі зменшенням розміру вибірок. Тим не менш, ми не намагаємося коригувати це зсув аналогічно тому, як, наприклад, робили це при обчисленні стандартного відхилення, коли підставляли у відповідну формулу не кількість спостережень, а кількість ступенів свободи. Насправді кількість спостережень, що використовується для обчислення коваріації, не впливає на величину.
Стандартний коефіцієнт кореляції призначений для використання зі змінними, пов'язаними між собою лінійним співвідношенням. Наявність нелінійності та/або помилок у даних (викиди) призводять до неправильного розрахунку коефіцієнта кореляції. Для діагностики проблем із даними рекомендується будувати точкові діаграми. Це єдиний тип діаграм в Excel, у якому горизонтальна, і вертикальна осі трактуються як осі значень. Лінійна діаграма один із стовпців визначає, як вісь категорій, що спотворює картину даних (рис. 4).
Мал. 4. Лінії регресії здаються однаковими, однак порівняйте між собою їх рівняння
Спостереження, використані побудови лінійної діаграми, розташовуються вздовж горизонтальної осі еквідистантно. Написи поділів уздовж цієї осі - це і є лише написи, а не числові значення.
Незважаючи на те, що кореляція часто означає наявність причинно-наслідкового зв'язку, вона не може бути доказом того, що так воно і є. Статистика не використовується для демонстрації того, істинна чи хибна теорія. Для виключення конкуруючих пояснень результатів спостережень ставлять планові експерименти. Статистика ж залучається для узагальнення інформації, зібраної в ході таких експериментів, і кількісної оцінки ймовірності того, що рішення може бути невірним при наявній доказовій базі.
Розділ 3. Проста регресія
Якщо дві змінні пов'язані між собою, отже значення коефіцієнта кореляції перевищує, скажімо, 0,5, то цьому випадку можна прогнозувати (з деякою точністю) невідоме значення однієї змінної за відомим значенням інший. Для отримання прогнозних значень ціни, з даних, наведених на рис. 5, можна використовувати будь-який з кількох можливих способів, але майже напевно ви не використовуватимете той, який представлений на рис. 5. І все ж вам варто з ним ознайомитися, оскільки жоден інший спосіб не дозволяє так само чітко продемонструвати зв'язок між кореляцією та прогнозуванням, як цей. На рис. 5 в діапазоні В2:С12 представлена випадкова вибірка з десяти будинків і наведені дані про площу кожного будинку (у квадратних футах) та його продажну ціну.
Мал. 5. Прогнозні значення продажної ціни утворюють пряму лінію
Знайдіть середні значення, стандартні відхилення та коефіцієнт кореляції (діапазон А14:С18). Розрахуйте z-оцінки площі (Е2: Е12). Наприклад, осередок ЕС містить формулу: =(В3-$В$14)/$В$15. Обчисліть z-оцінки прогнозної ціни (F2: F12). Наприклад, осередок F3 містить формулу: = ЕЗ * $ $ 18. Переведіть z-оцінки у ціни в доларах (Н2: Н12). У осередку НЗ формула: =F3*$C$15+$C$14.
Зверніть увагу: прогнозне значення завжди прагне зміститися у бік середнього, рівного 0. Чим ближче до нуля коефіцієнт кореляції, тим ближче до нуля прогнозна z-оцінка. У прикладі коефіцієнт кореляції між площею і продажною ціною дорівнює 0,67, і прогнозна ціна дорівнює 1,0*0,67, тобто. 0,67. Цьому відповідає перевищення значення над середнім значенням, що дорівнює двом третинам стандартного відхилення. Якби коефіцієнт кореляції дорівнював 0,5, то прогнозна вартість становила б 1,0*0,5, тобто. 0,5. Цьому відповідає перевищення значення над середнім значенням, що дорівнює лише половині стандартного відхилення. Щоразу, коли значення коефіцієнта кореляції відрізняється від ідеального, тобто. більше -1,0 і менше 1,0, оцінка прогнозованої змінної має бути ближчою до свого середнього значення, ніж оцінка передикторної (незалежної) змінної до свого. Це називається регресією до середнього, чи навіть регресією.
У Excel є кілька функцій визначення коефіцієнтів рівняння лінії регресії (в Excel вона називається лінією тренда) у =kx + b. Для визначення kслужить функція
=НАКЛОН(відомі_значення_у; відомі_значення_х)
Тут у– прогнозована змінна, а х- Незалежна змінна. Ви повинні суворо дотримуватися цього порядку змінних. Нахил лінії регресії, коефіцієнт кореляції, стандартні відхилення змінних та коваріація тісно пов'язані між собою (рис. 6). Функція ВІДРІЗОК() повертає значення, що відсікається лінією регресії на вертикальній осі:
= відрізок (відомі_значення_у; відомі_значення_х)
Мал. 6. Співвідношення між стандартними відхиленнями перетворює коваріацію в коефіцієнт кореляції та нахил лінії регресії
Зверніть увагу, що кількість значень х і у, що надаються функціям НАКЛОН() і ВІДРІЗОК() як аргументи, повинна бути однаковою.
У регресійному аналізі використовується ще один важливий показник- R 2 (R-квадрат), або коефіцієнт детермінації. Він визначає, який внесок у загальну мінливість даних робить виявлена за допомогою регресії залежність між хі у. У Excel йому є функція КВПИРСОН(), яка приймає точно самі аргументи, як і функція КОРРЕЛ().
Про дві змінні з ненульовим коефіцієнтом кореляції між ними говорять, що вони пояснюють дисперсію або мають пояснену дисперсію. Зазвичай пояснена дисперсія виявляється у відсотках. Так R 2 = 0,81 означає, що 81% дисперсії (розкиду) двох змінних є поясненою. Інші 19% обумовлені випадковими флуктуаціями.
У Excel є функція ТЕНДЕНЦІЯ, яка полегшує обчислення. Функція ТЕНДЕНЦІЯ():
- приймає відомі значення, що надаються вами хта відомі значення у;
- обчислює нахил лінії регресії та константу (відрізок);
- повертає прогнозні значення у, що визначаються на підставі застосування рівняння регресії до відомим значенням х(Мал. 7).
Функція ТЕНДЕНЦІЯ() є функцією масиву (якщо ви раніше не стикалися з такими функціями, рекомендую).
Мал. 7. Використання функції ТЕНДЕНЦІЯ() дозволяє прискорити та спростити обчислення порівняно з використанням пари функцій НАКЛОН() та ВІДРІЗОК()
Щоб ввести функцію ТЕНДЕНЦІЯ() у вигляді формули масиву в комірки G3:G12, виділіть діапазон G3:G12, введіть формулу ТЕНДЕНЦІЯ (СЗ:С12;ВЗ:В12), натисніть та утримуйте клавіші
Функція ТЕНДЕНЦІЯ() має ще два аргументи: нові_значення_хі конст. Перший дозволяє побудувати прогноз на майбутнє, а другий може змусити лінію регресії пройти через початок координат (значення ІСТИНА каже Excel використовувати розрахункову константу, значення брехня – константу = 0). Excel дозволяє намалювати регресійну пряму графіку так, щоб вона проходила через початок координат. Почніть із побудови точкової діаграми, після чого клацніть правою кнопкою миші на одному з маркерів ряду даних. Виберіть у відкритому контекстному менюпункт Додати лінію тренду; виберіть варіант Лінійна; за необхідності прокрутіть панель вниз, встановіть прапорець Налаштувати перетин; переконайтеся, що у пов'язаному з ним текстовому полі встановлено значення 0,0.
Якщо у вас є три змінні, і ви хочете визначити кореляцію між двома з них, виключивши вплив третьої, можна використовувати приватну кореляцію. Припустимо, вас цікавить зв'язок між відсотковою часткою мешканців міста, які закінчили коледж, та кількістю книг у міських бібліотеках. Ви зібрали дані по 50 містах, але… Проблема в тому, що обидва ці параметри можуть залежати від добробуту жителів того чи іншого міста. Зрозуміло, дуже важко підібрати інші 50 міст, які характеризуються точно однаковим рівнем добробуту жителів.
Застосовуючи статистичні методиДля виключення впливу, що надається фактором добробуту як на фінансову підтримку бібліотек, так і на доступність навчання в коледжі, ви могли б отримати більш точну кількісну оцінку ступеня залежності між змінними, які вас цікавлять, а саме: кількістю книг і кількістю випускників. Така умовна кореляція між двома змінними, коли значення інших змінних фіксовані і називається приватною кореляцією. Один із способів її розрахунку полягає у використанні рівняння:
Де rCB . W- коефіцієнт кореляції між змінними Коледж (College) та Книги (Books) за виняткового впливу (фіксованого значення) змінної Добробут (Wealth); rCB- коефіцієнт кореляції між змінними Коледж та Книги; rCW- коефіцієнт кореляції між змінними Коледж та Добробут; rBW- Коефіцієнт кореляції між змінними Книги та Добробут.
З іншого боку, приватну кореляцію можна розрахувати з урахуванням аналізу залишків, тобто. різниць між прогнозними значеннями та пов'язаними з ними результатами фактичних спостережень (обидва методи представлені на рис. 8).
Мал. 8. Приватна кореляція як кореляція залишків
Для спрощення підрахунку матриці коефіцієнтів кореляції (B16: E19) використовуйте пакет аналізу Excel (меню Дані –> Аналіз –> Аналіз даних). За промовчанням цей пакет у Excel не активний. Для його встановлення пройдіть по меню Файл –> Параметри –> Надбудови. Внизу вікна, що відкрилося ПараметриExcelзнайдіть поле Управління, Виберіть НадбудовиExcel, клацніть Перейти. Поставте галочку навпроти надбудови Пакет аналізу. Клацніть А наліз даних, виберіть опцію Кореляція. Як вхідний інтервал вкажіть $B$2:$D$13, поставте галочку Мітки у першому рядку, як вихідний інтервал вкажіть $B$16:$E$19.
Ще одна можливість – визначити отримувану кореляцію. Наприклад, ви досліджуєте вплив зростання та віку на вагу. Таким чином, у вас дві передікторні змінні – зростання та вік, і одна прогнозована змінна – вага. Ви хочете виключити вплив однієї передикторної змінної на іншу, але не на прогнозну змінну:
де Н - Зростання (Height), W - Вага (Weight), А - Вік (Age); в індексі отримуваного коефіцієнта кореляції використовуються круглі дужки, за допомогою яких вказується, вплив якої змінної усувається і з якої саме змінної. У разі позначення W(Н.А) свідчить про те, що вплив змінної Вік видаляється зі змінної Зростання, але з змінної Вага.
Може скластися враження, що питання, що обговорюється, не має істотного значення. Адже найважливіше те, наскільки точно працює загальне рівняннярегресії, тоді як проблема відносних вкладів окремих змінних у сумарну пояснену дисперсію видається другорядною. Однак, це далеко не так. Як тільки ви починаєте замислюватися над тим, чи варто взагалі використовувати якусь змінну в рівнянні множинної регресії, проблема стає важливою. Вона може проводити оцінку правильності вибору моделі для аналізу.
Глава 4. Функція Лінейн()
Функція ЛІНІЙН() повертає 10 статистик регресійного аналізу. Функція Лінейн() є функцією масиву. Для її введення виділіть діапазон, що містить п'ять рядків і два стовпці, надрукуйте формулу і натисніть
ЛІНІЙН(B2:B21;A2:A21;ІСТИНА;ІСТИНА)
Мал. 9. Функція ЛІНІЙН(): а) виділіть діапазон D2:E6; б) введіть формулу, як показано в рядку формул, в) натисніть
Функція ЛІНІЙН() повертає:
- коефіцієнт регресії (або нахил, осередок D2);
- відрізок (або константа, осередок Е3);
- стандартні помилкикоефіцієнта регресії та константи (діапазон D3:E3);
- коефіцієнт детермінації R 2 для регресії (комірка D4);
- стандартна помилка оцінки (осередок Е4);
- F-критерій для повної регресії (комірка D5);
- кількість ступенів свободи для залишкової суми квадратів (осередок Е5);
- регресійна сума квадратів (комірка D6);
- залишкова сума квадратів (осередок Е6).
Розглянемо кожну з цих статистик та їхню взаємодію.
Стандартна помилкау разі – це стандартне відхилення, обчислюване для помилок вибірки. Тобто це ситуація, коли генеральна сукупність має одну статистику, а вибірка – іншу. Розділивши коефіцієнт регресії на стандартну помилку, ви отримаєте значення 2,092/0,818 = 2,559. Іншими словами, коефіцієнт регресії, рівний 2,092, від нуля на дві з половиною стандартні помилки.
Якщо коефіцієнт регресії дорівнює нулю, то найкращою оцінкою змінної змінної є її середнє значення. Дві з половиною стандартні помилки - це досить велика величина, і ви впевнено можете вважати, що коефіцієнт регресії для генеральної сукупності має ненульове значення.
Можна визначити можливість отримання вибіркового коефіцієнтарегресії 2,092, якщо його фактичне значення у генеральній сукупності дорівнює 0,0 за допомогою функції
СТЬЮДЕНТ.РАСП.ПХ(t-критерій = 2,559; кількість ступенів свободи =18)
У загальна кількістьступенів свободи = n – k – 1, де n – кількість спостережень, а k – кількість передикторних змінних.
Ця формула повертає значення 0,00987 або, заокруглено, 1%. Воно повідомляє нам наступне: якщо коефіцієнт регресії для генеральної сукупності дорівнює 0%, то ймовірність отримання вибірки з 20 осіб, на яку розрахункове значення коефіцієнта регресії дорівнює 2,092, становить скромний 1%.
F-критерій (комірка D5 на рис. 9) виконує ті ж функції щодо повної регресії, що і t-критерій стосовно коефіцієнта простої парної регресії. F-критерій використовується для перевірки того, чи дійсно коефіцієнт детермінації R 2 для регресії має досить велику величину, що дозволяє відкинути гіпотезу про те, що в генеральній сукупності він має значення 0,0, яке вказує на відсутність дисперсії, що пояснюється передикторної та прогнозованої змінної. За наявності тільки однієї передикторної змінної F-критерій точно дорівнює квадрату t-критерію.
Досі ми розглядали інтервальні змінні. Якщо ж у вас змінні, які можуть набувати кількох значень, що являють собою прості імена, наприклад, Чоловік і Жінка або Плазуни, Земноводне та Риба, уявіть їх у вигляді числового коду. Такі змінні називаються номінальними.
Статистика R 2дає кількісну оцінку частки поясненої дисперсії.
Стандартна помилка оцінки.На рис. 4.9 представлені прогнозні значення змінної Вага, отримані на підставі її зв'язку зі змінною Зростання. У діапазоні Е2:Е21 містяться залишки для змінної Вага. Точніше ці залишки називати помилками - звідси слід термін стандартна помилка оцінки.
Мал. 10. Як R 2 так і стандартна помилка оцінки виражають точність прогнозів, одержуваних за допомогою регресії
Чим менше стандартна помилка оцінки, тим точніше рівняння регресії і тим ближчого збігу будь-якого прогнозу, отриманого за допомогою рівняння, з фактичним спостереженням ви очікуєте. Стандартна помилка оцінки надає спосіб кількісної оцінки цих очікувань. Вага 95% людей, які володіють деяким зростанням, перебуватиме в діапазоні:
(зростання * 2,092 - 3,591) ± 2,092 * 21,118
F-статистика- Це відношення міжгрупової дисперсії до внутрішньогрупової дисперсії. Ця назва була введена статистиком Джорджем Снедекором на честь сера, який розробив на початку XX століття дисперсійний аналіз (ANOVA, Analysis of Variance).
Коефіцієнт детермінації R 2 виражає частку загальної суми квадратів, пов'язану з регресією. Розмір (1 – R 2) висловлює частку загальної суми квадратів, пов'язану із залишками - помилками прогнозування. F-критерій можна отримати з використанням функції ЛІНІЙН (комірка F5 на рис. 11), з використанням сум квадратів (діапазон G10: J11), з використанням часток дисперсії (діапазон G14: J15). Формули можна вивчити в файлі Excel, що додається.
Мал. 11. Розрахунок F-критерію
У разі використання номінальних змінних використовується фіктивне кодування (рис. 12). Для кодування значень зручно використовувати значення 0 та 1. Імовірність F розраховується за допомогою функції:
F.РАСП.ПХ(К2;I2;I3)
Тут функція F.РАСП.ПХ() повертає ймовірність отримання F-критерію, що підпорядковується центральному F-розподілу (рис. 13) для двох наборів даних з кількістю ступенів свободи, наведеними в осередках I2 і I3, значення якого збігається зі значенням, наведеним у осередку К2.
Мал. 12. Регресійний аналіз із використанням фіктивних змінних
Мал. 13. Центральний F-розподіл при λ = 0
Розділ 5. Множинна регресія
Переходячи від простої парної регресії з однієї передикторної змінної до множинної регресії, ви додаєте одну або кілька змінних передикторних. Зберігайте значення передикторних змінних у суміжних стовпцях, наприклад, у стовпцях А та В у разі двох предикторів або А, В та С у разі трьох предикторів. Перш ніж вводити формулу, що включає функцію ЛІНІЙН(), виберіть п'ять рядків і стільки стовпців, скільки є передикторних змінних плюс ще один для константи. У разі регресії з двома передикторними змінними можна використати таку структуру:
Лінейн (А2: А41; В2: С41;; ІСТИНА)
Так само у випадку трьох змінних:
Лінейн (А2: А61; В2: D61;; ІСТИНА)
Припустимо, ви хочете вивчити можливий вплив віку та дієти на вміст ЛПНГ – ліпопротеїнів низької щільності, які вважаються відповідальними за утворення атеросклеротичних бляшок, що служать причиною атеротромбозу (рис. 14).
Мал. 14. Множинна регресія
R 2 множинної регресії (відбивається в комірці F13), більше, ніж R 2 будь-якої простої регресії (Е4, Н4). У множинні регресії одночасно використовуються кілька предикторних змінних. При цьому R2 майже завжди збільшується.
Для будь-якого простого лінійного рівняння регресії з однією предикторной змінною між прогнозними значеннями і значеннями предикторной змінної завжди буде спостерігатися ідеальна кореляція, оскільки у такому рівнянні значення предиктора множаться однією константу і до кожного твору додається інша константа. Цей ефект не зберігається у множинні регресії.
Відображення результатів, що повертаються функцією ЛІНІЙН() для множинної регресії (рис. 15). Коефіцієнти регресії виводяться у складі результатів, що повертаються функцією ЛІНІЙН() у порядку зворотному розташуванню змінних(G-H-I відповідає С-В-А).
Мал. 15. Коефіцієнти та їх стандартні помилки відображаються у зворотному порядку їх прямування на робочому аркуші
Принципи та процедури, що використовуються в регресійному аналізі з однією передикторною змінною, легко адаптуються для обліку кількох предикторних змінних. Виявляється, що багато в цій адаптації залежить від усунення впливу предикторних змінних один на одного. Останнє пов'язане з приватною та отримуваною кореляціями (рис. 16).
Мал. 16. Множинна регресія може бути виражена через парну регресію залишків (формули див. в Excel-файлі)
В Excel є функції, що надають інформацію про t-і F-розподілах. Функції, імена яких включають частину РАСП, такі як СТЬЮДЕНТ.РАСП() і F.РАСП(), приймають t- або F-критерій як аргумент і повертають ймовірність спостереження вказаного значення. Функції, імена яких включають частину ОБР, такі як СТЬЮДЕНТ.ОБР() і F.ОБР(), приймають значення ймовірності як аргумент і повертають значення критерію, що відповідає зазначеній ймовірності.
Оскільки ми шукаємо критичні значення t-розподілу, які відтинають краї його хвостових областей, ми передаємо 5% як аргумент однієї з функцій СТЬЮДЕНТ.ОБР(), яка повертає значення, що відповідає цій ймовірності (рис. 17, 18).
Мал. 17. Двосторонній t-тест
Мал. 18. Односторонній t-тест
Встановлюючи правило прийняття рішень у разі однохвостової альфа-області, ви збільшуєте статистичну потужність тесту. Якщо, приступаючи до експерименту, ви впевнені в тому, що у вас є всі підстави очікувати на отримання позитивного (або негативного) коефіцієнта регресії, то вам слід виконати однохвостовий тест. У цьому випадку ймовірність того, що ви приймаєте правильне рішення, відкидаючи гіпотезу про нульовий коефіцієнт регресії в генеральній сукупності, буде вищою.
Статистики вважають за краще використовувати термін спрямований тестзамість терміна однохвостовий тестта термін ненаправлений тестзамість терміна двохвостовий тест. Терміни спрямований і ненаправлений краще, оскільки наголошують на типі гіпотези, а не на природі хвостів розподілу.
Підхід до оцінки впливу предикторів, заснований на порівнянні моделей.На рис. 19 представлені результати регресійного аналізу, в якому тестується внесок змінної дієти в рівняння регресії.
Мал. 19. Порівняння двох моделей шляхом перевірки відмінностей у їх результатах
Результати функції ЛІНІЙН() (діапазон Н2:К6) мають відношення до того, що я називаю повною моделлю, в якій виконується регресія змінної ЛПНЩ за змінними дієта, вік і ЛПВЩ. У діапазоні Н9:J1З представлені розрахунки без урахування предикторної змінної дієти. Я називаю це обмеженою моделлю. У повній моделі 49,2% дисперсії залежної змінної ЛПНЩ пояснюється передикторними змінними. В обмеженій моделі лише 30,8% ЛПНГ пояснюється змінними Вік та ЛПВЩ. Втрата R 2 обумовлена винятком змінної Дієта з моделі становить 0,183. У діапазоні G15:L17 зроблено розрахунки, які показують, що лише з ймовірністю 0,0288 вплив змінної дієти є випадковим. В інших 97,1% дієта впливає на ЛПНГ.
Глава 6. Допущення та застереження щодо регресійного аналізу
Термін «допущення» не визначено досить суворо, а спосіб його використання передбачає, що якщо припущення не дотримується, то результати всього аналізу є щонайменше сумнівними або, можливо, не мають сили. Насправді це не так, хоча, безумовно, існують випадки, коли порушення припущення докорінно змінює картину. Основні припущення: а) залишки змінної Y нормально розподілені у будь-якій точці X вздовж лінії регресії; б) значення Y знаходяться у лінійній залежності від значень X; в) дисперсія залишків приблизно однакова у кожній точці Х; г) між залишками відсутня залежність.
Якщо припущення не відіграють істотну роль, статистики говорять про робастності аналізу стосовно порушення припущення. Зокрема, коли ви використовуєте регресію для тестування відмінностей між груповими середніми, припущення про те, що значення Y - а отже, і залишки - нормально розподілені, не відіграє суттєвої ролі: тести робастні по відношенню до порушення припущення про нормальність. У цьому важливо аналізувати дані з допомогою діаграм. Наприклад, включених у надбудову Аналіз данихінструмент Регресія.
Якщо дані не відповідають припущенням лінійної регресії, у вашому розпорядженні є інші підходи, відмінні від лінійного. Один із них – логістична регресія (рис. 20). Поблизу верхнього та нижнього граничних значень передикторної змінної лінійна регресія призводить до нереалістичних прогнозів.
Мал. 20. Логістична регресія
На рис. 6.8 відображено результати двох методів аналізу даних, спрямованих на дослідження зв'язку між щорічним доходом та ймовірністю купівлі будинку. Очевидно, ймовірність здійснення покупки збільшуватиметься зі збільшенням доходу. Діаграми спрощують виявлення відмінностей між результатами, що прогнозують можливість купівлі будинку за допомогою лінійної регресії, і результатами, які ви могли б отримати, використовуючи інший підхід.
На мові статистиків відкидання нульової гіпотези, коли насправді є істинною, називається помилкою I роду.
У надбудові Аналіз данихпропонується зручний інструментдля генерації випадкових чисел, що надає користувачеві можливість задати бажану форму розподілу (наприклад, Нормальний, Біноміальний або Пуассон), а також середнє значення та стандартне відхилення.
Відмінності між функціями сімейства СТЬЮДЕНТ.РАСП().Починаючи з версії Excel 2010, доступні три різні формифункції, що повертає частку розподілу зліва та/або праворуч від заданого значення t-критерію. Функція СТЮДЕНТ.РАСП() повертає частку площі під кривою розподілу ліворуч від вказаного вами значення t-критерію. Припустимо, у вас є 36 спостережень, тому кількість ступенів свободи для аналізу дорівнює 34, а значення t-критерію = 1,69. У цьому випадку формула
СТЬЮДЕНТ.РАСП(+1,69;34;ІСТИНА)
повертає значення 0,05 або 5% (рис. 21). Третій аргумент функції СТЬЮДЕНТ.РАСП() може мати значення ІСТИНА або БРЕХНЯ. Якщо він заданий рівним ІСТИНА, функція повертає кумулятивну площу під кривою зліва від заданого t-критерію, виражену у вигляді частки. Якщо ж він брехня, функція повертає відносну висоту кривої в точці, що відповідає t-критерію. Інші версії функції СТЬЮДЕНТ.РАСП() - СТЬЮДЕНТ.РАСП.ПХ() і СТЬЮДЕНТ.РАСП.2Х() - приймають як аргументи лише значення t-критерію та кількість ступенів свободи і не вимагають завдання третього аргументу.
Мал. 21. Більш темна затінена область у лівому хвості розподілу відповідає частці площі під кривою зліва від великого позитивного значення t-критерію
Щоб визначити площу праворуч від t-критерію, використовуйте одну з формул:
1 - СТИОДЕНТ.РАСП (1, 69; 34; ІСТИНА)
СТЬЮДЕНТ.РАСП.ПХ(1,69;34)
Вся площа під кривою повинна становити 100%, тому віднімання з 1 частки площі зліва від значення t-критерію, яку повертає функція, дає частку площі, що знаходиться праворуч від значення t-критерію. Можливо, вам здасться кращим варіант безпосереднього отримання частки площі, що вас цікавить, за допомогою функції СТЬЮДЕНТ.РАСП.ПХ(), де ПХ означає правий хвіст розподілу (рис. 22).
Мал. 22. 5% альфа область для спрямованого тесту
Використання функцій СТЬЮДЕНТ.РАСП() або СТЬЮДЕНТ.РАСП.ПХ () передбачає, що ви вибрали спрямовану робочу гіпотезу. Спрямована робоча гіпотеза у поєднанні із встановленням значення альфа на рівні 5% означає, що ви поміщаєте всі 5% у правий хвіст розподілами. Ви повинні відкинути нульову гіпотезу лише в тому випадку, якщо ймовірність отриманого вами значення t-критерію складе 5% і менше. Спрямовані гіпотези зазвичай призводять до більш чутливих статистичних тестів (цю велику чутливість також називають більшою статистичною потужністю).
При ненаправленому тесті значення альфа залишається тому ж рівні 5%, але розподіл буде іншим. Оскільки ви повинні допускати два результати, ймовірність хибнопозитивного результату повинна бути розподілена між двома хвостами розподілу. Загальноприйнято розподіляти цю можливість порівну (рис. 23).
Використовуючи те саме отримане значення t-критерію і ту ж кількість ступенів свободи, що і в попередньому прикладі, скористайтеся формулою
СТЬЮДЕНТ.РАСП.2Х(1,69;34)
Без будь-яких особливих причин функція СТЬЮДЕНТ.РАСП.2Х() повертає код помилки #ЧИСЛО!, якщо як перший аргумент їй надається негативне значення t-критерію.
Якщо вибірки містять різне числоданих, скористайтеся двовибірковим t-тестом із різними дисперсіями, включеним у пакет Аналіз даних.
Глава 7. Використання регресії для тестування відмінностей між груповими середніми
Змінні, які раніше фігурували під назвою прогнозованих змінних, у цьому розділі будуть називатися результативними змінними, а замість терміну передикторні змінні використовуватиметься термін факторні змінні.
Найпростішим із підходів до кодування номінальною змінною є фіктивне кодування(Рис. 24).
Мал. 24. Регресійний аналіз на основі фіктивного кодування
При використанні фіктивного кодування будь-якого роду слід дотримуватись правил:
- Кількість стовпців, що резервуються для нових даних, має дорівнювати кількості рівнів фактора мінус
- Кожен вектор становить один рівень фактора.
- Суб'єкти одного з рівнів, яким часто є контрольна група, одержують код 0 у всіх векторах.
Формула в осередках F2:H6 = ЛІНЕЙН(A2:A22;C2:D22;;ІСТИНА) повертає регресійні статистики. Для порівняння на рис. 24 відображено результати традиційного дисперсійного аналізу, що повертається інструментом Однофакторний дисперсійний аналізнадбудови Аналіз даних.
Кодування ефектів.В іншому типі кодування, який отримав назву кодування ефектів,Середнє кожну групу порівнюється з середнім групових середніх. Цей аспект кодування ефектів обумовлений використанням значення -1 замість 0 як код для групи, яка отримує один і той же код у всіх кодових векторах (рис. 25).
Мал. 25. Кодування ефектів
Коли використовується фіктивне кодування, значення константи, що повертається функцією ЛІНІЙН(), збігається із середньою групою, якій у всіх векторах призначені нульові коди (зазвичай це контрольна група). У разі кодування ефектів константа дорівнює загальному середньому (комірка J2).
Загальна лінійна модель корисний спосібконцептуалізації компонентів значення результуючої змінної:
Y ij = μ + α j + ε ij
Використання в цій формулі грецьких літер замість латинських підкреслює той факт, що вона відноситься до генеральної сукупності, з якої витягуються вибірки, але її можна переписати у вигляді, що вказує на те, що вона відноситься до вибірок, які витягують виданої генеральної сукупності:
Y ij = Y ̅ + a j + e ij
Ідея у тому, що кожне спостереження Y ij можна як суму наступних трьох компонентів: загальне середнє, μ; ефект обробки j, а j; величина e ij , яка представляє відхилення індивідуального кількісного показника Y ij від комбінованого значення загального середнього та ефекту j-йобробки (рис. 26). Метою рівняння регресії є мінімізація суми квадратів залишків.
Мал. 26. Спостереження, розкладені на компоненти загальної лінійної моделі
Факторний аналіз.Якщо досліджується зв'язок між результативною змінною та одночасно двома або більше факторами, то в цьому випадку говорять про використання факторного аналізу. Додавання одного або кількох факторів до однофакторного дисперсійного аналізу може збільшувати статистичну потужність. В однофакторному дисперсійному аналізі варіація результативної змінної, яка не може бути приписана фактору, включається до залишкового середнього квадрата. Але цілком може бути так, що ця варіація пов'язана з іншим фактором. Тоді ця варіація може бути видалена із середньоквадратичної помилки, зменшення якої призводить до збільшення значень F-критерію, а отже, до збільшення статистичної потужності тесту. Надбудова Аналіз данихвключає інструмент, що забезпечує обробку двох факторів одночасно (рис. 27).
Мал. 27. Інструмент Двофакторний дисперсійний аналіз із повтореннями Пакету аналізу
Використаний у цьому малюнку інструмент дисперсійного аналізу, корисний тим, що він повертає середнє і дисперсію результативної змінної, і навіть значення лічильника кожної групи, включеної у план. В таблиці Дисперсійний аналіз відображаються два параметри, відсутні у вихідній інформації однофакторної версії інструменту дисперсійного аналізу. Зверніть увагу на джерела варіації Вибіркаі Стовпціу рядках 27 та 28. Джерело варіації Стовпцівідноситься до статі. Джерело варіації Вибіркавідноситься до будь-якої змінної, значення якої займають різні рядки. На рис. 27 значення для групи КурсЛеч1 знаходяться у рядках 2-6, групи КурсЛеч2 - у рядках 7-11, а групи КурсЛечЗ - у рядках 12-16.
Головний момент полягає в тому, що обидва фактори, Пол (підпис Стовпці в осередку Е28) та Лікування (підпис Вибірка в осередку Е27), включені в таблицю Дисперсійний аналіз як джерела варіації. Середні чоловіки відрізняються від середніх жінок, і це створює джерело варіації. Середні для трьох видів лікування також різняться – ось вам ще одне джерело варіації. Існує також третє джерело – Взаємодія, що відноситься до об'єднаного ефекту змінних Підлога та Лікування.
Глава 8. Коваріаційний аналіз
Коваріаційний аналіз, або ANCOVA (Analysis of Covariation) зменшує зміщення та збільшує статистичну потужність. Нагадаю, що одним із способів оцінки надійності регресійного рівнянняє F-тести:
F = MS Regression /MS Residual
де MS (Mean Square) - середній квадрат, а індекси Regression та Residual вказують на регресійну та залишкову компоненти відповідно. Розрахунок MS Residual виконується за такою формулою:
MS Residual = SS Residual / df Residual
де SS (Sum of Squares) – сума квадратів, a df – кількість ступенів свободи. Коли ви додаєте підступ до рівняння регресії, деяка частка загальної суми квадратів включається не в SS ResiduaI, а в SS Regression. Це призводить до зменшення SS Residual, а значить, і MS Residual. Чим менше MS Residual, тим більше F-критерій і тим ймовірніше, що ви відкинете нульову гіпотезу про відсутність відмінностей між середніми. В результаті ви перерозподіляєте мінливість результативної змінної. У ANOVA, коли коваріація не враховується, мінливість перетворюється на помилку. Але в ANCOVA частина мінливості, що раніше відносилася до помилки, призначається коваріатом і стає частиною SS Regression.
Розглянемо приклад, у якому той самий набір даних аналізується спочатку з допомогою ANOVA, та був з допомогою ANCOVA (рис. 28).
Мал. 28. Аналіз ANOVA вказує на те, що результати, отримані за допомогою рівняння регресії, є ненадійними.
У дослідженні порівнюються відносні ефекти фізичних вправ, що розвивають м'язову силу, та когнітивних вправ (розгадування кросвордів), що активізують мозкову діяльність. Суб'єкти були випадково розподілені по двох групах, щоб на початку експерименту обидві групи знаходилися в однакових умовах. Після трьох місяців було виміряно когнітивні характеристики суб'єктів. Результати цих вимірювань наведено у стовпці Ст.
У діапазоні А2:С21 розміщені вихідні дані, що передаються функції ЛІНІЙН() для виконання аналізу з використанням кодування ефектів. Результати роботи функції ЛІНІЙН() наведено в діапазоні E2:F6, де в осередку Е2 відображається коефіцієнт регресії, пов'язаний з вектором впливу. У осередку Е8 міститься t-критерій = 0,93, а в осередку Е9 тестується надійність цього t-критерію. Значення, що міститься в осередку Е9, говорить про те, що ймовірність зустріти різницю між груповими середніми, що спостерігається в даному експерименті, становить 36%, якщо в генеральній сукупності групові середні рівні. Лише мало хто визнає цей результат статистично значущим.
На рис. 29 показано, що відбудеться при додаванні кваріати до аналізу. У цьому випадку я додав у набір даних вік кожного суб'єкта. Коефіцієнт детермінації R 2 для рівняння регресії, в якому використовується коваріату, дорівнює 0,80 (комірка F4). Значення R 2 в діапазоні F15:G19, в якому я відтворив результати ANOVA, отримані без використання кваріати, дорівнює лише 0,05 (комірка F17). Отже, рівняння регресії, що включає коваріату, передбачає значення змінної когнітивного показника набагато точніше, ніж з використанням тільки вектора Вплив. Для ANCOVA ймовірність випадкового отримання значення F-критерію, що відображається в комірці F5, дорівнює менш ніж 0,01%.
Мал. 29. ANCOVA повертає зовсім іншу картину
У Excelє ще швидший та зручний спосібпобудувати графік лінійної регресії (і навіть основних видів не лінійних регресій, Про що див. далі). Це можна зробити так:
1) виділити стовпці з даними Xі Y(Вони повинні розташовуватися саме в такому порядку!);
2) викликати Майстер діаграмта вибрати в групі Тип – Крапковаі відразу натиснути Готово;
3) не скидаючи виділення з діаграми, вибрати пункт основного меню, що з'явилася Діаграма, в якому слід вибрати пункт Додати лінію тренду;
4) у діалоговому вікні, що з'явилося Лінія трендуу вкладці Типвибрати Лінійна;
5) у вкладці Параметриможна активувати перемикач Показувати рівняння на діаграміщо дозволить побачити рівняння лінійної регресії (4.4), в якому будуть обчислені коефіцієнти (4.5).
6) У цій же вкладці можна активувати перемикач Помістити на діаграму величину достовірності апроксимації (R^2). Ця величина є квадрат коефіцієнта кореляції (4.3) і показує, наскільки добре розраховане рівняння описує експериментальну залежність. Якщо R 2 близький до одиниці, теоретичне рівняння регресії добре описує експериментальну залежність (теорія добре узгоджується з експериментом), а якщо R 2 близький до нуля, це рівняння не придатне для опису експериментальної залежності (теорія не узгоджується з експериментом).
Внаслідок виконання описаних дій вийде діаграма з графіком регресії та її рівнянням.
§4.3. Основні види нелінійної регресії
Параболічна та поліноміальна регресії.
Параболічнійзалежністю величини Yвід величини Хназивається залежність, виражена квадратичною функцією(параболою 2-го порядку):
Це рівняння називається рівнянням параболічної регресії Yна Х. Параметри а, b, зназиваються коефіцієнтами параболічної регресії. Обчислення коефіцієнтів параболічної регресії завжди є громіздким, тому для розрахунків рекомендується використовувати комп'ютер.
Рівняння (4.8) параболічної регресії є окремим випадком більш загальної регресії, званої поліноміальної. поліноміальноїзалежністю величини Yвід величини Хназивається залежність, виражена поліномом n-ого порядку:
де числа а i (i=0,1,…, n) називаються коефіцієнтами поліноміальної регресії.
Ступенева регресія.
Ступіньноюзалежністю величини Yвід величини Хназивається залежність виду:
Це рівняння називається рівнянням статечної регресії Yна Х. Параметри аі bназиваються коефіцієнтами статечної регресії.
ln = ln a+b · ln x. (4.11)
Це рівняння визначає пряму на площині з логарифмічними координатними осями ln xі ln. Тому критерієм застосування статечної регресії служить вимога того, щоб точки логарифмів емпіричних даних ln x iта ln у iзнаходилися найближче до прямої (4.11).
Показова регресія.
Показовою(або експоненційною) залежністю величини Yвід величини Хназивається залежність виду:
(або). (4.12)
Це рівняння називається рівнянням показовою(або експоненційною) регресії Yна Х. Параметри а(або k) та bназиваються коефіцієнтами показової(або експоненційною) регресії.
Якщо прологарифмувати обидві частини рівняння статечної регресії, то вийде рівняння
ln = x· ln a+ln b(або ln = k x+ln b). (4.13)
Це рівняння описує лінійну залежністьлогарифма однієї величини ln від іншої величини x. Тому критерієм застосування статечної регресії служить вимога того, щоб точки емпіричних даних однієї величини x iта логарифми іншої величини ln у iзнаходилися найближче до прямої (4.13).
Логарифмічна регресія.
Логарифмічноюзалежністю величини Yвід величини Хназивається залежність виду:
=a+b · ln x. (4.14)
Це рівняння називається рівнянням логарифмічної регресії Yна Х. Параметри аі bназиваються коефіцієнтами логарифмічної регресії.
Гіперболічна регресія.
Гіперболічнійзалежністю величини Yвід величини Хназивається залежність виду:
Це рівняння називається рівнянням гіперболічної регресії Yна Х. Параметри аі bназиваються коефіцієнтами гіперболічної регресіїта визначаються методом найменших квадратів. Застосування цього методу призводить до формул:
У формулах (4.16-4.17) підсумовування проводиться за індексом iвід одиниці до кількості спостережень n.
На жаль, у Excelнемає функції, що обчислюють коефіцієнти гіперболічної регресії. У тих випадках, коли свідомо не відомо, що вимірювані величини пов'язані зворотною пропорційністю, рекомендується замість рівняння гіперболічної регресії шукати рівняння статечної регресії, так Excelє процедура її знаходження. Якщо між вимірюваними величинами передбачається гіперболічна залежність, то коефіцієнти її регресії доведеться обчислювати за допомогою допоміжних розрахункових таблиць і операцій підсумовування за формулами (4.16-4.17).
Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.
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%. Значит вычисления достаточно точны.
Якщо виникне потреба, за набутими значеннями ми можемо побудувати графік.
Файл з прикладом додається - ПОСИЛАННЯ!
Категорії:// Від 28.10.2017
