SUMMENPRODUKT mit variablen "Arrays"

Bild

Betrifft: SUMMENPRODUKT mit variablen "Arrays"
von: NormanZ
Geschrieben am: 23.09.2015 21:54:57

Hallo zusammen,
ich starte nochmal einen Versuch.
Statt mit ZÄHLENWENNS versuche ich nun den im Bild dargestellten Sachverhalt mit SUMMENPRODUKT umzusetzen.
Userbild
Das Problem kommt, wenn ich in einer der Zellen, die in der SUMMENPRODUKT-Formel einbezogen ist, keinen Eintrag habe. Mein Ziel soll sein, dass die Formel dann auch mit weniger Faktoren zufrieden ist. Also wenn in einer nichts steht, dann diese auch nicht berücksichtigt, sondern das Ergebnis ausschließlich mit Einträgen der anderen errechnet wird. Aktuell kommt in so einem Fall dann immer nur 0.
Kann mir da jemand folgen und auch noch helfen?
Das wär so schön.
Vielen Dank und Gruß,
Norman Z.

Bild

Betrifft: und wieder Beispieldatei!
von: Sepp
Geschrieben am: 23.09.2015 22:06:59
Hallo Norman,
ich weiß nicht wieso auf einmal fast jeder ein Bild seiner Tabelle hoch lädt und glaubt, dass irgendjemand Lust hat, das ganze nachzubauen.
Lade eine Beispieldatei hoch!

Gruß Sepp


Bild

