VBA-Transponierung

Als Microsoft Excel-Benutzer haben Sie möglicherweise häufig die Transponierungsfunktion verwendet, die das Austauschen zwischen Zeilen und Spalten für ein Array ermöglicht. Das Konvertieren von Zeilen in Spalten und Spalten in Zeilen ist das, was eine Transponierungsfunktion in Excel für Sie erledigt. Die Anzahl der Zeilen wird zu einer Anzahl von Spalten und umgekehrt. Bedeutet, wenn Sie 2 Zeilen und 3 Spalten in Ihrem Array haben, wird es nach dem Transponieren in ein Array mit 3 Zeilen und 2 Spalten geändert. In diesem Tutorial werden wir uns mit VBA-Transponierung befassen, mit der Sie die in Excel verwendete Transponierungsmethode automatisieren können.

Syntax der Transponierung in Excel VBA

VBA Transpose hat die folgende Syntax:

Wo,

Arg1 : Es ist ein erforderliches Argument, das nichts anderes als eine Reihe von Zellen ist, die wir transponieren wollten (dh Array).

Der erste Teil der Syntax ist nichts anderes als ein Ausdruck, unter dem die Transponierfunktion verwendet werden kann. Zum Beispiel WorksheetFunction.

Wie verwende ich Excel VBA Transpose?

Wir werden anhand einiger Beispiele in Excel VBA lernen, wie eine Transponierung verwendet wird.

Sie können diese VBA Transpose Excel-Vorlage hier herunterladen - VBA Transpose Excel-Vorlage

Beispiel 1 - VBA-Transponierung eines eindimensionalen Arrays

Angenommen, Sie arbeiten an Daten mit Listen (das ist ein eindimensionales Array) wie den angegebenen Namen eines Mitarbeiters („Lalit“, „Sneha“, „Ethyl“, „John“, „Cory“) und möchten diese Liste in Excel-Zelle weise eingefügt werden. Mal sehen, wie wir das schaffen.

Führen Sie die folgenden Schritte aus, um Transpose in VBA zu verwenden.

Schritt 1: Fügen Sie ein neues Modul ein und definieren Sie eine neue Unterprozedur, um ein Makro in VBA zu erstellen.

Code:

 Sub Trans_ex1 () End Sub

Schritt 2: Definieren Sie eine neue Variable, die Ihr eindimensionales Array aufnehmen kann.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant End Sub

Schritt 3: Definieren Sie die Liste mit der Array-Funktion als Array. Dies ist die Liste, die Sie in Ihr Excel-Arbeitsblatt einfügen möchten.

Code:

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

Schritt 4: Definieren Sie nun mit der Range.Value-Methode den Bereich, in dem diese Werte eingefügt werden sollen.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Value = End Sub

Schritt 5: Verwenden Sie Application.WorksheetFunction.Transpose für das angegebene Array, um die unter Arr1-Variable definierte Liste transponieren zu können.

Code:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Value = Application.WorksheetFunction.Transpose (Arr1 ) End Sub

Hier haben wir einen Code definiert, der es dem System ermöglicht, die Daten im Listenarray mit dem Namen Arr1 zu transponieren und sie dann in Zelle A1: A5 im aktiven Arbeitsblatt zu speichern.

Schritt 6: Drücken Sie die Taste F5 oder Ausführen unter VBE, um diesen Code auszuführen und die Ausgabe unter der aktiven Excel-Tabelle anzuzeigen.

Sie können sehen, dass das angegebene Array der Liste in eine einzelne Spalte transponiert und in den Zellen A1 bis A5 gespeichert wird.

Beispiel 2 - VBA-Transponierung eines zweidimensionalen Arrays

Angenommen, Sie haben eine zweidimensionale Reihe von Mitarbeitern und ihren Gehältern wie folgt:

Dies sind zweidimensionale Daten mit sechs Zeilen und zwei Spalten. Nach dem Transponieren wäre es ein Array mit zwei Zeilen und sechs Spalten.

Führen Sie die folgenden Schritte aus, um Transpose in VBA zu verwenden.

Schritt 1: Definieren Sie eine Unterprozedur zum Speichern eines Makros.

Code:

 Sub Trans_Ex2 () End Sub

Schritt 2: Bestimmen Sie den Bereich, in dem Sie diese Daten transponieren möchten. Dies kann mit der Funktion Sheets.Range.Value erfolgen. Ich würde das transponierte Array in Zelle D1 bis I2 speichern.

Code:

 Sub Trans_Ex2 () Sheets ("Beispiel # 2"). Range ("D1: I2"). Value = End Sub

Schritt 3: Verwenden Sie WorksheetFunction.Transpose, um der Transponierungsfunktion ein Array A1: B6 zuzuweisen.

Code:

 Sub Trans_Ex2 () Sheets ("Beispiel # 2"). Range ("D1: I2"). Value = WorksheetFunction.Transpose (End Sub

Schritt 4: Wir müssen das Argument für die Transponierfunktion angeben. Wir wollten einen Bereich von Array A1: B6 transponieren. Verwenden Sie daher Range („A1: B6“) als Argument.

Code:

 Sub Trans_Ex2 () Sheets ("Beispiel # 2"). Range ("D1: I2"). Value = WorksheetFunction.Transpose (Range ("A1: B6")) End Sub

Schritt 5: Drücken Sie die Taste F5 oder Run, um diesen Code auszuführen und die Ausgabe anzuzeigen.

Hier wird der Bereich des Arrays A1: B6 transponiert und mit Hilfe der VBA-Transponierungsfunktion in Excel in einen Bereich des Arrays D1: I2 gespeichert.

Beispiel # 3 - VBA Transponieren eines Arrays mit der Paste Special Methode

Wir können das Array auch transponieren und einfügen, wie dies auch in Excel mit Alt + E + S möglich ist. Unter dieser Einfügemethode können wir verschiedene Operationen verwenden.

Betrachten wir dieselben Daten wie im vorherigen Beispiel.

Führen Sie die folgenden Schritte aus, um Transpose in VBA zu verwenden.

Schritt 1: Definieren Sie eine Unterprozedur zum Speichern des Makros.

Code:

 Sub Trans_Ex3 () End Sub

Schritt 2: Definieren Sie zwei neue Variablen, eine für das Datenquellenarray (sourceRng) und andere für den Ausgabearraybereich (targetRng).

Code:

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

Bitte beachten Sie, dass der Typ dieser Variablen als (Excel.Range) definiert ist. Weil wir die Daten, die ein Array-Bereich sind, transponieren wollten.

Schritt 3: Stellen Sie den Quellbereich mit der Sheets.Range-Funktion auf A1: B6 (die Daten, deren Transponierung wir übernehmen wollten) ein.

Code:

 Sub Trans_Ex3 () Dim sourceRng als Excel.Range Dim taretRng als Excel.Range Set sourceRng = Sheets ("Beispiel # 3"). Range ("A1: B6") End Sub

Schritt 4: Stellen Sie den Ziel- / Zielbereich mit der Funktion Sheets.Range auf D1: I2 (den Bereich der Zellen, in denen die Ausgabe gespeichert wird) ein.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Beispiel # 3"). Range ("A1: B6") Set targetRng = Sheets ("Beispiel # 3"). Range ( "D1: I2") End Sub

Schritt 5: Verwenden Sie nun den Befehl Kopieren, um den Quelldatenbereich aus Ihrem Arbeitsblatt zu kopieren.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Beispiel # 3"). Range ("A1: B6") Set targetRng = Sheets ("Beispiel # 3"). Range ( "D1: I2") sourceRng.Copy End Sub

Schritt 6: Wir werden die Funktion PasteSpecial für die Variable targetRng verwenden, um die transponierte Ausgabe im Zielbereich (D1: I2) in Ihrem Arbeitsblatt zu speichern.

Code:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Beispiel # 3"). Range ("A1: B6") Set targetRng = Sheets ("Beispiel # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub

Einfügen : Ermöglicht das Einfügen von Werten in einem anderen Format (z. B. Einfügen als Werte, als Formeln, als Format usw.). Es ist ähnlich wie in Excel (wir machen Alt + E + S, um verschiedene Einfügeoptionen zu haben). In unserem Beispiel haben wir es so eingestellt, dass es als Werte eingefügt wird.
Operation : Es gibt verschiedene Operationen wie Addition, Subtraktion, Multiplikation und Division (genauso wie in Excel).
SkipBlanks : Wenn True festgelegt ist, können mit diesem Befehl die Leerzeichen aus Ihren Daten übersprungen werden, während andere Einfügevorgänge ausgeführt werden. Wir haben es auf False gesetzt, dh wir wollten die Leerzeichen nicht überspringen.
Transponieren : Wenn diese Option auf "True" gesetzt ist, können Sie die Transponierung eines Datenarrays vornehmen.

Schritt 7: Führen Sie diesen Code aus, indem Sie die Taste F5 oder Ausführen drücken und die Ausgabe anzeigen.

Dinge, an die man sich erinnern sollte

Wenn Sie an einem eindimensionalen Array arbeiten, sollte es immer horizontal sein (eine Zeile, mehrere Spalten), um die Transponierungsmethode anzuwenden.
Wenn Sie VBA Transpose in Excel verwenden, müssen Sie unbedingt die Anzahl der Zeilen und die Anzahl der Spalten ermitteln. Wenn wir 3 Zeilen und 5 Spalten haben, gibt es nach der Transponierung 5 Zeilen mit drei Spalten.
Normalerweise beinhaltet die Transponierungsmethode keine Formatierung der Quelldaten. Wenn Sie dasselbe Format wie die Quelldaten anwenden möchten, müssen Sie es entweder manuell festlegen oder mit der Einfügeoption XlPasteFormat können Sie das Format der Quelldaten an die Zieldaten anpassen.
Die Anzahl der Elemente, die die Transponierungsfunktion in einem Array aufnehmen kann, darf 5461 nicht überschreiten.
Ein Array darf keine Elemente / Zeichenfolgen mit einer Länge von mehr als 255 enthalten. Wenn es enthalten ist, werden Fehler verursacht, z.
Das Quell-Array darf keinen Nullwert enthalten (z. B. "Null", "# N / A").

Empfohlene Artikel

Dies war eine Anleitung zur VBA-Transponierung. Hier wurde die Verwendung von Excel VBA Transpose zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage erläutert. Sie können auch unsere anderen Artikelvorschläge durchgehen -

Anleitung zur VBA-String-Funktion
Excel TRANSPOSE Formula
Lernen Sie VBA Case in Excel
Leere Zeilen in Excel entfernen (löschen)

VBA-Transponierung - Wie verwende ich Excel VBA Transpose?

Inhaltsverzeichnis: