Transpozycja danych to operacja w Excelu, która pozwala zamienić miejscami wiersze z kolumnami. Możemy jej używać również w VBA za pomocą funkcji TRANSPOSE. W tym przypadku, dość nieoczekiwanie zamienia ona jednak dni z miesiącami.
Transpozycja danych w Excelu
Z transpozycji można w Excelu skorzystać na dwa sposoby.
Pierwsza opcja (bardziej popularna) polega na skopiowaniu tabeli (CTRL+C) i użyciu polecenia Wklej specjalnie… (Ctrl+Alt+V potem E).
Drugi sposób (bardzo rzadko stosowany) to użycie funkcji arkuszowej TRANSPONUJ.
Oba sposoby różnią się zasadniczo od siebie.
W pierwszym przypadku dane źródłowe nie są w żaden sposób powiązane z danymi wynikowymi – obie tabele są od siebie niezależne. Oznacza to, że jeżeli zmienimy coś w tabeli źródłowej, nie wpłynie to w żaden sposób na tabelę przekształconą. Plusem tego rozwiązania jest natomiast zachowanie formuł i formatów, a także szybkość działania.
W drugim przypadku jest odwrotnie, przekształcenie ma charakter dynamiczny, ale tracimy formatowanie, ponieważ TRANSPONUJ jest wbudowaną funkcją arkuszową.
Funkcja TRANSPONUJ w VBA
W przeciwieństwie do Excela, w VBA bardzo często korzysta się z funkcji TRANSPONUJ. Ma to związek z faktem, że dane powinno się przetwarzać w tablicy, a nie w komórkach arkusza. Jest to nieporównywalnie szybsze. Zanim jednak wstawimy dane do komórek, musimy tą tablicę przetransponować.
Danych nie powinniśmy przetwarzać bezpośrednio w arkuszu. Należy je zrzucić do tablicy wirtualnej, przekształcić i wgrać jednym poleceniem do zakresu komórek. Jest to o wiele szybsze!
Case study – dziwna tabelka
Są osoby, które nie pracują w Excelu i nie wiedzą, że dane powinny mieć formę bazodanową. Ostatnio spotkałem się z przykładem danych sprzedaży zapisanych mniej więcej w taki sposób.
Oczywiście takich danych nie można nawet posortować, nie mówiąc o filtrowaniu czy użyciu tabeli przestawnej.
Konieczne jest napisanie makra, które konwertuje tą tabelę do właściwego układu.
Kod makra
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
Public Sub UtworzTabeleBazoDanowa() '// Makro konwertuje tabelkę Klienta do postaci bazodanowej 'Dane źródłowe Dim avZrodlo As Variant ' Tabela źrodłowa Dim dtData As Long ' Data Dim iKolumny As Integer ' Liczba kolumn w tabeli Dim iSprzedaz As Integer ' Sprzedaż (od 1 do 100 jednostek) 'Tablica wynikowa Dim avWynik() As Variant ' Dynamiczna tablica z wynikiem Dim r As Long ' Licznik zwiększający Dim x As Long, c As Long ' Liczniki pętli Const sPROC As String = "UtworzTabeleBazoDanowa" 'Aktywuj obsługę błędów na starcie 1 On Error GoTo ObslugaBledu 'Zaczytaj dane z arkusza do tablicy 2 avZrodlo = wksDane.Range("A1").CurrentRegion 'Sprawdź liczbę kolumn w tabeli 3 iKolumny = UBound(avZrodlo, 2) 'Przejdź w pętli po każdej dacie - pomiń nagłówki 4 For x = LBound(avZrodlo, 1) + 1 To UBound(avZrodlo, 1) 'Zaczytaj datę do zmiennej 5 dtData = avZrodlo(x, 1) 'Przejdź w pętli po każdej kolumnie 6 For c = 2 To iKolumny 'Zaczytaj Sprzedaż 7 iSprzedaz = avZrodlo(x, c) 'Dodaj datę i sprzedaż do tablicy, jeśli sprzedaż > 0 8 If iSprzedaz > 0 Then 'Zwiększ licznik 9 r = r + 1 'Dodaj do tablicy dane 10 ReDim Preserve avWynik(1 To 2, 1 To r) 11 avWynik(1, r) = dtData 12 avWynik(2, r) = iSprzedaz 13 End If '// If iSprzedaz > 0 Then 14 Next c '// For c = 1 To iKolumny 15 Next x '// For x = LBound(avZrodlo, 1) + 1 To UBound(avZrodlo, 1) 'Wgraj dane do kolumn H:I 16 With wksDane 17 .Range("H1:I1").EntireColumn.ClearContents 18 .Range("H1:I1").Value = Array("Data", "Sprzedaż") 19 .Range("H2").Resize(r, 2) = WorksheetFunction.Transpose(avWynik) 20 End With Wyjscie: 21 On Error GoTo 0 22 Exit Sub ObslugaBledu: 23 Application.ScreenUpdating = True 24 MsgBox Title:="Błąd programu!", Buttons:=vbInformation, _ Prompt:="Informacje dotyczące błędu: " & vbCr & vbCr & _ "Numer: " & vbTab & Err.Number & vbCr & _ "Opis: " & vbTab & Err.Description & vbCr & vbCr & _ "Moduł: " & vbTab & ms_MODUL & vbCr & _ "Makro: " & vbTab & sPROC & vbCr & _ "Linia: " & vbTab & Erl() 25 GoTo Wyjscie End Sub |
Analiza makra
- Najpierw aktywujemy obsługę błędów – makro przerwie bieg i wyświetli alert, jeśli natrafi na błąd
- Zaczytujemy do tablicy wirtualnej typu Variant całą tabelę arkuszową (dane sprzedaży)
- Sprawdzamy, ile kolumn ma tabela – wyszło, że 5
- Przechodzimy w pętli po każdej dacie. Sprawdzamy dla daty każdą kolumnę ze sprzedażą (od 2 do 5)
- Jeśli wystąpiła sprzedaż (>0) wówczas zwiększamy rozmiar naszej tablicy wirtualnej o 1. UWAGA! Zobaczcie, że tablica została zadeklarowana jako dynamiczna, ponieważ nie wiemy, ile elementów (data + sprzedaż) będzie przechowywać. W tablicy dynamicznej możemy zmieniać tylko drugi wymiar dlatego instrukcja: ReDim Preserve avWynik(1 To 2, 1 To r)
- Mając dane w tablicy wirtualnej chcemy je przenieść do komórek arkusza. Usuwamy stare dane, dodajemy nagłówki i wstawiamy!
- Wcześniej jednak musimy je przetransponować, ponieważ w tablicy mamy dane w dwóch wierszach i kilkunastu kolumnach. A my chcemy odwrotnie! Chcemy mieć dane w dwóch kolumnach i kilkunastu wierszach.
Gotowe!
Ale czy wszystko jest OK?
Problem z datami
Rzućcie proszę okiem na kolumnę H. Widać, że coś złego stało się z datami… Zamiast 1-paź mamy 10-sty… Zamiast 2-paź mamy 10-lut…. itd.
Sam byłem zaskoczony tymi wynikami…. Nie wiedziałem, dlaczego VBA zamienia mi dni z miesiącami.
Sprawdziłem ustawienia regionalne Windows dla dat, tam jednak nic się nie zmieniło.
Rozwiązanie problemu
Przyczynę problemu znalazłem w tym wątku.
Okazało się, że winę ponosi właśnie funkcja TRANSPONUJ, która w VBA – nie wiedzieć czemu – oprócz tablicy, transponuje jeszcze daty.
Jakie jest zatem rozwiązanie problemu? Bardzo proste! Wystarczy zmienną dtData zadeklarować, nie jako Date, lecz jako Long. Czyli Excel będzie rozpatrywał tą wartość, nie jako datę, lecz jako liczbę.
Jak widać na poniższym screenie, ta drobna zmiana, załatwiła sprawę 😊.
Plik XLSM
