Diagramm mit Durchschnitts- / Ziel-Linie in Excel erstellen

In Excel ist es eine häufige Aufgabe, einer Grafik eine horizontale oder vertikale Linie hinzuzufügen. Diese Linien können sich auf einen Zielwert oder einen Durchschnittswert beziehen. Solche Linien erleichtern den Vergleich zwischen tatsächlichen Werten und dem Ziel- oder Durchschnittswert. Mit Hilfe dieser Linie können Sie leicht erkennen, wo die Werte über oder unter diesem Referenzwert liegen, wie in den folgenden Screenshots gezeigt. In diesem Artikel werde ich erklären, wie man ein Säulen- oder Balkendiagramm mit einer Durchschnitts- oder Ziellinie in Excel erstellt.

Säulendiagramm mit horizontaler Linie	Balkendiagramm mit vertikaler Linie

• Säulendiagramm mit horizontaler Durchschnitts- oder Ziellinie erstellen
• Ziellinie mit unterschiedlichen Werten in einem Säulendiagramm hinzufügen
• Säulendiagramm mit dynamischer Durchschnittslinie mithilfe von Optionsfeldern erstellen
• Balkendiagramm mit vertikaler Durchschnitts- oder Ziellinie erstellen
• Ziellinie mit unterschiedlichen Werten in einem Balkendiagramm hinzufügen
• Ziel- / Durchschnittslinie zu einem Säulen- oder Balkendiagramm mit einer praktischen Funktion hinzufügen
• Beispieldatei für Diagramm mit Durchschnitts- / Ziellinie herunterladen

Säulendiagramm mit horizontaler Durchschnitts- oder Ziellinie erstellen

Um ein Säulendiagramm mit einer horizontalen Durchschnitts- oder Ziellinie zu erstellen, gehen Sie bitte wie folgt vor:

1. Wenn Sie ein Säulendiagramm mit einer Durchschnittslinie erstellen möchten, berechnen Sie zunächst den Durchschnittswert der Datenliste. Geben Sie dazu die folgende Formel in eine leere Zelle ein und ziehen Sie das Autoausfüllkästchen, um die Formel in andere Zellen zu kopieren, siehe Screenshot:

2. Wählen Sie dann den Datenbereich einschließlich der Durchschnittswerte aus und klicken Sie auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säule, siehe Screenshot:

3. Ein Säulendiagramm wird in das Arbeitsblatt eingefügt. Sie können unerwünschte Elemente wie Diagrammtitel oder Gitternetzlinien nach Bedarf löschen, siehe Screenshots:

4. Klicken Sie dann mit der rechten Maustaste auf die Durchschnittsdatenreihe (orangefarbene Balken) und wählen Sie "Diagrammtyp der Reihe ändern" aus dem Kontextmenü, siehe Screenshot:

5. Wählen Sie im Dialogfeld "Diagrammtyp ändern" unter der Liste "Diagrammtyp und Achse für Ihre Datenreihen" aus der Dropdown-Liste "Diagrammtyp" für die Durchschnittsdatenreihe das Liniendiagramm aus, siehe Screenshot:

6. Klicken Sie anschließend auf die Schaltfläche OK, und Sie erhalten das Diagramm wie im folgenden Screenshot gezeigt:

7. Als Nächstes können Sie die Datenbeschriftung für diese Durchschnittslinie hinzufügen. Doppelklicken Sie, um den am weitesten rechts stehenden Datenpunkt der Linie auszuwählen, siehe Screenshot:

8. Klicken Sie dann auf Diagrammelemente, aktivieren Sie das Kontrollkästchen Datenbeschriftungen, und die Datenbeschriftung wurde für die Durchschnittslinie eingefügt, siehe Screenshot:

Ziellinie mit unterschiedlichen Werten in einem Säulendiagramm hinzufügen

Manchmal möchten Sie die tatsächlichen Werte möglicherweise mit den Zielwerten vergleichen, die für jede Zeile unterschiedlich sind. Um die Zielwerte klarer darzustellen, kann der folgende Diagrammtyp hilfreich sein.

Um diesen Diagrammtyp zu erstellen, gehen Sie wie folgt vor:

1. Wählen Sie zunächst den Datenbereich aus und klicken Sie auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Säule, um das Diagramm einzufügen.

2. Klicken Sie dann mit der rechten Maustaste auf die Zieldatenreihe (orangefarbene Balken) und wählen Sie "Diagrammtyp der Reihe ändern" aus dem Kontextmenü. Wählen Sie im Dialogfeld "Diagrammtyp ändern" für die Zieldatenreihe aus der Dropdown-Liste "Diagrammtyp" das Liniendiagramm aus, siehe Screenshot:

3. Klicken Sie dann auf die Schaltfläche OK und doppelklicken Sie auf die Ziellinie. Wählen Sie Formatieren der Datenreihe, siehe Screenshot:

