Diagramm mit Durchschnitts- / Zielwertlinie in Excel erstellen

In Excel kann es eine häufige Aufgabe sein, eine horizontale Linie oder Vertikale Linie in ein Diagramm einzufügen; diese horizontale Linie oder Vertikale Linie kann sich auf einen Ziel- oder Durchschnittswert beziehen. Diese Art von Linie erleichtert den Vergleich der Tatsächlicher Wert mit dem Ziel- oder Durchschnittswert, und mithilfe dieser Linie können Sie leicht erkennen, wo die Werte über bzw. unter diesem Referenzwert liegen, wie in den folgenden Screenshots gezeigt. In diesem Artikel erläutere ich, wie Sie ein Säulen- oder Balkendiagramm-Diagramm mit Durchschnitts- oder Zielwertlinie in Excel erstellen.

Säulendiagramm mit horizontaler Linie	Balkendiagramm mit Vertikale Linie

• Erstellen Sie Säulendiagramm mit durchschnittlicher oder Zielwertlinie horizontal
• Fügen Sie eine Zielwertlinie mit Unterschiedlicher Wert in ein Säulendiagramm ein
• Erstellen Sie Säulendiagramm mit dynamischer Durchschnittslinie mithilfe von Optionsfeld
• Erstellen Sie Balkendiagramm mit durchschnittlicher oder Zielwertlinie vertikal
• Fügen Sie eine Zielwertlinie mit Unterschiedlicher Wert in ein Balkendiagramm ein
• Fügen Sie einer Spalte oder einem Balkendiagramm mithilfe einer praktischen Funktion eine Ziel- oder Durchschnittslinie hinzu
• Beispieldatei „Diagramm mit Durchschnitts- / Zielwertlinie“ herunterladen

Erstellen Sie Säulendiagramm mit durchschnittlicher oder Zielwertlinie horizontal

Um ein Säulendiagramm mit durchschnittlicher oder Zielwertlinie horizontal 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 anschließend am AutoAusfüll-Griff, um die Formel auf die anderen Zellen zu übertragen (siehe Screenshot):

2. Markieren Sie anschließend den Datenbereich – einschließlich der Durchschnittswerte – und klicken Sie dann auf Einfügen > Säule oder Balkendiagramm einfügen > Gruppierte Säulen (siehe Screenshot):

3. Es wird nun ein Säulendiagramm in das Arbeitsblatt eingefügt. Löschen Sie nicht benötigte Elemente wie den Diagrammtitel oder Gitternetzlinien ganz nach Bedarf (siehe Screenshots):

4. Klicken Sie dann mit der rechten Maustaste auf die Datenreihe für den Durchschnitt (orangefarbene Balken) und wählen Sie im Kontextmenü Datenreihentyp ändern (siehe Screenshot):

5. Wählen Sie im Dialogfeld Datenreihentyp ändern unter der Liste Wählen Sie den Diagrammtyp und die Achse für Ihre Datenreihe den Eintrag Liniendiagramm aus der Dropdownliste Diagrammtyp für die Datenreihe Durchschnitt aus (siehe Screenshot):

6. Klicken Sie anschließend auf die Schaltfläche OK, um das unten dargestellte Diagramm zu erhalten:

7. Fügen Sie als Nächstes das Datenbeschriftungsfeld für diese Durchschnittslinie hinzu. Doppelklicken Sie dazu auf den rechten äußersten Datenpunkt der Linie (siehe Screenshot):

8. Klicken Sie anschließend auf Diagrammelemente, aktivieren Sie das Kontrollkästchen Datenbeschriftungen, und die Datenbeschriftung wird für die Durchschnittslinie eingefügt (siehe Screenshot):

Fügen Sie eine Zielwertlinie mit Unterschiedlicher Wert in ein Säulendiagramm ein

Manchmal möchten Sie den tatsächlichen Wert mit Zielwerten vergleichen, die sich für jede Zeile unterscheiden. In diesem Fall kann der unten dargestellte Diagrammtyp dabei helfen, die Zielwerte übersichtlicher darzustellen.

Gehen Sie zur Erstellung dieses Diagrammtyps wie folgt vor:

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

2. Klicken Sie dann mit der rechten Maustaste auf die Zielwert-Datenreihe (orangefarbene Balken) und wählen Sie im Kontextmenü Datenreihentyp ändern. Wählen Sie im Dialogfeld Datenreihentyp ändern für die Zielwert-Datenreihe in der Dropdownliste unter Diagrammtyp den Eintrag Liniendiagramm aus (siehe Screenshot):

3. Klicken Sie anschließend auf die Schaltfläche OK, und doppelklicken Sie dann auf die Zielwertlinie. Wählen Sie danach Datenreihen formatieren (siehe Screenshot):

4. Wählen Sie im geöffneten Bereich Datenreihen formatieren die Registerkarte Füllung & Linie und wählen Sie im Abschnitt Linie die Option Keine Linie aus (siehe Screenshot):

5. Bleiben Sie auf der Registerkarte Füllung & Linie und wechseln Sie zum Abschnitt Marker. Wählen Sie unter Integriert in der Gruppe Markeroptionen den horizontalen Balken aus der Dropdownliste Typ aus und legen Sie im Feld Größe eine Größe fest, die der Breite Ihrer Balken entspricht (siehe Screenshot):

6. Legen Sie anschließend im Abschnitt Füllung eine beliebige Farbe für die Zielwertlinien fest (siehe Screenshot):

7. Entfernen Sie abschließend nicht benötigte Elemente wie Diagrammtitel oder Gitternetzlinien nach Bedarf.

Erstellen Sie Säulendiagramm mit dynamischer Durchschnittslinie mithilfe von Optionsfeld

Wenn Sie eine umfangreiche Tabelle mit den Verkaufszahlen eines Produkts über mehrere Jahre haben und die Durchschnittslinie für jedes Jahr in einem Diagramm anzeigen möchten, nutzen Sie einfach ein Diagramm mit Durchschnittslinie – klicken Sie dazu auf das Optionsfeld, wie in der folgenden Demo gezeigt. Sobald Sie ein Jahr auswählen, wird das Diagramm inklusive der Durchschnittslinie dynamisch aktualisiert.

Um dieses dynamische Diagramm zu erstellen, fügen Sie zunächst mehrere Optionsfelder ein, erstellen danach eine Hilfstabelle als Grundlage für das Diagramm und verknüpfen abschließend das Diagramm mit dem Optionsfeld. Gehen Sie dazu wie folgt vor:

Fügen Sie zunächst mehrere Optionsfeld ein

1. Klicken Sie auf Entwicklertools > Einfügen > Optionsfeld (Formularsteuerelement), und zeichnen Sie dann vier Optionsfelder. Bearbeiten Sie anschließend den Text entsprechend Ihren Anforderungen (siehe Screenshots):

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

3. Öffnen Sie im geöffneten Objekt formatieren-Dialogfeld unter der Registerkarte Steuerelement eine Zelle, um das Optionsfeld damit zu verknüpfen, geben Sie diese im Textfeld Zelle ein und klicken Sie auf OK, um das Dialogfeld zu schließen. Siehe Screenshot:

Fügen Sie als Zweites eine Hilfstabelle zum Erstellen des Diagramms basierend auf

4. Kopieren Sie die Produktnamen aus der Originaltabelle in einen neuen Bereich, geben Sie dann die folgende Formel in eine leere Zelle neben Ihren neu kopierten Daten ein und ziehen Sie anschließend am Ausfüllkästchen, um das Ergebnis zu erhalten. Siehe Screenshot:

5. Sobald Sie nun auf ein Optionsfeld klicken, wird automatisch das entsprechende Jahr der Daten wie in der folgenden Demo angezeigt:

6. Anschließend berechnen Sie die Durchschnittswerte ganz einfach mit der folgenden Formel. Siehe Screenshot:

Erstellen Sie als Drittes das Diagramm auf Basis der neuen Hilfstabelle.

Wählen Sie anschließend die neu erstellten Daten aus und wenden Sie dann Schritt 2 bis Schritt 8 der Methode 1 an, um ein Säulendiagramm mit einer Durchschnittslinie zu erstellen. So erhalten Sie ein Diagramm mit einer dynamischen Durchschnittslinie. Sehen Sie sich die folgende Demo an:

Erstellen Sie ein Balkendiagramm mit einer horizontalen Durchschnitts- oder Zielwertlinie

In diesem Abschnitt erfahren Sie, wie Sie ein Balkendiagramm mit einer horizontalen Linie für den Durchschnitts- oder Zielwert erstellen. Gehen Sie dazu wie folgt vor:

1. Erstellen Sie zunächst eine Hilfstabelle, geben Sie X und Y in zwei Zellen ein, und berechnen Sie anschließend die Durchschnittswerte in den Zellen D2 und D3. Siehe Screenshot:

2. Geben Sie anschließend die Zahlen 0 und 1 in die Zellen E2 und E3 als Y-Werte ein. Siehe Screenshot:

3. Wählen Sie anschließend den ursprünglichen Datenbereich aus und klicken Sie dann auf Einfügen > Säulen- oder Balkendiagramm einfügen > Gruppierte Balken. Siehe Screenshot:

4. Es wurde ein Balkendiagramm in das Arbeitsblatt eingefügt. Klicken Sie anschließend mit der rechten Maustaste auf einen Balken und wählen Sie im Kontextmenü Daten auswählen aus. Siehe Screenshot:

5. Klicken Sie im erscheinenden Daten auswählen-Quelle-Dialogfeld auf die Schaltfläche Hinzufügen. Siehe Screenshot:

6. Geben Sie anschließend im Dialogfeld Datenreihe bearbeiten einen Seriennamen in das Textfeld Serienname ein, und wählen Sie dann im Textfeld Serienwerte die Zellen mit den X-Werten aus (in diesem Beispiel D2:D3). Siehe Screenshot:

7. Klicken Sie auf OK > OK, um die Dialogfelder zu schließen. Die neue Datenreihe wird nun Ihrem Balkendiagramm hinzugefügt (zwei orangefarbene Balken). Klicken Sie anschließend mit der rechten Maustaste auf einen orangefarbenen Balken und wählen Sie Datenreihentyp ändern aus. Siehe Screenshot:

8. Wählen Sie im erscheinenden Datenreihentyp ändern-Dialogfeld für die Datenreihe Durchschnitt in der Dropdown-Liste Diagrammtyp den Eintrag Punktdiagramm mit interpolierten Linien aus. Siehe Screenshot:

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

10. Klicken Sie im Daten auswählen-Quelle-Dialogfeld in der Liste „Bereich der Reihennamen“ auf die Option Durchschnitt und anschließend auf die Schaltfläche Bearbeiten. Siehe Screenshot:

11. Anschließend erscheint das Dialogfeld Datenreihe bearbeiten. Wählen Sie im Feld X-Achsen-Wert der Serie die beiden X-Zellen (D2:D3) aus und im Feld Y-Achsen-Wert der Serie die beiden Y-Zellen (E2:E3). Siehe Screenshot:

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

13. Klicken Sie anschließend mit der rechten Maustaste auf die sekundäre vertikale Achse (die rechte Achse) und wählen Sie Achse formatieren aus dem Kontextmenü aus. Siehe Screenshot:

14. Geben Sie im geöffneten Achse formatieren-Bereich unter der Registerkarte Achsenoptionen im Abschnitt Achsenoptionen den Wert 1,0 in das Feld Maximaler Grenzwert ein. Sie erhalten daraufhin das im folgenden Screenshot dargestellte Diagramm:

15. Löschen Sie abschließend die sekundäre vertikale Achse und gegebenenfalls unerwünschte Gitterlinien. Fertig ist Ihr Balkendiagramm mit einer vertikalen Durchschnittslinie! Siehe Screenshot:

Fügen Sie eine Zielwertlinie mit Unterschiedlicher Wert in ein Balkendiagramm ein

Um die Zielwertlinie mit Unterschiedlicher Wert in ein Balkendiagramm wie in den folgenden Screenshots dargestellt einzufügen, gehen Sie bitte Schritt für Schritt wie folgt vor:

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

2. Klicken Sie anschließend mit der rechten Maustaste auf die Zielwert-Datenreihe (orangefarbene Balken) und wählen Sie im Kontextmenü Datenreihe formatieren aus. Siehe Screenshot:

3. Ändern Sie im geöffneten Datenreihe formatieren-Bereich unter der Registerkarte Datenreihenoptionen die Überlappung der Datenreihen auf 100 %. Siehe Screenshot:

4. Wechseln Sie im Bereich Datenreihe formatieren zur Registerkarte Füllung & Linie und wählen Sie im Abschnitt Füllung die Option Keine Füllung aus. Siehe Screenshot:

5. Wechseln Sie anschließend zur Registerkarte Effekte. Nehmen Sie im Abschnitt Schatten die folgenden Einstellungen vor:

• Geben Sie eine gewünschte Farbe aus der Farbe-Dropdownliste an;
• Stellen Sie die Unschärfe auf 0 pt;
• Passen Sie anschließend den Winkel auf 0°;
• Ändern Sie abschließend den Abstand auf 2 pt, wie benötigt.

6. Wechseln Sie nun erneut zur Registerkarte Datenreihenoptionen, wählen Sie im Abschnitt Sekundäre Achse unter Datenreihenoptionen aus und passen Sie die Abstandsbreite nach Bedarf auf 90 % an. Siehe Screenshot:

7. Löschen Sie abschließend unerwünschte Elemente wie beispielsweise die Diagrammkachel, die obere X-Achse oder die Gitternetzlinien nach Bedarf – und erhalten Sie so das Balkendiagramm mit verschiedenen Ziellinien, wie im folgenden Screenshot dargestellt:

Fügen Sie mithilfe einer praktischen Funktion ganz einfach eine Ziel- oder Durchschnittslinie zu Ihrem Säulen- oder Balkendiagramm hinzu.

Beispieldatei „Diagramm mit Durchschnitts- / Zielwertlinie“ herunterladen