Microsoft Excel

Herbers Excel/VBA-Archiv

Verketten von Spalten

Betrifft: Verketten von Spalten von: Quattro
Geschrieben am: 22.09.2014 20:37:33

Hallo,

ich bin eher so ein Excelanfänger und habe folgende Problemchen.
Ich möchte aus einer Reihe mit sagen wir 3000 Spalten 1000 Spalten machen

Vorname-1|Name-1 |Wohnort-1|Vorname-2|Name-2|Wohnort-2 |...
Herbert |Grönemeyer|Bochum |Marius |Müller|Westerwald|...

Adressdaten-1 |Adressdaten-2 |...
Herbert Grönemeyer Bochum |Marius Müller Westerwald |...

Denke Bild verdeutlicht was ich möchte.
Ginge manuell mit Verketten(A1;B1;C1)|Verketten(D1;E1;F1), aber
manuell ist mir das zu aufwendig und beim Markieren und Ziehen mit der Maus erkennt Excel nicht das Muster A1,B1,C1|D1,E1,F1|G1,..

Liebe Grüsse

Michael


  

Betrifft: Da hast du mehrere Möglichkeiten, ... von: Luc:-?
Geschrieben am: 23.09.2014 02:51:00

…Michael,
je nachdem, wie du vorgehen willst bzw kannst.
1. Lösung mit einer MatrixFml über den ganzen Quell- (3000 Spalten, hier aber nur 9 → B2:J10 → HauptArgumente) und ZielBereich (ErgebnisZellen über 1000, hier nur 3 Spalten):
{=INDEX(B2:J10;ZEILE(1:9);1+(SPALTE(A:C)-1)*3)&" "&INDEX(B2:J10;ZEILE(1:9);2+(SPALTE(A:C)-1)*3)&
" "&INDEX(B2:J10;ZEILE(1:9);3+(SPALTE(A:C)-1)*3)}
Das könnte aber bei den Dimensionen der QuellTab problematisch wdn!
2. Analoge Lösung mit kopierbarer MatrixFml, aber über nur jeweils 3 QuellSpalten (→ 1 ZielSpalte):
{=INDEX($B2:$J10;ZEILE(1:9);1+(SPALTE(A1)-1)*3)&" "&INDEX($B2:$J10;ZEILE(1:9);2+(SPALTE(A1)-1)*3)&
" "&INDEX($B2:$J10;ZEILE(1:9);3+(SPALTE(A1)-1)*3)}
Hier ist die Fixierung der QuellSpalten (absolute Angabe) erforderlich, denn die Fml wird in jede ZielSpalte kopiert. Das setzt aber voraus, dass die Zeilenanzahl nicht unbeherrschbar groß ist!
3. Lösungen für EinzelZellen, deren Fmln in jede ErgebnisZelle kopiert wdn müssen:
a) Mit BEREICH.VERSCHIEBEN:
=BEREICH.VERSCHIEBEN($B2;0;(SPALTE(A1)-1)*3)&" "&BEREICH.VERSCHIEBEN($C2;0;(SPALTE(A1)-1)*3)&
" "&BEREICH.VERSCHIEBEN($D2;0;(SPALTE(A1)-1)*3)
b) Mit INDEX (normal):
=INDEX($B2:$J2;1+(SPALTE(A1)-1)*3)&" "&INDEX($B2:$J2;2+(SPALTE(A1)-1)*3)&" "&INDEX($B2:$J2; 3+(SPALTE(A1)-1)*3)
c) Mit INDEX (speziell) unter Aufführung aller 3spaltigen TeilBereiche:
=INDEX(($B2:$D2;$E2:$G2;$H2:$J2);0;1;SPALTE(A1))&" "&INDEX(($B2:$D2;$E2:$G2;$H2:$J2);0;2;SPALTE(A1)) &
" "&INDEX(($B2:$D2;$E2:$G2;$H2:$J2);0;3;SPALTE(A1))
d) Mit UDF MxJoin, deren HptArgument wie bei der vorherigen Lösung angegeben wdn muss:
=MxJoin(($B2:$D2;$E2:$G2;$H2:$J2);;SPALTE(A1))
Bei c und d könnte die AngabeForm (als unzu­sammen­hän­gender Bereich) ggf problematisch sein bzw wdn!
Außerdem gibt es auch noch Lösungen mit INDIREKT. Diese Fkt ist aber wg ihrer Volatilität genauso umstritten (und wird deshalb oft nur empfohlen, wenn nichts Anderes fktioniert) wie BEREICH.VERSCHIEBEN.
Bei solchen DatenVolumina ist oft die Anwendung einer Subprozedur effektiver, zumal es sich hierbei ja um recht simple Operationen handelt. Dazu gibt's im Archiv auch genügend Bspp. Außerdem stellt auch die oben verlinkte UDF (in ihrem agierenden CodeTeil) ein solches dar.
Gruß, Luc :-?


  

Betrifft: AW: Da hast du mehrere Möglichkeiten, ... von: Quattro
Geschrieben am: 24.09.2014 02:26:25

Vielen Dank für die ausführliche Erklärung! Ich habe es mal mit Indirekt gemacht, weil ich mich da noch auskenne. Bedeutung von Volatilität im Zusammenhang mit Excel kenne ich nicht.
Ich habe halt nur gehört, dass durch viele Indirekt-Formeln Excel verlangsamt wird, was ich bestätigen kann und von daher sollte ich mich mal mit UDFs auseinandersetzen.

LG und danke !


  

Betrifft: Warum hast du überhpt gefragt, ... von: Luc:-?
Geschrieben am: 26.09.2014 01:20:17

…Quattro,
wenn du dann doch imstande warst, eine eigene Fml zu entwickeln (zumal ich INDIREKT nur erwähnt hatte!)? :-]
Eine volatile Fkt erkennt man daran, dass im FmlAssi kein Ergebnis, sondern stattdessen nur Veränderlich angezeigt wird. Volatilität bedeutet, dass eine Fml, die solche Fktt enthält, bei jeder Änderung in irgendeiner geöffneten Mappe (kann auch eine ganz andere sein!) neu berechnet wird. Ebenso wdn dann natürlich auch alle anderen Fmln, die auf irgendeine Weise mit dieser Fml verbunden sind, neu berechnet. Deshalb kann Xl dann uU recht langsam wdn.
Eine UDF kann man ebenfalls volatil machen, muss es aber idR nicht. Allerdings kann man den Eindruck gewinnen, dass Xl zumindest manche UDFs auch stets neu berechnet, egal ob sie Application.Volatile (Default-Parameter True) enthalten oder nicht. Das scheint also noch von Anderem abzuhängen.
Gruß, Luc :-?


  

Betrifft: AW: Verketten von Spalten von: Daniel
Geschrieben am: 23.09.2014 09:50:32

Hi

Folgende Formel in die obere Linke Zelle der Ergebnistabelle
Die Formel kannst du dann nach rechts und nach untenziehen.
Geschrieben für den Fall, dass beginnend ab Spalte B immer 3 Spalten zusammengefasst werden

=index(2:2;1;spalte(a2)*3-1)&" "&index(2:2;1;spalte(a2)*3+0)&" "&index(2:2;1;spalte(a2)*3+1)
Gruß Daniel


  

Betrifft: AW: Verketten von Spalten von: Quattro
Geschrieben am: 24.09.2014 03:12:08

Hallo Daniel,

ich habe deine Formel ausprobiert.
Erst einmal danke und nur so als Randnotiz, irgendwie muss ich in dem neuen Excel erst immer Semikolons durch Kommas ersetzen, damit die Formel von Excel angenommen wird.
Dann hat Formel und rüberziehen deiner Formel funktioniert.
Ich hatte es inzwischen mit Indirekt-Funktion gelöst:
=INDIREKT("sdf!"&ADRESSE(ZEILE(),SPALTE()*3-1))
=VERKETTEN(INDIREKT("sdf!"&ADRESSE(ZEILE(),SPALTE()+1))," ",INDIREKT("sdf!"&ADRESSE(ZEILE(),SPALTE() +2))," ",INDIREKT("sdf!"&ADRESSE(ZEILE(),SPALTE()+3)))
Die vielen Indirekt-Funktionen verlangsamen halt Excel, wäre das mit Index auch so?
Glaub dafür sollte mich mal wie von Luc angeregt mit so einer VBA-Lösung auseinandersetzen.

Danke jedenfalls für deinen Lösungsvorschlag!





  

Betrifft: Einer der meinen war ganz ähnlich! owT von: Luc:-?
Geschrieben am: 26.09.2014 01:06:08

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Verketten von Spalten"