Sverweis mit Nummernbereich (als Text) und Platzhaltern

[Doreen]

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++++*.
  Beispiel.ods (Größe: 13,63 KB / Downloads: 11)

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!

[WillWissen]

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.

[shift-del]

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.

[schauan]

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

[schauan]

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

[WillWissen]

Hi André,

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

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.

[schauan]

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

[Doreen]

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!

[shift-del]

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.

Verstehe ich richtig, daß diese Islike2 - Formel im "normalen" Excel nicht funktioniert?

Genau. Man benötigt eines der genannten Add-Ins.

[BoskoBiati]

Hallo,

habe ich da etwas übersehen, die Tabelle ist doch OpenOffice?