Transpozycja VBA

Będąc użytkownikiem programu Microsoft Excel, często korzystałeś z funkcji transpozycji, która umożliwia zamianę między wierszami i kolumnami dla tablicy. Konwersja wierszy na kolumny i kolumn na wiersze jest tym, co robi dla Ciebie funkcja transpozycji w programie Excel. Liczba wierszy staje się liczbą kolumn i odwrotnie. Oznacza to, że jeśli masz 2 wiersze i 3 kolumny w tablicy, po transpozycji zmieni się w tablicę z 3 wierszami i 2 kolumnami. W tym samouczku omówimy metodę transpozycji VBA, która pozwoli zautomatyzować metodę transpozycji używaną w programie Excel.

Składnia transpozycji w programie Excel VBA

Transpozycja VBA ma następującą składnię:

Gdzie,

Arg1 : Jest to wymagany argument, który jest niczym innym jak zakresem komórek, które chcieliśmy przetransponować (tj. Tablicą).

Pierwsza część składni to nic innego jak wyrażenie, pod którym można użyć funkcji transpozycji. Na przykład funkcja arkusza roboczego.

Jak korzystać z transpozycji Excel VBA?

Nauczymy się korzystać z transpozycji z kilkoma przykładami w Excel VBA.

Możesz pobrać ten szablon VBA Transpose Excel tutaj - Szablon VBA Transpose Excel

Przykład # 1 - Transpozycja VBA macierzy jednowymiarowej

Załóżmy, że pracujesz nad danymi z listami (które są jednowymiarową tablicą), takimi jak nazwiska nadanego pracownika („Lalit”, „Sneha”, „Ethyl”, „John”, „Cory”) i chcesz tę listę do wklejenia w komórkach Excela. Zobaczmy, jak możemy to zrobić.

Wykonaj poniższe kroki, aby użyć transpozycji w VBA.

Krok 1: Wstaw nowy moduł i zdefiniuj nową podprocedurę, aby utworzyć makro w VBA.

Kod:

 Sub Trans_ex1 () End Sub

Krok 2: Zdefiniuj nową zmienną, która może pomieścić twoją jednowymiarową tablicę.

Kod:

 Sub Trans_ex1 () Dim Arr1 As Variant End Sub

Krok 3: Zdefiniuj listę jako tablicę za pomocą funkcji Array. Będzie to lista, którą chcesz wkleić w arkuszu programu Excel.

Kod:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array („Lalit”, „Sneha”, „Ethyl”, „John”, „Cory”) End Sub

Krok 4: Teraz użyj metody Range.Value, aby zdefiniować zakres, w którym chcesz wkleić te wartości.

Kod:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array („Lalit”, „Sneha”, „Ethyl”, „John”, „Cory”) Zakres („A1: A5”). Wartość = End Sub

Krok 5: Użyj Application.WorksheetFunction.Transpose na podanej tablicy, aby móc transponować listę zdefiniowaną w zmiennej Arr1.

Kod:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array („Lalit”, „Sneha”, „Ethyl”, „John”, „Cory”) Zakres („A1: A5”). Wartość = Application.WorksheetFunction.Transpose (Arr1 ) Napis końcowy

Tutaj zdefiniowaliśmy kod, który pozwoli systemowi przetransponować dane do tablicy listy o nazwie Arr1, a następnie przechowywać je pod komórką A1: A5 w aktywnym arkuszu.

Krok 6: Naciśnij F5 lub przycisk Uruchom pod VBE, aby uruchomić ten kod i zobaczyć wyniki w aktywnym arkuszu kalkulacyjnym Excel.

Widać, że podana tablica listy jest transponowana do jednej pojedynczej kolumny i przechowywana w komórkach A1 do A5.

Przykład # 2 - Transpozycja VBA macierzy dwuwymiarowej

Załóżmy, że masz dwuwymiarowy zestaw pracowników i ich wynagrodzeń, jak poniżej:

To dwuwymiarowe dane z sześcioma wierszami i dwiema kolumnami. Po transpozycji byłaby to tablica z dwoma rzędami i sześcioma kolumnami.

Wykonaj poniższe kroki, aby użyć transpozycji w VBA.

Krok 1: Zdefiniuj podprocedurę do przechowywania makra.

Kod:

 Sub Trans_Ex2 () End Sub

Krok 2: Wybierz zakres, w którym chcesz transponować te dane. Można to zrobić za pomocą funkcji Sheets.Range.Value. Zapisałbym transponowaną macierz do komórki D1 do I2.

Kod:

 Sub Trans_Ex2 () Arkusze („Przykład 2”). Zakres („D1: I2”). Wartość = End Sub

Krok 3: Użyj WorksheetFunction.Transpose, aby móc przypisać tablicę A1: B6 do transpozycji funkcji.

Kod:

 Sub Trans_Ex2 () Arkusze („Przykład 2”). Zakres („D1: I2”). Wartość = Arkusz roboczy Funkcja.Transpose (End Sub

Krok 4: Musimy podać argument funkcji transpozycji. Chcieliśmy transponować zakres tablicy A1: B6. Dlatego jako argumentu użyj Range („A1: B6”).

Kod:

 Sub Trans_Ex2 () Arkusze („Przykład 2”). Zakres („D1: I2”). Wartość = Arkusz roboczy Funkcja.Transpose (Zakres („A1: B6”)) Koniec Sub

Krok 5: Naciśnij F5 lub przycisk Uruchom, aby uruchomić ten kod i zobaczyć wynik.

Tutaj zakres tablicy A1: B6 jest transponowany i zapisany w zakresie tablicy D1: I2 za pomocą funkcji transpozycji VBA w programie Excel.

Przykład # 3 - Transpozycja VBA tablicy za pomocą Wklej specjalną metodę

Możemy również transponować tablicę i wkleić tak samo, jak to specjalne w Excelu, używając Alt + E + S. Możemy użyć różnych operacji w ramach tej specjalnej metody wklejania.

Rozważmy te same dane, które wykorzystaliśmy w poprzednim przykładzie.

Wykonaj poniższe kroki, aby użyć transpozycji w VBA.

Krok 1: Zdefiniuj podprocedurę do przechowywania makra.

Kod:

 Sub Trans_Ex3 () End Sub

Krok 2: Zdefiniuj dwie nowe zmienne, jedną, która może przechowywać tablicę źródeł danych (sourceRng), a drugą, która może przechowywać zakres tablicy wyjściowej (targetRng).

Kod:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range End Sub

Należy pamiętać, że typ tych zmiennych jest zdefiniowany jako (Excel.Range). Ponieważ chcieliśmy transponować dane, które są zakresem tablic.

Krok 3: Ustaw zakres źródła jako A1: B6 (Dane, które chcieliśmy transponować) za pomocą funkcji Sheets.Range.

Kod:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Ustaw sourceRng = Arkusze („Przykład nr 3”). Zakres („A1: B6”) Koniec Sub

Krok 4: Ustaw zakres docelowy / docelowy jako D1: I2 (Zakres komórek, w których dane wyjściowe będą przechowywane) za pomocą funkcji Sheets.Range.

Kod:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Ustaw sourceRng = Arkusze („Przykład nr 3”). Zakres („A1: B6”) Ustaw targetRng = Arkusze („Przykład nr 3”). Zakres ( „D1: I2”) End Sub

Krok 5: Teraz użyj polecenia Kopiuj, aby skopiować źródłowy zakres danych z arkusza.

Kod:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Ustaw sourceRng = Arkusze („Przykład nr 3”). Zakres („A1: B6”) Ustaw targetRng = Arkusze („Przykład nr 3”). Zakres ( „D1: I2”) sourceRng.Copy End Sub

Krok 6: Użyjemy funkcji PasteSpecial na zmiennej targetRng, aby zapisać transponowane dane wyjściowe w zakresie docelowym (D1: I2) w arkuszu.

Kod:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Ustaw sourceRng = Arkusze („Przykład nr 3”). Zakres („A1: B6”) Ustaw targetRng = Arkusze („Przykład nr 3”). Zakres ( „D1: I2”) sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub

Wklej : Pozwala wkleić wartości w innym formacie (np. Wklej jako wartości, jako formułę, jako format itp.). Jest podobny do Excela (wykonujemy Alt + E + S, aby mieć różne specjalne opcje wklejania). W naszym przykładzie ustawiliśmy go na wklejanie jako wartości.
Operacja : Istnieją różne operacje, które można wykonać, takie jak dodawanie, odejmowanie, mnożenie, dzielenie (w taki sam sposób, jak w programie Excel).
SkipBlanks : Jeśli ustawione na True, to polecenie pozwala pominąć puste miejsca na danych, wykonując różne operacje specjalne wklejania. Ustawiliśmy na False, co oznacza, że nie chcieliśmy pomijać pustych miejsc.
Transpozycja : Jeśli jest ustawiona wartość Prawda, umożliwia transpozycję tablicy danych.

Krok 7: Uruchom ten kod, naciskając klawisz F5 lub Run i zobacz wynik.

Rzeczy do zapamiętania

Pracując nad jednowymiarową tablicą, zawsze powinna być pozioma (jeden rząd, wiele kolumn), aby zastosować metodę transpozycji.
Podczas korzystania z transpozycji VBA w programie Excel konieczne jest ustalenie liczby wierszy i liczby kolumn. Jeśli mamy 3 wiersze i 5 kolumn, po transpozycji będzie 5 wierszy z trzema kolumnami.
Zwykle metoda transpozycji nie obejmuje formatowania danych źródłowych. Jeśli chcesz zastosować ten sam format, co w przypadku danych źródłowych, musisz ręcznie ustawić go lub użyć specjalnej opcji wklejania XlPasteFormat, aby zachować format danych źródłowych do danych docelowych.
Liczba elementów, które funkcja transpozycji może przyjąć w tablicy, nie może przekraczać 5461.
Tablica nie może zawierać żadnego elementu / ciągu o długości większej niż 255. Jeśli zostanie uwzględniona, spowoduje błędy, takie jak 13, niezgodność typów, 5, nieprawidłowe wywołanie lub argument procedury, 1004, błąd zdefiniowany przez aplikację lub obiekt .
Tablica źródłowa nie może zawierać żadnej wartości zerowej (takiej jak „Null”, „# N / A”).

Polecane artykuły

To był przewodnik po transpozycji VBA. Tutaj omówiliśmy, jak korzystać z Excel VBA Transpose wraz z praktycznymi przykładami i szablonem Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

Przewodnik po funkcji VBA String
Formuła Excel TRANSPOSUJ
Dowiedz się VBA Case w Excelu
Usuń (usuń) puste wiersze w programie Excel

Transpozycja VBA - Jak korzystać z transpozycji Excel VBA?

Spisie treści: