Het voortschrijdend gemiddelde berekenen in Excel (eenvoudig, gewogen en exponentieel)

Zoals veel van mijn Excel-tutorials, is deze ook geïnspireerd op een van de vragen die ik van een vriend kreeg. Ze wilde het voortschrijdend gemiddelde in Excel berekenen en ik vroeg haar om het online te zoeken (of er een YouTube-video over te bekijken).

Maar toen besloot ik er zelf een te schrijven (het feit dat ik op de universiteit een beetje een statistieknerd was, speelde ook een ondergeschikte rol).

Voordat ik u vertel hoe u voortschrijdend gemiddelde in Excel kunt berekenen, wil ik u snel een overzicht geven van wat voortschrijdend gemiddelde betekent en welke soorten voortschrijdend gemiddelde er zijn.

Als je naar het gedeelte wilt gaan waar ik laat zien hoe je het voortschrijdend gemiddelde in Excel kunt berekenen, klik dan hier.

Opmerking: ik ben geen expert op het gebied van statistieken en het is niet mijn bedoeling in deze tutorial om alles over voortschrijdende gemiddelden te behandelen. Ik wil u alleen laten zien hoe u voortschrijdende gemiddelden in Excel kunt berekenen (met een korte introductie van wat voortschrijdende gemiddelden betekenen).

Wat is een voortschrijdend gemiddelde?

Ik weet zeker dat je weet wat een gemiddelde waarde is.

Als ik drie dagen aan dagelijkse temperatuurgegevens heb, kun je me gemakkelijk het gemiddelde van de afgelopen drie dagen vertellen (hint: je kunt de GEMIDDELDE functie in Excel gebruiken om dit te doen).

Een voortschrijdend gemiddelde (ook wel voortschrijdend gemiddelde of voortschrijdend gemiddelde genoemd) is wanneer u de tijdsperiode van het gemiddelde hetzelfde houdt, maar blijft bewegen terwijl nieuwe gegevens worden toegevoegd.

Als ik u bijvoorbeeld op dag 3 vraag naar de 3-daags voortschrijdend gemiddelde, geeft u mij de gemiddelde temperatuurwaarde van dag 1, 2 en 3. En als ik u op dag 4 de 3-daags voortschrijdend gemiddelde vraag , geeft u mij het gemiddelde van dag 2, 3 en 4.

Als er nieuwe gegevens worden toegevoegd, houdt u de tijdsperiode (3 dagen) hetzelfde, maar gebruikt u de nieuwste gegevens om het voortschrijdend gemiddelde te berekenen.

Het voortschrijdend gemiddelde wordt veel gebruikt voor technische analyse en veel banken en beursanalisten gebruiken het dagelijks (hieronder is een voorbeeld dat ik van de Market Realist-site heb gekregen).

Een van de voordelen van het gebruik van voortschrijdende gemiddelden is dat u de trend krijgt en schommelingen tot op zekere hoogte gladstrijkt. Als er bijvoorbeeld een echt warme dag is, zou het driedaagse voortschrijdend gemiddelde van de temperatuur er nog steeds voor zorgen dat de gemiddelde waarde is afgevlakt (in plaats van u een echt hoge waarde te tonen die een uitbijter zou kunnen zijn - een een- uit bijvoorbeeld).

Soorten voortschrijdende gemiddelden

Er zijn drie soorten voortschrijdende gemiddelden:

• Eenvoudig voortschrijdend gemiddelde (SMA)
• Gewogen voortschrijdend gemiddelde (WMA)
• Exponentieel voortschrijdend gemiddelde (EMA)

Eenvoudig voortschrijdend gemiddelde (SMA)

Dit is het eenvoudige gemiddelde van de gegevenspunten in de gegeven duur.

In ons dagelijkse temperatuurvoorbeeld, wanneer u gewoon een gemiddelde neemt van de afgelopen 10 dagen, geeft dit het 10-daagse eenvoudig voortschrijdend gemiddelde.

Dit kan worden bereikt door het gemiddelde te nemen van de datapunten in de gegeven duur. In Excel kunt u dit eenvoudig doen met de functie GEMIDDELDE (dit komt later in deze zelfstudie aan bod).

Gewogen voortschrijdend gemiddelde (WMA)

Laten we zeggen dat het weer elke dag koeler wordt en dat u een voortschrijdend 10-daags gemiddelde gebruikt om de temperatuurtrend te krijgen.

De temperatuur van dag 10 is waarschijnlijker een betere indicator van de trend in vergelijking met dag 1 (aangezien de temperatuur elke dag daalt).

We zijn dus beter af als we meer vertrouwen op de waarde van dag 10.

Om dit weer te geven in ons voortschrijdend gemiddelde, kunt u meer gewicht toekennen aan de meest recente gegevens en minder aan gegevens uit het verleden. Zo krijg je nog steeds de trend, maar met meer invloed van de laatste data.

Dit wordt het gewogen voortschrijdend gemiddelde genoemd.

Exponentieel voortschrijdend gemiddelde (EMA)

Het exponentieel voortschrijdend gemiddelde is een type gewogen voortschrijdend gemiddelde waarbij meer gewicht wordt toegekend aan de nieuwste gegevens en exponentieel afneemt voor de oudere gegevenspunten.

Het wordt ook wel het exponentieel gewogen voortschrijdend gemiddelde (EWMA) genoemd.

Het verschil tussen WMA en EMA is dat u met WMA gewichten kunt toekennen op basis van elk criterium. In een voortschrijdend 3-puntsgemiddelde kunt u bijvoorbeeld een gewichtsleeftijd van 60% toewijzen aan het laatste gegevenspunt, 30% aan het middelste gegevenspunt en 10% aan het oudste gegevenspunt.

In EMA wordt een hoger gewicht toegekend aan de laatste waarde en het gewicht wordt exponentieel lager voor eerdere waarden.

Genoeg statistiekles.

Laten we nu een duik nemen en kijken hoe u voortschrijdende gemiddelden in Excel kunt berekenen.

Eenvoudig voortschrijdend gemiddelde (SMA) berekenen met Data Analysis Toolpak in Excel

Microsoft Excel heeft al een ingebouwde tool om de eenvoudige voortschrijdende gemiddelden te berekenen.

Het heet de Toolpakket voor gegevensanalyse.

Voordat je het toolpak Data-analyse kunt gebruiken, moet je eerst controleren of je het in het Excel-lint hebt of niet. De kans is groot dat u een paar stappen moet nemen om het eerst in te schakelen.

Als je de optie Gegevensanalyse al hebt op het tabblad Gegevens, sla dan de onderstaande stappen over en bekijk de stappen voor het berekenen van voortschrijdende gemiddelden.

Klik op het tabblad Gegevens en controleer of u de optie Gegevensanalyse ziet of niet. Als je het niet ziet, volg dan de onderstaande stappen om het beschikbaar te maken in het lint.

1. Klik op het tabblad Bestand
2. Klik op Opties
3. Klik in het dialoogvenster Excel-opties op Invoegtoepassingen
4. Selecteer onderaan het dialoogvenster Excel Add-ins in de vervolgkeuzelijst en klik vervolgens op Go.
5. Vink in het dialoogvenster Add-ins dat wordt geopend de optie Analysis Toolpak aan
6. Klik OK.

De bovenstaande stappen zouden het Data Analysis Toolpack inschakelen en u ziet deze optie nu op het tabblad Gegevens.

Stel dat u de onderstaande dataset heeft en u wilt het voortschrijdend gemiddelde van de laatste drie intervallen berekenen.

Hieronder vindt u de stappen om gegevensanalyse te gebruiken om een ​​eenvoudig voortschrijdend gemiddelde te berekenen:

1. Klik op het tabblad Gegevens
2. Klik op de optie Gegevensanalyse
3. Klik in het dialoogvenster Gegevensanalyse op de optie Zwevend gemiddelde (mogelijk moet u een beetje scrollen om deze te bereiken).
4. Klik OK. Hierdoor wordt het dialoogvenster 'Moving Average' geopend.
5. Selecteer in het invoerbereik de gegevens waarvoor u het voortschrijdend gemiddelde wilt berekenen (B2:B11 in dit voorbeeld)
6. Voer in de optie Interval 3 in (omdat we een voortschrijdend driepuntsgemiddelde berekenen)
7. Voer in het uitvoerbereik de cel in waar u de resultaten wilt hebben. In dit voorbeeld gebruik ik C2 als het uitvoerbereik
8. Klik OK

