Registriert seit: 17.06.2016
Version(en): 10
08.12.2016, 16:04
(Dieser Beitrag wurde zuletzt bearbeitet: 08.12.2016, 16:17 von 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?
Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
Registriert seit: 13.04.2014
Version(en): 365
08.12.2016, 16:16
(Dieser Beitrag wurde zuletzt bearbeitet: 08.12.2016, 16:21 von 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,
?????????????????
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
08.12.2016, 16:39
(Dieser Beitrag wurde zuletzt bearbeitet: 08.12.2016, 16:45 von 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.
Registriert seit: 17.06.2016
Version(en): 10
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
Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
08.12.2016, 16:54
(Dieser Beitrag wurde zuletzt bearbeitet: 08.12.2016, 16:54 von 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.
Registriert seit: 13.04.2014
Version(en): 365
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' |
| 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 |
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
08.12.2016, 17:37
(Dieser Beitrag wurde zuletzt bearbeitet: 08.12.2016, 17:39 von 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 ...
Registriert seit: 13.04.2014
Version(en): 365
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.
Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
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