Transponieren abhängig von Werten: wie mache ich das?
#1
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?
Top
#2
http://excelformeln.de/formeln.html?welcher=315 (ohne die Vorspalte)
Top
#3
Hallo,

z.B. so:
Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDE
1xbobbill  
2yfoobarbazbom
3zloremipsumdolor 
4     
5     
6     
7xbob   
8xbill   
9yfoo   
10ybar   
11ybaz   
12ybom   
13zlorem   
14zipsum   
15zdolor   
16     

ZelleFormel
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,

?????????????????
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Top
#4
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.
Top
#5
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
Top
#6
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.
Top
#7
Hi lupo,


Zitat: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'
 AB
1xbob
2xbill
3yfoo
4ybar
5ybaz

ZelleFormel
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
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Top
#8
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 ...
Top
#9
Hallo,

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

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Top
#10
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
Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste