Office: Formel Summenprodukt

Hallo an Alle,

habe mal eine spezielle Frage zur Formel summenprodukt:

Ich werte auf einer Seite eine umfangreiche Datentabelle u.a. nach veschiedenen Werten pro Monat aus. Dabei wird unterschieden zwischen IM- (= ausgewählter Monat) und PER-Werten.

Bsp.: Wenn die April-Zahlen ausgewertet werden, dann wird bei IM-Wert nur der April-Wert angezeigt und beo dem PER-Wert die addierten Werte für Jan+Feb+Mrz+Apr.

Zur Vereinfachung habe ich für die IM-Werte ein Dropdown-Feld (=A1) mit den einzelnen Monaten angelegt und es mit dem Summenproduktformel verknüpft.
=SUMMENPRODUKT((A2:A1000=A1)*...)
Funktioniert super!!!

Mein Problem sind jetzt die PER-Werte!!!
Die PER-Werte ermittele ich über die Formel:
=SUMMENPRODUKT((A2:A1000={"Jan"."Feb"."Mär"})*....).
Wie kann ich die Monatsbereiche (Jan-Feb, Jan-Mrz. Jan-Apr,...) hinterlegen, damit ich automatisch die entsprechende Verknüpfung in der Summenprodukt-Formel habe???
Also das z.B. in B1 die auszuwertenden Zeiträume hinterlegt sind ???

Da die Werte für IM und PER zusammenhängen (Wenn ich den IM-Wert für Nov. brauche, dann brauche ich auch immer den PER-Wert für den Zeitraum Jan-Nov), könnte es auch eine Verknüpfung für das Auswahlfeld geben (z.B. über eine Wenn-Funktion).

Bin gespannt auf Eure Hilfe. Würde mir meine Tabelle ungemein vereinfachen. Andernfalls müßte ich 12 Register für die einzelnen Monate anlegen, was weder elegant noch übersichtlich wäre (( Hoffe aber auf Eure Hilfe....

Vielen Dank schon einmal vorab.

Dirk

 

Wenn Du die Monate nicht als Worte, sondern als Zahlen hättest...

Dann wäre vielleicht etwas in der Art möglich:

Code:

IM:  =SUMMENPRODUKT((A2:A1000=A1)*...)
PER: =SUMMENPRODUKT((A2:A1000<=A1)*....)

Gruß, Michael

 

Hallo Michael,

das mit den Zahlen ist eine gute Idee. Auf diesen Ansatz bin ich einfach nicht...

Kannst Du mir noch sagen, mit welcher Formel ich aus einer Datumsangabe den Monat "extrahieren" kann ?

Bsp.: 05.10.2009 = 10

Dann müßte ich die Formeln einfach umstricken können.

Vielen Dank schon einmal.

Dirk

 

Hallo Dirk,

1) Du kannst Dir zB. einen freien Zeilenbereich für 12 Zellen suchen, in meinem Beispiel sind das die Zellen G1:R1; in diesen Bereich speicherst Du Dir die 12 Monatsnamen "Jan","Feb",..,"Nov","Dez"
2) Du speicherst in die Zelle B1 die simple Formel:
=A1
Damit steht in B1 der 'Bis'-Monat, als 'Von'-Monat für das Summationsmonatsintervall neheme ich stets 'Jan' an.

3) Dann speicherst Du folgende Formel in die Zelle Deiner Wahl:

=SUMMENPRODUKT((A2:A1000=BEREICH.VERSCHIEBEN(G1;0;0;1;VERGLEICH(B1;G1:R1;0)))*(B2:B1000))

4) Falls Du in 1) einen anderen Zeilenbereich wählst, musst Du die rot gekennzeichneten Formelstellen entsprechend anpassen.

Das ist alles, was zu tun ist.

 

Hallo Dirk,

Um die Monatszahl zu erhalten: =MONAT(Datumszelle)
Um die abgekürzte Monatsbezeichnung zu erhalten: =TEXT(Datumszelle;"MMM")
Um die volle Monatsbezeichnung zu erhalten: =TEXT(Datumszelle;"MMMM")

Aber ich habe Dir vorhin ohnehin eine Lösung für Deine abgekürzten Monatsbezeichnungen geschickt.

 

Code:

=MONAT(zelle)

Und dazu passend gibt's noch tag(), jahr(), stunde(), ...

Gruß, Michael