Anzeige
Archiv - Navigation
1404to1408
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Problem mit SVERWEIS Formel

Problem mit SVERWEIS Formel
26.01.2015 11:10:45
Jenny
Hallo an alle,
habe folgende Formel, die ich gerne mit eurer Hilfe etwas erweitern würde. =WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:D;4;FALSCH)>0;SVERWEIS(D3;Actresses!A:D;4;FALSCH);"");"")
Wie ihr seht sucht die Formel D3 in Actresses!A:A und gibt entsprechend die Zahl in Spalte D aus.
Gibt es auch eine Formel, die entweder D oder F ausgibt, je nachdem welche der beiden Zahlen größer ist?
Und wie die Formel oben auch schon macht, wenn gar kein Wert vorhanden ist, die Zelle leer lässt?
Danke und Gruß
Jenny

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem mit SVERWEIS Formel
26.01.2015 11:21:11
Klaus
Hallo Jenny,
nimm die Formel MAX wie Maximum, um den höheren Wert heraus zu finden.
=max(deineFormelFuerD;deineFormelFuerF)
=max(WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:D;4;FALSCH)>0;SVERWEIS(D3;Actresses!A:D;4;FALSCH);"") ;"");WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:F;6;FALSCH)>0;SVERWEIS(D3;Actresses!A:F;6;FALSCH);"") ;""))
Grüße,
Klaus M.vdT:

AW: Problem mit SVERWEIS Formel
26.01.2015 11:33:48
Jenny
Hallo Klaus,
deine Formel funktioniert nur, wenn in D und in F etwas steht, ist nur eine der beiden Zellen leer oder auch beide Zellen, gibt sie #WERT! aus.
War eigentlich so gedacht, dass wenn in nur einer Zelle eine Zahl steht, dass das dann auch die Zahl ist die ausgegeben werden soll und das Ergebnis leer bleiben soll, wenn auch beide Zellen in D und F leer sind.
Wäre lieb, wenn du nochmal nach der Formel schaust.
Danke schonmal
Jenny

Anzeige
AW: Problem mit SVERWEIS Formel
26.01.2015 11:41:09
Klaus
Hallo Jenny,
ich nehme an, MAX mag deine ="" Sache nicht. Das müsste gehen:
=max(WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:D;4;FALSCH)>0;SVERWEIS(D3;Actresses!A:D;4;FALSCH);0) ;"");WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:F;6;FALSCH)>0;SVERWEIS(D3;Actresses!A:F;6;FALSCH);0) ;""))
Wenn in beiden Zellen nix steht, kommt eine 0 heraus - die kannst du wiederum abfragen und verhindern. Ich gehe davon aus, es gibt keine negativen Ergebnisse.
=wenn(Formel=0;"";Formel)
=wenn(max(WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:D;4;FALSCH)>0;SVERWEIS(D3;Actresses!A:D;4; FALSCH);0) ;"");WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:F;6;FALSCH)>0;SVERWEIS(D3;Actresses!A:F;6; FALSCH);0) ;""))=0;"";max(WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:D;4;FALSCH)>0;SVERWEIS(D3; Actresses!A:D;4;FALSCH);0) ;"");WENNFEHLER(WENN(SVERWEIS(D3;Actresses!A:F;6;FALSCH)>0;SVERWEIS(D3; Actresses!A:F;6;FALSCH);0) ;"")))
das geht bestimmt auch deutlich kürzer - aber dafür bräuchte es eine Musterdatei :-)
Grüße,
Klaus M.vdT.

Anzeige
AW: Problem mit SVERWEIS Formel
26.01.2015 12:00:49
Jenny
Hallo Klaus,
also die zweite Formel funktioniert, die erste kann ich nicht nehmen, weil ich auf die Ergebnisspalte RANG-Formeln anwende, die verfälscht würden, wenn da Nullen auftauchen.
Negative Zahlen kommen keine vor. Nein.
Es sind genau genommen Kalenderdaten, hab absichtlich von Zahlen geredet, damit es keine Missverständnisse gibt ob ich jetzt die höhere Zahl oder das ältere Datum meine mit Maximum.
https://www.herber.de/bbs/user/95272.xlsx
Danke
Jenny

drastisch kürzere Formel
26.01.2015 12:31:05
Klaus
Hallo,
anbei die drastisch kürzere Formel für Tabelle2!G3 (und runter kopieren):
=WENNFEHLER(1/(1/MAX(SVERWEIS(D3;Actresses!A:F;6;);SVERWEIS(D3;Actresses!A:F;4;)));"")
Der Trick: 1/(1/Ergebnis) ergibt Ergebnis. 1/0 ergibt aber Fehler! Durch das prüfen auf den 1/0-Fehler spart man sich das lästige Wiederholen der gesamten Formel im sonst-Teil einer WENN Formel.
Grüße,
Klaus M.vdT.

Anzeige
AW: drastisch kürzere Formel
26.01.2015 12:54:23
Jenny
Hallo Klaus,
ja die Formel funktioniert. Danke.
Jenny

Danke für die Rückmeldung! owT.
26.01.2015 13:01:51
Klaus
.

AW: Danke für die Rückmeldung! owT.
26.01.2015 14:28:03
Jenny
Hallo Klaus,
mir ist doch noch ein Problem aufgefallen.
Gebe ich die Daten direkt in Spalte E ein, funktioniert deine Formel.
Lasse ich diese Spalte E jedoch durch die Formel
=WENN(D1="";"";WENN(ISTZAHL(D1);D1;DATUM(RECHTS(D1;4);SVERWEIS(TEIL(D1;FINDEN(" ";D1)+1;FINDEN(" "; D1;FINDEN(" ";D1)+1)-FINDEN(" ";D1)-1);{"January".1;"February".2;"March".3;"May".5;"June".6;"July".7; "October".10;"December".12};2;0);LINKS(D1;FINDEN(" ";D1)-1)))) 
berechnen, greift deine Formel nicht mehr auf denn Wert in Spalte C zurück, wenn Spalte E leer ist.
Kannst du da nochmal nach schaun?
Gruß
Jenny

Anzeige
Bitte als Datei ...
26.01.2015 14:30:40
Klaus
... sonst muss ich das erst nachbauen ...
LG,
Klaus

AW: Bitte als Datei ...
26.01.2015 14:50:22
Jenny
Hallo Klaus, hier nochmal eine Datei.
Du siehst Tabelle2!F1 bleibt leer, obwohl die Formel Actresses!C1 ausgeben müsste.
Lösche ich die Formel in Actresses!E1 wird Actresses!C1 ausgegeben.
Gruß und danke
Jenny
Nicht wundern, ich hab den Tabellenaufbau ein wenig geändert.
https://www.herber.de/bbs/user/95282.xlsx

wieder das "" und 0 Thema ...
26.01.2015 15:11:41
Klaus
Hallo Jenny,
ein leeres Feld ist für die Formel MAX ein Wert mit 0, wobei 0 eine Zahl ist. Actress!E1 ist aber nicht leer, sondern es steht "" in dem Feld - das ist ein Text mit null Zeichen, aber keine Zahl. Damit kann die MAX-Formel wieder nicht arbeiten.
Irgendeinen Tod musst du sterben. Entweder in Actresses!E:E eine Null zulassen
=WENN(D1="";0;WENN([...]
(die kannst du ja notfalls per bedingter Formatierung unsichtbar machen)
oder statt der kurzen MAX(SVERWEIS();SVERWEIS()) Formel ein langes Formelmonster mit Verdoppelung bauen, um aus den "" an dieser Stelle wieder 0-en zu machen:
=WENNFEHLER(1/(1/(MAX(WENN(SVERWEIS(D1;Actresses!A:C;3;)="";0;SVERWEIS(D1;Actresses!A:C;3;));WENN(SVERWEIS(D1;Actresses!A:E;5;)="";0;SVERWEIS(D1;Actresses!A:E;5;)))));"")
Oder eine Matrixformel nehmen, wie im Zweig weiter unten vorgeschlagen wurde, oder eine UDF programmieren.
Ich würde in Actresses die Nullen nehmen.
Grüße,
Klaus M.vdT.

Anzeige
AW: wieder das "" und 0 Thema ...
26.01.2015 15:23:53
Jenny
Hallo Klaus,
doch eine Möglichkeit habe ich noch gefunden, doch leider war mit dieser all deine Arbeit umsonst.
Hab ne Hilffspalte mit
=WENN(ODER(C1"";E1"");MAX(C1;E1);"")
gemacht und meine ursprüngliche Formel dann geändert auf
=WENNFEHLER(WENN(SVERWEIS(D1;Actresses!A:F;6;FALSCH)>0;SVERWEIS(D1;Actresses!A:F;6;FALSCH);"");"") 
das funktioniert.
Gruß und trotzdem danke
Jenny

Erlaubt ist was klappt :-) ...
26.01.2015 15:31:23
Klaus
... und wenn du das mit Hilfsspalten einfach lösen kannst ist es sicherlich besser, als ohne Hilfsspalten mit Zeilenlangen Monsterformeln!
LG,
Klaus

Anzeige
es geht noch kürzer ... aber ...
26.01.2015 14:24:53
neopa
Hallo Klaus,
... Dein Vorschlag hat den Vorteil dass er ohne "Fingerverrenkung" bei der Eingabe ;-) auskommt und auch nicht nicht mittels benutzerdefinierte Zahlenformat evtl. störende 0 Ergebnisse ausblenden muss.
 DEFG
3nm0000017  27.12.1902
4nm0007229  18.09.1905
5nm0062066   
6nm0006800  27.04.1937

Formeln der Tabelle
ZelleFormel
G3{=MAX(Actresses!D$1:F99*(Actresses!A$1:A99=D3)*{1.0.1})}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: es geht noch kürzer ... aber ...
26.01.2015 14:29:49
Jenny
Hallo Werner,
dann ist mir aufgrund der Berechnungszeit eine NICHT-Matrix Formel lieber, jedoch schau mal bittem habe grad noch was zu Klaus letzter Antwort geschrieben, vielleicht hast du ja auch eine Idee die auch dieses Problem löst.
Gruß und danke
Jenny

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige