Dynamische Diagramme in Excel

Wenn Sie in Excel ein Diagramm erstellt haben und laufend noch Daten in einem Tabellenblatt ergänzen möchten, müssen Sie mehrere Schritte vornehmen, damit die Beschriftungen und Werte im Diagramm berücksichtigt werden. Mithilfe von dynamischen Diagrammen können die zu berücksichtigenden Datenreihen automatisch angepasst werden.

Sie möchten die Umsätze mit Ihren Kunden in einem Säulendiagramm darstellen.

Die Werte und das Diagramm im Tabellenblatt Umsätze in der Arbeitsmappe Statistiken.xlsx sehen folgendermaßen aus:

Bild1

Wenn Sie einen neuen Kunden mit seinem Umsatz erfassen, erscheint dieser Datenpunkt zunächst nicht im Diagramm. Sie müssen die Datenreihen im Diagramm manuell anpassen, damit der neue Kunde berücksichtigt wird.

Wenn Sie schon bei der Erstellung des Diagramms die später noch hinzukommenden Kunden berücksichtigen und die Datenbasis bereits entsprechend größer markieren, sieht das erzeugte Diagramm leider folgendermaßen aus, wenn Sie vorher den Bereich A1:B30 markieren:

Bild2

Eine Lösung ist die automatische Anpassung der Datenreihen im Diagramm durch die Vergabe von Namen unter Anwendung der Funktion BEREICH.VERSCHIEBEN. Die folgenden Schritte sind in Excel 2016, Excel 2013, Excel 2010 bzw. Excel 2007 notwendig, wenn Sie die Werte im Zellbereich von A2:B30 im Diagramm darstellen möchten:

    1. Klicken Sie auf der Registerkarte Formeln in der Gruppe Definierte Namen auf die Schaltfläche Namen definieren.

    2. Geben Sie einen Namen für die Datenreihe der Beschriftungen ein, z.B. Kunde.

    3. In dem Feld Bezieht sich auf tragen Sie die folgende Formel ein:
      =BEREICH.VERSCHIEBEN(Umsätze!$A$2;0;0;ANZAHL2(Umsätze!$A$2:$A$30);1)
      Erläuterung:
      Mit der Funktion BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite) können Sie einen neuen Zellbezug definieren, der gegenüber einem angegeben Bezug versetzt und/oder erweitert bzw. verkleinert ist. Ausgangspunkt des Beschriftungsbereichs ist die Zelle A2 in dem Tabellenblatt Umsätze. Der bisherige Bezug A2 wird nicht versetzt. Daher wird für die Argumente Zeilen und Spalten jeweils eine 0 eingetragen Die Höhe des neuen Bezugs wird in Zeilen angegeben. Sie ergibt sich aus der Anzahl der Zeilen mit Kundennamen im Bereich A2:A30. Die Breite des neuen Bezugs ist 1, weil nur die eine Spalte A berücksichtigt werden soll.

    4. Klicken Sie auf die Schaltfläche OK.

    5. Geben Sie einen weiteren Namen für die Datenreihe der Umsätze ein, z.B. Umsatz.

    6. In dem Feld Bezieht sich auf tragen Sie die folgende Formel ein:

      =BEREICH.VERSCHIEBEN(Umsätze!$B$2;0;0;ANZAHL(Umsätze!$B$2:$B$30);1)

    7. Klicken Sie auf die Schaltfläche OK.

    8. Wenn Sie auf der Registerkarte Formeln in der Gruppe Definierte Namen auf die Schaltfläche Namens-Manager klicken, sehen die beiden Namen folgendermaßen aus.

      Bild3

    9. Markieren Sie im Diagramm die Datenreihe mit den Umsatzdaten, indem Sie auf eine der Säulen klicken. In der Bearbeitungsleiste steht die folgende Formel:

      =DATENREIHE(Umsätze!$B$1;Umsätze!$A$2:$A$5;Umsätze!$B$2:$B$5;1).

      Sie sagt aus, dass die Beschriftung der Datenreihe aus der Zelle B1 genommen wird, die Rubriken bzw. horizontalen Achsenbeschriftungen aus dem Zellbereich A2:A5 und die Umsatzwerte aus dem Zellbereich B2:B5. Alle Informationen stammen aus dem Tabellenblatt Umsätze.

    10. Ersetzen Sie den Ausdruck Umsätze!$A$2:$A$5 für die Beschriftungen durch den folgenden Ausdruck:

      Statistiken.xlsx!Kunde

      Wichtig ist, dass Sie anstelle des Namens des aktuellen Tabellenblatts Umsätze den Namen der Arbeitsmappe so eingeben, wie er in der Titelleiste von Excel steht. In unserem Beispiel hat die Arbeitsmappe den Dateinamen Statistiken.xlsx. Sie geben den Namen der Arbeitsmappe an, weil sich die zuvor definierten Zellbereiche mit den Namen Kunde und Umsatz in unserem Beispiel auf die gesamte Arbeitsmappe und nicht auf ein Tabellenblatt beziehen.

    11. Anschließend ersetzen Sie den Ausdruck Umsätze!$B$2:$B$5 für die Datenreihe mit den Werten durch den folgenden Ausdruck:

      Statistiken.xlsx!Umsatz

      Die Formel sieht jetzt folgendermaßen aus:

      =DATENREIHE(Umsätze!$B$1;Statistiken.xlsx!Kunde;Statistiken.xlsx!Umsatz;1)

    12. Bestätigen Sie die geänderte Formel, indem Sie die ENTER-Taste drücken.

    13. Wenn Sie nun einen neuen Kunden mit seinem Umsatz erfassen, wird automatisch eine neue Säule im Diagramm angezeigt. In dem unteren Beispiel wurde der Kunde Jansen nachträglich hinzugefügt:

      Dynamisches Diagramm in Excel

       

 

Hinweise

  • Um zu prüfen, wie der Zellbereich Kunde aktuell definiert ist, rufen Sie die Funktionstaste F5 auf. In dem Dialogbild GeheZu tragen Sie im Feld Verweis den Namen Kunde ein. Nach dem Klick auf die Schaltfläche OK wird der aktuelle Zellbereich im Tabellenblatt markiert.
  • Die Formeln mit der Funktion BEREICH.VERSCHIEBEN können noch übersichtlicher werden, wenn Sie für die Zellen mit den Beschriftungen und Werten jeweils Namen vergeben.
  • Wenn Sie weitere Datenreihen im Diagramm darstellen möchten, müssen Sie für diese ebenfalls einen Namen mit der Funktion BEREICH.VERSCHIEBEN definieren.
  • Wenn in den Zellbereichen A2:A30 oder B2:B30 alle Zellen leer sind, erscheint der folgende Fehler, weil die Höhe und die Breite des Bereichs in der Funktion BEREICH.VERSCHIEBEN nicht 0 werden dürfen:

Bild5

 

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

29 Kommentare

  1. Sehr gut organisierter Kurs. Zu meiner Überraschung kannte ich viele Funktionen nicht, obwohl ich schon seit Jahren mit Excel arbeite.
    Danke für die Erklärung.

  2. Erstklassige Excel-Erklärung, und auch erstklassige Website-Programmierung bis ins Detail! Vielen Dank und höchsten Respekt! Ich hatte lange vergeblich gesucht, nicht zuletzt bei “support”.microsoft.com…

  3. Sehr schöner Beitrag. Danke.
    Ich habe mich ewig damit rumgeschlagen, dass DATENREIHE seltsamerweise keine Formeln in seinen Argumenten erlaubt. Da aber Namen erlaubt sind, kann man alle Berechnungen irgendwohin auslagern, für das Ergebnis einen Namen definieren und den dann in DATENREIHE als Argument verwenden.

    Natürlich geht es viel einfacher, wenn man die Daten in einer Tabelle unterbringt. Aber nur, wenn man ALLE Datenzeilen anzeigen will. Oft will man aber nur einen Teil anzeigen, z. B. die letzten sechs Monate oder den Durchschnitt eines Jahres aus der Tabelle, und dann braucht man wieder obiges Verfahren mit den Namen.

    Ich habe mir ein kleines Spreadsheet gebastelt, das beide Fälle vorführt. Leider kann ich hier aber keine Anlage anfügen.

  4. Das mit dem dynamischen Diagramm funktioniert super, gerade wenn die Anzahl der Datenreihen (Kunden) erweitert werden muss. Doch wie bekommt man es hin, das wenn Kunde Jansen nicht mehr geführt wird? Wenn ich die Daten (Name und Wert) händisch lösche, bleibt in dem Diagramm an dessen Stelle ein freier Platz erhalten, da sich der Rahmen mit dem blauen Dreieck (unten rechts) sich nicht automatisch bis auf Kunde “Krause” reduziert. Muss man das also immer manuell vornehmen, oder gibt es dafür auch eine Lösung (automatische Reduzierung des Datenrahmens). Das wäre gerade bei automatisierten Berichten mit größeren Umfängen interessant.
    Vielen Dank!

    • Hallo Herr Nottorf,

      um eine Zeile aus einer dynamischen Tabelle zu löschen, aktivieren Sie eine Zelle in der entsprechenden Zeile und rufen Sie über die rechte Maustaste das Kontextmenü auf. Klicken Sie auf den Eintrag Zeile/Spalte löschen und dann auf Tabellenzeile.

      • Hallo Herr Richter,
        komme leider jetzt erst dazu Ihnen zu antworten.
        Vielen Dank für den Hinweis, es klappt und ich kann das in meine automatischen Diagramme mit einbauen!

        Gruß Nottorf

  5. Beschrieben wie Bereichsnamen in Diagrammen verwendetw erden können. Datainame!… Vielen Dank!

  6. funktioniert nicht

  7. hatte Probleme mit der oben beschriebenen “Fehlermeldung” Dank!!! genau in der Reihenfolge – Zeitersparnis, weniger Stress

  8. noch keine Ersparnis, die kommt aber da nicht jedesmal neu erstellt, beschriftet werden muß. danke

  9. Elegante Lösung um Diagramme dynamisch anzupassen! Einfacher als ich erwartet hatte. Vielen Dank!

  10. Dem Vergessen der Aktualisierung des Diagramms vorgebeugt! 🙂 Danke für den Tip… Gruss E. Klumpp

  11. sehr sogar – 1A herzlichen Dank! Ich werde mir auch Weiteres auf Ihrer Seite anschauen

  12. gute Erklärung, so dass ich nicht selbst probieren musste.

  13. Ich habs einfach verstanden 🙂

  14. Ausgezeichnete Erklärung am Beispiel!

  15. aha-Erlebnis

  16. Datenreihen auch im Querformat Bayer Stammaktion !234!237!245!229! Datum !24.12.00!25.12.00!26.12.00!

  17. Damit war es mir möglich, ein akutes Problem zu lösen

  18. Wäre das ganze nicht einfacher, wenn man die Daten als Tabelle formatiert?

  19. weniger strss

  20. Eine neue Möglichkeit gezeigt

  21. Gute Erklärung – einfach verständlich

  22. Mit dem Tabellenformat spare ich mir bei sich vertikal erweiternden Tabellen die Formel Bereich.Verschieben – geht also viel einfacher

  23. OK

  24. Eine Excel Vorlage zum Beispiel

  25. Wie ist es möglich die Darstellung im Diagramm auf 12-Monate zu beschränken, sodass bei einem neuen Monat ein alter wegfällt? Vieln Dank

  26. Diadaktisch sehr gut erklärt, habe es auf Anhieb hin bekommen. Besten Dank. In meinem Fall (Histogramm) lasse ich die “Anzahl der Klassen” des Histogramms durch den Anwender änderbar, weshalb ich die noch nicht ausgefüllten Datenzeilen aber bereits mit Formeln vordefiniere, damit diese sich selbständig ausfüllen. Dadurch sind die Zellen zwar optisch leer, werden aber von ANZAHL2() nicht als leer erkannt, weshalb im Diagramm alle Zeilen, die für die Datenreihe vordefiniert wurde, angezeigt werden. Die Lösung war einfach, ich habe den Funktionsaufruf ANZAHL2() einfach durch den Zelleninhalt “Anzahl der Klassen” ersetzt. Entsprechungen für Anwender des englischen Excel: BEREICH.VERSCHIEBEN() –> OFFSET() ANZAHL2() –> COUNTA() DATENREIHE() –> SERIES() Nochmals besten Dank, diese Seite war mir eine große Hilfe.

  27. Ich habe etwa 15 Minuten gebraucht und konnte die Erklärungen sofort auf den Fall übertragen, dass Zeilen und Spalten vertauscht sind. Zuvor habe ich eine andere Erklärung aus dem Netz gelesen, die mir aber nicht wirklich geholfen hat. Eure ist wesentlich besser. Weiter so! 🙂

Pin It on Pinterest

Share This