Microsoft Excel

Herbers Excel/VBA-Archiv

Erklärung für komplizierte Formel(Summenprodukt) | Herbers Excel-Forum


Betrifft: Erklärung für komplizierte Formel(Summenprodukt) von: chris b.
Geschrieben am: 02.03.2010 14:25:08

Hallo Excelprofis,
würde mich sehr freuen wenn mir jemand diese Formel erklären könnte.
Und wenn mir vielleicht jemand sagen könnte ob die Formel "gut" ist oder ob man Sie auch vereinfachen kann.
Aber würde mich sehr freuen wenn mir jemand auch die Ausgabe dieser Formel erklären könnte.
ich blick da nämlich gar nicht durch :(
Vielen dank dafür im voraus !

gruß Chris

=WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="X")*1)>0;"X";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="L" _
)*1)>0;"L";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="&")*1)>0;"&";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="K")*1)>0;"K";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="J")*1)+SUMMENPRODUKT((LINKS($FS6:$HP6;1)="C")*1)>0;"C";"")))))

  

Betrifft: AW: Erklärung für komplizierte Formel(Summenprodukt) von: David
Geschrieben am: 02.03.2010 14:30:42

Hallo Chris,

was stehen denn für Daten in FS6:HP6?

Es wird geprüft, ob in diesem Bereich in einer Zelle ein Text steht, der mit X,L,&,K etc. als erstem Buchstaben beginnt.

Gruß
David


  

Betrifft: AW: Erklärung von: chris b.
Geschrieben am: 02.03.2010 14:48:16

Ja und was wird Ausgegeben ?
UNd wie soll ich das verstehen ? Kannst Du mir diese Formel etwas genauer erklären ?
Also wie sie Funktioniert.
Oder vielleicht jemand anderes ?

Vielen Dank schon im voraus an alle Profis !


  

Betrifft: AW: Erklärung von: David
Geschrieben am: 02.03.2010 15:02:16

Was hast du erwartet? Eine Antwort ist maximal so gut wie die Frage. Du wolltest wissen, was die Formel macht, das habe ich beantwortet. Und ob sie einfacher geht, kann man mit deinen Infos nicht sagen. Außerdem erwartet man bei Angabe von "Excel gut" ein wenig mehr Hintergrundwissen, so dass man dann automatisch weniger genaue Erläuterungen schreibt.

Summenprodukt vergleicht jede Zelle in dem Bereich, ob sie das Kriterium (hier: 1.Zeichen von Links) erfüllt. Wenn ja, entsteht ein Wahrheitswert. Durch die Multiplikation entsteht aus dem/n Wahrheitswert/en Zahlen (0 oder 1), die aufsummiert werden. Ist die Summe größer 0, hat irgendeine der Prüfungen den Wert WAHR, d.h. eine der Zellen enthält den gesuchten Buchstaben. Dann wird in der Formelzelle die WAHR-Bedingung der WENN-Formel ausgegeben. Falls keine Zelle den Buchstaben enthält, entsteht eine 0, was zu einem FALSCH der WENN-Bedingung führt, so dass die nächste Bedingung geprüft wird usw.
Du kannst das auch leicht mit der Formelüberwachung nachvollziehen, indem du dort Schritt für Schritt verfolgst. Vorher allerdings den Bereich etwas verkleinern, sonst wirds unübersichtlich.

Um zu prüfen, ob das einfacher geht, müsste man wissen, wie die Daten aussehen und wie das Ergebnis ggf. weiterverarbeitet werden soll. Dazu müsstest du aber noch ein paar Infos geben!!!

Gruß
David


  

Betrifft: Vereinfachung der SUMMENPRODUKT()-Funktion von: NoNet
Geschrieben am: 02.03.2010 16:36:00

Hallo Chris,

zunächst einmal stimme ich David zu : die Ausgabe hängt davon ab, was in den Zellen $FS6:$HP6 steht ! Das kann hier niemand erraten !
Was die Funktion bedeutet hat Davied ebenfalls bereits in einem kurzen Satz erklärt :
Je nachdem, ob in einer Zelle des Bereichs $FS6:$HP6 ein Wort mit "X", "L", &","K","J","C" beginnt, wird in der Zelle, die diese Funktion beinhaltet der entsprechende Anfangsbuchstabe ausgegeben !
Wenn mehrere Begriffe mit diesen Anfangszeichen enthalten sind, dann wird der erste passende Buchstabe in dieser Reihenfolge ausgegeben.

Hier übrigens eine "Vereinfachung" dieser Funktion die per Strg+Shift+ENTER bestätigt werden muss :

=WENN(MIN(WENN(($FS6:$HP6<>"")*ISTZAHL(SUCHEN(LINKS($FS6:$HP6;1);"XL&KJC"));SUCHEN(LINKS($FS6:$HP6;1);"XL&KJC")))=0;"";TEIL("XL&KJC";MIN(WENN(($FS6:$HP6<>"")*ISTZAHL(SUCHEN(LINKS($FS6:$HP6;1);"XL&KJC"));SUCHEN(LINKS($FS6:$HP6;1);"XL&KJC")));1))

Vereinfachung deshalb, da sie sich wesentlich leichter erweitern lässt - vor allem wenn man die gesuchten Anfangsbuchstaben (XL&KJC) in eine Zelle (z.B. in Zelle $B$6) schreibt und dann die Funktion entsprechend verkürzt :

=WENN(MIN(WENN(($FS6:$HP6<>"")*ISTZAHL(SUCHEN(LINKS($FS6:$HP6;1);$B$6));SUCHEN(LINKS($FS6:$HP6;1);$B$6)))=0;"";TEIL($B$6;MIN(WENN(($FS6:$HP6<>"")*ISTZAHL(SUCHEN(LINKS($FS6:$HP6;1);$B$6));SUCHEN(LINKS($FS6:$HP6;1);$B$6)));1))

Nun muss man zur suche weiterer Buchstaben/Zeichen diese einfach die Zelle B6 eintragen (also z.B. XL&KJC5YRA).

Gruß, NoNet


  

Betrifft: AW: Vereinfachung der SUMMENPRODUKT()-Funktion von: chris b.
Geschrieben am: 02.03.2010 18:50:09

Hi Nonet,
vielen Dank für deine gute Erklärung.
Jetzt hoffe ich es das mir die Formel morgen klarer wird wenn ich noch einmal Zeit habe.
Vielen Dank und Danke auch David


  

Betrifft: AW: Vereinfachung der SUMMENPRODUKT()-Funktion von: chris b.
Geschrieben am: 03.03.2010 07:46:40

Hallo Nonet ich habe jetzt einmal die Formel aufgesplittet.

SUMMENPRODUKT((LINKS($FS73:$HP73;1)="X")*1)>0;"X"
SUMMENPRODUKT((LINKS($FS73:$HP73;1)="L")*1)>0;"L"
SUMMENPRODUKT((LINKS($FS73:$HP73;1)="&")*1)>0;"&"
SUMMENPRODUKT((LINKS($FS73:$HP73;1)="K")*1)>0;"K"
SUMMENPRODUKT((LINKS($FS73:$HP73;1)="J")*1)+SUMMENPRODUKT((LINKS($FS73:$HP73;1)="C")*1)>0;"C";"" _
)))))
Diese sind auch alle jetzt etwa verständlich.
Auser die letzte:
die anderen prüfen den Anfangsbuchstaben.Aber was macht diese Hier ?
SUMMENPRODUKT((LINKS($FS73:$HP73;1)="J")*1)+SUMMENPRODUKT((LINKS($FS73:$HP73;1)="C")*1)>0;"C";"" _
)))))

Danke noch einmal für eure Deine Hilfe !?


  

Betrifft: AW: Zusatz Frage von: chris b.
Geschrieben am: 03.03.2010 07:50:45

Hallo habe jetzt die Formel etwas geändert.
Würde Sie jetzt das gleihe ergebniss bringen wie die Formel die ich gerade gepostet habe ?

=WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1)="X")*1)>0;"X";WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1) _
="L")*1)>0;"L";WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1)="&")*1)>0;"&";WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1)="K")*1)>0;"K";WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1)="J")*1)>0;"J";WENN(SUMMENPRODUKT((LINKS($FS74:$HP74;1)="C")*1)>0;"C";""))))))
Danke
gruß Chris


  

Betrifft: AW: Zusatz Frage von: fcs
Geschrieben am: 03.03.2010 12:31:36

Hallo Chris,

die beiden Formeln können nicht das gleiche Ergebnis liefern.

die bisherige Formel gibt als Ergebnis "C" aus wenn im Bereich kein Zellinhalt mit X, L, & oder K beginnt
aber mindestens ein Zellinhalt mit J oder C.

Die neue Formel gibt "J" als Ergebnis aus wenn im Bereich kein Zellinhalt mit X, L, & oder K beginnt aber
mindestens ein Zellinhalt mit J. Sie gibt "C" als Ergebnis aus wenn im Bereich kein Zellinhalt mit X, L, &, K
oder J beginnt aber mindestens ein Zellinhalt mit C.

Gruß
Franz


  

Betrifft: AW: Zusatz Frage von: chris b.
Geschrieben am: 03.03.2010 14:20:36

Ohjee das ist etwas kompliziert :(
Blick gerade nicht durch.

Kann man so sagen.Die erste Formel gibt bei inhalt der Zelle entweder wenn 1 C oder 1 J vorhanden ist ein C aus und die andere gibt wenn J vorhanden J aus und wenn C vorhanden C aus ?
Sorry diese Antwort jetzt aber ich blick gerade nicht mehr ganz durch.


  

Betrifft: AW: Zusatz Frage von: David
Geschrieben am: 03.03.2010 14:31:59

Es würde wahrscheinlich helfen, wenn du endlich mal zeigen würdest, wie die Daten denn aussehen und was mit dem Ergebnis der Formel passiert, ob dies weiterverarbeitet wird, wenn ja wie, oder ob die Info sonst irgend einen Zweck hat.
Bisher ist das Alles für diejenigen, die die Tabelle nicht kennen (also alle hier außer dir), nur abstrakte Theorie und ergibt keinen wirklichen Sinn.

Gruß
David


  

Betrifft: AW: Zusatz Frage von: fcs
Geschrieben am: 03.03.2010 16:39:05

Hallo Chris,

du hast hier mehrer WENN ineinander geschachtelt. Deren Bedingungungen werden nacheinander geprüft.
Fängt eine Zellinhalt mit "X" an? Wenn Ja dann Ergebnis = "X"
Wenn Nein, dann wird geprüft ob eine Zelle mit "L" anfängt. Wenn Ja dann Ergebnis = "L"
Wenn Nein, dann wird geprüft ob eine Zelle mit "&" anfängt. Wenn Ja dann Ergebnis = "&"
Wenn Nein, dann wird geprüft ob eine Zelle mit "K" anfängt. Wenn Ja dann Ergebnis = "K"
u.s.w.

Du kannst die letzten Bedingungen also nicht losgelöst von den 1. Bedingungen betrachten.
Bei deiner Formel ist es so, dass sobald ein "X" gefunden wird die Inhalte der anderen Zellen für das Ergebnis keine Rolle mehr spielen.

Gruß
Franz


  

Betrifft: AW: Ergänzungs frage dazu von: chris b.
Geschrieben am: 04.03.2010 00:24:09

Hallo Franz,
vielen dank für Deine Klasse Erklärung.
Habe nur noch ein Verständniss Problem.
Du schreibst ich kann die letzten bedingungen nicht Losgelöst von der ersten sehen.
Aber die letzte Bedingung in der langen Formel "unten" sieht anders aus also die die davor standen ?
Also ich meine diese letzten zeichen.

WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="J")*1)+SUMMENPRODUKT((LINKS($FS6:$HP6;1)="C")*1)>0;"C";" _
")

Zuerst werden ja die Buchstaben "X" und "L" und "&" und "K" geprüft.
Bei diesen sieht die Formel gleich aus.Aber bei den letzten Beiden Buchstaben "J" und "C" sieht die Formel etwas anders aus.Weil Sie eben mit einem + verbunden ist ? Was bedeutet das ?


Und hier noch einmal die Gesamte Formel.
=WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="X")*1)>0;"X";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="L" _
 _
           )*1)>0;"L";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="&")*1)>0;"&";WENN(SUMMENPRODUKT(( _
LINKS($FS6:$HP6;1)="K")*1)>0;"K";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="J")*1)+SUMMENPRODUKT((LINKS($FS6:$HP6;1)="C")*1)>0;"C";"")))))



  

Betrifft: AW: Ergänzende Antwort von: Renee
Geschrieben am: 04.03.2010 09:10:35

Hi Chris,

Weil Sie eben mit einem + verbunden ist ? Was bedeutet das ?
Das bedeutet wenn immer in dem Bereich mindestens ein Begriff mit "C" ODER (+) "J" anfängt (und alle anderen Tests auf "X","L" und "K" nicht erfolgreich waren), dann wird ein "C" als Resultat in die Zelle geschrieben.

GreetZ Renée


  

Betrifft: AW: Ergänzende Antwort von: chris b.
Geschrieben am: 04.03.2010 10:13:33

Hallo Renee genau das habe ich mir auch gedacht.
Nur war mir nicht sicher weil mann hätte es ja auch anders schreiben können.So oder ...

=WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="X")*1)>0;"X";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="L" _
)*1)>0;"L";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="&")*1)>0;"&";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="K")*1)>0;"K";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="J")*1);"J";WENN(SUMMENPRODUKT((LINKS($FS6:$HP6;1)="C")*1)>0;"J";""))))))
VieleN dank für die Hilfe ! An alle !!!


  

Betrifft: Frau/Mann kann immer alles anders schr.. (owT) von: Renee
Geschrieben am: 08.03.2010 11:21:40




  

Betrifft: AW: Ergänzungs frage dazu von: fcs
Geschrieben am: 04.03.2010 09:23:37

Hallo Chris,

"+" heißt hier schlicht und einfach mathematisch Plus - also Addition.

Von der Logik her wirkt es in deiner Formel wie ein ODER.
Wenn Anzahl "J" größer 0 ODER Anzahl "C" größer 0 dann ist die Addition (+) der beiden Ausdrucke größer 0 und die Prüfung ergibt als Ergebnis WAHR und die Formel spuckt als Ergebnis "C" aus. Wenn keine aller angegebenen Bedingungen erfüllt wird, dann wird der Wert nach dem letzten Semikolon als Ergebnis ausgegeben - hier ein Leerstring.

Gruß
Franz