Grafiek maken met gemiddelde/doellijn in Excel

In Excel is het een veelvoorkomende taak om een horizontale of verticale lijn aan een grafiek toe te voegen; deze lijn kan verwijzen naar een specifieke doel- of gemiddelde waarde. Zo’n referentielijn maakt het eenvoudig om de werkelijke waarden direct te vergelijken met de doel- of gemiddelde waarde. U ziet dan in één oogopslag waar de waarden boven of onder deze referentiewaarde liggen, zoals in de onderstaande schermafbeeldingen wordt weergegeven. In dit artikel leg ik uit hoe u een kolom- of staafdiagram met een gemiddelde- of doellijn maakt in Excel.

Kolomdiagram met horizontale lijn	Staafgrafiek met Verticale lijn

• Maak Kolomdiagram met gemiddelde- of doellijn horizontaal
• Voeg een doellijn toe met Verschillende waarde in een Kolomdiagram
• Maak Kolomdiagram met dynamische gemiddelde lijn door gebruik te maken van Keuzerondje
• Maak Staafgrafiek met gemiddelde- of doellijn verticaal
• Voeg een doellijn toe met Verschillende waarde in een Staafgrafiek
• Voeg met een handige functie een doel-/gemiddelde lijn toe aan een kolom of Staafgrafiek
• Download het voorbeeldbestand Grafiek met gemiddelde/doellijn

Maak Kolomdiagram met gemiddelde- of doellijn horizontaal

Volg de onderstaande stappen om een Kolomdiagram met een horizontale gemiddelde- of doellijn te maken:

1. Wilt u een kolomdiagram met een gemiddelde lijn maken? Bereken dan eerst het gemiddelde van de gegevenslijst. Voer hiervoor de volgende formule in een lege cel in en sleep de automatische vulgreep om de formule naar andere cellen te kopiëren. Zie de schermafbeelding:

2. Selecteer vervolgens het gegevensbereik, inclusief de gemiddelde waarden, en klik daarna op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde kolom. Zie de schermafbeelding:

3. Er wordt nu een kolomdiagram in het werkblad ingevoegd. U kunt onnodige elementen, zoals de grafiektitel en rasterlijnen, verwijderen indien gewenst. Zie de schermafbeeldingen:

4. Klik met de rechtermuisknop op de gegevensreeks van het gemiddelde (oranje balken) en kies Reeks grafiektype wijzigen in het contextmenu. Zie de schermafbeelding:

5. Selecteer in het dialoogvenster Grafiektype wijzigen onder het Lijstvak Grafiektype en as kiezen voor uw gegevensreeks de optie Lijndiagram uit de keuzelijst Grafiektype voor de gegevensreeks Gemiddelde. Zie de schermafbeelding:

6. Klik daarna op de knop OK. U krijgt dan de grafiek zoals hieronder wordt weergegeven:

7. Voeg daarna het gegevenslabel toe aan deze gemiddelde lijn door dubbel te klikken op het meest rechtse datapunt van de lijn. Zie de schermafbeelding:

8. Klik daarna op Grafiekonderdelen, vink het vakje Gegevenslabels aan en het gegevenslabel wordt toegevoegd aan de gemiddelde lijn. Zie de schermafbeelding:

Voeg een doellijn toe met Verschillende waarde in een Kolomdiagram

Soms wilt u de werkelijke waarde vergelijken met doelwaarden die per rij verschillen. In dat geval is het onderstaande type grafiek ideaal om die doelwaarden helder en overzichtelijk weer te geven.

Volg deze stappen om dit type grafiek te maken:

1. Selecteer eerst het gegevensbereik en klik daarna op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde kolom om de grafiek in te voegen.

2. Klik met de rechtermuisknop op de doelgegevensreeks (oranje balken) en kies Reeks grafiektype wijzigen in het contextmenu. Selecteer in het dialoogvenster Grafiektype wijzigen de optie Lijndiagram voor de doelgegevensreeks uit de keuzelijst Grafiektype. Zie de schermafbeelding:

3. Klik daarna op de knop OK en dubbelklik vervolgens op de doellijn. Kies daarna Gegevensreeks opmaken. Zie de schermafbeelding:

4. Selecteer in het geopende deelvenster Gegevensreeks opmaken onder het tabblad Opvulling en lijn de optie Geen lijn in de sectie Lijn. Zie de schermafbeelding:

5. Blijf op het tabblad Opvulling en lijn en schakel over naar de sectie Markering. Selecteer Ingebouwd in de sectie Markeringsopties en kies vervolgens de horizontale balk in de keuzelijst Type. Stel tot slot een grootte in die overeenkomt met de breedte van uw balken in het vak Grootte. Zie de schermafbeelding:

6. Stel daarna via de sectie Opvulling een gewenste kleur in voor de doellijnen. Zie de schermafbeelding:

7. Verwijder tot slot eventuele ongewenste elementen, zoals de grafiektitel en rasterlijnen, indien gewenst.

Maak Kolomdiagram met dynamische gemiddelde lijn door gebruik te maken van Keuzerondje

Als u een uitgebreide tabel heeft met verkoopcijfers van een product over meerdere jaren en u wilt in een grafiek de gemiddelde lijn per jaar weergeven, klikt u op het keuzerondje – zoals in het onderstaande voorbeeld – om een grafiek met gemiddelde lijn te gebruiken. Zodra u een jaar selecteert, wordt de grafiek met gemiddelde lijn dynamisch bijgewerkt.

Volg de onderstaande stappen om deze dynamische grafiek te maken: voeg eerst enkele keuzerondjes in, maak daarna een hulptabel als basis voor de grafiek en koppel ten slotte de grafiek aan de keuzerondjes.

Voeg eerst enkele Keuzerondje in

1. Klik op Ontwikkelaar > Invoegen > Optieknop (formulierbesturingselement) en teken vervolgens vier keuzerondjes. Pas de tekst naar wens aan. Zie de schermafbeeldingen:

2. Klik met de rechtermuisknop op het eerste keuzerondje en kies Besturingselement opmaken in het contextmenu. Zie de schermafbeelding:

3. In het geopende Object opmaken-dialoogvenster selecteert u onder het tabblad Besturingselement een cel om de keuzerondje aan te koppelen in het tekstvak Cel en klikt u op OK om het venster te sluiten. Zie de schermafbeelding:

Voeg vervolgens een hulptabel in voor het maken van de grafiek op basis van

4. Kopieer de productnamen uit de oorspronkelijke tabel naar een nieuw bereik. Pas daarna de onderstaande formule toe in een lege cel naast uw nieuw gekopieerde gegevens en sleep de vulgreep om het resultaat te verkrijgen. Zie de schermafbeelding:

5. Wanneer u nu op een keuzerondje klikt, wordt het bijbehorende jaartal automatisch weergegeven, zoals in de onderstaande demo te zien is:

6. Vervolgens berekent u de gemiddelde waarden met de onderstaande formule. Zie de schermafbeelding:

Maak ten derde de grafiek op basis van de nieuwe hulptabel

Selecteer vervolgens de nieuw gemaakte gegevens en pas daarna bovenstaande stap 2 tot en met stap 8 van methode 1 toe om een kolomdiagram met een gemiddelde lijn te maken. Zo krijgt u een grafiek met een dynamische gemiddelde lijn. Zie de onderstaande demo:

Maak een Staafgrafiek met een horizontale gemiddelde- of doellijn

In dit gedeelte leest u hoe u een staafgrafiek met een horizontale gemiddelde- of doellijn maakt. Volg hiervoor deze stappen:

1. Maak eerst een hulptabel aan, voer X en Y in twee cellen in en bereken vervolgens de gemiddelde waarden in cel D2 en D3. Zie de schermafbeelding:

2. Voer vervolgens het getal 0 en 1 in cel E2 en E3 in als Y-waarden. Zie de schermafbeelding:

3. Selecteer vervolgens het oorspronkelijke gegevensbereik en klik daarna op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde staaf. Zie de schermafbeelding:

4. Er is nu een staafdiagram ingevoegd in het werkblad. Klik met de rechtermuisknop op de staaf en kies Gegevens selecteren in het contextmenu. Zie de schermafbeelding:

5. In het verschijnende Gegevens selecteren-bron-dialoogvenster klikt u op de knop Toevoegen. Zie de schermafbeelding:

6. Voer vervolgens in het Reeks bewerken-dialoogvenster een reeksnaam in het Reeksnaam-tekstvak in en selecteer daarna de cellen met de X-waarden (D2:D3 in dit voorbeeld) in het Reekswaarden-tekstvak. Zie de schermafbeelding:

7. Klik op OK > OK om de dialoogvensters te sluiten. De nieuwe gegevensreeks is nu toegevoegd aan uw staafgrafiek (twee oranje staven). Klik met de rechtermuisknop op een oranje staaf en kies Reekstype wijzigen. Zie de schermafbeelding:

8. In het verschijnende Reekstype wijzigen-dialoogvenster selecteert u Spreiding met vloeiende lijnen in de keuzelijst Grafiektype voor de gegevensreeks Gemiddelde. Zie de schermafbeelding:

9. Klik vervolgens op OK om het dialoogvenster te sluiten. De nieuwe gegevensreeks is nu omgezet naar een datapunt langs de primaire Y-as. Klik met de rechtermuisknop op de staaf en kies daarna Gegevens selecteren in het contextmenu. Zie de schermafbeelding:

10. In het Gegevens selecteren-bron-dialoogvenster klikt u in het lijstvak Bereik van de reeksnaam op de optie Gemiddelde en vervolgens op de knop Bewerken. Zie de schermafbeelding:

11. Vervolgens verschijnt het dialoogvenster Reeks bewerken. Selecteer in het vak X-as reeks waarde de twee X-cellen (D2:D3) en in het vak Y-as reeks waarde de twee Y-cellen (E2:E3). Zie de schermafbeelding:

12. Klik op OK > OK om de dialoogvensters te sluiten. U ziet nu dat er een horizontale gemiddelde lijn aan de grafiek is toegevoegd. Zie de schermafbeelding:

13. Klik met de rechtermuisknop op de secundaire verticale as (de rechteras) en kies As opmaken in het contextmenu. Zie de schermafbeelding:

14. In het geopende As opmaken-deelvenster typt u onder het tabblad Asopties, in de sectie Asopties, het getal 1,0 in het vak Maximumgrens. U krijgt dan de grafiek zoals hieronder wordt getoond:

15. Ten slotte kunt u, indien gewenst, de secundaire verticale as en de ongewenste rasterlijnen verwijderen. De staafgrafiek met een horizontale gemiddelde lijn is nu klaar. Zie de schermafbeelding:

Voeg een doellijn toe met Verschillende waarde in een Staafgrafiek

Volg de onderstaande stappen om een doellijn toe te voegen met Verschillende waarde in een Staafgrafiek, zoals in de onderstaande schermafbeeldingen wordt getoond:

1. Selecteer het gegevensbereik en klik vervolgens op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde staaf om een staafgrafiek in te voegen. Zie de schermafbeelding:

2. Klik vervolgens met de rechtermuisknop op de doelgegevensreeks (oranje staven) en kies Gegevensreeks opmaken in het contextmenu. Zie de schermafbeelding:

3. In het geopende Gegevensreeks opmaken-deelvenster wijzigt u onder het tabblad Reeksopties de reeksoverlapping naar 100 %. Zie de schermafbeelding:

3. Blijf in het Gegevensreeks opmaken-deelvenster en schakel over naar het tabblad Opvulling en lijn. Selecteer Geen opvulling in de sectie Opvulling. Zie de schermafbeelding:

5. Ga vervolgens naar het tabblad Effecten en stel onder de sectie Schaduw de volgende instellingen in:

• Geef een gewenste kleur op via de Kleur-keuzelijst;
• Stel de vervaging in op 0 pt;
• Pas vervolgens de hoek aan naar 0°;
• Stel tot slot de afstand in op 2 pt, zoals u dat nodig heeft.

6. Schakel nu opnieuw over naar het tabblad Reeksopties, selecteer Secundaire as in de sectie Reeksopties en wijzig de afstandsbreedte naar 90 % indien gewenst. Zie de schermafbeelding:

7. Verwijder ten slotte naar behoefte ongewenste elementen, zoals de grafiektitel, de bovenste X-as en rasterlijnen. Zo verkrijgt u de staafgrafiek met verschillende doellijnen, zoals te zien is op de onderstaande schermafbeelding:

Voeg met een handige functie een doel-/gemiddeldelijn toe aan een kolom- of Staafgrafiek

Download het voorbeeldbestand Grafiek met gemiddelde/doellijn