HERBERS Excel-Forum - das Archiv
Thema: Summenprodukt dynamisch
-
Summenprodukt dynamischAlbrecht
-
AW: ich schlage INDEX() vor ...Albrecht
-
AW: Summenprodukt dynamischAlbrecht
-
AW: doch noch nicht ganz gelöstAlbrecht
-
AW: doch noch nicht ganz gelöstAlbrecht
-
AW: ich bin zu blöd...Albrecht
-
AW: ich bin zu blöd...Albrecht
Summenprodukt dynamisch
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
AW: Summenprodukt dynamisch
fcs
Hallo Albrecht,
mit der Funktion BEREICH.VERSCHIEBEN erreicht man die Dynamik abhängig von der Jahreszahl.
Gruß
Franz
Gesamt
| A | B | C | D | |
| 1 | 2015 | Fußball | Handball | Basketball |
| 2 | Hans | 0 | 4 | 12 |
| 3 | Georg | 7 | 0 | 0 |
| 4 | Fritz | 1 | 0 | 0 |
| 5 | Karl | 0 | 8 | 0 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Die Formel in B2 kann man nach rechts und unten kopieren.
AW: ich schlage INDEX() vor ...
...
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.
Sepp
Gruß Sepp
AW: ich schlage INDEX() vor ...
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
AW: ich schlage INDEX() vor ...
Sepp
Hallo Albrecht,
du musst auf die $ achten und bei deinem Tabellenaufbau heißt es bei meiner Formel am Ende +2 statt +1
Productores
| A | B | C | D | E | F | G | H | I | J | K | |
| 1 | 2015 | Hectareas | |||||||||
| 2 | Avena | Cebada | Descanso | Haba | Papa | Pasto Natural | Quinua Blanca | Quinua Mixta | Quinua Roja | Trébol | |
| 3 | Jorge | 4,25 | 5 | ||||||||
| 4 | Juan | 2 | |||||||||
| 5 | Perez, Juan | 5,2 | |||||||||
| 6 | Gerardo | ||||||||||
| 7 | Liliana | ||||||||||
| 8 | Eduardo |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: Summenprodukt dynamisch
Sepp
Hallo Albrecht,
mit INDEX(), besser als BEREICH.VERSCHEBEN, da diese Funktion Volatil ist.
Gesamt
| A | B | C | D | E | |
| 1 | 2013 | Fußball | Handball | Basketball | |
| 2 | Hans | 3 | 5 | 0 | |
| 3 | Georg | 2 | 0 | 3 | |
| 4 | Fritz | 0 | 0 | 1 | |
| 5 | Karl | 6 | 0 | 0 | |
| 6 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: Summenprodukt dynamisch
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
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
AW: doch noch nicht ganz gelöst
Sepp
Hallo Albrecht,
meine Antwort übersehen?
https://www.herber.de/forum/messages/1458434.html
Gruß Sepp
AW: wie Sepp auch schon schrieb, wobei ...
...
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
.. , - ...
AW: doch noch nicht ganz gelöst
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...
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...
AW: ich bin zu blöd...
Sepp
Hallo Albrecht,
so?
Productores
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
| 1 | 2015 | Hectareas | Producción estimada (solo cultivos a certificar, solo orgánico!) | |||||||||||||
| 2 | Avena | Cebada | Descanso | Haba | Papa | Pasto Natural | Quinua Blanca | Quinua Mixta | Quinua Roja | Trébol | Quinua Blanca | Quinua Mixta | Quinua Roja | |||
| 3 | Jorge | 4,25 | 5 | 4750 | ||||||||||||
| 4 | Juan | 2 | ||||||||||||||
| 5 | Perez, Juan | 3 | 2000 | |||||||||||||
| 6 | Gerardo | |||||||||||||||
| 7 | Liliana | 1 | 800 | |||||||||||||
| 8 | Eduardo | |||||||||||||||
| 9 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: ich bin zu blöd...
Albrecht
Das +1 hat gefehlt...
Dankeschöne noch mal!
Albrecht
Summenprodukt dynamisch
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
AW: Summenprodukt dynamisch
fcs
Hallo Albrecht,
mit der Funktion BEREICH.VERSCHIEBEN erreicht man die Dynamik abhängig von der Jahreszahl.
Gruß
Franz
Gesamt
| A | B | C | D | |
| 1 | 2015 | Fußball | Handball | Basketball |
| 2 | Hans | 0 | 4 | 12 |
| 3 | Georg | 7 | 0 | 0 |
| 4 | Fritz | 1 | 0 | 0 |
| 5 | Karl | 0 | 8 | 0 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Die Formel in B2 kann man nach rechts und unten kopieren.
AW: ich schlage INDEX() vor ...
...
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.
Sepp
Gruß Sepp
AW: ich schlage INDEX() vor ...
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
AW: ich schlage INDEX() vor ...
Sepp
Hallo Albrecht,
du musst auf die $ achten und bei deinem Tabellenaufbau heißt es bei meiner Formel am Ende +2 statt +1
Productores
| A | B | C | D | E | F | G | H | I | J | K | |
| 1 | 2015 | Hectareas | |||||||||
| 2 | Avena | Cebada | Descanso | Haba | Papa | Pasto Natural | Quinua Blanca | Quinua Mixta | Quinua Roja | Trébol | |
| 3 | Jorge | 4,25 | 5 | ||||||||
| 4 | Juan | 2 | |||||||||
| 5 | Perez, Juan | 5,2 | |||||||||
| 6 | Gerardo | ||||||||||
| 7 | Liliana | ||||||||||
| 8 | Eduardo |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: Summenprodukt dynamisch
Sepp
Hallo Albrecht,
mit INDEX(), besser als BEREICH.VERSCHEBEN, da diese Funktion Volatil ist.
Gesamt
| A | B | C | D | E | |
| 1 | 2013 | Fußball | Handball | Basketball | |
| 2 | Hans | 3 | 5 | 0 | |
| 3 | Georg | 2 | 0 | 3 | |
| 4 | Fritz | 0 | 0 | 1 | |
| 5 | Karl | 6 | 0 | 0 | |
| 6 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: Summenprodukt dynamisch
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
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
AW: doch noch nicht ganz gelöst
Sepp
Hallo Albrecht,
meine Antwort übersehen?
https://www.herber.de/forum/messages/1458434.html
Gruß Sepp
AW: wie Sepp auch schon schrieb, wobei ...
...
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
.. , - ...
AW: doch noch nicht ganz gelöst
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...
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...
AW: ich bin zu blöd...
Sepp
Hallo Albrecht,
so?
Productores
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
| 1 | 2015 | Hectareas | Producción estimada (solo cultivos a certificar, solo orgánico!) | |||||||||||||
| 2 | Avena | Cebada | Descanso | Haba | Papa | Pasto Natural | Quinua Blanca | Quinua Mixta | Quinua Roja | Trébol | Quinua Blanca | Quinua Mixta | Quinua Roja | |||
| 3 | Jorge | 4,25 | 5 | 4750 | ||||||||||||
| 4 | Juan | 2 | ||||||||||||||
| 5 | Perez, Juan | 3 | 2000 | |||||||||||||
| 6 | Gerardo | |||||||||||||||
| 7 | Liliana | 1 | 800 | |||||||||||||
| 8 | Eduardo | |||||||||||||||
| 9 |
| Formeln der Tabelle | ||||
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp
AW: ich bin zu blöd...
Albrecht
Das +1 hat gefehlt...
Dankeschöne noch mal!
Albrecht
Excel-Beispiele