Nora führt seit Jahren ein Wetterprotokoll. Jedes Blatt der Arbeitsmappe ist ein separates Jahr, wobei Spalte A auf jedem Blatt die Daten im Jahr und Spalte B die Niederschlagsmenge an diesem Tag ist, falls vorhanden. Nora möchte ein Diagramm erstellen, das den diesjährigen Niederschlag im Vergleich zum letztjährigen Niederschlag zeigt. Sie fragt sich, ob es eine Möglichkeit gibt, dass diese Karte automatisch die korrekten Niederschlagswerte für beide Jahre basierend auf dem heutigen Datum referenziert.

Es gibt verschiedene Möglichkeiten, diesen Bedarf zu decken, je nachdem, was genau Sie erreichen möchten. Wenn Sie den Niederschlag in diesem Jahr nur mit dem Niederschlag im letzten Jahr nach Datum vergleichen möchten, können Sie dies einfach tun, indem Sie einige dynamische benannte Bereiche einrichten, die die Daten definieren, die Sie verwenden möchten.

Nehmen wir zunächst an, dass sich Ihre Daten für 2015 in einem Arbeitsblatt mit dem Namen 2015 und Ihre Daten für 2016 (bisher) in einem Arbeitsblatt mit dem Namen 2016 befinden.

In jedem Arbeitsblatt enthält Zeile 1 Überschriften. Dies bedeutet, dass Ihre Daten tatsächlich in Zelle A2 beginnen und Ihre Niederschlagswerte in Zelle B2.

Führen Sie die folgenden Schritte aus, um die Bereiche einzurichten:

  1. Zeigen Sie die Registerkarte Formeln des Menübands an.

  2. Klicken Sie in der Gruppe Definierte Namen auf das Namensmanager-Tool. Excel zeigt das Dialogfeld Name Manager an.

  3. Klicken Sie auf die Schaltfläche Neu. Excel zeigt das Dialogfeld Neuer Name an. (Siehe Abbildung 1.)

  4. Geben Sie im Feld Name den Namen CurrentYear ein (beachten Sie, dass dies ein einzelnes Wort ohne Leerzeichen ist).

  5. Geben Sie im Feld Verweise auf die folgende Formel ein:

  6. Klicken Sie auf die Schaltfläche OK, um die Erstellung des benannten Bereichs abzuschließen. Der neue Bereich sollte im Dialogfeld Name Manager angezeigt werden.

  7. Klicken Sie erneut auf die Schaltfläche Neu. Excel zeigt (erneut) das Dialogfeld Neuer Name an.

  8. Geben Sie im Feld Name den Namen PreviousYear ein (beachten Sie, dass dies wiederum ein einzelnes Wort ohne Leerzeichen ist).

  9. Geben Sie im Feld Verweise auf die folgende Formel ein:

  10. Klicken Sie auf die Schaltfläche OK. Der neue Bereich sollte im Dialogfeld Name Manager angezeigt werden.

  11. Klicken Sie ein drittes Mal auf die Schaltfläche Neu. Excel zeigt das Dialogfeld Neuer Name an.

  12. Geben Sie im Feld Name den Namen Datum ein.

  13. Geben Sie im Feld Verweise auf die folgende Formel ein:

  14. Klicken Sie auf die Schaltfläche Schließen, um das Dialogfeld Namensmanager zu schließen.

Mit den definierten Bereichen können Sie das Diagramm jetzt mit den folgenden Bereichen erstellen:

  1. Zeigen Sie die Registerkarte Einfügen des Menübands an.

  2. Klicken Sie in der Gruppe Diagramme auf die Option Spalte. Excel zeigt eine Dropdown-Liste an.

  3. Klicken Sie einmal auf das Clustered Column-Format. (Dies ist die erste Option unter der Überschrift 2-D-Spalte.) Excel erstellt ein leeres Diagrammobjekt in Ihrem Arbeitsblatt.

  4. Klicken Sie mit der rechten Maustaste auf das Diagrammobjekt. Excel zeigt ein Kontextmenü an.

  5. Wählen Sie im Kontextmenü die Option Daten auswählen. Excel zeigt das Dialogfeld Datenquelle auswählen an, das vollständig leer sein sollte, da Sie dem Diagramm keine Datenbereiche hinzugefügt haben.

  6. Auf der linken Seite des Dialogfelds unter den Legendeneinträgen (Serien)

Klicken Sie in der Überschrift auf die Schaltfläche Hinzufügen. Excel zeigt das Dialogfeld Serie bearbeiten an. (Siehe Abbildung 2.)

  1. Geben Sie im Feld Serienname 2015 ein.

  2. Geben Sie im Feld Serienwerte Folgendes ein: ‚2015‘! Vorheriges Jahr.

  3. Klicken Sie auf die Schaltfläche OK. Die Datenreihe wird jetzt im Dialogfeld Datenquelle auswählen angezeigt.

  4. Klicken Sie erneut auf dieselbe Schaltfläche Hinzufügen, auf die Sie in Schritt 6 geklickt haben. Excel zeigt erneut das Dialogfeld Datenreihe bearbeiten an.

  5. Geben Sie im Feld Serienname 2016 ein.

  6. Geben Sie im Feld Serienwerte Folgendes ein: ‚2016‘! CurrentYear.

  7. Klicken Sie auf die Schaltfläche OK. Diese zweite Datenreihe wird jetzt im Dialogfeld Datenquelle auswählen angezeigt.

  8. Auf der rechten Seite des Dialogfelds unter Horizontal (Kategorie)

Klicken Sie in der Überschrift Achsenbeschriftungen auf die Schaltfläche Bearbeiten. Excel zeigt das Dialogfeld Achsenbeschriftungen an. (Siehe Abbildung 3.)

  1. Geben Sie im Achsenbeschriftungsbereich Folgendes ein: ‚2016‘! Daten.

  2. Klicken Sie auf die Schaltfläche OK. Das Dialogfeld Datenquelle auswählen sollte vollständig mit den erforderlichen Informationen ausgefüllt sein. (Siehe Abbildung 4.)

  3. Klicken Sie auf OK, um das Dialogfeld Datenquelle auswählen zu schließen.

Ihr aktualisiertes Diagramm, in dem nur die Daten bis zum heutigen Datum angezeigt werden, sollte jetzt sichtbar sein. Sie können das Diagramm nach Bedarf weiter formatieren.

(Zum Beispiel möchten Sie wahrscheinlich die Daten im Diagramm so formatieren, dass sie kein Jahr enthalten.) Außerdem ist das Diagramm dynamisch, sodass beim Öffnen der Arbeitsmappe morgen ein Tag mehr angezeigt wird als zuvor heute.

Eine andere Möglichkeit, damit umzugehen, besteht darin, zu überdenken, wie Sie Ihre Daten speichern.

Anstatt alle Ihre Niederschlagswerte in separaten Arbeitsblättern (nach Jahr) zu speichern, sollten Sie sie alle in einem einzigen Arbeitsblatt speichern. Da Excel über eine Million Datenzeilen in einem Arbeitsblatt verarbeiten kann, stoßen Sie nicht auf praktische Einschränkungen. (Eine Million Zeilen entsprechen weit über 2.700 Jahren.)

Jetzt können Sie in einem anderen Arbeitsblatt zwei Array-Formeln verwenden, um den kumulierten Niederschlag für beide Jahre bis heute zu berechnen. Die folgende Array-Formel liefert den Niederschlag für das Vorjahr:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000<=EDATE(NOW(),-12),1,0)))

Dies setzt voraus, dass sich die ursprünglichen Niederschlagswerte in einem Arbeitsblatt mit dem Namen Daten befinden und nicht mehr als 1000 Zeilen umfassen. (Sie können eine dieser Optionen nach Bedarf ändern.) Um den aktuellen Niederschlag für dieses Jahr zu erhalten, können Sie die folgende Array-Formel verwenden:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000<=NOW(),1,0)))

Denken Sie daran: Dies sind beide Array-Formeln, daher sollten sie mit Strg + Umschalt + Eingabetaste eingegeben werden. Der von jeder Formel zurückgegebene Einzelwert repräsentiert den kumulierten Niederschlag von jedem Jahr bis heute. Diese beiden Werte können dann in jedem gewünschten Diagramm verwendet werden.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13427) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.