Tipps & Tricks 22.02.2014, 09:52 Uhr

Summe über variablen Matrixbereich ermitteln

Problemstellung: Sie haben in Spalte A ab A6 die Monatsnamen Januar bis Dezember und in Spalte B ab B6 die zugehörigen Umsatzzahlen. Nun wollen Sie die Zahlen aus einem Von-/Bis-Bereich zusammenzählen, beispielsweise von März bis Juni. Und dies, ohne jedes Mal die Formel anzupassen.
Lösung: In Excel und LibreOffice Calc kommen Sie mit der Funktion SUMMENPRODUKT ans Ziel.
Die Ausgangslage ist also wie gehabt: In A6 bis A17 stehen die Monate Januar bis Dezember. Tippen Sie aber hier anstelle der Monatsnamen gleich das Excel-Datum ein, also für den Monat März den 1.3.2013. Soll Excel/Calc da dennoch nur die Monatsnamen anzeigen, greifen Sie zu einer Benutzerdefinierten Zellformatierung: Rechtsklick, Zellen formatieren/Benutzerdefiniert. In unserem Beispiel ist dies MMMM.
In Zelle B1 tippen Sie das Datum für den «Von»-Monat ein (z.B. 1.3.2013 für März), in Zelle B2 jenes für den «Bis»-Monat (z.B. 1.6.2013 für Juni). Verwenden Sie auch hier die vorhin erwähnte benutzerdefinierte Formatierung MMMM. Mit dem unten angefügten Zusatztipp lässt sich die Auswahl später zusätzlich vereinfachen. Aber eins nach dem anderen! In Zelle D1 soll Excel die Summe der nebenan gewählten Monate anzeigen.
In D1 verwenden Sie also folgende Formel:
=SUMMENPRODUKT((A6:A17>=B1)*(A6:A17<=B2)*(B6:B17))
Das war es eigentlich schon! Aber im Screenshot entdecken Sie, dass wir in den Auswahlzellen B1 und B2 kein Datum eintippen, sondern ein Ausklappmenü haben.
Hier der Zusatztipp: Damit Sie bei der Auswahl des Von- und Bis-Wertes nicht jedesmal ein Datum eintippen müssen, können Sie den Monat hier per Ausklappmenü auswählen. Definieren Sie für den Bereich der Monatsnamen einen Namen. Markieren Sie hierzu die Zellen A6:A17 und gehen in Excel 2013 zu Formeln/Definierte Namen/Namen definieren
In LibreOffice Calc finden Sie das unter Daten/Bereich festlegen. Als Bezeichnung tippen Sie in beiden Programmen z.B. Datumsbereich ein. Markieren Sie die Von-/Bis-Zellen (hier B1 und B2). In Excel 2013 gehts jetzt zu Daten/Datenüberprüfung, in Calc gehts zu Daten/Gültigkeit. In beiden wählen Sie bei «Zulassen» den Eintrag «Liste» und tippen als «Quelle» dies ein: =Datumsbereich
Und hier selbiges in LibreOffice Calc
Bestätigen Sie das Fenster mit OK oder Schliessen, dann erscheinen beim Klick in die Auswahlzellen kleine Aufklapppfeile. Über die lassen sich jetzt die Monate ganz einfach wählen. (PCtipp-Forum)



Kommentare
Es sind keine Kommentare vorhanden.