Widzieliśmy funkcję transpozycji w arkuszu programu Excel, gdy wklejamy dowolną tabelę danych do arkusza roboczego, transpozycja zmienia położenie wierszy i kolumn, tj. Wiersze stają się kolumnami, a kolumny stają się wierszami w tabeli danych, ponieważ jest to arkusz roboczy Funkcji w VBA używamy z metodą Application.worksheet w VBA.
Jak transponować w VBA?
Przełączanie wierszy i kolumn jest jedną z technik manipulacji danymi, które prawie wszyscy użytkownicy wykonują w programie Excel. Proces konwersji danych poziomych na pionowe i pionowych na poziome jest w programie Excel nazywany „transpozycją”. Jestem pewien, że musisz znać transpozycję w zwykłym arkuszu roboczym, w tym artykule pokażemy, jak używać metody transpozycji w kodowaniu VBA.
W VBA możemy transponować na dwa sposoby.
- Transponuj za pomocą formuły TRANSPOSE.
- Transpozycja przy użyciu specjalnej metody wklejania.
Kiedy dokonujemy transpozycji, zamieniamy wiersze na kolumny i kolumny na wiersze. Na przykład, jeśli dane są w macierzy 4 x 3, to staje się tablicą 3 x 4.
Zobaczmy kilka przykładów transpozycji kolumny do wiersza w VBA.
# 1 - Transpozycja VBA za pomocą formuły TRANSPOSE
Podobnie jak w przypadku korzystania z TRANSPOSE w programie Excel, podobnie możemy użyć formuły TRANSPOSE w VBA. Nie mamy formuły TRANSPOSE w VBA, więc musimy użyć jej w klasie Worksheet Function.
Jako przykład spójrz na poniższy obraz danych.
Spróbujemy przetransponować tę tablicę wartości. Wykonaj poniższe kroki, aby transponować dane.
Krok 1: Uruchom podprocedurę
Kod:
Sub Transpose_Example1 () End Sub
Krok 2: Najpierw musimy zdecydować, gdzie przetransponujemy dane. W tym przypadku zdecydowałem się na transpozycję z komórki D1 do H2. Tak więc wprowadź kod VBA jako Zakres („D1: H2”). Wartość =
Kod:
Sub Transpose_Example1 () Range („D1: H2”). Wartość = End Sub
Krok 3: Teraz w wyżej wymienionym zakresie potrzebujemy wartości z zakresu od A1 do B5. Aby dojść do tej otwartej klasy „Funkcja arkusza” i wybierz formułę „Transpozycja”.
Krok 4: W Arg 1 podaj zakres źródła danych, tj. Range („A1: D5”) .
Kod:
Sub Transpose_Example1 () Range („D1: H2”). Value = WorksheetFunction.Transpose (Range („A1: D5”)) End Sub
Ok, skończyliśmy z kodowaniem formuły TRANSPOSE. Teraz uruchom kod, aby zobaczyć wynik w zakresie komórek od D1 do H2.
Jak widzieliśmy na powyższym obrazie, przekonwertował zakres komórek z kolumn na wiersze.
# 2 - Transpozycja VBA za pomocą specjalnej metody wklejania
Możemy również transponować używając metody Paste Special. Rozważ te same dane również w tym przykładzie.
Pierwszą rzeczą, jaką musimy zrobić, aby dokonać transpozycji, jest skopiowanie danych. Więc zapisz kod jako Range („A1: B5”)
Kod:
Sub Transpose_Example2 () Range („A1: B5”). Copy End Sub
Następną rzeczą jest to, że musimy zdecydować, gdzie zamierzamy wkleić dane. W tym przypadku wybrałem D1 jako żądaną komórkę docelową.
Kod:
Sub Transpose_Example2 () Range („A1: B5”). Copy Range („D1”). Napis końcowy
Po wybraniu żądanej komórki docelowej musimy wybrać „Paste Special Method”.
Dzięki wklejaniu specjalnemu możemy wykonywać wszystkie czynności, które mamy, zwykłymi metodami wklejania w arkuszu.
Zignoruj wszystkie parametry i wybierz ostatni parametr, tj. Transponuj i ustaw jako TRUE.
Kod:
Sub Transpose_Example2 () Range („A1: B5”). Copy Range („D1”). PasteSpecial Transpose: = True End Sub
Spowoduje to również transpozycję danych, podobnie jak w poprzedniej metodzie.
W ten sposób możemy użyć formuły TRANSPOSE lub metody Wklej specjalnie, aby transponować dane, aby przełączyć wiersze na kolumny i kolumny na wiersze.
Rzeczy do zapamiętania
- Jeśli korzystamy z funkcji arkusza TRANSPOSE, konieczne jest obliczenie liczby wierszy i kolumn w celu transpozycji danych. Jeśli mamy 5 wierszy i 3 kolumny, to podczas transpozycji otrzymujemy 3 wiersze i 5 kolumn.
- Jeśli chcesz mieć takie samo formatowanie podczas używania wklejania specjalnego, musisz użyć argumentu Paste Type jako „xlPasteFormats”
Możesz pobrać ten szablon VBA Transpose Excel stąd - VBA Transpose Excel Template
