Microsoft Excel

Herbers Excel/VBA-Archiv

Problem mit Summenprodukt Formel

Betrifft: Problem mit Summenprodukt Formel von: Christian
Geschrieben am: 16.08.2014 13:34:44

Hallo an alle,

würde mich freuen, wenn einer von euch mein Problem lösen kann.

Bin mal soweit hier:

{=SUMMENPRODUKT(--((D1:D5000>=5)*(D1:D5000<=5)*(E1:E5000>=D1:D5000)+(E1:E5000>=5)*(E1:E5000<=5)  *(D1:D5000>=E1:E5000)>0)) }
Eine wunderbare Formel, wenn es darum geht die Zeilen zu zählen, in denen entweder D oder E gleich 5 ist und der jeweils andere Wert größer gleich 5.

Jetzt stellt euch vor, ich mache 5 dieser Formeln, mit 1, 2, 3, 4 und 5 und bilde eine Summe daraus.

Geht das auch kürzer, also eine Formel, in der die Addition von 5 Summanden erspart bleibt und ich einfach nur alle 5 in 6 ändern muss, wenn auch noch die Zeilen addiert weden sollen, in denen D oder E gleich 6 ist und die andere Zahl größer gleich 6, hat da einer eine Lösung?

Gruß und Danke
Christian

  

Betrifft: AW: Problem mit Summenprodukt Formel von: {Boris}
Geschrieben am: 16.08.2014 21:30:48

Hi Christian,

die Abfrage

(D1:D5000>=5)*(D1:D5000<=5)

ist doch unnötig lang - sie entspricht doch

(D1:D5000=5).
Gleiches gilt für Spalte E.

=SUMMENPRODUKT(N((D1:D5000={1.2.3.4.5})*(E1:E5000>={1.2.3.4.5})+(E1:E5000={1.2.3.4.5}) *(D1:D5000>={1.2.3.4.5})>0))

Und ne {Arrayformel} ist das auch nicht ;-)

VG, Boris


  

Betrifft: AW: Problem mit Summenprodukt Formel von: Christian
Geschrieben am: 16.08.2014 21:47:05

Hallo Boris,

ich bin halt leider jemand der nicht ganz soviel Ahnung von Excel hat wie du und dem momentan die Systematik der Summenproduktformel, für was sie alles eingesetzt werden kann, wie sie aufgebaut ist, noch zu hoch ist. Die Formel die ich da hatte ist eine, die ich mir selbst durch Forenrecherche irgendwie versucht hatte zu basteln, bis sie das ausgegeben hatte, was sie sollte.

Aber ich möchte dich jetzt doch etwas fragen, eben aus mangelndem Wissen. Wozu ist vonnöten, das Ergebnis mit der N Formel in eine Zahl umzuwandeln? ich hab die Formel auch ohne N probiert und dann gab sie 0 aus. Mir erschließt sich nicht, was da passiert dass die N Formel aus 0 in meinem Fall jetzt 985 macht.

Jedenfalls deine Formel funktioniert. Ich gehe dann einfach mal davon aus, dass ich nur hinter die 5 jeweils noch .6 schreiben muss, wenn Zahlen dazukommen.

Aber noch eine Frage an euch Spezialisten am Rande. Wenn ich irgendwann mal fertig bin mit der Tabelle, würde der Teil in der geschweiften Klammer bis 57 gehen. Macht Excel das noch mit, oder werden da Grenzen überschritten, die andere Lösungen notwendig machen?

DAnke auf jeden Fall und viele Grüße
Christian


  

Betrifft: Deine Fml ist ja auch nicht grundsätzlich ... von: Luc:-?
Geschrieben am: 17.08.2014 03:59:43

…falsch, Christian,
sondern nur etwas umständlich und sollte im Original eigentlich das gleiche Ergebnis liefern wie Boris' Verkürzung.
Bei der XlFkt SUMMENPRODUKT handelt es sich um das, was dt Mathematiker Produkt[e]summe nennen (Übersetzungsfehler von MS), d.h., 2…255 Vektoren bzw Matrizen wdn Element für Element miteinander multipliziert und aus diesen Ergebnissen dann die Summe gebildet, weshalb die klassische Form dieser Fkt auch SUMMENPRODUKT(Array1;Array2;…;Array255) lautet. Allerdings fktt das auch mit nur 1em Argument, dann aber quasi nur als SUMME mit den (dann erwünschten) Besonderheiten von SUMMENPRODUKT wie zB Verarbeitung ganzer Bereiche (und ggf Datenfelder) auch ohne MatrixFml-Kennzeichnung, da sie idR nur einen Wert zum Ergebnis hat. Wenn aber wie hier Ausdrücke als Argument(e) auftreten, kann in Abhängigkeit von den in diesen verwendeten Fktt (falls diese selbst Datenfelder liefern) eine Kennzeichnung als MatrixFml erforderlich sein.
N ist idR im Zusammenhang mit INDIREKT erforderlich, hier aber nur eine Variante der Umwandlung eines Wahrheitswerts (aus dem Vgl) in eine Zahl (-- bzw 1* oder 0+ tut's hier auch.
Statt der von Boris verwendeten MatrixKonstante ({…}) kann natürlich auch ein diesen Zahlenbereich liefernder Ausdruck verwendet wdn. Das wäre auch zu bevorzugen, wenn es mal bis 57 gehen soll, damit der Schreibaufwand dann nicht so groß ist.
Andere FmlVarianten wären bspw …
{=SUMMENPRODUKT(--((D1:D5000=MTRANS(ZEILE(1:6)))*(E1:E5000>=D1:D5000)+(E1:E5000=MTRANS(ZEILE(1:6)) )*(D1:D5000>=E1:E5000)>0)) }
{=SUMMENPRODUKT(--((D1:D5000=MTRANS(ZEILE(1:6)))*(E1:E5000>=MTRANS(ZEILE(1:6))) +(E1:E5000=MTRANS(ZEILE(1:6)))*(D1:D5000>=MTRANS(ZEILE(1:6)))>0)) }
=SUMMENPRODUKT(--((D1:D5000=SPALTE(A:F))*(E1:E5000>=D1:D5000)+(E1:E5000=SPALTE(A:F))*(D1:D5000> =E1:E5000)>0))
=SUMMENPRODUKT(--((D1:D5000=SPALTE(INDIREKT("S1:S6";0)))*(E1:E5000>=D1:D5000) +(E1:E5000=SPALTE(INDIREKT("S1:S6";0)))*(D1:D5000>=E1:E5000)>0))
…und höchstwahrscheinlich auch …
=SUMMENPRODUKT((TEILERGEBNIS(5;INDIREKT(WECHSELN("D#:E#";"#";ZEILE(1:5000))))=SPALTE(A:F))*
(TEILERGEBNIS(4;INDIREKT(WECHSELN("D#:E#";"#";ZEILE(1:5000))))>=SPALTE(A:F)))
…wobei bei letzterer Variante auch die klassische Form möglich wäre.
Leider kann man hier nichts in benannte Fmln auslagern, weil sich die XLM-Fkt AUSWERTEN auch auf die XlSteuerung verlässt und deshalb dieses WECHSELN dort nicht zum gewünschten Ergebnis führt. Ebenso lässt sich da wohl auch nichts mit INDEX machen. Allerdings könnte das in ZellFmln mit einer udFkt auf Basis der vbFkt Evaluate eher fktionieren, wie nachfolgend gezeigt:
=SUMME(TransFor(D1:D5000;"sumproduct(--(min(D#:E#)=row(1:6)),--(max(D#:E#)>=row(1:6)))"))
Anm: Für diejenigen Mitleser, die über die udFktt Compute bzw Explore verfügen → mit diesen dürfte es ähnlich gut fktionieren.
Gruß, Luc :-?


  

Betrifft: AW: Deine Fml ist ja auch nicht grundsätzlich ... von: Christian
Geschrieben am: 17.08.2014 09:07:16

Hallo Luc,

danke für die Erklärung, aber sie war mir leider zu hoch. Ab allerdings funktioniert... verstehe ich nur noch Bahnhof. Aber an deinen Formeln sieht man ja, wo Bereiche von 1 bis 6, bzw. A bis F stehen, die dann in 57 oder BJ (hoffe da hab ich mich jetzt nicht vertan) zu ändern, sollte ja kein Problem sein.

Auf jeden Fall vielen Dank

Christian

PS: Habe heute nacht um 4 eine E-Mail bekommen, dass auf meine Nachricht eine Antwort eingegangen ist, also wirst du auch zu ähnlicher Uhrzeit geschrieben haben. Du darfst das nächste mal auch gerne zu "menschlischeren" Uhrzeiten antworten. So dringend ist es dann doch nicht.


  

Betrifft: Du hattest in deiner Fml nur EIN Argument ... von: Luc:-?
Geschrieben am: 17.08.2014 13:28:56

…verwendet, Christian,
denn alles, was innerhalb der Klammern der Fkt SUMMENPRODUKT stand, war ein zusammenhängender Ausdruck, quasi eine selbständige TeilFml, die einen Vektor aus 0en und 1en liefert. SUMMENPRODUKT addiert dann nur noch, benötigte hier aber (im Ggsatz zu SUMME) keine MxFml-Form.
Die neue Fml ist zwar komplizierter, hat aber auch nur ein Argument, d.h., SUMMENPRODUKT erhält bereits eine fertige Matrix, die nur noch summiert wdn muss. Das kannst du ja auch mal separat auf dem TabBlatt mit eigener (Matrix-)Fml für diesen FmlTeil ausprobieren.
Die MxFml-Form einiger Fmln ist Komponenten des Ausdrucks (als Argument1) geschuldet. MTRANS fktt nur so richtig.
Die letzte StandardFml kann man auch mit 2 Argumenten („klassisch“) schreiben, weil hier eine andere Überlegung zugrunde liegt, die den abschließenden Vgl mit 0 überflüssig wdn lässt, also:
=SUMMENPRODUKT(--(TEILERGEBNIS(5;INDIREKT(WECHSELN("D#:E#";"#";ZEILE(1:5000))))=SPALTE(A:F));
--(TEILERGEBNIS(4;INDIREKT(WECHSELN("D#:E#";"#";ZEILE(1:5000))))>=SPALTE(A:F)))
Die Form mit nur 1 Arg ist aber 4 Zeichen kürzer… ;-)
Übrigens, falls du mal in VBA die WorksheetFunction.SumProduct anwenden willst, kannst du das nur in klassischer Form tun, da dann die Unterstützung durch die allgemeine Xl-Steuerung wegfällt. In Formen wie den angegebenen (MxFml-Kennung entfällt dann), kann nur evaluiert, d.h., vbFkt Evaluate auf den FmlText in US-Original-Notation) angewendet wdn. In diesem Fall steht die Xl-Steuerung nämlich zV.
SchöSo, Luc :-?

PS: Zeit „menschlich“ genug…? ;-)


  

Betrifft: AW: Deine Fml ist ja auch nicht grundsätzlich ... von: Christian
Geschrieben am: 17.08.2014 22:16:35

Hallo Luc,

ich hätte mal eine Frage ob es noch eine Alternative gibt, in sofern, dass ich die höchste Zahl, in deinen Beispielen 6 in die Zelle G1 schreibe und sich die Formel dann auf G1 bezieht, sodass ich nur noch G1 ändern muss, wenn es dann mal bis 7, 8, 9 oder auch 57 gehen soll. Ist sowas auch möglich?

Gruß
Christian


  

Betrifft: Ja, aber dann ist ohnehin keine MxKonst ... von: Luc:-?
Geschrieben am: 18.08.2014 02:33:36

…möglich, Christian,
und das Ganze läuft auf eine INDIREKT-Variante mit Z1S1-Schreibung (bzw MTRANS) hinaus, zB:
=SUMMENPRODUKT(--((D1:D5000=SPALTE(INDIREKT("S1:S"&G1;0)))*(E1:E5000>=D1:D5000)+
(E1:E5000=SPALTE(INDIREKT("S1:S"&G1;0)))*(D1:D5000>=E1:E5000)>0))
In der TEILERGEBNIS-Fml könnte man das auch einfügen, nur wird die dann noch länger…
Morrn, Luc :-?


  

Betrifft: AW: Ja, aber dann ist ohnehin keine MxKonst ... von: Christian
Geschrieben am: 18.08.2014 07:45:44

Hallo Luc,

wieder so eine nette Uhrzeit :-) Aber, auch wenn ich auch bei dieser Formel nur Bahnhof verstehe. Sie funktioniert. Das GUte ist jetzt, G1 kann ich noch für 2 andere Formeln gut gebrauchen (die ich aber bereits habe und funktionieren). Danke

Christian


  

Betrifft: Bitte sehr u.Kurzerläuterung, ... von: Luc:-?
Geschrieben am: 18.08.2014 16:25:38

…Christian;
Boris hatte eine sog MatrixKonstante eingesetzt, die einen ZeilenVektor darstellt, was auch unbedingt erforderlich ist, da ganze Spalten vgln wdn. So wird dann jeder Wert der Spalte mit jedem Wert des ZeilenVektors vgln. Würde das auch ein SpaltenVektor sein, käme der Vgl durcheinander.
Außer mit einer direkt mit allen Elementen notierten MxKonst kann man das auch mit einer quasi Von-Bis-Angabe per xlFkt SPALTE erreichen. Da du aber in G1 eine Zahl notieren willst, wäre ZEILE günstiger. Allerdings müsste man den so entstehenden SpaltenVektor erst noch zu einem ZeilenVektor machen, was mit MTRANS geschehen kann. Da du aber flexibel sein und den Endwert in Zelle G1 notieren willst, böte sich hier INDIREKT an, denn diese Fkt wandelt einen AdressText in einen echten Bezug um. Das erfolgt standard­mäßig für AdressTexte in der üblichen A1-Notation. Alternativ können aber auch Adress­Texte in Z1S1-Notation angegeben wdn, wobei das 2.Argument der INDEX-Fkt mit 0 angegeben wdn muss. Der Adress­Text hat dann die allgemeine Form ZmSn für absolute Adress­Angabe, wobei Z für Zeile und S für Spalte, m, n für die entsprd Nr steht. (Für relative Adressen wird die Differenz zur StandortZelle in Klammern angegeben, wobei 0 weggelassen wdn kann, aber das wird hier ja nicht benötigt.)
Damit wäre dann SPALTE(INDIREKT("S1:S"&G1;0)) gleichbedeutend mit SPALTE($A:$F), falls in G1 eine 6 steht, denn $A:$F würde in Z1S1-Notation S1:S6 entsprechen.
Gruß, Luc :-?


  

Betrifft: AW: Bitte sehr u.Kurzerläuterung, ... von: Jenny
Geschrieben am: 18.08.2014 20:26:52

Hallo Luc,

ich glaube so langsam steige ich dahinter. Vielen Dank

Christian


  

Betrifft: 'INDEX-Fkt' lies 'INDIREKT-Fkt', ... von: Luc:-?
Geschrieben am: 18.08.2014 21:58:16

…Jenny alias Christian bzw Christian & Jenny… ;-)
Passiert mir mitunter wg gewisser Konkurrenzen zwischen beiden Fktt, wobei idR INDEX vorzuziehen ist (aber wohl eher nicht hier)…
Luc :-?


  

Betrifft: AW: 'INDEX-Fkt' lies 'INDIREKT-Fkt', ... von: Daniel
Geschrieben am: 18.08.2014 22:45:06

Hallo
ich sehe da keine grosse Konkurrenz.
INDEX ist in der Regel die besser Wahl, weil es nicht volatil ist.

INDIREKT wird eigentlich nur in folgenden Fällen zwingend benötigt, weil diese über INDEX nicht darstellbar sind:
- der Tabellenblattname selbst wird berechnet oder aus einer Zelle gelesen
- es wird ein Bezug zu einem Namen erstellt und dieser Name wird berechnet oder aus einer Zelle gelesen
- die Spalte, zu der ein Bezug hergestellt werden muss ist variabel und liegt als Spaltenbuchstabe vor


Gruß Daniel


  

Betrifft: Sicher, aber das hat wohl eher nichts mit ... von: Luc:-?
Geschrieben am: 19.08.2014 02:44:22

…meinem („Freudschen“) Verschreiber zu tun…
Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "Problem mit Summenprodukt Formel"