Summenprodukt dynamisch

Bild

Betrifft: Summenprodukt dynamisch
von: Albrecht Benzing
Geschrieben am: 15.11.2015 01:09:03

Liebe Experten,
ich bin neu hier. Ich murkse seit Stunden an einem Summenprodukt rum, siehe:
https://www.herber.de/bbs/user/101519.xlsx
Im Blatt "Einzel" werden die Daten eingetragen, die im Blatt "Gesamt" per Summenprodukt zusammengefasst werden sollen. Dabei sollen die Punkte aus "Einzel" nicht aus einer bestimmten Spalte addiert werden, sondern in Abhängigkeit von der Jahreszahl in "Gesamt" A1 (gelb). Jetzt lautet die Formel in den grünen Zellen
=SUMMENPRODUKT((Einzel!$B$1:$G$1=Gesamt!$A$1)*(Einzel!$B$3:$G$8=Gesamt!B$1) *(Einzel!$A$3:$A$8=Gesamt!$A2)*(Einzel!$G$3:$G$8))
Hat jemand einen Tipp, wodurch ich den letzten Array (Einzel!$G$3:$G$8), der sich jetzt auf eine fixe Spalte bezieht, ersetzen könnte, damit das klappt?
Vielen Dank im Voraus!
Albrecht

Bild

Betrifft: AW: Summenprodukt dynamisch
von: fcs
Geschrieben am: 15.11.2015 03:03:47
Hallo Albrecht,
mit der Funktion BEREICH.VERSCHIEBEN erreicht man die Dynamik abhängig von der Jahreszahl.
Gruß
Franz
Gesamt

 ABCD
12015FußballHandballBasketball
2Hans0412
3Georg700
4Fritz100
5Karl080

Formeln der Tabelle
ZelleFormel
B2=SUMMENPRODUKT((Einzel!$A$3:$A$8=$A2)*(BEREICH.VERSCHIEBEN(Einzel!$A$3:$A$8;0;VERGLEICH($A$1;Einzel!$1:$1;0)-1)=B$1)*(BEREICH.VERSCHIEBEN(Einzel!$A$3:$A$8;0;VERGLEICH($A$1;Einzel!$1:$1;0))))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Die Formel in B2 kann man nach rechts und unten kopieren.

Bild

Betrifft: AW: ich schlage INDEX() vor ...
von: ... neopa C
Geschrieben am: 15.11.2015 08:02:10
Hallo Albrecht,
... auch in Kombination mit der Funktion VERGLEICH().
INDEX() ist keine volatile Funktion (dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=41) und ist mE auch einfacher in der Anwendung.
Folgende Formel in Gesamt!B2:
=SUMMENPRODUKT((Einzel!$B$1:$G$1=$A$1)*(Einzel!$B$3:$G$8=B$1)*(Einzel!$A$3:$A$8=$A2)*(INDEX(Einzel!$B$3:$G$8;;VERGLEICH($A$1;Einzel!$1:$1;))))
nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: Oups, deine Antwort nicht gesehen ;-)) o.T.
von: Sepp
Geschrieben am: 15.11.2015 08:16:59

Gruß Sepp


Bild

Betrifft: AW: ich schlage INDEX() vor ...
von: Albrecht Benzing
Geschrieben am: 15.11.2015 10:12:39
Hallo nochmal,
ich hab doch etwas zu früh gejubelt. Mit meiner Dummy-Datei funktionierte es prima, aber beim Versuch, die Formel in meine eigentliche Datei zu übertragen (mit der ich euch eigentlich verschonen wollte), bin ich dann schon wieder gescheitert... Ich habe sie jetzt doch hochgeladen:
https://www.herber.de/bbs/user/101523.xlsx
Dort in der Zelle B3 (rosa) die Formel von Sepp, in B4 (blau) die Formel von Werner. Jedesmal kriege ich die Fehlermeldung Wert#. Was mache ich falsch?
Beste Grüße
Albrecht

Bild

Betrifft: AW: ich schlage INDEX() vor ...
von: Sepp
Geschrieben am: 15.11.2015 10:21:57
Hallo Albrecht,
du musst auf die $ achten und bei deinem Tabellenaufbau heißt es bei meiner Formel am Ende +2 statt +1
Productores

 ABCDEFGHIJK
12015Hectareas         
2 AvenaCebadaDescansoHabaPapaPasto NaturalQuinua BlancaQuinua MixtaQuinua RojaTrébol
3Jorge4,25     5   
4Juan 2        
5Perez, Juan       5,2  
6Gerardo          
7Liliana          
8Eduardo          

Formeln der Tabelle
ZelleFormel
B3=SUMMENPRODUKT((Parcelas!$A$4:$A$500=Productores!$A3)*(INDEX(Parcelas!$K$4:$Z$500;;VERGLEICH($A$1;Parcelas!$K$1:$Z$1;0))=Productores!B$2)*INDEX(Parcelas!$K$4:$Z$500;;VERGLEICH($A$1;Parcelas!$K$1:$Z$1;0)+2))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: AW: Summenprodukt dynamisch
von: Sepp
Geschrieben am: 15.11.2015 08:16:20
Hallo Albrecht,
mit INDEX(), besser als BEREICH.VERSCHEBEN, da diese Funktion Volatil ist.
Gesamt

 ABCDE
12013FußballHandballBasketball 
2Hans350 
3Georg203 
4Fritz001 
5Karl600 
6     

Formeln der Tabelle
ZelleFormel
B2=SUMMENPRODUKT((Einzel!$A$3:$A$8=$A2)*(INDEX(Einzel!$B$3:$G$8;;VERGLEICH($A$1;Einzel!$B$1:$G$1;0))=B$1)*INDEX(Einzel!$B$3:$G$8;;VERGLEICH($A$1;Einzel!$B$1:$G$1;0)+1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: AW: Summenprodukt dynamisch
von: Albrecht Benzing
Geschrieben am: 15.11.2015 09:28:30
Hallo zusammen,
das ging ja echt flott. Herzlichen Dank! Beide Lösungen scheinen sehr gut zu funktionieren!
Albrecht

Bild

Betrifft: AW: doch noch nicht ganz gelöst
von: Albrecht Benzing
Geschrieben am: 15.11.2015 10:53:56
Jetzt doch noch mal,
ich hatte zu früh gejubelt... Während es bei meiner Dummy-Datei prima geklappt hat, kriege ich die Übertragung in meine eigentliche Datei einfach nicht hin. Ich wollte euch eigentlich damit verschonen, aber hier jetzt doch:
https://www.herber.de/bbs/user/101525.xlsx
Dort im Tabellenblatt "Productores" habe ich in B3 Sepps Lösung, in B4 Werners. Bei beiden kriege ich die Fehlermeldung Wert#. Was mach ich falsch?
Beste Grüße

Bild

Betrifft: AW: doch noch nicht ganz gelöst
von: Sepp
Geschrieben am: 15.11.2015 10:58:09
Hallo Albrecht,
meine Antwort übersehen?
https://www.herber.de/forum/messages/1458434.html

Gruß Sepp


Bild

Betrifft: AW: wie Sepp auch schon schrieb, wobei ...
von: ... neopa C
Geschrieben am: 15.11.2015 11:39:11
Hallo Albrecht,
... meine angepasste Formel an Deine derzeitige Datenstruktur etwas anders ausfällt (beachte insbesondere den Versatz in der Suchmatrix von VERGLEICH() gegenüber den sonst definierten Auswertungsbereich; zusätzlich können die 2012 Daten berücksichtigen werden):
In B3:
=SUMMENPRODUKT((Parcelas!$G$1:$Z$1=$A$1)*(Parcelas!$A$4:$A$500=$A3)*(Parcelas!$G$4:$Z$500=B$2)*(INDEX(Parcelas!$G$4:$Z$500;;VERGLEICH($A$1;Parcelas!$E$1:$Z$1;))))

Gruß Werner
.. , - ...

Bild

Betrifft: AW: doch noch nicht ganz gelöst
von: Albrecht Benzing
Geschrieben am: 15.11.2015 11:52:39
Hallo zusammen,
Ja, jetzt klappts prima. Noch mals vielen Dank an alle! (Irgendwie kapiere ich bei diesem Forum noch nicht so ganz, wo ich was wie rein schreiben muss, das kommt hoffentlich noch...)
Schönen Sonntag!
Albrecht

Bild

Betrifft: AW: ich bin zu blöd...
von: Albrecht Benzing
Geschrieben am: 15.11.2015 12:40:52
Jedesmal wenn ich denke, ich habe es jetzt wirklich kapiert, tut sich das nächste Hindernis auf. Müsste wohl mein Level von "gut" auf "mies" korrigieren... Ich will jetzt im Blatt "Productores" in Spalten L und folgende die Ertragsmengen addieren aus dem Blatt "Parcelas", aber mit einer zusätzlichen Bedingungen verbunden, nämlich dass im Blatt "Parcelas" in der Spalte "Status" für 2015 (Spalte W) die Bezeichnung "Org" steht.
https://www.herber.de/bbs/user/101528.xlsx
Ich habe also in die Summenproduktformel in Productores L3 den zusätzlichen Array *(INDEX(Parcelas!$G$3:$AE$499;;VERGLEICH($A$1;Parcelas!$G$1:$AE$1;0))="Org") reingebastelt. Das Ergebnis müsste eigentlich 4750 sein, aber es ist 0.
Schönes Spielchen...

Bild

Betrifft: AW: ich bin zu blöd...
von: Sepp
Geschrieben am: 15.11.2015 14:09:59
Hallo Albrecht,
so?
Productores

 ABCDEFGHIJKLMNOP
12015Hectareas         Producción estimada (solo cultivos a certificar, solo orgánico!)    
2 AvenaCebadaDescansoHabaPapaPasto NaturalQuinua BlancaQuinua MixtaQuinua RojaTrébolQuinua BlancaQuinua MixtaQuinua Roja  
3Jorge4,25     5   4750    
4Juan 2             
5Perez, Juan       3   2000   
6Gerardo               
7Liliana        1   800  
8Eduardo               
9                

Formeln der Tabelle
ZelleFormel
L3=SUMMENPRODUKT((Parcelas!$A$3:$A$499=Productores!$A3)*(INDEX(Parcelas!$G$3:$AE$499;;VERGLEICH($A$1;Parcelas!$G$1:$AE$1;0))=Productores!L$2)*(INDEX(Parcelas!$G$3:$AE$499;;VERGLEICH($A$1;Parcelas!$G$1:$AE$1;0)+1)="Org")*INDEX(Parcelas!$G$3:$AE$499;;VERGLEICH($A$1;Parcelas!$G$1:$AE$1;0)+3))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: AW: ich bin zu blöd...
von: Albrecht Benzing
Geschrieben am: 15.11.2015 16:13:11
Das +1 hat gefehlt...
Dankeschöne noch mal!
Albrecht

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Summenprodukt dynamisch"