Честотен анализ в MS Excel. Изчисляване на подвижна средна с помощта на Excel
Пълзящата средна е статична функция, която улеснява получаването на резултати за различни задачи. Например задачата за получаване на прогноза.
Пълзящата средна ви позволява да промените абсолютните динамични стойности на редица клетки до средни аритметични, като използвате изглаждане на данни. Често се използва при изчисления на икономически борси, в търговията и други области.
Как да го приложим в Excel - нека да разгледаме всичко стъпка по стъпка.
Този метод в Excel се прилага чрез използването на функцията на пакета за анализ и директно чрез самата вградена функция, която се нарича "AVERAGE".
Помислете за първия начин за използване на метода на пълзящата средна чрез пакета за анализ:
1. Пакетът за анализ не е включен в стандартния набор от функции, така че трябва да бъде активиран. Това става през настройките на документа - "Файл" - "Опции" - "Добавки". В долната част на диалоговия прозорец има раздел Добавки. Тя е тази, от която се нуждаем.
Включете "Пакета за анализ" и запазете. Цялата функционалност е добавена към "Данни" и е напълно готова за използване.
2. За да разберем как работи методът на пълзящата средна, нека се опитаме да получим данни за 12 месеца въз основа на тези, които вече сме получили за 11 предишни - ще направим прогноза. Попълваме първоначалните стойности на таблицата.
3. В предварително добавената функционалност "Анализ на данни" на работния панел от параметрите на добавката на документа изберете желаната функция "Пълзяща средна" и щракнете върху "ОК".
4. В появилия се диалогов прозорец попълнете всички стойности. "Интервал на въвеждане" - всички наши показатели за 11 месеца без желаната клетка. "Интервал" - индикатор за изглаждане, по отношение на нашите първоначални данни ще зададем "3". "Изходен интервал" - клетки, в които получените данни ще бъдат показани по метода на пълзящата средна. Включете „Стандартни грешки“ и получете всички желани стойности.
5. За да получим по-точен резултат, ще извършим многократно изглаждане с интервал от "2" единици. Посочете нов "Изходен интервал" и получете нови данни.
6. Въз основа на получените нови данни можете да направите прогнозен индикатор за желания месец, като изчислите метода на пълзящата средна за последен период. Ние се основаваме на факта, че колкото по-малка е стандартната грешка, толкова по-точни са данните.
Помислете за втория начин - функцията AVERAGE:
1. Ако пакетът за анализ прави почти всички операции автоматизирани, тогава използването на функцията AVERAGE изисква използването на няколко стандартни функции на Excel. Използваме едни и същи първоначални данни за 11 месеца. Нека вмъкнем функция.
2. В диалоговия прозорец на съветника за функции отидете в раздела "Статистически" и изберете желаната от нас функция "СРЕДНО".
3. Функцията "СРЕДНО" има много прост синтаксис - "= СРЕДНО (число1; число2; число3; ...). Посочете в аргумента "номер 1" диапазона за "Януари" и "Февруари".
4. Изчислете индикатора за оставащите периоди от време, като плъзнете маркера за запълване на формулата надолу в колоната.
5. Ще извършим същата операция, но с разлика в периода от 3 месеца.
6. Но кои данни са верни в нашия случай, на база два или три месеца? За да получим правилния отговор, прилагаме изчислението на абсолютното отклонение, средния квадрат и няколко други показателя. пер абсолютно отклонениеотговаря на функцията ABS.
В диалоговия прозорец на функцията посочваме разликата между дохода и подвижната средна за два месеца.
7. Попълнете колоната с маркера за запълване и изчислете „СРЕДНО“ за цялото време.
8. Нека извършим подобна операция, за да намерим абсолютното отклонение и средната стойност за период от три месеца.
9. Остават още няколко стъпки. Като начало изчисляваме относителното отклонение за два и три месеца, като търсим абсолютната стойност на разделянето на установеното отклонение в наличните първоначални данни, а също така намираме средната стойност на получените стойности.
Всички данни ще бъдат представени като процент.
10. За да получите крайния резултат от метода на пълзящата средна, остава да изчислите средната стандартно отклонениесъщо два и три месеца.
Желаното от нас стандартно отклонение ще бъде равно на корен квадратен от сбора на квадратите на разликите между първоначалните данни за приходите и данните за пълзящата средна, разделен на периода от време.
Нека напишем нашата функция "ROOT(SUMQDIFF(B6:B12;C6:C12)/COUNT(B6:B12))", попълваме колоните с маркери за запълване и намираме средната стойност от получените данни.
11. Нека анализираме получените данни и можем уверено да заключим, че изглаждането за два месеца даде най-верните крайни показатели.
препис
1 Прогнозиране в Excel с помощта на метода на подвижната средна д-р. мат. науки, професор Гавриленко В.В. асистент Парохненко Л.М. (Национален транспортен университет) Теоретичен справочник. При моделирането на различни икономически процеси на практика широко се използват нарастващите възможности на съвременните компютърни технологии, както и ефективни начинипрогнозиране. Така че, за да разработите прогнози в пакета Excel, можете да използвате инструменти като: изграждане на регресии; експоненциално изглаждане; пълзяща средна. В тази статия процесът на разработване на прогноза с помощта на 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 може да се осъществи чрез: въвеждане на подходяща формула в клетките, например с помощта на вградената функция AVERAGE(); използване на инструмента Moving Average на добавката Analysis ToolPak; добавяне на тренд линия към диаграмата, изградена върху оригиналния времеви ред, базиран на метода на линейно филтриране.
3 Задача. Като вземете предвид данните за месечната печалба на компанията за 11 месеца на текущата година, представени в таблицата, направете прогноза за печалбата на компанията за 12-ия месец. Фиг. 1. Таблица на стойностите на печалбата на компанията по месеци Решение на проблема В бъдеще, при решаването на формулирания проблем, за удобство на представяне на резултатите от изчисленията ще се използват работни листове Z1, Z2, Z3, Z4: лист Z1 за формирането на изгладени времеви редове на базата на метода на пълзящата средна с помощта на функцията AVERAGE() и изчисляване на средните им отклонения от оригиналния времеви ред; лист Z2 за прилагане на процеса на изглаждане на оригиналния времеви ред с помощта на инструмента Moving Average на добавката Analysis Package; лист Z3 за визуално представяне на изгладените времеви редове, изградени с помощта на тренд линия от тип Линейно филтриране въз основа на диаграма за оригиналния времеви ред; лист Z4 за сравнителен анализрезултати, получени с помощта на инструментите, избрани по-горе: въз основа на оригиналния времеви ред, изгладени времеви редове от 2-месечни подвижни средни стойности се изграждат с помощта на функцията AVERAGE(), инструмента Moving Average на добавката „Пакет за анализ“ и линия на тренд от тип Линеен филтър. Приложението на вградената функция AVERAGE() Работният лист на 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:C16; D8:D16; E9:E16. Първо изграждаме поредица от движещи се средни стойности за два месеца: в клетка C7 въвеждаме формулата =AVERAGE(B5:B6) и с помощта на маркера за запълване я копираме в диапазона от клетки C8:C16, като в резултат на което диапазонът от клетки C7:C16 се запълва с изчислените показатели 2- x месечна пълзяща средна. По същия начин се изграждат серии от стойности на 3-та и 4-та месечна пълзяща средна: в клетка D8 въведете формулата = AVERAGE (B5: B7) и с помощта на маркера за запълване я копирайте в диапазона от клетки D9: D16, което води до диапазон от клетки D8:D16, запълнен с индикатори на 3-месечна пълзяща средна; въведете формулата =AVERAGE(B5:B8) в клетка E9 и използвайте маркера за запълване, за да я копирате в диапазона от клетки 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 се въвежда формулата: =SQRT(SUMQDIFF(B9:B15,C9:C15)/COUNT(B9:B15)), в клетка C41 се въвежда формулата: = SQRT(SUMQDIFF(B9:B15,D9:D15)/COUNT(B9:B15)), въведете формулата в клетка D41: =SQRT(SUMSDIFF(B9:B15,E9:E15) /БРОЙ(B9:B15)). Трябва да се отбележи, че за да се извърши сравнителен анализ на грешките за 2, 3 и 4-месечната плъзгаща се средна, беше взет същия брой наблюдения. Заключение. От горните таблици следва, че за да се изгладят първоначалните времеви редове и да се направи прогноза за тенденцията в промяната на печалбата на компанията, моделът на 2-месечната пълзяща средна е за предпочитане, тъй като той по-точно реагира на колебанията в първоначалната времеви редове и има по-малки грешки при прогнозиране (абсолютни, относителни, средноквадратични). Прогнозната стойност на печалбата на компанията за 12-ия месец е 8325 хиляди UAH. Инструментът Moving Average на добавката "Analysis Package" Изпълнението на процеса на изглаждане и прогнозиране с помощта на метода на пълзящата средна в средата на Excel може да се извърши с помощта на инструмента Moving Average на добавката "Analysis Package" според към следната процедура: 1. В работен лист Z2 създайте таблица, в която обхватът от клетки A5: B15 се попълва с данни от времеви серии от оригиналната таблица (фиг. 1). 2. Диапазонът от клетки C5:С15 се попълва със стойностите на изгладената серия, получена от данните за предходните 2 месеца с помощта на инструмента Moving Average на добавката „Пакет за анализ“, а диапазонът от клетки D5 :D15 се запълва със стойностите на неговите стандартни грешки. 3. По същия начин диапазоните от клетки E5:E15 и F5:F15 се запълват със стойностите на изгладената серия, получена от данните за предходните 3 месеца, и съответно стойностите на нейните стандартни грешки. Технологията за конструиране на поредица от стойности, например за 2-месечна подвижна средна с помощта на инструмента Moving Average на добавката „Пакет за анализ“, е следната: Изберете командата Data Analysis от менюто Tools. Ще се появи диалоговият прозорец Data Analysis (Фигура 11), който съдържа всички налични инструменти за анализ на данни. Изберете инструмента Moving Average от списъка и щракнете върху бутона OK. Ще се появи диалоговият прозорец Moving Average (Фигура 12). В полето Интервал на въвеждане посочете диапазона от изходни данни в работния лист на Excel, тоест диапазона от клетки B5:B15.
9 Фиг.11. Диалогов прозорец Data Analysis Фиг.12. Диалоговият прозорец Пълзяща средна В полето Интервал въведете броя месеци, които са включени в изчислението на подвижната средна, т.е. числото 2 (тъй като в този случай подвижната средна стойност се основава на данните от предходните 2 месеца ). В полето за въвеждане на интервал на извеждане въведете диапазона от клетки, в които ще се показват резултатите, т.е. диапазона от клетки C5:C15. Когато поставите отметка в квадратчетата в полетата Graph Output и Standard Errors, автоматично ще бъде създадена диаграма въз основа на резултатите от анализа и към резултата ще бъде добавена колона, съдържаща статистическа оценкагрешки. Полето Етикети трябва да бъде отметнато, ако първият ред (колона) във входния диапазон съдържа заглавия. Ако диапазонът на въвеждане не съдържа заглавки, квадратчето за отметка трябва да бъде изчистено. Щракваме върху бутона OK. По същия начин се конструира серия от стойности на 3-месечна пълзяща средна и нейните стандартни грешки. Фигура 13 показва таблица със стойности на 2- и 3-месечни подвижни средни и техните стандартни грешки, получени с помощта на инструмента Moving Average на добавката „Пакет за анализ“, а на фигури 14a, 14b, съдържанието на клетки от тази таблица, тоест използвани в процеса на решаване на формула.
10 Фиг.13. Изгладени серии и техните стандартни грешки, получени с помощта на инструмента Moving Average на добавката "Analysis Package" Фиг.14a. Съдържанието на клетките на таблицата Фиг. 13 (началото)
11 Фиг.14b. Съдържанието на клетките на таблицата Фиг.13 (продължение) Фиг.15. Графики на оригиналния времеви ред и изгладени времеви редове, изградени с помощта на инструмента Moving Average на добавката „Пакет за анализ“ Заключение: сравнение на стандартните грешки от диапазона от клетки D9:D15 със съответните стандартни грешки от диапазона от клетки F9 :F15 (фиг. 13) ни позволяват да четем модел 2 -x месечната пълзяща средна е за предпочитане за изглаждане и прогнозиране, тъй като се взема предвид във всички точки
12 от дадения времеви диапазон има по-малки стандартни грешки. Прогнозната стойност на печалбата на компанията за 12-ия месец ще бъде стойността, съдържаща се в клетка C15, тоест 8325 хиляди UAH. Изграждане на линии на тренд с помощта на метода на линейно филтриране За графичен анализ на данни на диаграма можете да използвате изграждането на линия на тренд въз основа на подвижни средни точки. Такава тренд линия ви позволява да изградите изгладена крива, чието графично представяне по-ясно показва съществуващия модел в развитието на данните. За оригиналната таблица със стойности (фиг. 2) прилагаме метода на линейно филтриране (или метода на подвижната средна стойност) и изграждаме линии на тенденция. Технологията за построяване на линия на тренд е следната: Използвайки данните от изходната таблица (фиг. 2), ще построим графика, като в диалоговия прозорец Chart Type изберем тип Point. По желание можете да промените външния вид на изградената графика и нейния маркер, тип линия, цвят и дебелина. За да направите това, отидете в режим на редактиране на получената графика, като щракнете двукратно с левия бутон на мишката върху начертаната графика. В появилия се диалогов прозорец Format Data Series задайте необходимите параметри за промяна на графиката и натиснете бутона OK. След това изберете тази серия от данни, като щракнете върху линията на графиката с десния бутон на мишката (изборът на серията ще бъде направен от черни квадратчета). В контекстното меню, което се показва, изберете елемента от менюто Добавяне на линия на тенденция. Или, след като изберете серията, като щракнете върху който и да е бутон на мишката, изберете командата Add Trendline от менюто Chart. На екрана ще се появи диалоговият прозорец Trend Line (фиг. 16). В раздела Тип изберете типа линия на тренда Линейно филтриране (пълзяща средна). Ако изберете тип линейно филтриране, трябва да въведете в полето Период броя периоди (точки), използвани за изчисляване на подвижната средна. Нека въведем числото 2 в това поле, т.к чертаем тренд линия за 2 месеца. Натискаме OK. По аналогия, ние процедираме, когато изграждаме тренд линия за 3 месеца, като въвеждаме числото 3 в полето Период. представени са конструираните графики на първоначалния времеви ред и линиите на тренда на 2- и 3-месечната пълзяща средна.
13 Фиг.16. Диалогов прозорец Trendline Изградените трендови линии могат да бъдат форматирани. За да направите това: изберете линията на тенденцията, като щракнете върху нея с мишката, след това щракнете с десния бутон и изберете елемента Форматиране на линията на тенденцията от контекстното меню, което се появява. появява се диалоговият прозорец Форматиране на тренд линия (фиг. 17), в който можете да зададете желания тип тренд: тип линия, цвят, дебелина; Можете да промените името на гладката крива, като отворите раздела Опции в същия диалогов прозорец. След като зададете необходимите параметри, щракнете върху OK.
14 Фиг. Фигура 17. Диалогов прозорец Формат на линията на тренда Обърнете внимание на следното: Тъй като методът на линейно филтриране се прилага чрез начертаване на линия на тренда върху диаграмата, неговият ефект може да се наблюдава визуално, но не е възможно да получите числени резултати на ваше разположение, тъй като те не са въведени в електронна таблица.
15 Фиг. Фиг. 18. Графики на първоначалните времеви редове и линиите на тенденцията на 2- и 3-месечната пълзяща средна Сравнение на инструментите пълзяща средна с помощта на функцията AVERAGE() и 2-месечната пълзяща средна на пакета за анализ. Нека начертаем оригиналния времеви ред и линиите на тенденцията на изгладения времеви ред.
16 Фиг. Фиг. 19. Таблица със стойности на 2-месечната пълзяща средна, получена с помощта на функцията AVERAGE() и пакета за анализ Фиг.20. Графики на оригиналния времеви ред, втората месечна плъзгаща се средна стойност, получена с помощта на функцията AVERAGE, инструмента Moving Average на добавката Analysis ToolPak с добавяне на линия на тренда от типа Линеен филтър
17 Сравняване на стойностите на подвижната средна в колона C, получена чрез директно въвеждане на формули в клетките на работния лист, със стойностите на подвижната средна в колона D, изчислена с помощта на инструмента Moving Average на Analysis ToolPak (Фигура 20 ), можете да видите, че стойностите на пълзящата средна пълзяща средна в колона C са изместени с една позиция надолу в сравнение с колона D. Този проблем може да бъде решен например по следния начин: след като стойностите на пълзящата средна са изчислени, всички тези стойности трябва да бъде избрано и преместено надолу с един ред на работния лист. Това действие ще ви позволи да свържете прогнозите точно с периодите, за които се отнасят. Въпреки това, ако квадратчето за отметка Show Graph в диалоговия прозорец Moving Average (Фигура 12) е отметнато, графиката ще постави прогнозните данни според данните от работния лист. Като преместите стойностите на работния лист с един ред надолу, трябва също така да редактирате диаграмата въз основа на прогнозните данни. Нека отбележим предимствата и недостатъците на изготвянето на прогноза с помощта на метода на пълзящата средна: Създаването на прогноза с помощта на инструмента на пълзящата средна е доста проста и сравнително точно отразява промените в основните показатели от предходния период. Понякога дори са по-ефективни при прогнозиране от методите, базирани на дългосрочни наблюдения. Простата пълзяща средна обаче е бърз, но не винаги точен начин за идентифициране на общи тенденции във времева серия. Когато правите прогнози за подвижна средна с помощта на добавката за пакет за анализ, прогнозата се генерира един период от време по-рано. Можете да начертаете диаграма, която използва данни от времеви редове, за да начертаете тренд линия на пълзяща средна, но диаграмата не показва действителните числени стойности на пълзящата средна. Освен това няма начин да промените местоположението на тренд линията на графиката. Правенето на прогнози въз основа на подвижна средна не води до прогноза, която надхвърля известните данни. Можете да преместите границата на прогнозата към бъдещето по времевата ос, като използвате една от статистическите функции регресионен анализ Excel пакет. Литература 1. Карлберг К. Бизнес анализ с използвайки Excel. К.: Диалектика, с. 2. Гавриленко В.В., Парохненко Л.М. Решаване на проблеми с приближаване с помощта на Excel // Компютри + програми, S N.V. Макарова, В.Я. Трофимец. Статистика в Excel: Урок. М.: Финанси и статистика, стр. 4. Ю.Н. Тюрин, А.А. Макаров. Анализ на данни на компютър / Ed. В.Е. Фигурнова. М: ИНФРА-М, стр.
Лабораторна работа 2 Тема: Технология на аналитичното моделиране в DSS. Технологии за анализ и прогнозиране, базирани на тенденции Цел: да се проучат възможностите и да се развие способността за използване на универсалния
Практическа работа 3.7. Използване на съветника за функции на MS Excel. Изграждане на диаграми Целта на работата. След като завършите тази работа, ще научите как да: въвеждате формули в клетките на таблицата; използвайте съветника за функции на MS Excel
Лабораторна работа 8. ИЗГРАЖДАНЕ НА ГРАФИКИ И ГРАФИКИ В EXCEL Целта на работата: да научите как да използвате средствата за графично показване на информация в средата на Excel, как да я форматирате и използвате
ПРОГНОЗИРАНЕ НА ОБЕМА НА ПРОДАЖБИТЕ НА БЕНЗИН ПО МЕТОДА НА ЕКСТРАПОЛАЦИЯ НА ТЕНДЕНЦИИ Пучкова В. С., Растеряев Н. В. Държава Дон Технически университет(DSTU) Ростов на Дон, Русия ПРОГНОЗИРАНЕ НА ОБЕМИ НА ПРОДАЖБИ
РЕШЕНИЕ НА ПРОБЛЕМИ НА ОПИСАТЕЛНАТА СТАТИСТИКА С ИЗПОЛЗВАНЕ НА ПАКЕТА ЗА АНАЛИЗ 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. Изграждане, форматиране и редактиране на диаграми, графики. ЦЕЛ НА УРОКА: да научите как да създавате, форматирате и редактирате диаграми, графики.
Изчертаване на функции и трендови линии. Волчков В.М., Стяжин В.Н. кафене Приложна математика, VolgGTU Урок 3 Има много специализирани компютърни програми, които ви позволяват да изграждате графики
Лабораторна работа 5. Обработка на експериментални данни в електронни таблици Задача 1. На първия работен лист от документа въведете изходните данни, съответстващи на варианта на задачата. Изграждане на графика
Лабораторни упражнения Microsoft Excel 2007. Работа с диаграми 1. Вмъкване на колони Извикайте контекстното меню за колона и изберете Вмъкване (нова колона се добавя вляво от избраната). 1.1. Избор
Използване на MS Excel за графична обработка на получените резултати (препоръки за студенти и преподаватели) Редактор на електронни таблици MS Excel, включен в стандартния пакет MS Office,
АВТОМАТИЗАЦИЯ НА ИКОНОМЕТРИЧНО МОДЕЛИРАНЕ Т. А. Заяц Беларуски търговско-икономически университет на потребителските кооперации, Гомел
МИНИСТЕРСТВО НА ЗДРАВЕОПАЗВАНЕТО НА РУСКАТА ФЕДЕРАЦИЯ SBEE НА ВИСШЕТО ПРОФЕСИОНАЛНО ОБРАЗОВАНИЕ АМУРСКА ДЪРЖАВНА МЕДИЦИНСКА КАДЕМИЯ E.V. RAINBOW ELECTRONIC EXCEL ТАБЛИЦИ. МЕТОДИЧЕСКИ УКАЗАНИЯ
Лабораторна работа 4 Табулиране на функциите и чертане Цел: Да се придобият умения за изчисляване на таблица с функционални стойности и чертане. Указания: Табулирането на функция е изчисление
Урок 10 ЕТ позволяват обработка на големи масиви от числени данни. За разлика от хартиените електронни таблици, електронните таблици предоставят
Теми за практическа работа: Практическа работа 1. Въвеждане на данни в клетки, редактиране на данни, промяна на ширината на колоната, вмъкване на ред (колона) Практическа работа 2. Въвеждане на формули Практическа работа 3.
ЛАБОРАТОРНИ РАБОТИ В MS EXCEL 2007 ЛАБОРАТОРИЯ 1.... 1 ЛАБОРАТОРИЯ 2... 3 ЛАБОРАТОРИЯ 3... 4 ЛАБОРАТОРИЯ 4... 7 ЛАБОРАТОРИЯ 5... 8 ЛАБОРАТОРИЯ 6... 10
АПРОКСИМАЦИЯ В практиката често се среща проблемът с изглаждането на експерименталните данни, проблемът с апроксимацията. Основната задача на апроксимацията е изграждането на приближена (апроксимираща) функция
Лабораторно-практическа работа 13 "Свързани таблици в MS Excel 2007" Основни принципи на формиране на работна книга. За правилна организацияработа в електронни таблици на Excel 2007 от оформлението
Excel. Имена на диапазони Може да сте работили с работни листове, които използват формула като: =SUM(A5000:A5078). Чудили ли сте се какво има в клетки A5000: A5078!? Ако в клетки A5000: A5078
Инвестиции в недвижими имоти: икономика, управление, експертиза
Глава 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 Сдвоена регресия
"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. Съкращението му означава пълзяща среднасближаване-разминаване или ако на руски сближаване-разминаване на подвижните средни (означава исторически ценностицени на акции или други инструменти).
Графичното значение на хистограмата MACD е да потвърди продължаването на тренда (посоката на развитие) на движението на цената. Грубо казано, акциите продължават да поевтиняват или поскъпват. Посоката на движение на цената се определя като разликата между два съседни бара.
Използваме excel, за да начертаем MACD хистограмата.
1) Първо имаме нужда от исторически данни за анализ. В предишна статия дадох пример откъде могат да се получат такива данни. Нека последваме този пример и отидем на страницата за експортиране на данни на брокера:
След като зададем изискванията за формата на изтеглените данни, получаваме файл с данни във формат csv, който Excel разбира. Също така историческите данни за инструмента, който ни интересува, могат да бъдат изтеглени на уебсайта на брокера ZAO FINAM po тази връзка.
2) данните трябва да бъдат форматирани, както е описано в .
Крайният резултат трябва да бъде нещо подобно:
3) Сега нека създадем нов лист в книгата на excel за изчисления и чертане технически анализ. Така че нека наречем този лист: „Изчисляване на MACD“. След това копирайте колоната с дата в този лист
4) Сега нека изчислим експоненциалната пълзяща средна с прозорец от 12 дни (EMA 12). EMA 12 се изчислява по формулата:
Поставете тази формула в колоната отдясно на цената на затваряне
Нека копираме получената формула в клетката по-долу и я редактираме малко: вместо стойността от клетка B3, във втората част на формулата, заменете стойността от клетка C2. C2 - това ще бъде EMA12 от предишния ден.
Трябва да се получи така:
Сега нека умножим формулата, получена във втората клетка, за цялата колона EMA12. За да направите това, щракнете веднъж в клетка C3, така че около клетката да се появи черна удебелена рамка, след което преместете курсора в долния десен ъгъл на черната удебелена рамка, така че курсорът да приеме формата на удебелен черен кръст и щракнете двукратно левия бутон на мишката, за да умножите формулата за цялата колона. Като този:
Сега по подобен начин изчисляваме експоненциалната пълзяща средна с прозорец от 26 дни (EMA 26). EMA 26 се изчислява по формулата:
Нека поставим тази формула в колоната отдясно на изчислената EMA12. За целта започваме да пишем в клетката със символа “=”, който казва на процесора на excel, че ще бъде въведена формула. За първата клетка формулата е малко по-различна от тази за останалите клетки, поради факта, че днешната цена на затваряне трябва да бъде заменена с вчерашната EMA26. Като този:
Нека копираме получената формула в клетката по-долу и я редактираме малко: вместо стойността от клетка B3, във втората част на формулата заменете стойността от клетка D2. D2 - това ще бъде EMA26 от предишния ден. Трябва да се получи така:
Сега нека умножим формулата, получена във втората клетка, за цялата колона EMA26. За да направите това, щракнете веднъж в клетка D3, така че около клетката да се появи черна удебелена рамка, след което преместете курсора в долния десен ъгъл на черната удебелена рамка, така че курсорът да приеме формата на удебелен черен кръст и щракнете двукратно левия бутон на мишката, за да умножите формулата за цялата колона. Като този:
Честито! Ние се занимавахме с изчисляването на експоненциални средни стойности. Сега трябва да получите "бърза" MACD линия. За да направите това, извадете EMA26 от EMA12. Нека поставим тази формула в следващата колона вдясно:
Сега трябва да изчислим деветдневната експоненциална плъзгаща се средна за "бързата" MACD линия. Получената линия ще се нарича "сигнална" MACD линия. Ще изчислим по следната формула:
По същия начин попълваме формулата за изчисление в Excel в клетката вдясно от "бързия" MACD ред:
В клетката на долния ред коригираме формулата по същия начин, както направихме при изчисляването на двадесет и шестдневната и дванадесетдневната експоненциална подвижна средна. Ето как трябва да изглежда формулата в клетка F3:
И накрая, можем да изчислим последната колона с данни, за да изградим MACD хистограмата. Стойностите на тази колона за изграждане на хистограма са разликата между "бързите" и "сигналните" MACD линии. Вкарваме последната формула за изчисляване на данни за конструиране на хистограма:
Много по-удобно е да разгледате хистограмата на MACD до диаграмата на колебанията на цените за анализирания инструмент. В предишна статия описах подробно как да изградя такава графика. За да изградим ценова диаграма за инструмент, ще копираме селекция от необходимите данни на отделен лист. Нещо като това:
Най-лесният начин да изградите борсова диаграма е точно тук, на този лист. След това трябва да го копирате на отделен лист, този, на който ще поставим MACD хистограмата.
Ние създаваме отделен лист за нашите диаграми. Поставете копираната диаграма от клипборда и я персонализирайте малко външен вид. Прозорецът на графиката се разтяга и свива по дължина и ширина, подобно на прозорците в самия Windows.
И като щракнете с левия бутон на мишката върху скалата с ценови стойности, можете да промените формата на данните на оста на графиката. След такова натискане мащабът на стойностите на вертикалната (в нашия случай) ос се подчертава с правоъгълна рамка. Веднага щом се появи такава рамка, трябва да натиснете десния бутон на мишката, за да извикате контекстното меню. В контекстното меню с левия бутон на мишката изберете реда<Формат оси…>, като този:
В диалоговия прозорец, който се отваря за настройка на параметрите на оста на графиката, задайте минималната стойност (80) и максималната стойност (160). Това са горните два реда в диалоговия прозорец, който се отваря. Фигурата по-долу показва желаната позиция на радио бутоните и стойностите 80 и 160 се въвеждат в съответните редове:
Под прозореца на ценовата диаграма вмъкнете прозорец за бъдещата MACD хистограма. Изберете раздела в главното меню<<Вставка>> след това подменю<<Гистограмма>> и изберете горната лява икона на хистограма от падащото меню, тази икона е маркирана в жълто на екранната снимка по-долу:
Най-важното е, че преди да поставите втората диаграма, не забравяйте да премахнете отметката от първата. В противен случай една графика може да бъде заменена с друга и имаме нужда от двете графики.
Преди да извикате менюто<<Гистограмма>> би било хубаво да преместите курсора върху клетка A16 и да натиснете левия бутон на мишката. След като вмъкнем хистограмата, трябва да посочим нашата колона с изчислените данни на MACD хистограмата. За да направите това, преместете курсора на мишката върху хистограмата и натиснете десния бутон на мишката, за да извикате контекстното меню за управление на диаграмата. В контекстното меню, което се отваря, изберете елемента<Выбрать данные>:
След натискане на бутона<<Добавить>> в предишния прозорец трябва да напишем името на нашата графика - "MACD", и в долния ред да натиснете бутона вдясно от реда:
След натискане на бутона вдясно от долния ред се отваря тесен прозорец „Промяна на ред“. Без да затваряте този прозорец, преместете с мишката до листа с име MACD:
След като колоната с данни е покрита с тънка пунктирана линия в полето „Промяна на ред“, щракнете върху бутона вдясно. Това ще отвори прозореца Редактиране на ред с два реда. Тук в този прозорец можете да щракнете върху бутона<
Връщайки се към листа с името "ГРАФИКИ" в прозореца за избор на данни за конструиране на хистограма, също натискаме бутона<
Можете да си поиграете малко с размера на прозорците за диаграмите и да получите резултата, който изглежда по-ясен:
И ето същите графики, изградени от системата за търговия QUIK. Изглежда, че го направихме с вас?
Уважаеми читателю! Ако решите да изградите тези графики и нещо не ви се получава, оставете въпроса си в коментарите и заедно със сигурност ще го разберем и ще научим как да изграждаме графики в Excel.
Изходните файлове на excel, от които са направени скрийншотове и в които има начертани графики, можете да изтеглите от .
Изберете от менюто Обслужванепараграф Анализ на данни, ще се появи прозорец със същото име, чийто основен елемент е областта Инструменти за анализ. Тази област предоставя списък с внедрени в Microsoft Методи на Excelстатистическа обработка на данни. Всеки от изброените методи е реализиран като отделен режим на работа, за активирането на който трябва да изберете съответния метод с показалеца на мишката и да натиснете бутона OK. След като се появи диалоговият прозорец на извикания режим, можете да започнете работа.
Режим на работа " пълзяща средна» служи за изглаждане на нивата на емпирични динамичен сериалвъз основа на метода на простата подвижна средна.
Режим на работа " Експоненциално изглаждане» служи за изглаждане на нивата на емпиричните динамични редове, базирани на простите експоненциално изглаждане.
В диалоговите прозорци на тези режими (Фигура 2 и 3) се задават следните параметри:
2. Кутия за отметка Етикети– активното състояние е зададено, ако първият ред (колона) във входния диапазон съдържа заглавки. Ако няма заглавки, отметката трябва да бъде деактивирана. В този случай автоматично ще се генерират стандартни имена за данните за изходния диапазон.
3. Интервал(само в диалоговия прозорец Moving Average) – въведете размера на прозореца за изглаждане Р. По подразбиране р=3.
Фигура 2 - Диалогов прозорец за плъзгаща се средна
4. Фактор на разпад(само в диалоговия прозорец Експоненциално изглаждане) – въведете стойността на експоненциалния коефициент на изглаждане стр. По подразбиране, р=0,3.
5. Изходен интервал / Нов работен лист / Нова работна книга– в позиция Изходен интервал се активира полето, в което е необходимо да се въведе препратка към горната лява клетка на изходния диапазон. Размерът на изходния диапазон ще бъде определен автоматично и на екрана ще се появи съобщение, ако изходният диапазон може да се припокрива с изходните данни. В позиция New worksheet се отваря нов лист, в който започвайки от клетката A1вмъкват се резултатите от анализа. Ако трябва да зададете име в полето срещу съответната позиция на превключвателя. В позиция Нова работна книга се отваря нова книга, на чийто първи лист, започвайки от кл A1вмъкват се резултатите от анализа.
6. Графичен изход– е зададено в активно състояние за автоматично генериране в работния лист на графики на действителните и теоретични нивадинамична линия.
7. Стандартни грешки– са зададени в активно състояние, ако се изисква да се включи колона, съдържаща стандартни грешки в изходния диапазон.
Фигура 3 - Диалогов прозорец за експоненциално изглаждане
Пример 1
Данните за продажбата (милиони рубли) на селскостопански продукти от градските потребителски кооперационни магазини са дадени в таблица, генерирана на работен лист на Microsoft Excel (Фигура 4). В посочения период (2009 - 2012 г.) е необходимо да се идентифицира основната тенденция в развитието на този икономически процес.
Фигура 4 - Първоначални данни
За да разрешим проблема, използваме режима на работа " пълзяща средна". Стойностите на параметрите, зададени в едноименния диалогов прозорец, са показани на фигура 5, показателите, изчислени в този режим, са показани на фигура 6, а начертаните графики са показани на фигура 7.
Фигура 5 - Попълване на диалоговия прозорец
Фигура 6 - Резултати от анализа
Фигура 7 – Пълзяща средна
Колона D (Фигура 5) изчислява стойностите на изгладените нива. Например стойността на първото изгладено ниво се изчислява в клетка D5 по формулата =СРЕДНО(C2:C5), стойността на второто изгладено ниво се изчислява в клетка D6 по формулата =СРЕДНО(C5:C8) и т.н. .
Колона E изчислява стандартните грешки с помощта на формулата =SQRT(SUMQDIFF (блок с действителна стойност; блок с прогнозна стойност) / размер на изглаждащия прозорец).
Например стойността в клетка E10 се изчислява по формулата =SQRT(SUMQDIFF(C7:C10,O7:B10)/4).
Въпреки това, както беше отбелязано по-горе, ако размерът на изглаждащия прозорец е четно число ( p=2m), тогава изчислената средна стойност не може да бъде свързана с конкретно време t, така че трябва да се приложи процедурата за центриране.
За въпросния пример р=4, така че процедурата по центриране е необходима. Така първото изгладено ниво (265.25) е регистрирано между II и III тримесечие. 2009 г. и др. Прилагайки процедурата за центриране (за това използваме функцията AVERAGE), получаваме изгладени нива с центриране. За III kV. 2009 г. се определя медианата между първото и второто изгладени нива: (265,25 + 283,25)/2 = 274,25; за IV тримесечие. 2009, второто и третото изгладени нива са центрирани: (283,25 + 292,00)/2 = 287,6 и т.н. Изчислените стойности са представени в таблица 1. Коригираната графика на подвижната средна е показана на фигура 8.
Таблица 1 - Динамика на изгладените нива на продажби на продукти
| година | Квартал | Обем на продажбите, милиони рубли | Гладки нива с центриране |
| 274,25 | |||
| 287,63 | |||
| 297,00 | |||
| 307,50 | |||
| 334,63 | |||
| 374,13 | |||
| 402,88 | |||
| 421,00 | |||
| 429,00 | |||
| 430,75 | |||
| 435,38 | |||
| 446,63 | |||
Фигура 8 - Графика с коригирана подвижна средна
Пример 2
Разглежданият проблем може да бъде решен и чрез метода на простото експоненциално изглаждане. За да направите това, трябва да използвате режима на работа "Експоненциално изглаждане". Стойностите на параметрите, зададени в едноименния диалогов прозорец, са показани на фигура 9, индикаторите, изчислени в този режим, са показани на фигура 10, а начертаните графики са показани на фигура 11.
Фигура 9 - Попълване на диалоговия прозорец "Exponential Smoothing".
Фигура 10 - Резултати от анализа
Фигура 11 - Експоненциално изглаждане
В колона D (Фигура 10) стойностите на изгладените нива се изчисляват въз основа на рекурентни отношения.
Колона E изчислява стандартните грешки по формулата =SQRT(SUMQDIFF (блок от действителни стойности; блок от прогнозирани стойности) / 3). Както можете лесно да видите (сравнете фигури 8 и 11), когато използвате простия метод на експоненциално изглаждане, за разлика от метода на простата подвижна средна, малките вълни се запазват.
Методът на подвижната средна е статистически инструмент, който може да се използва за решаване различни видовезадачи. По-специално, доста често се използва при прогнозиране. AT програма ExcelМожете също да използвате този инструмент за решаване на редица проблеми. Нека разберем как се използва подвижната средна в Excel.
Значение този методсе състои в това, че с негова помощ абсолютните динамични стойности на избраната серия се променят на средните аритметични за определен период чрез изглаждане на данните. Този инструмент се използва за икономически изчисления, прогнозиране, в процеса на търговия на фондовата борса и др. Най-добрият начин да приложите метода на подвижната средна в Excel е с помощта на мощен инструмент за обработка на статистически данни, наречен Пакет за анализ. Можете също да използвате вградената функция на Excel за същата цел. СРЕДНО АРИТМЕТИЧНО.
Метод 1: Пакет за анализ
Пакет за анализе добавка на Excel, която е деактивирана по подразбиране. Следователно, на първо място, трябва да го активирате.
След тази стъпка пакетът "Анализ на данни"се активира и съответният бутон се появява на лентата в раздела "Данни".
А сега нека да разгледаме как можете директно да използвате функциите на пакета Анализ на даннида използвате метода на пълзящата средна. Нека направим прогноза за дванадесетия месец въз основа на информация за приходите на компанията за 11 предходни периода. За целта ще използваме таблицата, пълна с данни, както и инструменти Пакет за анализ.
- Отидете в раздела "Данни"и щракнете върху бутона "Анализ на данни", който се намира на лентата с инструменти в блока "Анализ".
- Списък с инструменти, налични в Пакет за анализ. Изберете име от тях „Пълзяща средна“и щракнете върху бутона Добре.
- Стартира се прозорецът за въвеждане на данни за прогнозиране на подвижна средна.
В полето "Интервал на въвеждане"посочете адреса на диапазона, където се намира месечната сума на приходите, без клетката, в която трябва да се изчислят данните.
В полето "Интервал"задайте интервала за обработка на стойности чрез метода на изглаждане. Като начало нека зададем стойността на изглаждане на три месеца и следователно въведете числото "3".
В полето „Интервал за изход“трябва да зададете произволен празен диапазон на листа, където ще се показват данните след обработката, който трябва да бъде с една клетка повече от интервала за въвеждане.
Трябва също да поставите отметка в квадратчето до „Стандартни грешки“.
Ако е необходимо, можете също да поставите отметка в квадратчето до „Изход на графика“за визуална демонстрация, въпреки че в нашия случай това не е необходимо.
След като всички настройки са направени, щракнете върху бутона Добре.
- Програмата показва резултата от обработката.
- Сега нека проведем изглаждане за период от два месеца, за да разберем кой резултат е по-правилен. За тези цели стартираме инструмента отново „Пълзяща средна“ Пакет за анализ.
В полето "Интервал на въвеждане"оставяме същите стойности, както в предишния случай.
В полето "Интервал"сложете номер "2".
В полето „Интервал за изход“посочете адреса на новия празен диапазон, който отново трябва да бъде с една клетка повече от интервала на въвеждане.
Останалите настройки остават същите. След това кликнете върху бутона Добре.
- След това програмата изчислява и показва резултата на екрана. За да определим кой от двата модела е по-точен, трябва да сравним стандартните грешки. Колкото по-малък е този показател, толкова по-голяма е вероятността за точност на резултата. Както можете да видите, за всички стойности стандартната грешка при изчисляване на двумесечната пълзяща средна е по-малка от същия индикатор за 3 месеца. По този начин прогнозната стойност за декември може да се счита за стойността, изчислена по плъзгащия метод за последния период. В нашия случай тази стойност е 990,4 хиляди рубли.
Метод 2: Използвайте функцията AVERAGE
В Excel има друг начин за прилагане на метода на подвижната средна. За да го използвате, трябва да приложите редица стандартни програмни функции, основната от които за нашата цел е СРЕДНО АРИТМЕТИЧНО. Например ще използваме същата таблица с доходите на предприятието, както в първия случай.
Както миналия път, ще трябва да създадем изгладен времеви ред. Но този път действията няма да са толкова автоматизирани. Трябва да се изчислява средна стойност за всеки два и след това три месеца, за да могат да се сравняват резултатите.
На първо място, ние изчисляваме средните стойности за предходните два периода с помощта на функцията СРЕДНО АРИТМЕТИЧНО. Можем да направим това едва от март, тъй като за по-късни дати има прекъсване на стойностите.
- Изберете клетка в празна колона в реда за март. След това щракнете върху иконата "Вмъкване на функция", който се поставя близо до лентата с формули.
- Прозорецът е активиран Помощници за функции. Категория "статистически"търсейки смисъл "СРЕДНО АРИТМЕТИЧНО", изберете го и щракнете върху бутона Добре.
- Стартира се прозорецът с аргументи на оператора СРЕДНО АРИТМЕТИЧНО. Синтаксисът му е следният:
СРЕДНО(число1, число2,...)
Изисква се само един аргумент.
В нашия случай на полето "Номер 1"трябва да се обърнем към диапазона, където са посочени приходите за предходните два периода (януари и февруари). Поставяме курсора в полето и избираме съответните клетки на листа в колоната "доходи". След това кликнете върху бутона Добре.
- Както можете да видите, резултатът от изчисляването на средната стойност за предходните два периода беше показан в клетката. За да извършим подобни изчисления за всички останали месеци от периода, трябва да копираме тази формула в други клетки. За да направите това, ставаме курсора в долния десен ъгъл на клетката, съдържаща функцията. Курсорът се преобразува в манипулатор за запълване, който изглежда като кръст. Задръжте левия бутон на мишката и го плъзнете надолу до самия край на колоната.
- Получаваме изчислението на резултатите от средната стойност за предходните два месеца до края на годината.
- Сега изберете клетката в следващата празна колона в реда за април. Извикване на прозореца с аргументи на функцията СРЕДНО АРИТМЕТИЧНОпо същия начин, както е описано по-рано. В полето "Номер 1"въведете координатите на клетките в колоната "доходи"от януари до март. След това щракнете върху бутона Добре.
- С помощта на манипулатора за попълване копирайте формулата в клетките на таблицата по-долу.
- И така, изчислихме стойностите. Сега, както и предишния път, ще трябва да разберем кой тип анализ е по-добър: с изглаждане от 2 или 3 месеца. За да направите това, изчислете стандартното отклонение и някои други показатели. Първо, изчисляваме абсолютното отклонение, като използваме стандарта Функция на Excel коремни мускули, който вместо положителни или отрицателни числа връща техния модул. Тази стойност ще бъде равна на разликата между действителния приход за избрания месец и прогнозния. Поставяме курсора на следващата празна колона в реда за май. Обаждане Съветник за функции.
- Категория "математически"маркирайте името на функцията коремни мускули. Кликнете върху бутона Добре.
- Стартира се прозорецът с аргументи на функцията коремни мускули. В единственото поле "номер"посочете разликата между съдържанието на клетките в колоните "доходи"и "2 месеца"за май. След това щракнете върху бутона Добре.
- С помощта на маркера за запълване копирайте тази формула във всички редове на таблицата до ноември включително.
- Изчисляваме средната стойност на абсолютното отклонение за целия период, като използваме вече познатата ни функция СРЕДНО АРИТМЕТИЧНО.
- Извършваме подобна процедура, за да изчислим абсолютното отклонение за подвижната средна за 3 месеца. Първо прилагаме функцията коремни мускули. Само този път отчитаме разликата между съдържанието на клетките с действителен доход и планирания, изчислен по метода на подвижната средна за 3 месеца.
- След това изчисляваме средната стойност на всички данни за абсолютно отклонение с помощта на функцията СРЕДНО АРИТМЕТИЧНО.
- Следващата стъпка е да се изчисли относителното отклонение. То е равно на съотношението на абсолютното отклонение към действителния показател. За да избегне отрицателни стойности, отново ще използваме възможностите, които предлага операторът коремни мускули. Този път, използвайки тази функция, разделяме стойността на абсолютното отклонение по метода на 2-месечната пълзяща средна на действителния доход за избрания месец.
- Но относителното отклонение обикновено се показва като процент. Затова изберете подходящия диапазон на листа, отидете в раздела "У дома", където в кутията с инструменти "номер"в специално поле за форматиране задайте процентния формат. След това резултатът от изчислението на относителното отклонение се показва като процент.
- Извършваме подобна операция за изчисляване на относителното отклонение с данни, като използваме изглаждане за 3 месеца. Само в този случай за изчислението, като дивидент, използваме друга колона от таблицата, която имаме името "Коремни мускули. изключен (3m)". След това превеждаме числените стойности в процентна форма.
- След това изчисляваме средните стойности за двете колони с относително отклонение, както преди да използваме функцията за това СРЕДНО АРИТМЕТИЧНО. Тъй като за изчислението приемаме процентни стойности като аргументи на функцията, няма нужда да извършваме допълнително преобразуване. Операторът за изход дава резултата вече в процентен формат.
- Сега стигаме до изчисляването на стандартното отклонение. Този индикатор ще ни позволи директно да сравним качеството на изчислението при използване на изглаждане за два и три месеца. В нашия случай стандартното отклонение ще бъде равно на корен квадратен от сбора на квадратите на разликите между действителния приход и подвижната средна, разделен на броя на месеците. За да направим изчисление в програмата, трябва да използваме по-специално редица функции КОРЕН, СУММQВАРИАНи ПРОВЕРКА. Например, за да изчислим стандартното отклонение при използване на изглаждащата линия за два месеца през май, в нашия случай ще се приложи следната формула:
SQRT(SUMDIFF(B6:B12;C6:C12)/БРОЙ(B6:B12))
Копираме го в други клетки на колоната с изчисляване на стандартното отклонение с помощта на маркера за запълване.
- Подобна операция за изчисляване на стандартното отклонение се извършва и за пълзящата средна за 3 месеца.
- След това изчисляваме средната стойност за целия период и за двата показателя, като прилагаме функцията СРЕДНО АРИТМЕТИЧНО.
- Сравнявайки изчисленията на подвижна средна с 2 и 3 месечно изглаждане за абсолютно отклонение, относително отклонение и стандартно отклонение, можем да кажем с увереност, че двумесечното изглаждане дава по-надеждни резултати от използването на тримесечно изглаждане. Това се доказва от факта, че горните цифри за двумесечна пълзяща средна са по-малки от тези за тримесечна.
- Така прогнозираният показател за приходите на компанията за декември ще бъде 990,4 хиляди рубли. Както можете да видите, тази стойност напълно съвпада с тази, която получихме при изчисляване с помощта на инструментите Пакет за анализ.
Изчислихме прогнозата с помощта на метода на пълзящата средна по два начина. Както можете да видите, тази процедура е много по-лесна за изпълнение с помощта на инструменти. Пакет за анализ. Някои потребители обаче не винаги се доверяват автоматично изчислениеи предпочитат да използват функцията за изчисления СРЕДНО АРИТМЕТИЧНОи свързани оператори, за да проверят най-надеждната опция. Въпреки че, ако всичко е направено правилно, резултатът от изчисленията трябва да се окаже напълно същият.
Зареждане...