Spalte mit Makro transponieren, je drei Zeilen

Forumthread: Spalte mit Makro transponieren, je drei Zeilen

07.08.2015 15:36:14

Sascha

Hallo allerseits,
ich bin mir sicher, dass ihr mir hierbei behilflich sein könnt.
Mein Arbeitsblatt in Excel sieht im Moment so aus, dass alle Werte in einer einzige Spalte aufgelistet sind.
Es handelt sich dabei jeweils um Blöcke mit je drei Zellen, d.h. Zeile 1-3 gehören logisch zusammen, Zeile 4-6 ebenfalls, usw.
Nun möchte ich diese Werte so transponieren, dass aus jedem Dreier-Block eine einzelne Zeile wird.
Im Forum bin ich bezüglich eines Makros hierfür teilweise schon fündig geworden:


Sub Transponiere()
Sheets("Blatt1").Range("A1:A3").Copy
Sheets("Blatt2").Range("A1:C1").PasteSpecial Transpose:=True
End Sub

Dieses Makro macht im Grunde genau das was ich möchte, allerdings hab ich keine Ahnung wie ich den Befehl jetzt auf das restliche Dokument anwende.
Der nächste Schritte wäre ja nun, die Zellen A4:A6 nach A2:C2 zu transponieren.
Die Datei hat tausende dieser Dreier-Blöcke und da sollte es doch eine schlanke Lösung für geben.
VBA-Kenntnisse habe ich bisher leider keine.
Besten Dank schon mal für eure Unterstützung

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Spalte mit Makro transponieren, je drei Zeilen

07.08.2015 15:59:52

Daniel

Hi
das schnellste ist, wenn du folgende Formel in B1 einträgst und diese bis Dxxx ziehst (soweit wie benötigt)
=INDEX($A:$A;(ZEILE()-1)*3+SPALTE()-1)
dann Spalten B:C kopieren und an gleicher Stelle als Wert einfügen, dann Spalte A löschen.
sieht als Makros so aus:

With Range("B1").Resize(Worksheetfunction.CountA(Columns(1))/3, 3)
.formular1c1 = "=Index(C1,(Row()-1)*3+Column()-1)"
.copy
.pastespecial xlpastevalues
end with
columns(1).delete

gruß Daniel

AW: Spalte mit Makro transponieren, je drei Zeilen

07.08.2015 17:43:14

Sascha

Super, vielen Dank für die schnellen Antworten. Hat bestens funktioniert =)

AW: Spalte mit Makro transponieren, je drei Zeilen

07.08.2015 16:09:00

Sepp

Hallo Sascha,
eine Möglichkeit,

Sub Transponiere()
Dim lngI As Long, lngN As Long, lngMax As Long
Dim vntOut() As Variant

With Sheets("Blatt1")
  lngMax = Application.Max(3, .Cells(.Rows.Count, 1).End(xlUp).Row)
  Redim vntOut(1 To Clng(lngMax / 3))
  For lngI = 1 To lngMax Step 3
    lngN = lngN + 1
    vntOut(lngN) = Application.Transpose(.Cells(lngI, 1).Resize(3, 1))
  Next
End With

Sheets("Blatt2").Range("A1").Resize(UBound(vntOut), 3) = _
  Application.Transpose(Application.Transpose(vntOut))
End Sub

Gruß Sepp

AW: Spalte mit Makro transponieren, je drei Zeilen

07.08.2015 16:14:50

Peter

Hallo Sascha,
das Makro sollte deine Wünsche erfüllen.
Gruß Peter

Option Explicit
Public Sub Transformier_mich()
Dim vEingabe    As Variant
Dim vAusgabe()  As Variant
Dim lLetzte     As Long
Dim lIndx_E     As Long
Dim lZeile      As Long
With ThisWorkbook.Worksheets("Tabelle1") ' den Tabellenblattnamen "Tabelle1" ggf. anpassen
lLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
vEingabe = .Range("A1:A" & lLetzte).Value
End With
ReDim vAusgabe(1 To lLetzte / 3, 1 To 3)
For lIndx_E = LBound(vEingabe) To UBound(vEingabe) Step 3
lZeile = lZeile + 1
vAusgabe(lZeile, 1) = vEingabe(lIndx_E + 0, 1)
vAusgabe(lZeile, 2) = vEingabe(lIndx_E + 1, 1)
vAusgabe(lZeile, 3) = vEingabe(lIndx_E + 2, 1)
Next lIndx_E
'     den Tabellenblattnamen "Tabelle2" ggf. anpassen
ThisWorkbook.Worksheets("Tabelle2").Range("A1:C" & lLetzte / 3).Value = vAusgabe
End Sub

Infobox / Tutorial

Spalte in Excel transponieren: Werte je drei Zeilen umwandeln

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um deine Daten in Excel zu transponieren und dabei jeweils drei Zeilen in eine einzelne Zeile umzuwandeln, kannst du ein VBA-Makro verwenden. Hier ist eine einfache Anleitung:

Öffne Excel und gehe zu dem Arbeitsblatt, das die Daten enthält (z.B. "Blatt1").
Drücke ALT + F11, um den VBA-Editor zu öffnen.
Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.

Füge das folgende Makro ein:

Sub Transponiere()
   Dim lngI As Long, lngN As Long, lngMax As Long
   Dim vntOut() As Variant

   With Sheets("Blatt1")
       lngMax = Application.Max(3, .Cells(.Rows.Count, 1).End(xlUp).Row)
       ReDim vntOut(1 To lngMax / 3, 1 To 3)
       For lngI = 1 To lngMax Step 3
           lngN = lngN + 1
           vntOut(lngN, 1) = .Cells(lngI, 1)
           vntOut(lngN, 2) = .Cells(lngI + 1, 1)
           vntOut(lngN, 3) = .Cells(lngI + 2, 1)
       Next lngI
   End With
   Sheets("Blatt2").Range("A1:C" & lngN).Value = vntOut
End Sub

Schließe den VBA-Editor und gehe zurück zu Excel.
Führe das Makro aus, indem du auf Entwicklertools > Makros gehst und das Transponiere-Makro auswählst.

Das Makro wird nun deine Daten aus "Blatt1" in "Blatt2" transponieren und die Werte je drei Zeilen in eine Zeile umwandeln.

Häufige Fehler und Lösungen

Fehler: „Laufzeitfehler 9: Index außerhalb des gültigen Bereichs“
- Lösung: Überprüfe, ob die Blattnamen in deinem Makro korrekt sind (z.B. "Blatt1" und "Blatt2"). Wenn deine Blätter anders benannt sind, passe die Namen im Makro an.
Fehler: Daten werden nicht korrekt transponiert
- Lösung: Stelle sicher, dass die Anzahl der Zeilen in deiner Datenquelle durch drei teilbar ist. Andernfalls könnte das Makro nicht alle Daten korrekt verarbeiten.

Alternative Methoden

Falls du keine VBA-Kenntnisse hast oder einfach eine Formel verwenden möchtest, kannst du die folgenden Schritte durchführen:

Gehe zu Zelle B1 und gib die folgende Formel ein:
```
=INDEX($A:$A,(ZEILE()-1)*3+SPALTE()-1)
```
Ziehe die Formel nach rechts bis zur Spalte D und nach unten, bis alle benötigten Zeilen abgedeckt sind.
Kopiere die Werte in Spalte B:C und füge sie an derselben Stelle als Werte ein. Lösche anschließend die ursprüngliche Spalte A.

Praktische Beispiele

Angenommen, du hast folgende Daten in "Blatt1":

A1: Wert1
A2: Wert2
A3: Wert3
A4: Wert4
A5: Wert5
A6: Wert6

Nach Ausführung des Makros wirst du in "Blatt2" folgendes erhalten:

A1: Wert1 | B1: Wert2 | C1: Wert3
A2: Wert4 | B2: Wert5 | C2: Wert6

Tipps für Profis

Du kannst das Makro anpassen, um weitere Spalten zu transponieren, indem du die Dimensionen des Arrays vntOut veränderst.
Verwende die Application.ScreenUpdating = False und Application.ScreenUpdating = True Befehle vor und nach dem Makro, um die Ausführungsgeschwindigkeit zu erhöhen und Bildschirmflackern zu vermeiden.

FAQ: Häufige Fragen

1. Wie viele Zeilen kann ich gleichzeitig transponieren? Du kannst beliebig viele Zeilen transponieren, solange dein Computer und Excel genügend Ressourcen haben. Das Makro ist so konzipiert, dass es mit einer großen Anzahl von Zeilen umgehen kann.

2. Funktioniert dieses Makro in allen Excel-Versionen? Ja, das Makro sollte in den meisten modernen Excel-Versionen funktionieren, einschließlich Excel 2010, 2013, 2016, 2019 und Microsoft 365. Stelle sicher, dass die Makros in deinen Excel-Einstellungen aktiviert sind.

Beliebteste Forumthreads (12 Monate)

Spalte mit Makro transponieren, je drei Zeilen

Forumthread: Spalte mit Makro transponieren, je drei Zeilen

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Spalte in Excel transponieren: Werte je drei Zeilen umwandeln

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Spalte mit Makro transponieren, je drei Zeilen

Forumthread: Spalte mit Makro transponieren, je drei Zeilen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Spalte in Excel transponieren: Werte je drei Zeilen umwandeln

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread