Прогнозування в Excel методом ковзного середнього. Прогнозування ціни акцій на ринку цінних паперів в Excel
Ковзне середнє або просто МА (Moving Average), є середньоарифметичним ціновим рядом. Загальна формула ковзного середнього:
Де:
МА - ковзне середнє;
n- період усереднення;
Х – значення ціни акції.
Для прогнозування ціни акціїна кілька періодів наперед скористаємося формулою. Прогноз ціни в наступному періоді дорівнюватиме значення ковзаючого середнього в попередньому періоді.
Спрогнозуємоза допомогою моделі ковзного середнього вартість акційкомпанії Аерофлот (AFLT). Для цього експортуємо котирування акції із сайту finam.ru за половину 2009 року. Усього буде 20 значень.
Графік вартості акцій Аерофлотуза вибраний проміжок часу наведено нижче.
Вибір періоду усередненняnу моделі ковзного середнього
Використання більшого в моделі МА(n) призводить до сильного спотворення даних, у результаті істотні значення цінового ряду усереднюються, і в результаті втрачається чіткість прогнозу, можна сказати що він стає "розмитим". Використання надто дрібного періоду усереднення додає у прогноз більше шумової компоненти. Як правило, період усереднення підбирається емпіричним шляхом на історичних даних.
Побудуємо ковзне середнєз періодом усереднення три місяці MA(3). Для розрахунку значення ковзного середнього для акції скористаємося формулою Excel.
СРЗНАЧ(C2:C4)
У колонці “D” розраховані значення ковзного середнього з періодом усереднення 3.
Після розрахунку ковзного середнього побудуємо прогноз на 3 періодивперед (три місяці вперед). Скористайтеся формулою для визначення значення ціни акції, перше прогнозне значення буде рівним останнього значенняковзного середнього. Помаранчева область - це область прогнозів. С22 дорівнюватиме значенню ковзного середнього, тобто:
С22 = D21 С23 = D22 і т.д.
Від нових прогнозних даних вартості акції розраховується наступне середнє, що ковзає.
Побудуємо прогнозні значенняпо ковзному середньому для акцій Аерофлоту на три місяці наперед. Нижче наведено графік та прогнозні значення акції.
Транскрипт
1 Прогнозування в Excel шляхом ковзного середнього доктор фіз. мат. наук, професор Гавриленко В.В. помічник Парохненко Л.М. (Національний транспортний університет) Теоретична довідка. При моделюванні різних економічних процесів практично широко використовуються зростаючі можливості сучасних комп'ютерних технологій, і навіть ефективні способипрогнозування. Так, розробки прогнозів у пакеті Exсel можна скористатися такими інструментами , як: побудова регресій; експоненційне згладжування; ковзне середнє. У цій роботі процес розробки прогнозу засобами Excelздійснюється за допомогою методу ковзного середнього. Зауважимо, що методика прогнозування за допомогою регресій досить докладно описана авторами у . Метод ковзного середнього використовуються для згладжування та прогнозування часових рядів. Нагадаємо, що тимчасовий ряд це безліч пар даних (X,Y), в яких X це моменти або періоди часу (незалежна змінна), а параметр Y, що характеризує величину досліджуваного процесу (залежна змінна). Метод ковзного середнього дозволяє виявити тенденції зміни фактичних значень параметра Y у часі та спрогнозувати майбутні значення Y. Отриману модель можна ефективно використовувати у випадках, якщо для значень прогнозованого параметра спостерігається усталена тенденція динаміці. Цей метод менш ефективний у випадках, коли така тенденція порушується, наприклад, при стихійних лихах, військових діях, громадських заворушеннях, при різкій зміні параметрів внутрішньої чи зовнішньої ситуації (рівня інфляції, цін на сировину); при докорінному зміні плану діяльності фірми, що зазнає збитків. Основна ідея методу ковзного середнього полягає у заміні фактичних рівнів досліджуваного часового ряду їх середніми значеннями, що погашають випадкові коливання. Таким чином, в результаті виходить згладжений ряд значень досліджуваного параметра, що дозволяє чіткіше виділити основну тенденцію його зміни. Метод ковзного середнього відносно простий метод згладжування і прогнозування часових рядів, заснований на поданні прогнозу y t у вигляді середнього значення m попередніх значень y (i= 1, m), то m * 1 є: y t = yt i. Якщо, наприклад, при дослідженні часового ряду даних m i = 1 про прибуток підприємства за місяцями як прогноз вибрати ковзне середнє за три місяці (m = 3), то прогнозом на червень буде середнє значення по- t i
2 козачі за три попередні місяці (березень, квітень, травень). Якщо ж вибрати 4-місячне ковзне середнє (m = 4), то прогнозом на червень буде середнє значення показників за чотири попередні місяці (лютий, березень, квітень, травень). Часто, наприклад, при розробці прогнозу обсягу продажів підприємства метод ковзного середнього, заснований на спостереженнях за 3 (або 4) попередні місяці, буває ефективнішим (дозволяє відстежувати фактичний обсяг продажів з більшою точністю), ніж методи, що базуються на довгострокових спостереженнях (за 12 місяців і більше). Це пояснюється тим, що в результаті застосування 3-місячного ковзного середнього кожне з 3 значень показника (за ці три місяці) відповідає за одну третину значення прогнозу. При 12-місячному ковзному середньому значення кожного з показників цих останніх трьох місяців відповідають лише за одну дванадцяту прогнозу. На жаль, немає правила, що дозволяє підбирати оптимальне число m членів ковзного середнього. Проте можна зазначити, що менше m, тим більше прогноз реагує на коливання часового ряду, і навпаки, що більше m, тим процес прогнозування стає більш інерційним. На практиці величина m зазвичай приймається в межах від 2 до 10. За наявності достатньої кількості елементів часового ряду прийнятне для прогнозу значення m можна визначити, наприклад, так: задати кілька попередніх значень m; згладити тимчасовий ряд, використовуючи кожне задане значення m; обчислити середню помилкупрогнозування за однією із формул: 1 * o ε = y t y t (середнє абсолютне відхилення); n 1 yt o ε = y n y t t t * t (середнє відносне відхилення); 1 * 2 o ε = (yt yt) (середнє квадратичне відхилення), n t де n кількість використовуваних при розрахунку моментів часу t; вибрати значення m, яке відповідає меншій помилці. Реалізацію процесу згладжування та прогнозування методом ковзного середнього в середовищі Excelможна здійснити: введенням у комірки відповідної формули, наприклад, використовуючи вбудовану функцію СРЗНАЧ(); за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу"; додаванням у діаграму, побудовану за вихідним часовим рядом, лінії тренду на основі методу лінійної фільтрації.
3 Завдання. Враховуючи представлені у таблиці дані щомісячного прибутку фірми за 11 місяців поточного року, скласти прогноз про прибуток фірми на 12-й місяць. Рис.1. Таблиця значень прибутку фірми по місяцях Розв'язання задачі Надалі при вирішенні сформульованої задачі для зручності представлення отриманих результатів розрахунків будуть використовуватися робочі листи Z1, Z2, Z3, Z4: лист Z1 для формування згладжених часових рядів на основі методу ковзного середнього за допомогою функції СРЗНАЧ() та обчислення їх середніх відхилень від вихідного часового ряду; лист Z2 для реалізації процесу згладжування вихідного часового ряду за допомогою інструмента Ковзаюча середня надбудова Пакет аналізу; лист Z3 для візуального подання згладженого часового ряду, побудованого за допомогою лінії тренду типу Лінійна фільтрація на основі діаграми для вихідного часового ряду; лист Z4 для порівняльного аналізурезультатів, отриманих за допомогою вибраних вище інструментів: на основі вихідного часового ряду будуються згладжені часові ряди значень 2-х місячного ковзного середнього за допомогою функції СРЗНАЧ(), інструменту Середнє середнє надбудови "Пакет аналізу" і лінії тренду типу Лінійна фільтрація. Застосування вбудованої функції СРЗНАЧ() Процес отримання згладженого часового ряду, а також прогноз про прибуток фірми на 12-й місяць поточного року за даними вихідного часового ряду здійснюватиметься за таким сценарієм: 1. На основі даних, наведених у таблиці рис.1, на робочому листі Excel створюється таблиця, що заповнюється даними вихідного часового ряду. 2. Формуються та заносяться до таблиці дані згладжених тимчасових рядів для 2-х, 3-х та 4-х місячного ковзного середнього.
4 3. Будуються графіки вихідного часового ряду та згладжених часових рядів. 4. За однією з наведених вище формул обчислюються середні відхилення отриманих згладжених часових рядів від вихідного часового ряду. 5. Як модель вибирається згладжений часовий ряд з меншим середнім відхиленням, і на підставі його показників складається прогноз про прибуток фірми на 12-й місяць поточного року. Переходимо до реалізації розв'язання задачі. 1. Заповнюємо діапазон осередків A5:B15 робочого листа Z1 даними часового ряду таблиці рис.1. В результаті одержуємо таблицю, наведену на рис.2. Рис.2. Вихідна таблиця на робочому аркуші Excel 2. За даними часового ряду з діапазону осередків A5:B15 будуємо на основі методу ковзного середнього три моделі досліджуваної залежності за даними за 2, 3 і 4 попередні місяці відповідно. Значення отриманих згладжених часових рядів маємо відповідно в діапазонах осередків C7:С16; D8: D16; E9: E16. Спочатку будуємо ряд значень ковзного середнього за двома місяцями: в комірку C7 заносимо формулу =СРЗНАЧ(B5:B6) і, використовуючи маркер заповнення, копіюємо її на діапазон осередків C8:C16, в результаті чого діапазон осередків C7:C16 заповнюється обчисленими показниками х місячного ковзного середнього. Аналогічно будуються ряди значень 3-х і 4-х місячного ковзного середнього: в комірку D8 вводимо формулу =СРЗНАЧ(B5:B7) і, використовуючи маркер заповнення, копіюємо її на діапазон комірок D9:D16, внаслідок чого діапазон комірок D8:D16 заповнюється показниками 3-х місячного ковзного середнього; вводимо в комірку E9 формулу = СРЗНАЧ(B5:B8) і маркером заповнення копіюємо її на діапазон осередків E10:E16, в результаті чого діапазон осередків E9:E16 заповнюється показниками 4-місячного ковзного середнього. На рис.3 4 наведені таблиці з результатами для 2-х, 3-х і 4-х місячного ковзного середнього, а також формули, що при цьому застосовуються.
5 Мал.3. Таблиця значень для 2-х, 3-х, 4-х місячного ковзного середнього Рис.4. Вміст осередків таблиці рис.3 На рис.5 наведено графік вихідного часового ряду і побудовані щодо нього прогнозні лінії тренду ковзного середнього. Зазначимо, що ці графіки будувалися за стандартною методикою побудови діаграм Excel. Оскільки отримані значення згладжених часових рядів на основі ковзного середнього базуються на даних попередніх спостережень, то вони запізнюються у порівнянні з відповідними значеннями вихідного часового ряду: лінії тренду ковзного середнього зсунуті щодо графіка вихідного часового ряду (рис.5). У таблицях на рис.6 10 наведені абсолютні, відносні та середні квадратичні відхилення значень 2-х, 3-х та 4-х місячного ковзного середнього
6 від відповідних значень вихідного часового ряду, а також вміст осередків у цих таблицях. Рис.5. Графіки вихідного часового ряду та згладжених часових рядів Рис.6. Таблиця абсолютних відхилень
7 Мал.7. Вміст осередків у таблиці рис.6 Мал. 8. Таблиця відносних відхилень Рис.9. Вміст осередків таблиці рис.8 Рис.10. Таблиця середніх квадратичних відхилень
8 Значення середнього квадратичного відхилення в діапазоні осередків B41:D41 виходять таким чином: в комірку B41 вводиться формула: =КОРІНЬ(СУМКВРАЗН(B9:B15;C9:C15)/РАХУНОК(B9:B15)), в комірку формула C:1 КОРІНЬ(СУМКВРАЗН(B9:B15;D9:D15)/РАХУНОК(B9:B15)), в комірку D41 вводиться формула: =КОРІНЬ(СУМКВРАЗН(B9:B15;E9:E15)/РАХУНОК(B9:B15). Слід звернути увагу, що для проведення порівняльного аналізу похибок для 2-х, 3-х та 4-х місячного ковзного середнього було взято однакову кількість спостережень. Висновок. З наведених таблиць випливає, що для згладжування вихідного часового ряду та складання прогнозу про тенденцію зміни прибутку фірми краще модель 2-х місячного ковзного середнього, оскільки вона більш точно реагує на коливання вихідного часового ряду і має менші помилки прогнозування (абсолютні, відносні, середнє квадратичні). ). Прогнозне значення прибутку фірми на 12 місяців 8325 тис. грн. Інструмент Ковзне середнє надбудови "Пакет аналізу" Реалізацію процесу згладжування та прогнозування методом ковзного середнього в середовищі Excel можна здійснити за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу" за наступною методикою: 1. На робочому аркуші Z2 створюємо таблицю, в якій діапазон комірок A5: B15 заповнюємо даними часового ряду вихідної таблиці (рис.1). 2. Діапазон осередків C5:С15 заповнюємо значеннями згладженого ряду, отриманого за даними за 2 попередні місяці за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу", а діапазон осередків D5:D15 значеннями його стандартних похибок. 3. Аналогічно заповнюються діапазони осередків E5:E15 та F5:F15 значеннями згладженого ряду, отриманого за даними за 3 попередні місяці, та значеннями його стандартних похибок відповідно. Технологія побудови ряду значень, наприклад, для 2-х місячного ковзного середнього за допомогою інструмента "Ковзне середнє надбудови "Пакет аналізу" полягає в наступному: Вибираємо в меню Сервіс команду Аналіз даних. З'явиться діалогове вікно Аналіз даних (рис.11), де містяться всі доступні інструменти аналізу даних. Зі списку вибираємо інструмент Ковзне середнє і клацаємо по кнопці ОК. З'явиться діалогове вікно Ковзне середнє (рис.12). У полі Вхідний інтервал вказуємо діапазон вихідних даних на робочому аркуші Excel, тобто діапазон осередків B5: B15.
9 Рис.11. Діалогове вікно Аналіз даних Рис.12. Діалогове вікно Ковзне середнє У полі Інтервал вводимо кількість місяців, які включаються в підрахунок ковзного середнього, тобто число 2 (оскільки в даному випадку ковзне середнє будується за даними 2-х попередніх місяців). У полі введення Вихідний інтервал вводимо діапазон осередків, в якому будуть виведені отримані результати, тобто діапазон осередків C5: C15. При установці прапорців у полях Виведення графіка та Стандартні похибки автоматично буде створено діаграму за результатами аналізу і в результат додасться стовпець, що містить статистичну оцінкупохибки. У полі Мітки слід встановити прапорець, якщо перший рядок (стовпець) у вхідному діапазоні містить заголовки. Якщо вхідний діапазон не містить заголовків, необхідно зняти прапорець. Клацаємо по кнопці ОК. Аналогічно будується ряд значень 3-х місячного ковзного середнього та його стандартні похибки. На рис.13 наведена таблиця значень 2-х і 3-х місячних ковзних середніх та їх стандартних похибок, отриманих за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу", а на рис.14а, 14б вміст осередків даної таблиці, тобто використовуваних в процесі розв'язування формул.
10 Рис.13. Згладжені ряди та їх стандартні похибки, отримані за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу" Рис.14а. Вміст клітинок таблиці рис.13 (початок)
11 Рис.14б. Вміст клітинок таблиці рис.13 (продовження) Рис.15. Графіки вихідного часового ряду та згладжених часових рядів, побудованих за допомогою інструменту Ковзне середнє надбудови "Пакет аналізу" Висновок: порівняння стандартних похибок з діапазону осередків D9:D15 з відповідними стандартними похибками з діапазону осередків F9:F15 (рис.13) дозволяють -х місячного ковзного середнього краще для згладжування та прогнозування, так як вона у всіх точках розглянь-
12 тимчасового діапазону має менші стандартні похибки. Прогнозним значенням прибутку фірми на 12 місяць буде значення, що міститься в осередку C15, тобто 8325 тис. грн. Побудова ліній тренду за методом лінійної фільтрації Для графічного аналізу даних на діаграмі можна скористатися побудовою лінії тренду за точками ковзного середнього. Така лінія тренду дозволяє побудувати згладжену криву, графічне уявлення якої чіткіше показує існуючу закономірність у розвитку даних. Для вихідної таблиці значень (рис.2) застосуємо метод лінійної фільтрації (або метод ковзного середнього) та побудуємо лінії тренду. Технологія побудови лінії тренда полягає в наступному: За даними вихідної таблиці (рис.2) побудуємо графік, вибираючи тип Точковий у діалоговому вікні Тип діаграми. За бажанням можна змінити вигляд побудованого графіка та його маркера, тип лінії, колір та товщину. Для цього слід перейти в режим редагування отриманого графіка, клацнувши подвійним натисканням лівою кнопкою миші на побудованому графіку. У діалоговому вікні Формат ряду даних, що з'явилося, задаємо необхідні параметри зміни графіка і натискаємо клавішу ОК. Далі виділяємо цей ряд даних, клацнувши по лінії графіка правою кнопкою миші (виділення ряду буде зроблено чорними квадратиками). У контекстному меню, вибираємо пункт меню Додати лінію тренда. Або після виділення ряду натисканням будь-якої кнопки миші виберіть команду Додати лінію тренда в меню Діаграма. На екрані з'явиться діалогове вікно Лінія тренду (рис.16). На вкладці Тип вибираємо тип лінії тренду Лінійна фільтрація (ковзна середня). При виборі типу Лінійна фільтрація необхідно ввести в поле Період число періодів (точок), які використовуються для розрахунку середнього ковзного. Введемо у полі число 2, т.к. проводимо побудову лінії тренду по 2 місяці. Натискаємо ОК. За аналогією надаємо при побудові лінії тренду по 3 місяцях, ввівши в поле Період число 3. На рис18. представлені побудовані графіки вихідного часового ряду та лінії тренду 2-х та 3-х місячного ковзного середнього.
13 Рис.16. Діалогове вікно Лінія тренду Побудовані лінії тренда можна форматувати. Для цього: виділяємо лінію тренда, клацнувши але нею мишею, потім клацніть правою кнопкою миші і з контекстного меню, що з'явилося, вибираємо пункт Форматування лінії тренда. з'являється діалогове вікно Формат лінії тренду (рис. 17), в якому можна встановити бажаний вид тренду: тип лінії, колір, товщину; можна змінити назву згладженої кривої, відкривши в цьому діалоговому вікні вкладку Параметри. Встановивши потрібні параметри, натискаємо ОК.
14 Мал. 17. Діалогове вікно Формат лінії тренда Зазначимо наступне: Оскільки метод лінійної фільтрації реалізується шляхом нанесення на діаграму лінії тренда, його дію можна спостерігати візуально, але при цьому немає можливості отримати у своє розпорядження чисельні результати, оскільки вони не заносяться до електронної таблиці.
15 Мал. 18. Графіки вихідного часового ряду і ліній тренду 2-х і 3-х місячного ковзного середнього ковзного середнього за допомогою функції СРЗНАЧ() та 2-х місячного ковзного середнього Пакету аналізу. Побудуємо графік вихідного часового ряду та лінії тренду згладжених часових рядів.
16 Мал. 19. Таблиця значень 2-х місячного ковзного середнього, отриманого за допомогою функції СРЗНАЧ() та Пакету аналізу Рис.20. Графіки вихідного часового ряду, 2-го місячного ковзного середнього, отриманого за допомогою функції СРЗНАЧ, інструмента Ковзне середнє надбудови "Пакет аналізу" з додаванням лінії тренду типу Лінійна фільтрація
17 Порівнюючи значення ковзного середнього в стовпці С, отримані шляхом безпосереднього введення формул в комірки робочого листа, зі значеннями ковзного середнього в стовпці D, обчисленими за допомогою інструмента Ковзне середнє надбудови "Пакет аналізу" (рис.20), можна помітити, що показники ковзного середнього в стовпці С зсунуті на одну позицію вниз порівняно зі стовпцем D. Цю проблему можна вирішити, наприклад, так: після того, як буде обчислено значення ковзного середнього, слід виділити всі ці значення і змістити їх на один рядок робочого листа вниз. Ця дія дозволить пов'язати прогнози саме з тими періодами, до яких вони належать. Однак, якщо буде встановлено прапорець Виведення графіка в діалоговому вікні Ковзне середнє (рис.12), то графік розмістить дані прогнозу відповідно до даних робочого листа. Зсунувши значення робочої таблиці однією рядок вниз, необхідно також відредагувати і побудований графік за даними прогнозу. Зазначимо переваги та недоліки складання прогнозу із застосуванням методу ковзного середнього: Складання прогнозу за допомогою інструменту ковзного середнього досить прості і досить точно відображають зміни основних показників попереднього періоду. Іноді при складанні прогнозу вони навіть ефективніші, ніж методи, що базуються на довготривалих спостереженнях. Однак просте ковзне середнє є хоч і швидким, але не завжди точним способом виявлення загальних тенденцій часового ряду. При складанні прогнозів ковзного середнього за допомогою надбудови Пакет Аналізу прогноз створюється на один період часу раніше. Можна побудувати графік, у якому дані часового ряду використовуються для побудови лінії тренду ковзного середнього, але на графіці не показані фактичні числові значення ковзного середнього. Також немає можливості змінити розташування лінії тренда на графіку. Складання прогнозів на основі ковзного середнього не дають прогнозу, що виходить за межі відомих даних. Пересунути межу оцінки у майбутнє по часовій осі можна за допомогою однієї зі статистичної функції регресійного аналізупакету Excel. Література 1. Карлберг К. Бізнес аналіз з допомогою Excel. К.: Діалектика, с. 2. Гавриленко В.В., Парохненко Л.М. Вирішення задач апроксимації засобами Excel // Комп'ютери + програми, З Н.В. Макарова, В.Я. Трохимець. Статистика в Excel: Навчальний посібник. М.: Фінанси та статистика, с. 4. Ю.М. Тюрін, А.А. Макарів. Аналіз даних на комп'ютері/За ред. В.Е. Фігурнова. М: ІНФРА-М, с.
Лабораторна робота 2 Тема: Технологія аналітичного моделювання у СППР. Технології аналізу та прогнозування на основі трендів Мета: вивчення можливостей та формування вміння використання універсальної
Практична робота 3.7. Використання майстра функцій MS Excel. Побудова діаграм Ціль роботи. Виконавши цю роботу, Ви навчитеся: вводити формули в комірки таблиці; використовувати Майстер функцій MS Excel
Лабораторна робота 8. ПОБУДУВАННЯ ГРАФІКІВ І ДІАГРАМ У EXCEL Мета роботи: навчитися користуватися засобами графічного відображення інформації в середовищі Ecel, способах її форматування та використання
ПРОГНОЗУВАННЯ ОБСЯГУ ПРОДАЖУ БЕНЗИНУ МЕТОДОМ ЕКСТРАПОЛЯЦІЇ ТРЕНДІВ Пучкова В. С., Растеряєв Н.В. Донський державний технічний університет(ДДТУ) Ростов-на-Дону, Росія FORECASTING OF SALES VOLUMES
РІШЕННЯ ЗАВДАНЬ ОПИСНОЇ СТАТИСТИКИ ЗА ДОПОМОГОЮ ПАКЕТУ АНАЛІЗУ MS EXCEL Найпростіші завдання описової статистики можуть вирішуватися з використанням табличних процесорів. Далі всі приклади наводяться для
Лабораторна робота з Excel (файл.xls на сторінці www.matburo.ru/sub_appear.php?p=l_excel) Створення, заповнення, редагування та форматування таблиць Що освоюється та вивчається? Введення та форматування
3.4. Робота з електронними таблицями 3.4.1. Інтерфейс програми користувача Microsoft Excel. Створення та редагування таблиць Документ у програмі Microsoft Excel (MS Excel) називається робочою книгою,
Назви рядів Графічне подання даних з використанням діаграм 1.1 Основні поняття Будь-яка діаграма будується в системі координат, що задається горизонтальною віссю, званою віссю категорій, та
Розділ 4. Робота з діаграмами та зведені таблиці Подання даних за допомогою діаграм дозволяє забезпечити наочну демонстрацію інформації, розташованої в осередках робочого аркуша. Так, за допомогою діаграм
ПРАКТИКУМ 5.2.4. ДІАГРАМИ. ТЕХНОЛОГІЯ ПОБУДУВАННЯ ТА РЕДАКТУВАННЯ ПРАКТИКУМ 5.2.4. ДІАГРАМИ. ТЕХНОЛОГІЯ ПОБУДУВАННЯ ТА РЕДАКТУВАННЯ... 1 ОБ'ЄКТИ ДІАГРАМИ... 1 ПОБУДУВАННЯ ДІАГРАМИ... 3 1-й крок. Виділення
Діаграми та графіки Попередні відомості про побудову діаграм Побудова та редагування діаграм та графіків Встановлення кольору та стилю ліній. Редагування діаграми Форматування тексту, чисел,
Число газет Лабораторно-практична робота ТЕМА: MS Excel. Побудови, форматування та редагування діаграм, графіків». МЕТА УРОКУ: навчитися будувати, форматувати та редагувати діаграми, графіки.
Побудова графіків функцій та лінії тренду. Волчков В.М., Стяжін В.М. кав. Прикладної математики, ВолгГТУ Заняття 3 Існує безліч спеціалізованих комп'ютерних програм, що дозволяють будувати графіки
Лабораторна робота 5. Обробка експериментальних даних у електронних таблицях Завдання 1. На першому робочому аркуші документа запровадити вихідні дані, відповідні варіанту завдання. Побудувати графік
Лабораторна робота Microsoft Excel 2007. Робота з діаграмами 1. Вставка стовпців Викликати контекстне меню для стовпця і вибрати Вставити (новий стовпець додається ліворуч виділеного). 1.1. Виділення
Використання MS Excel для графічної обробки отриманих результатів (рекомендації для учнів та вчителів) Редактор таблиць MS Excel, що входить до стандартного комплекту постачання пакета програм MS Office,
АВТОМАТИЗАЦІЯ ЕКОНОМЕТРИЧНОГО МОДЕЛЮВАННЯ Т. А. Заєць УО «Білоруський торговельно-економічний університет споживчої кооперації», м. Гомель У сучасних економічних умовах планування та управління
МІНІСТЕРСТВО ОХОРОНИ ЗДОРОВ'Я РОСІЙСЬКОЇ ФЕДЕРАЦІЇ ДБОУ ВИЩОЇ ПРОФЕСІЙНОЇ ОСВІТИ АМУРСЬКА ДЕРЖАВНА МЕДИЧНА КАДЕМІЯ О.В. ПЛАЩОВА ЕЛЕКТРОННІ ТАБЛИЦІ EXCEL. МЕТОДИЧНІ ВКАЗІВКИ
Лабораторна робота 4 Табулювання функцій та побудова графіків Мета: Придбати навички обчислення таблиці значень функції та побудови графіків. Методичні вказівки: Табулювання функції - це обчислення
Урок 10. Електронні таблиці Основні параметри електронних таблиць. ЕТ дозволяють обробляти великі масиви числових даних. На відміну від таблиць на папері, електронні таблиці забезпечують проведення
Теми практичних робіт: Практична робота 1. Введення даних у комірки, редагування даних, зміна ширини стовпця, вставка рядка (стовпця) Практична робота 2. Введення формул Практична робота 3.
ЛАБОРАТОРНІ РОБОТИ ПО MS EXCEL 2007 ЛАБОРАТОРНА РОБОТА 1.... 1 ЛАБОРАТОРНА РОБОТА 2... 3 ЛАБОРАТОРНА РОБОТА 3... 4 ЛАБОРАТОРНА РОБОТА 4... 7 ЛАБОРАТОРНА РОБОТА
АППРОКСИМАЦІЯ Насправді часто доводиться зіштовхуватися із завданням згладжування експериментальних даних завдання апроксимації. Основне завдання апроксимації - побудова наближеної (апроксимуючої) функції
Лабораторно-практична робота 13 «Пов'язані таблиці у MS Excel 2007» Основні засади формування робочої книги. Для правильної організаціїроботи в електронних таблицях Excel 2007 сформуйте макет
Excel. Імена діапазонів Можливо, вам доводилося працювати з листами, в яких використовувалася, формула типу: = СУМ (А5000: А5078). Ви гадали, що ж знаходиться в осередках А5000: А5078!? Якщо в осередках А5000: А5078
Інвестування нерухомості: економіка, управління, експертиза.
Розділ 1 Основи побудови діаграм Дані в електронній таблиці представлені у вигляді рядків та стовпців. При додаванні діаграми цінність цих даних можна підвищити, виділивши зв'язки та тенденції, які не
ОСНОВНІ КОМАНДИ ТА ОПЕРАЦІЇ! Перевірте, як Ви запам'ятали вивчений матеріал Операційна система Windows 7 та текстовий процесор MS Word Основні дії під час роботи у Windows 7. Виділити значок Клацнути
Лабораторна робота Тема: Побудова графіків функцій Мета роботи: Вивчення графічних можливостей пакета Ms Ecel Придбання навичок побудови графіка функції на площині засобами пакета
ПОБУДУВАННЯ ДІАГРАМ. ТАБУЛЮВАННЯ ФУНКЦІЙ Мета роботи: освоїти основні прийоми створення та редагування діаграм; вивчити операцію копіювання формул за допомогою заповнення; навчитися вирішувати розрахункові
1 Лабораторна робота 3 Розв'язання задач. Підбір параметрів, пошук рішення 1. Реалізація математичної моделі в Excel Математична модельце опис стану поведінки деякої реальної системи (об'єкта,
Лабораторна робота 6. Побудова емпіричної залежності теплоємності речовини від температури методом найменших квадратів. Побудувати графік температурної залежності теплоємності речовини в
Загальні відомості. Табулювання функції - це обчислення значень функції (залежна змінна) за зміни аргументу функції (незалежна змінна) від деякого початкового значення до деякого кінцевого
ВВЕДЕНИЕ Табулювання функції - це обчислення значень функції (залежна змінна) при зміні аргументу функції (незалежна змінна) від деякого початкового значення до деякого кінцевого
Практичне заняттяАналіз результатів тестування Для аналізу результатів тестування виконаємо такі действия:. підрахуємо середній балпо групі, отриманий при тестуванні; за матрицею результатів
28 Розділ 1. Починаємо працювати з Microsoft Excel 2013 Вставка та видалення осередків, рядків та стовпців Якщо у вже набрану частину таблиці потрібно вставити новий осередок, стовпець або рядок, клацніть мишею на стрілці
Розділ 8 Бази даних у OpenOffice.org Calc У цьому розділі ми вивчимо можливості пакету OpenOffice.org Calc під час роботи з базами даних. Досить часто виникає необхідність зберігати та обробляти дані
Практична робота 8 Тема: ОЧИСЮВАЛЬНІ ФУНКЦІЇ ТАБЛИЧНОГО ПРОЦЕСОРА MICROSOFT EXCEL ДЛЯ ФІНАНСОВОГО АНАЛІЗУ Мета заняття. Вивчення інформаційної технології використання вбудованих обчислювальних
Основи зведених таблиць Визначення зведеної таблиці Уявіть, що Excel це великий ящик з інструментами. Тоді зведена таблиця це лише один із інструментів Excel. Продовжуючи аналогію з інструментами,
Лабораторна робота Початкове знайомство з Microsoft Office Excel 2007 В результаті виконання даної лабораторної роботи Ви зможете: знати основні поняття та об'єкти табличного процесора, складати
Тема 6.8. Обчислення певного інтегралуДидактична ціль. Ознайомити учнів з методами наближеного обчислення певного інтегралу. Виховна ціль. Тема цього заняття має велике
Лабораторна робота 5 Оформлення тексту у вигляді списків та стовпчиків Створення списків У текстових документах перерахування різного типу оформляються у вигляді списків. Існують списки різних типів: нумеровані
Економетричне моделювання Лабораторна робота 3 Парна регресія Зміст Парна регресія... 3 Метод найменших квадратів (МНК)... 3 Інтерпретація рівняння регресії... 4 Оцінка якості збудованої
«MICROSOFT OFFICE EXCEL» Дисципліна «Програмні засоби професійної діяльності» Лектор: Ст. викладач кафедри «Електроприводу та електрообладнання» Вороніна Наталія Олексіївна Призначення
Основні способи введення даних у NormCAD: На вкладці Дані У тексті звіту У режимі діалогу (автоматичний запит даних під час розрахунку) На вкладках документа (у таблицях) Введення даних на вкладці
1 Лабораторна робота 1 Редагування робочої книги. Побудова діаграм Мета роботи: Вивчення способів роботи з даними в осередку. Вивчення можливостей автозаповнення. Побудова діаграм. Завдання 1.
6 цілей інвестування в ІТ (опитування) Підвищення ефективності операційної діяльності Нові товари, послуги, бізнес-моделі Тісні контакти з покупцями та постачальниками Підтримка прийняття рішень Конкурентні
ПЗ 6. Технології використання Пакету аналізу для статистичної обробкиданих 1. Випробування гіпотез Дуже часто Генеральна сукупність 1 має підкорятися деяким параметрам. Наприклад, фасувальна
Комбінована діаграма в Excel Комбінована діаграма поєднує два і більше типи стандартних діаграм. Для створення комбінованої діаграми потрібно виконати кілька кроків: Виділити
Практична робота Створення контролюючих систем засобами програми Microsoft Excel Завдання 1 Створити систему контролю знань учнів засобами програми Microsoft Excel, що містить не менше 3 тестових
1. Введення Лабораторна робота 3 Підбір параметрів Під час вирішення різних завдань часто доводиться займатися проблемою підбору одного значення шляхом зміни іншого. Для цієї мети дуже ефективно використовується
Міністерство освіти та науки Російської ФедераціїФедеральне державне бюджетне освітня установавищого професійної освіти«Володимирський державний університетімені
Лабораторна робота. MS Excel 1. Створіть робочу книгу, зберігши її під ім'ям «Офісні програми». Не забувайте періодичні виконувати збереження інформації. 2. Перейменуйте перший лист, поставивши його
Завдання розподілу ресурсів підприємства Змістовна постановка завдання Фабрика випускає сумки: жіночі, чоловічі, дорожні. Дані про матеріали, що використовуються для виробництва сумок та місячний запас
Робота з діаграмами у MS Excel Діаграми призначені для графічного представлення даних. За допомогою діаграм суттєво спрощується процес аналізу залежностей між різними показниками, представленими
Робота зі списками в MS EXCEL Мета: Придбати навички пошуку та агрегування даних у списку. Коротка теоріяКомп'ютерні інформаційні технологіїшироко використовуються для аналізу даних та підготовку управлінських
Графічне рішення систем рівнянь Аналітична геометрія вивчає геометричні об'єкти за їх рівняннями. MS Excel надає широкі можливості візуалізації різних рівнянь. В Excel
Розділ 7 Обробка результатів експерименту в OpeOffice.org Calc У цьому розділі ми розглянемо можливості пакета OpeOffice.org Calc під час вирішення завдань обробки експериментальних даних. Однією з найпоширеніших
Паспортизація. Система паспортизації обладнання котелень та елементів системи теплопостачання дозволяє враховувати індивідуальні технічні характеристикиреальних об'єктів під час виконання розрахункових завдань.
Я повторюсь. Поведінка натовпу інерційна. А значить ймовірність того, що натовп завтра поводитиметься також як учора і позавчора набагато вище, ніж ймовірність зміни настрою.
Для того, щоб відстежувати поведінку натовпу на ринку, існує стародавній індикатор MACD. Його абревіатура розшифровується як moving average convergence-divergence або якщо російською сходження-розбіжність ковзаючих середніх (маються на увазі історичні значення цін на акції або інші інструменти).
Графічний зміст гістограми MACD полягає у підтвердженні продовження тенденції (напряму до розвитку) руху ціни. Грубо кажучи, акції продовжують дешевшати чи дорожчати. Напрямок руху ціни визначається як різниця між двома сусідніми стовпчиками.
Для побудови гістограми MACD використовуємо excel.
1) Спочатку нам знадобляться історичні дані для аналізу. У попередній статті я наводив приклад, де такі дані можна роздобути. Наслідуємо цей приклад і перейдемо на брокерську сторінку експорту даних:
Виставивши вимоги до формату даних, що скачуються, отримуємо файл з даними формату csv, який розуміє excel. Також історичні дані по інструменту, що цікавить нас, можна скачати на сайті брокера ЗАТ «ФІНАМ по цій засланні .
2) дані слід відформатувати як описано у .
Зрештою має вийти ось такий набір:
3) Тепер створимо новий аркуш у книзі excel для розрахунків та побудови графіка технічного аналізу. Так і назвемо цей лист: "Розрахунок MACD". Потім скопіюємо на цей лист стовпець з датами
4) Тепер розрахуємо експоненційну ковзну середню з вікном у 12 днів (EMA 12). ЕМА 12 розраховується за формулою:
Закладемо цю формулу в стовпець праворуч від ціни закриття
Скопіюємо формулу, що вийшла, в комірку нижче і трохи підредагуємо: замість значення з комірки B3, у другій частині формули, підставимо значення з комірки C2. C2 це і буде EMA12 попереднього дня.
Повинно вийти так:
Тепер розмножимо формулу, отриману в другому осередку для всього стовпця EMA12. Для цього клікнемо один раз мишкою в комірку C3 так, щоб навколо комірки з'явилася чорна жирна рамочка, потім переміщуємо курсор у правий нижній кут чорної жирної рамочки так, щоб курсор набув форми жирного чорного хрестика і подвійним клацанням лівої кнопки мишки розмножуємо формулу на весь стовпець. Ось так:
Тепер аналогічним чином розрахуємо експоненційну ковзну середню з вікном 26 днів (EMA 26). ЕМА 26 розраховується за формулою:
Закладемо цю формулу в стовпець праворуч від розрахованої EMA12. Для цього запис у комірку починаємо з символу «=», що повідомляє процесору excel про те, що буде вводиться формула. Для першого осередку формула трохи інша ніж інших осередків, через те, що замість вчорашньої EMA26 слід підставити сьогоднішню ціну закриття. Ось так:
Скопіюємо формулу, що вийшла, в комірку нижче і трохи підредагуємо: замість значення з комірки B3, в другій частині формули, підставимо значення з комірки D2. D2 це і буде EMA26 попереднього дня. Повинно вийти так:
Тепер розмножимо формулу, отриману в другому осередку для всього стовпця EMA26. Для цього клікнемо один раз мишкою в комірку D3 так, щоб навколо комірки з'явилася чорна жирна рамочка, потім переміщуємо курсор у правий нижній кут чорної жирної рамочки так, щоб курсор набув форми жирного чорного хрестика і подвійним клацанням лівої кнопки мишки розмножуємо формулу на весь стовпець. Ось так:
Вітаю! Ми з вами впоралися із розрахунком експоненційних середніх. Тепер слід отримати швидку лінію MACD. Для цього потрібно з EMA12 відняти EMA26. Заб'ємо цю формулу в наступний стовпець праворуч:
Тепер потрібно вирахувати дев'ятиденну експоненційну ковзну середню для «швидкої» лінії MACD. Отримана лінія називатиметься "сигнальною" лінією MACD. Розрахунок зробимо за такою формулою:
Аналогічно забиваємо формулу розрахунку в excel в комірку правіше «швидкої» лінії MACD:
У комірці нижнього ряду коригуємо формулу також, як робили це при розрахунку двадцятишестиденної та дванадцятиденної експоненційних ковзних середніх. Ось така має бути формула в осередку F3:
І нарешті ми можемо розрахувати останній стовпець даних для побудови гістограми MACD. Значеннями цього стовпця для побудови гістограми є різниця між швидкою і сигнальною лініями MACD. Вбиваємо останню формулу розрахунку даних для побудови гістограми:
Розглядати гістограму MACD набагато зручніше поруч із графіком коливання цін на аналізований інструмент. У попередній статті я докладно описав, як побудувати такий графік. Для побудови графіка ціни інструмент скопіюємо вибірку необхідних даних на окремий лист. Якось так:
Побудова біржового графіка найпростіше зробити тут же, на цьому аркуші. Потім слід його скопіювати на окремий аркуш, на якому ми розмістимо і гістограму MACD.
Створюємо окремий аркуш для наших графіків. Вставляємо з буфера обміну копіювану діаграму і трохи налаштовуємо її зовнішній вигляд. Вікно з графіком розтягується і скорочується по довжині і ширині подібно до вікон у самій Windows.
А тицьнувши лівою кнопкою миші в шкалу зі значеннями цін, можна змінити формат даних осі побудови графіка. Після такого стукання шкала значень вертикальної (у нашому випадку) осі виділяється прямокутною рамкою. Як тільки з'явилася така рамка, слід натиснути праву кнопку миші для виклику контекстного меню. У контекстному меню лівою кнопкою миші вибираємо рядок<Формат оси…>, ось так:
У діалоговому вікні налаштування параметрів осі графіка налаштовуємо мінімальне значення (80) і максимальне (160). Це верхні два рядки в діалоговому вікні, що відкрилося. На малюнку нижче показано потрібне положення радіокнопок та вписані значення 80 та 160 у відповідні рядки:
Під вікном графіка цін вставляємо вікно майбутньої гістограми MACD. У головному меню вибираємо вкладку<<Вставка>> потім підміню<<Гистограмма>> і в меню, що випадає, вибираємо лівий верхній значок гістограми, цей значок підсвічений жовтим на скрін-шоті нижче:
Головне, перед вставкою другого графіка не забути зняти виділення з першого. Інакше може статися заміщення одного графіка іншим, а нам потрібні обидва графіки.
Перед викликом меню<<Гистограмма>> непогано навести курсор на комірку А16 і натиснути ліву кнопку миші. Після вставки гістограми нам необхідно вказати наш стовпець із розрахунковими даними гістограми MACD. Для цього слід навести курсор миші на гістограму та натиснути праву кнопку миші для виклику контекстного меню керування діаграмою. У контекстному меню, що відкрилося, вибираємо пункт<Выбрать данные>:
Після натискання кнопки<<Добавить>> у попередньому вікні слід набрати найменування нашого графіка — «MACD», а нижньому ряду натиснути кнопочку праворуч від ряду:
Після натискання кнопки праворуч від нижнього ряду відкривається вузьке віконце «Зміна ряду». Не закриваючи цього вікна, переходимо за допомогою миші на лист з назвою MACD:
Після того, як стовпець з даними охоплений тонкою пунктирною лінією у вікні «Зміна ряду», слід натиснути кнопочку праворуч. Після цього відкриється вікно "Зміна ряду" з двома рядками. Ось у цьому віконці можна натиснути кнопку<
Повернувшись на аркуш із найменуванням «ГРАФІКИ» у вікні вибору даних для побудови гістограми теж натискаємо кнопку<
Можна трохи пограти з розміром вікон для графіків і отримати той результат, який здається наочнішим:
А ось ті ж графіки, побудовані торговою системою QUIK. Схоже, вийшло у нас з вами?
Дорогий читачу! Якщо ти вирішив побудувати ці графіки і в тебе щось не виходить - залиш своє питання в коментарях і разом ми обов'язково розберемося і навчимося будувати графіки в excel.
Вихідні файли excel з яких зроблено скріншоти і в яких є побудовані графіки можна завантажити по .
У бізнесі, як і в будь-якій іншій діяльності людина, хоче знати, а що буде далі. Навіть важко уявити багатство того щасливця, який зі 100% точністю міг би вгадувати майбутнє. Але, на жаль (або, на щастя) дар передбачення зустрічається вкрай рідко. А… намагатися хоча б у загальних рисахуявити майбутню бізнес ситуацію підприємець просто зобов'язаний.
Спочатку я хотів написати в одному пості відразу про кілька простих і зручних методик, але піст став виходити дуже великим. І тому буде кілька постів присвячених теміпрогнозування. У даному пості ми опишемо один із найпростіших методів прогнозування з використанням можливостей Excel – метод ковзного середнього.
Найчастіше у практиці маркетингових досліджень прогнозуються такі величини:
- Обсяги продажів
- Розмір та ємність ринку
- Об'єми виробництва
- Обсяги імпорту
- Динаміка цін
- та ін.
Для прогнозування, яке ми розглядаємо в даному пості, раджу дотримуватися наступного простого алгоритму:
1. Збір вторинної інформаціїз проблеми(бажано як кількісної, і якісної). Так, наприклад, якщо Ви прогнозуєте розмір свого ринку, потрібно зібрати статистичну інформацію щодо ринку (обсяги виробництва, імпорту, динаміку цін, обсяги продажу та ін.) так і тенденції, проблеми чи можливості ринку. Якщо ви прогнозуєте обсяг продажу, тоді вам потрібні дані про продаж за період. Для прогнозування чим більше історичних даних ви розглянете, тим краще. Бажано прогнозування доповнити аналізом факторів, що впливають на прогнозоване явище (можна SWOT, PEST аналіз або будь-який інший). Це дозволить розуміти логіку розвитку, і ви зможете таким чином перевіряти правдоподібність тієї чи іншої моделі тренду.
2. Далі бажано перевірити кількісні дані. Для цього потрібно порівняти значення тих самих показників, але отриманих з різних джерел. Якщо все сходитися, можна «заганяти» дані в Excel. Також дані повинні відповідати таким вимогам:
- Базова лінія включає результати спостережень - починаючи з ранніх і закінчуючи останніми.
- Усі періоди базової лінії мають однакову тривалість. Не слід змішувати дані, наприклад, за день із середніми триденними показниками.
- Спостереження фіксуються в той самий момент кожного тимчасового періоду. Наприклад трафік замірятись повинен в один і той же час.
- Перепустка даних не допускається. Перепустка навіть одного результату спостережень небажана при прогнозуванні» тому, якщо у ваших спостереженнях відсутні результати за незначний відрізок часу, постарайтеся заповнити їх хоча б приблизними даними.
3. Перевіривши дані, можна застосовувати різні методики прогнозування. Почати я хотів би з самого простого методу – МЕТОДУ КОВЗНОГО СЕРЕДНЬОГО
МЕТОД КОВЗНОГО СЕРЕДНЬОГО
Метод ковзного середнього застосовувати досить нескладно, проте він дуже простий для побудови точного прогнозу. При використанні цього методу прогноз будь-якого періоду є не що інше, як отримання середнього показника за кількома попередніми спостереженнями часового ряду. Наприклад, якщо ви вибрали ковзне середнє за три місяці, прогнозом на травень буде середнє значення показників за лютий, березень та квітень. Вибравши як метод прогнозування ковзне середнє за чотири місяці, ви зможете оцінити травневий показник як середнє значення показників за січень, лютий, березень та квітень.
Як правило, прогноз із застосуванням ковзного середнього розглядається як прогноз на період, що безпосередньо наступає за періодом спостереження. Разом з цим такий прогноз можна застосувати, коли досліджуване явище розвивається послідовно, тобто. є певні тенденції, і крива значень не скаче по діаграмі як пригоріла.
Щоб визначити, скільки спостережень бажано включити в середнє ковзне, потрібно виходити з попереднього досвіду і наявної інформації про набір даних. Необхідно витримувати рівновагу між підвищеним відгуком ковзного середнього на кілька свіжих спостережень і великою мінливістю цього середнього.
Отже, як це робити вExcel
1. Припустимо, що у Вас є обсяги місячних продажів за останні 29 місяців. І ви хочете визначити, який обсяг продажів буде у 30 місяці. Але, якщо чесно, зовсім не обов'язково при розрахунку прогнозних значень оперувати 30 історичними значеннями, адже цей метод використовуватиме для розрахунку середнього лише кілька останніх місяців. Тому для розрахунку достатньо лише кілька минулих місяців.
2. Наводимо цю таблицю як зрозумілий Excel, тобто. щоб усі значення були в одному ряді.
3. Далі вводимо формулу розрахунку середнього за попереднім трьом (чотирьом, п'яти? як самі оберіть) значенням (див. в). Найзручніше все-таки використовуватиме розрахунку останні 3 значення, т.к. якщо враховувати більше, дані будуть занадто середні, якщо менше - не будуть точними.
4. Використовуючи функцію автозаповнення для всіх наступних значень до 30, прогнозного місяця. Таким чином, функція розрахує прогноз на червень 2010 р. Згідно з прогнозними значеннями у червні продажу становитимуть близько 408 одиниць товару. Але зверніть увагу, що якщо тенденція падіння постійна, як у нашому прикладі, розрахунок прогнозу за середньою буде трохи завищеним, або буде «відставати» від реальних значень.
Ми розглянули одну із самих простих методикпрогнозування – метод ковзного середнього. У наступних постах ми розглянемо інші, точніші і складніші методики. Сподіваюся, мій пост буде Вам корисним.
Завантаження...