4. Im geöffneten Datenreihe formatieren Bereich unter der Registerkarte Füllung & Linie wählen Sie Keine Linie im Abschnitt Linie aus, siehe Screenshot:

5. Bleiben Sie im Tab " Füllung & Linie", wechseln Sie zum Abschnitt Marker, wählen Sie Integriert aus dem Abschnitt Markeroptionen aus und wählen Sie dann den horizontalen Balken im Dropdown-Menü Typ aus. Zum Schluss legen Sie eine Größe fest, die der Breite Ihrer Balken entspricht, im Feld Größe, siehe Screenshot:

6. Legen Sie dann eine Farbe für die Zielzeilen nach Belieben im Abschnitt Füllung fest, siehe Screenshot:

7. Löschen Sie abschließend die unerwünschten Elemente wie Diagrammtitel oder Gitternetzlinien nach Bedarf.

Säulendiagramm mit dynamischer Durchschnittslinie mithilfe von Optionsfeldern erstellen

Wenn Sie eine große Tabelle mit den Verkaufszahlen eines Produkts über mehrere Jahre haben und nun die Durchschnittslinie für jedes Jahr in einem Diagramm anzeigen möchten, können Sie ein Diagramm mit einer Durchschnittslinie verwenden, indem Sie auf das Optionsfeld klicken, wie in der folgenden Demo gezeigt. Wenn Sie das entsprechende Jahr auswählen, wird das Diagramm mit der Durchschnittslinie dynamisch angezeigt.

Um dieses dynamische Diagramm zu erstellen, sollten Sie zunächst mehrere Optionsfelder einfügen, dann eine Hilfstabelle erstellen, auf der das Diagramm basiert, und schließlich ein Diagramm verknüpfen, das mit dem Optionsfeld verknüpft ist. Gehen Sie bitte wie folgt vor:

Zuerst mehrere Optionsfelder einfügen

1. Klicken Sie auf Entwicklertools > Einfügen > Optionsfeld (Formularsteuerelement) und zeichnen Sie vier Optionsfelder und bearbeiten Sie den Text nach Bedarf, siehe Screenshots:

2. Klicken Sie dann mit der rechten Maustaste auf das erste Optionsfeld und wählen Sie " Steuerelement formatieren" aus dem Kontextmenü, siehe Screenshot:

3. Wählen Sie im geöffneten Dialogfeld "Objekt formatieren" unter der Registerkarte Steuerelement eine Zelle aus, um das Optionsfeld im Textfeld Zellverknüpfung zu verknüpfen, und klicken Sie auf OK, um es zu schließen, siehe Screenshot:

Zweitens, eine Hilfstabelle für die Erstellung des Diagramms einfügen

4. Kopieren Sie die Produktnamen aus der ursprünglichen Tabelle in einen neuen Bereich und wenden Sie dann die folgende Formel in einer leeren Zelle neben Ihren neu kopierten Daten an und ziehen Sie den Ausfüllkästchen, um das Ergebnis zu erhalten, siehe Screenshot:

5. Jetzt werden bei Klick auf ein Optionsfeld automatisch die entsprechenden Jahresdaten wie in der folgenden Demo angezeigt:

6. Berechnen Sie dann die Durchschnittswerte mit der folgenden Formel, siehe Screenshot:

Drittens, das Diagramm basierend auf der neuen Hilfstabelle erstellen

Wählen Sie dann die neu erstellten Daten aus und wenden Sie die oben genannten Schritte 2 bis 8 der Methode 1 an, um ein Säulendiagramm mit einer Durchschnittslinie zu erstellen, und Sie erhalten ein Diagramm mit einer dynamischen Durchschnittslinie. Siehe unten Demo:

Balkendiagramm mit vertikaler Durchschnitts- oder Ziellinie erstellen

Dieser Abschnitt stellt vor, wie man ein Balkendiagramm mit einer vertikalen Durchschnitts- oder Ziellinie erstellt. Gehen Sie bitte wie folgt vor:

1. Erstellen Sie zunächst eine Hilfstabelle, geben Sie X und Y in zwei Zellen ein und berechnen Sie dann die Durchschnittswerte in die Zellen D2 und D3, siehe Screenshot:

2. Geben Sie dann die Zahl 0 und 1 in die Zellen E2 und E3 für die Y-Werte ein, siehe Screenshot:

3. Wählen Sie dann den ursprünglichen Datenbereich aus und klicken Sie auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierter Balken, siehe Screenshot:

4. Ein Balkendiagramm wurde in das Blatt eingefügt. Klicken Sie dann mit der rechten Maustaste auf den Balken und wählen Sie "Daten auswählen" aus dem Kontextmenü, siehe Screenshot:

5. Klicken Sie im erscheinenden Dialogfeld "Datenquelle auswählen" auf die Schaltfläche Hinzufügen, siehe Screenshot:

6. Geben Sie im Dialogfeld "Reihe bearbeiten" einen Reihennamen in das Textfeld Reihenname ein und wählen Sie dann die Zellen mit den X-Werten (D2:D3 in diesem Beispiel) im Textfeld Reihenwerte aus, siehe Screenshot:

