Лінійна і експонентна регресії – ЧАСТИНА 2

Рис 173 Функція ЛИНЕЙН обчислює кутовий коефіцієнт прямої залежності і точку перетину лінії регресії з віссю Y

Приклад регресійного аналізу

Одну з популярних моделей регресійного аналізу багато хто називає порівняльним аналізом ринку (Competitive Market Analysis – СМА) Агенти з продажу нерухомості використовують цей метод для отримання оціночної вартості будинків на основі даних про проведені раніше продажах приблизно таких же будівель в тому ж самому регіоні На наведеному нижче малюнку показаний приклад побудови цієї моделі засобами Excel документ ми назвали Оцінювачем нерухомості

Тут наявні дані з областіВхідні Даніаналізуються за допомогою функції ЛИНЕЙН, і за отриманими результатами в області Пропозиція генерується оціночна вартість Масив, повертається функцією ЛИНЕЙН, насправді розташовується за видимою областю робочого листа, як

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

У нормальному стані цієї робочої книги приховані заголовки рядків і стовпців і рядки з 25 по 37, включена захист робочого листа, а також захищені всі комірки, крім тих, в які потрібно вводити вихідні дані І нарешті, дійсно корисну пораду: як сказано в примітці до документа, він дозволяє отримати всього лише оціночну вартість, і якщо вам вдасться дізнатися дійсну ціну нерухомості, вона, безсумнівно, буде більш реалістичною

Функція Тендеція

Якщо функція ЛИНЕЙН повертає параметри прямої лінії, апроксимуючої наявні дані, то функція ТЕНДЕНЦІЯ (TREND) дозволяє визначити точки, що лежать на цій прямій Масив чисел, видаваних функцією Тендеція, можна використовувати для побудови лінії тренда – прямий, що допомагає оцінити і зрозуміти поведінку фактичних даних Крім того, ця функція дозволяє екстраполювати дані, то Тобто будувати прогноз майбутніх значень на основі тенденції відхилень, виявленої для існуючих даних (Потрібно врахувати те, що функція ТЕНДЕНЦІЯ лише виражає вихідні дані прямою лінією, але вона нічого не говорить про те, наскільки добре ця лінія буде передбачати майбутні значення Зробити висновки на цей рахунок допоможе статистика, яка повертається функцією ЛИНЕЙН)

Функція ТЕНДЕНЦІЯ має чотири аргументи:ізвестние_значенія_у ізвестние_ значенія_x, новие_значепія_x, констПерші два є відомими значеннями залежних і незалежних змінних відповідно Як і у випадку з функцією ЛИНЕЙН, аргументізвестние_зпачепія_уможе бути представлений одним стовпцем, одним рядком або прямокутним діапазоном Опис цих двох аргументів наводилося раніше, при розгляді функції ЛИНЕЙН

Наступні два аргументи можуть бути опущені Аргументновие_значенія_х –це нові значеннях,для яких функція ТЕНДЕНЦІЯ повертає відповідні значення у При його відсутності в списку покладається, що аргументи новие_значенія_х і ізвестние_значенія_х збігаються Якщо аргумент конст має значення ІСТИНА або не заданий, коефіцієнт b обчислюється звичайним чином, а коли його значення БРЕХНЯ, b покладається рівним 0

Для розрахунку лінії тренда опустіть третій і четвертий аргументи цієї функції, тоді масив результатів матиме точно такий же розмір, що й масив відомих значень хНа рис 174 для знаходження координат кожної точки на лінії регресії, апроксимуючої дані, представлені на рис 173, застосовувалася функція ТЕНДЕНЦІЯ Щоб отримати ці значення, необхідно виділити діапазон С2: С19 і ввести формулу = ТЕНДЕНЦІЯ (В2: В19 А2: А19) як формулу масиву, натиснувши клавіші Ctrl + Shift + Enter

Рис 174 Функція ТЕНДЕНЦІЯ повертає значення для лінії регресії, що дозволяє використовувати їх для побудови діаграми

Щоб виконати екстраполяцію на основі наявних даних, необхідно задати діапазон для аргументуновие_значенія_х,він може складатися з будь-якої кількості осередків Масив результатів виявиться точно такого ж розміру, що й зазначений діапазон На рис 175 для розрахунку попиту на 19, 20 і 21-й місяці роботи підприємства застосовувалася функція ТЕНДЕНЦІЯ Ми ввели числа з 19 по 21 в осередку А21: А23, потім виділили діапазон С21: С23 і ввели формулу = ТЕНДЕНЦІЯ (В2: В19 А2: А19 А21: А23) як формулу масиву

Функція ПРЕДСКАЗ

