Office: (Office 2007) Mehrere Zeilen in eine Spalte transponieren

Helfe beim Thema Mehrere Zeilen in eine Spalte transponieren in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, ich habe eine Tabelle mit mehrzeiligen Daten. Das sieht Beispielweise so aus: Tag1 12 3 4 5 Tag2 6 3... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von stern20, 20. September 2015.

  1. stern20 Neuer User

    Mehrere Zeilen in eine Spalte transponieren


    Hallo zusammen,

    ich habe eine Tabelle mit mehrzeiligen Daten.

    Das sieht Beispielweise so aus:

    Tag1 12 3 4 5
    Tag2 6 3
    Tag3 5 7 8 9 10
    Tag4 3 4 5
    ich möchte, dass es aber so aussieht, d.h alle Daten in eine Spalte:
    Tag1
    12
    3
    4
    5
    Tag2
    6
    3
    Tag3
    5
    7
    8
    9
    10
    usw..
    Wie kriege ich es in Excel hin?

    Danke für Eure Hilfe!
     
  2. ThekesC Erfahrener User
    Hi,
    Zeilen unter dem Tag einfügen bis genug Platz vorhanden ist, dann:
    Daten (zb. 12, 3, 4, 5) kopieren und dann oben links auf "Start" -> "Einfügen (Pfeil)" -> Transponieren.
     
  3. Exl121150 Erfahrener User
    Hallo,

    ich habe dir in der beiliegenden Excel-Datei 3 verschiedene Varianten eingebaut:

    1. Version mit Excel-Arbeitsblattfunktionen unter Verwendung einer Hilfsspalte:
      a) Im Zellbereich A3:I8 befindet sich dein Demo-Datenbestand mit variabel langen Zeilen.
      b) Im Zellbereich J3:J9 neben dem Datenbestand befindet sich eine Hilfsspalte, die die Dateneinträge des Datenbereichs durchzählt. Im Bereich L3:L9 habe ich dir die in J3:J9 enthaltenen Formeln im Klartext eingefügt.
      c) Im Zellbereich A12:A39 befindet sich dein gewünschter umgewandelter Datenbereich. Im Bereich C12:C39 habe ich dir die in A12:A39 enthaltenen Formeln im Klartext eingefügt.
    2. Nummerierte Liste (Benutzerdefinierte VBA-Funktion)
      Ich habe dir ferner im allgemeinen Codemodul von VBA eine Funktion programmiert, die im Prinzip eine nummerierte Liste darstellt. Im Bereich M17:M38 und im Bereich O17:R26 habe ich dir 2 Anwendungsbeispiele eingefügt, die sich jeweils auf die Daten aus Punkt 1a) beziehen.
      In Zelle M17 habe ich die Formel =TRANS_Dicht($A$3:$I$8;ZEILE(A1)) eingetippt und diese einfach nach unten gezogen bis zur Zelle M38. Dabei sorgt jeweils die Funktion ZEILE(A1)...ZEILE(A22) für die Nummerierung des Eintrages, der aus dem Datenbereich aus Punkt 1a) angezeigt werden soll.
    3. Matrixfunktion (Benutzerdefinerte VBA-Funktion)
      In den Bereichen O29:Q38 bzw. S29:T38 bzw. V18:V38 habe ich 3 Beispiele für die Matrixfunktion eingefügt. Dazu muss man zuerst den betreffenden Bereich markieren, dann in die Formeleditierzeile die Formel eingeben (zB: =MTRANS_Dicht(A3:I8;22;1) und diese Formeleingabe nicht mit der EINGABE-Taste abschließen, sondern mit der Tastenkombination Strg+Umschalt+EINGABE, sodass dann die Formel in geschweiften Klammern eingeschlossen erscheint.
      Die beiden Zahlen nach der Quellbereichsangabe bedeuten die Zeilen- und Spaltenanzahl der Matrix, die für den Zellbereich der Formel vorgesehen wird.
    Anmerkung:
    Die Funktionen aus Punkt 1) mögen es nicht, wenn im Datenbereich aus Punkt 1a) Löcher enthalten sind, also wenn die Daten nicht von links her geschlossen aufgereiht sind pro Zeile.
    Den Funktionen aus Punkt 2) bzw. 3) ist das egal - die kommen auch mit Datenlöchern zurecht.
     
    Zuletzt bearbeitet: 21. September 2015
    Exl121150, 21. September 2015
    #3
  4. Mehrere Zeilen in eine Spalte transponieren

    Hallo zusammen,

    ich weiß nun nicht, ob der Thread noch aktiv ist. Aber ich versuch`s mal!
    Ich habe eine ähnliches Ausgangsproblem wie Stern20, aber einen größeren Datenbereich:

    Folgendes Setting
    24 Stundenwerte in einer Zeile (=24Spalten) und 365 Zeilen (=Jede Zeile ist ein Tag), somit insgesamt 8760 Werte (also Jahresstundenwerte). Diese Werte sollen nun ALLE in EINE Spalte geschrieben werden. Habe mir dann dazu die Ausführungen von Exl121150 (Anton Exl)
    zum Spezialtransponieren angeschaut und die Formel für meinen Fall wie folgt umgeschrieben: {=MTRANS_Dicht(B2:Y366;8760;1)}. Leider taucht dann in jeder der 8760 Zellen die Meldung "#NAME?" auf. (Ich hatte vor Eingabe der Formel den Zielbereich entsprechend markiert).


    Weiß hier jemand (Anton Exl?) Rat? Würde mir sehr weiterhelfen!! Besten Dank schon mal vorab.
    Mit vorweihnachtlichen Grüßen, Frank

    Nutze Version Office Professional Plus 2016
     
    Frank Brehm, 15. Dezember 2017
    #4
  5. lupo1
    lupo1 Tutorial Guru
    AA1[:AA8760]: =INDEX(A:X;ZEILE(A24)/24;REST(ZEILE(A24);24)+1) (für individuelle Inhalte) oder
    AA1[:AA8760]: =(ZEILE(A1)-1)/24+"1.1" (für die Zeiten an sich)
     
    Zuletzt bearbeitet: 15. Dezember 2017
  6. Exl121150 Erfahrener User
    Hallo,

    da du eine fixe Spaltenanzahl für die Daten verwendest, ist die EXCEL-Formellösung von Lupo1 völlig ausreichend.

    Der Ergebniswert "#NAME?" tritt in Excel - egal ob Arbeitsblattfunktion oder VBA-Funktion - immer dann auf, wenn Excel auf einen unbekannten (Funktions)namen trifft. Da ich in die Arbeitsmappe 2 benutzerdefinierte VBA(Array-)Funktionen eingebaut habe, kann das nur heißen, dass du die Makrofunktionalität nicht aktiviert hast.

    Man kann Makros auf verschiedene Arten aktivieren je nachdem, was zuvor in den Excel-Optionen diesbezüglich eingestellt ist/war:
    -Menüband > Tabulator "Datei" > Befehl "Optionen"
    -Dialog "Excel-Optionen" > Auswahl "Trust Center" > Button "Einstellungen für das Trust Center..."
    -Dialog "Trust Center" > Auswahl "Makroeinstellungen" > Makroeinstellungen: Hier darf nicht der 1. Radiobutton ("Alle Makros ohne Benachrichtigung deaktivieren") ausgewählt sein, sondern einer der 3 folgenden Radiobuttons (am besten der 2. Radiobutton "Alle Makros mit Benachrichtigung deaktivieren").

    Ist dieser 2. Radiobutton selektiert, erhältst du unmittelbar nach dem Öffnen der Arbeitsmappe je 1x oder 2x eine Aufforderung, per Button-Klick die enthaltenen Makros zu aktivieren/zu deaktivieren. Solange du die Makros nicht explizit aktivierst (durch Klicken auf den Button), solange sind sie auch nicht aktiv. Die Folge ist, dass natürlich auch die in ihnen zwar enthaltenen und von dir verwendeten (Funktions)namen Excel unbekannt sind --> Funktionsergebnis "#NAME?".
     
    Exl121150, 15. Dezember 2017
    #6
  7. Hallo Lupo1 und Exl121150,

    gerade geantwortet und abgeschickt, als ich die Meldung erhielt, dass System mich abgemeldet hat und Text futsch war ;-((

    erstmal danke bisher. War gerade dabei Lupos Ansatz aufzugreifen, als Antons Meldung kam. Werde deshalb erstmal mit der MTRANS-funktion fortfahren (obwohl ich die andere auch probieren werde).
    Die Einstellungen für Makros im Trust-Center sind so wie Du sie empfohlen hast.
    Vielleicht liegt es daran: Ich habe aus Stern20-Beispielsdatei lediglich die Formel übernommen und für meine Tabelle angepasst! Hätte ich hier das VBA-Skript irgendwie vorher in meine Datei kopieren müssen (Habe leider keine Ahnung von VBA)?

    Schöne Grüße
    Frank
     
    Frank Brehm, 15. Dezember 2017
    #7
  8. Mehrere Zeilen in eine Spalte transponieren

    hi,
    habe deinen INDEX-Befehl versucht. Bei der ersten Formel habe ich für den Bereich "A:X" sowohl nur die erste Zeile (in meinem Falle B2:Y23) als auch die gesamte Matrix (B2:Y366) probiert und in beiden Fällen führt das dazu, dass er mir die Werte, wenn ich Zelle an der Ecke anfasse und nach untenziehe, wie folgt auswirft:
    1.Stunde des 1.Tages
    2.Stunde des 2.Tages
    3.Stunde des 3.Tages
    ...

    Bei der zweiten Formel wirft er mir Ergebnisse aus, die ich mir überhaupt nicht erklären kann!?

    Nicht klar war für mich auch, was du mit "für individuelle Inhalte" und "für die Zeiten an sich" meinst?
    Vielleicht habe ich mich mißverständlich ausgedrückt: Was ich erzeugen will, ist eine Spalte mit allen Werten für die jeweilige Stunde untereinander:
    Zelle1: 1.Stunde des 1.Tages
    Zelle2: 2.Stunde des 1.Tages
    ..
    Zelle 8760: 24.Stunde des 365.Tages des Jahres

    Schöne Grüße
    Frank
     
    Frank Brehm, 15. Dezember 2017
    #8
  9. Hallo Exl121150,

    meine Antwort ist leider in der Moderator-Überprüf-Schleife gelandet (wohl den falschen Antworten-Knopf gedrückt). Ich weiß nicht, wie lange das dauert. Soviel schon mal vorab: Die Einstellungen sind so wie Du sie empfohlen hast. Hätte ich mglw noch das VBA-Script mit in meine Datei übernehmen müssen und nicht einfach nur die Formel? (Leider keine VBA-Ahnung ;-(

    Schönes WE, Frank
     
    Frank Brehm, 15. Dezember 2017
    #9
  10. Exl121150 Erfahrener User
    Hallo Frank,

    wenn du eine benutzerdefinierte Funktion im Arbeitsblatt verwendest, dann musst du auch zuerst vorsorgen, dass diese auch vorhanden und verfügbar ist. Denn so ein Computer ist kein Wunderwuzzi, der von sich auch wissen muss, was du (machen) möchtest. Und so wird mir auch klar, warum du als Funktionsresultat "#NAME?" erhältst: Eine nicht installierte Funktion, die trotzdem im Arbeitsblatt verwendet wird, muss zwangsläufig Excel namentlich unbekannt sein.

    In deinem Fall wird es daher am besten sein, wenn du meine Funktionen vergisst und die Funktion, so wie sie Lupo1 vorgeschlagen hat, verwendest. Du musst sie eben nur ein bisschen anpassen.
    Dein Datenbereich (mit fixen Zeilenlängen) befindet sich offenbar im Zellbereich B2:Y366. Dieser rechteckige Bereich besitzt somit 365 Zeilen und 24 Spalten. Lupo1 ist von einem Bereich A:X ausgegangen, der somit 24 (ganze) Spalten umfasst - und somit (fast) beliebig viele Zeilen besitzt.

    Die Anpassung seiner Formel sieht in deinem Fall so aus:
    =INDEX($B$2:$Y$366;ZEILE(A24)/24;REST(ZEILE(A24);24)+1)
    1) Diese Formel gibst du exakt so in die 1. Zelle deines Zielbereiches ein (zusammen mit den 4 Dollarzeichen in der Bereichsangabe nach der INDEX-Funktion).
    2) Diese Formel kannst du dann in die restlichen 24*365-1 = 8759 Zellen hinunterziehen.

    Die 4 Dollarzeichen in der Formel $B$2:$Y$366 bewirken, dass aus dem Zellbereich B2:Y366, der ein relativer Zellbereich ist, ein absoluter Zellbereich wird. Ein relativer Zellbereich passt sich beim Kopieren der Formel automatisch relativ an, während ein absoluter Zellbereich auch beim Kopieren völlig unverändert bleibt. Das "Hinunterziehen" der Formel in Punkt 2) oben ist ebenfalls nur ein Formelkopieren. Und das war genau dein Problem mit der Formel von Lupo1.
     
    Zuletzt bearbeitet: 19. Dezember 2017
    Exl121150, 19. Dezember 2017
    #10
  11. Hallo Anton,
    hat bestens funktioniert! Herzlichen Dank und schöne Weihnachtstage!
    Frank
     
    Frank Brehm, 21. Dezember 2017
    #11
Thema:

Mehrere Zeilen in eine Spalte transponieren

Die Seite wird geladen...
  1. Mehrere Zeilen in eine Spalte transponieren - Similar Threads - Zeilen Spalte transponieren

  2. Zeilen Nummern gleicher Werte einer Spalte ausgebenn

    in Microsoft Excel Hilfe
    Zeilen Nummern gleicher Werte einer Spalte ausgebenn: Hallo, Ich habe eine "Tabelle1" mit variabel vielen Zeilen, es können durchaus mehr als tausend sein. Wie kann ich die Zeilennummern gleicher Werte auslesen und in eine andere "Tabelle 2"...
  3. Filter: Spalten statt Zeilen

    in Microsoft Excel Hilfe
    Filter: Spalten statt Zeilen: Hallo zusammen, die Filterfunktion ist mir bekannt. Ich habe in der Überschrift die Möglichkeit nach Kriterien zu filtern. Soweit OK. Es geht um den Tarifdschungel der schweizer Bundenbahn...
  4. Spalte 2 bis n jeweils in Zeilen transponieren (zur wiederholten Spalte 1)

    in Microsoft Excel Tutorials
    Spalte 2 bis n jeweils in Zeilen transponieren (zur wiederholten Spalte 1): a 1 3 5 b 2 4 6 c 7 8 9 wird mit =LET(x;A1:D3;y;SPALTEN(x)-1;z;ZEILEN(x);a;SEQUENZ(z*y;;1;1/y);HSTAPELN( INDEX(x;a;1); INDEX(x;a;REST(SEQUENZ(z*y;;y);y)+2))) zu a 1 a 3 a 5 b 2 b 4 b 6 c 7 c 8 c...
  5. Transponieren (Vertauschen) von Daten aus Spalten in Zeilen oder umgekehrt

    in Microsoft Excel Tutorials
    Transponieren (Vertauschen) von Daten aus Spalten in Zeilen oder umgekehrt: Transponieren (Vertauschen) von Daten aus Spalten in Zeilen oder umgekehrt Excel für Microsoft 365 Excel für das Web Excel 2019 Excel 2016 Excel 2013...
  6. Transponieren von Daten aus Zeilen in Spalten (oder umgekehrt) in Excel für Mac

    in Microsoft Excel Tutorials
    Transponieren von Daten aus Zeilen in Spalten (oder umgekehrt) in Excel für Mac: Transponieren von Daten aus Zeilen in Spalten (oder umgekehrt) in Excel für Mac Excel für Microsoft 365 für Mac Excel 2019 für Mac Excel 2016 für Mac Mehr......
  7. Spalten in Zeilen transponieren dabei aber Zuordnung bei behalten

    in Microsoft Excel Hilfe
    Spalten in Zeilen transponieren dabei aber Zuordnung bei behalten: Hallo, vielleicht kann mir hier ja jemand helfen oder sagen, dass es mit excel nicht geht. Ich habe eine Tabelle in der Form, dass in den Zeilen immer der Familienname steht also bspw. in A1...
  8. Excel Einträge in einer Spalte auf Zeilen verteilen, nicht transponieren

    in Microsoft Excel Hilfe
    Excel Einträge in einer Spalte auf Zeilen verteilen, nicht transponieren: Hallo zusammen, ich habe eine Tabelle mit ganz vielen Zahlenwerten in einer Spalte. Das sieht sagen wir so aus: 1 2 3 4 5 6 ... Soll aber so aussehen: 1 4 ... 2 5 3...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden