Transponieren abhängig von Werten: wie mache ich das?

[chilly_bang]

Hi
Ich habe eine Tabelle wie

Code:

╔═══╦═══════╦═══════╦═══════╦═════╗
║ a ║ b     ║ c     ║ d     ║ e   ║
╠═══╬═══════╬═══════╬═══════╬═════╣
║ x ║ bob   ║ bill  ║       ║     ║
╠═══╬═══════╬═══════╬═══════╬═════╣
║ y ║ foo   ║ bar   ║ baz   ║ bom ║
╠═══╬═══════╬═══════╬═══════╬═════╣
║ z ║ lorem ║ ipsum ║ dolor ║     ║
╚═══╩═══════╩═══════╩═══════╩═════╝

Ich möchte sie so umwandeln, dass alle Werte aus den Spalten ab B nach rechts (so viele es gibt - kann auch richtig viel sein), in die Spalte B kommen, untereinander, und in die Spalte A der ihnen entsprechende Wert aus A kommt. Dass die Tabelle so wie folgt aussieht:

Code:

╔═══╦═══════╗
║ a ║ b     ║
╠═══╬═══════╣
║ x ║ bob   ║
╠═══╬═══════╣
║ x ║ bill  ║
╠═══╬═══════╣
║ y ║ foo   ║
╠═══╬═══════╣
║ y ║ bar   ║
╠═══╬═══════╣
║ y ║ baz   ║
╠═══╬═══════╣
║ y ║ bom   ║
╠═══╬═══════╣
║ z ║ lorem ║
╠═══╬═══════╣
║ z ║ ipsum ║
╠═══╬═══════╣
║ z ║ dolor ║
╚═══╩═══════╝

Wie mach ich das?

[lupo1]

http://excelformeln.de/formeln.html?welcher=315 (ohne die Vorspalte)

[BoskoBiati]

Hallo,

z.B. so:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E
1	x	bob	bill
2	y	foo	bar	baz	bom
3	z	lorem	ipsum	dolor
4
5
6
7	x	bob
8	x	bill
9	y	foo
10	y	bar
11	y	baz
12	y	bom
13	z	lorem
14	z	ipsum
15	z	dolor
16

Zelle	Formel
A7	=A1
B7	=WENN(A7="";"";SVERWEIS(A7;$A$1:$I$6;ZÄHLENWENN($A$7:A7;A7)+1;0))
A8	=WENN(ZEILE(A2)>ANZAHL2($B$1:$I$6);"";WENN(ZÄHLENWENN($A$7:A7;A7)=ANZAHL2(INDEX($B$1:$I$6;VERGLEICH(A7;$A$1:$A$6;0);));INDEX($A$1:$A$6;VERGLEICH(A7;$A$1:$A$6;0)+1);A7))
B8	=WENN(A8="";"";SVERWEIS(A8;$A$1:$I$6;ZÄHLENWENN($A$7:A8;A8)+1;0))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

@lupo1,

?????????????????

[lupo1]

Bosko mag keine Hilfszellen ... bei sehr vielen Daten explodiert dann aber die Rechenzeit. Mit der gleichen Lage der Ausgangsdaten folgende Formeln:

G1: 0
G2: =ANZAHL2(B1:E1)+G1 entlang der Ausgangsdaten runterkopieren (Hilfsspalte 1)

I2: =VERGLEICH(ZEILE(A1)-1;G:G) definiert nun daran die Zeile ... (Hilfsspalte 2)
J2: =J1*(I2=I1)+1 definiert nun daran die Spalte ... (Hilfsspalte 3)
K2: =INDEX(A:A;I2)
L2: =INDEX(B:E;I2;J2)
Diese vier Zellen so weit runterkopieren, wie es Einträge in B:E hat.

Gibt es mehr Daten in der Breite, fügst Du aber erst jetzt (!) vor E:E entsprechend viele Spalten ein.

[chilly_bang]

Danke BoskoBiati und lupo1! Das Problem ist nur, dass die Tabellen, die so manipuliert werden sollen, mehrere Tausende Zeilen und bis 30 Spalten haben (immer alles verschieden).

Ich habe noch keine Möglichkeit gefunden beide Lösungsansätze so zu gestalten, dass sie allgemeiner ansetzbar wären

[lupo1]

Dann hast Du Spalten von B:AE. Somit (ungetestet):

AG1: 0
AG2: =ANZAHL2(B1:AE1)+AG1 entlang der Ausgangsdaten runterkopieren (Hilfsspalte 1)

AI2: =VERGLEICH(ZEILE(A1)-1;AG:AG) definiert nun daran die Zeile ... (Hilfsspalte 2)
AJ2: =AJ1*(AI2=AI1)+1 definiert nun daran die Spalte ... (Hilfsspalte 3)
AK2: =INDEX(A:A;AI2)
AL2: =INDEX(B:AE;AI2;AJ2)
Diese vier Zellen so weit runterkopieren, wie es Anzahl Einträge in B:AE hat.

Du wirst sehen: Blitzschnell. Bosko muss dann wohl Kaffee trinken gehen.

[BoskoBiati]

Hi lupo,

Bosko mag keine Hilfszellen

kann man so nicht sagen. Hier läßt sich das ja mit zwei Formeln erschlagen, also warum Hilfszellen?

in einer separaten Tabelle:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	x	bob
2	x	bill
3	y	foo
4	y	bar
5	y	baz

Zelle	Formel
A1	=Tabelle1!A1
A2	=WENN(ZEILE(A2)>ANZAHL2(Tabelle1!$B$1:$AB$6000);"";WENN(ZÄHLENWENN($A$1:A1;A1)=ANZAHL2(INDEX(Tabelle1!$B$1:$AB$6000;VERGLEICH(A1;Tabelle1!$A$1:$A$6000;0);));INDEX(Tabelle1!$A$1:$A$6000;VERGLEICH(A1;Tabelle1!$A:$A;0)+1);A1))
B1	=WENN(A1="";"";SVERWEIS(A1;Tabelle1!$A:$AB;ZÄHLENWENN($A$1:A1;A1)+1;0))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[lupo1]

Mein Excel 2010 schmiert bei Kopie runter bis Zeile 99.999 mit Deiner Lösung ab, Bosko. Meine rechnet in 3 Sekunden (kleinstes MS Surface).

Da bekommt Dein "mit 2 Formeln erschlagen" einen ganz neuen Geschmack  :72:

TE sprach von "Tausenden Zeilen" und "bis zu 30 Spalten".

Daraus mache ich 5.000 Zeilen und durchschnittlich 15 Einträge pro Zeile, also 75.000 Ausgabesätze.

Man sollte seine Lösung dann auch testen, nicht nur am Verständnis-Beispiel, sondern auch an Daten, die im Umfang die Realität widerspiegeln.

Warum muss die Gesamt-Anzahl2 bei Dir in jeder Formel neu gerechnet werden? Nur wegen der hässlichen Hilfszellen?

Nicht umsonst testen gute Softwareentwickler ihre Programme zwischendurch immer auch auf schwachbrüstigen PC's ...

[BoskoBiati]

Hallo,

Gott sei Dank bin ich weit ab von jeder Programmierkunst. :19: :19:

[lupo1]

Ich auch.

Aber die braucht es nicht, nur ein wenig Nachrechnerei, was Matrizenoperationen einem alles verderben können.

=ZÄHLENWENN(A1:A$1) 100.000mal runterkopiert ergibt 4.999.950.000 zu berücksichtigende Eingänge! Ka Ta Stro Phe