Het afvlakken van Excel-diagramgegevens met een vast voortschrijdend gemiddelde werkt wanneer u ervaring hebt met de gegevens en hoe variabel deze zijn. Maar als de gegevens veranderen of nieuw zijn, willen u of het management misschien experimenteren met een ander aantal perioden in het afvlakkingsgemiddelde.

In dit voorbeeld wordt een OFFSET-formule gebruikt om een gemiddelde te maken over een dynamisch bereik. U voert het aantal maanden in dat u wilt gebruiken voor een afvlakperiode en het AVERAGE en OFFSET berekenen over hoeveel cellen het gemiddelde moet worden berekend.

Voordat u het AVERAGE dynamisch maakt, laten we eerst begrijpen hoe OFFSET werkt. In de volgende figuur bevat cel G26 de formule voor het afvlakken van de gegevens in rij 20,

=AVERAGE(OFFSET(G20,0,0,1,-3))

Deze formule is een tussenstap om u te helpen begrijpen hoe de dynamische formule werkt. De OFFSET functie geeft een bereik terug waarover AVERAGE zal werken. De kenmerken van OFFSET zijn,

OFFSET(referentie,rijen,kolommen,,)

In dit voorbeeld begint het bereik dat OFFSET berekent bij G20 en heeft het nul rij- of kolom-offset. Met andere woorden, de linkerbovenhoek van het bereik dat OFFSET berekent is gewoon G20. Het zal niet worden verschoven of verschoven met rijen of kolommen. Het berekende bereik zal 1 rij hoog zijn en de huidige cel G26 bevatten en nog twee naar links (dat is een -3). Dus, dit berekende bereik zal zijn G20:E20.

Kopieer die formule in G26 naar rechts en je zult zien dat het gemiddelde wordt berekend van de gegevenscel erboven in rij 20 en de vorige twee cellen naar links.

Dus, hier is de truc die dit een dynamisch en gemiddelde zal maken over een bereik dat je kiest. Wat als u een celverwijzing vervangt door die -3 in de formule?

In de onderstaande figuur is de formule in cel G26 gewijzigd zodat de wordt gespecificeerd door het getal in cel E24,

AVERAGE(OFFSET(G20,0,0,1,-$E$24))

$E$24 is de groen gearceerde cel. Het getal dat u in cel E24 typt, bepaalt de breedte van de cellen die worden gebruikt om het gemiddelde te berekenen.

Op dit punt kunt u een spinner of een vervolgkeuzemenu gebruiken om uw gebruiker te laten kiezen hoe breed het afvlakkingsgemiddelde moet worden gemaakt. Het resultaat van hun keuze moet in cel E24 terechtkomen.

Download de afvlakking Excel Grafiekgegevens met vaste of dynamische afvlakking

Er is één probleem met deze regeling. Als de gebruiker in E24 een groter getal invoert dan er cellen links van het getal zijn, zal OFFSET ook lege cellen en het label omvatten. Dit zal een onjuist gemiddelde opleveren. Als E24 zo groot is dat OFFSET buiten het blad gaat, geeft AVERAGE een fout. Wat moeten we doen? We hebben een oplossing nodig die niet alleen een fout stopt, zoals ISERROR, we hebben er een nodig die onjuiste antwoorden stopt.

In deze volgende figuur is de formule in cel G26 aangepast. Rij 25 heeft een reeks getallen die de grenzen zijn voor geldige reeksen aan de linkerkant. De nieuwe formule in cel G26 kijkt naar rij 25 en als het getal in cel E24 groter is dan de limiet in rij 25, dan wordt NA() geproduceerd. Deze nieuwe foutcorrigerende formule in G26 is,

=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))

Wanneer een Excel-diagram NA() verwijst in een cel, wordt er niets geplot. Dat voorkomt dat de afvlakkingslijn tot nul daalt.

Problemen met het afvlakken van gegevens en hoe ze te omzeilen

Nu kunt u uw gebruiker elke gewenste afvlakkingsperiode laten proberen. Echter, (er lijkt altijd een “maar” of een “maar, in dit geval” te zijn) er is een probleem met het afvlakken van gegevens. Smoothing haalt de volatiliteit eruit, maar het betekent ook dat je geen richtingsveranderingen, buigpunten of nieuwe trends zult zien tot ongeveer halverwege de smoothing periode. Je loopt achter op de curve.

Het beste van beide werelden is om een gewogen gemiddelde afvlakkingsfunctie te gebruiken. Op die manier kunt u meer gewicht geven aan kortetermijngegevens zodat u trends ziet, maar u kunt afvlakken op basis van oudere gegevens om een deel van de volatiliteit te elimineren. Dit klinkt als een goed voorbeeld voor een ander artikel, Afvlakken van Excel-grafiekgegevens met behulp van een gewogen gemiddelde.

Download het voorbeeldbestand voor het afvlakken van Excel-grafiekgegevens met behulp van een bewegend of dynamisch afvlakken

Gerelateerde artikelen,

• Afvlakken van Excel-grafiekgegevens met behulp van een bewegend gemiddelde
• Afvlakken van Excel-grafiekgegevens met behulp van een gewogen gemiddelde