Clever-Excel-Forum › Office-Anwendungen › Excel v
Sverweis mit Nummernbereich (als Text) und Platzhaltern

Thema geschlossen 
Baumstrukturmodus
Sverweis mit Nummernbereich (als Text) und Platzhaltern
Doreen Offline
pausiert
Registriert seit: 05.06.2015
Version(en): 365
#1
05.06.2015, 21:43
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++++*.
.ods   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!
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#2
06.06.2015, 05:34
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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#3
06.06.2015, 06:01 (Dieser Beitrag wurde zuletzt bearbeitet: 06.06.2015, 06:02 von shift-del.)
Hallo

Eine Lösung außerhalb der Wertung [*].
Wobei mir nicht klar ist warum "2405254N" als WAHR deklariert sein muss.
 ABCDEFGHIJK
1Kriterium (als Text)Range/Platzhalter SOLL-ErgebnisErgebnis einer Auswertung (als Text)Klassifizierungentspricht Kriteriumliegt in einem BereichPlatzhalter 1Platzhalter 2
2123456789024000002499999 WAHR2400000WAHR01FALSCHFALSCH
3112233445526000002609999 WAHR1357924680WAHR10FALSCHFALSCH
41231231234?240????*  FALSCH1809876FALSCH00FALSCHFALSCH
51357924680?260????*  WAHR2405254NFALSCH00FALSCHFALSCH
62233445   WAHR2525252WAHR10FALSCHFALSCH
72345678          
82323234          
92525252          
102525254N          
11C2525254          

ZelleFormel
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.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

[-] Folgende(r) 1 Nutzer sagt Danke an shift-del für diesen Beitrag:
  • Doreen
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#4
06.06.2015, 06:55
Hallo Günter,

da hab ich zwei gute Nachrichten Wink

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
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#5
06.06.2015, 06:58
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'
 ABC
1AntonAntonAnton
2Berta1?nto?
3Antonne2*nto*

ZelleFormel
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
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Doreen
Homepage Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#6
06.06.2015, 08:21 (Dieser Beitrag wurde zuletzt bearbeitet: 06.06.2015, 08:22 von WillWissen. Bearbeitungsgrund: Tab.-Ausschn. eingefügt )
Hi André,

(06.06.2015, 06: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. Whistle

Tabelle1

ABCDEFG
1Kriterium RangeSOLLKlassif.Ergebnis
21müllerjaneinmüller
32meierjaneinmeier
43bergerneinneinschulze
54huberjaneinhuber
Formeln der Tabelle
ZelleFormel
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, 06: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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#7
06.06.2015, 10:44 (Dieser Beitrag wurde zuletzt bearbeitet: 06.06.2015, 10:45 von 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'
 ABC
11Müller3
22Maier3
33Schulze 
44Meyer 

ZelleFormel
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 Smile
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
Doreen Offline
pausiert
ThreadStarter
Registriert seit: 05.06.2015
Version(en): 365
#8
06.06.2015, 12:26
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 Blush

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!
Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#9
06.06.2015, 12:32
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.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Homepage Suchen
Top
BoskoBiati Offline
नौसिखिया [vidyaarthi]

Registriert seit: 13.04.2014
Version(en): 365
#10
06.06.2015, 17:34
Hallo,

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

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste