Excel Tabelle in eine lange Zeile verwandeln?
Hallo Ich habe eine lange Liste mit Namen und zugehörigen Daten die momentan in einer Tabellen untereinander stehen.
Ich möchte die Tabelle so umändern das nachher alle Daten die zu einem Namen gehören in einer Zeile hintereinander stehen. Also transponieren hilft da leider nicht, weil dann die Daten auch untereinander stehen, will eine lange foltlaufende Liste in einer Zeile. Kann mir jemand einen Tipp geben wie man das bei Excel macht.
4 Antworten
Wie ist definiert, was zu einem Namen gehört?
Wenn es feste Abstände sind, ginge es wie im Bild gezeigt.
A1 bis A... stehen die Informationen unter einander.
Ab B1
=WENN(REST(ZEILE(B1);4)=1;INDEX($A$1:$A$1000;ZEILE(B1)+SPALTE(B1)-2;1);"")
Anschließend kopieren, als Werte einfügen und die leeren Zeilen über den Filter sortieren / löschen
Die Abfrage von rest(zeile(b1);4)=1 sorgt dafür, dass nur jede 5 Zeile Einträge erhält. Mit Spalte (B2)-2 kannst Du die Formel einfach in die anderen Spalten ziehen und musst manuell nichts in der Formel anpassen
Wenn es kein fester Aufbau ist, dann hilft diese Lösung nix. Wenn es mehr oder weniger als meine verwendeten 4 Angaben sind (Name, Str, PLZ, Ort) muss man die fettgedruckte 4 ändern.
Das ist jetzt ein ziemliches Gewurstel und funktioniert nur, wenn Du in D2 anfängst mit der Formel. Diese dann in die anderen Spalten und Zeilen ziehen.
=WENN(UND($A2<>$A1;(SPALTE(D1)/2)-2<ZÄHLENWENN($A$1:$A$1000;$A2));INDEX($B$2:$C$1000;ZEILE(D1)+ABRUNDEN(SPALTE(D2)/2;0)-2;REST(SPALTE(D1);2)+1);"")
Ich weiß gar nicht, ob ich nachvollziehbar die Formel erklären kann
Bedingung, dass nicht "" kommt ist, dass es die erste Zeile eines Namens ist (a2<>a1) und die Anzahl der Spalten (Spaltenpärchen) nicht größer ist, als die Anzahl der Einträge zu einem Namen
Aus dem Bereich B2:C1000 wird dann mit jeder zweiten Spalte eine Zeile weiter nach unten gelesen und abwechselnd Spalte 1 oder 2 gelesen, je nachdem ob gerade oder ungerade Spalte.
Allerdings: Für künftige Auswertungen und Berechnungen ist es eine grausige Idee gleiche Daten (hier Datum und Artikel) in verschiedenen Spalten stehen zu haben. Ich weiß nicht, was Du mit der Tabelle vorhast, also warum Du die Daten nebeneinander stehen haben möchtest. Wenn es nur gemacht wird, um ein anderes Berechnungsproblem zu lösen, gibt es sicherlich bessere Möglichkeiten als die Daten umzukopieren.
Ich habe die Formel etwas geändert. Sie funktioniert jetzt für vier Spalten, wobei B, C und D übernommen werden.
Bei noch mehr Spalten, wären die fettmarkierten Zahlen jeweils um eins zu erhöhen und der Bereich zu erweitern (A:D, A:E etc)
Weiterhin wichtig:
Erste Zeile ist leer
Die Formel muss unverändert in die ersten Zelle des Ergebnisbereichs kopiert werden.
Bei vier Spalten gehört dann in E2
=WENN(UND($A2<>$A1;(SPALTE(A2)/3)<ZÄHLENWENN($A$1:$A$1000;$A2));INDEX($B$2:$D$1000;ABRUNDEN((ZEILE(A2)+(SPALTE(A2)+2)/3);0)-2;REST(SPALTE(A2)+2;3)+1);"")
Danach rüberkopieren in die Spalten und Zeilen.
Falls es zu Irritationen führt, dass die Formel in E2 steht, aber
die Abfragen auf A2 verwendet werden:. Das ist bewusst so, denn E ist zwar die 5. Spalte, wenn man das ganze Tabellenblatt betrachtet - innerhalb des Ergebnisbereichs ist es die 1. Spalte. Dafür liefert spalte(a1) eben das richtige Ergebnis. Man könnte auch spalte(E1)-4 nehmen, macht es nicht einfacher.
Darum auch bei künftigen Erweiterungen wichtig, dass in der ersten Zelle die Formel mit A2 steht.
Funktionsweise:
Im wesentlich geht es um einen Zugriff über Index. Einfach sähe das z.B. so aus:
=INDEX(B2:D10;1;1) aus dem Bereich B2:D10 wird Zeile 1, Spalte 1 gelesen. Das kannst Du in Deiner Tabelle leicht nachstellen.
Die Zeilen und Spaltenangabe muss aber variabel werden:
Mit jeder Spalte, die die Formel nach rechts wandert muss eine andere Zelle ausgelesen werden.
E2 liest das Ergebnis von B2
F2 das aus C2 und
G2 das aus D2.
Danach muss die Zeile um eins erhöht werden und wieder zurück in Spalte B angefangen werden.
Die Formel holt sich über zeile(A2) und spalte(A2) die Informationen, in welcher Zelle sie selbst steht. Zeile(A2) liefert als Ergebnis 2 (2. Zeile eben), Spalte(A2) eine 1.
Mit dem Runden und Rest erreicht man, dass sich der Wert nicht 1:1 erhöht, wenn man die Formel in eine andere Spalte zieht, sondern nur anteilig.
=REST(SPALTE(A2)+2;3)+1) liefert, wenn man es von links nach rechts zieht immer 1,2,3,1,2,3
Das ist der Zugriff auf die Spalten, immer abwechseln die 1.,2.,3. Spalte aus dem Bereich (B/C/D)
Dieses +2, +1 usw. ist dann Gefummel, damit beim Wechseln der Spalten / Zeilen die gewünschten Werte entstehen.
Der Zugriff auf die Zeilen ist ähnlich, nur dass hier drei Mal der gleiche Wert ermittelt werden muss (für die drei Spalten B, C, D die übernommen werden) und dann den Wert um 1 erhöhen:
=ABRUNDEN((ZEILE(A2)+(SPALTE(A2)+2)/3);0)-2
wenn du das in den Spalten rüberziehst kommt 2,2,2,3,3,3,4,4,4
Beides zusammen, also Ermittlung der Zeile und der Spalte bewirkt dann dieses Muster, dass immer drei Spalten aus einer Zeile nebeneinanderstehen und danach die nächste Zeile ausgelesen wird.
Die Wenn-Bedingungen zu Beginn entscheiden, ob überhaupt ein Eintrag erfolgt. Der soll ja nur in der ersten Zeile eines neuen Namens erfolgen (a1<>a2 ist dann, wenn erstmals ein neuer Name erscheint)
Wenn man die Formel nach rechts zieht und sich dadurch die Zeilen beim Auslesen des Index immer weiter erhöhen, würde man irgendwann die Daten des nächsten Namens erwischen. Also braucht man eine Begrenzung
=(SPALTE(A1)/3)<ZÄHLENWENN($A$1:$A$1000;$A2))
Zählenwenn ermittelt, wieviele Einträge es zu einem Namen gibt, z.B. 3. Je Eintrag werden drei Spalten geschrieben (B, C, D). Also 9 Spalten können gefüllt werden. Wenn die Formel in weiteren Spalten steht, darf hier kein Ergebnis mehr angezeigt werden, denn das gehört schon zum nächsten Namen.
Ich habe gerade auf diese Frage hier geantwortet:
https://www.gutefrage.net/frage/wie-kann-ich-in-excel-viele-zellen-einer-spalte-miteinander-verketten-gibt-es-die-moeglichkeit-bestimmte-bereiche-automatisch-zu-einer-zelle-zusammenzufuegen?foundIn=list-answers-by-user#answer-223973196
Das ist ein ähnliches Problem.
Hilft Dir das vlt. schon weiter?
Sonst beschreibe mal den Aufbau Deine Tabelle genauer oder - noch besser - lade ein Beispiel hoch.
Danke ich versuchs erst Mal mit deiner Antwort. Wie kann ich hier denn Bilder hochladen? Bin neu hier :)
Das kannst Du nur in einer Antwort auf Deine eigene Frage.
Weise dann hier darauf hin, damit ich/ wir es bemerke(n).
Beispieltabellen kannst Du nur bei einem Uploadservice hochladen und hier verlinken.
Ich nehme mal an Du meinst Zelle, nicht Zeile?!
Dann mache es erstmal so, dass sie in einer Zelle stehen (als Werte).
Anschließend kannst Du dann diese Spalte markieren und mit:
Daten/ Text in Spalten/ Getrennt/ Trennzeichen; Komma/ Fertig stellen
diese Spalte in mehrere aufteilen.
Damit es keinbe Missverständnisse gibt: Willst Du den Namen und alle Daten aneinandergehängt in EINER ZELLE stehen haben oder in verschiedenen Zellen in einer ZEILE?
Geht beides, sind aber unterschiedliche Lösungsansätze, und weil beides recht aufwändig ist, frag ich vorher!
Da Du ausser der Namensspalte nur 2 Spalten auszulesen hast, schlage ich eine ganz konventionelle Lösung vor:
Erst mal Bestimmst Du die maximale Häufigkeit eines Namens mit der Formel: {=MAX(ZÄHLENWENN(A:A;A:A))}
Achtung, Matrixformel! Nicht mit Enter eingeben, sondern mit Strg+Shift+Enter (ohne geschweifte {Klammern} und bei jeder Korrektur wieder! Nimm die rechten Tasten, dann gehts mit 1 Hand und lässt sich leichter merken))
Die Formel findet das maximale Auftreten eines Namens. Du schreibst was von 15x, dann musst Du 30 Formeln schreiben. Ist zwar Arbeit, aber die automatische Erstellung ist auch viel Arbeit und bleibt lange Zeit undurchsichtig.)
Nun schreibst Du in D2 diese Formel
=WENN(UND($A2<>$A1;A3=A2);$B3;".")
und in E2: =WENN(UND($A2<>$A1;A3=A2);$C3;".")
In F2 dann: =WENN(UND($A2<>$A1;A4=A2);$B4;".")
und in G2: =WENN(UND($A2<>$A1;A4=A2);$C4;".")
erkennst Du schon die Systematik? wenn noch nicht,
dann in H2: =WENN(UND($A2<>$A1;$A5=$A2);$B5;".")
und in i2: =WENN(UND($A2<>$A1;$A5=$A2);$C5;".")
in Worten: Nach einem Namenswechsel ist zu prüfen, ob der Name 1, 2, 3,... Zeilen darunter noch derselbe ist. Wenn ja, dann hole die Daten aus 1. 2, 3., ... Zeilen darunter. Das Ganze für 15 Zeilen => 30 Spalten.
Nun markierst Du D2:Enderechts und "ziehst"(kopierst) die Formel nach unten, über die gesamte Tabellenlänge. Es sind jetzt immer nur die Zeilen des ersten Auftretens des Namens gefüllt, sonst stehen überall Punkte.
Nun markierst Du den gesamten Formelbereich, kopierst ihn und fügst ihn an selber Stelle ALS WERTE wieder ein.
Nun musst Du noch die überflüssigen Zeilen loswerden:
dazu fügst Du vor Spalte A eine neue ein und schreibst ins neue A2:
=B1<>B2
das ergibt bei jedem Wechsel ein WAHR, sonst lauter FALSCH.
Spalte wieder kopieren und ALS WERTE wieder an selber stelle einfügen, dann die gesamte Tabelle nach dieser Spalte sortieren.
Die gesammelten FALSCH-Zeilen kannst Du nun löschen, übrig bleiben die relevanten WAHR-Zeilen. Danach kannst Du Spalte A wieder löschen.
Sollte Deine Tabelle doch komplizierter sein, v. allem mehr DatenSpalten enthalten, dann melde Dich und schreib aber, wieviele Spalten ausgewertet werden müssen (mit Spaltenbuchstaben-Angabe der Datenspalten, der Name in A bleibt ja einmalig je Zeile)
Dann lohnt sich nämlich doch die automatisierte Formelerstellung.
wahrscheinlich hast Du Dein Problem mithilfe von Nonombre schon gelöst. Ich stelle aber meine Lösung trotzdem hier ein:
Nachdem Du obige Formel fürs maximale Vorkommen ausgewertet hast, deaktiviere sie (zB ' vors = setzen), sie verzögert die Berechnung zu lange.
A: Namen; B:Datum; C:Objekt; Erste Namensazeile:2
Für weitere Spalten müssten die Formeln angepasst werden.
D2: =WENN(UND($A2<>$A1;BEREICH.VERSCHIEBEN($A2;GANZZAHL(SPALTE()/2)-1;0)=$A2);BEREICH.VERSCHIEBEN($B2;GANZZAHL(SPALTE()/2)-1;0);0)
E2: =WENN(UND($A2<>$A1;BEREICH.VERSCHIEBEN($A2;GANZZAHL(SPALTE()/2)-1;0)=$A2);BEREICH.VERSCHIEBEN($C2;GANZZAHL(SPALTE()/2)-1;0);0)
D2:E2 gemeinsam markieren, nach rechts kopieren sooft nötig, dann alle Formeln nach unten kopieren über die gesamte Listenlänge.
Alle Zweit- und höheren Zeilen von Namen müssen nun 0 enthalten, die Daten stehen in den Spalten der ersten Namensnennung soweit wie vorhanden
Die Anweisung zum Löschen der nun Überflüssigen kannst Du nun übernehmen.
Für 3 Datenspalten werde ich heute abend noch die Formeln anpassen, muss jetzt weg
jetzt für 3 Datenspalten (B, C, D) nach der Namensspalte A:
Die Formeln weichen im Ganzzahl()-Teil ein wenig ab i.Vgl zu denen mit 2 Datenspalten, untereinander unterscheiden sie sich lediglich durch B, C, D vor dem letzten Ganzzahl:
in E2: =Wenn(Und($A2<>$A1;Bereich.Verschieben($A2;Ganzzahl((Spalte()-2)/3);0)=$A2);Bereich.Verschieben($B2;Ganzzahl((Spalte()-2)/3);0);"0")
in F2: =Wenn(Und($A2<>$A1;Bereich.Verschieben($A2;Ganzzahl((Spalte()-2)/3);0)=$A2);Bereich.Verschieben($C2;Ganzzahl((Spalte()-2)/3);0);"0")
in G2: =Wenn(Und($A2<>$A1;Bereich.Verschieben($A2;Ganzzahl((Spalte()-2)/3);0)=$A2);Bereich.Verschieben($D2;Ganzzahl((Spalte()-2)/3);0);"0")
E:G markieren, als Dreierblock nach rechts kopieren soweit nötig
Anschließend Formeln nach unten kopieren
Stören Dich die vielen Nullen, kannst Du anstatt "0" auch "" schreiben. Dann siehst Du aber nicht mehr, wo eine Formel drinsteht.
Ich bevorzuge eine andere Lösung: ein Ben-def Zahlenformat:
Standard;[rot]-Standard;[magenta]°;@
(erster Abschnitt: positive Zahl, zweiter negative, dritter 0, vierter Text)
Das zeigt Dir statt der großen schwarzen 0 einen unauffälligen °-Kringel in magenta, der aber anzeigt, dass in der Zelle etwas steht, was 0 ergibt, hier eine Formel. (nur bei glatter 0, die kannst Du dadurch sogar von einer gerundeten 0 unterscheiden)
sehe grade, dass ich die jeweils letzte 0 irrtümlich als Label geschrieben hab. Mach das ohne Gänsefüßchen, sonst greift die Ben-def Formatierung nicht. Oder ersetze die Text-0 durch ein anderes unauffälliges Zeichen, zB "-" oder "٠" oder "∙". Sind dann aber alles Texte. Wenn das eine Auswertung stört, musst Du eine Zahl-0 schreiben und ggf das Ben-def. Format mit so einem Zeichen gestalten, sollte der °-kringel immer noch zu groß sein und den Anblick/die Übersicht stören..
Kannst auch als Farbe anstatt [Magenta] [Farbe38] (ist blasses Magenta, 39 (blasses Blauviolett) oder 15 (hellgrau) nehmen oder weitere (1..56) ausprobieren, (letzteres ist aber den Zeitaufwand nicht wert.)
Das zweite: Daten in verschiedenen Zellen aber in einer Zeile
Ich denke, du suchst VERKETTEN.
https://support.office.com/de-de/article/VERKETTEN-Funktion-8f8ae884-2ca8-4f7a-b093-75d702bea31d
Kommt aber auch drauf an, wie die Tabelle aufgebaut ist.
Willst du aber die Zellen nach unten in die Zellen nach rechts eintragen, dann kommt eben in B1 ein "=A2", in C1 ein "=A3" und so weiter.
Kommt eben auch drauf an, was du denn nun genau willst und wie die Tabelle aussieht.
Also ich glaube das ist schon der richtige Ansatz, in meiner Tabelle gibt es 3 Spalten nebeneinander und ca. 15 Zeilen darunter die ich dann in eine lange Zeile verwandeln will wo abwechselnd die jeweiligen Spalten kommen.
Vorher
[URL=http://www.directupload.net/file/d/4499/w8izzdpx\_png.htm][IMG]http://fs5.directupload.net/images/161005/temp/w8izzdpx.png[/IMG][/URL]
Nachher
[URL=http://www.directupload.net/file/d/4499/xazhnd4b\_png.htm][IMG]http://fs5.directupload.net/images/161005/temp/xazhnd4b.png[/IMG][/URL]