Samenvatting
Om een voortschrijdend of voortschrijdend gemiddelde te berekenen, kunt u een eenvoudige formule gebruiken op basis van de GEMIDDELDE-functie met relatieve verwijzingen. In het getoonde voorbeeld is de formule in E7:
=AVERAGE(C5:C7)
Terwijl de formule naar beneden wordt gekopieerd, wordt een 3-dagen voortschrijdend gemiddelde berekend op basis van de verkoopwaarde voor de huidige dag en de twee voorgaande dagen.
Hieronder is een meer flexibele optie gebaseerd op de OFFSET-functie die variabele perioden verwerkt.
Over voortschrijdende gemiddelden
Een voortschrijdend gemiddelde (ook wel voortschrijdend gemiddelde genoemd) is een gemiddelde dat is gebaseerd op subsets van gegevens met bepaalde intervallen. Door op specifieke intervallen een gemiddelde te berekenen, worden de gegevens gladgestreken door de impact van willekeurige fluctuaties te verminderen. Dit maakt het gemakkelijker om algemene trends te zien, vooral in een grafiek. Hoe groter het interval dat wordt gebruikt om een voortschrijdend gemiddelde te berekenen, hoe meer afvlakking er optreedt, aangezien er meer gegevenspunten in elk berekend gemiddelde worden opgenomen.
Uitleg
De formules die in het voorbeeld worden getoond, gebruiken allemaal de functie GEMIDDELDE met een relatieve referentie die is ingesteld voor elk specifiek interval. Het 3-daagse voortschrijdend gemiddelde in E7 wordt berekend door AVERAGE een bereik te geven dat de huidige dag en de twee voorgaande dagen als volgt omvat:
=AVERAGE(C5:C7) // 3-day average
De 5-daagse en 7-daagse gemiddelden worden op dezelfde manier berekend. In elk geval wordt het bereik dat aan AVERAGE wordt verstrekt, vergroot met het vereiste aantal dagen:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Alle formules gebruiken een relatieve verwijzing voor het bereik dat aan de functie GEMIDDELD wordt geleverd. Terwijl de formules door de kolom worden gekopieerd, verandert het bereik bij elke rij om de waarden op te nemen die voor elk gemiddelde nodig zijn.
Wanneer de waarden in een lijndiagram zijn uitgezet, is het afvlakkingseffect duidelijk:
Niet genoeg data
Als u de formules in de eerste rij van de tabel start, hebben de eerste paar formules niet genoeg gegevens om een volledig gemiddelde te berekenen, omdat het bereik zich boven de eerste rij met gegevens uitstrekt:
Dit kan al dan niet een probleem zijn, afhankelijk van de structuur van het werkblad en of het belangrijk is dat alle gemiddelden zijn gebaseerd op hetzelfde aantal waarden. De GEMIDDELDE-functie negeert automatisch tekstwaarden en lege cellen, dus het zal doorgaan met het berekenen van een gemiddelde met minder waarden. Dit is waarom het "werkt" in E5 en E6.
Een manier om duidelijk aan te geven dat er onvoldoende gegevens zijn, is door het huidige rijnummer te controleren en af te breken met #NA als er minder dan n waarden zijn. Voor het gemiddelde van 3 dagen kunt u bijvoorbeeld gebruiken:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Het eerste deel van de formule genereert eenvoudig een "genormaliseerd" rijnummer, beginnend met 1:
ROW()-ROW($C$5)+1 // relative row number
In rij 5 is het resultaat 1, in rij 6 is het resultaat 2, enzovoort.
Als het huidige rijnummer kleiner is dan 3, retourneert de formule # N / A. Anders retourneert de formule een voortschrijdend gemiddelde als voorheen. Dit bootst het gedrag na van de Analysis Toolpak-versie van Moving Average, die # N / A uitvoert totdat de eerste volledige periode is bereikt.
Naarmate het aantal perioden toeneemt, zult u uiteindelijk geen rijen boven de gegevens meer hebben en kunt u het vereiste bereik niet binnen AVERAGE invoeren. U kunt bijvoorbeeld geen voortschrijdend gemiddelde over zeven dagen instellen met het werkblad zoals weergegeven, omdat u geen bereik kunt invoeren dat zich 6 rijen boven C5 uitstrekt.
Variabele periodes met OFFSET
Een flexibelere manier om een voortschrijdend gemiddelde te berekenen, is met de functie OFFSET. OFFSET kan een dynamisch bereik creëren, wat betekent dat we een formule kunnen opzetten waarbij het aantal perioden variabel is. De algemene vorm is:
=AVERAGE(OFFSET(A1,0,0,-n,1))
waarbij n het aantal perioden is dat in elk gemiddelde moet worden opgenomen. Zoals hierboven, geeft OFFSET een bereik terug dat wordt doorgegeven aan de GEMIDDELDE functie. Hieronder ziet u deze formule in actie, waarbij "n" het benoemde bereik E2 is. Beginnend bij cel C5, construeert OFFSET een bereik dat teruggaat tot vorige rijen. Dit wordt bereikt door een hoogte te gebruiken die gelijk is aan negatief n. Wanneer E5 wordt gewijzigd in een ander getal, wordt het voortschrijdend gemiddelde herberekend voor alle rijen:
De formule in E5, gekopieerd, is:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Net als de originele formule hierboven, zal de versie met OFFSET ook het probleem hebben van onvoldoende gegevens in de eerste paar rijen, afhankelijk van het aantal perioden dat in E5 wordt gegeven.
In het getoonde voorbeeld worden de gemiddelden met succes berekend omdat de GEMIDDELDE-functie automatisch tekstwaarden en lege cellen negeert en er geen andere numerieke waarden boven C5 zijn. Dus hoewel het bereik dat is doorgegeven aan GEMIDDELDE in E5 C1: C5 is, is er maar één waarde voor het gemiddelde, 100. Naarmate de perioden toenemen, zal OFFSET echter een bereik blijven creëren dat zich uitstrekt tot boven het begin van de gegevens en uiteindelijk tegenkomt bovenaan het werkblad en retourneert een #REF-fout.
Een oplossing is om de omvang van het bereik te "capteren" op het aantal beschikbare gegevenspunten. Dit kan worden gedaan door de MIN-functie te gebruiken om het aantal dat voor hoogte wordt gebruikt te beperken, zoals hieronder te zien is:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Dit ziet er best eng uit, maar is eigenlijk best simpel. We beperken de hoogte die wordt doorgegeven aan OFFSET met de MIN-functie:
MIN(ROW()-ROW($C$5)+1,n)
Binnen MIN is de eerste waarde een relatief rijnummer, berekend met:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
De tweede waarde die aan MIN wordt gegeven, is het aantal perioden, n. Als het relatieve rijnummer kleiner is dan n, retourneert MIN het huidige rijnummer naar OFFSET voor hoogte. Als het rijnummer groter is dan n, geeft MIN n terug. Met andere woorden, MIN retourneert eenvoudigweg de kleinste van de twee waarden.
Een leuke eigenschap van de OFFSET-optie is dat n eenvoudig kan worden gewijzigd. Als we n veranderen in 7 en de resultaten plotten, krijgen we een grafiek als volgt:
Opmerking: een eigenaardigheid met de bovenstaande OFFSET-formules is dat ze niet werken in Google Spreadsheets, omdat de OFFSET-functie in Spreadsheets geen negatieve waarde voor hoogte of breedte toestaat. De bijgevoegde spreadsheet bevat tijdelijke formules voor Google-spreadsheets.
