Als je op de verkoopafdeling werkt en je leven draait om doelen, dan is deze grafiektechniek iets voor jou. En als je dat niet bent, lees het dan toch om enkele coole Excel-grafiektrucs te leren.
In deze blogpost laat ik je een supermanier zien om een dynamische doellijn in een Excel-diagram te maken, waarmee je je prestaties over de maanden kunt volgen. Iets zoals hieronder weergegeven: 
De doellijn wordt bestuurd door de schuifbalk en alsof het doel in een van de maanden wordt bereikt (of overschreden), wordt het groen gemarkeerd.
Een dynamische doellijn maken in Excel-staafdiagram
Deze grafiek bestaat uit 3 delen:
- Het staafdiagram
- De doellijn (horizontale stippellijn)
- De schuifbalk (om de doelwaarde te regelen)
Het staafdiagram
Ik heb gegevens zoals hieronder weergegeven:
Cellen B2:B13 hebben alle waarden, terwijl C2:C13 alleen een waarde toont als deze de doelwaarde overschrijdt (in cel F2). Als de waarde lager is dan de doelwaarde, wordt #N/A weergegeven. Nu moeten we deze waarden in een clusterdiagram plotten
- Selecteer de volledige dataset (A1:C13)
- Ga naar Invoegen -> Grafieken -> Geclusterd kolomdiagram
- Selecteer een van de balken voor 'Boven doel'-waarden en klik met de rechtermuisknop en selecteer Gegevensreeks opmaken
- Wijzig in het gedeelte Serie-optie de waarde voor serie-overlap in 100%
- Dit creëert een grafiek, waarin alle waarden die het doel overschrijden in een andere kleur worden gemarkeerd (u kunt dit controleren door de doelwaarde te wijzigen)
De doellijn
Hier laat ik je een slimme manier zien om een doellijn te maken met behulp van foutbalken
- Selecteer de grafiek en ga naar Ontwerp -> Gegevens selecteren
- Klik in het dialoogvenster Gegevensbron selecteren op Toevoegen
- Typ in het vak Reeks bewerken de naam van de reeks als 'Doellijn' en selecteer in Reekswaarde uw cel Doelwaarde
- Hiermee wordt alleen een staafdiagram ingevoegd voor het eerste gegevenspunt (januari)
- Selecteer deze gegevensbalk en klik met de rechtermuisknop en selecteer Grafiektype serie wijzigen
- Wijzig het grafiektype in spreidingsdiagram. Hierdoor verandert de balk in januari in een enkele stip
- Selecteer het gegevenspunt en ga naar Ontwerp -> Grafieklay-outs -> Grafiekelement toevoegen -> Foutbalken -> Meer opties voor foutbalken
- Selecteer voor Excel 2010 het gegevenspunt en ga naar Lay-out -> Analyse -> Foutbalken -> Meer opties voor foutbalken
- Selecteer voor Excel 2010 het gegevenspunt en ga naar Lay-out -> Analyse -> Foutbalken -> Meer opties voor foutbalken
- U ziet horizontale foutbalklijnen aan beide zijden van het spreidingspunt. Selecteer die horizontale foutbalk en selecteer vervolgens in het gedeelte Foutbalkopties Aangepast en klik op Waarde opgeven
- Geef een positieve waarde als 11 en een negatieve waarde als 0 (u kunt hit and trial gebruiken om te zien welke waarde er goed uitziet voor uw grafiek)
- Selecteer het Scatter-gegevenspunt en klik met de rechtermuisknop en selecteer Seriegegevens opmaken. Ga naar Markeropties en selecteer Markertype als geen. Hiermee wordt het gegevenspunt verwijderd en hebt u alleen de foutbalk (dit is uw doellijn)
- Houd er rekening mee dat uw foutbalk zou veranderen wanneer u de doelwaarde wijzigt. Formatteer het gewoon om er een stippellijn van te maken en verander de kleur om het er beter uit te laten zien
De schuifbalk
- Maak een schuifbalk en lijn deze uit met de grafiek. Pas de schuifbalk samen met de grafiek aan. Klik hier om te leren hoe u een schuifbalk maakt in Excel.
- Maak de maximale waarde van de schuifbalk gelijk aan de maximale waarde in uw grafiek en koppel de schuifbalkwaarde aan een cel (ik heb G2 gebruikt)
- Gebruik in de cel met de doelwaarde de formule =500-G2 (500 is de maximale waarde in de grafiek)
- Dit is om ervoor te zorgen dat uw doelwaarde nu met de schuifbalk meebeweegt
Dat is het!! Wanneer u nu de schuifbalk verplaatst en de doelwaarden wijzigt, worden de balken die aan het doel voldoen automatisch gemarkeerd in een andere kleur.
Probeer het zelf… Download het bestand
