Maak een grafiek met gemiddelde / doellijnen in Excel

In Excel is het een veelvoorkomende taak om een horizontale of verticale lijn aan een grafiek toe te voegen, waarbij de horizontale of verticale lijn een bepaalde doel- of gemiddelde waarde kan weergeven. Dit type lijn maakt het eenvoudig om de werkelijke waarden te vergelijken met de doel- of gemiddelde waarde, en met behulp van deze lijn kunt u gemakkelijk zien waar de waarden boven of onder deze referentiewaarde liggen, zoals te zien is in de onderstaande schermafbeeldingen. In dit artikel zal ik uitleggen hoe u een kolom- of staafdiagram maakt met een gemiddelde of doellijn in Excel.

Kolomdiagram met horizontale lijn	Staafdiagram met verticale lijn

• Maak een kolomdiagram met een gemiddelde of doellijn horizontaal
• Voeg een doellijn met verschillende waarden toe aan een kolomdiagram
• Maak dynamisch een kolomdiagram met een gemiddelde lijn door middel van radioknoppen
• Maak een staafdiagram met een gemiddelde of doellijn verticaal
• Voeg een doellijn met verschillende waarden toe aan een staafdiagram
• Voeg een doel-/gemiddelde lijn toe aan een kolom- of staafdiagram met een handige functie
• Download Voorbeeldbestand Grafiek Met Gemiddelde / Doellijn

Maak een kolomdiagram met een gemiddelde of doellijn horizontaal

Om een kolomdiagram met een gemiddelde of doellijn horizontaal te maken, volg dan de volgende stappen:

1. Als u een kolomdiagram wilt maken met een gemiddelde lijn, moet u eerst de gemiddelde waarde van de lijst met gegevens berekenen. Voer de volgende formule in een lege cel in en sleep vervolgens de automatische vulgreep om de formule naar andere cellen te kopiëren, zie screenshot:

2. Selecteer vervolgens het gegevensbereik inclusief de gemiddelde waarden, en klik op Invoegen > Invoegen Kolom- of Staafdiagram > Geclusterde Kolom, zie screenshot:

3. Er wordt een kolomdiagram ingevoegd in het werkblad. U kunt onnodige elementen verwijderen, zoals de titel van de grafiek, rasterlijnen, indien nodig, zie screenshots:

4. Klik vervolgens met de rechtermuisknop op de gemiddelde gegevensreeks (oranje balken) en kies Type Gegevensreeks Wijzigen uit het contextmenu, zie screenshot:

5. In het dialoogvenster Type Grafiek Wijzigen, onder de keuzelijst Kies het grafiektype en de as voor uw gegevensreeks, selecteer Lijngrafiek uit de vervolgkeuzelijst Grafiektype voor de gemiddelde gegevensreeks, zie screenshot:

6. Klik vervolgens op de knop OK en u krijgt de grafiek zoals in de onderstaande schermafbeelding te zien is:

7. Vervolgens kunt u een gegevenslabel toevoegen aan deze gemiddelde lijn. Dubbelklik om het meest rechtse datapunt van de lijn te selecteren, zie screenshot:

8. Klik vervolgens op Grafiekelementen, vink het vak Gegevenslabels aan, en het gegevenslabel wordt ingevoegd voor de gemiddelde lijn, zie screenshot:

Voeg een doellijn met verschillende waarden toe aan een kolomdiagram

Soms wilt u mogelijk de werkelijke waarden vergelijken met de doelwaarden die verschillend zijn voor elke rij. In dit geval, om de doelwaarden duidelijker te visualiseren, kan het onderstaande type grafiek u helpen.

Volg deze stappen om dit type grafiek te maken:

1. Selecteer eerst het gegevensbereik, en klik vervolgens op Invoegen > Invoegen Kolom- of Staafdiagram > Geclusterde Kolom om de grafiek in te voegen.

2. Klik vervolgens met de rechtermuisknop op de doelgegevensreeks (oranje balken), en kies Type Gegevensreeks Wijzigen uit het contextmenu. In het dialoogvenster Type Grafiek Wijzigen, kies Lijngrafiek voor de doelgegevensreeks uit de vervolgkeuzelijst Grafiektype, zie screenshot:

3. Klik vervolgens op de knop OK, en dubbelklik vervolgens op de doellijn, en kies Gegevensreeks Opmaak, zie screenshot:

4. In het geopende venster Gegevensreeks Opmaak, onder het tabblad Opvulling & Lijn, selecteer Geen lijn in de sectie Lijn, zie screenshot:

5. Nog steeds in het tabblad Opvulling & Lijn, ga naar de sectie Markering, selecteer Ingebouwd in de sectie Marker Opties, en selecteer vervolgens de horizontale balk in de vervolgkeuzelijst Type, en stel tot slot een grootte in die overeenkomt met de breedte van uw balken in het vak Grootte, zie screenshot:

6. Stel vervolgens een kleur in voor de doellijnen zoals u wilt in de sectie Opvulling, zie screenshot:

7. Ten slotte kunt u ongewenste elementen verwijderen, zoals de titel van de grafiek, rasterlijnen, indien nodig.

Maak dynamisch een kolomdiagram met een gemiddelde lijn door middel van radioknoppen

Als u een grote tabel heeft met de verkoop van producten in meerdere jaren, en u wilt nu de gemiddelde lijn voor elk jaar in een grafiek tonen. Om deze taak af te handelen, kunt u een grafiek met een gemiddelde lijn gebruiken door op de radioknop te klikken zoals in de onderstaande demo te zien is. Wanneer u het bijbehorende jaar selecteert, wordt de grafiek met de gemiddelde lijn dynamisch weergegeven.

Om deze dynamische grafiek te maken, moet u eerst verschillende radioknoppen invoegen, vervolgens een hulptabel met gegevens maken waarop de grafiek gebaseerd is, en tot slot een grafiek koppelen aan de radioknop. Volg de volgende stappen:

Voeg eerst verschillende radioknoppen in

1. Klik op Ontwikkelaar > Invoegen > Keuzerondje (Formuliercontrol) en teken vervolgens vier keuzerondjes en bewerk de tekst naar wens, zie screenshots:

2. Klik vervolgens met de rechtermuisknop op het eerste keuzerondje en kies Formaat Controleren uit het contextmenu, zie screenshot:

3. In het geopende dialoogvenster Formaat Object, onder het tabblad Besturing, selecteer een cel om het keuzerondje te koppelen in het tekstvak Cel koppeling, en klik op OK om het te sluiten, zie screenshot:

Voeg vervolgens een hulptabel met gegevens in om de grafiek op te baseren

4. Kopieer de productnamen uit de originele tabel naar een nieuw bereik, en pas vervolgens de volgende formule toe in een lege cel naast uw nieuwe gekopieerde gegevens, en sleep de vulgreep om het resultaat te krijgen, zie screenshot:

5. Nu, wanneer u op een keuzerondje klikt, worden de gegevens van het corresponderende jaar automatisch weergegeven zoals in de onderstaande demo te zien is:

6. Bereken vervolgens de gemiddelde waarden met behulp van de volgende formule, zie screenshot:

Maak vervolgens de grafiek op basis van de nieuwe hulptabel met gegevens

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

Maak een staafdiagram met een gemiddelde of doellijn verticaal

In deze sectie wordt uitgelegd hoe u een staafdiagram maakt met een gemiddelde of doellijn verticaal, volg deze stappen:

1. Maak eerst een hulptabel met gegevens, voer X en Y in twee cellen in, en bereken vervolgens de gemiddelde waarden in cel D2 en D3, zie screenshot:

2. Voer vervolgens de getallen 0 en 1 in cel E2 en E3 in voor de Y-waarden, zie screenshot:

3. Selecteer vervolgens het originele gegevensbereik, en klik op Invoegen > Invoegen Kolom- of Staafdiagram > Geclusterde Staaf, zie screenshot:

4. Er is een staafdiagram ingevoegd in het werkblad, klik vervolgens met de rechtermuisknop op de staaf en kies Gegevens Selecteren uit het contextmenu, zie screenshot:

5. Klik in het pop-upvenster Gegevensbron Selecteren op de knop Toevoegen, zie screenshot:

