Registriert seit: 06.02.2020
Version(en): 2016
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
Registriert seit: 03.02.2020
Version(en): Office 365 Professional Plus
06.02.2020, 13:02
(Dieser Beitrag wurde zuletzt bearbeitet: 06.02.2020, 13:02 von ruppy.)
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
Registriert seit: 02.05.2018
Version(en): Excel 365 & 2016
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.
Schöne Grüße
Berni
Registriert seit: 11.04.2014
Version(en): Office 2003 - Office 2007 - Office 365
Hallo ruppy,
MTrans hilft da wohl nicht. Die Daten sollen untreinander stehen ... ansonsten hätte ich schon Transformieren vorgeschlagen.
Gruß
Marcus
Wissen ist Macht - es ist aber nicht schlimm nicht alles zu wissen.
Man muss nicht alles wissen - man muss nur wissen wo es steht, oder wo man Hilfe bekommt.
Registriert seit: 03.02.2020
Version(en): Office 365 Professional Plus
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
Registriert seit: 14.04.2014
Version(en): 2003, 2007
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
Gruß Atilla
Registriert seit: 12.10.2014
Version(en): 365 Insider (32 Bit)
07.02.2020, 07:13
(Dieser Beitrag wurde zuletzt bearbeitet: 07.02.2020, 07:14 von RPP63.)
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
Gib einem Mann einen Fisch und du ernährst ihn für einen Tag.
Lehre einen Mann zu fischen und du ernährst ihn für sein Leben. (Konfuzius)
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
07.02.2020, 08:12
(Dieser Beitrag wurde zuletzt bearbeitet: 07.02.2020, 08:12 von LCohen.)
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.
Registriert seit: 02.05.2018
Version(en): Excel 365 & 2016
@Ralf: Deswegen hatte ich nach dem Hintergrund gefragt :)
Die von mir angemerkte dritte Spalte ist aber essentiell, sonst sind die Werte vollkommen aussagelos.
Schöne Grüße
Berni
Registriert seit: 06.02.2020
Version(en): 2016
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.
|
