ExcelMagazine

Problem wydawał się trywialny. Potrzebowałem szybkiej procedury, która połączy razem k dwuwymiarowych tablic (n,5) typu Variant.

Kluczem do rozwiązania była procedura łącząca w jedną dwie dowolnej długości tablice. Whenever possible, steal code -- na daily dose szybko znalazłem właściwą funkcję. Niestety, działa tylko dla wektorów:

 
Function ArrayUnion(ByVal va1 As Variant, ByVal va2 As Variant) As Variant
   Dim i As Long, Upper As Long
   If TypeName(va1) = “Empty” Then
      va1 = va2
   Else
      Upper = UBound(va1)
      If LBound(va2) = 0 Then Upper = Upper + 1
      ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) - LBound(va2) + 1)
      For i = LBound(va2) To UBound(va2)
         va1(Upper + i) = va2(i)
      Next i
   End If
   ArrayUnion = va1
End Function
 

Modyfikacja powyższego kodu o drugi wymiar napotkała jeden poważniejszy problem. Używając Preserve można rozszerzyć tylko ostatni wymiar tablicy. Na szczęście Excel udostępnia funkcję Transpozycja() -- Application.Transpose(), którą wykorzystałem przy przekazywaniu tablic funkcji. Oto końcowy efekt:

 
Function ArrayUnion(ByVal va1 As Variant, ByVal va2 As Variant) As Variant
   Dim i As Long, Upper As Long, j As Long
   Dim temp As Variant
   If TypeName(va1) = "Empty" Then
      va1 = va2
      temp = va1
   Else
      Upper = UBound(va1, 2)
      If LBound(va2, 2) = 0 Then Upper = Upper + 1
      ReDim Preserve va1(1 To 5, LBound(va1, 2) To UBound(va1, 2) + UBound(va2, 2) -    LBound(va2, 2) + 1)
      For i = LBound(va2, 2) To UBound(va2, 2)
      For j = 1 To 5
          va1(j, Upper + i) = va2(j, i)
      Next j
   Next i
   ArrayUnion = temp
End Functio

Połączenie ponad trzystu tablic w jedną o wymiarach (8500,5) przy jej wykorzystaniu trwało niecałe 2 minuty (dokładnie 114 sekund). Zbyt długo. Postanowiłem więc spróbować zamienić kosztowne czasowo Preserve na podwójną pętlę:

...

 
   ReDim temp(1 To 5, 1 To UBound(va1, 2) + UBound(va2, 2))
   For i = 1 To UBound(va1, 2) + UBound(va2, 2)
     If i <= UBound(va1, 2) Then
     For j = 1 To 5
        temp(j, i) = va1(j, i)
     Next j
   Else
     For j = 1 To 5
        temp(j, i) = va2(j, i - UBound(va1, 2))
     Next j
   End If
   Next i
End If
ArrayUnion = temp ' va1
End Functio

Efekt? Czas zadania wydłużył się o ponad minutę (do trzech). Stara zasada -- korzystaj z pętli, dopiero gdy zawiodą wbudowane mechanizmy Excela -- jest więc nadal aktualna. Może czas pomyśleć o lepszym procesorze...

Powiązane artykuły:

• Wtorki z VBA cz. 15 – Wczytywanie zakresów do tablicy
• Wtorki z VBA cz.14 – Zapisywanie zakresów
• Wtorki z VBA cz.12 – Usuwanie pustych wierszy
• Wtorki z VBA cz.11 – Wydajne przetwarzanie zakresów
• Podstawowa interakcja z użytkownikiem

One Response to “Łączenie tabel a wydajność cz.1”

1. ExcelMagazine | Łączenie tabel, a wydajność… cz.2 says:

   April 29, 2008 at 1:22 pm

   [...] dni temu w tym poście zastanawiałem się nad wydajnością łączenia k tablic (n,5) w jedną. Dziś rano, zupełnie [...]