Betrifft: Im Prinzip kann das auch niemand nachbauen, ...
von: Luc:-?
Geschrieben am: 24.09.2015 03:37:02
Sepp (& Norman),
denn das hier Gezeigte ist offensichtlich ziemlich irrelevant, da sich die Fml auf Daten in 2 anderen Blättern, die hier gerade nicht gezeigt wdn, zu beziehen scheint. Wir müssten uns also alles auch noch ausdenken!
Allerdings kann man auch schon sagen, Norman (& Sepp),
dass eine Leerzelle im Vgl mit einer nicht-leeren Zelle idR FALSCH ergeben wird, was bei einer ProdukteSumme diesen Faktor automatisch 0 wdn lässt, was in der ganzen Zeile zu einem Ergebnis 0 führt. Es wdn also Zeilen mit Leerzellen gar nicht berücksichtigt. Offensichtlich geht es hier aber darum, nur den jeweiligen Vgl nicht zu berücksichtigen! Das erreicht man bei einer Multipli­kation bekanntlich durch Multiplizieren mit 1! Da aber alle anderen Vgle auch 1 oder 0 ergeben, wird wohl noch eine Abfrage ala ISTLEER, ISTTEXT, ISTKTEXT oder ISTZAHL erforderlich sein, was man für ein ProduktSummen-Element prinzipiell wie folgt realisieren könnte:
Var1: (Tapete!A1:A160=Übersicht!H4)+ISTLEER(Tapete!A1:A160)
Sollen hierin auch alle (nicht-leeren) Zellen einbezogen wdn, wenn die VglsZelle leer ist, dann so:
Var2: --((Tapete!A1:A160=Übersicht!H4)+ISTLEER(Tapete!A1:A160)>0)
Ggf ließe sich die Fml auch mit Standard­Fmln noch kürzen (evtl eine Aufgabe für die Aggregierer wie die Sepps und neopa ;-) ).
Ich habe nun aber noch andere Möglichkeiten, über die bisher nur sehr Wenige verfügen. Eine davon hier mal für die Wenigen zum Testen: ;-)
{=SUMME(VectAr(--((Tapete!A1:F160=Übersicht!H4:M4)+ISTLEER(Tapete!A1:F160)>0);;6))}
Die UDF erzeugt einen vertikalen Vektor aus horizontalen Vektoren. Die Einzel­Elemente der Letzteren wdn miteinander multipliziert (6=PRODUKT), so dass ein Vektor dieser Produkte entsteht. Davon kann dann die Summe gebildet wdn. Die MatrixFml-Form ist hierbei erforderlich!
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: NormanZ
Geschrieben am: 24.09.2015 10:06:47
Hallo,
vielen Dank für den bisherigen Einsatz!
Leider komme ich mit den zwei Varianten noch nicht weiter. Auch damit wird 0 ausgegeben, sobald ich ein Feld offen lasse bzw. wenn ich sie auf mehrere Felder erweitere.
Also z.B.
=SUMMENPRODUKT((Tapete!A1:A160=Übersicht!H4)+ISTLEER(Tapete!A1:A160)>0; (Tapete!B1:B160=Übersicht!I4)+ISTLEER(Tapete!B1:B160)>0)
Irritiert war ich auch über die zwei Minuszeichen beim Anfang von Variante 2, soll das so?
Wenn das mit der Vektor-Geschichte anders sein sollte, würde ich mich sehr freuen, kurz erläutert zu bekommen, wie dies aktiviert wird. Ich habe die Fml einfach kopiert, eingefügt und dann auch mit STRG, Shift und Return "aktiviert", aber da kommt nix.
Habe den Wink verstanden und nun keine Bilddatei, sondern eine Beispiel-Datei angehangen. :-)
https://www.herber.de/bbs/user/100353.xlsm
Im Endprodukt soll in Zelle Übersicht!N4 eine Formel hinterlegt sein, die entsprechend flexibel die Ergebnisse rausgibt. Also im aktuellen Eintrag eine 1, aber sobald die Zellen J4 und K4 gelöscht werden, soll das Ergebnis 2 sein. (Weniger Spezialisierung, also höheres Ergebnis)
WIE GEHT DAS?? (verzweifel...)
Hoffe weiterhin auf Erkenntnisse! :-)
Dank und Gruß,
Norman

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: GeorgH
Geschrieben am: 24.09.2015 11:04:07
Hallo Norman,
anbei eine Lösung. Bitte mal testen.
https://www.herber.de/bbs/user/100360.xlsm
Viele Grüße
Georg

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: NormanZ
Geschrieben am: 24.09.2015 11:35:17
Hallo Georg, das ist schon super, aber noch nicht komplett. Ich bekomme auch in deiner Datei die Matrix in Gang, übertragen auf meine aber nicht. Woran kann das denn liegen?

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: GeorgH
Geschrieben am: 24.09.2015 11:43:37
Hallo Norman,
Matrixformeln mit: "Shift + Strg + Enter" eingeben!
Aber die Formel von Sepp ist eleganter, daher bitte die Formel von Sepp verwenden -:)
Viele Grüße
Georg

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: NormanZ
Geschrieben am: 24.09.2015 12:07:52
Hi Georg, habe gerade auch Sepps Formel entdeckt und die klappt perfekt. Trotzdem danke für Deine Version. Aber das Matrix-Problem ist noch nicht gelöst. Bei deiner Übungsdatei klappt das auch bei mir mit STRG+Shift+Return, in meiner dann nicht mehr. Die Klammern kommen wohl, aber die Funktion bleibt aus.
Habe ich etwas nicht beachtet?
Gruß,
Norman

Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: Sepp
Geschrieben am: 24.09.2015 11:06:01
Hallo Norman,

=SUMMENPRODUKT(((Tapete!A1:A160=H4)+((Tapete!A1:A160<>"")*(H4="")))*((Tapete!B1:B160=I4) +((Tapete!B1:B160<>"")*(I4="")))*((Tapete!C1:C160=J4)+((Tapete!C1:C160<>"")*(J4=""))) *((Tapete!D1:D160=K4)+((Tapete!D1:D160<>"")*(K4="")))*((Tapete!E1:E160=L4)+((Tapete!E1:E160<>"") *(L4="")))*((Tapete!F1:F160=M4)+((Tapete!F1:F160<>"")*(M4="")))) 


Bild

Betrifft: AW: Im Prinzip kann das auch niemand nachbauen, ...
von: NormanZ
Geschrieben am: 24.09.2015 12:04:36
JJAAAAAAAAAAAAAAAAAAAA!!!!!
SUPER, vielen Dank, das war es!! :-)
Ein TOP-Forum mit TOP-Leuten :-)
Beste Grüße,
Norman

 Bild

Beiträge aus den Excel-Beispielen zum Thema "SUMMENPRODUKT mit variablen "Arrays""