Kopsavilkums
Lai aprēķinātu slīdošo vai slīdošo vidējo, varat izmantot vienkāršu formulu, kuras pamatā ir funkcija AVERAGE ar relatīvām atsaucēm. Parādītajā piemērā formula E7 ir:
=AVERAGE(C5:C7)
Formulu nokopējot uz leju, tā aprēķina 3 dienu slīdošo vidējo vērtību, pamatojoties uz pašreizējās dienas un divu iepriekšējo dienu pārdošanas vērtību.
Zemāk ir elastīgāka opcija, kuras pamatā ir funkcija OFFSET, kas apstrādā mainīgus periodus.
Par mainīgajiem vidējiem rādītājiem
Slīdošais vidējais (saukts arī par slīdošo vidējo) ir vidējais, pamatojoties uz datu apakškopām noteiktos intervālos. Aprēķinot vidējo vērtību ar noteiktiem intervāliem, dati tiek izlīdzināti, samazinot nejaušu svārstību ietekmi. Tas ļauj vieglāk redzēt kopējās tendences, it īpaši diagrammā. Jo lielāks intervāls tiek izmantots, lai aprēķinātu slīdošo vidējo rādītāju, jo vairāk tas notiek, jo katrā aprēķinātajā vidējā vērtībā tiek iekļauti vairāk datu punktu.
Paskaidrojums
Piemērā redzamās formulas izmanto funkciju AVERAGE ar relatīvu atsauci, kas iestatīta katram konkrētam intervālam. 3 dienu slīdošo vidējo rādītāju E7 aprēķina, vidēji ievadot diapazonu, kas ietver pašreizējo dienu un divas iepriekšējās dienas šādi:
=AVERAGE(C5:C7) // 3-day average
5 un 7 dienu vidējos rādītājus aprēķina vienādi. Katrā gadījumā AVERAGE sniegtais diapazons tiek palielināts, iekļaujot nepieciešamo dienu skaitu:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Visas formulas izmanto relatīvo atsauci diapazonam, kas tiek piegādāts funkcijai AVERAGE. Formulas kopējot kolonnā, diapazons katrā rindā mainās, iekļaujot katram vidējam rādītājam nepieciešamās vērtības.
Kad vērtības ir attēlotas līniju diagrammā, izlīdzināšanas efekts ir skaidrs:
Nepietiek datu
Ja sākat formulas tabulas pirmajā rindā, dažām pirmajām formulām nebūs pietiekami daudz datu, lai aprēķinātu pilnīgu vidējo rādītāju, jo diapazons paplašināsies virs pirmās datu rindas:
Tas var būt vai nav problēma, atkarībā no darblapas struktūras un no tā, vai ir svarīgi, lai visi vidējie rādītāji būtu balstīti uz vienādu vērtību skaitu. Funkcija AVERAGE automātiski ignorēs teksta vērtības un tukšas šūnas, tāpēc tā turpinās aprēķināt vidējo vērtību ar mazākām vērtībām. Tāpēc tas "darbojas" E5 un E6.
Viens veids, kā skaidri norādīt nepietiekamus datus, ir pārbaudīt pašreizējā rindas numuru un pārtraukt ar #NA, ja vērtības ir mazākas par n. Piemēram, 3 dienu vidējam rādītājam varat izmantot:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Formulas pirmā daļa vienkārši ģenerē "normalizētu" rindas numuru, sākot ar 1:
ROW()-ROW($C$5)+1 // relative row number
5. rindā rezultāts ir 1, 6. rindā rezultāts ir 2 utt.
Kad pašreizējais rindas numurs ir mazāks par 3, formula atgriež # N / A. Pretējā gadījumā formula atgriež slīdošo vidējo līmeni kā iepriekš. Tas atdarina Moving Average Analysis Toolpak versijas uzvedību, kas izsniedz # N / A, līdz tiek sasniegts pirmais pilnais periods.
Tomēr, palielinoties periodu skaitam, galu galā jums beigsies rindas virs datiem un nevarēsit ievadīt nepieciešamo diapazonu AVERAGE. Piemēram, ar darblapu, kā parādīts attēlā, nevar iestatīt kustīgu 7 dienu vidējo rādītāju, jo nevar ievadīt diapazonu, kas pārsniedz 6 rindas virs C5.
Mainīgi periodi ar OFFSET
Elastīgāks veids, kā aprēķināt slīdošo vidējo rādītāju, ir funkcija OFFSET. OFFSET var izveidot dinamisku diapazonu, kas nozīmē, ka mēs varam iestatīt formulu, kurā periodu skaits ir mainīgs. Vispārīgā forma ir:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kur n ir katrā vidējā rādītājā iekļaujamo periodu skaits. Tāpat kā iepriekš, OFFSET atgriež diapazonu, kas tiek pārsūtīts uz funkciju AVERAGE. Zemāk jūs varat redzēt šo formulu darbībā, kur "n" ir nosauktais diapazons E2. Sākot ar šūnu C5, OFFSET izveido diapazonu, kas sniedzas atpakaļ uz iepriekšējām rindām. To panāk, izmantojot augstumu, kas vienāds ar negatīvu n. Kad E5 tiek mainīts uz citu skaitli, slīdošais vidējais lielums pārrēķina visas rindas:
Lejupielādētā E5 formula ir šāda:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Tāpat kā sākotnējā formula iepriekš, versijai ar OFFSET arī būs problēma ar nepietiekamiem datiem dažās pirmajās rindās atkarībā no tā, cik periodu ir norādīts E5.
Parādītajā piemērā vidējie rādītāji tiek veiksmīgi aprēķināti, jo funkcija AVERAGE automātiski ignorē teksta vērtības un tukšas šūnas, un virs C5 nav citu skaitlisko vērtību. Tātad, kamēr E5 vidējais rādītājs ir C1: C5, vidējai vērtībai ir tikai viena vērtība - 100. Tomēr, periodiem palielinoties, OFFSET turpinās veidot diapazonu, kas pārsniedz datu sākumu, galu galā nokļūstot darblapas augšdaļā un atgriežot kļūdu #REF.
Viens no risinājumiem ir diapazona lieluma noteikšana pieejamo datu punktu skaitam. To var izdarīt, izmantojot MIN funkciju, lai ierobežotu augstumam izmantoto skaitli, kā redzams zemāk:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Tas izskatās diezgan biedējoši, bet patiesībā ir diezgan vienkārši. Mēs ierobežojam OFFSET augstumu, izmantojot MIN funkciju:
MIN(ROW()-ROW($C$5)+1,n)
MIN iekšpusē pirmā vērtība ir relatīvs rindas numurs, ko aprēķina ar:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Otrā vērtība, kas piešķirta MIN, ir periodu skaits, n. Kad relatīvais rindas numurs ir mazāks par n, MIN atgriež pašreizējā rindas numuru uz OFFSET augstumam. Kad rindas numurs ir lielāks par n, MIN atgriež n. Citiem vārdiem sakot, MIN vienkārši atgriež mazāko no abām vērtībām.
Jauka OFFSET opcijas iezīme ir tā, ka n var viegli mainīt. Ja mēs mainīsim n uz 7 un noformēsim rezultātus, mēs iegūsim šādu diagrammu:
Piezīme. Nepatīkamais ar iepriekš aprakstītajām OFFSET formulām ir tāds, ka tie nedarbosies Google izklājlapās, jo izklājlapu funkcija OFFSET nepieļaus negatīvu augstuma vai platuma vērtību. Pievienotajā izklājlapā ir Google formāta apkarošanas formulas.