Функція ПРЕДСКАЗ (FORECAST) аналогічна функції ТЕНДЕНЦІЯ, за винятком того, що вона повертає лише одну точку на лінії регресії, а не масив, який визначає цю лінію Дана функція має такі аргументи:х ізвестние_значенія_у ізвестние_значенія_х

Координата х – це точка на осі X, для якої потрібно виконати екстраполяцію даних Наприклад, щоб отримати оцінне значення для комірки

С23 на рис 175, зручно замість функції ТЕНДЕНЦІЯ скористатися функцією ПРЕДСКАЗ: = ПРЕДСКАЗ (21 В2: В19 А2: А19) У даному випадку аргумент х це 21-я точка на побудованій лінії регресії За допомогою функції ПРЕДСКАЗ можна визначити значення для будь-якої точки залежності

Рис 175 Функція ТЕНДЕНЦІЯ дозволяє провести прогнозну оцінку обсягів продажів за 19, 20 і 21-й місяці роботи підприємства

Функція НАХИЛ

Функція НАХИЛ (SLOPE) повертає кутовий коефіцієнт прямої регресії, який визначається як результат ділення відстані між двома точками на лінії регресії по вертикалі на відстань між ними по горизонталі Значення функції НАХИЛ збігається з першим числом в масиві, повертаним функцією ЛИНЕЙН Функція НАХИЛ має такі аргументи:відомі_    значення _у \ ізвестние_значенія_х

Щоб знайти кутовий коефіцієнт лінії регресії для даних, представлених на рис 175, можна записати формулу = НАХИЛ (В2: В19 А2: А19) як формулу масиву Вона поверне значення 20,613

Функція CTOШYX

Функція CTОШYX (STEYX) обчислюєстандартну помилкурегресії як міру кількості помилок у передвіщених значеннях у для кожного значення х в залежності Її аргументи:відомі _значенія_у ізвестние_значенія_хЯкщо застосувати цю функцію до даних, представленим на рис 175, формула = CTОШYX (B2: B19 A2: A19) поверне значення нормальної помилки 12,96562

Експонентна регресія

На відміну від методу лінійної регресії, який апроксимує наявні дані прямою лінією, в методі експоненційної регресія для цього використовується крива,

розраховується по масиву вихідних значень Її рівняння виглядає наступним чином:

x1 x2 хп

у = b × m1

×m2

× ..× Тn

Якщо є лише одна незалежна змінна, то це рівняння прийме вид у=b × тх

Функція ЛГРФПРІБЛ

Функція ЛГРФПРІБЛ (LOGEST) працює так само, як функція ЛИНЕЙН, але використовується при аналізі нелінійних залежностей Вона обчислює параметри експоненціального наближення, тобто значення коефіцієнтів для кожної незалежної змінної і константуb  Функція ЛГРФПРІБЛ має ті ж самі аргументи (Відомі    зіачеіія_у відомі _значенія_х конст статистика), що і функція ЛИНЕЙН, і повертає аналогічний масив результатів

Якщо для необовязкового аргументу статистика задано значення ІСТИНА, то разом з параметрами експоненційної кривої функція додатково генерує статистику по регресії Детальніше про аргументи функції ЛИНЕЙН і повертається статистикою розповідається в розділі «Функція ЛИНЕЙН» даної глави

Функція РОСТ

У той час як функцію ЛГРФПРІБЛ цікавлять параметри експоненційної кривої, апроксимуючої безліч вихідних даних, функція РОСТ (GROWTH) дозволяє знаходити координати точок, що лежать на цій кривій Вона працює точно так само, як її аналог в лінійної регресії – функція ТЕНДЕНЦІЯ, і має подібні аргументи (Відомі _значенія_у ізвестние_значенія_х повие_ значеннях конст) Ми їх вже розглядали детально в розділі «Функція ТЕНДЕНЦІЯ» даної глави

Інструменти пакета аналізу

Якщо при установці Excel був обраний повний набір компонентів, то функції та інструменти надбудови Пакет аналізу будуть доступні при кожному запуску програми Щоб звернутися до цих інструментів, виберіть у меню Сервіс команду Аналіз даних (Data Analysis) На екрані зявиться однойменне діалогове вікно, зображене на мал 176

Описова статистика

Інструмент Описова статистика (Descriptive Statistics) дозволяє формувати таблицю основних статистичних характеристик і параметрів для одного або декількох наборів вихідних значень, як показано на рис 177

Щоб застосувати інструмент Описова статистика, виберіть у меню Сервіс команду Аналіз даних, а потім у списку Інструменти аналізу (Analysis Tools) виберіть цей інструмент і натисніть кнопку ОК На екрані зявиться діалогове вікно Описова статистика (Descriptive Statistics), представлене на рис 178

Рис 176 Діалогове вікно Аналіз даних містить список доступних інструментів

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*