Beitrag aus SmartTools Excel Weekly
So können Sie immer die letzten 12 Monate in einem Diagramm abbilden
Excel 365 2024 2021 2019 2016 2013
Nehmen wir an, dass Sie in einer Tabelle ein monatliches Fehlerprotokoll mit den Spaltenüberschriften "Monat", "Fehler1", "Fehler2" und "Fehler3" führen. In der Monatsspalte stehen Datumswerte in der Form "Jan 24", "Feb 24" usw. In den Fehlerspalten wird die Anzahl der jeweils aufgetretenen Fehler eingetragen. Für diese Werte wird ein Liniendiagramm mit separaten Datenreihen für die einzelnen Fehlerklassen erstellt.
Das Diagramm soll automatisch aktualisiert werden, sobald Sie neue Fehlerwerte für einen Monat eingeben. Dabei sollen aber nicht alle Fehlerwerte abgebildet werden, sondern nur die Werte der letzten zwölf Monate.
Für eine solche Aufgabenstellung benötigen Sie dynamische Wertebereiche, die Sie den Datenreihen Ihres Diagramms zugrunde legen. Zu diesem Zweck erstellen Sie benannte Bereiche, die sich mit Hilfe von Formeln immer auf die letzten zwölf Werte Ihrer Tabellenspalten beziehen. Anschließend binden Sie diese benannten Bereiche in die Datenreihenformeln des Diagramms ein. Die genaue Vorgehensweise lässt sich am besten anhand eines Beispiels verdeutlichen.
Angenommen, Sie haben die Überschriften "Monat", "Fehler1", "Fehler2" und "Fehler3" in die Zellen A4:D4 eines Tabellenblatts mit dem Namen "Tabelle1" eingegeben. Darunter - ab Zeile 5 des Tabellenblatts - folgen die eigentlichen Daten.
Verwenden Sie dann Einfügen-Diagramme-Linie, um auf Basis der Tabellenwerte ein einfaches Liniendiagramm zu erstellen.
Nun erstellen Sie einen benannten Bereich für die letzten zwölf Werte in der Monatsspalte:
- Wählen Sie das Menü Formeln-Definierte Namen-Namen definieren an.
- Geben Sie als Namen "Monatswerte" ein und ersetzen Sie den Inhalt des Feldes Bezieht sich auf durch folgende Formel:
=BEREICH.VERSCHIEBEN(INDIREKT("Tabelle1!$A$"&
VERGLEICH(1E-30;Tabelle1!$A:$A;-1));0;0;
-1*(MIN(ANZAHL2(Tabelle1!$A$5:$A$10000);12));1)
- Klicken Sie auf Ok.
Die Formel sucht per VERGLEICH-Funktion nach dem letzten Zahlenwert in Spalte A, womit Sie die entsprechende Zeilennummer erhalten. Die INDIREKT-Funktion erzeugt daraus die Adresse der letzten Spaltenzelle, um sie als Basis der Funktion BEREICH.VERSCHIEBEN einzusetzen.
BEREICH.VERSCHIEBEN erweitert den Bereich nach oben, indem durch die Multiplikation mit "-1" eine negative Höhe angegeben wird. Die Standardhöhe beträgt "12" Zeilen. Mit der MIN-Funktion prüfen Sie aber, ob überhaupt so viele Zeilen verfügbar sind. Sollte ANZAHL2 im Bereich A5:A10000 weniger Einträge zählen, wird die Anzahl der vorhandenen Einträge als Höhe genommen.
Um die Formel in Ihren Tabellen einzusetzen, müssen Sie natürlich den Tabellennamen (hier: "Tabelle1") und den Spaltenbezug (hier sind es Verweise auf Spalte A) anpassen. Außerdem müssen Sie im Rahmen der ANZAHL2-Funktion eine Bereichsadresse angeben, die von der Zeile des ersten Datensatzes in der Suchspalte (hier: A5) so weit nach unten reicht, dass auf absehbare Zeit alle neuen Datensätze darin enthalten sind. Im Beispiel sollte A10000 Platz genug für zukünftige Tabelleneingaben bieten.
Die Namen in den Fehlerspalten definieren Sie jetzt einfach als Bereiche, die um x Spalten vom Bereich "Monatswerte" versetzt sind. Für die letzten 12 Werte in der "Fehler1"-Spalte definieren Sie zum Beispiel den Namen "Fehlerwerte1" mit folgender Formel im Feld Bezieht sich auf:
=BEREICH.VERSCHIEBEN(Monatswerte;0;1)
So erstellen Sie auch die Namen "Fehlerwerte2" mit einem Spaltenversatz von "2" und "Fehlerwerte3" mit einem Versatz von "3".
Damit die Namen im Diagramm berücksichtigt werden, bearbeiten Sie die Formeln der Datenreihen:
- Klicken Sie mit der Maus auf die erste Datenreihe - in Ihrem Fall zum Beispiel auf die Linie der "Fehler1"-Werte. In der Bearbeitungsleiste sehen Sie daraufhin eine Formel wie diese:
=DATENREIHE(Tabelle1!$B$4;Tabelle1!$A$5:$A$21;
Tabelle1!$B$5:$B$21;1)
- Das erste Argument der DATENREIHE-Funktion ist der Name, der in der Legende erscheint, das zweite Argument bezeichnet die X-Werte des Diagramms, das dritte Argument die Y-Werte und das vierte Argument ist die Datenreihennummer.
- Ersetzen Sie die Bereichsadresse hinter dem Tabellennamen und dem Ausrufezeichen im zweiten Argument durch den Namen der Monatswerte. Anstelle von "Tabelle1!$A$5:$A$21" müsste das zweite Argument im Beispiel also so aussehen:
Tabelle1!Monatswerte
- Verfahren Sie genauso im dritten Argument der DATENREIHE-Funktion, wobei Sie hier den Namen der ersten Fehlerspalte einsetzen. Die vollständige Formel der Datenreihe lautet dann wie folgt:
=DATENREIHE(Tabelle1!$B$4;Tabelle1!Monatswerte;
Tabelle1!Fehlerwerte1;1)
- Wiederholen Sie die Schritte für die anderen Datenreihen Ihres Diagramms.
Lassen Sie sich nicht dadurch irritieren, wenn die DATENREIHE-Formeln am Ende den Dateinamen der Arbeitsmappe enthalten - etwa:
=DATENREIHE(Tabelle1!$B$4;Fehlerprotokoll.xls!
Monatswerte;Fehlerprotokoll.xls!Fehlerwerte1;1)
Excel nimmt diese Änderung automatisch vor. Bei der Bearbeitung der Formeln genügt es aber, den Tabellennamen beizubehalten. Unzulässig ist es lediglich, nur den Bereichsnamen ohne Voranstellung des Tabellen- oder Dateinamens einzugeben.
Nachdem Sie das Diagramm bearbeitet haben, können Sie Ihre Tabelle nun beliebig fortsetzen. Das Diagramm bildet immer automatisch die letzten zwölf Monate ab.