навчитися робити прогноз за допомогою трендових моделей

Таблиця 1.1 – Статистичні дані з виробництва вугілля готового

1.4. Оберіть для введеної таблиці один з визначених форматів MS Excel. Для цього виділіть діапазон чарунок A3:M6, викличте пункт основного меню „ Формат ”, підпункт „ Автоформат …” та оберіть формат, що здається вам раціональним для оформлення введених даних. Наприклад, автоформат під назвою „ Список 1 ”.

1.5. Відцентруйте назву таблиці, визначте її формат. Для цього виділіть діапазон чарунок A1:M1, натисніть кнопку панелі інструментів „ Объединить и поместить в центре ” (див. рис. 1.3).

Рисунок 1.3 – Фрагмент панелі інструментів „Форматирование” з кнопкою „Объединить и поместить в центре”

У результаті повинна вийти таблиця на листі MS Excel (див. рис. 1.4).

Рисунок 1.4 – Електронна таблиця з даними для опрацювання

1.6. Збережіть створену електронну таблицю, при збереженні назвіть її на свій розсуд, наприклад, своїм прізвищем. Розширення xls буде додано автоматично, якщо ви не оберете інший тип.Не слід давати таблиці ім’я
Книга 1, бо це стандартне ім’я за шаблоном і кожен наступний користувач може записати свою таблицю під цим самим ім’ям замість вашої. Окрім того, ім’я файлового об’єкту повинно бути інформативним.

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

2. Побудуйте діаграми типу „графік”. Тип діаграми повинен бути адекватним меті аналізу даних, в даному прикладі тип „графік” дає можливість, по-перше, зробити порівняльний аналіз, по-друге, зробити графічний прогноз.

2.1. Для побудови діаграми виділіть потрібний діапазон A3:M5.

Далі скористайтесь майстром діаграм.

Виберіть пункт меню „ Вставка ”, підпункт „ Диаграмма ” або натисніть на кнопку(„ Мастер диаграмм ”) на панелі інструментів„ Стандартная ”. Відкриється вікно „ Мастер диаграмм ”. Для побудови будь-якої діаграми необхідно послідовно пройти чотири кроки Мастера.

2.2. На першому кроці “Тип диаграммы” виберіть з списку „Тип: График”.

В списку „ Вид: ” уточніть, який саме формат графіку буде використаний – „ График с маркерами, помечающими точки данных ” (див. рис. 1.5). Є можливість натиснути на кнопку „ Просмотр результата ” та побачити в області „ Вид: ” зразок вигляду майбутньої діаграми обраного типу та виду.

Натисніть на кнопку „ Далее > ”.

Рисунок 1.5 – Перший крок майстру побудови діаграми

2.3. На другому кроці “Источник данных диаграммы” є можливість задати або змінити діапазон чарунок для створення діаграми.

Вкладка „ Диапазон данных ” нам не потрібна, тому що діапазон ми вже задали, виділивши його перед викликом майстра діаграм.

Вкладка „ Ряд ” потрібна для того, щоб уточнити імена та значення рядів вхідного діапазону. У більшості випадків цей крок можна пропустити, бо MS Excel автоматично виконує розподіл вхідного діапазону на значення рядів, їхні імена та підписи до значень і градуювання осей. Однак в прикладі, що розглядається, у цьому кроці є необхідність, тому що у якості імен рядів виступають дані (роки) такого ж формату (числового цілого) і порядку (10³), як і дані, що є значеннями цих рядів. Тому MS Excel не зміг самостійно відрізнити імена від значень, і в результаті діаграма буде неінформативною – без так званої легенди. До того ж вона буде нести в собі помилку – бо числа, що означають роки, задіяні як значення графіків.

Для виправлення цих помилок (див. рис. 1.6):

- оберіть у списку „ Ряд ” значення „ Ряд 1 ”;

- для обраного ряду 1 натисніть на кнопку згортки, яка знаходиться справа в полі „ Имя: ”. Вікно майстра згорнеться. Виділіть в документі чарунку з адресою A4. Ця чарунка містить в собі числове значення року „ 2008 ”, яке буде іменем першого ряду значень (першого графіку діаграми, що будується);