7. Klicken Sie auf OK > OK, um die Dialoge zu schließen, und die neue Datenreihe wird jetzt Ihrem Balkendiagramm hinzugefügt (zwei orangefarbene Balken). Klicken Sie dann mit der rechten Maustaste auf den orangefarbenen Balken und wählen Sie "Diagrammtyp der Reihe ändern", siehe Screenshot:

8. Wählen Sie im erscheinenden Dialogfeld "Diagrammtyp ändern" aus der Dropdown-Liste Diagrammtyp für die Durchschnittsdatenreihe "Punktdiagramm mit glatten Linien" aus, siehe Screenshot:

9. Klicken Sie dann auf OK, um das Dialogfeld zu schließen, und die neue Datenreihe wird in einen Datenpunkt entlang der primären Y-Achse konvertiert. Klicken Sie dann mit der rechten Maustaste auf den Balken und wählen Sie "Daten auswählen" aus dem Kontextmenü, siehe Screenshot:

10. Wählen Sie im Dialogfeld "Datenquelle auswählen" in der Liste Legendenfelder (Reihen) die Option Durchschnitt und klicken Sie dann auf die Schaltfläche Bearbeiten, siehe Screenshot:

11. Es erscheint ein Prompt-Feld „Reihe bearbeiten“. Wählen Sie im Feld Reihen-X-Werte die beiden X-Zellen (D2:D3) und im Feld Reihen-Y-Werte die beiden Y-Zellen (E2:E3), siehe Screenshot:

12. Klicken Sie auf OK > OK, um die Dialogfelder zu schließen, und Sie sehen, dass eine vertikale Durchschnittslinie zum Diagramm hinzugefügt wurde, siehe Screenshot:

13. Klicken Sie dann mit der rechten Maustaste auf die sekundäre vertikale Achse (die rechte Achse) und wählen Sie "Achse formatieren" aus dem Kontextmenü, siehe Screenshot:

14. Im geöffneten Achse formatieren Bereich, unter der Registerkarte Achsenoptionen, im Abschnitt Achsenoptionen, geben Sie 1,0 in das Feld Maximale Grenze ein, und Sie erhalten das Diagramm wie im folgenden Screenshot gezeigt:

15. Löschen Sie abschließend die sekundäre vertikale Achse und die unerwünschten Gitternetzlinien nach Bedarf. Nun ist ein Balkendiagramm mit einer vertikalen Durchschnittslinie fertiggestellt. Siehe Screenshot:

Ziellinie mit unterschiedlichen Werten in einem Balkendiagramm hinzufügen

Um die Ziellinie mit unterschiedlichen Werten in einem Balkendiagramm wie in den folgenden Screenshots gezeigt hinzuzufügen, gehen Sie bitte Schritt für Schritt wie folgt vor:

1. Wählen Sie den Datenbereich aus und klicken Sie auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierter Balken, um ein Balkendiagramm einzufügen, siehe Screenshot:

2. Klicken Sie dann mit der rechten Maustaste auf die Zieldatenreihe (orangefarbene Balken) und wählen Sie "Datenreihe formatieren" aus dem Kontextmenü, siehe Screenshot:

3. Im geöffneten Bereich "Datenreihe formatieren" unter der Registerkarte Reihenoptionen ändern Sie die Reihenüberlappung auf 100 %, siehe Screenshot:

4. Bleiben Sie im Bereich "Datenreihe formatieren", wechseln Sie zur Registerkarte Füllung & Linie und wählen Sie Keine Füllung im Abschnitt Füllung aus, siehe Screenshot:

5. Wechseln Sie als Nächstes zur Registerkarte Effekte, im Abschnitt Schatten führen Sie bitte die folgenden Operationen aus:

• Wählen Sie eine Farbe, die Sie benötigen, aus dem Dropdown-Menü Farbe aus;
• Setzen Sie die Unschärfe auf 0 pt;
• Passen Sie dann den Winkel auf 0° an;
• Ändern Sie abschließend den Abstand auf 2 pt, wie Sie es benötigen.

6. Wechseln Sie nun erneut zur Registerkarte Reihenoptionen, wählen Sie Sekundäre Achse im Abschnitt Reihenoptionen aus und ändern Sie die Lückenbreite auf 90 %, wie Sie es benötigen. Siehe Screenshot:

7. Löschen Sie abschließend die unerwünschten Elemente wie Diagrammtitel, oberste X-Achse, Gitternetzlinien nach Bedarf. Sie erhalten das Balkendiagramm mit unterschiedlichen Ziellinien wie im folgenden Screenshot gezeigt:

Ziel- / Durchschnittslinie zu einem Säulen- oder Balkendiagramm mit einer praktischen Funktion hinzufügen

Beispieldatei für Diagramm mit Durchschnitts- / Ziellinie herunterladen