Метод на преместване в Microsoft Excel

Методът с плъзгаща се средна стойност е статистически инструмент, с който можете да решите различни задачи. По-специално, тя често се използва при прогнозиране. В Excel можете да използвате този инструмент и за решаване на редица задачи. Нека видим как се използва средната стойност в Excel.

Прилагане на плъзгаща се средна стойност

Значението на този метод е, че с негова помощ има промяна в абсолютните динамични стойности на избраната серия до средна аритметика за определен период чрез изглаждане на данните. Този инструмент се използва за икономически изчисления, прогнози, в процес на търговия на фондовата борса и др. Прилагането на метода Moving Average в Excel се извършва най-добре с помощта на мощен инструмент за статистическа обработка на данни, наречен Analysis Pack . Освен това, за същата цел, можете да използвате вградената функция Excel AVERAGE .

Метод 1: Пакет за анализ

Пакетът за анализ е добавка в Excel, която е деактивирана по подразбиране. Следователно, на първо място, трябва да го включите.

1. Преминаваме към раздела "Файл" . Кликнете върху елемента "Опции" .
2. В отворения прозорец отидете в раздела "Добавки" . В долната част на прозореца параметрите "Добавки в Excel" трябва да бъдат зададени в полето "Управление" . Кликнете върху бутона "Отиди" .
3. Влизаме в прозореца на добавките. Поставете отметка в квадратчето до "Пакет за анализ" и кликнете върху бутона "OK" .

След това действие пакетът "Анализ на данните" се активира и съответният бутон се появи на лентата в раздела "Данни" .

И сега нека разгледаме как можете директно да използвате възможностите за анализ на данните за метода с плъзгаща се средна стойност. Нека направим прогноза за дванадесетия месец въз основа на информация за приходите на компанията за 11-те предишни периода. За целта използваме таблица с данни, както и инструменти за инструменти за анализ .

1. Отидете в раздела "Данни" и кликнете върху бутона "Анализ на данните" , който се намира на колана за инструменти в блока "Анализ" .
2. Ще се отвори списък с инструменти, които са налице в пакета за анализ . Изберете от тях името "плъзгаща се средна стойност" и кликнете върху бутона "OK" .
3. Появява се прозорецът за въвеждане на данни за прогнозиране, като се използва методът с плаваща средна стойност.

   В полето "Интервал на въвеждане" посочете адреса на диапазона, в който се изчисляват месечните приходи без клетката, данните, които трябва да се изчислят.

   В полето "Интервал" посочете интервала за обработка на стойностите, като използвате метода за изтриване. Първо, нека настроим стойността на анти-плюс до три месеца и следователно въведете числото "3" .

   В полето "Изходен интервал" трябва да посочите произволен празен диапазон на листа, където данните ще бъдат изведени след обработката, което трябва да бъде една клетка по-голяма от интервала за въвеждане.

   Също така проверявайте полето "Стандартни грешки" .

   Ако е необходимо, можете също да поставите отметка в квадратчето "Изходна графика" за визуална демонстрация, въпреки че в нашия случай това не е необходимо.

   След като направите всички настройки, кликнете върху бутона "OK" .

4. Програмата показва резултата от обработката.
5. Сега направете изглаждане за период от два месеца, за да определите кой резултат е по-правилен. За тези цели отново стартираме инструмента "Moving Average" на пакета за анализ .

   В полето "Интервал на въвеждане" оставаме същите стойности, както в предишния случай.

   В полето "Интервал" поставяме числото "2" .

   В полето "Изходящ интервал" посочваме адреса на новия празен диапазон, който отново трябва да бъде една клетка по-голяма от интервала за въвеждане.

   Останалите настройки са еднакви. След това кликнете върху бутона "OK" .

6. След това програмата изчислява и показва резултата на екрана. За да се определи кой от двата модела е по-точен, трябва да сравним стандартните грешки. Колкото по-малък е даден индекс, толкова по-голяма е вероятността точността на получения резултат. Както можем да видим, при всички стойности стандартната грешка при изчисляването на двумесечното преобръщане е по-малка от същия индикатор за 3 месеца. По този начин прогнозната стойност за декември може да се счита за стойността, изчислена чрез плъзгащия метод за последния период. В нашия случай тази стойност е 990.4 хил. Рубли.

Метод 2: Използвайте функцията AVERAGE

В Excel има друг начин за прилагане на метода с плъзгаща се средна стойност. За да го използвате, трябва да приложите няколко стандартни функции на програмата, основната от които за нашата цел е СРЕДНО . Например, ще използваме същата таблица на дохода на компанията, както в първия случай.

Както и в последния момент, трябва да създадем загладена динамична серия. Но този път действията няма да бъдат толкова автоматизирани. Трябва да изчислите средната стойност за всеки две, а след това и три месеца, за да можете да сравните резултатите.

Първо, изчисляваме средните стойности за двата предходни периода с помощта на функцията AVERAGE . Можем да направим това само от март, тъй като за по-късни дати има прекъсване на стойностите.

1. Изберете клетката в празната колона в реда за март. След това кликнете върху иконата "Вмъкване на функция" , която се намира близо до формулата.
2. Прозорецът Магьосници е активиран. В категорията "Статистически" търсим стойност "СРЕДНО" , изберете го и кликнете върху бутона "ОК" .
3. Прозорецът с аргументи на израза AVERAGE е стартиран . Синтаксисът е както следва:

   =СРЗНАЧ(число1;число2;…)

   Необходим е само един аргумент.

   В нашия случай в полето "Номер 1" трябва да посочим връзка към обхвата, в който са посочени приходите за двата предходни периода (януари и февруари). Задайте курсора в полето и изберете съответните клетки в листа в графата "Приходи" . След това кликнете върху бутона "OK" .

4. Както можете да видите, резултатът от изчисляването на средната стойност за двата предходни периода е показан в клетката. За да извършим тези изчисления за всички останали месеци от периода, трябва да копираме тази формула в други клетки. За да направите това, ние ставаме курсор в долния десен ъгъл на клетката, съдържаща функцията. Курсорът се преобразува в маркер за попълване, който прилича на кръст. Задръжте левия бутон на мишката и го плъзнете надолу до самия край на колоната.
5. Получаваме изчислението на резултатите от средната стойност за предходните два месеца преди края на годината.
6. Сега изберете клетката в следващата празна колона в реда за април. Ние наричаме прозореца с аргументи на функцията AVERAGE по същия начин, както е описано по-горе. В полето "Номер1" въвеждаме координатите на клетките в графата "Приходи" от януари до март. След това кликнете върху бутона "OK" .
7. Използвайки маркера за попълване, копирайте формулата в клетките на таблицата по-долу.
8. Така че, ние сме изчислили стойностите. Сега, както и в предишния момент, ще трябва да разберем кой анализ е по-добър: с изглаждане от 2 или 3 месеца. За тази цел изчислете средното квадратно отклонение и някои други показатели. Първо, изчисляваме абсолютното отклонение, като използваме стандартната Excel функция ABS , която вместо положителните или отрицателните числа връща своя модул. Тази стойност ще бъде равна на разликата между реалната цифра на приходите за избрания месец и прогнозираната. Задайте курсора на следващата празна колона в реда за май. Обадете се на съветника за функциите .
9. В категорията "Математически" ние подчертаваме името на функцията "ABS" . Кликнете върху бутона "OK" .
10. Прозорецът с аргументи на функцията ABS е отворен. В единичното поле "Брой" ние посочваме разликата между съдържанието на клетките в колоните "Приходи" и "2 месеца" за месец май. След това кликнете върху бутона "OK" .
11. Използвайки пълнителя, копирайте тази формула във всички редове на таблицата до ноември включително.
12. Изчисляваме средната стойност на абсолютното отклонение за целия период с помощта на познатата функция СРЕДНО .
13. Извършваме подобна процедура, за да изчислим абсолютното отклонение за движението през 3 месеца. Първо прилагайте функцията ABS . Само този път разглеждаме разликата между съдържанието на клетките с действителен доход и планираните, изчислени по метода на подвижната средна стойност за 3 месеца.
14. След това изчислете средната стойност на всички абсолютни данни за отклонението, като използвате функцията AVERAGE .
15. Следващата стъпка е да се изчисли относителното отклонение. Тя е равна на съотношението на абсолютното отклонение към действителния индикатор. За да избегнем отрицателни стойности, отново използваме възможностите, предлагани от оператора на ABS . Този път, използвайки тази функция, разделяме абсолютната стойност на отклонението, като използваме метода на подвижната средна стойност за 2 месеца за действителния доход за избрания месец.
16. Относителното отклонение обаче обикновено се показва като процент. Затова изберете подходящия диапазон на листа, отидете в раздела "Начало" , където в полето "Номер", в полето за специален формат, задайте процентния формат. След това резултатът от изчисляването на относителното отклонение се показва в проценти.
17. Подобна операция за изчисляване на относителното отклонение се извършва с данни с използването на изглаждане за 3 месеца. Само в този случай, за да изчислим като делим, използваме друга колона от таблицата, която имаме под името "Abs. (3м) " . След това преобразуваме цифровите стойности в проценти.
18. След това изчислете средните стойности за двете колони с относително отклонение, както преди да използвате функцията AVERAGE . Тъй като вземаме процентни стойности като аргументи за дадена функция, няма нужда да я конвертираме. Операторът извежда резултата в процентен формат.
19. Сега стигнахме до изчислението на средното квадратно отклонение. Този показател ще ни позволи директно да сравним качеството на изчислението, когато използваме анти-плюс за два и три месеца. В нашия случай стандартното отклонение ще бъде равно на квадратния корен на сумата от квадратите на разликите в реалните приходи и плъзгащата се средна стойност, разделена на броя месеци. За да направим изчисление в програмата, трябва да използваме редица функции, по-специално ROOT , SUMMKVRAZN и ACCOUNT . Например, за да изчислим средното квадратично отклонение при използване на линията за изглаждане за два месеца през май, в нашия случай ще се използва следната формула:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    Копирайте го в други клетки в колоната, като изчислите средното квадратно отклонение с помощта на маркера за попълване.

20. Подобна операция за изчисляване на средното квадратично отклонение се извършва за плъзгащата се средна стойност за 3 месеца.
21. След това изчисляваме средната стойност за целия период и за двата индикатора, като използваме функцията AVERAGE .
22. Чрез сравняване на изчисленията с метода на подвижната средна стойност с изглаждане на 2 и 3 месеца за такива показатели като абсолютно отклонение, относително отклонение и отклонение на средно квадратно отклонение, можем със сигурност да кажем, че изглаждането за два месеца дава по-надеждни резултати от прилагането на изглаждане за три месеца. Това се потвърждава от факта, че горните показатели за двумесечна пълзяща средна са по-малко от три месеца.
23. По този начин прогнозираният доход на компанията за декември ще бъде 990.4 хил. Рубли. Както можете да видите, тази стойност напълно съвпада с тази, която получихме, като направим изчисления с помощта на инструментите за анализ на пакета .

Урокът: Съветник за функции в Excel

Изчислихме прогнозата по метода на подвижната средна по два начина. Както можете да видите, тази процедура е много по-лесна за изпълнение с инструментите на пакета за анализ . Въпреки това, някои потребители не винаги се доверяват на автоматичното изчисление и предпочитат да използват функцията AVERAGE и придружаващите оператори за изчисления, за да проверят най-надеждната версия. Въпреки че, ако всичко е направено правилно, изходът от изчислението трябва да бъде точно същия при изхода.

• ← Изчисляване на функцията Laplace в Microsoft Excel
• Изтриване на линия в Microsoft Excel →