Hallo Liebes Forum.
Ich habe mich hier angemeldet, da ich ein auf Tipps und Tricks aus dem Forum hoffe.
Bin schon länger und öfter hier als suchender unterwegs, aber nun brauche ich Hilfe.
Ich stehe vor einem Excel-Problem, zu dem ich eine Lösung benötige.
Ich habe eine Datenbank mit ca. 80.000 Zeilen.
Jede Zeile kann bis zu 20 Spalten mit Informationen haben.
Nun sollte ich die Spalteninhalte untereinander anordnen und dabei den Bezug zu einer Zelle bestehen lassen.
Ich probier das mal zu erklären:
Ausgangszustand:
A1 | B1 | C1 | D1 | E1 | F1 | ...
1 | 12 | 23 | 24 | 55 | 99 | ...
2 | 77 | 99 | ...
...
Sollzustand:
A1 | B1 | ...
1 | 12
1 | 23
1 | 24
1 | 55
1 | 99
2 | 77
2 | 99
...
Ich denke ohne VBA / Makro kommt man da nicht weit. Und da kenne ich mich leider nicht wirklich aus.
Es kann sein, dass die Felder ab D3 leer sind. Wenn leere Felder nicht übertragen werden, wäre das super. Ansonsten mache ich das nachträglich.
Kann hier jemand einen Ratschlag geben?
Ich weiß, dass Excel nur 1 mio und ein paar zerquetschte Zeilen verarbeiten kann..
Ich müsste in dem Fall das Ganze ein wenig Stückeln.
Vielen Dank bereits an alle, die mir hier helfen.
Gruß
Andi
Versuche mal die Funktion MTRANS().
Wenn du neuestes Excel hast, reicht sie wie sie daherkommt.
Ansonsten als Matrix-Formel eingeben (statt mit "Enter" mit "Strg+Shift+Enter" bestätigen).
Einfach den Bereich den du transponieren willst (das ist das was du mit dem Thread-Namen beschreibst) in der Formel verwenden (ist nur dieses eine Argument).
Wenn es mit den Zeilen eng wird, kannst du das auch in Power Query machen.
BG ruppy
Erläutere doch bitte mal, wieso du das überhaupt machen willst. Denn du hast ja bestehend eine Tabelle, die entsprechend den Regeln der Datenverarbeitung aufgebaut ist. Nun willst du sie aber "kaputtmachen". Wieso?
Außerdem bedenke, dass du ja nicht immer 20 Zeilen pro Datensatz bekommen wirst. Ergo ist es auch nicht mehr eindeutig, was die Werte aussagen. Meines Erachtens müsste nicht nur die Datensatznummer, sondern auch die Spaltenüberschrift mit übernommen werden.
Hallo ruppy,
MTrans hilft da wohl nicht. Die Daten sollen untreinander stehen ... ansonsten hätte ich schon Transformieren vorgeschlagen.
Gruß
Marcus
Hi marose67,
ok jetzt sehe ich es auch und besonders verstehe ich jetzt auch MisterBurns.
Finde auch das ist sinnfrei.
Habe es nur überflogen und dachte ok da muss transponieren gesucht sein.
Sorry für meinen Schnellschuss.
BG ruppy
Hallo,
unten zwei Prozeduren, einmal mit Prüfung auf Leerzellen und einmal ohne.
Teste mal. Du kannst für die Variable
lngLetzte einen festen Zeilenwert eintragen und mal schauen ob es das macht, was Du möchtest.
Schau Dir die Kommentare im Code an und passe an den entsprechenden Stellen die Tabellennamen an.
Ich habe mit 5000 Zeilen und 20 Spalten getestet und es war recht flott.
Vielleicht schaut ja snb noch vorbei und stellt etwas schnelleres ein.
Code:
Sub Zeile_tranponieren() 'ohne Prüfung auf leer
Dim i As Long, j As Long, k As Long
Dim lngLetzte As Long, lngSpalten As Long, lngGesamt As Long
Dim arr, outArr
lngSpalten = 20 'eingelen werden 20 Spalten
With Worksheets("Tabelle1") 'Datentabelle aus der eingelesen wird
lngLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range(.Cells(1, 1), .Cells(lngLetzte, lngSpalten))
lngGesamt = lngLetzte * lngSpalten
End With
With Worksheets("Tabelle4") 'Ausgabe Tabelle
.Cells.Clear 'alle Zellen werden geleert
outArr = .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) 'angenommener Schreibbereich leer einlesen
End With
k = 1
For i = 1 To lngLetzte
For j = 1 To lngSpalten
outArr(k, 1) = i
outArr(k, 2) = arr(i, j)
k = k + 1
Next j
Next i
'Ausgabe
With Worksheets("Tabelle4")
.Range(.Cells(1, 1), .Cells(lngGesamt, 2)) = outArr
End With
End Sub
Sub Zeile_tranponieren2() 'Mit Prüfung auf leer
Dim i As Long, j As Long, k As Long
Dim lngLetzte As Long, lngSpalten As Long, lngGesamt As Long
Dim arr, outArr
lngSpalten = 20 'eingelen werden 20 Spalten
With Worksheets("Tabelle1") 'Datentabelle aus der eingelesen wird
lngLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range(.Cells(1, 1), .Cells(lngLetzte, lngSpalten))
lngGesamt = lngLetzte * lngSpalten
End With
With Worksheets("Tabelle4") 'Ausgabe Tabelle
.Cells.Clear 'alle Zellen werden geleert
outArr = .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) 'angenommener Schreibbereich leer einlesen
End With
k = 1
For i = 1 To lngLetzte
For j = 1 To lngSpalten
If arr(i, j) <> "" Then
outArr(k, 1) = i
outArr(k, 2) = arr(i, j)
k = k + 1
End If
Next j
Next i
'Ausgabe
With Worksheets("Tabelle4")
.Range(.Cells(1, 1), .Cells(lngGesamt, 2)) = outArr
End With
End Sub
Moin!
Wenn ich das richtig sehe, handelt es sich um ein simples Entpivotieren einer Kreuztabelle.
Das ist mit PowerQuery mit wenigen Mausklicks möglich.
Ausgangssituation:
| A | B | C | D | E |
| 1 | Gruppe | Wert1 | Wert2 | Wert3 | Wert4 |
| 2 | 1 | 7 | 9 | | |
| 3 | 2 | 4 | 5 | 2 | |
| 4 | 3 | 5 | | | |
Ergibt:
| A | B | C |
| 1 | Gruppe | Attribut | Wert |
| 2 | 1 | Wert1 | 7 |
| 3 | 1 | Wert2 | 9 |
| 4 | 2 | Wert1 | 4 |
| 5 | 2 | Wert2 | 5 |
| 6 | 2 | Wert3 | 2 |
| 7 | 3 | Wert1 | 5 |
Spalte B kann jetzt ausgeblendet werden (oder bereits im PQ-Editor gelöscht werden)
Ich würde sie aber beibehalten, hilft sie doch bei weiteren Auswertungen.
Gruß Ralf
(06.02.2020, 14:12)MisterBurns schrieb: [ -> ]Erläutere doch bitte mal, wieso du das überhaupt machen willst. Denn du hast ja bestehend eine Tabelle, die entsprechend den Regeln der Datenverarbeitung aufgebaut ist. Nun willst du sie aber "kaputtmachen". Wieso?
Moin Berni!
Die vorliegende Kreuztabelle ist ja bereits ein "Endprodukt", welches weitere Auswertungen erschwert bzw. sogar unmöglich macht.
Die entpivotierte Fassung erlaubt hingegen weiterführende Auswertungen, ist also imo ausgesprochen sinnvoll.
Gruß Ralf
G1: =INDEX(A:E;ZEILE(A5)/5;REST(ZEILE(A5);5)+1) runterziehen, plattmachen, A:F löschen (bei fix 5 Spalten)
____________________________________________
Bei bis zu 20 Spalten:
V1: =INDEX(A:T;ZEILE(A20)/20;REST(ZEILE(A20);20)+1)
W1: =ISTLEER(V1)
X1: =KÜRZEN(ZEILE(A20)/20)
alle 3 runterziehen, plattmachen, A:U löschen. Nun nach Spalte B (ehem. W) sortieren.
Hier hat Mr. Burns recht, RPP63. Daher die Spalte X, die die Zeilennummer rettet.
@Ralf: Deswegen hatte ich nach dem Hintergrund gefragt :)
Die von mir angemerkte dritte Spalte ist aber essentiell, sonst sind die Werte vollkommen aussagelos.
Hallo zusammen.
Vielen Dank für eure Vorschläge und Ideen.
Heute Abend werde ich meinen Rechner neu aufsetzen und das alles testen.
Die "Datenbank" soll später als CSV für eine Webpage mit Produktsuche funktionieren.
Ich habe die Daten bereits in dem Ursprungsformat auf 90.000 Zeilen vorliegen.
Aussage von einem Webpage programmier ist, dass ein Suchen nach dem neuen Muster schneller und effizienter sein.
Da ich mich mit dem Programmieren, Datenbanken und so nicht suuuuper auskenne, wollte ich einfach mal die Datenbank in dem neuen Format bereitstellen.
Ich melde mich am Wochenende zurück und gebe feedback.
Dank nochmal an alle.