Microsoft Excel

Herbers Excel/VBA-Archiv

sverweis mit dynamischen Suchbereich

Betrifft: sverweis mit dynamischen Suchbereich von: Joachim
Geschrieben am: 20.08.2008 10:26:28

Hallo,

gibt es eine Möglichkeit, den Suchbereich des sverweis zu dynamisieren, soll heissen:

Bei sverweis(A1;Tabelle1!A2:F30;2;FALSCH) soll F30 dynamisch sein, Excel soll das Ende der Tabelle erkennen. Wenn also 2 neue Zeilen dazukommen, muss der sverweis bis F32 gehen.

Man könnte natürlich gleich F100 reinschreiben, aber das macht die Suche langsamer.

Da ich das öfter brauche, wäre so ein dyn. Ende echt toll.

Gruss
Joachim

  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Hajo_Zi
Geschrieben am: 20.08.2008 10:29:27

Hallo Joachim,

http://hajo-excel.de/gepackt/ohne_vba/sverweis_hp.htm

GrußformelHomepage


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Rudi Maintaire
Geschrieben am: 20.08.2008 10:32:09

Hallo,

aber das macht die Suche langsamer


die Festlegung eines dynamischen Bereichs wird die Sache auch nicht gerade beschleunigen.

Namen definieren; Name: Daten; Bezug: =Bereich.Verschieben($A$2:$F$2;;;anzahl2($A:$A)-1)

Gruß
Rudi


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Daniel
Geschrieben am: 20.08.2008 10:49:48

Hi

soweit ich weiß, kannst du problemslos A:F (also die ganze Spalte) als Suchmatrix verwenden, da Excel dann automatisch nur den benutzen Zellbereich verwendet und nicht stur alle Zeilen durchsucht.

was den SVERWEIS langsam macht, ist der 4. Parameter = FALSCH, hier lassen sich vor allembei grösseren Datenmengen gewaltige Geschwindigkeitsverbesserungen erzielen, wenn dieser Parameter auf WAHR gesetzt wird.
allerdings gilt dabei zu beachten:
- die Daten in der Suchmatrix müssen aufsteigend nach der 1. Spalte sortiert sein
- es gibt keine Fehlermeldung bei einem nicht gefundenen Wert, dh. sollte das abgesichert werden, müsste dies geprüft werden (Sverweis mit Spalte 1 als Rückgabewert)

Gruß, Daniel


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Joachim
Geschrieben am: 20.08.2008 11:00:33

Hallo Daniel,

genau deshalb brauche ich das "FALSCH", da bei "WAHR" der sverweis den Wert des nächst niedrigeren Suchkriteriums nimmt. Ich brauche aber eine exakte Übereinstimmung oder ein #NV.

Das mit A:F muss ich ausprobieren. Ich dachte, ich kann dem sverweis eine Art "last-row" übergeben.

Gruss
Joachim


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Daniel
Geschrieben am: 20.08.2008 11:16:44

Hi

du kannst ja mit der Abfrage:

=WENN(A1<>SVERWEIS(A1;B:C;1;1);#NV;SVERWEIS(A1;B:C;2;1))


prüfen, ob bei einem SVerweis mit der Spalte 1 als Rückgabespalte der Rückgabewert mit dem Suchwert übereinstimmt und falls nicht, einen Fehler oder eine sonstige Meldung ausgeben.
auch mit dieser Prüfung ist der SVerweis mit 4. Parameter = WAHR noch zig-fach schneller.
nur mal als vergleich:
bei 1000 Datenzeilen braucht der der SVerw(Falsch) je nach Fundort 1-1000 Zellvergleiche, um zum Ziel zu kommen (dh. im statistischen Mittel 500).
der SVerw(Wahr) kann aufgrund der Sortierung optimiert suchen und ist nach 10-11 Zellvergeichen am Ziel.
bei 2000 Datenzeilen ist das Verhältnis schon 1000 zu 12, bei 4000 dann 2000 zu 13.

(ist ja im Prinzip so wie bei der klassischen Suche im Telefonbuch: einen Namen findet man schnell, für eine Nummer muss man jede einzelne anschauen)

Gruß, Daniel


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Joachim
Geschrieben am: 20.08.2008 11:24:48

Hallo Daniel,

wie Du schon schreibst, die Daten müssten dann auch noch sortiert sein, und das sind sie oft nicht! Das würde noch einen Arbeitsschritt bedeuten, was bei einer anderen Art von Bearbeitung der Tabellen wieder eine neue Sortierung nach sich ziehen würde. Da bleibe ich lieber bei "FALSCH".

Gruss
Joachim


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: Daniel
Geschrieben am: 20.08.2008 11:38:55

das kannst du gerne machen, wenn es nicht allzuviele Daten sind oder du zuviel Zeit hast.
Gruß, Daniel


  

Betrifft: AW: sverweis mit dynamischen Suchbereich von: mpb
Geschrieben am: 20.08.2008 12:26:32

Hallo Joachim,

versuche mal folgende Matrixformel:

=SVERWEIS(A1;INDIREKT("Tabelle1!A1:F"&MAX((Tabelle1!A1:A65535<>"")*ZEILE(1:65535)));2;0)

Eingabe nicht mit ENTER, sondern mit STRG-SHIFT-ENTER abschließen.

Gruß
Martin


 

Beiträge aus den Excel-Beispielen zum Thema "sverweis mit dynamischen Suchbereich"