Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1000to1004
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

sverweis mit dynamischen Suchbereich

sverweis mit dynamischen Suchbereich
20.08.2008 10:26:28
Joachim
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

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: sverweis mit dynamischen Suchbereich
20.08.2008 10:32:09
Rudi
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

AW: sverweis mit dynamischen Suchbereich
20.08.2008 10:49:00
Daniel
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

Anzeige
AW: sverweis mit dynamischen Suchbereich
20.08.2008 11:00:33
Joachim
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

AW: sverweis mit dynamischen Suchbereich
20.08.2008 11:16:44
Daniel
Hi
du kannst ja mit der Abfrage:

=WENN(A1SVERWEIS(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

Anzeige
AW: sverweis mit dynamischen Suchbereich
20.08.2008 11:24:48
Joachim
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

AW: sverweis mit dynamischen Suchbereich
20.08.2008 11:38:00
Daniel
das kannst du gerne machen, wenn es nicht allzuviele Daten sind oder du zuviel Zeit hast.
Gruß, Daniel

AW: sverweis mit dynamischen Suchbereich
20.08.2008 12:26:32
mpb
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
Anzeige

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige