Excel Formel falsch?

[jochen1001]

Hi Leute,

ich hab folgende Formel aufgestellt, bis mir zum Schluss aufgefallen ist, dass ich einen Fall nicht bedacht habe: wenn die Felder B2 bis D2 = 0 sind, soll er mir "keine Werte" ausgeben.

Gerade hier zeigt mir Excel einen Fehler an, kann ihn jedoch nicht finden :-(

=WENN(MAX(B2:D2)=B2;$B$1;WENN(MAX(B2:D2)=C2;$C$1;WENN(MAX(B2:D2)=D2;$D$1;WENN(SUMME(B2:D2)=0;"keine Werte"))))

Ich hoffe ihr könnt mir weiterhelfen! Liegt es vielleicht daran, dass ich nicht mehr als 3 wenn Funktionen ineinander verschachteln kann?

P.S. um genau zu sein, fehlt mir noch der Fall, dass zwei Werte gleich sind :-)

 

[GMG-CC]

Gerade hier zeigt mir Excel einen Fehler an, kann ihn jedoch nicht finden :-(

... Und welchen Fehler?

=WENN(MAX(B2:D2)=B2;$B$1;WENN(MAX(B2:D2)=C2;$C$1;WENN(MAX(B2:D2)=D2;$D$1;WENN(SUMME(B2:D2)=0;"keine Werte"))))

Angenommen, in B22 steht -30 | 10 | 20 dann ist die Summe durchaus null ... Was du eventuell meinst, sähe dann so aus:

UND(B2=0;C2=0;D2=0

Liegt es vielleicht daran, dass ich nicht mehr als 3 wenn Funktionen ineinander verschachteln kann?

Nein, denn bis zu 8 Ebenen sind möglich.

P.S. um genau zu sein, fehlt mir noch der Fall, dass zwei Werte gleich sind :-)

Geht auch mit der UND()-Funktion bzw. mit ODER() und/oder einer Kombination dieser Funktionen.

Ich habe aber so nebenbei das Gefühl, dass nicht in jedem denkbaren Fall das korrekte Ergebnis heraus kommt ... Die Reihenfolge der Prüfungen ist schon wichtig!

 

[jochen1001]

ok danke :-)

=WENN(MAX(B2:D2)=B2;$B$1;WENN(MAX(B2:D2)=C2;$C$1;WENN(MAX(B2:D2)=D2;$D$1;WENN(SUMME(B2:D2=0)*UND(B2=0;C2=0;D2=0);"keine Werte";))))

Wenn ich hier jetzt in allen 3 Feldern null eintrage, spuckt er mir B1 aus.

 

[Stefan-]

Macht ja auch Sinn. Die Bedingung mit den Nullen muss nach ganz vorne:

=WENN(UND(B2=0;C2=0;D2=0);"keine Werte";
WENN(MAX(B2:D2)=B2;$B$1;
WENN(MAX(B2:D2)=C2;$C$1;
WENN(MAX(B2:D2)=D2;$D$1;)))
)

Wobei das eh relativ kompliziert für Excel formuliert ist. Guck doch mal, ob du das nicht mit SVERWEIS o.ä. lösen kannst. So schreibst du dir ja einen Wolf.

 

[h@mster]

schonmal mit der Funktion "Daten überprüfen" versucht ?

 

[GMG-CC]

Macht ja auch Sinn. [..]

Wobei das eh relativ kompliziert für Excel formuliert ist.

Nein Stefan, das ist nach dem, was jochen1001 mitgeteilt hat, optimal! Durch dieses "blöde" MAX() wird es schwierig mit jeder Art von Verweisen.

@jochen1001: Wenn es immer noch nicht so hinhaut, wie gewünscht, dann schick uns mal eine Musterdatei und EXAKT alle denkbaren Möglichkeiten und natürlich die zu erzielenden Ergebnisse. - Und falls dir nicht vollkommen klar ist, warum die von Stefan vorgestellte Reihenfolge wichtig ist, schreibe uns das noch einmal. Nur übernehmen bringt es ja nicht, Verstehen ist wichtig! (Darum habe ich auch versucht, dich zu einer Umstellung mit Probieren zu animieren. Denn mein Leitsatz: "Fehler sind dazu da, gemacht zu werden und daraus zu lernen.")

 

[Stefan-]

Hm, natürlich geht es einfacher: Nehmen wir an, du willst die Zellen B-D vergleichen:

=INDIREKT(ZEICHEN(64+VERGLEICH(MAX(B2:D);B2:D2;0))&"1")

Okay, es ist kein SVERWEIS, aber VERGLEICH gehört zur Funktionsgruppe. Das klemmt man nun folgendermaßen in die WENN-Abfrage rein:

=WENN(UND(B2=0;C2=0;D2=0);"keine Werte";INDIREKT(""&ZEICHEN(64+VERGLEICH(MAX(B2:D2);B2:D2;0))&"1")

Beliebig erweiterbar ohne zusätzliche Verschachtelungen.

EDIT: Noch besser:

=WENN(SUMME(ABS(A2:G2))=0;"keine Werte";INDIREKT(""&ZEICHEN(64+VERGLEICH(MAX(A2:G2);A2:G2;0))&"1"))

Das gibst du als Matrix-Formel ein, dann hast du nach meinen Kenntnissen schon fast alles rausgeholt.

 

[jochen1001]

boa danke für die vielen Rückmeldungen!

1.) Also zuerst würd ich gern wissen, wieso diese Nullabfrage nach ganz vorne muss?

2.)

=WENN(UND(B2=0;C2=0;D2=0);"keine Werte";

Ist das ne Kurzschreibweise? Oder wieso steht vor dem UND nichts mehr?

@ Stefan

kannst du mir deine Vereinfachung der Formel näher erläutern? Wieso 64+...? Indirekt?

P.S. Sverweis kenn ich, hab ich mir auch schon überlegt, kann ich hier aber wohl nicht gebrauchen

P.P.S. In dem Anhang hab ich n Screenshot, wofür ich das ganze eigentlich machen will!

 

[Stefan-]

Also:

INDIREKT(""&ZEICHEN(64+VERGLEICH(MAX(A2:G2);A2:G2;0))&"1")

MAX(A2:G2) ermittelt den höchsten Wert der Zellen A2 bis G2, d.h. z.B. 5,52€
VERGLEICH(...;A2:G2;0) ermittelt die Spalte, in der dieser Wert steht. Für A wäre das dann = 1
ZEICHEN(64+...) macht nun aus der 1 ein 'A', weil das 65. ASCII-Zeichen = "A" ist.
INDIREKT(""&...&"1") löst jetzt nur noch die Zeichenkette "A1" zur Zelle A1 auf, d.h. in den Wert "Aldi" (oder so).

Zur Nullabfrage: Die muss nach ganz vorne, da WENN von links nach rechts arbeitet und ja jede der anderen Bedingungen auch erfüllt sein kann. Nehmen wir an, es sei B2 = C2 = D2 = 0. Dann ist auch das MAX(B2: D2) = 0 = B2 = C2 = D2. Aus deiner Ursprungsformel ergäbe sich dann, dass die erste Bedingung "MAX(B2: D2)=B2" direkt erfüllt wäre. Da steht ja praktisch "MAX(0;0;0)=0", also "0=0" und das ist WAHR.

 

[jochen1001]

ok danke!

Ein Problem bleibt dennoch:

Wenn ich diese Formel benutze:

=WENN(UND(B2=0;C2=0;D2=0);"keine Werte";
WENN(MAX(B2: D2)=B2;$B$1;
WENN(MAX(B2: D2)=C2;$C$1;
WENN(MAX(B2: D2)=D2;$D$1; )))
)

Die Formel schließt nicht alle angrenzenden Zellen mit ein.

Was soll denn das jetzt?

 

[Stefan-]

Hm, sehe ich gerade nicht. Kannst du nicht "Bezug erweitern um Zellen einzuschließen" (oder so ähnlich) auswählen?

 

[jochen1001]

kann ich natürlich machen, jedoch hab ich dann das Datum vorne noch mit drin, und die Berechnung ist dahin, naja, er rechnet ja jetzt richtig. Formel hab ich auch geblickt. Ob da jetzt so ein rotes Dreieck oben ist oder nicht, ist mir jetzt egal