Office: Effektive Suchfunktion 30000 Einträge

Hallo Zusammen,

ich möchte in meiner Tabelle alle Zeilen aufgelistet haben bei dem zwei Suchkriterien zutreffen. Die Zeilen möchte ich in meinem späteren Makro weiter verwenden.
Da die Tabelle noch deutlich wachsen wird möchte ich einen möglichst effektiven Suchalgorithmus haben der auch bei großen Datenmengen (bis ca. 30.000 Einträgen) möglichst schnell seine Ergebnisse ausgibt.

Was habe ich:
Eine Tabelle in der in Spalte A Länder und Spalte B Städte zu finden sind. In beiden Spalten können die Länder und Städte mehrfach vorkommen.

Was möchte ich:
Suche in Spalte A nach einem Land (z.B. Deutschland) in den Zeilen in denen "Deutschland" gefunden wurde suche bitte in Spalte B eine Stadt (z.B. Berlin) und gebe alle zutreffenden Zeilennummern in einer Variable aus.

Anmerkungen:
Die Tabelle ist bunt gemischt d.h. Länder und Städte sind nicht sortiert.

Ich hoffe ich habe alle relevanten Daten genannt die benötigt werden. Ansonsten bitte melden.

Danke!

Freundliche Grüße
Sven

:)

 

Hallo Sven,

wenn du in deinem Makro die Daten entsprehcend der jeweiligen Auswahl filterst, kannst du dann die gefilterten Zeilen direkt verarbeiten.

So wie du das machen willst, hört es sich für mich etwas umständlich an.

 

Hallo Ws-53

d.h. Excel filtert im Hintergrund die Tabelle (bei einer neuen Suche müsste der Filter zurück gesetzt werden) und ich frage die noch sichtbaren (gefilterten) Zeilen ab. Wie müsste das dann in VBA aussehen?

 

Hallo Sven,

ich habe gerade zu der Fragestellung Selbst-aktualisierender Filter eine Art Spezialfilter programmiert und mir die dazu notwendigen Informationen durch aufzeichnen mit dem Makrorecorder und einigen Google-Abfragen zusammengesucht. Und dann beim Testen solange die Fehler ausgemerzt, bis es, zumindest für mich, zufriedenstellend lief.

Die Verarbeitung in Abhängigkeit von sichtbar oder unsichtbar kannst du ja mit

f cells(x,y).entirerow.hidden=true then
.....
end if

steuern.

 

Ich habe mir deinen Vorschlag angeschaut. Ich glaub ich sitz heute schon zu lange vor der Scheibe. Ich steige trotz kleinem Code und Übersicht nicht durch.

Mein Gedanke ging zuerst in diese Richtung:

Code:

Werde aber vermutlich mit wachsender Tabelle lange Laufzeiten erreichen.
Meine Tabelle läuft im Hintergrund und bekommt der Endnutzer nicht zu sehen. Würde das mit deiner Lösung immer noch zeitliche Vorteile bringen?

Auf meiner Suche im web habe ich von verschiedenen Ansätzen gelesen aber bei der Umsetzung hapert es bei mir. Da wurde was von MATCH oder einer Array Lösung gesprochen. Aber an Beispielen mangelt es mir.

Grüße

 

Hallo,

ich gehe auch davon aus, dass wenn du mit einer For-Schleife alle Sätze der Tabelle abklapperst, dies länger dauert, als wenn due die Filterfunktion von Excel benutzt.

Das filtern kannst du doch mit dem Makrorecoder aufzeichen. Und dort wo du dann deine Filterkriterien siehst, musst du diese durch Variablen ersetzen, die du von deinen definierten Zelladressen übernimmst.

Aber evtl. macht es auch Sinn, wenn du einm,al komplett erläterst, was du machen willst und ob dies eine einmalige oder ständig wiederkehrende Anforderung ist.

So besteht zumindest die Chance, dass u.U. eine viel bessere Lösungsvariante dabei heraus kommt.

Ansonsten, einfach mal abschalten und morgen noch mal anschauen.

 

Hallo,

da ich weiß, dass du meinen Tipp sowieso ignorieren wirst, fasse ich mich kurz:

Autofilter auf die Spalten A und B --> Filtrat kopieren über .Copy Destination:=Tabelle2.Range("A1")

Nichts in Excel ist schneller als der Autofilter.

Sabina

 

Hallo,

bei so vielen Datensätze ist dei übliche Empfehlung zuerst alle Daten in ein Array zu übertragen (Ar = Range("A1").currentRegion) und dann mit For-Next Schleifen zu durchsuchen. Falls das immer noch nicht reicht, kann man weiter testen.

mfg

(zum Autofilter suche mal in: VBA for smarties: Autofilter)

 

Hallo Sven,

hier einmal einige Bemerkungen:

1. zu

Warum nicht? Das sortieren von 30.000 Datensätzen sollte in Excel nur Sekunden dauern. Es macht alle möglichen programmierten Filterprozesse über diese sortierten Spalten schneller.

aber

2. zu

Warum?
Ich nehme an, dass die Zeilennummer nicht das letzte Ziel der Auswertungen sind. Warum nicht gleich alle relevanten Daten der Liste in einem separaten Bereich ausgeben und dann die Auswertung über diesen Filterbereich durchführen. Das macht Programmierte Filter nicht unwesentlich langsamer, aber eventuell spätere Zugriffe auf die Daten schneller.
Das geht auch ganz ohne VBA mit dem Spezialfilter unter "Daten">"Sortieren und Filtern">"Erweitert".
Ich nehme an dass dies auch schneller ist als eine Programmierte Lösung. Heutige Rechner haben in der Regel mehrere Kerne. VBA nutz davon immer nur einen, da nicht parallelisierbar, Excel selbst kann aber mehrere gleichzeitig nutzen.

 

Guten Morgen euch allen und danke für eure Tipps und Hinweise!

Ok ich verstehe nun warum ich die Filter-Funktion dazu holen soll, werde ich auch machen.

@ Sabina
Ich ignoriere dich nicht. Ich hoffe wir beide können irgendwann mal wieder normal miteinander reden *frown.gif*

Das war einer der entscheidenen Sätze warum ich die Lösung so umsetzen werde.

Ich setze die Autofilter Funktion mal um wenn ich nicht weiter kommen sollte melde ich mich noch einmal.

Bis dahin!

 

Sehe ich das richtig wenn ich die Tabelle gefiltert habe muss ich dann dennoch mit einer For-Schleife arbeiten um die gefilterten Zeilen zu erkennen und zu verarbeiten?
Würde es dann nicht auf das gleiche rauskommen? Oder geht das dann schneller als ohne Filter?

 

Hallo,

nein ... komplett falsch verstanden.

Auf Tabelle2 ist der Autofilter ... kopiere einfach das Filtrat ... kann auch sein über ...

Worksheets("Tabelle2").UsedRange.Copy Destination:=Worksheets("Tabelle1").Range("A1")

Du nutzt dabei die Tatsache, dass Excel dabei nur sichtbare Daten kopiert.

Hab ich aber im Groben, oben schon geschrieben ... womit wir wieder beim Thema wären ... wieso probierst du es nicht einfach selbst aus ?
So unverständlich drücke ich mich doch auch nicht aus, oder doch ?

 

Als alternativer Gedanke:
Listen (intelligente Tabellen) kann man mit Datenbankmethoden verarbeiten. Die folgende SQL-Anweisung als Ansatz führt die gewünschte mehrstufige Filterung in einem Zug aus.
Code:

SQL kann zwar keine Zeilennummern liefern. Man kann aber gleich über die gefilterte Menge mit der folgenden (unbekannten) Verarbeitung fortsetzen, wenn man sie schon im Zugriff hat.

Umsetzung dann mit ADO und Excel oder etwas neuer und mehr excellike mit Power Query.

 

30.000 Datensätze sind keine große Datenmenge: Fenneks Tipp aus Beitrag #8 stellt bereits eine sehr performante Lösung dar, wurde aber (warum auch immer) nicht beachtet. Das spart auch die Notwendigkeit, einen Autofilter zu setzen oder den Spezialfilter zu nutzen (beides rasante Möglichkeiten, hier aber m. E. nicht notwendig). 30.000 Datensätze in einem Array abzuklappern dauert auch auf meinem altersschwachen Rechner gerade mal 5% einer Sekunde.

Da das eigentliche Ziel trotz Nachfrage verschwiegen wird, kann man auch nicht wirklich zielorientiert helfen. Möglicherweise ist auch eine Pivot-Tabelle ein geeignetes Mittel, wenn es darum geht, die Suche mit vielen Kriterienpaarungen zu wiederholen. Die Verwendung eines Dictionarys als Erweiterung von Fenneks Tipp käme hier auch infrage.

 

Hallo Earlfred,

wenn ich in Tabellen, die auch Formeln enthalten, dann wird bei mir, wenn ich den Filter nutze, jedesmal eine Neuberechnung angestossen, selbst dann wenn ich nicht mit Teilergebnis arbeite. Bei 10.000 Zeilen und Summewenn, oder Index/Vergleich kann dann selbst bei beinem sehr schnellen PC es schon 10 bis 15 Sekunden dauern, bis die Sanduhr weg ist.