Hallo zusammen,
ich habe 2 verschiedene Excel-Dateien mit jeweils eine Tabelle. Ich habe
einen Makro gebaut der mir die Werte aus der erste Spalte einer Datei
mit der ersten Spalte zweiter Datei vergleicht und bei der
Übereinstimmung, farbig markiert. Da die Tabellen bis zu 10k Werte haben
können hängt der Excel sich ab und zu auf :)
Hier ist der Code-Ausschnitt:
Da ich nicht so viel Erfahrung habe mit Makroerstellung habe, habe ich
bestimmt den aufwändigsten Weg genommen. Kann mir bitte jmd. Helfen bei
der Optimierung des Codes??
Danke im Voraus
Gruß
Moin,
du könntest ja fürs Erste mal die innere Schleife weg lassen (for
z=3..). Benutzt du eh nicht und das spart schon mal eine Menge
durchläufe.
Grüße,
nicht"Gast" schrieb:> du könntest ja fürs Erste mal die innere Schleife weg lassen (for> z=3..). Benutzt du eh nicht und das spart schon mal eine Menge> durchläufe.
Danke für die schnelle Antwort.
Die innere Schleife benötige ich, da ich die jede einzelne Zeile der
Datei 2 mit der jeder Zeile in der Tabelle der Datei 1 vergleiche. Da
die Werte können unterschiedlich angeordnet werden.
Excel Neuling schrieb:> Die innere Schleife benötige ich, da ich die jede einzelne Zeile der> Datei 2 mit der jeder Zeile in der Tabelle der Datei 1 vergleiche. Da> die Werte können unterschiedlich angeordnet werden.
Wie in der ersten Antwort schon geschrieben, benutzt Du z nirgends, also
überflüssiger Schwachsinn.
Makro schrieb:> Excel Neuling schrieb:>> Die innere Schleife benötige ich, da ich die jede einzelne Zeile der>> Datei 2 mit der jeder Zeile in der Tabelle der Datei 1 vergleiche. Da>> die Werte können unterschiedlich angeordnet werden.>> Wie in der ersten Antwort schon geschrieben, benutzt Du z nirgends, also> überflüssiger Schwachsinn.
ups sorry da ist ein Fehler unterlaufen
Weil beide Schleifen über alle Zeilen laufen sind viele Vergleiche
doppelt.
Es müsste reichen wenn z bei i anfängt, dann wird nur noch bis zum Ende
der Tabelle verglichen.
Was das soll ist mir noch nicht ganz klar, ich nehme an dass ein
Großteil der Werte identisch ist und Du nur ein paar Ausreisser farblich
hervorheben willst... da würde eine einfachere Schleife reichen, immer
nur zwei Nachbarwerte vergleichen und damit einer Schleife die Tabelle
zeilenweise durchlaufen.
Tcf K. schrieb:> Weil beide Schleifen über alle Zeilen laufen sind viele Vergleiche> doppelt.> Es müsste reichen wenn z bei i anfängt, dann wird nur noch bis zum Ende> der Tabelle verglichen.
Meiner Meinung nach ist keins der Vergleiche doppelt.
Es wird eine Zeile aus Datei 2 genommen und mit der Tabelle in der Datei
1 verglichen . Dann die zweite Zeile aus Datei 2 usw.
Tcf K. schrieb:> Was das soll ist mir noch nicht ganz klar, ich nehme an dass ein> Großteil der Werte identisch ist und Du nur ein paar Ausreisser farblich> hervorheben willst... da würde eine einfachere Schleife reichen, immer> nur zwei Nachbarwerte vergleichen und damit einer Schleife die Tabelle> zeilenweise durchlaufen.
Die Anordnung der Daten ist unbestimmt. D.h. Die gleichen Werte haben
nicht die gleiche Reihenfolge und mich interessiert nur ob die Werte in
der Tabelle beinhaltet sind oder nicht...
Arc N. schrieb:> 10000 Zeilen mal 10000 Zeilen = 100 Mio Vergleiche... das dauert.> Schneller wird's nur wenn die Daten sortiert sind und dann mit> Binärsuche gesucht wird. Macht z.B. die Match/Vergleich-Funktion von> Excel
Danke genau das Problem habe ich.
Gibt es bei VBA keinen Typ Set (=Menge), Dictionary o.ä., in das man die
Werte aus der Vergleichsdatei komplett stecken kann und der (intern mit
Hash-Werten und schnell) nach "ist drin:ja/nein" suchen kann?
Prüft der existierende Code nicht effektiv nur, ob der Wert in der
letzten Zeile von Datei 2 steht? Wenn ja, soll das so?
Excel Neuling schrieb:> https://support.office.com/de-at/article/VERGLEICH...>> Soweit ich verstanden habe, sucht er nur einen Wert aus der Matrix raus.
Ich habe keine Ahnung von Excel, aber offenbar wird bei 1 oder -1 als
"Vergleichstyp" eine binäre Suche durchgeführt, keine lineare.
http://www.tushar-mehta.com/publish_train/xl_vba_cases/match-exact-vs-binary.htm> Bei mir kann es sein dass es mehrere sind die gleich heißen.
Meinst du damit, dass es in Tabelle 2 mehrere Zeilen geben kann, in
denen die erste Zelle (*) jeweils den gleichen Inhalt hat?
(*) wegen
Workbooks(2).Sheets(1).Cells(z, 1).Value =
Workbooks(1).Worksheets(1).Cells(i, 1).Value
Bei einer binären Suche landet man ja leider im Allgemeinen auf einem
zufälligen Element, dass das Suchkriterium erfüllt.
D.h., zusätzlich zur von Arc Net erwähnten Vorgehensweise könntest du
bei einem Treffer in Tabelle 2 wieder so weit nach oben gehen, bis du
die erste Änderung findest und alles zwischen dieser Zeile und der Zeile
mit der nächsten Änderung markieren. Falls auch in Tabelle 1
Mehrfachwerte vorkommen können, könnte man sofort abbrechen, wenn die
erste Fundstelle in Tabelle 2 bereits markiert wurde.
Insgesamt dürfte das die einfachste Möglichkeit mit akzeptabler
Performance sein.
> Insgesamt dürfte das die einfachste Möglichkeit mit akzeptabler> Performance sein.
Soll natürlich heißen, wenn man es "zu Fuß" macht. Wie gesagt, siehe
> Ich habe keine Ahnung von Excel
Fräulein Eysenbarth schrieb:> Meinst du damit, dass es in Tabelle 2 mehrere Zeilen geben kann, in> denen die erste Zelle (*) jeweils den gleichen Inhalt hat?
richtig!
Fräulein Eysenbarth schrieb:> Bei einer binären Suche landet man ja leider im Allgemeinen auf einem> zufälligen Element, dass das Suchkriterium erfüllt.>> D.h., zusätzlich zur von Arc Net erwähnten Vorgehensweise könntest du> bei einem Treffer in Tabelle 2 wieder so weit nach oben gehen, bis du> die erste Änderung findest und alles zwischen dieser Zeile und der Zeile> mit der nächsten Änderung markieren. Falls auch in Tabelle 1> Mehrfachwerte vorkommen können, könnte man sofort abbrechen, wenn die> erste Fundstelle in Tabelle 2 bereits markiert wurde.> Insgesamt dürfte das die einfachste Möglichkeit mit akzeptabler> Performance sein.
Danke, das könnte die Sache beschleunigen. Allerdings das Problem noch
nicht beheben... ich werde aber trotzdem versuchen dies umzusetzen.
Ich vermute aber das in Excel müssen extra Funktionen geben die es
Leistungsarm machen können...
Excel Neuling schrieb:> Danke, das könnte die Sache beschleunigen. Allerdings das Problem noch> nicht beheben...
Könnte schon klappen, denn was du vorher versucht hast, ist quadratisch,
das andere dagegen logarithmisch-linear. Das ist bei vielen Elementen
schon ein großer Unterschied.
Wenn beide Tabellen je 10.000 Zeilen haben:
Vorher m * n = 100.000.000 "Durchgänge", mit der neuen Vorgehensweise
m * log2 n = 140.000, also rund 700x weniger (dazu kommt zwar das
Abklappern der Zeilen in Tabelle 2 mit gleichem Wert in Zelle 1 nach
jedem Treffer, aber dafür fallen diese Zeilen dann ja quasi weg).
> Ich vermute aber das in Excel müssen extra Funktionen geben die es> Leistungsarm machen können...
Könnte schon sein, vielleicht meldet sich ja noch ein Excel-Kenner.