Завдання 6. Аналіз рядів динаміки і прогнозування
Умова 1. Побудуйте ряд динаміки абсолютних величин на основі щорічних даних про будь-яке соціально-економічне явище за останні 9 або 11 років, скориставшись даними будь-якого статистичного щорічника, збірника або іншого джерела (с. 8) з обов’язковим посиланням на нього (додаток 7).
2. Визначте аналітичні абсолютні та відносні показники динаміки: ланцюгові і базисні та перевірте їх взаємозв'язок, і середні показники ряду динаміки. Результати розрахунку показників динаміки викладіть у формі таблиці та багатостовпчикових діаграм, побудованих за фактичними рівнями і абсолютними ланцюговими показниками. Поясніть обчислені показники.
4. Визначте значення трирічної, п’ятирічної та семирічної плинних середніх і побудуйте лінійну діаграму за фактичними рівнями ряду динаміки та обчисленими плинними середніми і зробіть висновки щодо характеру основної тенденції розвитку.
5. Визначте параметри лінійного рівняння тренду такими методами: 1) складанням і розв’язуванням системи нормальних рівнянь; 2) застосуванням вбудованої функції ЛИНЕЙН; 3) побудови лінійної діаграми з відображенням параметрів лінійного рівняння тренду та коефіцієнта детермінації. Поясніть зміст обчислених параметрів трендового рівняння і коефіцієнта детермінації та здійсніть його перевірку на істотність за допомогою F— критерію з рівнем істотності α = 0,05 і 0,01.
6. Зробіть висновки щодо правомірності використання лінійного рівняння
тренду для описання основної тенденції розвитку, використавши метод перебору різних функцій, запропонованих програмою Excel.
7. Визначте очікуваний рівень досліджуваного явища на наступні два роки.
Література: [1, с. 131 — 142]; [3, 334 — 375]; [4, с. 351 —414]; [5, с.297 —353]; [6, с. 173, 196 — 198].
Розв’язування завдання та подання одержаних результатів засобами Excel
Для обчислення аналітичних показників динаміки побудуємо таблицю (табл. 15), в яку вводимо в діапазони комірок А4:А16 і В4:В16 вихідні дані про динаміку врожайності овочів Чернігівської області за 1994 — 2006 рр. за данними, що подані у щорічному статистично довіднику Чернігівської області.
Для розрахунку засобами Excel абсолютних змін рівнів урожайності соняшнику порівняно з попереднім роком, тобто ланцюгових абсолютних приростів або зменшень за формулою tл = уt – уt–1 потрібно в активізовану комірку C5 (табл. 15) згідно зазначеної формули їх розрахунку ввести формулу =B5 – B4 або таку формулу =В5:В16 – В4 і натиснути клавішу ENTER, а потім після появи в ній першого значення обчисленого показника виділити її і здійснити копіювання записаної формули в діапазон комірок C6:C16. Значення ланцюгових показників абсолютних змін з’явиться в діапазоні комірок С6:С16.
Обчислене ланцюгове абсолютне зменшення (30) для 1995 року показує, що врожайність овочів в Чернігівській області в 1995 р. порівняно з 1994 р. збільшилася на 30 ц/га, а розрахований ланцюговий приріст (-34) для 1996 р. означає, що врожайність овочів в 1996 р. порівняно з 1995 р. зменшилася на 34 ц/га. Аналогічним чином робиться висновок по решті абсолютних ланцюгових показників.
Аналізуючи ланцюгові абсолютні показники за усі роки досліджуваного періоду, можна зробити висновок, що рівень урожайності овочів в чернігівській області за всі роки характеризується значними коливаннями по роках і ці коливання спостерігаються із року в рік. Найзначніше підвищення врожайності овочів мало місце в 1995 р. порівняно з 1994 р. і в 2006 р. порівняно з 2005 р. і воно становило 30 ц/га, а найбільше зниження — в 1996 р. порівняно з 1995 р. і становило 34 ц/га.
Для обчислення абсолютних змін рівнів урожайності овочів порівняно з базисним 1994 р., тобто базисних абсолютних приростів або зменшень за формулою tл = уt – у0 треба виділити діапазон комірок D5:D16 і в першу комірку виділеного діапазону D5 (табл. 15) згідно зазначеної формули їх розрахунку ввести таку формулу =B5:B16 – B4 та виконати клавішну комбінацію CTREL+SHIFT+ENTER. Значення показника абсолютних змін з’явиться в діапазоні комірок D5:D16. Значення цього показника можна обчислити і іншим способом, який полягає в копіюванні створеної з використанням відносних і змішаних адресів комірок формули його розрахунку. Оскільки при розрахунку цього показника від даного рівня ряду динаміки віднімається одне і те саме значення базисного рівня, тому в формулі для даного рівня вказується відносна адреса комірки, а для базисного рівня — змішана адреса значення базисного рівня, яке при копіюванні залишається без зміни. Для цього при складанні формули розрахунку =В5–B$4 в активізованій комірці D5 потрібно після введення в неї адреси комірки В4 два рази натиснути клавішу F4. Після цього потрібно натиснути на клавішу ENTER і скопіювати отриману формулу до решти комірок D6:D16.
З даних табл. 15 видно, що в 1999 р. базисне абсолютне зменшення дорівнює –10. Це означає, що врожайність овочів в Чернігівській області в 1999 р. порівняно з 1994 р. зменшилася на 10 ц/га.
Аналіз базисних абсолютних показників за майже всі роки досліджуваного періоду свідчить, що в Чернігівській області порівняно з 1994 р відбувалося зменшення урожайності овочів, причому найбільше зниження спостерігалось у 2000 р. і становило -12 ц/га, а от в 1995 1998 і 2006 роки порівняно з 1994 роком спостерігається збільшення урожайності, причому найбільше збільшення спостерігалося в 1995 р. і становило 30 ц/га.
Для перевірки взаємозв’язку ланцюгових і базисних абсолютних показників за формулою за даними табл. 15, щоб отримати суму ланцюгових абсолютних показників активізуємо комірку С17 і введемо до неї формулу =СУММ(С5:С16). Після натиснення клавіші ENTER в комірці С17 отримаємо = 28. Порівнюючи отримане число з числом, наведеним в комірці D16, яке являє собою , бачимо, що вони точно співпадають.
Для розрахунку показників абсолютної зміни швидкості (абсолютного прискорення або уповільнення) рівнів урожайності овочів за формулою δt = t – t–1 треба в активізовану комірку Е6 (табл. 15) згідно зазначеної формули розрахунку даного показника ввести формулу =С6 – С5 або таку формулу = С6:С16 – С5 і натиснути клавішу ENTER, а потім після появи у ній першого значення обчисленого показника виділити її і здійснити копіювання записаної формули в діапазон комірок Е7:Е16. Значення ланцюгових показників абсолютних змін з’явиться в діапазоні комірок Е7:Е16.
З даних табл. 15 видно, що для 1996 р. порівняно з 1995 р. різниця абсолютних ланцюгових показників має знак “мінус” і дорівнює 64, а для 1997 р. порівняно з 1996 — знак “плюс” і дорівнює 32. Це свідчить про уповільнення абсолютної зміни швидкості рівня врожайності овочів в Чернігівській області 1996 р. порівняно з 1995 р. на 64 ц/га, а в 1997 р. порівняно з 1996 р. про прискорення абсолютної зміни швидкості рівня врожайності овочів на 32 ц/га.
Аналіз абсолютної зміни швидкості за усі роки досліджуваного періоду, показує, що для розглядуваного періоду характерна зміна знака абсолютної швидкості, тобто чергування прискорення і уповільнення абсолютної зміни швидкості, що свідчить про нерівномірність зміни рівнів урожайності овочів в Чернігівській області протягом досліджуваного періоду.
Для обчислення темпів зростання рівнів урожайності овочів в формі відсотків порівняно з попереднім роком, тобто ланцюгових темпів зростання за формулою потрібно в активізовану комірку F5 (табл. 15) згідно зазначеної формули розрахунку даного показника ввести формулу: =B5/B4*100 або таку формулу =(В5:В16/В4)*100 і натиснути клавішу ENTER, а потім після появи у ній першого значення обчисленого показника активізувати її та здійснити копіювання записаної формули в діапазон комірок F6:F16. Значення ланцюгових показників абсолютних змін з’явиться в діапазоні комірок F6:F16.
За даними табл. 15 для 1995 р. ланцюговий темп зростання в формі відсотків становить 123,1%, а для 1996 р. 78,8%, і в формі коефіцієнтів — відповідно 1,231 і 0,788. Це означає, що в 1995 р. врожайність овочів в
Чернігівській області становила проти 1994 р. 123,1 %, або вона порівняно з 1994 р. зросла в 1,231 рази. Стосовно ланцюгового темпу зростання 1996 р. можна сказати, що врожайність овочів проти 1995 становила 78,8%, або вона зросла в 0,788 рази. Аналогічні висновки можна зробити за рештою обчислених в табл. 15 ланцюгових темпів зростання.
Аналіз обчислених ланцюгових темпів зростання для всіх років з 1995 до 2006 року підтверджує раніше зроблений на основі ланцюгових абсолютних показників висновок про нерівномірність зміни рівня врожайності овочів в чернігівській області протягом усього досліджуваного періоду. Найбільше значення ланцюгового темпу зростання, яке дорівнює 123,4%, відноситься до 2006 р., а найменше, яке становить 78,8% — до 1996 р.
Для розрахунку темпів зростання рівнів урожайності овочів порівняно з 1994 р. у формі відсотків, тобто базисних темпів потрібно в активізовану комірку G5 (табл. 15) згідно формули їх розрахунку ввести формулу =(В5:В16/В4)*100 та виконати клавішну комбінацію CTREL+SHIFT+ENTER. Значення базисних темпів з’явиться в діапазоні комірок G5:G16. Значення цього показника можна обчислити описаним вище способом копіювання створеної з використанням відносних і змішаних адресів комірок формули його розрахунку. Для цього при складанні в активізованій комірці G5 формули розрахунку: = (В5/B$4)*100 потрібно після введення в неї адреси комірки В4 два рази натиснути клавішу F4. Після цього потрібно натиснути на клавішу ENTER і скопіювати отриману формулу до решти комірок G6:G16.
За даними табл. 15 для 1996 р. базисний темп зростання в формі відсотків становить 96,9%, а в формі коефіцієнтів — 0,969. Це означає, що в 1996 р. порівняно з 1994 р. врожайність овочів в Чернігівській області становила проти 1994 р. 96,9 %, або вона порівняно з 1994 р. зросла в 0,969 рази.
Аналіз обчислених базисних темпів зростання для майже всіх років досліджуваного періоду свідчить про зниження рівня урожайності овочів в Чернігівській області з 1995 до 2006 року, окрім 1995, 1998, 2004 і 2006 років. Найбільше значення базисного темпу зростання, яке дорівнює 123,1%, відноситься до 1995 р., а найменше, яке становить 90,8% — до 2000 р.
Для перевірки взаємозв’язку ланцюгових і базисних коефіцієнтів зростання за формулою за даними табл. 15, щоб отримати добуток ланцюгових коефіцієнтів зростання активізуємо комірку F17 і введемо до неї формулу =ПРОИЗВЕД(F5:F16). Після натиснення клавіші ENTER в комірці F17 отримаємо = 0,804. Порівнюючи отримане число з числом, наведеним в комірці H16, яке являє собою , бачимо, що вони точно співпадають.
Для визначення показників відносних змін рівнів урожайності овочів порівняно з попереднім роком, тобто ланцюгових темпів приросту або зменшення треба в активізовану комірку H5 (табл. 15) згідно формули їх розрахунку ввести формулу =F5-100 або таку формулу = (В5:В16/В4)*100-100 і натиснути клавішу ENTER, а потім після появи у ній першого значення обчисленого показника активізувати її та здійснити копіювання записаної формули в діапазон комірок H6:H16. Значення показників ланцюгових відносних змін з’явиться в діапазоні комірок H6:H16.
Згідно даних табл. 15 показник відносних ланцюгових змін для 1995 р. становить 23,1%, а для 1996 р.— -21,3%. Це означає, що в 1995 р. порівняно з 1994 р. врожайність овочів в Чернігівській області збільшилася на 23,1%, а в 1996 р. вона порівняно з 1995 р. зменшилася на 21,3%. Аналогічні висновки можна зробити по решті обчислених в табл. 15 показників відносних ланцюгових змін.
Аналіз обчислених показників відносних ланцюгових змін для всіх років з 1995 до 2006 року підтверджує раніше зроблений на основі ланцюгових абсолютних показників висновок про нерівномірність зміни рівня врожайності овочів в Чернігівській області протягом усього досліджуваного періоду. Найбільше значення ланцюгового темпу приросту, яке дорівнює 23,4%, відноситься до 2006 р., а найбільше значення темпу зменшення, яке становить 21,3% — до 1996 р.
Для визначення показників відносних змін рівнів урожайності овочів порівняно з 1994 р., тобто базисних темпів приросту або зменшення треба в активізовану комірку І5 (табл. 15) згідно формули їх розрахунку ввести формулу =G5 – 100 або таку формулу = G5:G16-100 і натиснути клавішу ENTER, а потім після появи у ній першого значення обчисленого показника активізувати її та здійснити копіювання записаної формули в діапазон комірок I6:I16. Значення базисних показників відносних змін з’явиться в діапазоні комірок I6:I16.
Згідно даних табл. 15 для 1996 р. базисний темп зменшення становить 3,1%. Це означає, що в 1996 р. порівняно з 1994 р. врожайність овочів в Чернігівській області зменшилась на 3,1%.
Згідно даних табл. 15 для 1998 р. базисний темп збільшення становить 1,5%. Це означає, що в 1998 р. порівняно з 1994 р. врожайність овочів в Чернігівській області збільшилася на 1,5%.
Аналіз обчислених показників відносних базисних змін для майже всіх років досліджуваного періоду свідчить про зниження рівня урожайності овочів в Чернігівській області з 1995 до 2006 року, окрім 1995, 1998, 2004 і 2006- тут спостерігається збільшення. Найбільше значення базисного темпу зменшення, яке дорівнює 9,2%, відноситься до 2000 р., а набільший приріст, який становить 23,1% — до 1995 р.
Для розрахунку абсолютного значення 1% приросту за формулою потрібно згідно цієї формули в активізовану комірку J5 (табл. 15) ввести формулу =В4/100 або формулу =В4:В15/100 і натиснути клавішу ENTER, а потім після появи у ній першого значення обчисленого показника активізувати її та здійснити копіювання записаної формули в діапазон комірок J6:J16. Значення показників абсолютного значення 1% приросту з’явиться в діапазоні комірок J5:J16.
За даними табл. 15 абсолютне значення 1% приросту для 1995 року дорівнює 1,3. Це означає, що абсолютне значення 1% приросту рівня врожайності овочів Чернігівської області в 1995 становило 1,3 ц/га.
Аналіз показників абсолютного значення 1% приросту за всі роки досліджуваного періоду свідчить про помітні їх коливання протягом усього досліджуваного періоду і це ще раз є підтвердженням раніше зробленого висновку про нерівномірність зміни рівня врожайності овочів в Чернігівській області з 1994 до 2006 роки.
Обчислимо середні показники ряду динаміки врожайності овочів в Чернігівській області за період 1994 — 2006 рр. Так як досліджуваний ряд динаміки є періодичним, то середньорічний рівень врожайності овочів має обчислюватись за формулою середньої арифметичної простої, тобто . Звідси випливає, що для визначення середньорічного рівня врожайності овочів можна застосувати вбудовану статистичну функцію СРЗНАЧ. Для цього в активізовану комірку І18 (табл. 15) вводимо таку формулу =СРЗНАЧ(В4:В16) і натискуємо клавішу ENTER. Значення 11,9 отримано в комірці І18.
Отже, середньорічна врожайність овочів в Чернігівській області за період 1994 — 2006 рр. становила 131,1 ц/га.
Для визначення середньорiчної абсолютної змiни врожайності овочів в Чернігівській області за період 1994 — 2006 рр. за формулою використовуємо вбудовану функцію СРЗНАЧ. Для цього в активізовану комірку J19 (табл. 15) вводимо формулу =СРЗНАЧ(С5:С16). Після натиснення клавіші ENTER в комірці J19 отримано значення 2,33, тобто за період 1994 – 2006 рр. урожайність овочів в Чернігівській області в середньому щорічно збільшувався на 2,33 ц/га.
Щоб визначити середньорiчну абсолютну змiну врожайності овочів за формулою потрібно в активізовану комірку ввести формулу =(B16-B4)/12 і натиснути кнопку ENTER.
Для обчислення середньорічного коефіцієнта зростання за формулою
потрібно згідно цієї формули в активізовану комірку К18 (табл. 15) ввести формулу =(В16/B4)^(1/12). Після натиснення клавіші ENTER у комірці К18 отримаємо значення середньорічного коефіцієнту зростання 1,016, тобто врожайність овочів в Чернігівській області за період 1994 — 2006 рр. у середньому щорічно зростала в 1,016 рази. Звідси, середньорічний темп зростання врожайності соняшнику 101,6 %, а середньорічний темп зменшення становить , тобто врожайність овочів в Чернігівській області з 1994 до 2006 року в середньому щорічно збільшувалася на 1,6 %.
Зазначимо, що середньорічний темп зростання врожайності овочів можна також визначити за допомогою вбудованої статистичної функції СРГЕОМ. Для цього потрібно в активізовану комірку І20 (табл. 15) ввести формулу = СРГЕОМ(F5:F16)*100. Після натиснення клавіші ENTER в комірці І20 отримаємо значення середньорічного темпу зростання 101,6% , а в комірці J20 за формулою І20-100 — значення середньорічний темп зменшення .
Побудуємо багатостовпчикові діаграми, які дозволяють наочно і виразно відобразити зміну в часі рівнів урожайності овочів в Чернігівській області з 1994 до 2006 року. Зазначимо, що багатостовпчикові діаграми засобами програми Excel можна побудувати двома способами: з використанням Мастера диаграмм [5, с. 334 — 335] і способом перебудови з використанням функціональної клавіші F11, який зараз розглядатимемо.
Для побудови багатостовпчикових діаграм і відображення зміни в часі рівнів урожайності овочів в Чернігівській області потрібно послідовно виконати такі дії:
1. Ввести вихідну інформацію, щодо періодичного ряду динаміки врожайності овочів в Чернігівській області за 1994 — 2006 рр., як це показано в табл. 16, причому комірка А1 має бути незаповненою, тобто вона не повинна містити слово “Рік”. Це слово потрібно набрати безпосередньо перед друкуванням діаграми.
2. Виділити діапазон комірок, які містять відповідно цифри років і рівнів ряду динаміки врожайності овочів, в нашому прикладі А1:N2, та натиснути клавішу F11, або здійснити клавішну комбінацію ALT+F1.
3. Вибрати команду Диаграмма, Размещение і у виниклому діалоговому вікні Размещение диаграммы вибрати перемикач имеющемся і виконати 1КЛ на командній кнопці ОК.
4. Після появи вбудованої діаграми за допомогою процедури ТРАНС помістити її в потрібному місці робочого аркуша та змінити її розміри.
5. Вилучити рамку і горизонтальну координатну сітку, для цього виділити їх і натиснути клавішу DELETE.
6. Вибрати команду Диаграмма, Параметры диаграммы і після появи діалогового вікна Параметры диаграммы — вкладку Заголовки і в ній в полі введення Ось Х(категорий) набрати слово “Рік”, а в полі введення Ось Y(категорий) — назву рівнів ряду динаміки з їх одиницями виміру, а потім вибрати вкладку Подписи данных і в ній в групі Подписи значений установити перемикач значение і виконати 1КЛ на командній кнопці ОК.
7. Виконати 2КЛ на будь-якому стовпчику і після появи діалогового вікна Формат рядов данных вибрати вкладку Параметры і в ній в полі з лічильниками Ширина зазора ввести число нуль, щоб стовпчики діаграми були зімкнуті один з другим, а потім вибрати вкладку Вид і в ній — команду Способы заливки і після появи діалогового вікна Способы заливки вибрати вкладку Узор, де з розкривального списку Штриховка (з палітри кольорів), — чорний колір, а потім в групі Узор, яка містить різні види штриховки, вибрати один із видів штриховки. Після послідовно два рази виконати 1КЛ на командній кнопці ОК, щоб закрити діалогові вікна Способы заливки і Формат рядов данных.
8. Вибрати команду Диаграмма,Тип диаграммы і після появи діалогового вікна Тип диаграммы в групі Вид вкладки Стандартные — другий вид типу діаграм Гистограмма.
9. Виконати 2КЛ на маркері з позначенням рівнів ряду динаміки будь-якого стовпчика і після появи діалогового вікна Формат подписи данных вибрати вкладку Вид і в ній в полі з палітрою кольорів — білий колір і виконати 1КЛ на командній кнопці ОК.
Результат побудови багатостовпчикової діаграми, яка відображає динаміку врожайності овочів в Чернігівській області наведено на рис. 21.
Слiд вiдмiтити, що багатостовпчиковi та багатострічкові дiаграми можуть бути використанi не тiльки для графiчного зображення рiвнiв ряду динаміки, а й також для обчислених на їх основi статистичних показникiв, якi характеризують швидкiсть розвитку дослiджуваного явища, зокрема абсолютних приростiв, абсолютного значення одного процента приросту i середнiх їх значень. Зображення цих показників за допомогою стовпчикових і багатострічкових діаграм наочне й дуже виразне.
Техніка побудови багатостовпчикової діаграми, яка відображає абсолютну щорічну зміну рівнів урожайності овочів в Чернігівській області (ланцюгові абсолютні показники динаміки), аналогічна техніці побудови багатостовпчикової діаграми за рівнями ряду динаміки, наведеної на рис. 21, з тією лише різницею, що спочатку потрібно виділити несуміжний блок комірок за допомогою клавіші CTRL, тобто виділити блок комірок А1:N1, а потім натиснути клавішу CTRL і, утримуючи її, виділити блок комірок А3:N3, після чого відпустити клавішу CTRL, і не виконувати пп. 8 і 9, а після побудови багатостовпчикової діаграми абсолютних ланцюгових показників, щоб розмістити цифри років внизу діаграми потрібно виконати 2КЛ на осі абсцис і після появи діалогового вікна Формат оси вибрати вкладку Вид і в ній в групі Метки делений — перемикач внизу і виконати 1КЛ на командній кнопці ОК.
Результат побудови багатостовпчикової діаграми, яка відображає абсолютні щорічні зміни врожайності овочів в Чернігівській області за 1994 — 2006 рр. показано на рис. 22.
Рис. 21 і 22 дозволяють легко і швидко дійти до таких висновків: по-перше, що врожайність овочів в Чернігівській області з 1994 до 2006 року змінюється із року в рік; по-друге, в окремі роки спостерігається збільшення врожайності соняшнику, а в окремі — зниження; по-третє, як збільшення врожайності, так і його зменшення відбувається нерівномірно, по- четверте, найбільший рівень врожайності овочів 160 ц/га спостерігався в 1995 р., а найменший 118 ц/га — в 2000 р.
Зазначимо, що при побудовi багатострічкових дiаграм виконуються такi самi процедури, як i при побудовi багатостовпчикових, за винятком того, що при виконанні п. 8 в списку Тип вкладки Стандартные вибирається тип стрiчкової дiаграми Линейчатая (Стрічкова), а в групі Вид другий її вид при побудові першої діаграми (рис. 21) і перший її вид при побудові другої діаграми (рис.22).
Зазначимо також, що в тих випадках, коли побудована діаграма містить на шкалі ординат мінімальне і максимальне числові позначення, які дещо перевищують мінімальний і максимальний рівень вихідних даних, то для їх заміни потрібно виконати 2КЛ на осi ординат i потiм у щойно вiдкритому дiалоговому вiкнi Формат оси слід вибрати вкладку Шкала і в групі Шкала по оси Y(Значений) у вiдповiдних її полях введення для осi ординат задати відповідні числовi значення.
Обчислення плинних середніх засобами програми Excel можна здійснити двома способами: з використанням вбудованої статистичної функції СРЗНАЧ і за допомогою засобів діалогового вікна Скользящее среднее, яке належить до надбудови Пакета анализа.
Щоб обчислити, наприклад, трирічні плинні середні за допомогою функції СРЗНАЧ за даними ряду динаміки, поданого в табл. 17, треба ввести в активізовану комірку С5 таку формулу: =СРЗНАЧ (В4: В6) і натиснути на клавішу ENTER, а потім скопіювати її в комірки В6:В15.
Щоб обчислити плинні середні за допомогою засобів діалогового вікна Скользящее среднее треба виконати наступне:
1 Вибрати команду Сервис, Анализ данных і в щойно відкритому діалоговому вікні аналогічної назви в списку Інструменты анализа — інструмент Скользящее среднее і виконати 1ЛК на кнопці ОК. З'явиться діалогове вікно Скользящее среднее, яке показано в табл. 17.
2. Після появи діалогового вікна Скользящее среднее в групі Входные данные в полі введення Входной интервал ввести за допомогою клавіатури діапазон В4:В16 (табл. 17) або виділити його за допомогою миші в робочому аркуші з використанням кнопок стулення діалогового вікна, а в полі введення Интервал ввести число, яке вказує число років періоду згладжування, наприклад, для обчислення п’ятирічних плинних середніх число 5.
3. У групі Параметры вывода в полі введення Выходной интервал ввести адресу комірки, яка має бути початком виведення обчислених значень плинних середніх, у даному випадку D4, або просто виконати на ній 1ЛК, а потім виконати 1ЛК на кнопці ОК.
Значення обчислених плинних середніх виводяться, починаючи із символів #Н/Д, тому що при розрахунку плинної середньої декілька перших рівнів відсутніх і повертає замість них ці символи. В табл. 17 подано результат обчислення засобами діалогового вікна Скользящее среднее трирічних і п’ятирічних плинних середніх для наведеного в ній ряду динаміки.
Програма Excel дозволяє не тільки здійснити розрахунок різних за періодами згладжування плинних середніх, але й графічно відобразити їх за допомогою лінійної діаграми, без їхнього попереднього розрахунку, причому на одній лінійній діаграмі відобразити одночасно вихідний фактичний ряд динаміки та згладжений за методом плинної середньої, що дає можливість зробити порівняння двох зображуваних їх статистичних кривих і скласти певне міркування щодо характеру основної тенденції досліджуваного явища.
Щоб здійснити за допомогою Excel побудову лінійної діаграми за фактичними рівнями ряду динаміки слід виконати такі самі дії, що і при побудові лінійної діаграми за даними табл. 13 для зображення теоретичної лінії регресії парного лінійного рівняння регресії, наведеної на рис. 16, з тією лише різницею, що в комірки робочого аркуша потрібно ввести вихідну інформацію, як це показано в табл. 17, і виділити діапазон В4:В16, а для відображення на одній і тій самій діаграмі і згладженого рядів, складених на основі розрахованих плинних середніх при виконанні п. 2 потрібно в групі Построение линии тренда (аппроксимация и сглаживание) вкладки Тип вибрати тип функції Линейная фильтрация, а в полі з лічильником Точки задати потрібний період згладжування, а п. 3 в групі Название аппроксимирующей (сглаженной) кривой набрати за допомогою клавіатури вираз щодо обраного виду плинної середньої.
Лінійна діаграма, подана на рис. 23, містить статистичні криві, одна з яких відбиває фактичні рівні зображуваного ряду динаміки, а інші — згладжені його рівні за допомогою відповідної плинної середньої, назва й умовне позначення яких містить легенда діаграми.
На рис. 23 статистична крива, побудована за фактичними рівнями ряду динаміки показує відносно значні коливання врожайності овочів в Чернігівській області, впродовж 13 років вона 6 разів зменшувалася і 7 разів збільшувалася в порівнянні з попереднім роком. Ці часті коливання врожайності соняшнику не дозволяють зробити певний висновок щодо тенденції її розвитку. Навпаки, плавний вигляд статистичних кривих, побудованих за плинними середніми свідчить про незначне збільшення врожайності овочів протягом досліджуваного періоду, тобто про наявність тенденції до збільшення врожайності овочів в Чернігівській області.
Загальний вигляд статистичних кривих, побудованих за різними плинними середніми, дозволяє зробити припущення, що для аналітичного описання основної тенденцiї динаміки врожайності овочів в Чернігівській області за період 1994 — 2006 рр. можна використати рівняння прямої лінії, тобто Yt = a +bt.
Побудуємо засобами Excel лінійну діаграму, зобразивши на ній фактичні рівні ряду динаміки урожайності овочів і теоретичні рівні за прямою, а також рівняння лінійного тренду та значення коефіцієнта детермінації (рис. 24).
Техніка побудови лінійної діаграми на рис. 24 така сама, як і лінійної діаграми, наведеної на рис. 23, з тією тільки різницею, що при виконанні п. 2 потрібно в групі Построение линии тренда (аппроксимация и сглаживание) вкладки Тип вибрати вид теоретичної кривої Линейная, а після — вкладку Параметры і в ній для нанесення на легенду діаграми назви теоретичної лінії тренду в полі введення другое групи Название аппроксимирующей (сглаженной) кривой набрати вираз “Теоретичні рівні динаміки за прямою”, а для записування на діаграмі рівняння лінійного тренду встановити прапорець показывать уравнение на диаграмме, а для записування значення коефіцієнта детермінації — прапорець поместить на диаграмму величину достоверности аппроксимации (R^2) і виконати 1КЛ на командній кнопці ОК.
Визначимо параметри лінійного тренду способом складання і розв’язування системи нормальних рівнянь. У табл. 18 наведені вихідні дані і розрахунки всіх необхідних сум для розрахунку параметрів: n=13; Σу = 1704; Σt = 91; Σуt =11972; Σt2 =819. На їх основі складена система нормальних рівнянь з двома невідомими. Для її розв’язування поділимо кожне рівняння на коефіцієнт при параметрі а, одержимо нову систему рівнянь. Ця система рівнянь, а також розрахунки, пов’язані з її складанням наведено в табл. 18 у комірках D17 — D20. Для знаходження параметра b активізуємо комірку D21 і вводимо формулу = (D20-D18)/(D19-D17), а для знаходження параметра активізуємо комірку С22 і вводимо формулу = D20-D19*D21 та натиснемо клавішу ENTER. Обчислені значення параметра b = 0,2418 і параметра а =129,3846 отримаємо відповідно в комірках D21 і С22.
Обчислимо параметри лінійного трендового рівняння а і b з використанням готових формул їх розрахунку:
Для цього активізуймо комірки H11 і H12 і введемо до них зазначені формули і натиснемо клавішу ENTER (табл.18).
Як вже зазначалося раніше, застосування вбудованої статистичної функції ЛИНЕЙН дозволяє не тільки визначити параметри рівняння, але й низку інших статистичних характеристик для детального вивчення обраного трендового рівняння. У табл. 18 у діапазоні E17:F21 показано результати розрахунків за допомогою функції ЛИНЕЙН, яка введена застосуванням формули масиву: =ЛИНЕЙН(В3:В15;С3:С15;1;1).
Обчислені параметри а і b лінійного рівняння тренду знаходяться в першому рядку діапазону E17:F21 відповідно в комірках F17 і E17.
Порівнюючи результати обчислення параметрів а і b лінійного рівняння тренду за різними способами їх розрахунку, бачимо, що вони точно співпадають. Це свідчить про те, що вони визначені правильно.
Отже, лінійне рівняння тренду матиме такий вигляд:
Yt = 129,385+0,2418t.
де Yt — теоретичні значення рівнів ряду динаміки врожайності овочів.
Параметр b =0,24 ц/га показує, що за період 1994 — 2006 рр. у Чернігівській області врожайність овочів в середньому щорічно збільшувалась на 0,24 ц/га. Параметр а = 129,38 ц/га характеризує значення теоретичного рівня врожайності при t= 0. У нашому випадку це рівень 1993 р., тому що програма Excel умовну нумерацію періодів або моментів часу починає з t = 1, тобто t = 1, 2, 3, · · ·, n.
Підставляючи в рівняння Yt = 129,385+0,2418t. значення t, починаючи з 1-го і кінчаючи 13-им, матимемо теоретичні рівні врожайності овочів, які відображатимуть лінійну основну тенденцію врожайності овочів в Чернігівскій області за період 1994 — 2006 рр. (див. останню графу табл. 18). Щоб виконати ці дії засобами Excel потрібно в активізовану комірку F3 ввести формулу = Yt = 129,385+0,2418*1 і натиснути клавішу ENTER та скопіювати цю формулу до комірок F4:F15.
Значення коефіцієнта детермінації, обчислене за допомогою графічного методу (рис.24) і функції ЛИНЕЙН (комірку Е19 табл. 18) дорівнює 0,0049.
Отже, 0,5 % щорічної варіації врожайності овочів в Чернігівскій області лінійно пов’язано з варіацією факторів, що визначають основну тенденцію рівня врожайності овочів, а 95,5% припадає на інші випадкові фактори.
Здійснимо перевірку істотності обчисленого коефіцієнта детермінації за допомогою критичних значень і F-критерія при рівнях істотності * = 0,05 і * = 0,01.
За даними табл. 18 фактичне значення коефіцієнта детермінації факт дорівнює 0,0049 (комірка Е19), а фактичне значення F-критерія (комірка Е20) дорівнює Fфакт = 0,0542.
Згідно таблиць додатку 5 критичне значення коефіцієнта детермінації при ступенях вільності k1 = m − 1=2 − 1 =1 і k2 = n− m = 13 − 2 = 11 для рівня істотності *=0,05 становить 0,95(1,11) = 0,306, а для рівня істотності *= 0,0 1 — 0,99(1,11) = 0,467. За таблицями додатку 4 критичне значення F-критерія для рівня істотності *=0,05 становить F0,95(1,11) = 4,84, а для рівня істотності *= 0,0 1— F0,99(1,11) = 9,65.
Для перевірки визначимо критичні значення F-критерія за допомогою вбудованої статистичної функції FРАСПОБР. Для цього активізуємо комірки D23 і H23 табл. 18 і вводимо відповідно для визначення критичного значення F-критерія при ступенях вільності k1 = 1 і k2 = 11 для рівня істотності *=0,05 в комірку D23 формулу =FРАСПОБР(0,05;1;11) а для рівня істотності *= 0,01 в комірку H23 — формулу = FРАСПОБР(0,01;1;11). Після натиснення клавіші ENTER в комірках D23 і H23 табл. 14 отримуємо такі самі величини критичних значень F-критерія, як і при їх визначенні за таблицями додатку 4.
Зіставлення фактичних значень F-критерія і з критичними дає підстави з імовірністю 0,95 стверджувати про вірогідність обчисленого за лінійним рівнянням тренду, проте з імовірністю 0,99 цей висновок не доведено.
Для перевірки придатності лінійного рівняння тренду для описання основної тенденції динаміки врожайності овочів в Чернігівскій області за період 1994 — 2006 рр. побудуємо лінійну діаграму за фактичними рівнями відповідного ряду динаміки і помістимо на ній теоретичні криві, побудовані за пропонованими програмою Excel різними трендовими рівняннями з показом відповідних їм параметрів аналітичних рівнянь і значень коефіцієнтів детермінації R2 . Для цього слід виконати такі самі процедури, як і при зображенні теоретичної кривої лінійного рівняння тренду з показом аналітичного рівняння лінійного тренду і коефіцієнта детермінації R2 на діаграмі, наведеної на рис. 24 з тією лише різницею, що при виконанні п. 2 потрібно вибрати відповідний тип функції, а п. 3 в групі Название аппроксимирующей (сглаженной) кривой набрати вираз “Теоретичні рівні динаміки за …(вказати тип обраної функції)”.
Результати роботи по нанесенню на побудовану за фактичними рівнями ряду динаміки лінійну діаграму та теоретичних кривих, обчислених за різними трендовими рівняннями і відповідних їм аналітичних трендових рівнянь і значень коефіцієнтів детермінації R2 показано на рис. 25.
Порівняння величин наведених на рис. 25 коефіцієнтів детермінації R2 дає змогу зробити висновок, що найбільш адекватно зміну рівнів урожайності овочів в Чернігівській області за період 1994 — 2006 рр. характеризує парабола другого порядку. Цей висновок підтверджується порівнянням величин середніх квадратичних похибок Sε, обчислених за лінійним і параболічним рівняннями тренду за допомогою функції ЛИНЕЙН застосуванням відповідно в активізованих діапазонах E17:F21 табл. 18 і D17:F21 табл. 19 формул масиву: =ЛИНЕЙН(В3:В15;С3:С15;1;1) і =ЛИНЕЙН(В3:В15;С3:С15^{1;2};;1).
Обчислена за лінійним рівнянням тренду середня квадратична похибка дорівнює відповідно 14,0139 (див. табл. 18 комірка F19), а за параболічним рівнянням тренду —10,5908 (див. табл. 19 комірка Е21), тобто за параболічним рівнянням тренду вона менша.
Перевіримо адекватність лінійного і параболічного рівнянь тренду за допомогою середньої похибки апроксимації .
Для розрахунку середньої похибки апроксимації для лінійного тренду активізуємо комірку L23 в табл. 18 і введемо до неї формулу масиву =СУММ(ABS(В3:В15-F3:F15)/(B3:B15))/13*100. Після виконання клавішної комбінації CTRL+SHIFT+ENTER в комірці L23 в табл. 18 одержимо для лінійного тренду значення середньої похибки апроксимації 7,01%.
Для розрахунку середньої похибки апроксимації для параболічного рівняння тренду спочатку обчислимо теоретичні значення рівнів ряду динаміки врожайності овочів за обчисленим параболічним рівнянням тренду Yt= 154,5944 – 9,84216t + 0,72028t2 . Для цього введемо в активізовану комірку Е3 табл. 19 таку формулу =154,5944– 9,84216*С3+0,72028*С3^2) і натиснемо клавішу ENTER, а потім скопіюємо цю формулу до діапазону комірок Е4:Е15. Після появи значень теоретичних рівнів ряду динаміки врожайності соняшнику в діапазоні комірок Е3:Е15 в активізовану комірку M22 табл. 19 введемо таку формулу масиву =СУММ(ABS((В3:В15-E3:E15)/(B3:B15))/13*100. Після виконання клавішної комбінації CTRL+SHIFT+ENTER в комірці M22 табл. 19 одержимо для параболічного тренду значення середньої похибки апроксимації 4,91%.
Отже, і величина середньої похибки апроксимації свідчить про те, що найбільш адекватно зміну врожайності овочів за 1994 — 2006 рр. В Чернігівській області характеризує парабола 2-го порядку.
Рівняння параболічного тренду Yt= 154,5944 – 9,84216t + 0,72028t2 показує , що за період 1994 — 2006 рр. в Чернігівській області врожайність овочів в середньому щорічно зменшувалась на 9,8 ц/га з щорічним середнім прискоренням на 1,44 ц/га (2*0,72).
Зазначимо також, що обчислене трендове рівняння параболи 2-го порядку має мінімум, тому що параметр b1 = –9,84216 0, а параметр b2 = 0,72028 0. Використовуючи формулу визначення екстремуму за часом t, а саме одержимо Це означає, що мінімальний теоретичний рівень врожайності слід очікувати при t = 7, що відповідає 2000 року. Дійсно, аналізуючи наведені в табл. 19 значення теоретичних рівнів врожайності овочів, розраховані за розглядуваним параболічним рівнянням тренду, можна констатувати, що найменше їх значення, яке дорівнює 120,993 ц/га, якраз спостерігається в цьому році.
На основі обраного параболічного рівняння тренду врожайності овочів проведемо розрахунок прогнозних значень урожайності овочів на 2007 і 2008 рр. методом екстраполяції.
На основі рівняння тренду Yt= 154,5944 – 9,84216t + 0,72028t2, яке характеризує зміну врожайності овочів за період 1994 — 2006 рр. в Чернігівській області, визначимо точкові прогнозні значення на 2007 і 2008 рр.:
врожайності овочів Y2007 = Yt= 154,5944 – 9,84216*14 + 0,72028*142=158 ц/га;
врожайності овочів Y2008 = Yt= 154,5944 – 9,84216*15 + 0,72028*152 = 169 ц/га
Зазначимо, що для розрахунку точкових прогнозних значень урожайності овочів на 2007 і 2008 рр. за допомогою Excel потрібно значення t доповнити значеннями періоду прогнозу, тобто ввести в комірки С16 і С17 табл. 19 числа 14 і 15 і виділивши комірку Е16 за допомогою маркера заповнення і процедури ТРАНС здійснити копіювання формули, яку містить ця комірка, в комірки Е16 і Е17.
На основі точкових прогнозних значень урожайності овочів визначимо верхні та нижні довірчі межі прогнозних значень врожайності овочів в Чернігівській області на 2007 і 2008 роки з імовірністю 0,90, використовуючи формулу: Yt+l ± *.
Величина середньої квадратичної похибки для параболічного тренду обчислена за допомогою функції ЛИНЕЙН і дорівнює 10,5908(див. табл. 19 комірка Е21). Згідно додатку 8 для ймовірності 0,9 і при n = 13 значення K* для L = 1 і L = 2 відповідно становлять K* = 2,536 і K* = 2,965.
Підставивши необхідні дані в формулу Yl+k ± , знаходимо для 2007 р. довірчі межі прогнозного рівня для врожайності овочів 158 ц/га ± 10,59082,536, а для 2008 — 169 ц/га ± 10,59082,965.
Таким чином з імовірністю 0,90 можна стверджувати, що в 2007 р. врожайність овочів в Чернігівській області буде щонайменше 131,14 ц/га і не перевищить 184,86 ц/га, а в 2008 р. — не менше 137,6 ц/га, але і не більше 200,4 ц/га.