Supportnet / Forum / Tabellenkalkulation
WENN Funktion mit mehr als 7 Bedingungen?
Frage
Hallo ich habe in 348 Zeilen mehrstellige Nummern stehen. Diese Nummer sind Wirtschaftszweige und sollen aggregiert werden.
Für eine zweifelsfreie Identifikation brauche ich nur die ersten beiden Stellen, das hab ich mit:
=TEIL(Zielzelle;2) hinbekommen
Nun soll anhand der ersten beiden Stellen dem Wirtschaftszweig eine neues Bezeichnung zugeordnet werden, das hab ich mit folgender Formel versucht:
=WENN(AZ4>98;"Q";WENN(AZ4>94;"P";WENN(AZ4>89;"O";WENN(AZ4>80;"N";WENN(AZ4>79;"M";WENN(AZ4>74;"L";WENN(AZ4>69;"K";WENN(AZ4>64;"J";WENN(AZ4>59;"I";WENN(AZ4>54;"H";WENN(AZ4>49;"G";WENN(AZ4>44;"F";WENN(AZ4>39;"E";WENN(AZ4>35;"DN";WENN(AZ4>33;"DM";WENN(AZ4>29;"DL";WENN(AZ4>28;"DK";WENN(AZ4>26;"DJ";WENN(AZ4>25;"DI";WENN(AZ4>24;"DH";WENN(AZ4>23;"DG";WENN(AZ4>22;"DF";WENN(AZ4>20;"DE";WENN(AZ4>19;"DD";WENN(AZ4>18;"DC";WENN(AZ4>16;"DB";WENN(AZ4>14;"DA";WENN(AZ4>5;"C";WENN(AZ4>4;"B";WENN(AZ4>0;"A";"XX"))))))))))))
Leider klappt es nicht, ich vermute, da ich mehr als 7 Verschachtelungen gewählt hab. Woran kann es liegen und wie löse ich mein Problem?
1000 Dank!
MfG
Marquis
Antwort 1 von landibaer
hi,
ich würde das mit einem SVERWEIS auf eine Hilfstabelle machen. Das geht dann recht schnell.
Gruß
Michael
ich würde das mit einem SVERWEIS auf eine Hilfstabelle machen. Das geht dann recht schnell.
Gruß
Michael
Antwort 2 von Aliba
Hi Marquis,
entweder Du legst Dir eine Hilfstabelle an, mit den entsprechenden Wert-Buchstaben-Gegenüberstellung und machst es über die SVERWEIS-Formel, oder Du schreibst es direkt in die SVERWEIS-Formel, z.B.:
=WENN(ODER(AZ4="";ISTNV(SVERWEIS(AZ4;{0."A";5."B";6."C";....98."Q"};2;1)));"XX";SVERWEIS(AZ4;{0."A";5."B";6."C";....98."Q"};2;1))
CU Aliba
entweder Du legst Dir eine Hilfstabelle an, mit den entsprechenden Wert-Buchstaben-Gegenüberstellung und machst es über die SVERWEIS-Formel, oder Du schreibst es direkt in die SVERWEIS-Formel, z.B.:
=WENN(ODER(AZ4="";ISTNV(SVERWEIS(AZ4;{0."A";5."B";6."C";....98."Q"};2;1)));"XX";SVERWEIS(AZ4;{0."A";5."B";6."C";....98."Q"};2;1))
CU Aliba
Antwort 3 von Marquis79
Hallo,
wenn ich zitieren darf:
"WENN ;-) dein einziges Werzeug ein Hammer ist, dann wirst du genaigt sein, in jedem Problem einen Nagel zu sehen."
Ich probiere es mal mit SVERWEIS und meld mich wieder, wenn ich damit nicht zurecht komme!
Eine erste Frage hab ich aber, ich habe keine Wert-Buchstaben-Gegenüberstellung, sondern eine Wertbereiche-Buchstaben-Gegenüberstellung, wie mache ich das?
So?
A | 01-02
B | 05
C | 10-14
usw.
Erstmal danke!!
MfG
Marquis
wenn ich zitieren darf:
"WENN ;-) dein einziges Werzeug ein Hammer ist, dann wirst du genaigt sein, in jedem Problem einen Nagel zu sehen."
Ich probiere es mal mit SVERWEIS und meld mich wieder, wenn ich damit nicht zurecht komme!
Eine erste Frage hab ich aber, ich habe keine Wert-Buchstaben-Gegenüberstellung, sondern eine Wertbereiche-Buchstaben-Gegenüberstellung, wie mache ich das?
So?
A | 01-02
B | 05
C | 10-14
usw.
Erstmal danke!!
MfG
Marquis
Antwort 4 von Marquis79
Also das hier ist meine Formel:
=WENN(ODER(AZ4="";ISTNV(SVERWEIS(AZ4;{0."A";5."B";10."C";15."DA";17."DB";19."DC";20."DD";21."DE";23."DF";24."DG";25."DH";26."DI";27."DJ";29."DK";30."DL";34."DM";36."DN";40."E";45."F";50."G";55."H";60."I";65."J";70."K";75."L";80."M";85."N";90."O";95."P";98."Q"};2;1)));"XX";SVERWEIS(AZ4;{0."A";5."B";10."C";15."DA";17."DB";19."DC";20."DD";21."DE";23."DF";24."DG";25."DH";26."DI";27."DJ";29."DK";30."DL";34."DM";36."DN";40."E";45."F";50."G";55."H";60."I";65."J";70."K";75."L";80."M";85."N";90."O";95."P";98."Q"};2;1))
mit der hat es leider nicht geklappt, ich bekomme immer XX ausgegeben.
Woran kann es liegen?
MfG
Marquis
=WENN(ODER(AZ4="";ISTNV(SVERWEIS(AZ4;{0."A";5."B";10."C";15."DA";17."DB";19."DC";20."DD";21."DE";23."DF";24."DG";25."DH";26."DI";27."DJ";29."DK";30."DL";34."DM";36."DN";40."E";45."F";50."G";55."H";60."I";65."J";70."K";75."L";80."M";85."N";90."O";95."P";98."Q"};2;1)));"XX";SVERWEIS(AZ4;{0."A";5."B";10."C";15."DA";17."DB";19."DC";20."DD";21."DE";23."DF";24."DG";25."DH";26."DI";27."DJ";29."DK";30."DL";34."DM";36."DN";40."E";45."F";50."G";55."H";60."I";65."J";70."K";75."L";80."M";85."N";90."O";95."P";98."Q"};2;1))
mit der hat es leider nicht geklappt, ich bekomme immer XX ausgegeben.
Woran kann es liegen?
MfG
Marquis
Antwort 5 von pelle
Hallo Marquis,
deine lange Formel in Antwort 4 möchte ich mir nicht näher angucken, ist mir zu lang!
Aber um mal auf deinen Hammer in Antwort 3 zurück zukommen, würde ich folgendes vorschlagen:
Lege doch für jeden Wert im deinem Wertebereich eine Zeile an, dann klappt auch der Sverweis!
gruss
pelle
deine lange Formel in Antwort 4 möchte ich mir nicht näher angucken, ist mir zu lang!
Aber um mal auf deinen Hammer in Antwort 3 zurück zukommen, würde ich folgendes vorschlagen:
Lege doch für jeden Wert im deinem Wertebereich eine Zeile an, dann klappt auch der Sverweis!
gruss
pelle
Antwort 6 von Marquis79
Also ich hab nun folgende Formel
=SVERWEIS(D4;$F$4:$G$61;2)
In D4 ist mein Suchkriterium
F4:G61 gibt meine Matrix an
F-Spalte in den einzelnen Zellen:
zwei Zahlen von 01-99, als Text formatiert (das 01 erhalten bleibt ist wichtig), nicht alle Zahlen dazwischen sind vorhanden.
G-Spalte in den einzelnen Zellen:
Jeder Zahl aus Spalte F hat einen oder zwei Buchstaben zugeordnet, nicht alle Buchstaben des Alphabet sind vorhanden.
Ich möchte nun über die Formel aus der Suchmatrix die zweite Spalte, in meinem Fall Spalte G ausgegeben haben.
Es klappt leider nicht bei allen Zellen, einige Zuordnungen funktionieren, andere nicht???
MfG
Marquis
=SVERWEIS(D4;$F$4:$G$61;2)
In D4 ist mein Suchkriterium
F4:G61 gibt meine Matrix an
F-Spalte in den einzelnen Zellen:
zwei Zahlen von 01-99, als Text formatiert (das 01 erhalten bleibt ist wichtig), nicht alle Zahlen dazwischen sind vorhanden.
G-Spalte in den einzelnen Zellen:
Jeder Zahl aus Spalte F hat einen oder zwei Buchstaben zugeordnet, nicht alle Buchstaben des Alphabet sind vorhanden.
Ich möchte nun über die Formel aus der Suchmatrix die zweite Spalte, in meinem Fall Spalte G ausgegeben haben.
Es klappt leider nicht bei allen Zellen, einige Zuordnungen funktionieren, andere nicht???
MfG
Marquis
Antwort 7 von pelle
Hallo Marquis,
ohne Tabelle ist dies etwas problematische nachzuvollziehen.
Es könnte vielleicht an der Sortierung deiner Tabelle liegen.
Ändere mal deine Formel wie folgt:
=SVERWEIS(D4;$F$4:$G$61;2;Falsch)
dann ist die Sortierung egal!
gruss
pelle
ohne Tabelle ist dies etwas problematische nachzuvollziehen.
Es könnte vielleicht an der Sortierung deiner Tabelle liegen.
Ändere mal deine Formel wie folgt:
=SVERWEIS(D4;$F$4:$G$61;2;Falsch)
dann ist die Sortierung egal!
gruss
pelle
Antwort 8 von Marquis79
Hi,
also mit deiner Formel:
macht er nun folgendes:
Alle denen vorher die richtige Buchstabenkombi zugeordnet wurde, sind wieder ausgegeben und richtig :-)
Alle denen vorher die falsche Buchstabenkombi zugeordnet wurde, werden nun als #NV ausgegeben.
Verdammt und zugenäht..., manchmal ist es zum verzweifeln. Ich will wieder ein HAMMER-Problem ;-)
Dank dir!
MfG
Marquis
also mit deiner Formel:
Zitat:
=SVERWEIS(D4;$F$4:$G$61;2;Falsch)
=SVERWEIS(D4;$F$4:$G$61;2;Falsch)
macht er nun folgendes:
Alle denen vorher die richtige Buchstabenkombi zugeordnet wurde, sind wieder ausgegeben und richtig :-)
Alle denen vorher die falsche Buchstabenkombi zugeordnet wurde, werden nun als #NV ausgegeben.
Verdammt und zugenäht..., manchmal ist es zum verzweifeln. Ich will wieder ein HAMMER-Problem ;-)
Dank dir!
MfG
Marquis
Antwort 9 von pelle
Hallo Marquis,
dann sind wir ja schon mal ein Stück weiter! :-)
Wenn du jetzt die #NV Meldung bekommst, heisst das ganz klar, dieser Schlüssel ist nicht vergeben!!!!
Entweder stimmt was nicht in deiner "Ausgangstabelle" (?) unbekannte/falsche Wirtschaftszweige oder du hast den Wertebereich nicht vollständig angelegt!
gruss
pelle
dann sind wir ja schon mal ein Stück weiter! :-)
Wenn du jetzt die #NV Meldung bekommst, heisst das ganz klar, dieser Schlüssel ist nicht vergeben!!!!
Entweder stimmt was nicht in deiner "Ausgangstabelle" (?) unbekannte/falsche Wirtschaftszweige oder du hast den Wertebereich nicht vollständig angelegt!
gruss
pelle
Antwort 10 von Marquis79
Bin mit meinem Können am Ende, hab wirklich keine Ahnung, die Werte die ich nicht ausgegeben bekomme sind definiert, ich weiß nicht warum es bei einigen Zellen klappt und bei anderen nicht, wirklich komisch ???
Antwort 11 von pelle
Hallo Marquis,
willst doch wohl noch nicht aufgeben?
Kannst du die Tabelle z.B. hier:
http://www.netupload.de
mal einstellen und den Link nachher hier einstellen!
Manchmal sieht man vor lauter Bäume den Wald nicht mehr, ist mir jetzt auch mal bei Access so ergangen!
gruss
pelle
willst doch wohl noch nicht aufgeben?
Kannst du die Tabelle z.B. hier:
http://www.netupload.de
mal einstellen und den Link nachher hier einstellen!
Manchmal sieht man vor lauter Bäume den Wald nicht mehr, ist mir jetzt auch mal bei Access so ergangen!
gruss
pelle
Antwort 12 von Marquis79
Aufgeben kann ich gar nicht, brauche das dringend ;-)
Netter Webservice, kannte ich noch gar nicht. Hier der Link:
http://www.netupload.de/downloaddata.php/c3459805c321dc35aee747d036b7eea9.xls
1000 Dank!!
MfG
Marquis
Netter Webservice, kannte ich noch gar nicht. Hier der Link:
http://www.netupload.de/downloaddata.php/c3459805c321dc35aee747d036b7eea9.xls
1000 Dank!!
MfG
Marquis
Antwort 13 von pelle
Hallo Marquis,
Problem gelöst!!!
Ich habe mir mal deine Tabelle angeschaut!
Punkt 1:
Du hast deine Suchfelder und die Vergleichsfelder unterschiedlich formatiert bzw. auf Standard stehen!
Mal werden sie als Zahl mals Text interpretiert!
Dadurch kommt Excel mit der Funktion ins schleudern!
Ich habe es mal umgewandelt un angepasst!
Auf den ersten Blick läuft es sauber!!
Habe auch einen Wert gefunden, der nicht hinterlegt ist!
Ich kann dir die angepasste Tabelle mal zuschicken,
brauchte dafür aber deine E-Mail Adresse!
gruss
pelle
Ps:: Bin noch etwa 20 Minuten online!
Problem gelöst!!!
Ich habe mir mal deine Tabelle angeschaut!
Punkt 1:
Du hast deine Suchfelder und die Vergleichsfelder unterschiedlich formatiert bzw. auf Standard stehen!
Mal werden sie als Zahl mals Text interpretiert!
Dadurch kommt Excel mit der Funktion ins schleudern!
Ich habe es mal umgewandelt un angepasst!
Auf den ersten Blick läuft es sauber!!
Habe auch einen Wert gefunden, der nicht hinterlegt ist!
Ich kann dir die angepasste Tabelle mal zuschicken,
brauchte dafür aber deine E-Mail Adresse!
gruss
pelle
Ps:: Bin noch etwa 20 Minuten online!
Antwort 14 von DukeNT
Hi Marquis79,
eine andere Lösung hätte ich per VBE zur Hand.
Kein Plan ob du da mit klar kommst aber kannst ja mal versuchen.
Geh in den Visual Basic-Editor ->Einfügen->Modul
In das Fenster kopierst du folgendes:
Sub zuordnen()
Application.ScreenUpdating = False
lz = Sheets("Tabelle1").Range("AZ65536").End(xlUp).Row
For a = 1 To lz
Zelle = Cells(a, 52).Value
Select Case Zelle
Case Is > 98
Cells(a, 1).Value = "Q"
Case Is > 94
Cells(a, 1).Value = "P"
...
...
Case Is > 0
Cells(a, 1).Value = "A"
Case Else
Cells(a, 1).Value = "XX"
End Select
Next
Application.ScreenUpdating = True
End Sub
VBE beenden und dann einfach das Macro "zuordnen" ausführen.
Wenn dein Tabellenblatt anders als "Tabelle1" heist dann im Code entsprechend ändern.
Teste erstmal nur mit ein paar zeilen ob es geht! Hast ja schon genug an vergleichstabellen erstellt.
LG Duke
eine andere Lösung hätte ich per VBE zur Hand.
Kein Plan ob du da mit klar kommst aber kannst ja mal versuchen.
Geh in den Visual Basic-Editor ->Einfügen->Modul
In das Fenster kopierst du folgendes:
Sub zuordnen()
Application.ScreenUpdating = False
lz = Sheets("Tabelle1").Range("AZ65536").End(xlUp).Row
For a = 1 To lz
Zelle = Cells(a, 52).Value
Select Case Zelle
Case Is > 98
Cells(a, 1).Value = "Q"
Case Is > 94
Cells(a, 1).Value = "P"
...
...
Case Is > 0
Cells(a, 1).Value = "A"
Case Else
Cells(a, 1).Value = "XX"
End Select
Next
Application.ScreenUpdating = True
End Sub
VBE beenden und dann einfach das Macro "zuordnen" ausführen.
Wenn dein Tabellenblatt anders als "Tabelle1" heist dann im Code entsprechend ändern.
Teste erstmal nur mit ein paar zeilen ob es geht! Hast ja schon genug an vergleichstabellen erstellt.
LG Duke
Antwort 15 von Saarbauer
Hallo,
nach etwas knobel eine für @Marquis79 aus meiner Sicht brauchbare Lösung mit
=VERWEIS(TEIL(A1;1;2);Tabelle1!$A$1:$A$30;Tabelle1!$B$1:$B$30)
wobei ich die Grenzwerte um einen Punkt nach oben schieben musste und alle Zahlen als Text abgespeichert habe.
http://www.netupload.de/detail.php?img=b636eae973f5351434eea5eb75f5...
meine Testtabelle hinterlegt. Hatte es auch mit Sverweis versuch, klappte aber nicht
Gruß
Helmut
nach etwas knobel eine für @Marquis79 aus meiner Sicht brauchbare Lösung mit
=VERWEIS(TEIL(A1;1;2);Tabelle1!$A$1:$A$30;Tabelle1!$B$1:$B$30)
wobei ich die Grenzwerte um einen Punkt nach oben schieben musste und alle Zahlen als Text abgespeichert habe.
http://www.netupload.de/detail.php?img=b636eae973f5351434eea5eb75f5...
meine Testtabelle hinterlegt. Hatte es auch mit Sverweis versuch, klappte aber nicht
Gruß
Helmut
Antwort 16 von Marquis79
Hi,
meine Mail: marquis79@web.de
Welchen Wert hast du denn gefunden?
1000 Dank, echt super!!!
MfG
Marquis
meine Mail: marquis79@web.de
Welchen Wert hast du denn gefunden?
1000 Dank, echt super!!!
MfG
Marquis
Antwort 17 von pelle
Hallo Marquis,
die Tabelle ist unterwegs!
habe sie hier
http://www.netupload.de/detail.php?img=4f3439e111d4232ef4628758b66c...
auch nochmal eingestellt!
Der Wert 72 fehlte!
Ich hoffe es hilft dir weiter!
gruss
pelle
@Helmut
Grüß dich!
Eins verstehe ich bei deiner Antwort nicht!
Wenn du doch alle Felder als Textfelder defniert hast, müsste doch der Sverweis bei dir auch funktioniert haben.
Ich habe doch eigentlich nur den anderen Weg der Zahlenfelder gewählt!
gruss
pelle
die Tabelle ist unterwegs!
habe sie hier
http://www.netupload.de/detail.php?img=4f3439e111d4232ef4628758b66c...
auch nochmal eingestellt!
Der Wert 72 fehlte!
Ich hoffe es hilft dir weiter!
gruss
pelle
@Helmut
Grüß dich!
Eins verstehe ich bei deiner Antwort nicht!
Wenn du doch alle Felder als Textfelder defniert hast, müsste doch der Sverweis bei dir auch funktioniert haben.
Ich habe doch eigentlich nur den anderen Weg der Zahlenfelder gewählt!
gruss
pelle
Antwort 18 von Saarbauer
Hallo @pelle,
hatte zwischendrinn immer wieder #NV o.ä.
Gruß
Helmut
hatte zwischendrinn immer wieder #NV o.ä.
Gruß
Helmut
Antwort 19 von pelle
Hallo @Helmut,
ist ja schon ein wenig komisch!
Ich vermute mal, dass ein Problem mit den Standarddefinitionen besteht!
Ich musste um das Problem von Marquis zu lösen auch
a) ein Hilfsspalte einrichten und mit der Funktion Wert(XX:XX) den Wert ermitteln
b) anschließend diese Spalte kopiert und als Werte einfügen an gleicher Stelle wieder hinterlegt.
c) die Hilfsspalte, die ja jetzt als Zahl definiert war auf seine Originalspalte kopiert!
Einfach die Zellen als Zahl formatieren klappte nicht!
gruss
pelle
ist ja schon ein wenig komisch!
Ich vermute mal, dass ein Problem mit den Standarddefinitionen besteht!
Ich musste um das Problem von Marquis zu lösen auch
a) ein Hilfsspalte einrichten und mit der Funktion Wert(XX:XX) den Wert ermitteln
b) anschließend diese Spalte kopiert und als Werte einfügen an gleicher Stelle wieder hinterlegt.
c) die Hilfsspalte, die ja jetzt als Zahl definiert war auf seine Originalspalte kopiert!
Einfach die Zellen als Zahl formatieren klappte nicht!
gruss
pelle
Antwort 20 von Marquis79
1000 Dank, hat mir sehr geholfen, super Support hier im Net(t)!!
mfg
Marquis
mfg
Marquis
Antwort 21 von pelle
Hallo Marquis,
schön, dass es dir geholfen hat und Dank für deine Rückmeldung!
gruss
pelle
schön, dass es dir geholfen hat und Dank für deine Rückmeldung!
gruss
pelle