Prüfen ob Monat und Jahr gleich ist

[Kuwer]

Hallo,

hier ein Testcode aller bisherigen Beispiele und meins dazu (aber mal mit 1 Million Zeilen):

Code:

Sub FillIt()
  Cells.Delete
  With Range("A1:B1000000")
    .NumberFormat = "m/d/yyyy"
    .Columns(1).Formula = "=DATE(2000,1,RANDBETWEEN(1,20000))"
    .Columns(2).Formula = "=A1+15"
    .Copy
    .PasteSpecial xlPasteValues
  End With
  With Application
    .Goto Cells(1)
    .CutCopyMode = False
  End With
End Sub

Sub TestIt()
  Dim Start As Double, Ziel As Double, i As Long, k As Long
  Dim arr As Variant
  arr = Cells(1).CurrentRegion.Value
 
  k = 0
  Start = Timer
  For i = 1 To UBound(arr)
    If Month(arr(i, 1)) = Month(arr(i, 2)) Then
      If Year(arr(i, 1)) = Year(arr(i, 2)) Then k = k + 1
    End If
  Next
  Ziel = Timer
  Debug.Print "Monat dann Jahr-Vergleich:         " & Ziel - Start & vbTab & "Übereinstimmungen: " & k
 
  k = 0
  Start = Timer
  For i = 1 To UBound(arr)
    If Year(arr(i, 1)) = Year(arr(i, 2)) Then
      If Month(arr(i, 1)) = Month(arr(i, 2)) Then k = k + 1
    End If
  Next
  Ziel = Timer
  Debug.Print "Jahr dann Monat-Vergleich:         " & Ziel - Start & vbTab & "Übereinstimmungen: " & k
 
  k = 0
  Start = Timer
  For i = 1 To UBound(arr)
    If Month(arr(i, 1)) = Month(arr(i, 2)) And _
      Year(arr(i, 1)) = Year(arr(i, 2)) Then k = k + 1
  Next
  Ziel = Timer
  Debug.Print "Monat und Jahr-Vergleich:          " & Ziel - Start & vbTab & "Übereinstimmungen: " & k
 
  k = 0
  Start = Timer
  For i = 1 To UBound(arr)
    If Format(arr(i, 1), "yyyymm") = Format(arr(i, 2), "yyyymm") Then k = k + 1
  Next
  Ziel = Timer
  Debug.Print "Format-Vergleich:                  " & Ziel - Start & vbTab & "Übereinstimmungen: " & k
  ' Variant-Array mit Strings
  k = 0
  Start = Timer
  For i = 1 To UBound(arr)
    arr(i, 1) = FormatDateTime(arr(i, 1), vbShortDate)
    arr(i, 2) = FormatDateTime(arr(i, 2), vbShortDate)
  Next

  For i = 1 To UBound(arr)
    If Mid$(arr(i, 1), 4) = Mid$(arr(i, 2), 4) Then k = k + 1
  Next
  Ziel = Timer
  Debug.Print "String-Vergleich (Variant/String): " & Ziel - Start & vbTab & "Übereinstimmungen: " & k

  ' String-Array
  Dim astr() As String
  k = 0
  Start = Timer
  ReDim astr(1 To UBound(arr), 1 To 2)

  For i = 1 To UBound(arr)
    astr(i, 1) = arr(i, 1)
    astr(i, 2) = arr(i, 2)
  Next

  For i = 1 To UBound(arr)
    If Mid$(astr(i, 1), 4) = Mid$(astr(i, 2), 4) Then k = k + 1
  Next
  Ziel = Timer
  Debug.Print "String-Vergleich (String):         " & Ziel - Start & vbTab & "Übereinstimmungen: " & k

End Sub

Gruß, Uwe

[snb]

Code:

for j=0 to ubound(sn)
  n=n-(right(sn(j,1),7)=right(sn(j,2),7))
Next

Msgbox n  

[knobbi38]

[snb]

So besser ?

Code:

Sub M_snb()
  Randomize
  ReDim sn(10 ^ 6)
  t = Timer
 
  For j = 0 To UBound(sn)
    sn(j) = DateSerial(2000, 1, 20000 * Rnd())
    n = n - (Format(sn(j), "mmyyyy") = Format(sn(j) + 15, "mmyyyy"))
  Next
 
  MsgBox n & vbLf & Timer - t
End Sub

[snb]

Hier die schnellste Methode

Code:

Sub M_snb()
  Randomize
  ReDim sn(10 ^ 6)
  t = Timer
 
  For j = 0 To UBound(sn)
    sn(j) = DateSerial(2000, 1, 20000 * Rnd())
    n = n - (Right(sn(j), 7) = Right(sn(j) + 15, 7))
  Next
 
  MsgBox n & vbLf & Timer - t
End Sub

[knobbi38]

@snb:

Es geht doch nicht darum, den Testalgorithmus zu optimieren, sondern die verschiedenen Methoden für den  Vergleich von Monat/Jahr eines Datums gegenüberzustellen. Der einzige Unterschied zu den anderen Methoden ist bei dir nur der Hinweis auf die Funktion Right() anstatt z.B. Mid(). Wobei hier nicht noch die Variant-Versionen und nicht die String-Versionen verwendet werden.  

Knobbi38

[Andreas Killer]

Es geht doch nicht darum, den Testalgorithmus zu optimieren, sondern die verschiedenen Methoden für den  Vergleich von Monat/Jahr eines Datums gegenüberzustellen. 

Hey, ist ja doch noch jemand bei der Sache.

Ich denk mal laut, wenn's erlaubt ist.

Ein Datum in Excel ist eine Zahl, sagen wir mal wir haben 45872 und 45765. 
Jetzt soll geprüft werden ob Monat und Jahr von diesen beiden Zahlen gleich sind oder nicht.

Wir haben dafür 2 Funktionen, Month und Year. Wie rechnen die aus dieser Zahl den Monat/das Jahr? Vereinfacht gesagt mit einer Formel. 

Sowas ähnliches wie Ganzzahl(48872/365.25+1900) und da kommt dann 2025 raus, das Jahr. Für den Monat nochmal das gleiche modulo 1 um den Rest zu bekommen, mal 12. Wer es genau wissen will... ich meine bei Wikipedia kann man das nachkucken, tut hier nix zur Sache. Ich sage das nur um zu verstehen was da im Hintergrund läuft.

Wie geht nun der schnellste Vergleich?

Die Wahrscheinlichkeit das bei einem Vergleich von 2 Daten das Jahr identisch ist, ist logischer Weise 12x höher, also können wir den Vergleich schon gleich abbrechen wenn der Monat nicht gleich ist.
Anstatt AND zu verwenden und zudem das Jahr auch noch zu prüfen. (VBA optimiert den Code leider nicht, im Gegensatz zu anderen Compilern).

Also:

Ergebnis: Ungleich
Monat von beiden Daten ausrechnen
If Monat=Monat then
  Jahr von beiden Daten ausrechnen
  if Jahr=Jahr then
    Ergebnis: Gleich
  endif
endif

Maximal 2 Zahlen vergleichen, schneller wird es nicht.

Wie sieht es nun mit einem Vergleich mit Strings aus?

Wenn ich prüfen will ob "082025" und "042025" gleich ist, dann muss der Compiler einen Code generieren der max. je 6 Zeichen vergleichen muss (Da ein Zeichen auch nur eine Zahl ist, können wir das in etwa gleich setzen, wobei in den Details man sich fragen darf welchen Datentyp die Zahlen haben => Anzahl der Bytes und wie der Assembler-Code den Vergleich in der CPU dann macht). 

Für unseren Fall müssen also immer 2 Vergleiche stattfinden bevor es <> wird. Jetzt kann es theoretisch sein ein Monat "082025" ist, der 2te aber "122025", in diesem Fall wäre nur ein Vergleich nötig, das wäre dann genauso schnell wie der Code nur mit Zahlen.

ABER, und das ist ein ganz dickes fettes ABER, um "082025" zu bekommen muss AUCH zuerst aus der Zahl 45872 der Monat UND das Jahr berechnet werden und dann zusätzlich müssen diese beiden Zahl formatgerecht nach "MMJJJJ" in einen Text konvertiert werden.

Wie soll das dann am Ende schneller sein?

Andreas.

[snb]

Code:

Sub M_snb()
  Randomize
  ReDim sn(10 ^ 6)

  For j = 0 To UBound(sn)
    sn(j) = DateSerial(2000, 1, 20000 * Rnd())
  Next
 
  t1 = Timer
  For j = 0 To UBound(sn)
    m = m - (Right(sn(j), 7) = Right(sn(j) + 15, 7))
  Next
  t2 = Timer
   
  For j = 0 To UBound(sn)
      n = n - (Format(sn(j), "mmyyyy") = Format(sn(j) + 15, "mmyyyy"))
  Next
  t3 = Timer
   
  For j = 0 To UBound(sn)
      p = p - (Right(FormatDateTime(sn(j), 2), 7) = Right(FormatDateTime(sn(j) + 15, 2), 7))
  Next
  t4 = Timer
   
  For j = 0 To UBound(sn)
      q = q - (Month(sn(j)) & Year(sn(j)) = Month(sn(j) + 15) & Year(sn(j) + 15))
  Next
  t5 = Timer

  MsgBox n & vbLf & "right(date)" & vbTab & t2 - t1 & vbLf & "format(date)" & vbTab & t3 - t2 & vbLf & "formatdatetime(date)" & vbTab & t4 - t3 & vbLf & "Month(date) & year(date)" & vbTab & t5 - t4
End Sub

[schauan]

Hallöchen,

ich habe bei mir schon sehr seltsame Ergebnisse. Ich habe von Uwe mal zwei Vergleiche rausgenommen, weil ich schauen wollte, wie die einzeln und zusammen laufen. Rausgekommen ist das:

String-Vergleich (Variant/String): 0,7265625 Übereinstimmungen: 507212
String-Vergleich (String): 0,390625 Übereinstimmungen: 507212
String-Vergleich (String): 0,6015625 Übereinstimmungen: 507212
String-Vergleich (String): 0,5703125 Übereinstimmungen: 507212
String-Vergleich (String): 0,5859375 Übereinstimmungen: 507212
String-Vergleich (String): 0,6328125 Übereinstimmungen: 507212
String-Vergleich (String): 0,5859375 Übereinstimmungen: 507212
String-Vergleich (Variant/String): 0,71875 Übereinstimmungen: 507212
String-Vergleich (String): 0,390625 Übereinstimmungen: 507212

Wenn ich den (Variant/String) Vergleich vor dem (String) laufen lasse, ist (String) schneller als wenn ich nur (String) laufen lasse

[knobbi38]

@Andreas:

Deine Überlegungen, erst den Monat und dann das Jahr zu überprüfen, ist schon nachvollziehbar, aber man müßte jetzt schauen, was der VBA Kompiler jetzt wirklich daraus macht. U.U. spielt das gar keine Rolle, wenn der auszuführende Code im CPU-Cache abgearbeitet werden kann. 

Was deine Überlegungen zum Stringvergleich betrifft, führt in die Irre. CPUs können sehr schnell Bereiche im Memory vergleichen, indem einfach Register  inkrementiert werden und ein "Comparer" die Werte vergleicht. Sind die Werte auf Wortgrenzen ausgerichtet, also auf Word- oder sogar Longword-Adressen, kann im günstigsten Fall sogar mit einem Zugriff ein 64-Bit-Wert in einem Rutsch geprüft werden – alles reine CPU-Hardware.

Dasselbe trifft natürlich auch auf Zahlen zu, solange es keine Fließkommazahlen sind. Diese werden von einer anderen, komplexeren Einheit verarbeitet, was i.d.R. mehr CPU Zeit kostet.

Das eigentliche Problem sind die verschieden Datentypen in VBA. Daten werden intern als Double gespeichert, sind also Fließkommazahlen, dessen Verarbeitung mehr Zeit kostet, als die Verarbeitung reiner Long- oder Integer-Werte. 

Deshalb kann es auch sein, daß mit VBA die Verwendung von reinen Stringvergleichen im Speicher durchaus schneller sein kann, als die Vergleiche von Fließkommazahlen, selbst wenn vorher eine Typkonvertierung von Date (Double) nach String erfolgen muß.    

@schauan:

Wenn ich den (Variant/String) Vergleich vor dem (String) laufen lasse, ist (String) schneller als wenn ich nur (String) laufen lasse .

Das kann mit der internen Speicherverwaltung von VBA zusammen hängen, zumal es hier ja nicht nur um einfache Strings geht, sondern eigentlich um Arrays, entweder als Variant/String oder String, welche aber immer als SafeArrays verwaltet werden.

Hier Artikel mit mehr Infos zu den verschiedenen Datentypen von VBA:
https://www.codeguru.com/visual-basic/how-visual-basic-6-stores-data/


Gruß Knobbi38