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

 

Клацніть на елементі Кут діаграми і поверніть площину, щоб поліпшити перегляд діаграми.

29. Клацніть на елементі Стіни і змініть формат стін, зама­лювавши їх жовтим кольором, або помістіть на них зображення з файлу.

Сформатуйте область побудови діаграми.

Повільно ведіть вказівником у рамці, доки не з'явиться такий напис: Область побудови діаграми. Клацніть лівою клавішею і розтягніть область. Клацніть правою клавішею і замалюйте її градієнтним кольором.

31. Для задачі про діяльність фірми побудуйте пелюсткову
діаграму і розташуйте її на окремому аркуші Пелюсткова.

У таблиці має бути п'ять-шість рядів даних і три стовпці.

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

Назвіть аркуш Біржова.

33. Побудуйте графік функції від двох змінних z = х² - у².
На наступному аркуші з назвою Поверхня побудуйте таблицю
значень цієї функції для значень х та у на проміжках [-2; 2] з
кроком h = 0,2. Для цього перший рядок, починаючи з
клітинки В1, заповніть значеннями х: -2; —1,8;...; 1,8; 2 за
правилами створення арифметичної прогресії: введіть у В1

число -2 ■=> Редагувати ■=> Заповнити ■=> Прогресія ■=> Арифметична ■=> По рядках о Крок 0,2 <=> Граничне значення

2 О ОК. Аналогічно заповніть перший стовпець значеннями у, починаючи з клітинки А2. У клітинку В2 введіть формулу =В$1"2 - $А2~2 і скопіюйте її у прямокутний діапазон B1:V22. Запустіть майстра побудови діаграм і виберіть тип діаграми Поверхня ■=> ГОТОВО. Отриману поверхню називають сідлом або гіперболічним параболоїдом.

35. Модифікуйте таблицю і побудуйте поряд поверхню для функції z = х²* у².

36. Збережіть книжку на диску. Продемонструйте діаграми: кругову, булькову, точкову, графік, гістограму, пелюст­кову, біржову і дві поверхні.

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

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

1. Яке призначення діаграм?

2. Які є типи діаграм?

3. Що таке булькова діаграма?

4. Які є види кругової діаграми?

5. Що таке гістограма?

6. З яких елементів складається діаграма?

7. Як отримати на екрані назву елемента діаграми?

8. Які дії визначені над елементами діаграми?

9. Як розмальовувати елемент діаграми?

 

10. Як можна побудувати діаграму?

11. Як виокремити несуміжні діапазони даних?

Як працює Майстер діаграм?

13. Як вилучити діаграму з аркуша?

14. Що таке пелюсткова діаграма?

15. Що таке біржова діаграма?

16. Як підписати осі діаграми?

17. Для чого використовують точкові діаграми?

 

152

153

18. Як внести зміни в діаграму?

19. Як увімкнути панель інструментів Діаграма?

20. Для чого використовують стовпцеві діаграми?

21. Яка відмінність між графіком і точковою діаграмою?

22. Які є різновиди стовпцевих діаграм?

23. Як перемістити діаграму в потрібне місце?

24. Як скопіювати діаграму на іншу сторінку?

25. Як розтягнути діаграму?

26. Як сформатувати заголовок діаграми?

27. Як повернути об'ємну діаграму?

28. Як змінити тип діаграми?

29. Які є типи нестандартних діаграм?

30. Як сформатувати область побудови діаграми?

31. Які осі мають діаграми?

32. Як помістити зображення на елемент діаграми?

33. Як виокремити елемент діаграми?

34. Як викликати контекстне меню області побудови діаграми?

35. Як побудувати поверхню?

Практична робота № 21 Тема

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

Мета

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

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

Протягом перших десяти місяців року доходи фірми DIGI­TAL були такими: 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.

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

 

154

155

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

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

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

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

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

 

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

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

4. У першу клітинку ввести формулу =TREND(Y;X).

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

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

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

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

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

9. У першу клітинку ввести формулу =TREND(Y;X;Z).

 

10. Натиснути на клавішу F2.

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

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

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

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

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

 

156

157

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

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

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

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

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

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

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

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

 

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

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

 

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

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

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

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

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

3. Виконати команду Додати ЛІНІЮ тренду...

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

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

ОК.

Хід роботи