De bovenstaande stappen geven u het voortschrijdend gemiddelde resultaat zoals hieronder weergegeven.

Merk op dat de eerste twee cellen in kolom C het resultaat hebben als #N/A-fout. Dit komt omdat het een voortschrijdend gemiddelde van drie punten is en ten minste drie gegevenspunten nodig zijn om het eerste resultaat te geven. Dus de werkelijke voortschrijdende gemiddelde waarden beginnen na het derde gegevenspunt.

U zult ook opmerken dat al deze toolpak voor gegevensanalyse een GEMIDDELDE formule op de cellen heeft toegepast. Dus als je dit handmatig wilt doen zonder het Data Analyse toolpack, dan kan dat zeker.

Er zijn echter een paar dingen die gemakkelijker te doen zijn met het data-analysetoolpak. Als u bijvoorbeeld zowel de standaardfoutwaarde als de grafiek van het voortschrijdend gemiddelde wilt krijgen, hoeft u alleen maar een vakje aan te vinken en het maakt deel uit van de uitvoer.

Berekening van voortschrijdende gemiddelden (SMA, WMA, EMA) met formules in Excel

U kunt de voortschrijdende gemiddelden ook berekenen met de formule GEMIDDELDE.

Als alles wat je nodig hebt de voortschrijdend gemiddelde waarde is (en niet de standaardfout of grafiek), kan het gebruik van een formule een betere (en snellere) optie zijn dan het gebruik van de Data Analysis Toolpak.

Ook geeft Data-analyse Toolpak alleen het Simple Moving Average (SMA), maar als u WMA of EMA wilt berekenen, hoeft u alleen op formules te vertrouwen.

Eenvoudig voortschrijdend gemiddelde berekenen met formules

Stel je hebt de dataset zoals hieronder weergegeven en je wilt de 3-punts SMA berekenen:

Voer in cel C4 de volgende formule in:

=GEMIDDELDE(B2:B4)

Kopieer deze formule voor alle cellen en u krijgt de SMA voor elke dag.

Onthoud: wanneer u SMA berekent met formules, moet u ervoor zorgen dat de verwijzingen naar de formule relatief zijn. Dit betekent dat de formule =GEMIDDELDE(B2:B4) of =GEMIDDELDE($B2:$B4) kan zijn, maar niet =GEMIDDELDE($B$2:$B$4) of =GEMIDDELDE(B$2:B$4 ). Het rijnummergedeelte van de verwijzing moet zonder het dollarteken zijn. U kunt hier meer lezen over absolute en relatieve verwijzingen.

Aangezien we een 3-punts Simple Moving Average (SMA) berekenen, zijn de eerste twee cellen (voor de eerste twee dagen) leeg en beginnen we de formule vanaf de derde dag te gebruiken. Als u wilt, kunt u de eerste twee waarden ongewijzigd gebruiken en vanaf de derde de SMA-waarde gebruiken.

Gewogen voortschrijdend gemiddelde berekenen met formules

Voor WMA moet u de gewichten kennen die aan de waarden worden toegekend.

Stel dat u de 3-punts WMA moet berekenen voor de onderstaande dataset, waarbij 60% gewicht wordt gegeven aan de laatste waarde, 30% aan de vorige en 10% van de vorige.

Om dit te doen, voert u de volgende formule in cel C4 in en kopieert u deze voor alle cellen.

=0,6*B4+0,3*B3+0,1*B2

Aangezien we een 3-punts gewogen voortschrijdend gemiddelde (WMA) berekenen, zijn de eerste twee cellen (voor de eerste twee dagen) leeg en beginnen we de formule vanaf de derde dag te gebruiken. Als u wilt, kunt u de eerste twee waarden ongewijzigd gebruiken en vanaf de derde de WMA-waarde gebruiken.

Exponentieel voortschrijdend gemiddelde berekenen met formules

Exponentieel voortschrijdend gemiddelde (EMA) geeft een hoger gewicht aan de laatste waarde en de gewichten worden exponentieel lager voor eerdere waarden.

Hieronder vindt u de formule om de EMA te berekenen voor een voortschrijdend driepuntsgemiddelde:

EMA = [Laatste waarde - Vorige EMA-waarde] * (2 / N+1) + Vorige EMA

… waarbij N in dit voorbeeld 3 zou zijn (omdat we een driepunts-EMA berekenen)

Opmerking: voor de eerste EMA-waarde (als u geen eerdere waarde hebt om EMA te berekenen), neemt u gewoon de waarde zoals deze is en beschouwt u deze als de EMA-waarde. U kunt deze waarde vervolgens gebruiken.

Stel dat u de onderstaande dataset heeft en u wilt de drie-periode EMA berekenen:

Voer in cel C2 dezelfde waarde in als in B2. Dit komt omdat er geen eerdere waarde is om EMA te berekenen.

Voer in cel C3 de onderstaande formule in en kopieer deze voor alle cellen:

=(B3-C2)*(2/4)+C2

In dit voorbeeld heb ik het simpel gehouden en de laatste waarde en vorige EMA-waarde gebruikt om de huidige EMA te berekenen.

Een andere populaire manier om dit te doen, is door eerst het eenvoudige voortschrijdend gemiddelde te berekenen en dit vervolgens te gebruiken in plaats van de werkelijke laatste waarde.

Trendlijn voortschrijdend gemiddelde toevoegen aan een kolomdiagram

Als je een dataset hebt en je maakt er een staafdiagram mee, dan kun je met een paar klikken ook de voortschrijdend gemiddelde trendlijn toevoegen.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Hieronder vindt u de stappen om een ​​staafdiagram te maken met behulp van deze gegevens en een driedelige trendlijn voor voortschrijdend gemiddelde aan dit diagram toe te voegen:

1. Selecteer de dataset (inclusief de headers)
2. Klik op het tabblad Invoegen
3. Klik in de groep Grafiek op het pictogram 'Kolom- of staafdiagram invoegen'.
4. Klik op de optie Geclusterd kolomdiagram. Hiermee wordt de grafiek in het werkblad ingevoegd.
5. Klik met de grafiek geselecteerd op het tabblad Ontwerp (dit tabblad verschijnt alleen als de grafiek is geselecteerd)
6. Klik in de groep Grafieklay-outs op 'Grafiekelement toevoegen'.
7. Beweeg de cursor over de optie 'Trendline' en klik vervolgens op 'Meer trendlijnopties'
8. Selecteer in het deelvenster Trendlijn opmaken de optie 'Moving Average' en stel het aantal perioden in.

Dat is het! De bovenstaande stappen zouden een bewegende trendlijn aan uw kolomdiagram toevoegen.

Als u meer dan één voortschrijdend gemiddelde trendlijn wilt invoegen (bijvoorbeeld één voor 2 perioden en één voor 3 perioden), herhaalt u de stappen van 5 tot 8).

U kunt dezelfde stappen ook gebruiken om een ​​voortschrijdend gemiddelde trendlijn in een lijndiagram in te voegen.

De voortschrijdend gemiddelde trendlijn opmaken

In tegenstelling tot een gewone lijngrafiek, laat een voortschrijdend gemiddelde trendlijn niet veel opmaak toe. Als u bijvoorbeeld een specifiek gegevenspunt op de trendlijn wilt markeren, kunt u dat niet doen.

Een paar dingen die u in de trendlijn kunt opmaken, zijn onder meer:

• Kleur van de lijn. U kunt dit gebruiken om een ​​van de trendlijnen te markeren door alles in de grafiek licht van kleur te maken en de trendlijn eruit te laten springen met een heldere kleur
• De dikte van de lijn
• De transparantie van de lijn

Om de voortschrijdend gemiddelde trendlijn op te maken, klikt u er met de rechtermuisknop op en selecteert u de optie Trendlijn opmaken.

Hierdoor wordt het deelvenster Trendlijn opmaken aan de rechterkant geopend. Dit paneel bevat alle opmaakopties (in verschillende secties - Opvulling en lijn, effecten en trendlijnopties).