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

sverweis mit dynamischen Suchbereich

Forumthread: 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

Anzeige

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

Anzeige
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

Anzeige
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

Anzeige
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
;

Forumthreads zu verwandten Themen

Dynamische Matrix SVerweis
traktorjoe

Hallo zusammen,
ich habe folgendes Problem: Tabellenblatt 1 enthält diverse Information zu mehreren Personen. Auf Tabellenblatt 2 möchte ich diese Infos dann mittels SVerweis aggregieren, aber ich bekomme es einfach nicht hin, die Matrixangabe dynamisch zu gestalten.
Das ganze sollte d...
16.05.2014 15:24:30
Thread-ID: 1361956
3 Beiträge
Bei SVerweis Matrix zu groß .. Dynamisch möglich?
sabrina

Hallo Jungs,
danke für eure Hilfe.
Mein Beitrag ist leider aud dem Forum weg, deswegen eröffne ich einen neuen Beitrag
Ich habe jetzt herausgefunden wann der Fehler beim Speichern auftritt.
Der Bereich der Matrix des SVerweis umfasst mehr als 24000 Zeilen.
Wenn ich den Berei...
08.05.2015 11:15:42
Thread-ID: 1425353
5 Beiträge
SVERWEIS - Matrix dynamisch
Inga

Hallo Forum,
ich hadere gerade mit einem Problem bei einer SVERWEIS-Funktion. Der Bereich auf den sich die Matrix beziehen soll, liegt in einem anderen Excelblatt und hat zwar eine feste Anzahl an Spalten, jedoch eine ständig wechselnde Anzahl an Zeilen.
Es wäre schön, wenn mir jemand z...
01.01.1970 00:00:00
Thread-ID: 401027
3 Beiträge
SVERWEIS - 4. Argument / wahr/falsch
Sabine

hallo!
ich hab mal wieder ein sverweis problem...
mein sverweis soll einen text hinzufügen, aber irgendwie klappt das nicht so ganz und ich bekomme teilweise richtige ergebnisse und teilweise #nv. der text ist in beiden zellen als standard formatiert und der text in der sverweistabelle...
01.08.2007 15:07:00
Thread-ID: 892692
11 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Dynamischer SVERWEIS: So machst du deinen Suchbereich flexibel

Schritt-für-Schritt-Anleitung

  1. Dynamischen Bereich definieren: Um den Suchbereich für den SVERWEIS dynamisch zu gestalten, kannst du den Befehl INDIREKT nutzen. Damit wird der Suchbereich automatisch angepasst, wenn Daten hinzugefügt oder entfernt werden.

    Ein Beispiel für eine Matrixformel sieht so aus:

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

    Diese Formel sucht in der Spalte A nach dem Wert in A1 und gibt den Rückgabewert aus der 2. Spalte zurück. Stelle sicher, dass du die Eingabe mit STRG+SHIFT+ENTER abschließt.

  2. Eingabeparameter festlegen: Achte darauf, dass du den vierten Parameter des SVERWEIS auf 0 (FALSCH) setzt, wenn du eine exakte Übereinstimmung benötigst.

Häufige Fehler und Lösungen

  • Problem: Der SVERWEIS gibt #NV zurück.

    • Lösung: Überprüfe, ob der Suchwert in der ersten Spalte der Suchmatrix vorhanden ist. Falls nicht, wird ein Fehler angezeigt.

  • Problem: Die Formel ist zu langsam.

    • Lösung: Vermeide die Verwendung von großen festen Bereichen wie A:F. Stattdessen kannst du die Matrix dynamisch gestalten, um nur die tatsächlich verwendeten Zeilen zu berücksichtigen.

Alternative Methoden

Eine alternative Methode ist die Verwendung von BEREICH.VERSCHIEBEN, um einen dynamischen Bereich zu erstellen. Beispiel:

=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;0;ANZAHL(Tabelle1!$A:$A)-1;6)

Dieser Befehl erstellt einen Bereich, der sich entsprechend der Anzahl der Einträge in Spalte A anpasst.

Praktische Beispiele

  1. Einfache Anwendung: Angenommen, du hast in Tabelle1 die Daten in den Zellen A2 bis F30. Du kannst die obigen Formeln nutzen, um den SVERWEIS dynamisch zu machen.

  2. SVERWEIS mit dynamischer Matrix: Wenn du suchst, z.B. in Zelle A1, und die Daten in Tabelle1 in Spalte A bis F stehen, kannst du die Formel mit INDIREKT verwenden, um immer die aktuelle Datenmenge zu berücksichtigen.

Tipps für Profis

  • Verwende Namensbereiche: Definiere benannte Bereiche für deine Daten. So hast du eine bessere Übersicht und kannst die Formeln einfacher gestalten.

  • Sortierung der Daten: Wenn du den vierten Parameter auf WAHR setzt, achte darauf, dass die Daten in der Suchmatrix aufsteigend sortiert sind. Das verbessert die Geschwindigkeit des SVERWEIS erheblich.

FAQ: Häufige Fragen

1. Wie kann ich den SVERWEIS schneller machen?
Setze den vierten Parameter auf WAHR und stelle sicher, dass die Daten in der Suchmatrix sortiert sind.

2. Was ist der Unterschied zwischen FALSCH und WAHR im SVERWEIS?
FALSCH sucht nach einer exakten Übereinstimmung, während WAHR den nächst niedrigeren Wert zurückgibt, was jedoch eine sortierte Datenbasis erfordert.

3. Kann ich ganze Spalten als Suchmatrix verwenden?
Ja, du kannst ganze Spalten (z.B. A:F) verwenden. Excel optimiert die Suche und verwendet nur den tatsächlich genutzten Bereich.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige