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 für dieses Dilemma ist die Definition einer Tabelle, die als Datenbasis für das Diagramm dient.

Gehen Sie bitte folgendermaßen vor:

  1. Aktivieren Sie eine beliebige Zelle in dem Zellbereich mit den im Diagramm darzustellenden Daten.
  2. Klicken Sie im Menüband auf der Registerkarte Einfügen in der Gruppe Tabellen auf die Schaltfläche Tabelle. Das folgende Dialogbild erscheint:

    Erstellen Tabelle für Diagramm

  3. Klicken Sie auf die Schaltfläche OK.
  4. Klicken Sie danach auf der Registerkarte Einfügen in der Gruppe Diagramme auf die Schaltfläche Säule.
  5. Wählen Sie in dem Bereich 2-D-Säule die erste Diagrammart Gruppierte Säulen. Das folgende Säulendigramm wird im Tabellenblatt erzeugt.

    Säulendiagramm auf Basis einer Excel-Tabelle

Wenn Sie in der Tabelle mit den Umsatzdaten eine neue Zeile erfassen, erscheint im Diagramm sofort eine Säule für den neu erfassten Kunden.

Säulendiagramm auf Basis einer Excel-Tabelle

In folgendem Diagramm sehen Sie, wie sich Leerzeilen in der Tabelle mit den Diagrammdaten im Diagramm auswirken.

Säulendiagramm auf Basis einer Excel-Tabelle mit Leerzeilen

Eine zweite 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 die 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

Seminartipp

Die Erstellung von dynamischen Diagrammen ist ein Thema unseres Seminars Unternehmensdaten visualisieren mit Excel.

Hat Ihnen dieser Beitrag weitergeholfen?

ja nein Andere Erwartung

Geben Sie uns Ihr Feedback. Möchten Sie eine Antwort von uns erhalten, tragen Sie zusammen mit Ihrem Feedback auch Ihre E-Mail-Adresse ein. Ihre E-Mail-Adresse wird ausschließlich für die Kommunikation mit Ihnen verwendet. Sie wird nicht veröffentlicht oder weitergegeben.

Benötigen Sie professionelle Unterstützung beim Einsatz von Word, Excel, Outlook, PowerPoint oder Access, so sind wir der richtige Ansprechpartner für Sie. Senden Sie uns eine E-Mail mit einer kurzen Problembeschreibung. Jemand aus unserem Team wird sich bei Ihnen melden, um weitere Details abzuklären und Ihnen ein Angebot für unsere Dienstleistung zu erstellen.