Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt dynamisch

Summenprodukt dynamisch
15.11.2015 01:09:03
Albrecht
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

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt dynamisch
15.11.2015 03:03:47
fcs
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.

Anzeige
AW: ich schlage INDEX() vor ...
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
.. , - ...

Oups, deine Antwort nicht gesehen ;-)) o.T.
15.11.2015 08:16:59
Sepp
Gruß Sepp

Anzeige
AW: ich schlage INDEX() vor ...
15.11.2015 10:12:39
Albrecht
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

Anzeige
AW: ich schlage INDEX() vor ...
15.11.2015 10:21:57
Sepp
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

Anzeige
AW: Summenprodukt dynamisch
15.11.2015 08:16:20
Sepp
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

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

AW: doch noch nicht ganz gelöst
15.11.2015 10:53:56
Albrecht
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

Anzeige
AW: wie Sepp auch schon schrieb, wobei ...
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
.. , - ...

Anzeige
AW: doch noch nicht ganz gelöst
15.11.2015 11:52:39
Albrecht
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

AW: ich bin zu blöd...
15.11.2015 12:40:52
Albrecht
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...

Anzeige
AW: ich bin zu blöd...
15.11.2015 14:09:59
Sepp
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

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

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige