17172

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

Практическая работа

Информатика, кибернетика и программирование

Практична робота №17 Тема: Ms Excel. Задачі апроксимації і прогнозування даних. Метод найменших квадратів. Елементи регресійного аналізу. Побудова ліній тренду на діаграмах. Мета: Уміти використовувати математичні функції для роботи з масивами даних і статистичні функці

Украинкский

2013-06-29

82 KB

150 чел.

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

Тема: Ms Excel. Задачі апроксимації і прогнозування даних. Метод найменших квадратів. Елементи регресійного аналізу. Побудова ліній тренду на діаграмах.

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

Обладнання: ПЕОМ. Табличний процесор MS Excel.

Хід виконання

  1.  Правила ТБ
  2.  Теоретичні відомості

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

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

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

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

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

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

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

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

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

r2 = 1-Е/Т, де   

а суми обчислюють за індексом і від 1 до n.

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

  1.  середнє значення фактора;
  2.   середнє значення експериментальних даних;
  3.    
  4.  

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

2. Реалізація регресійного аналізу в ЕТ. Розглянемо найпростіший спосіб прогнозування даних. Достатньо виокремити діапазон з експериментальними даними У і перетягнути маркер копіювання на k клітинок (вниз) отримаємо лінійний прогноз для точок хп+1, хп+2, ..., xn+k . Якщо перетягування виконати правою клавішею миші, то з контекстного меню можна вибрати тип апроксимації: лінійною тх+b чи експоненціальною bmх функцією (залежно від тенденцій у даних). Недоліками такого способу є припущення, що фактором X є масив чисел 1, 2, 3, ..., а також нереагування прогнозованих значень на зміни в експериментальних даних.

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

Головним способом розв'язування задачі лінійної апроксимації і прогнозування даних є використання статистичної функції TREND(діапазон 1; діапазон 2; діапазон 3). Діапазоні має містити експериментальні дані у. Діапазон2 — значення хi. Діапазон 3 має містити точки для прогнозу хп+1, хп+2, ..., хп+k.  Функцію можна використовувати з двома аргументами або трьома. У першому випадку розв'язують задачу апроксимації експериментальних даних прямою лінією, а в іншому випадку задачу прогнозування даних.

3. Використання функції TREND. Розглянемо алгоритм розв'язування задачі апроксимації даних прямою лінією, який реалізується статистичною функцією TREND.

1. Утворити вертикальний діапазон з xi, який назвати X.

  1.  Утворити вертикальний діапазон з yt, який назвати Y.
  2.  Поряд вибрати порожній вертикальний діапазон з п клітинок.
  3.  У першу клітинку ввести формулу =TREND(Y;X).
  4.  Натиснути на клавішу F2.

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

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

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

  1.  Поряд вибрати порожній вертикальний діапазон з k клітинок.
  2.  У першу клітинку ввести формулу =TREND(Y;X;Z).
  3.  Натиснути на клавішу F2.
  4.  Натиснути на комбінацію клавіш Shift+Ctrl+Enter — результат-прогноз f(xi), і=п+1,..., k, заповнить порожній діапазон.

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

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

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

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

Над матрицями-масивами визначені операції додавання віднімання, множення на число, а також такі функції: для множення матриці на матрицю — MMU(матриця1; матриця2), транспонування — TRANSPOSE(масив чи діапазон), обчислення оберненої матриці — MINVERSE( масив) та детермінанта матриці — MDETERM(матриця).

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

Формулу для дій з масивами чи діапазонами називають формулою масиву. Перед виконанням дій з масивами потрібно вибрати порожній діапазон клітинок, де міститиметься результат обчислення формули — масиву. Особливість дій користувача така: після набору формули у рядку формул її вводять у ЕТ не простим натисканням клавіші Enter, а комбінацією клавіш Shift+Ctrl+ +Enter. Формула масиву буде записана у фігурних дужках автоматично (їх не набирають). Формули масивів слід застосувати в цій роботі для обчислення значень т, b і r2.

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

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

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

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

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

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

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

  1.  Експоненційну апроксимацію використовують у тому випадку, якщо швидкість зміни даних безперервно зростає, наприклад, у задачах розмноження вірусів. Неможливо застосувати степеневу й експоненційну апроксимацію, якщо дані містять нульові або від'ємні значення.
  2.  Є ще метод апроксимації даних, який називають змінним середнім (лінійною фільтрацією, рос. скользящее среднее). Лінія тренду будується за певною кількістю точок (ця кількість визначається параметром ТОЧКИ). Метод застосовують, коли точок є багато і вони дуже розкидані. Дані усереднюють попарно чи інакше й одержані середні значення використовують для апроксимацій Якщо значення параметра Точки є 2, першу точку кривої визначають як середнє значення перших двох даних, другу точку — як середнє значення другого і третього даного і т. д.

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

  1.  Побудувати діаграму для ряду експериментальних даних.
  2.  Клацнути правою клавішею на маркері ряду даних, для яких треба побудувати лінію тренду — отримаємо контекстне меню ряду даних.
  3.  Виконати команду Додати лінію тренду...
  4.  На закладці Тип вибрати тип лінії тренду.
  5.  На закладці Параметри задати назву кривої (можна не задавати), довжину відрізка (в одиницях зміни аргументу) прогнозу, координату точки перетину з віссю Y (можна не задавати), зобразити рівняння регресії на діаграмі, розташувати на діаграмі значення r2 (R^2).

ОК.

  1.  Індивідуальне завдання

Задача  «Прогноз доходу фірми»

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

Хід роботи

1.    Запустіть ЕТ і відкрийте нову книжку.

2.    Уведіть у три діапазони масиви чисел у вигляді матриць: масив А і В розмірності три на три (по 9 елементів) і масив — стовпець D з трьох елементів.

Надайте діапазонам імена А, В і D.

3. Обчисліть 2*А.

4. Обчисліть добуток масивів — матриць А*В.

5. Обчисліть суму масивів — матриць А+В.

6. Обчисліть добуток діапазонів D*D.

Результат діапазон з квадратами даних з D.

7. Обчисліть детермінант матриці А.

Якщо детермінант дорівнює нулю, змініть числа в матриці А.

8. Розв'яжіть систему лінійних алгебраїчних рівнянь AX=D. 

Метод розв'язування Х= A_1*D, де А-1 обернена матриця.

9. Перейдіть на новий аркуш для розв'язування задачі № 6. 

У першому рядку введіть заголовки стовпців: Місяці, X, Y,Апроксимація.

10. Уведіть у другий рядок список назв місяців шляхом автозаповнення рядка і транспонуйте рядок з елементами списку в стовпець.

Виберіть стовпець і застосуйте формулу {=TRANSPOSE(A2:K2)}.

11. Виконайте алгоритм використання функції TREND().

12. Побудуйте плоску стовпцеву діаграму для експериментальних даних з графіком лінії тренду.

Виберіть спочатку діапазон, що містить експериментальні (Y), апроксимовані та прогнозовані дані (Апроксимація). Запустіть Майстра побудови Діаграм. На першому кроці виберіть на закладці нестандартних діаграм тип Графік | Гістограма, що дасть змогу накласти графік лінії тренду на діаграму.

13. Обчисліть значення m і Ь.

Для обчислення сум застосуйте формули масивів на зразок {=SUM(діапазон1* діапазон2)} і Ctrl+Shift+Enter.

  1.  Обчисліть значення г2.
  2.  Застосуйте функцію LINEST().

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

  1.  Скопіюйте вхідні дані задачі № 6 на третій аркуш.
  2.  Дослідіть графічні способи прогнозування за допомогою ліній тренду різних типів. Нанесіть на аркуш шість діаграм з лініями тренду.

Виберіть діапазон чисел Y і побудуйте просту стовпчикову діаграму (або графік з маркерами тощо). Застосуйте описаний в теоретичних відомостях алгоритм нанесення ліній тренду з відповідними параметрами для прогнозу на дві одиниці вперед і виведення рівнянь регресії та значення r2.

  1.  Зробіть висновок про те, який тип апроксимації є найліпшим.
  2.  Який прогноз обсягів доходу фірми у листопаді та грудні?
  3.  Продемонструйте три аркуші та закінчіть роботу.

  1.  Контрольні запитання:

  1.  Що таке МНК і яке його призначення?
  2.  У чому полягає суть задачі апроксимації даних?
  3.  У чому полягає суть задачі прогнозування даних?
  4.  Що таке регресійний аналіз?
  5.  Що таке однофакторний аналіз? Наведіть приклад задачі.
  6.  Що таке багатофакторний аналіз? Наведіть приклад задачі.
  7.  Які види ліній використовують для апроксимації даних?
  8.  Як оцінюють якість апроксимації?
  9.  Як найпростіше отримати лінійний прогноз даних?
  10.  Яке призначення функції TREND()?
  11.  Який алгоритм використання функції TREND()?
  12.  Яке призначення функції GROWTH()?
  13.  Яке призначення функції LINEST()?
  14.  Яке призначення функції LOGEST()?
  15.  Що таке масив чисел в ЕТ?
  16.  Які дії можна виконувати над масивами?
  17.  Які є функції для дій з масивами?
  18.  Що таке формула — масив?
  19.  Яке призначення акорду Shift+Ctrl+Enter?
  20.  Яке призначення ліній тренду?
  21.  Які лінії тренду можна нанести на діаграму?
  22.  На які діаграми можна нанести лінії тренду?
  23.  Як нанести лінію тренду на діаграму?
  24.  Що таке метод «змінного середнього»?
  25.  Що означає оцінка r2?

  1.  Оформлення звіту.
  2.  Захист роботи.


 

А также другие работы, которые могут Вас заинтересовать

5542. Генетика микроорганизмов. Генетический материал бактерий 48 KB
  Генетика микроорганизмов Генетика - наука об изменчивости и наследственности организмов. Основателем учения об изменчивости и наследственности является Ч. Дарвин, доказавший в 1859 году, что все существующие виды растений и животных произошли из...
5543. Окончание холодной войны. Распад советского блока 33 KB
  Окончание холодной войны. Распад советского блока Советский Союз проиграл холодную войну, так как его экономика не выдержала колоссальных нагрузок. Поражение это поставило крест на идее мировой революции, которой жило советское руководство с 1917г...
5544. Рынок, рыночный механизм, субъекты рыночной экономики 76 KB
  Рынок, рыночный механизм, субъекты рыночной экономики Цель изучения темы: получить представление о сущности рынка и условиях его возникновения, уяснить основные элементы рыночного механизма и принципы их взаимодействия , изучить предпринимател...
5545. Журналістика Великобританії 121 KB
  Журналістика Великобританії Журналістика Великобританії XVІІІ століття Британська преса ХІХ століття Англійська журналістика ХХ століття Радіо- і телевізійне мовлення в Англії Розвиток цифрового телебачення Великобританії...
5546. Семантическая структура слова 42 KB
  Семантическая структура слова Словесный знак может иметь одно значение, он также может обладать рядом значений. Однозначность многозначность является одним из важнейших противопоставлений, лежащих в основе лексико-семантической системы. Однозначные ...
5547. Социальное партнерство в сфере труда. Коллективные договоры и соглашения 41.85 KB
  Социальное партнерство в сфере труда. Коллективные договоры и соглашения. План занятия: 1. Социальное партнерство: понятие, стороны, система и формы. 2. Принципы социального партнерства, их характеристика. 3. Представители работников и работодателя ...
5548. Экономический контроль и аудит в системе управления компанией 41.81 KB
  Контроль и аудит 1. Экономический контроль и его место в системе управления компанией 2. Особенности системы внутреннего контроля 3. Цели контроля и их взаимосвязь с общими целями аудиторской проверки. Оценка системы внутреннего контроля 1. Экономич...
5549. Туберкулёз кожи. Вирусные и инфекционные поражения кожи слизистых оболочек 116.5 KB
  Туберкулёз кожи. Вирусные и инфекционные поражения кожи слизистых оболочек Определение Этиология Классификация по клиническим формам и клиническим разновидностям Тактика среднего медицинского работника при данных заболеваниях...
5550. Вирусные гепатиты. Холера. Полиомиелит 118.5 KB
  Вирусные гепатиты. Холера. Полиомиелит Раздел Вирусные гепатиты Вирусные гепатиты — группа острых инфекционных заболеваний человека, которые имеют клинически сходные проявления, полиэтиологичны, но различны по эпидемиологическим характеристи...