6. Voer in het dialoogvenster Reeks Bewerken een reeksernaam in het tekstvak Reeksernaam in, en selecteer vervolgens de cellen met de X-waarden (D2:D3 in dit voorbeeld) in het tekstvak Reekswaarden, zie screenshot:

7. Klik op OK > OK om de dialoogvensters te sluiten, en de nieuwe gegevensreeks is nu toegevoegd aan uw staafdiagram (twee oranje balken), klik vervolgens met de rechtermuisknop op de oranje balk en kies Type Gegevensreeks Wijzigen, zie screenshot:

8. Selecteer in het pop-upvenster Type Grafiek Wijzigen de optie Spreiding met Gladde Lijnen uit de vervolgkeuzelijst Grafiektype voor de gemiddelde gegevensreeks, zie screenshot:

9. Klik vervolgens op OK om het dialoogvenster te sluiten, en de nieuwe gegevensreeks wordt omgezet in een datapunt langs de primaire Y-as, klik vervolgens met de rechtermuisknop op de staaf, en kies Gegevens Selecteren uit het contextmenu, zie screenshot:

10. Klik in het dialoogvenster Gegevensbron Selecteren in de lijst Legenda-items (Reeksen) op de optie Gemiddelde, en klik vervolgens op de knop Bewerken, zie screenshot:

11. Er wordt een promptvenster Reeks Bewerken gepop-upd, selecteer in het vak Reeks X-waarden de twee X-cellen (D2:D3), en in het vak Reeks Y-waarden, selecteer twee Y-cellen (E2:E3), zie screenshot:

12. Klik op OK > OK om de dialoogvensters te sluiten, en u ziet dat er een verticale gemiddelde lijn is toegevoegd aan de grafiek, zie screenshot:

13. Klik vervolgens met de rechtermuisknop op de secundaire verticale as (de rechteras), en kies As Opmaak uit het contextmenu, zie screenshot:

14. In het venster As Opmaak, onder het tabblad As Opties, in de sectie As Opties, typ 1.0 in het vak Maximum grens, en u krijgt de grafiek zoals in de onderstaande schermafbeelding te zien is:

15. Verwijder ten slotte de secundaire verticale as en de ongewenste rasterlijnen indien nodig, nu is een staafdiagram met een verticale gemiddelde lijn voltooid. Zie screenshot:

Voeg een doellijn met verschillende waarden toe aan een staafdiagram

Om de doellijn met verschillende waarden toe te voegen aan een staafdiagram zoals te zien is in de onderstaande schermafbeeldingen, volg dan de volgende stappen:

1. Selecteer het gegevensbereik, en klik op Invoegen > Invoegen Kolom- of Staafdiagram > Geclusterde Staaf om een staafdiagram in te voegen, zie screenshot:

2. Klik vervolgens met de rechtermuisknop op de doelgegevensreeks (oranje balken), en kies Gegevensreeks Opmaak uit het contextmenu, zie screenshot:

3. In het venster Gegevensreeks Opmaak, onder het tabblad Reeks Opties, wijzig de overlap van de reeks naar 100%, zie screenshot:

4. Nog steeds in het venster Gegevensreeks Opmaak, ga naar het tabblad Opvulling & Lijn, selecteer Geen opvulling in de sectie Opvulling, zie screenshot:

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

• Specificeer een kleur die u nodig hebt uit de vervolgkeuzelijst Kleur;
• Stel de vervaging in op 0 pt;
• Pas vervolgens de hoek aan naar 0°;
• Wijzig ten slotte de afstand naar 2pt zoals u nodig hebt.

6. Ga nu terug naar het tabblad Reeks Opties, selecteer Secundaire As in de sectie Reeks Opties, en wijzig de tussenruimte naar 90% zoals u nodig hebt. Zie screenshot:

7. Verwijder ten slotte de ongewenste elementen, zoals de titel van de grafiek, de bovenste X-as, rasterlijnen indien nodig, en u krijgt het staafdiagram met verschillende doellijnen zoals te zien is in de onderstaande schermafbeelding:

Voeg een doel-/gemiddelde lijn toe aan een kolom- of staafdiagram met een handige functie

Download Voorbeeldbestand Grafiek Met Gemiddelde / Doellijn