Метад слізгальнай сярэдняй - гэта статыстычны інструмент, з дапамогай якога можна вырашаць рознага роду задачы. У прыватнасці, ён даволі часта выкарыстоўваецца пры прагназаванні. У праграме Excel для вырашэння цэлага шэрагу задач таксама можна ўжываць дадзены інструмент. Давайце разбярэмся, як выкарыстоўваецца слізгальная сярэдняя ў Эксэля.
Прымяненне слізгальнай сярэдняй
Сэнс дадзенага метаду складаецца ў тым, што з яго дапамогай адбываецца змена абсалютных дынамічных значэнняў абранага шэрагу на сярэднія арыфметычныя за вызначаны перыяд шляхам згладжвання дадзеных. Гэты інструмент ўжываецца для эканамічных разлікаў, прагназавання, у працэсе гандлю на біржы і г.д. Ужываць метад слізгальнай сярэдняй у Эксэля лепш за ўсё з дапамогай магутнага інструмента статыстычнай апрацоўкі дадзеных, які называецца пакетам аналізу. Акрамя таго, у гэтых жа мэтах можна выкарыстоўваць убудаваную функцыю Excel СРЗНАЧ.
Спосаб 1: Пакет аналізу
пакет аналізу ўяўляе сабой надбудову Excel, якая па змаўчанні адключаная. Таму, перш за ўсё, патрабуецца яе ўключыць.
- Перамяшчаемся ва ўкладку "Файл". Робім пстрычка па пункце "Параметры".
- У запусцім акне параметраў варта перайсці ў раздзел "Надбудовы". У ніжняй частцы акна ў поле "Упраўленне" павінен быць выстаўлены параметр "Надбудовы Excel". Пстрыкаем па кнопцы "Перайсці".
- Мы трапляем у акно надбудоў. Усталёўваем галачку каля пункта "Пакет аналізу" і пстрыкаем па кнопцы "OK".
Пасля гэтага дзеяння пакет "Аналіз дадзеных" актываваны, і адпаведная кнопка з'явілася на стужцы ва ўкладцы "Дадзеныя".
А цяпер давайце разгледзім, як непасрэдна можна выкарыстоўваць магчымасці пакета аналіз дадзеных для працы па метадзе слізгальнай сярэдняй. Давайце на аснове інфармацыі пра даход фірмы за 11 папярэдніх перыядаў складзем прагноз на дванаццаты месяц. Для гэтага скарыстаемся запоўненай дадзенымі табліцай, а таксама інструментамі пакета аналізу.
- Пераходзім ва ўкладку "Дадзеныя" і ціснем на кнопку "Аналіз дадзеных", Якая размешчана на стужцы інструментаў у блоку "Аналіз".
- Адкрываецца пералік інструментаў, якія даступныя ў пакеце аналізу. Выбіраем з іх найменне "Слізгальная сярэдняя" і ціснем на кнопку "OK".
- Запускаецца акно ўводу дадзеных для прагназавання метадам слізгальнай сярэдняй.
У полі "Уваходны інтэрвал" паказваем адрас дыяпазону, дзе размешчана памесячна сума выручкі без ячэйкі, дадзеныя ў якой варта разлічыць.
У полі "Інтэрвал" варта паказаць інтэрвал апрацоўкі значэнняў метадам згладжвання. Для пачатку давайце ўсталюем значэнне згладжвання у тры месяцы, а таму ўпісваем лічбу "3".
У полі "Выхадны інтэрвал" трэба паказаць адвольны пусты дыяпазон на лісце, дзе будуць выводзіцца дадзеныя пасля іх апрацоўкі, які павінен быць на адну вочка больш уваходнага інтэрвалу.
Таксама варта ўсталяваць галачку каля параметру "Стандартныя хібнасці".
Пры неабходнасці, можна таксама ўсталяваць галачку каля пункта "Выснова графіка" для візуальнай дэманстрацыі, хоць у нашым выпадку гэта і не абавязкова.
Пасля таго, як усе налады ўнесены, ціснем на кнопку "OK".
- Праграма выводзіць вынік апрацоўкі.
- Цяпер выканаем згладжванне за перыяд у два месяцы, каб выявіць, які вынік з'яўляецца больш карэктным. Для гэтых мэтаў ізноў запускаем інструмент "Слізгальная сярэдняя" пакета аналізу.
У полі "Уваходны інтэрвал" пакідаем тыя ж значэння, што і ў папярэднім выпадку.
У полі "Інтэрвал" ставім лічбу "2".
У полі "Выхадны інтэрвал" паказваем адрас новага пустога дыяпазону, які, зноў жа, павінен быць на адну вочка больш уваходнага інтэрвалу.
Астатнія налады пакідаем ранейшымі. Пасля гэтага ціснем на кнопку "OK".
- Услед за гэтым праграма вырабляе разлік і выводзіць вынік на экран. Для таго, каб вызначыць, якая з дзвюх мадэляў больш дакладная, нам трэба параўнаць стандартныя хібнасці. Чым менш гэты паказчык, тым вышэй верагоднасць дакладнасці атрыманага выніку. Як бачым, па ўсіх значэнняў стандартная хібнасць пры разліку двухмесяцовай слізгальнай менш, чым аналагічны паказчык за 3 месяцы. Такім чынам, прагназуемым значэннем на снежань можна лічыць велічыню, разлічаную метадам слізгацення за апошні перыяд. У нашым выпадку гэта значэнне 990,4 тыс. Рублёў.
Спосаб 2: выкарыстанне функцыі СРЗНАЧ
У Эксэля існуе яшчэ адзін спосаб прымянення метаду слізгальнай сярэдняй. Для яго выкарыстання патрабуецца прымяніць цэлы шэраг стандартных функцый праграмы, базавай з якіх для нашай мэты з'яўляецца СРЗНАЧ. Для прыкладу мы будзем выкарыстоўваць усё тую ж табліцу даходаў прадпрыемства, што і ў першым выпадку.
Як і ў мінулы раз, нам трэба будзе стварыць згладжаныя часовыя шэрагі. Але на гэты раз дзеянні будуць не настолькі аўтаматызаваны. Варта разлічыць сярэдняе значэнне за кожныя два, а потым тры месяцы, каб мець магчымасць параўнаць вынікі.
Перш за ўсё, разлічым сярэднія значэння за два папярэднія перыяду з дапамогай функцыі СРЗНАЧ. Зрабіць гэта мы можам, толькі пачынаючы з сакавіка, так як для пазнейшых дат ідзе абрыў значэнняў.
- Вылучаем вочка ў пусты калонцы ў радку за сакавік. Далей ціснем на значок "Уставіць функцыю", Які размешчаны паблізу радкі формул.
- актывуецца акно майстры функцый. У катэгорыі "Статыстычныя" шукаем значэнне "СРЗНАЧ", Вылучаем яго і пстрыкаем па кнопцы "OK".
- Запускаецца акно аргументаў аператара СРЗНАЧ. Сінтаксіс ў яго наступны:
= СРЗНАЧ (число1; число2; ...)Абавязковым з'яўляецца толькі адзін аргумент.
У нашым выпадку, у поле "Число1" мы павінны паказаць спасылку на дыяпазон, дзе паказаны даход за два папярэднія перыяду (студзень і люты). Усталёўваем курсор у полі і вылучаем адпаведныя ячэйкі на лісце ў Стоўбцах "Прыбытак". Пасля гэтага ціснем на кнопку "OK".
- Як бачым, вынік разліку сярэдняга значэння за два папярэднія перыяду адлюстраваўся ў вочку. Для таго, каб выканаць падобныя вылічэнні для ўсіх астатніх месяцаў перыяду, нам трэба скапіяваць дадзеную формулу ў іншыя клеткі. Для гэтага становімся курсорам у ніжні правы кут ячэйкі, якая змяшчае функцыю. Курсор пераўтворыцца ў маркер запаўнення, які мае выгляд крыжык. Зацісканы левую кнопку мышы і працягваем яго ўніз да самага канца слупка.
- Атрымліваем разлік вынікаў сярэдняга значэння за два папярэднія месяцы да канца года.
- Цяпер вылучаем вочка ў наступным пустым слупку ў радку за красавік. Выклікаем акно аргументаў функцыі СРЗНАЧ тым жа спосабам, які быў апісаны раней. У полі "Число1" ўпісваем каардынаты вочак ў Стоўбцах "Прыбытак" са студзеня па сакавік. Затым ціснем на кнопку "OK".
- З дапамогай маркера запаўнення капіюем формулу ў вочка табліцы, размешчаныя ніжэй.
- Такім чынам, значэння мы падлічылі. Цяпер, як і ў папярэдні раз, нам трэба будзе высветліць, які выгляд аналізу больш якасны: са згладжваннем ў 2 ці ў 3 месяцы. Для гэтага варта разлічыць сярэдняе квадратычны адхіленне і некаторыя іншыя паказчыкі. Для пачатку разлічым абсалютная адхіленне, скарыстаўшыся стандартнай функцыяй Excel ABS, Якая замест станоўчых ці адмоўных лікаў вяртае іх модуль. Дадзенае значэнне будзе роўна рознасці паміж рэальным паказчыкам выручкі на абраны месяц і прагназуемым. Усталёўваем курсор у наступны пусты слупок ў радок за травень. выклікаем майстар функцый.
- У катэгорыі "Матэматычныя" вылучаем найменне функцыі "ABS". Ціснем на кнопку "OK".
- Запускаецца акно аргументаў функцыі ABS. У адзіным поле "Лічба" паказваем рознасць паміж змесцівам вочак у слупках "Прыбытак" і "2 месяцы" за травень. Затым ціснем на кнопку "OK".
- З дапамогай маркера запаўненняў капіюем дадзеную формулу ва ўсе радкі табліцы па лістапад ўключна.
- Разлічваем сярэдняе значэнне абсалютнага адхіленні за ўвесь перыяд з дапамогай ужо знаёмай нам функцыі СРЗНАЧ.
- Аналагічную працэдуру выконваем і для таго, каб падлічыць абсалютная адхіленне для слізгальнай за 3 месяцы. Спачатку ўжываем функцыю ABS. Толькі на гэты раз лічым розніцу паміж змесцівам вочак з фактычным прыбыткам і планавым, разлічаных па метадзе слізгальнай сярэдняй за 3 месяцы.
- Далей разлічваем сярэдняе значэнне ўсіх дадзеных абсалютнага адхіленні з дапамогай функцыі СРЗНАЧ.
- Наступным крокам з'яўляецца падлік адноснага адхіленні. Яно роўна адносінах абсалютнага адхіленні да фактычнага паказчыку. Для таго каб пазбегнуць адмоўных значэнняў, мы зноў скарыстаемся тымі магчымасцямі, якія прапануе аператар ABS. На гэты раз з дапамогай дадзенай функцыі дзелім значэнне абсалютнага адхіленні пры выкарыстанні метаду слізгальнай сярэдняй за 2 месяцы на фактычны даход на абраны месяц.
- Але адноснае адхіленне прынята адлюстроўваць у працэнтных выглядзе. Таму вылучаем адпаведны дыяпазон на лісце, пераходзім ва ўкладку "Галоўная", Дзе ў блоку інструментаў "Лічба" ў адмысловым поле фарматавання выстаўляем працэнтны фармат. Пасля гэтага вынік падліку адноснага адхіленні адлюстроўваецца ў працэнтах.
- Аналагічную аперацыю па падліку адноснага адхіленні праробліваем і з дадзенымі з ужываннем згладжвання за 3 месяцы. Толькі ў гэтым выпадку для разліку ў якасці дзеліва выкарыстоўваем іншы слупок табліцы, які ў нас мае назву "АБС. Адкл (3м)". Затым перакладаем лікавыя значэння ў працэнтны выгляд.
- Пасля гэтага вылічваем сярэднія значэння для абедзвюх калонак з адносным адхіленнем, як і раней выкарыстоўваючы для гэтага функцыю СРЗНАЧ. Бо для разліку ў якасці аргументаў функцыі мы бярэм працэнтныя велічыні, то дадатковую канвертаванне вырабляць не трэба. Аператар на выхадзе выдае вынік ужо ў працэнтных фармаце.
- Цяпер мы падышлі да разліку сярэдняга квадратычнай адхіленні. Гэты паказчык дазволіць нам непасрэдна параўнаць якасць разліку пры выкарыстанні згладжвання за два і за тры месяцы. У нашым выпадку сярэдняе квадратычны адхіленне будзе роўна пні квадратнага з сумы квадратаў рознасцяў фактычнай выручкі і слізгальнай сярэдняй, дзеленай на колькасць месяцаў. Для таго, каб правесці разлік у праграме, нам трэба будзе скарыстацца цэлым шэрагам функцый, у прыватнасці КОРАНЬ, СУММКВРАЗН і кошт. Напрыклад, для разліку сярэдняга квадратычнай адхіленні пры выкарыстанні лініі згладжвання за два месяцы ў маі будзе ў нашым выпадку прымяняцца формула наступнага выгляду:
= КОРАНЬ (СУММКВРАЗН (B6: B12; C6: C12) / кошт (B6: B12))Капіюем яе ў іншыя вочкі слупка з разлікам сярэдняга квадратычнай адхіленні дапамогай маркера запаўнення.
- Аналагічную аперацыю па разліку сярэдняга квадратычнай адхіленні выконваем і для слізгальнай сярэдняй за 3 месяцы.
- Пасля гэтага разлічваем сярэдняе значэнне за ўвесь перыяд для абодвух гэтых паказчыкаў, ужыўшы функцыю СРЗНАЧ.
- Вырабячы параўнанне разлікаў метадам слізгальнай сярэдняй са згладжваннем ў 2 і 3 месяцы па такіх паказчыках, як абсалютная адхіленне, адноснае адхіленне і сярэднеквадратовае адхіленне, можна з упэўненасцю сказаць, што згладжванне за два месяцы дае больш дакладныя вынікі, чым прымяненне згладжвання за тры месяцы. Пра гэта кажа тое, што вышэйзгаданыя паказчыкі па двухмесячны зменнаму сярэдняму, менш, чым па трохмесячнаму.
- Такім чынам, прагназуемы паказчык даходу прадпрыемства за снежань складзе 990,4 тыс. Рублёў. Як бачым, гэта значэнне цалкам супадае з тым, якое мы атрымалі, вырабляючы разлік з дапамогай інструментаў пакета аналізу.
Урок: Майстар функцый у Эксэля
Мы вырабілі разлік прагнозу пры дапамозе метаду слізгальнай сярэдняй двума спосабамі. Як бачым, дадзеную працэдуру нашмат прасцей выканаць з дапамогай інструментаў пакета аналізу. Тым не менш некаторыя карыстальнікі не заўсёды давяраюць аўтаматычнага разліку і аддаюць перавагу для вылічэнняў выкарыстоўваць функцыю СРЗНАЧ і спадарожныя аператары для праверкі найбольш дакладнага варыянту. Хоць, калі ўсё зроблена правільна, на выхадзе вынік разлікаў павінен атрымацца цалкам аднолькавым.
