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

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

 

Мета

Уміти використовувати математичні функції для роботи з масивами даних: MMULT (МУМНОЖ), MINVERSE (МОБР), MDETERM (МОПРЕД), TRANSPOSE (ТРАНСІ!) і статистиці ні функції для дослідження тенденцій (тренду) в даних: TREND (ТЕНДЕНЦИЯ), LINEST (ЛИНЕЙН), GROWT (РОСТ), LOGEST (ЛГРФПРИБЛ).

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

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

План

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

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

3. Використання функції TREND.

4. Використання функції LINEST.

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

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

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

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

Такий аналіз даних також називають регресійним аналізом.

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

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

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

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

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

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

Нехай у деякій однофакторній задачі кількість експеримен­тальних даних п, значення фактора (незалежної величини, аргументу функції) утворюють масив чисел x_yt x₂,..., х_п, зна­чення експериментальних даних утворюють масив у_г, */₂,..., у_л. Нехай для апроксимації вибрано і визначено функцію f(x).

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

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

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

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

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

Діапазон для задачі 6 має містити числа 1, 2,..., 10. Щоб діа-пазон назвати як X, треба його виокремити і виконати команди Вставити > Ім'я > Присвоїти > Введіть X > Додати > ОК.

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

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

4. У рядок формул ввести формулу =TREND(Y;X).

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

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

6. Утворити вертикальний діапазон із зазначених значень фактора (тут 11, 12) під діапазоном X і назвати його Z.

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

8. У рядок формул ввести формулу =TREND(Y;X;Z).

10. Натиснути на комбінацію клавіш Shift+Ctrl+Enter —

результат-прогноз fх., і=/г+1,..., /k, заповнить порожній діапазон.

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

Якщо виявиться, що апроксимацію виконано невдало (тен-

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Виконати команду Додати лінію тренду. 4. На закладці Тип вибрати тип лінії тренду.

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

зобразити рівняння регресії на діаграмі, розташувати на діаграмі значення г2 (R^2).

6. ОК.

ХІД РОБОТИ

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

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

Обчисліть значення r2.

Мета

Уміти використовувати логічну функцію ЯКЩО (ЕСЛИ, IF) та абсолютні адреси клітинок для розв'язування типових економічних і математичних задач.

План

1. Абсолютна і змішана адреси.

2. Логічні функції.

3. Дати.

4. Метод добирання параметра.

5. Метод простих ітерацій.

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

1. Абсолютна і змішана адреси. Розглянемо поняття абсолют ної і змішаної адрес клітинки у формулі. Абсолютною називають адресу, в якій є два символи $: один перед назвою стовпця, другий — перед номером рядка, наприклад $Е$3. Змішана адреса місти ть, лише один символ $. Правило: частина адреси після символу $ не модифікується під час копіювання формули.

Абсолютні адреси слугують, зокрема, для посилання на клітинки, які містять константи, що є у формулах. Такою ют стантою є, наприклад, відсотки (12% =0,12) річних у задачі2. Якщо для задачі 2 число 0,12 занести в клітинку ЕЗ, тов клітинку СЗ можна ввести формулу = ВЗ*$Е$3.

2. Логічні функції. Розгалуження в ЕТ реалізовують допомогою функції ЯКЩО (ЕСЛИ, IF), яка використовується у формулах і має таку структуру:

ЯКЩО(<логічний вираз>; <вираз 1>; <вираз 2>).

Логічний вираз — це форма запису умови: простої або складеної.

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

Вираз 1 чи вираз 2 також може бути функцією ЯКЩО —так утворюють вкладені розгалуження. Часто виразом 1 чи виразом 2 є лише адреса клітинки, яка містить деяке значення або конкретне число.

Прості умови записують так, як в алгоритмічних мовах —за допомогою операцій порівняння =, >, <, <=, > =, <>, визначених над виразами, наприклад, 7>5, А5<=20 тощо.

Складені умови записують за допомогою логічних функцій||(умова1>;<умова2>;...) та АБО(<умова1>;<умова2>;...). Функція І (И, AND) істинна, якщо всі умови в списку істинні. Функція АБО (ИЛИ, OR) істинна, якщо хоч би одна умова в вписку її аргументів істинна.

Наприклад, функція ЯКЩО(АБО(5>7; 5<7); 5; 7) набуває значення 5, а функція ЯКЩО(І (5>7; 5<7); 5; 7) — значення 7. Якщо користувач не пам'ятає вигляду функції, він може вста вити її у вираз за допомогою майстра функцій, який викли-кають командою Вставити => Функція. У цьому разі потрібно ви-брати назву функції із запропонованого списку (крок 1) і запов-нити поля значеннями параметрів (крок 2).

Працюючи в Excel, потрібно користуватися російськими (ЕС-ЛИ, И, ИЛИ) або англійськими (IF, AND, OR) назвами функцій. 3. Дати. В економічних чи бухгалтерських задачах дати тра-I и миються доволі часто. Дати в ЕТ чисто умовно зачисляють до даних типу дата. Наспраді дати зберігаються в ЕТ як цілі числа і лише відображаються на екрані в тому чи іншому форматі, наприклад: 12 січня 2006 р. чи 12.01.2006, чи 2006-01-12, чи 88729. За точку відліку дат взято 1 січня 1900 року. Будь-яка дата еквівалентна цілому числу, що дорівнює кількості днів, які минули від точки відліку (38729 для 12 січня 2006 p.). Це дає змогу виконувати над датами такі операції: віднімати дати для визначення проміжку днів між двома датами, додавати чи від-німати від дат ціле число.

Можна застосовувати функції опрацювання дат з метою визначення окремих компонент дати: поточного номера дня в тижні (WEEKDAY), в місяці (DAY), номера місяця в році (MONTH), року (YEAR), поточної дати (TODAY) тощо. Ці функції потрібно вставляти у вирази командами Вставити => Функція => Дати і час. Деякі з цих функцій мають параметри, дія яких описується у відповідному діалоговому вікні.

Час задається годиною, хвилиною, секундою. Йому від-даповідає десяткове число від 0 до 1, що відображає частину доби.

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

4. Метод добирання параметра. Метод добирання параметри призначений для розв'язування нелінійного рівняння f(x)=е, Суть методу полягає в тому, щоб автоматично визначити (а деякою точністю) таке значення параметра х, для якого функція f(x) одержує потрібне значення с

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

Розглянемо задачу: встановити тарифну ставку (це пара метр) дванадцятьом працівникам, щоб вкластися в запланований обсяг зарплатні (це функція) 1000 грн.

Модель задачі. Нехай А1 — адреса клітинки, що міститиме відповідь — значення параметра-ставки, a f(Al) — задана функ ція (мета дослідження), наприклад, зарплатня(А1) = 12*А1 Складаємо рівняння, яке є математичною моделлю задачі 12*А1 - 1000.

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

має підібрати значення А1, щоб задовольнити будь-яке рівняння

Алгоритм дій користувача такий.

1. У будь-яку клітинку (але не в А1) треба занести формулу = f(Al). У нашому випадку формула така: = 12*А1.

2. Вибрати цю клітинку і виконати команду Сервіс => Підбір параметра. Отримаємо діалогове вікно Підбір параметра.

3. Заповнити три поля: а) зазначити адресу формули (вони буде вказана автоматично, якщо клітинка з формулою була вибрана перед цим); б) бажане значення формули, тобто с (у нашому випадку 1000); в) адресу параметра — А1.

4. Натиснути на ОК і у клітинці А1 отримати результат. Другий спосіб полягає у використанні можливостей програми Solver («Пошук розв'язку»), що додається до Excel. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмежсн нями. Наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не пере вищували деяких величин (це обмеження у вигляді нерівностей, див. роботу № 24).

5. Метод простих ітерацій. Продовжимо вивчати застосу вання електронних таблиць для розв'язування типових матема тичних задач. Розглянемо ще два способи розв'язування нелінійІ ного рівняння: 1) метод простих ітерацій з побудовою таблиці; 2) метод простих ітерацій з використанням двох клітинок.

Розглянемо метод простих ітерацій. Щоб нелінійне рівняння f(х) = с можна було розв'язати методом простих ітерацій, його зводятьдо вигляду х = z(x) так, щоб виконувалась нерівність: z(х)| < 1. За цієї умови метод простих ітерацій збігається, тобто

Ідає правильний розв'язок. Наприклад, рівняння 2пх~п = sinnx спочатку треба звести до такого вигляду: х = (sinx + п)/2п.

Метод простої ітерації реалізують за допомогою рекурентної формули так:

x_l+l = (sinx + п)/2п.

дг х — будь-яке початкове наближення, і=0, 1, 2,..., а замість п треба підставити значення свого варіанта. Домовимося, що коли t=8, то значення xі (тобто х₈) вважатимемо розв'язком рівняння. Розглянемо реалізацію рекурентної формули в ЕТ. Нехай n= 1, а в клітинку А6 введено будь-яке початкове наближення, наприклад 2. Тоді наступне наближення отримаємо в клітинці B6, ввівши туди формулу =(Sin(А6)+1)/2. Це значення приймаємо за початкове для наступної ітерації: в А7 заносимо значення В6. У клітинці В7 отримуємо наступне наближення і т.д. У клітинці В1З буде останнє (восьме) наближення, яке і прий-маємо за розв'язок.

Другий спосіб полягає у використанні властивості ЕТ автоматичного багаторазового переобчислення, якщо ввімкнений режим ітерацій у діалоговому вікні Параметри. Тут для розв'я­зування задачі достатньо двох клітинок (див. рис. 45, рядок 17). Цей спосіб розглянемо під час виконання роботи.

Словник

Підбір параметра Подбор параметра Goal Seak

Пошук розв'язку Поиск решения Solver

Дата Дата Date

Якщо/і/або Если/и/или If/ And/Or

Захист Защита Protection

Функція Функция Function

Ітерації Итерации Iterations

Клітинка-ціль Целевая ячейка Targer Cell

Обмеження Ограничения Constraints

Сторінка/Книжка Лист/Книга Sheet/Book

СЬОГОДНІ СЕГОДНЯ TODAY

Хід роботи

Роках).

Від дати в $Е$2 потрібно відняти дати зі стовпця Початок і результат поділити на 365. Формат клітинки задати як число­вий загальний.

13. Нарахуйте премію працівникам від нарахованої суми 10%, якщо стаж до 5 років, і 25%, якщо стаж більший.

Збережіть книжку на диску.

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

 

Мета

Уміти використовувати математичні функції для роботи з масивами даних: MMULT (МУМНОЖ), MINVERSE (МОБР), MDETERM (МОПРЕД), TRANSPOSE (ТРАНСІ!) і статистиці ні функції для дослідження тенденцій (тренду) в даних: TREND (ТЕНДЕНЦИЯ), LINEST (ЛИНЕЙН), GROWT (РОСТ), LOGEST (ЛГРФПРИБЛ).