Excel 2007: Spalten in Zeilen umwandeln

Computer: Software & Programmierung Office Software
#1

Hallo zusammen!

Ich habe ein Problem. Ich muss eine Tabelle umwandeln, damit sie in ein anderes Programm eingelesen werden kann.

Die Quelle sieht so aus:

Name Bananen Äpfel Birnen
Test1 10 20 30
Test2 60 0 0
Test3 10 60 0
Test4 0 30 30

Das Ziel (neues Blatt) soll so aussehen:

Test1 Bananen 10
Test1 Äpfel 20
Test1 Birnen 30
Test2 Bananen 60
Test3 Bananen 10
Test3 Äpfel 60
Test4 Äpfel 30
Test4 Birnen 30

Das kann man leider nicht durch Transponieren erreichen.
Gibts dafür eine Formel oder geht das nur mit einem Makro.
Von Makros habe ich leider überhaupt keine Ahnung.

Kann jemand helfen?

LG Torsten

#2

Kreuztabelle auflösen

Name Bananen Äpfel Birnen
Test1 10 20 30
Test2 60 0 0
Test3 10 60 0
Test4 0 30 30

Das Ziel (neues Blatt) soll so aussehen:
Test1 Bananen 10
Test1 Äpfel 20
Test1 Birnen 30
Test2 Bananen 60
Test3 Bananen 10
Test3 Äpfel 60
Test4 Äpfel 30
Test4 Birnen 30

Hallo Torsten,

bei größeren Tabellen mußte die Zellbereiche der beiden Namen
vergrößern.

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │ C │
───┼───────┼─────────┼────────┤
 1 │ Name │ Obst │ Anzahl │
───┼───────┼─────────┼────────┤
 2 │ Test1 │ Äpfel │ 20 │
───┼───────┼─────────┼────────┤
 3 │ Test1 │ Birnen │ 30 │
───┼───────┼─────────┼────────┤
 4 │ Test2 │ Bananen │ 60 │
───┼───────┼─────────┼────────┤
 5 │ Test2 │ Äpfel │ 0 │
───┼───────┼─────────┼────────┤
 6 │ Test2 │ Birnen │ 0 │
───┼───────┼─────────┼────────┤
 7 │ Test3 │ Bananen │ 10 │
───┼───────┼─────────┼────────┤
 8 │ Test3 │ Äpfel │ 60 │
───┼───────┼─────────┼────────┤
 9 │ Test3 │ Birnen │ 0 │
───┼───────┼─────────┼────────┤
10 │ Test4 │ Bananen │ 0 │
───┼───────┼─────────┼────────┤
11 │ Test4 │ Äpfel │ 30 │
───┼───────┼─────────┼────────┤
12 │ Test4 │ Birnen │ 30 │
───┴───────┴─────────┴────────┘
Benutzte Formeln:
A2 : =WENN(ZEILE(A2)\>Spa\*Zei;"";INDEX(Tabelle1!A:A;
KÜRZEN((ZEILE(A2)-1)/Spa)+1+1))
A3 : =WENN(ZEILE(A3)\>Spa\*Zei;"";INDEX(Tabelle1!A:A;
KÜRZEN((ZEILE(A3)-1)/Spa)+1+1))
A4 : =WENN(ZEILE(A4)\>Spa\*Zei;"";INDEX(Tabelle1!A:A;
KÜRZEN((ZEILE(A4)-1)/Spa)+1+1))
usw. in A

B2 : =WENN(ZEILE(A2)\>Spa\*Zei;"";INDEX(Tabelle1!$B$1:blush:E$1;
REST(ZEILE(A2)-1;Spa)+1))
B3 : =WENN(ZEILE(A3)\>Spa\*Zei;"";INDEX(Tabelle1!$B$1:blush:E$1;
REST(ZEILE(A3)-1;Spa)+1))
B4 : =WENN(ZEILE(A4)\>Spa\*Zei;"";INDEX(Tabelle1!$B$1:blush:E$1;
REST(ZEILE(A4)-1;Spa)+1))
usw. in B

C2 : =WENN(ZEILE(A2)\>Spa\*Zei;"";INDEX(Tabelle1!B$2:F$20;
KÜRZEN((ZEILE(A2)-1)/Spa)+1;REST(ZEILE(A2)-1;Spa)+1))
C3 : =WENN(ZEILE(A3)\>Spa\*Zei;"";INDEX(Tabelle1!B$2:F$20;
KÜRZEN((ZEILE(A3)-1)/Spa)+1;REST(ZEILE(A3)-1;Spa)+1))
C4 : =WENN(ZEILE(A4)\>Spa\*Zei;"";INDEX(Tabelle1!B$2:F$20;
KÜRZEN((ZEILE(A4)-1)/Spa)+1;REST(ZEILE(A4)-1;Spa)+1))
usw. in C

Festgelegte Namen:
Spa: =ANZAHL2(Tabelle1!$A$1:blush:F$1)-1
Zei: =ANZAHL2(Tabelle1!$A$1:blush:A$10)-1

A1:C12
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Quelle:
http://excelformeln.de/formeln.html?welcher=138

Gruß
Reinhard

#3

Die Quelle sieht so aus:

Name Bananen Äpfel Birnen
Test1 10 20 30
Test2 60 0 0
Test3 10 60 0
Test4 0 30 30

Das Ziel (neues Blatt) soll so aussehen:

Test1 Bananen 10
Test1 Äpfel 20
Test1 Birnen 30
Test2 Bananen 60
Test3 Bananen 10
Test3 Äpfel 60
Test4 Äpfel 30
Test4 Birnen 30

Hallo Torsten

Ich habe dazu (vor langer, langer Zeit irgendwo hinter den sieben Bergen im Netz) ein interessantes Makro gefunden, das ich für die vorliegende Aufgabe adaptiert habe.

Jedem Fachmann werden sich die Haare sträuben, wie dieses Makro zusammengebastelt ist. Aber es läuft. Ich hoffe, auch bei Dir.

Beachten muss man: Die 0-Werte werden ebenfalls in die neue Liste übertragen. - In der Quelltabelle dürfen keine leeren Zellen vorkommen. Leere Zellen müssen mit „0“ oder mit einem Leerschlag „gefüllt“ werden.

Grüsse Niclaus

Sub Tabelle_zu_Liste()

SpAUeschrift = Range(„A1“)
SpBUeschrift = „Art“
SpCUeschrift = „Menge“
'Spaltenbeschriftungen der neuen Liste
zeile = 1
spalte = 1
bspalte = 1
bzeile = 1

Do While Cells(zeile, spalte) „“
If Cells(zeile + 1, spalte) Cells(zeile, spalte) Then
'Art-Sprung erkannt
bzeile = zeile
bspalte = 1
counter = 0
'Arten werden von links nach rechts „abgemäht“
Do While Cells(bzeile, spalte + 1) „“
'Anzahl Arten zählen für Rückschritt bei Löschung Ursprungszeile
counter = counter + 1
'Umstrukturierung
Cells(zeile + 1, bspalte).EntireRow.Insert
Cells(zeile + 1, bspalte) = Cells(bzeile, bspalte)
Cells(zeile + 1, bspalte + 1) = Cells(counter, 3)
Cells(zeile + 1, bspalte + 2) = Cells(bzeile, spalte + 1)

zeile = zeile + 1
spalte = spalte + 1
Loop
Else
zeile = zeile + 1
End If
spalte = 1
'Rückschritt und Ursprungszeile löschen
If zeile > counter Then
Cells(zeile - counter, spalte).EntireRow.Delete
Else
End If
Loop

cc = counter - 1
Rows(„1:“ & cc).Select
Selection.Delete Shift:=xlUp
Range(„A1“).Select: Selection = SpAUeschrift
Range(„B1“) = SpBUeschrift
Range(„C1“) = SpCUeschrift

End Sub

#4

Hallo Reinhard,

vielen Dank für deine Lösung, probiere ich gleich mal aus.
Eine Frage habe ich dazu: Diese festgelegten Namen Spa und Zei schreibe ich wohin? Oder muss ich jedes Mal die dafür stehende Formel einsetzen?

LG
Torsten

#5

Vielen Dank, Niclaus.

LG Torsten