Hallo zusammen,
ich bin so froh das ich euer Forum gefunden habe! Schon oft habe ich hier Hilfe gefunden in anderen Threads aber diesmal muss ich selbst fragen denn ich habe nichts passendes gefunden.
In der angehängten Tabelle hab ich euch einen kleinen Ausschnitt meiner Tabelle gepackt.
Es geht um folgendes: ich habe eine große Tabelle mit PLZ aus mehreren Gebieten, leider wurde die Tabelle erstellt (nicht von mir) und dabei mehrere PLZ in eine Zelle geschrieben.
Wenn ich die Suchfunktion in der Tabelle benutze, findet Excel auch eine PLZ die mit anderen PLZ in einer Zelle steht.
Ich möchte aber, bzw muss, das Ganze wie in Tabelle 2 dargestellt, auswerten. D.h. ich gebe eine PLZ ein und erhalte dann eine Aussage ob diese berechtigt ist oder nicht.
Der SVerweis den ich dort verwende, scheint aber nicht mit den mehreren Werten in einer Zelle klar zu kommen.
Hat jemand von euch vielleicht eine Lösung wie ich das Ganze hin bekomme?
Ich habe auch schon mit dem Gedanken gespielt die Werte alle zu trennen und in einzelne Zellen zu bringen, aber dies gestaltet sich sehr aufwendig bei über 5000 Datensätzen.
Hallo, wenn ich dass richtig verstanden habe könnte das hier gehen..:
PHP-Code:
=WENN((LÄNGE(C7)=5)*(ISTZAHL(VERWEIS(42;1/SUCHEN(C7;Tabelle1!A2:A13);ZEILE(B2:B13))));"ja";"nein")
Die Bereiche musst du (aber) anpassen...
Hallo,
der Ansatz war mE der richtige. Ich würde das aber so machen:
Code:
=WENN(ISTNV(SVERWEIS("*"&C7&"*";Tabelle1!A:C;1;FALSCH));"nein";"ja")
Denn wenn der Wert nicht gefunden wird, kommt ein #NV und das kann man abfragen.
EDIT: Gegebenenfalls kann es zu Geschwindigkeitsverlust führen, wenn Du den SVERWEIS über die gesamten Spalten anwendest. Ich würde hier nur den tatsächlich notwendigen Bereich verwenden!
(10.10.2018, 14:12)Der Steuerfuzzi schrieb: [ -> ]Hallo,
der Ansatz war mE der richtige...
Hallo, probier' deine Formel doch mal mit 52477... Außerdem würde ich den VERGLEICH() nehmen, statt SVERWEIS() mit Spaltenindex 1... Aber ich habe ja eh einen anderen Ansatz.
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| B | C | D | E |
| 3 | |
| 4 |
| 5 | | PLZ | Berechtigt? | |
| 6 | |
| 7 | 52477 | ja |
| 8 | |
| 9 |
| Zelle | Formel |
| D7 | =WENN((LÄNGE(C7)=5)*(ISTZAHL(VERWEIS(42;1/SUCHEN(C7;Tabelle1!A2:A13);ZEILE(B2:B13))));"ja";"nein") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| B | C | D | E |
| 3 | |
| 4 |
| 5 | | PLZ | Berechtigt? | |
| 6 | |
| 7 | 52477 | nein |
| 8 | |
| 9 |
| Zelle | Formel |
| D7 | =WENN(ISTNV(SVERWEIS("*"&C7&"*";Tabelle1!A:C;1;FALSCH));"nein";"ja") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo,
Michaels Formel klappt bei den allein stehenden PLZ nur, wenn diese als Text formatiert sind. Außerdem werden auch unvollständig eingegebene Zahlen als vorhanden angegeben.
Gruß Uwe
(10.10.2018, 14:20)Jockel schrieb: [ -> ]Hallo, probier' deine Formel doch mal mit 52477... Außerdem würde ich den VERGLEICH() nehmen, statt SVERWEIS() mit Spaltenindex 1... Aber ich habe ja eh einen anderen Ansatz.
Bei mir funktioniert es :05: Allerdings habe ich auch die Postleitzahlen der Tabelle1 als Text formatiert. Das würde sowieso sinn machen, da dann auch PLZ, die mit '0' beginnen, richtig angezeigt werden.
Ob man jetzt VERGLEICH oder SVERWEIS nimmt ist mE egal. Es wäre auch folgende alternative VERWEIS-Formel denkbar, dann kann man sich die Formatierung als Text sparen:
Code:
=WENN(ISTNV(VERWEIS(2;1/FINDEN(C7;Tabelle1!A2:A16)));"Nein";"ja")
EDIT: Der Einwand von Uwe ist sehr gut. Unvollständige Postleitzahlen werden auch als richtig markiert. Wenn das nicht stört, kann man also die Prüfung auf 5 Stellen weglassen. Diese Prüfung auf LÄNGE(...)=5 ist sowieso problematisch, da hier als Zahl formatierte PLZ, die mit '0' beginnen nicht korrekt ausgegeben würden.
(10.10.2018, 14:40)Der Steuerfuzzi schrieb: [ -> ]Bei mir funktioniert es :05: Allerdings habe ich auch die Postleitzahlen der Tabelle1 als Text formatiert. Das würde sowieso sinn machen, da dann auch PLZ, die mit '0' beginnen, richtig angezeigt werden.
Ob man jetzt VERGLEICH oder SVERWEIS nimmt ist mE egal. Es wäre auch folgende alternative VERWEIS-Formel denkbar, dann kann man sich die Formatierung als Text sparen:
Code:
=WENN(ISTNV(VERWEIS(2;1/FINDEN(C7;Tabelle1!A2:A16)));"Nein";"ja")
EDIT: Der Einwand von Uwe ist sehr gut. Unvollständige Postleitzahlen werden auch als richtig markiert. Wenn das nicht stört, kann man also die Prüfung auf 5 Stellen weglassen. Diese Prüfung auf LÄNGE(...)=5 ist sowieso problematisch, da hier als Zahl formatierte PLZ, die mit '0' beginnen nicht korrekt ausgegeben würden.
Super! Die Formel funktioniert einwandfrei.
Habe die PLZ trotzdem als Text markiert und werde mir noch überlegen ob ich die Prüfung auf 5 Stellen einfüge.
Ich muss mich noch entscheiden wer die Tabelle nutzen darf, je nach dem macht eine Prüfung extrem Sinn.
Vielen lieben Dank an euch alle! :) :)
Was genau macht die ISTNV Formel? Kann ich allerdings auch googeln. Möchte ja auch verstehen was ich da jetzt eingefügt hab ;)