- натисніть на кнопку розгортки, щоб розвернути вікно майстра діаграм;

- натисніть на кнопку згортки, яка знаходиться справа в полі „ Значения: ”. Виділіть в документі діапазон B4:M4. Цей діапазон чарунок містить в собі числові значення ряду, які складають перший графік діаграми, що будується;

- натисніть на кнопку розгортки, щоб розвернути вікно майстра діаграм;

- оберіть у списку „ Ряд ” значення „ Ряд 2 ” та повторіть для нього усі дії по перейменуванню та уточненню значень;

- натисніть на кнопку згортки, яка знаходиться справа в полі „ Подписи по оси Х: ”. Виділіть в документі діапазон B3:M3. Цей діапазон чарунок містить в собі підписи до значень ряду, які складають градуювання оси абсцис Х для діаграми – графіку, що будується;

- натисніть на кнопку розгортки, щоб розвернути вікно майстра діаграм;

-
натисніть на кнопку „ Далее > ”.

Рисунок 1.6 – Другий крок майстру побудови діаграми, вкладка „Ряд”

2.4. На третьому кроці “Параметры диаграммы” є можливість задати додаткові не критичні для графічного аналізу параметри діаграми.

Наприклад (див. рис. 1.7):

- введіть в поле „ Название диаграммы ” у вкладці „ Заголовки ” наступний текст – назву діаграми, що будується: „ Порівняльний аналіз виробництва вугілля готового ”;

- зніміть активне значення прапорця „ Ось У: основные линии ” у вкладці „ Линии сетки ”;

- активізуйте значення перемикача „ Размещение внизу ” у вкладці „ Легенда ”;

-
натисніть на кнопку „ Далее > ”.

Рисунок 1.7 – Третій крок майстру побудови діаграми, вкладка „Легенда”

2.5. На четвертому кроці “Размещение диаграммы” є можливість задати варіант розміщення діаграми.

Можливі два варіанти розміщення діаграми, що будується:

- „ Поместить диаграмму на листе имеющемся ” – розміщення діаграми на будь-якому з листів, що вже є в книзі електронної таблиці, поверх чарунок. Як правило, це розміщення на тому ж листі, де вхідні дані діаграми. Розміри діаграми при цьому варіюються користувачем, як у рисованого об’єкту, масштаб автоматично не адаптується до розмірів;

- „ Поместить диаграмму на листе отдельном ” – для розміщення діаграми буде автоматично додано у файл електронної таблиці новий лист. Це лист діаграми, що створений спеціально для неї, має свої специфічні риси, пов'язаний з вхідною таблицею даних на іншому листі, але сам не є таблицею. Розміри діаграми при цьому не варіюються, масштаб автоматично адаптований до розмірів.

Для побудови діаграми рекомендується використати другий варіант - окремий новий лист робочої книги спеціального формату, бо це найбільш оптимально для її вигляду. При цьому в полі імені нового листа замість „ Диаграмма 1 ” можна ввести більш інформативне ім’я, наприклад, „ Графіки порівняльного аналізу ” (див. рис. 1.8).

Рисунок 1.8 – Четвертий крок майстру побудови діаграми „Размещение диаграммы”

Натисніть на кнопку „ Готово ”.

2.6. Шкала вертикальної осі (осі значень, або ординат Y) повинна бути змінена таким чином, щоб найбільш наочно відображати динаміку показника.

Після завершення роботи майстра діаграм потрібно змінити шкалу вертикальної осі (осі значень Y) так, щоб розміщення отриманої діаграми в координатній площині було оптимальним. Очевидно, що для цього ось абсцис X повинна перетинати ось значень не в нульовому значенні, а приблизно в значенні „ 4000 ” (найменшому, ніж менше зі значень графіків). При цьому велика порожня частина координатної площини (значення від „ 0 ” до „ 3999 ”) не буде відображатися на діаграмі.

Для зміни шкали вертикальної осі:

- клацніть мишею на вертикальній осі – на кінцях осі з'являться розмірні маніпулятори (невеликі квадратики);

- потім будь-яким способом, наприклад, з контекстного меню, за допомогою пункту меню „ Формат ” – підпункту „ Выделенная ось ” або подвійним клацанням викличте діалогове вікно „ Формат оси ”, відкрийте вкладку „ Шкала ”;

-
у вікні діалогу, що з'явиться, видно, що всі параметри для осі призначені автоматично (відповідні прапорці активні). Для зміни точки перетинання у відповідне поле „ минимальное значение ” введіть нове значення „ 4000 ”. При цьому прапорець „ Авто ” для цього значення буде вимкнений (див. рис. 1.9);

Рисунок 1.9 – Діалогове вікно зміни шкали осі ординат

- клацніть на кнопці „ OK ”, щоб зміни вступили в силу.

Результат побудови порівняльної діаграми – графіків представлені на рис. 1.10.

Рисунок 1.10 – Результат побудови порівняльної діаграми – графіків

3. Зробіть прогноз за допомогою трендових моделей.

Є неповні дані стосовно динаміки розглядуваного показника у 2010 році (див. таблицю 1.1). Необхідно додати графік 2010 року на побудовану діаграму та за допомогою побудови лінії тренду обраної математичної моделі отримати прогнозні значення показника у листопаді та грудні 2010 року з певною достовірністю.

Для того, щоб «добудувати» графік на раніше створену діаграму, можна або скопіювати необхідні дані разом з міткою (іменем нового ряду) в буфер обміну та вставити на діаграму за допомогою підпункту „ Специальная вставка ” пункту меню „ Правка”, або скористуватися пунктом меню „ Диаграмма ”, підпунктом „ Добавить данные … ”.

3.1. Перший засіб – копіювання даних нового графіку:

- виділіть необхідний ряд даних (діапазон A6:K6) і скопіюйте його в буфер обміну (пункти меню „ Правка ” – „ Копировать ”, або підпункт контекстного меню „ Копировать ”, або відповідна кнопка панелі інструментів „ Стандартная ”);

- переключіться на лист з діаграмою;

- виберіть пункт меню „ Правка ”, підпункт „ Специальная вставка ”, або відкрийте список команд кнопки „ Вставить ” панелі інструментів „ Стандартная ” та виберіть команду „ Специальная вставка ”;

·
у вікні діалогу, що виникне, задати потрібний параметр – ввімкнути (активізувати) прапорець „ Имена рядов в первом столбце ” (див. рис. 1.11).

Рисунок 1.11 – Діалогове вікно „Специальная вставка”

Натисніть на кнопку „ ОК ”.

3.2. Другий засіб – додавання даних графіку через пункт меню „Диаграмма”, підпункт „Добавить данные …”:

- переключіться на лист з діаграмою;

- виберіть пункт меню „ Диаграмма ”, підпункт „ Добавить данные … ”;

- натисніть на кнопку згортки, яка знаходиться справа в полі „ Диапазон ”, потім натисніть на ярлик листа з даними („ Лист 1 ”) та виділіть необхідний діапазон A6:K6 (див. рис. 1.12), включаючи ім’я (мітку) „ 2010 ”;

- натисніть на кнопку розгортки;

- натисніть на кнопку „ ОК ”.

3.3. Для побудови прогнозної кривої (лінії тренду) на новому графіку:

- виділіть криву, для якої виконується прогноз – графік „ 2010 ”;

- виберіть пункт меню „ Диаграмма ”, підпункт „ Добавить линию тренда ” або викличте контекстне меню і виберіть пункт „ Добавить линию тренда ”;

- у вікні діалогу, що з'явиться, виберіть „ Тип ” лінії тренду з 6 запропонованих - „ Полиномиальная ”, за допомогою лічильника введіть ступінь поліному – „ 6 ”, максимальний в MS Excel (див. рис. 1.13).

Рисунок 1.12 – Процес виділення додаткового ряду при додаванні графіку через пункт меню „Диаграмма”, підпункт „Добавить данные …”

Рисунок 1.13 – Діалогове вікно вибору типу лінії тренду та параметрів

Поліноміальний тип обраний, тому що графік „ 2010 ” має типовий для поліномів вигляд (дані поперемінно зростають і понижуються) і найбільш близька його апроксимація ймовірно буде саме за допомогою цієї лінії тренду. Ступінь „ 6 ” обрано, тому що графік „ 2010 ” містить 5 екстремумів (мінімумів та максимумів), а поліном n-го ступеню може описати кількість екстремумів, що дорівнює n – 1.

- оберіть вкладку „ Параметры ” – за її допомогою можна задати додаткові параметри, що впливають на відображення кривої, яка виконує апроксимацію;

- за допомогою лічильника введіть прогноз „ вперед на: ” – „ 2 ” періоди, тобто місяці листопад та грудень;

- зробіть активним прапорець „ показывать уравнение на диаграмме ” – це дасть можливість отримати рівняння поліному, що описує прогнозну криву;

- зробіть активним прапорець „ поместить на диаграмму величину достоверности аппроксимации (R^2) ”. За допомогою коефіцієнта детермінації R² оцінюється ступінь близькості апроксимації вхідних даних обраною функцією. Якщо є декілька варіантів типів апроксимуючих функцій, які підходять, та немає яких-небудь теоретичних міркувань, можна обрати функцію з найбільшим коефіцієнтом детермінації (таким, що стримиться до 1). При оцінці точності (достовірності) апроксимації та прогнозу говорять, що вона достатньо висока, якщо R² близький до 1. Або що прогноз зроблений з достовірністю R². Прогнозні значення за листопад та грудень можна визначити, спроектувавши відповідні точки на ось значень Y. Для листопада це приблизно 7000, а для грудня 14000, з достовірністю R² = 0.73. Достовірність досить низька (менше 0.9), тому, ймовірно, необхідно шукати інші методи прогнозу.

Натисніть кнопку „ ОК ”. Отриману діаграму разом з кривою апроксимації див. на рис. 1.14.

Рисунок 1.14 – Діаграма – графіки разом з лінією тренду (прогнозною)

Індивідуальні завдання з лабораторної роботи № 1

Згідно варіанту виконайте наступні завдання.

1. Створіть та збережіть електронну таблицю, вивчіть принцип заповнення списків та чисельних рядів.

2. Побудуйте діаграму типу „графік”.

3. Зробіть прогноз за допомогою трендових моделей. Оцініть ступінь достовірності прогнозу.

4. Виконайте друк таблиці та діаграми.

4.1. Виконайте попередній перегляд таблиці. Якщо вона не вміщується на один лист, в режимі перегляду натисніть на кнопку „ Страница ”. Розверніть лист (орієнтація альбомна), або змініть його масштаб за допомогою спеціального лічильника, у % до натуральної величини. Також ці дії можна виконати у відповідному діалоговому вікні, викликавши пункт меню „ Файл ”, підпункт „ Параметры страницы ”. Роздрукуйте таблицю.

4.2. Тому що побудована діаграма виведена на окремий лист, її масштаб не потрібно змінювати перед друком, вона автоматично буде розташована на окремому листі альбомної орієнтації. Роздрукуйте діаграму.

5. Оформіть звіт з роботи, стисло опишіть хід роботи і основні моменти виконання завдань. Додайте роздруківки, інтерпретацію результатів у вигляді осмислених коментарів або висновків та титульний лист стандартного зразка. Представте звіт до захисту керівнику з лабораторного практикуму.

Варіант 1

Виробництво нафти сирої, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 2

Виробництво газового конденсату, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 3

Виробництво газу нафтового, млн. м³., по місяцях 2008, 2009 і 2010 років

Варіант 4

Виробництво яловичини і телятини свіжої, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 5

Виробництво яловичини і телятини мороженої, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 6

Виробництво свинини свіжої, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 7

Виробництво свинини мороженої, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 8

Виробництво м'яса і субпродуктів харчових свійської птиці, свіжі чи охолоджені, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 9

Виробництво м'яса і субпродуктів харчових свійської птиці, морожені,
тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 10

Виробництво ковбасних виробів, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 11

Виробництво молока обробленого рідкого, тис. т., по місяцях 2008, 2009 і 2010 років

Варіант 12

Виробництво вершків жирністю більше 8%, тис. т., по місяцях 2008, 2009 і 2010 років

[1] Виробництво найважливіших видів промислової продукції по місяцях 2008, 2009 і 2010 років. – Експрес випуск Державного комітету статистики України від 19.04.2010 р. № 90.