Hallo,
ich bin auf der Suche nach einer Formel, die es mir ermöglicht in einen sverweis auch Bereiche und Platzhalter mit aufzunehmen.
Ich habe in Spalte A eine Liste mit einzelnen Kriterien; daneben gibt es in Spalte B/C auch noch verschiedene Nummernbereiche (z.B: von 2400000 bis 2409999) oder auch Platzhalter (heißen die so in dem Zusammenhang?) wie +240++++*.[
attachment=2215]
Wenn jetzt nun ein Kriterium aus Spalte F in Spalte A oder im definierten Nummernbereich/Platzhalter zu finden ist, dann soll mir "ja" zurückgegeben werden, ansonsten "nein".
Mit folgender Formel krieg ich das für die Liste der Kostenarten noch hin:
=WENN(ISTFEHLER(SVERWEIS(F1;'Tabelle 1'!A:A;1;FALSCH));"nein";"ja"))
...aber ich scheitere an den Nummernbereichen/Platzhaltern.
(Wenn ich die einzelnen Nummernbereiche auflöse, scheitere ich an der maximalen Zeilenanzahl in Excel und hab immer noch das Problem mit den Platzhaltern).
Gibt es hier in Excel eine Möglichkeit (Beispieldatei ist in LibreOffice, da ich hier grad nix anderes habe, aber ich bräcuhte die Formel in Excel)?
Danke vorab und viele Grüße!
Hallo Doreen,
unabhängig davon, dass ich das mit den Bereichen und den Platzhaltern noch nicht verstanden haben, kann deine Formel kein richtiges Ergebnis bringen. Du suchst aus Spalte F und willst aus Spalte A das Ergebnis haben. Der SVERWEIS funktioniert nicht nach links - die Suchspalte ist immer die erste Spalte der Matrix.
Schau dir dazu auch mal die integrierte Hilfe (F1) zu dieser Funktion an.
Hallo
Eine Lösung außerhalb der Wertung [*].
Wobei mir nicht klar ist warum "2405254N" als WAHR deklariert sein muss.
| | A | B | C | D | E | F | G | H | I | J | K |
| 1 | Kriterium (als Text) | Range/Platzhalter | | SOLL-Ergebnis | Ergebnis einer Auswertung (als Text) | Klassifizierung | entspricht Kriterium | liegt in einem Bereich | Platzhalter 1 | Platzhalter 2 |
| 2 | 1234567890 | 2400000 | 2499999 | | WAHR | 2400000 | WAHR | 0 | 1 | FALSCH | FALSCH |
| 3 | 1122334455 | 2600000 | 2609999 | | WAHR | 1357924680 | WAHR | 1 | 0 | FALSCH | FALSCH |
| 4 | 1231231234 | ?240????* | | | FALSCH | 1809876 | FALSCH | 0 | 0 | FALSCH | FALSCH |
| 5 | 1357924680 | ?260????* | | | WAHR | 2405254N | FALSCH | 0 | 0 | FALSCH | FALSCH |
| 6 | 2233445 | | | | WAHR | 2525252 | WAHR | 1 | 0 | FALSCH | FALSCH |
| 7 | 2345678 | | | | | | | | | | |
| 8 | 2323234 | | | | | | | | | | |
| 9 | 2525252 | | | | | | | | | | |
| 10 | 2525254N | | | | | | | | | | |
| 11 | C2525254 | | | | | | | | | | |
| Zelle | Formel |
| G2 | =ODER(H2:K2) |
| H2 | =ZÄHLENWENN($A$2:$A$11;F2) |
| I2 | =SUMMENPRODUKT(--($B$2:$B$3<=F2);--($C$2:$C$3>=F2))*ISTZAHL(--F2) |
| J2 | =ISLIKE2(F2;$B$4) |
| K2 | =ISLIKE2(F2;$B$5) |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
[*]Wegen der Verwendung von ISLIKE2() - Teil der Speedtools bzw. FastExcel V3.
Hallo Günter,
da hab ich zwei gute Nachrichten
SVERWEIS(F1;'Tabelle 1'!A:A;1;FALSCH)
F1 ist der Suchbegriff und Spalte A der Bereich, sollte also funktionieren.
SVERWEIS nach links:
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=24
Hallo Doreen,
das mit dem Begriff "Platzhalter" ist ok. Allerdings müsstest Du auch die korrekten verwenden - wie im Beitrag von shift-del zu sehen ist.
? steht als Ersatz für genau ein Zeichen
* steht als Ersatz für eine beliebige Anzahl Zeichen
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | A | B | C |
| 1 | Anton | Anton | Anton |
| 2 | Berta | 1 | ?nto? |
| 3 | Antonne | 2 | *nto* |
| Zelle | Formel |
| B1 | =SVERWEIS(C1;Tabelle2!A:A;1;FALSCH) |
| B2 | =ZÄHLENWENN(Tabelle2!A:A;C2) |
| B3 | =ZÄHLENWENN(Tabelle2!A:A;C3) |
| Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg |
Hi André,
(06.06.2015, 05:55)schauan schrieb: [ -> ]SVERWEIS(F1;'Tabelle 1'!A:A;1;FALSCH)
F1 ist der Suchbegriff und Spalte A der Bereich, sollte also funktionieren.
vielleicht bin ich ja wirklich ein wenig begriffstutzig. Ich habe nach Doreens Mustermappe das folgende, sehr einfache, Beispiel konstruiert. Und - erwartungsgemäß - funktioniert der Sverweis nach links nicht (ist, glaube ich, auch schon gefühlte 22 1/2 Millionen Mal im www ge- und beschrieben worden). Nun hoffe ich auf Erleuchtung.
Tabelle1 | A | B | C | D | E | F | G |
| 1 | Kriterium | Range | | | SOLL | Klassif. | Ergebnis |
| 2 | 1 | müller | | | ja | nein | müller |
| 3 | 2 | meier | | | ja | nein | meier |
| 4 | 3 | berger | | | nein | nein | schulze |
| 5 | 4 | huber | | | ja | nein | huber |
| Formeln der Tabelle |
| Zelle | Formel | | F2 | =WENN(ISTFEHLER(SVERWEIS(G2;Tabelle1!$A$2:$A$5;1;0));"nein";"ja") | | F3 | =WENN(ISTFEHLER(SVERWEIS(G3;Tabelle1!$A$2:$A$5;1;0));"nein";"ja") | | F4 | =WENN(ISTFEHLER(SVERWEIS(G4;Tabelle1!$A$2:$A$5;1;0));"nein";"ja") | | F5 | =WENN(ISTFEHLER(SVERWEIS(G5;Tabelle1!$A$2:$A$5;1;0));"nein";"ja") |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
(06.06.2015, 05:55)schauan schrieb: [ -> ]SVERWEIS nach links:
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=24
Die Lösung mit INDEX/VERGLEICH ist mir natürlich bekannt.
Hallo Günter,
Zitat von Excelformeln:
Ergänzung von Klaus "Perry" Pago:
Für die absoluten SVERWEIS-Nostalgiker geht es auch mit SVERWEIS:
=SVERWEIS("Schulze";WAHL({2.1};A1:A10;B1:B10);2;0)
Dabei funktionieren aber keine ganzen Spaltenangaben (A:A).
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C |
| 1 | 1 | Müller | 3 |
| 2 | 2 | Maier | 3 |
| 3 | 3 | Schulze | |
| 4 | 4 | Meyer | |
| Zelle | Formel |
| C1 | =SVERWEIS("Schulze";WAHL({2.1};A1:A10;B1:B10);2;0) |
| C2 | =INDEX(A:A;VERGLEICH("Schulze";B:B;0)) |
| Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg |
INDEX ist hier aber sicher die bessere WAHL
Hallo zusammen,
oh, ihr seit aber fleißig. Vielen Dank für die Rückmeldungen!
Es war meine erste Frage in einem Forum - sie hätte sicher noch mehr Details/Erklärungen enthalten können. Aber natürlich ist es für denjenigen, der schreibt völlig logisch
Danke für den Hinweis mit den "falschen" Platzhaltern. Ich hatte die Selektionskriterien aus einer Business Warehouse Abfrage so übernommen. Das scheinen dann wohl andere zu sein, als im Excel verwendet werden.
Letztlich will ich das Suchkriterium in Spalte G dahingehend untersuchen, ob Sie entweder in der Spalte A zu finden sind oder aber im Nummernbereich oder den Platzhaltern (hiermit wird im Wesentlichen ein zusätzlicher Buchstaben am Ende mit abgedeckt). Es muß wahrscheinlich gar nicht mal ein Sverweis sein, der diese Kriterien untersucht (ich will ja keinen rechtsgelagerten Wert zurückgeben, sondern nur prüfen, ob das Suchkriterium zu finden/enthalten ist).
Zur Frage von shift-del, warum "2405254N" als WAHR deklariert sein muss > weil es "+240++++*" abgedeckt wäre > oder verlangt das "+" am Anfang zwangsläufig ein Zahl/Buchstaben? Verstehe ich richtig, daß diese Islike2 - Formel im "normalen" Excel nicht funktioniert?
Ok, dann versuch ich mal Eure Vorschläge im Detail zu verstehen und umzusetzen.
Nochmal Danke!
Zitat:Zur Frage von shift-del, warum "2405254N" als WAHR deklariert sein muss > weil es "+240++++*" abgedeckt wäre > oder verlangt das "+" am Anfang zwangsläufig ein Zahl/Buchstaben?
Ich habe das + als Äquivalent zum ? gesehen. Und das steht für genau ein Zeichen.
Damit "2405254N" selektiert wird müsste der Filter "240*" oder "*240*" lauten.
Zitat:Verstehe ich richtig, daß diese Islike2 - Formel im "normalen" Excel nicht funktioniert?
Genau. Man benötigt eines der genannten Add-Ins.
Hallo,
habe ich da etwas übersehen, die Tabelle ist doch OpenOffice?