Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1144to1148
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Schneller 2 Spalten vergleichen als mit for next

Schneller 2 Spalten vergleichen als mit for next
alex
Hallo alle zusammen.
Ich wollte in meiner Tabelle diese Formel durch ein Makro ersetzen (vergleicht 2 spalten in tabelle 1 mit 2 spalten in tabelle 2). in der hoffnung das die performance meiner tabelle dadurch steigt.
(Die Formel steht in ca. 2000 Zeilen
=WENN(SUMMENPRODUKT((Mängelliste!$A$7:$A$398=B3)*(Mängelliste!$B$7:$B$398=D3)) -SUMMENPRODUKT((Mängelliste!$A$7:$A$398=B3)*(Mängelliste!$B$7:$B$398=D3)*(Mängelliste!$E$7:$E$398>0)) >0;"!Mängel!";"")
Im Internet habe ich ein makro gefunden welches ich meiner Tabelle angepasst habe. Es funktioniert auch, allerdings brauch es "ewig"(ca. 2 sec) bis es durchgelaufen ist.
Gibt es vielleicht eine schnellere Möglichkeit als eine For Next Schleife mein Vorhaben umzusetzen?
Private Sub Worksheet_Activate() 'Vergleicht die Spalte A des Tabellenblattes 1 mit der Spalte 2 des Tabelenblattes 2 _ und markiert die doppelten Werte in Hellgelb Dim i As Integer, j As Integer 'Festlegen der Variabeln Application.ScreenUpdating = False EndeA = Worksheets("Netz").Cells(Rows.Count, 2).End(xlUp).Row 'legt das Ende des ersten Bereiches fest EndeB = Worksheets("Mängelliste").Cells(Rows.Count, 1).End(xlUp).Row 'legt das Ende des zweiten Bereiches fest For i = 3 To EndeA 'beginnt bei Zelle 1 und hört bei der letzten ausgefüllten Zelle des Bereiches auf For j = 8 To EndeB If Sheets("Netz").Cells(i, 2) = Sheets("Mängelliste").Cells(j, 1) And Sheets("Netz").Cells(i, 4) _ = Sheets("Mängelliste").Cells(j, 2) And Sheets("Mängelliste").Cells(j, 5) = Empty Then Sheets("Netz").Cells(i, 6) = "!Mängel!" End If 'Fortsetzen der Schleife Next j Next i Application.ScreenUpdating = True End Sub
Private Sub Worksheet_Deactivate()
EndeA = Worksheets("Netz").Cells(Rows.Count, 2).End(xlUp).Row
Range("F3:F" & EndeA) = Empty
End Sub
Gruß alex
AW: Schneller 2 Spalten vergleichen als mit for next
18.03.2010 20:25:41
ransi
HAllo
Ich hab mal versucht anhand deiner Formel und anhand deines Makros herauszufinden was du möchtest.
Ich habs nicht geschafft.
Aber nur soviel:
Im Code ist jede Menge Optimierungspotenzial.
Kannst du mal ne Beispieldatei hochladen ?
ransi
AW: Schneller 2 Spalten vergleichen als mit for next
18.03.2010 20:39:08
alex
ja klar kein problem. Sorry dachte es währe verständlich.
https://www.herber.de/bbs/user/68641.xls
Is nicht schön aber selten
AW: Schneller 2 Spalten vergleichen als mit for next
18.03.2010 22:46:08
firmus
Hi Alex,
bisher hatte ich gute Ergebnisse erzielt mit:
Application.ScreenUpdating = False
Einfach als Zeile am Anfang in Dein Makro einfügen.
Kurz vor Ende des Makros das Gegenstück setzen: Application.ScreenUpdating = True
Gruß.
Firmus
Anzeige
AW: Schneller 2 Spalten vergleichen als mit for next
19.03.2010 00:26:02
alex
Hi Firmus...danke für deine antwort.
Den trick kenn ich leider schon. Die geschwindigkeit ist zwar ein wenig schneller aber es treten immer noch längere ladephasen auf die ich bei der Formel nicht so stark habe.
AW: Schneller 2 Spalten vergleichen als mit for next
18.03.2010 22:48:49
fcs
Hallo Alex,
um die Makro-Ausführung zu Beschleunigen solltest du während der Ausführung die Bildschirmaktualisierung deaktivieren und den Berechnungsstatus auf "Manuel".
Das Schachteln von 2 For-Next-Schleifen ist hier nicht ideal. Wenn die Anzahl der Zeilen in der Mängelliste nicht wesentlich größer wird, dann spielt das kaum eine Rolle.
Bei langen Listen arbeitet man besser mit der Suchfunktion und eine Do-Loop-Schleife für die innere Schleife.
Generell gilt: VBA ist meistens langsamer als vergleichbare Formeln. Erst bei umfangreichen Tabellen mit komplexeren Matrixformeln, die insbesondere die laufende Aktualisierung der Berechnungen ausbremsen macht es Sinn per VBA zu berechnen und feste Werte in Zellen zu schreiben.
Gruß
Franz
Private Sub Worksheet_Activate()
'Vergleicht die Spalte A des Tabellenblattes 1 mit der Spalte 2 des Tabelenblattes 2 _
und markiert die doppelten Werte in Hellgelb
Dim i As Integer, j As Integer, wksNetz As Worksheet, wksMaengel As Worksheet
Dim vSuchen, sZelle1 As String, Zelle As Range, BereichB As Range
Dim StatusCalc As Long
'Festlegen der Variabeln
Set wksNetz = Worksheets("Netz")
Set wksMaengel = Worksheets("Mängelliste")
Application.ScreenUpdating = False
StatusCalc = Application.Calculation
Application.Calculation = xlCalculationManual
With wksNetz
'legt das Ende des ersten Bereiches fest
EndeA = .Cells(.Rows.Count, 2).End(xlUp).Row
End With
With wksMaengel
'legt das Ende des zweiten Bereiches fest
endeB = .Cells(Rows.Count, 1).End(xlUp).Row
'Bereich mit den Objekten
Set BereichB = .Range(.Cells(8, 1), .Cells(endeB, 1))
End With
For i = 3 To EndeA
'beginnt bei Zelle 1 und hört bei der letzten ausgefüllten Zelle des Bereiches auf
vSuchen = wksNetz.Cells(i, 2).Value 'Objektname merken
'Objekt in Mängelliste suchen
Set Zelle = BereichB.Find(What:=vSuchen, LookIn:=xlValues, lookat:=xlWhole)
If Zelle Is Nothing Then
'do nothing
Else
sZelle1 = Zelle.Address '1. Fundstelle merken
Do
'Kriterien prüfen
If wksNetz.Cells(i, 4) = wksMaengel.Cells(Zelle.Row, 2) _
And wksMaengel.Cells(Zelle.Row, 5) = Empty Then
wksNetz.Cells(i, 6) = "!Mängel!"
Exit Do
End If
Set Zelle = BereichB.FindNext(After:=Zelle)
Loop Until Zelle.Address = sZelle1
End If
'Fortsetzen der Schleife
Next i
Application.ScreenUpdating = True
Application.Calculation = StatusCalc
End Sub
Private Sub Worksheet_Deactivate()
EndeA = Worksheets("Netz").Cells(Rows.Count, 2).End(xlUp).Row
Range("F3:F" & EndeA) = Empty
End Sub

Anzeige
AW: Schneller 2 Spalten vergleichen als mit for next
19.03.2010 00:53:31
alex
Hi Franz. Dein Makro is super. Der Unterschied ist kaum merkbar zur Formel.
Ich wollte meine Formel ersetzen weil ich mir davon einen kleinen Geschwindigkeitszuwachs beim speichern der Arbeitsmappe erhoffe. Die brauch im moment noch 8 sec. bis sie gespeichert ist. Das ist mir zu lang.
gruß und vielen dank
alex
AW: kleiner Optimierungsansatz
19.03.2010 01:43:52
Daniel
Hi
um mal etwas ins Detail zu gehen:
wenn man 2 Bedingungen hat, die mit AND verknüpft sind, ist es günstger, anstelle von :
IF Bedingung1 AND Bedingung 2 Then
End IF
2 If-Blöcke zu Schachteln:
IF Bedingung 1 Then
IF Bedingung 2 Then
End IF
End IF
das liegt daran, daß in der Verknüpfung mit AND diesowohl Bedingung1 als auch Bedingung2 immer geprüft werden.
bei den Verschachtelten IF-Blöcken, wird die Bedingung2 nur dann geprüft, wenn Bedingung1 erfüllt ist.
dadurch wird in der 2. Variante weniger geprüft als in der ersten, dadurch ist sie schneller.
wenn bekannt ist, mit welcher Häufigkeit die Bedingungen erfüllt sind, lässt sich durch geschickte anordnung die Geschwindigkeit nochmal optimieren.
Der Geschwindigkeitsgewinn durch diese Maßnahme ist sicherich nicht gross, aber in oft durchlaufenen Schleifen können auch kleine Optimiierungen eine spürbare Verbesserung bringen.
Gruß, Daniel
Anzeige
AW: „A bisserl was geht immer.“
19.03.2010 07:18:47
fcs
wie unser aller Fußballkaiser zu sagen pflegt.
Hallo Daniel,
wahrscheinlich kann man hier auch noch Zeit gewinnnen, wenn man die For-Next-Schleife über die hier deutlich kürzere Liste (hier Blatt Mängelliste) laufen läßt und in der langen Liste (Netz) sucht.
Gruß
Franz
AW: Schneller 2 Spalten vergleichen als mit for next
19.03.2010 08:51:43
Andreas
"GetMoreSpeed"
Sub GetMoreSpeed(bYesNo As Boolean)
'//Ein- bzw. Ausschalten von Excel-Aktionen
'//Bildschirmaktualisierung
Application.ScreenUpdating = Not (bYesNo)
'//Excel-Aktion-Methoden
Application.EnableEvents = Not (bYesNo)
'//Zellen- /Formelberechnung
Application.Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
End Sub

Bissel mehr Aufwand beim Programmieren, Daten der Tabellen in Arrays schreiben, anschliessend gleiche Logik (For Next) über Array laufen lassen.
Kann immer nur "ein" gefundes Suchkriterium existieren, kann man die Schleife mit EXIT FOR unterbrechen.
Für nur 2000 Datensätze ist das aber Pipifax.
Andi
Anzeige
=summenprodukt() vs. Hilfsspalte
19.03.2010 10:30:44
ransi
HAllo
=summenprodukt() ist ein Performancefrsser. Besonders wenn es im Rudel auftritt.
Da ist man mit einer Hilfsspalte und einfachen Formeln oft besser bedient.
Schau es dir mal an:
Netz

 BCDEFGH
2Objekt Typ Mängel MakroMängel Formel 
3Ahrensfelde Stw. BMA   Format: "!Mangel";;;
4Anhalter Bahnhof UST BMA    
5Anhalter Bahnhof UST EMA !Mängel!!Mangel 
6Bad Saarow BSTR BMA    
7Bad Saarow BSTR EMA    

Formeln der Tabelle
ZelleFormel
G3=ZÄHLENWENN(Mängelliste!$G$8:$G$47;Netz!B3&Netz!D3)
G4=ZÄHLENWENN(Mängelliste!$G$8:$G$47;Netz!B4&Netz!D4)
G5=ZÄHLENWENN(Mängelliste!$G$8:$G$47;Netz!B5&Netz!D5)
G6=ZÄHLENWENN(Mängelliste!$G$8:$G$47;Netz!B6&Netz!D6)
G7=ZÄHLENWENN(Mängelliste!$G$8:$G$47;Netz!B7&Netz!D7)


Mängelliste

 ABCDEFG
7ObjektTypFestgestellte MängelFestelldatumErledigt DatumNameHilfsspalte
8Beelitz Heilstätten BSTREMA 08.12.2009  Beelitz Heilstätten BSTREMA
9Gleisdreieck ÜbergabestationBMA 03.12.2009  Gleisdreieck ÜbergabestationBMA
10Olympiastadion BstrEMA 02.12.2009  Olympiastadion BstrEMA
11Ruhleben ESTWEMA 02.12.2009  Ruhleben ESTWEMA

Formeln der Tabelle
ZelleFormel
G8=A8&B8
G9=A9&B9
G10=A10&B10
G11=A11&B11


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
Anzeige
AW: =summenprodukt() vs. Hilfsspalte
19.03.2010 17:52:30
alex
Hallo alle zusammen,
danke nochmal für eure tipps. Ich werd mal versuchen einige davon noch ins makro einzubauen.
gruß alex

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige