Excel Vergleich zweier Dateien

[Excel Neuling (Gast)]

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:

Set Datei2 = Workbooks.Open("C:\Users\Datei2.xls")

Zeilen = Datei2.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row


For i = 3 To Zeilen Step 1

z = 3

For z = 3 To Zeilen Step 1

If Workbooks(2).Sheets(1).Cells(i, 1).Value = Workbooks(1).Worksheets(1).Cells(i, 1).Value Then
Workbooks(2).Sheets(1).Cells(i, 1).Interior.Color = vbYellow

Else
Workbooks(2).Sheets(1).Cells(i, 1).Interior.Color = vbRed
End If

Next z

Next i

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ß

[nicht"Gast" (Gast)]

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,

[Excel Neuling (Gast)]

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.

[Makro (Gast)]

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.

[Excel Neuling (Gast)]

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

Set Datei2 = Workbooks.Open("C:\Users\Datei2.xls")

Zeilen = Datei2.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row


For i = 3 To Zeilen Step 1

z = 3

For z = 3 To Zeilen Step 1

If Workbooks(2).Sheets(1).Cells(z, 1).Value = Workbooks(1).Worksheets(1).Cells(i, 1).Value Then
Workbooks(2).Sheets(1).Cells(z, 1).Interior.Color = vbYellow

Else
Workbooks(2).Sheets(1).Cells(z, 1).Interior.Color = vbRed
End If

Next z

Next i

[Tcf K. (tcfkao)]

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.

[Arc N. (arc)]

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
https://support.office.com/de-at/article/VERGLEICH-Funktion-e8dffd45-c762-47d6-bf89-533f4a37673a

[Excel Neuling (Gast)]

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.

[Tom (Gast)]

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 (Gast)]

Arc N. schrieb:
> Macht z.B. die Match/Vergleich-Funktion von
> Excel
> 
https://support.office.com/de-at/article/VERGLEICH-Funktion-e8dffd45-c762-47d6-bf89-533f4a37673a

Soweit ich verstanden habe, sucht er nur einen Wert aus der Matrix raus.
Bei mir kann es sein dass es mehrere sind die gleich heißen.

[Fräulein Eysenbarth (Gast)]

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.

[Fräulein Eysenbarth (Gast)]

> 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

[Excel Neuling (Gast)]

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...

[nicht"Gast" (Gast)]

Schau Dir mal find() an. Damit musst du nur noch über eine Datenreihe 
mit for iterieren. Das sollte wesentlich schneller gehen.

[nicht"Gast" (Gast)]

Ach, noch anders ^^

schau mal hier:

http://www.excel-easy.com/examples/compare-two-lists.html

den Vorgang kannst du auch mit dem Makro Recorder in VBA gießen und dann 
automatisiert anwenden.

[Fräulein Eysenbarth (Gast)]

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.