MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 28.09.2018, 13:56   #1
iSven
MOF User
MOF User
Standard VBA - 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

__________________

Lig jetzt weg.
iSven ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 14:01   #2
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 14:18   #3
iSven
Threadstarter Threadstarter
MOF User
MOF User
Standard

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?

__________________

Lig jetzt weg.
iSven ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 15:46   #4
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 17:16   #5
iSven
Threadstarter Threadstarter
MOF User
MOF User
Standard

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:

Sub neu()

    Dim intAktuelleZeile    As Integer
    Dim strLand             As String
    Dim strStadt            As String

    strLand = "Deutschland"
    strStadt = "Berlin"

    With Tabelle1
        For intAktuelleZeile = 3 To .Cells(Rows.Count, 1).End(xlUp).Row
            If .Cells(intAktuelleZeile, 1).Value = strLand Then
                If .Cells(intAktuelleZeile, 2).Value = strStadt Then
                    '...
                End If
            End If
        Next intAktuelleZeile
    End With

End Sub
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

__________________

Lig jetzt weg.
iSven ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 17:31   #6
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 17:39   #7
Flotter Feger
MOF User
MOF User
Standard

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
Flotter Feger ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 17:44   #8
Fennek11
MOF Profi
MOF Profi
Standard

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: http://snb-vba.eu/VBA_Autofilter_en.html)
Fennek11 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 28.09.2018, 18:04   #9
Oge
MOF Profi
MOF Profi
Standard

Hallo Sven,

hier einmal einige Bemerkungen:

1. zu

Zitat:

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

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

Zitat:

...ich möchte in meiner Tabelle alle Zeilen aufgelistet haben bei dem zwei Suchkriterien zutreffen ...

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.

__________________

helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.

Geändert von Oge (28.09.2018 um 18:07 Uhr).
Oge ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.10.2018, 09:04   #10
iSven
Threadstarter Threadstarter
MOF User
MOF User
Standard

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

Zitat:

Nichts in Excel ist schneller als der Autofilter.

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!

__________________

Lig jetzt weg.
iSven ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.10.2018, 10:47   #11
iSven
Threadstarter Threadstarter
MOF User
MOF User
Standard

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?

__________________

Lig jetzt weg.
iSven ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.10.2018, 14:14   #12
Flotter Feger
MOF User
MOF User
Standard

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 ?

__________________

VG Sabina

Wer auch weiter Hilfe erwartet, sollte sich nicht zu schade sein, ein kurzes Feedback zu geben.
War der Post für dich hilfreich, darf er von dir auch gerne positiv bewertet werden.
bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Flotter Feger ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 04.10.2018, 09:59   #13
ebs17
MOF Guru
MOF Guru
Standard

Zitat:

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.

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:

SELECT 
   * 
FROM 
   (
      SELECT 
         * 
      FROM 
         TabelleX 
      WHERE 
         Land = 'Deutschland' 
   ) AS X 
WHERE 
   Ort = 'Berlin'
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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (04.10.2018 um 10:06 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 04.10.2018, 10:17   #14
EarlFred
MOF Guru
MOF Guru
Standard

Zitat:

der auch bei großen Datenmengen (bis ca. 30.000 Einträgen)

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.

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 6 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,044% per 26.07.2018) - eine tolle Geste!

Geändert von EarlFred (04.10.2018 um 10:21 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 04.10.2018, 13:13   #15
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 08:26 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.