Frage von JohnGold91, 90

Wie nutze ich bei Microsoft Excel die WENN-Funktion mit mehreren DANN-Werten ?

Ich möchte bei Excel eine Gruppentabelle zur aktuellen Fußball EM erstellen.Die ganz einfache WENN-Funktion kenne ich. Beim googlen habe ich von einer erweiterten WENN-Funktion gelesen. Diese umfasst allerdings nur mehrere WENN-Bedingungen und nicht mehrere DANN-Werte.

Wenn ich zum Beispiel das Ergebnis Frankreich gegen Albanien eintrage, sollen in einer weiteren Zelle die Punkte berechnet werden.Ich schreibe dann in die Zelle wo die Punkte von Frankreich gezeigt werden: 

=WENN(Tore Frankreich > Tore Albanien;3;0)

In diesem Beispiel kriegt Frankreich 3 Punkte, wenn sie mehr Tore als Albanien schießen.

In der Zelle mit Albaniens Punkten schreibe ich es ähnlich, nur anders herum, also so

:=WENN(Tore Frankreich < Tore Albanien;3;0)

Dann kriegt Albanien 3 Punkte, wenn sie mehr Tore als Frankreich erzielten.

Das funktioniert ja. Aber wenn beide jetzt die gleiche Anzahl Tore schießen, kriegen mit dieser Formel beide 0 Punkte und nicht jeweils 1 Punkt, wie es eigentlich sein sollte.

Wie sähe die richtige Formel aus, wo beide einen Punkte erhalten bei einem Unentschieden?

Außerdem spielt jede Mannschaft in der Gruppenphase 3 Spiele, also müsste ich ja alle 3 Spiele in der WENN-Formel berücksichtigen und nicht nur die von dem einen Spiel Frankreich gegen Albanien.

Könnt ihr mir auch davon die komplette Formel aufzeigen?

Danke sehr!!!

PS: Ich füge ein Bild meiner falschen Formel bei, um eventuell das Verständnis zu erleichtern.

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von MeikelZW, 56

Hallo JohnGold91,

Ich habe keine Ahnung von Fußball und werde mir nie ein Spiel ansehen weil ich Sport im TV generell nicht mag! Aber ich habe Ahnung von Excel (über 20 Jahre beruflich gebraucht). Hier meine Lösung:

Die „französiche“ Punktezelle:
=WENN(Fra<Alb;0;WENN(Fra>Alb;3;1))

Die „alnbansiche“ Punktezelle:
=WENN(Alb<Fra;0;WENN(Alb>Fra;3;1))

Vorraussetzung, es ist so, dass gewonnen = 3, unentschieden = 1 und verloren = 0 Punkte sind…
Sonst musst Du die Punktwerte noch ändern, der Rest wird gehen. 

Für eine Tabelle sind die Funktionen KGRÖSSTE MAY MIN RANG und SVERWEIS wichtig…

Gruß, MeikeZW

Kommentar von JohnGold91 ,

Habe es noch nicht ausprobiert, aber auf den ersten Blick sehen die Formeln sehr gut aus. Deswegen schonmal ein fettes Danke!

Aus KGRÖSSTE MAY MIN RANG & SVERWEIS bin ich nicht schlauer geworden, aber die Formeln oben reichen mir. Danke

Kommentar von MeikelZW ,

Sieh Die bitte meine zweite Antwort an, dann bist Du schlauer. Ist mir nachträglich eingefallen.

Antwort
von flaglich, 51

Ich halte deinen Plan für keine gute Idee, aber du kannst die Wenn-Funktion schachteln, einfach nach ersten Semikolon (wahr) oder zweiten Semikolon (falsch) wieder ein Wenn() aufmachen. Lass die Lehrzeichen weg, dann gibt es weniger Fehler.

Antwort
von Seanna, 55

Wenn x dann y sonst z.

Alternativ verschachtelte Wenn-Funktionen.

Antwort
von MeikelZW, 49

...noch mal ich,
vergessen zu erklären, wie die „Rangtabelle“ gemacht wird:

mit SUMMEWENN!

Du musst zwei SUMMEWENN Matrixen verwenden und sie addieren (weil die Ländernamen mal links und mal rechts stehen:

Hier mundgerecht fertig für Frankreich:
=SUMMEWENN($B$7:$G$12;H7;$E$7:$E$12)+SUMMEWENN($D$7:$G$12;H7;$G$7:$G$12)

die für alle 4 runterziehen, also 3 × kopieren - fertig.

Gruß, MeikelZW

Kommentar von Jackie251 ,

Was bitte soll diese Formel ermitteln!?

=SUMMEWENN($B$7:$G$12;H7;$E$7:$E$12)

Bereich und Summebereich sind unterschiedlich. Daher wird Excel intern aus E7:E12 automatisch E7:J12 machen, da sich Form und Anzahl der Zellen von Bereich und Summebereich IMMER gleichen müssen.

Folglich addiert schon die Formel

=SUMMEWENN($B$7:$D$12;H7;$E$7:$G$12)

Die Tore einer Mannschaft, da braucht es keine 2 x Summewenn.

Allerdings ist auch der Lösungsansatz schlicht Quatsch, da man aus der Anzahl der Tore nicht die Punkte einer Mannschaft berechnen kann.

Kommentar von MeikelZW ,

Wie ich eingangs erwähnt hatte, habe ich nur Ahnung von Excel, nicht von Fußball, den hasse ich schon fast!

Recht hats Du, dass man dabei „nur“ die Tore addiert. Das ist mir aber erst später aufgefallen, dass das die Ergebnisse sind - wer lesen kann, ist klar im Vorteil ;-)

Aber in der „Endzelle“ eine All-in-one Formel zu beschreiben, würde auf Kosten der Verständlichkeit gehen.

Ich würde an dieser Stelle die Punkte in ausgeblendeten Zellen ermiteln und die SUMMEWENN Formel darauf anwenden.

Somit ist nicht der Ansatz „schlicht Quatsch“ sonder die schlichte Interpretation.

Und doch! wenn die Grundform der Tabelle so bleiben soll, sind zwei dieser Formeln unablässig!

Gruß, MeikelZW

Kommentar von MeikelZW ,

...und noch was,
wenn die Formel

=SUMMEWENN($B$7:$D$12;H7;$E$7:$G$12)

verwendet wird, dann werden nur die Pumkte der Mannschaft aus H7 ermittelt - und nur dann, wenn diese links steht. Für Frankreich also nur in 1 & 4, nicht jedoch in Spiel 5. Wenn diese Formel für jedes einzelne Spiel kopiert würde, dass wäre es nur einmal H7 weil die »$« Zeichen dann fehlen würden! Es geht in Excel immer darum, möglichst eine allgemeingültige Formel zu erstellen um diese nicht für alle einzel entwerfen und tippen zu müssen. Kopieren geht über Studieren!

Eigentlich einer der tieferen Sinne von Excel!

MeikelZW

Kommentar von Jackie251 ,

punkt 1)
Nein ist es "Quatsch" denn für die Punkteberechnung muss man wissen - wie auch der FS  schon ausgeführt hat - wie oft eine Mannschaft gewonnen hat. Dafür ist es völlig belanglos, wieviele Tore sie geschossen hat.
Schon an diesem Punkt sollte klar sein, das Summewenn nicht die richtige Wahl ist, sondern Zählewenn(s) gewählt werden sollte.

Punkt 2)
ich stimme dir zu das Hilfspalten generell kein schlechter Stil sind und durchaus der Übersichtlichkeit beitragen.
Allerdings ist meine All-In-One Formel (siehe unten) aus meiner Sicht gut überschaubar - zumindest einfacher als 6 Hilfsspalten.

Punkt 3)
Du irrt dich in der Arbeitsweise von Summewenn. Bitte prüfe doch sowas zunächst, bevor du falsche Antworten gibt. Es ist nicht schlimm - auch nicht nach 20 Berufsjahren - wenn man in einem Punkt falsch liegt. Aber man muss seinen Irrtum ja nicht versteifen, weil man gar nicht erst in Excel prüft.

Deine Formel

=SUMMEWENN($B$7:$G$12;H7;$E$7:$E$12)+SUMMEWENN($D$7:$G$12;H7;$G$7:$G$12)

ergibt für Excel keinen Sinn da Bereich einen Matrix mit 6 Spalten (B:G) und 6 Zeilen (7:12) Aufspannung. Excel prüft für JEDE dieser 36 Zellen OB die Bedinung H7 erfüllt ist.
Als nächstes wird jeder wahrer Bedingung der Wert aus dem Summe_Bereich zugeordnet. Dieser Summebereich E7:E:12 ist aber nur 1 Spalte x 6 Zeilen groß - eine 6x6 Matrix kann aber nicht mit einer 1x6 Matrix kombiniert werden.
Excel erweitert daher intern (und zugegebenermaßen fies, da es nicht dokumentiert wird) deine E7:E:12 Eingabe einfach auf E7:J12.
Aus diesem grunde führt deine Formel zB schon bei der Schweiz zu 4 Toren - obwohl die nur 2 Tore geschossen haben. Der Grund ist, das bei dir das erste Summewenn bereits die Anzahl der Tore korrekt ermittelt. Die Tore der 2. Spalte werden jedoch nochmal dazuaddiert.

Meine Formel hingegen

=SUMMEWENN($B$7:$D$12;H7;$E$7:$G$12)

ermittelt für jedes Spiel  die Tore.

H7 ist der Bezug auf die Nation. diese wird nur vertikal kopiert und muss beim Kopieren vertikal relativ bleiben. Von daher ist hier eine absolute Adresse für die Spalte nicht erforderlich (kein horizontales Kopieren) und für die Zeile schlicht falsch (horz. muss relativ sein).

Bitte erstelle doch zunächt eine solche Tabelle und prüfe mal was deine Formel macht. Das geht in wenigen Minuten und wir müssen hier nicht über Basics der Matrixoperationen rechnen (erford. gleiche Größen der Matrizen).

Kommentar von MeikelZW ,

Habe ich und meine Lösung funktioniert, die andere nicht :-((
(Original abgekupfert!)

Kommentar von MeikelZW ,

@Jackie251,

sorry, bei mir hatte sich ein Kopierteufelchen eingeschlichen. Und weil ich den Dingen gern auf den Grund gehe, hier meine Entschuldigung! Darum habe ich alles noch mal von vorn gemacht und siehe da: beide Ansätze funktionieren!

Mein Antrieb, das in zwei sich addierende Formeln zu packen war zu aller erst die Verständlichkeit beim Fragenden. Und leider hatte ich noch einen Denkfehler: ich hatte die Argumentvorschrift bei der Matrix von SVERWEIS auf SUMMEWENN angewandt. Im Eifer des Gefechts hatte ich mich da verbissen, noch mal Sorry!

Gruß, MeikelZW

Kommentar von Jackie251 ,

alles gut, sowas passiert ;-)

schön das du den Fehler findes konntest

Antwort
von Jackie251, 27

für sowas brauchst du keine Wennfunktion, diese macht es nur unübersichtlich

=(Tore Frankreich > Tore Albanien)*3+(Tore Frankreich > Tore Albanien)*1
Kommentar von Jackie251 ,

hab mal das Bild angesehen

mit dieser Formel in I7

=SUMME(((H7=$B$7:$B$12)*($E$7:$E$12>$G$7:$G$12)+(H7=$D$7:$D$12)*($G$7:$G$12>$E$7:$E$12))*3;((H7=$B$7:$B$12)*($E$7:$E$12=$G$7:$G$12)+(H7=$D$7:$D$12)*($G$7:$G$12=$E$7:$E$12))*(NICHT(ISTLEER($E$7:$E$12))*NICHT(ISTLEER($G$7:$G$12))))

kannst du auch gleich die Punkte einer Mannschaft ermitteln lassen

Keine passende Antwort gefunden?

Fragen Sie die Community