Закiнчiть роботу. Закрийте вiкна.

Практична робота № 5

Тема

МS Ехсеl. Задачi апроксимацiї i прогнозування даних. Метод найменших квадратiв. Елементи регресiйного аналiзу. Побудова лiнiй тренду на дiаграмах.

Мета

Умiти використовувати математичнi функцiї для роботи з масивами даних i статистичнi функцiї для дослiдженяя тенденцiй (тренду) в даних.

Задача 6 „Прогноз доходу фірми”

Протягом перших десяти мiсяцiв року доходи фiрми Digital були такими: 200 000, 220 000, 230 000, 225 000, 235 000, 225 000, 230 000, 250 000, 245 000, 280 000. Побудувати лiнiю тренду i спрогнозувати дохiд фiрми у листопадi й груднi.

План

1. Поняття про метод найменших квадратiв.

2. Реалiзацiя регресiйного аналiзу в ЕТ.

3. Використання функцi Тrend.

4. Використання функцi Linest.

5. Поняття про масиви i формули масивiв.

6. Побудова лiнiй тренду за допомогою дiаграм.

Теоретичні відомості

1. Поняття про метод найменших квадратiв. Метод найменших квадратiв (МНК) застосовють для реазв’язування задач про згладження експериментальних даних та апроксимацію (наближення) даних деякою нескладною аналітичною функцією з метою використання цієї функції для прогнозування подальших змiн даних. Такий аналіз даних також називають регресійним аналізом.

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

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

Це приклади однофакторних задач. ‘У першій задачi фактором є дані які впливають на змiну даних, а у другій — місяці року.

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

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

Заздалегідь складно визначити який тип функції є оптимальним для конкретних даних, зокрема. якщо їх багато. Тому якiсть апроксимації оцiнюють на пiдставi критерію який називається „Критерій R-квадрат” (використовують також позначення r2). Значення r2 для рiзних функцiй буде рiзним. Апроксимація вважається тим ліпшою, чим ближче значення r2 до числа 1, та ідеальною, якщо r2=1

Нехай у деякій однофакторнiй задачi кількість експериментальних даних п, значення фактора (незалежної величини аргументу функції утворюють масив чисел х1, х2, х3,..., хп, значення екепериментальних даних утворюють масив у1, у2,..., уп. Нехай для апроксимації вибрано і визначено функцію f(x).

Тодi r2 обчислюють так:

r2 = 1—Е/Т, де Е= Σ (у1 — f(x1))². Т = Σ y²_i —(Σ y_i)²/n, а суми обчислюють за iндексом i вiд 1 до п.

Найчастiше припускають, що тренд має лiнiйний характер. На основi такого припущення вибирають функцiю f(x) = mx+b. Ріняння вигляду у = f(x) називають рiвнянням регресії. Числа m та b отримують з вхiдних даних за допомогою такого алгоритму:

1) х_с= Σ х_і/п — середнє значення фактора;

2) у_с = Σ у_і/п — середнє значення експериментальних даних

3) m = Σ (х_і — х_с)(у_і —у_с)/ Σ (х_і — х_с)²

4) b = у_с – m* х_с

Для обчислення суми чи середнiх значень використовую вiдповiднi функції ЕТ.

2. Реалiзацiя регресiйного аналiзу в ЕТ. Розглянемо найпростiший спосiб прогнозування даних. Достатньо виокремити дiапазон з експериментальними даними У і перетягнути маркер копiювання на k клiтинок (вниз) - отримаємо лiнiйний прогноз для точок х_n+1, х_{n+2, …,} х_n+k. Якщо перетягування виконати правою клавiшею миші, то з контекстного меню можна вибрати тип апроксимації: лiнiйною mx+b чи експоненцiальною b m^х функцiєю (залежно вiд тенденцiй у даних). Недолiками такого способу є припущення, що фактором Х е масив чисел 1, 2, 3,..., а також нереагування прогнозованих значень на змiни в експериментальних даних.

Інший спосiб розв’язування задачi — це застосування iнструмента Регресії, який запускають командою Сервiс - Аналiз даних... - Регресiї - ОК. Вiн забезпечуе лiнiйну апроксимацiю даних і повертає статистичну iнформацiю, що дає змогу оцiнити якiсть апроксимації.

Головним способом розв’язування задачi лiнiйної апроксимації i прогнозування даних є використання статистичної функції Тrend (дiапазон1; дiапазон2; дiапазонЗ). Дiапазон1 ма’ мiстити експериментальнi данi у_i,, дiапазон2 — значення х_i, дiапазон3 має мiстити точки для прогнозу х_n+1, х_{n+2, …,} х_n+k. Функцiю можна використовувати з двома аргументами або з трьома. У першому випадку розв’язують задачу апроксимації експериментальних даних прямою лiнiєю, а в iншому випадку — задачу прогнозування даних.

21. Використання функцiї Тrend. Розглянемо алгоритм розв’язування задачi апроксимацiї даних прямою лiнiєю, який реалiзується статистичною функцiею Тrend.

1. Утворити вертикальний дiапазон з х_і, який назвати Х.

2. Утворити вертикальний дiапазон з у_і, який назвати У.

3. Поряд вибрати порожнiй вертикальний дiапазон з п клiтинок.

4. У першу клiтинку ввести формулу = Тrend (У; Х).

5. Натиснути на клавiшу F2.

6. Натиснути на комбінацію клавiш Shift+Ctrl+Enter - результати f(x_і) заповнять порожнiй дiапазон.

Розглянемо алгоритм прогнозування данях для значень фактора х_n+1, х_{n+2, …,} х_n+k

7. Утворити вертикальний дiапазон iз зазначених значень фактора пiд дiапазоном Х і назвати його Е.

8. Поряд вибрати порожнiй вертикальний дiапазон з k клiтинок.

9. У першу клiтинку ввести формулу == Тrend (У; Х; Z).

10. Натиснутя на клавiшу F 2.

11. Натиснути на комбiнацію клавiш Shift+Ctrl+Enter — результат-прогрноз f(x_і), і=п+1,...k, заgовнить порожнiй діапазон.

Тепер можна виконати додатковi обчислення для визначення значень m та b та r2 за формулами, якi наведенi вище.

Якщо виявиться, що апроксимацію виконано невдало (тенденцiя змiни даних є далекою вiд лiнiйної, г2< 0, 8) то слiд вибрати iншу функцiю, наприклад Growth(), яка здiйснює експоненціальну апроксимацію або скористатися графічним способом розв’язування задачi, який має бiльший набiр апроксимацiйних функцiй.

4. Використання функції LINEST Функцiя LINEST(Y; X; True; True) повертає масив, перший рядок якого — це значення m та b, другий — це стандартнi похибки цих двох значень вiдповiдно, третiй рядок мiстить значення г2 і стандартну похибку для функцiї, четвертий рядок мiстить данi, які використовуються в теорії F-статистики. Цiкавою особливiстю функцiї є те, що її можна використати для лiнiйного атофакторного регресiйного аналiзу. Для експоненціального аналiзу є функція LOGEST().

5. Поняття про масиви і формули масивiв. Одновимiрним масивом чисел називають послiдовнiсть чисел, взятих у фiгурні дужки, наприклад {1; 1, 5; 4, 2}. Двовимiрний масив мистить набори чисел, якi розмежованi символом двокрапка (якщо числа вiдокремлюються крапкою з комою і кома є десятковим роздiлювачем) або крапкою з комою (якщо роздiлювачами є кома та крапка вiдповiдно) наприклад {1; 1, 5; 4, 2: 1; 1, 5; 4, 2). Один набiр чисел вiдповiдае рядку чисел у зображеннi масиву у виглядi матрицi. Дiапазон з числами можна трактувати як масив чисел (матрицю).

Над матрицями-масивами визначеиi операцiї додавання, вiднiмання, множення на число, а також такi функцї: для множення матрицi на матрицю – ММU(матриця 1; матриця2), транспонування ТRАNSPOSE (масив чи дiапазон), обчисленн оберненої матрицi - MINVERSE(масив) та детермінованої матриці- MDETERM(матриця).

Зауважимо, що дiапазони можна перемножувати, але це не тотожно добутковi масивiв-матриць. Добуток двох дiапазонiв - це дiапазон з покомпонентно перемноженими елементами, що можна використати для розв’язування багатьох задач. Наприклад, вартiсть всiх товарiв (одне число) у задачi про товарниi чек можна визначити за допомогою такої формули: {= SUM(Кількiсть* Цiна)).

Формулу для дiй з масивами чи дiапазонами називають. формулою масиву. Перед виконанням дiй з масивами потрiбно вибрати порожнiй дiапазон клiтинок, де мiститиметься результат обчислення формули-масиву. Особливiсть дiй користувача така:

пiсля набору формули у рядку формул її вводять у ЕТ не простим натисканням клавiшi Еntег, а комбiнацiєю клавiш Shift+Ctrl+ Еntег. Формула масиву буде записана у фiгурних дужках автоматично (їх не набирають). Формули масивiв слiд застосувати в цiй роботi для обчислеиня значень m, b та г2.

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

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

Є шiсть рiзних типiв лiнiй тренду, якi можна додати до дiаграми. Тип лінії тренду потрiбно вибирати, виходячи з типу даних (вiзуально аналiзуючи тенденцiї в даних).

1. Лiнiйна апроксимацiя — це пряма лiнiя, яка наближено описує сукупнiсть даних. Її застосовують в найпростiших випадках, коли данi розташовані близько до прямої.

2. Логарифмiчну апроксимацiю використовують для опису величини, яка спочатку швидко зростає або зменшується, а потiм поступово стабiлiзується. Для логарифмiчної апроксимацiї можна використовувати як вiд’ємнi, так і додатнi значення даних.

З. Полiноміальну апроисимацiю використовують для опису величин, якi то плавно зростають то спадають, а також для аналiзу великої кількості даних. Степiнь полiнома (до шостого) визначають кiлъкiстю екстремумів (максимумів i мінімумів) серед даних. Полiном другого степени може описати лише один максимум або мiнiмум Полiном третього степеня може мати два екстремуми і т. д.

4. Степеневу апроксимацію використовують для опису величини, що монотонно эростає або монотонно спадає, наприклад, вiдстанi, яку проходить автомобiль пiд час розгону.

5. Екслонеацiйну апроксимацію використовують у тому випадку, якщо швидкість змiни даних безперервно зростає на црикладі задач розмноження вiрусів. Неможливо застосувати степевеву й експоненційну апроксимацію якщо дані мiстять нульовi або вiд’ємні значення.

6 Є ще метод апроксимаії даних, який називають змiнним середнім (лiнійною фільтрацією рос. скользящее среднее). Лiнiя тренду будується за певною кількістю точок -вони дуже розкидані. Данi усереднюють попарно чи інакше й одержанi середні значення використовують для апроксимації. Якщо значення параметра Точки є 2, першу точку кривої визначають як середнє значенния перших двох даних, другу точку – як середнє значення другого і третього даних і т.д.

Розглянемо алгоритм додавання лінії тренду до дiаграми.

1. Побудувати дiаграму для ряду експериментальних даних.

2. Клацнути правою клавішею на маркері ряду даних, для яких треба побудувати лінію тренду – отримаємо контектне меню ряду даних

З. Виконати команду Додати лiнію тренду.

4. На закладці Тип вибрати тип лінії тренду.

5. На закладці Параметри задати назву кривої (можна не задавати) довжину відрізка (в одиницяхИ зміни аргументу) прогнозу, координату точки перетину з вiссю У (можна не задавати) зобразити рiвняння регресії на дiаграмі, розташувати на дiаграмi значения г2.

6. ОК.

Хiд роботи: