Wenn Sie in der Verkaufsabteilung arbeiten und Ihr Leben nur von Zielen geprägt ist, ist diese Charting-Technik genau das Richtige für Sie. Und wenn nicht, lesen Sie es trotzdem, um einige coole Excel-Chart-Tricks zu lernen.
In diesem Blogbeitrag zeige ich Ihnen eine super Möglichkeit, eine dynamische Ziellinie in einem Excel-Diagramm zu erstellen, die Ihnen helfen kann, Ihre Leistung über die Monate zu verfolgen. Etwas wie unten gezeigt: 
Die Ziellinie wird durch die Bildlaufleiste gesteuert, und als ob das Ziel in einem der Monate erreicht (oder überschritten) wird, wird es grün hervorgehoben.
Erstellen einer dynamischen Ziellinie in einem Excel-Balkendiagramm
Dieses Diagramm besteht aus 3 Teilen:
- Das Balkendiagramm
- Die Ziellinie (horizontale gestrichelte Linie)
- Die Bildlaufleiste (um den Zielwert zu steuern)
Das Balkendiagramm
Ich habe Daten wie unten gezeigt:
Die Zellen B2: B13 haben alle Werte, während C2: C13 nur einen Wert anzeigt, wenn er den Zielwert (in Zelle F2) überschreitet. Wenn der Wert niedriger als der Zielwert ist, wird #N/A angezeigt. Jetzt müssen wir diese Werte in einem Clusterdiagramm darstellen
- Gesamten Datensatz auswählen (A1:C13)
- Gehen Sie zu Einfügen -> Diagramme -> Gruppiertes Säulendiagramm
- Wählen Sie einen der Balken für die Werte "Über dem Ziel" aus und klicken Sie mit der rechten Maustaste und wählen Sie Datenreihen formatieren
- Ändern Sie im Abschnitt Serienoption den Wert für Serienüberlappung in 100 %
- Dadurch wird ein Diagramm erstellt, in dem alle Werte, die den Zielwert überschreiten, in einer anderen Farbe hervorgehoben werden (Sie können dies überprüfen, indem Sie den Zielwert ändern).
Die Ziellinie
Hier zeige ich Ihnen einen intelligenten Weg, eine Ziellinie mit Fehlerbalken zu erstellen
- Wählen Sie das Diagramm aus und gehen Sie zu Design -> Daten auswählen
- Klicken Sie im Dialogfeld Datenquelle auswählen auf Hinzufügen
- Geben Sie im Feld Serie bearbeiten den Seriennamen als "Ziellinie" ein und wählen Sie unter Serienwert Ihre Zielwertzelle aus
- Dadurch wird ein Balkendiagramm nur für den ersten Datenpunkt eingefügt (Januar)
- Wählen Sie diese Datenleiste aus, klicken Sie mit der rechten Maustaste und wählen Sie Diagrammtyp der Serie ändern
- Ändern Sie den Diagrammtyp in Streudiagramm. Dadurch wird der Balken im Januar in einen einzelnen Punkt verwandelt
- Wählen Sie den Datenpunkt aus und gehen Sie zu Design -> Diagrammlayouts -> Diagrammelement hinzufügen -> Fehlerbalken -> Weitere Fehlerbalkenoptionen
- Wählen Sie für Excel 2010 den Datenpunkt aus und gehen Sie zu Layout -> Analyse -> Fehlerbalken -> Weitere Fehlerbalkenoptionen
- Wählen Sie für Excel 2010 den Datenpunkt aus und gehen Sie zu Layout -> Analyse -> Fehlerbalken -> Weitere Fehlerbalkenoptionen
- Sie werden auf beiden Seiten des Streupunktes horizontale Fehlerbalkenlinien bemerken. Wählen Sie diese horizontale Fehlerleiste aus und wählen Sie dann im Abschnitt Fehlerleistenoptionen die Option Benutzerdefiniert aus und klicken Sie auf Wert angeben
- Geben Sie einen positiven Wert als 11 und einen negativen Wert als 0 an (Sie können Hit and Trial verwenden, um zu sehen, welcher Wert für Ihr Diagramm gut aussieht)
- Wählen Sie den Streudatenpunkt aus, klicken Sie mit der rechten Maustaste und wählen Sie Seriendaten formatieren. Gehen Sie zu Markierungsoptionen und wählen Sie Markierungstyp als keiner aus. Dadurch wird der Datenpunkt entfernt und Sie haben nur die Fehlerleiste (die Ihre Ziellinie ist)
- Beachten Sie, dass sich Ihre Fehlerleiste ändert, wenn Sie den Zielwert ändern. Formatieren Sie es einfach, um es zu einer gepunkteten Linie zu machen, und ändern Sie seine Farbe, damit es besser aussieht
Die Bildlaufleiste
- Erstellen Sie eine Bildlaufleiste und richten Sie sie am Diagramm aus. Passen Sie die Bildlaufleiste zusammen mit dem Diagramm an. Klicken Sie hier, um zu erfahren, wie Sie eine Bildlaufleiste in Excel erstellen.
- Machen Sie den Maximalwert der Bildlaufleiste gleich dem Maximalwert in Ihrem Diagramm und verknüpfen Sie den Bildlaufleistenwert mit einer beliebigen Zelle (ich habe G2 verwendet)
- Verwenden Sie in der Zelle mit dem Zielwert die Formel =500-G2 (500 ist der Höchstwert im Diagramm)
- Damit stellen Sie sicher, dass sich Ihr Zielwert nun mit der Bildlaufleiste bewegt
Das ist es!! Wenn Sie nun die Bildlaufleiste verschieben und die Zielwerte ändern, werden die Balken, die dem Ziel entsprechen, automatisch in einer anderen Farbe hervorgehoben.
Probieren Sie es selbst aus… Laden Sie die Datei herunter
