Excel - Formeln und Funktionen - Tipp Nr. 1401

Datums-Wertreihen bequem analysieren

Tipp getestet unter Excel 2003, 2007, 2010;

Resi, die Leiterin des Hinterbacher Heimatmuseums listet die Besucherzahlen der einzelnen Öffnunstage penibel in einer Excel-Tabelle auf (im Beispiel in den Spalten A und B).
Sie hätte gerne eine Auswertung der Monatswerte, wobei die Monate in Zeilen und die Jahre in Spalten aufzulisten wären.
So löst sie das Problem:

  1. Sie erstellt eine Monatsreihe:
  2. Resi erstellt eine Jahresreihe, indem Sie in die Zelle E1 die Zahl 2011 schreibt und die Jahresreihe wie in Punkt 2. beschrieben nach rechts fortsetzt.
  3. Jetzt werden die einzelnen Monatssummen erstellt:
    1. Zuerst bildet Resi in der Zelle D2 die Summe aller Besucherzellen (B2 bis B 211) und setzt den Bezug absolut, damit sie die Formel später nach rechts kopieren kann, ohne den Bezug auf die Spalte B zu verlieren.
    2. Sie tippt ein: "=SUMME(B2:B211", betätigt dann die Taste (damit ändert sich der Bezug zu "$B$2:$B$211"), gibt noch eine schließende Klammer ein und schließt mit ab.
  4. Mit einem Trick schränkt Resi die Summe auf das Jahr 2011 und den Monat 1 (Jänner) ein. Da sie den Fast & Easy - Tipp Rechnen mit Wahrheitswerten gründlich studiert hat, setzt sie diese hier sinnvoll ein:
    Um zu ermitteln, ob ein Datum zum Jahr (Zelle E1) gehört, gibt sie innerhalb der Summenfunktion ein:

    (JAHR($A2:$A211)=E$1)*

    Das Ergebnis des Wahrheitswerts ist 0, wenn FALSCH bzw. 1, wenn WAHR, womit sicher gestellt ist, dass nur Werte aus dem angegebenen Jahr summiert werden. Die Formel bis hierher lautet also:

    =SUMME((JAHR($A2:$A211)=E$1)*$B$2:$B$211)

    Jetzt muss die Summierung auch noch auf den Monat (Zelle D2) beschränkt werden, analog zumJahr geschieht das mit

    (MONAT($A2:$A211)=$D2)*

    Und schon hat die Museumschefin die vollständige Formel:

    =SUMME((JAHR($A2:$A211)=E$1)*(MONAT($A2:$A211)=$D2)*$B$2:$B$211)

  5. Um die Bereiche in den Funktionen JAHR und MONAT interpretieren zu können, benötigt Excel eine Matrixformel. Resi schließt daher nicht mit ab, sondern mit der Tastenkombination + + . Die Formel wird von Excel in geschwungene Klammern eingefasst:

    {=SUMME((JAHR($A2:$A211)=E$1)*(MONAT($A2:$A211)=$D2)*$B$2:$B$211)}

    Vergleiche auch Tipp Matrixformeln verstehen.