Kreuztabelle in Excel auflösen
#1
Hallo Clever-Excel Community!  Blush

Ich bin Excel-Anfänger, gebrauche es nur sehr rudimentär. Allerdings arbeite ich gerade an einem Projekt bei dem Excel die Arbeit sehr erleichtert.
Aufgrund von Datenschutz kann ich nicht alles veröffentlichen, es dreht sich allerdings um Datenaufbereitung bevor sie in ein anderes System importiert werden. Die Arbeiten habe ich soweit abgeschlossen, allerdings wollen jetzt Kreuztabellen aufgelöst werden, was händisch nur sehr umständlich gemacht werden kann und Excel ist ja sehr mächtig was das angeht.

Ich hänge euch natürlich die Datei mit Beispielsdaten an.

Detaillierter zu meinem Problem: 
Ich habe zwei Dateien, welche aus einer Datenbank (die mir nicht zur Verfügung steht) exportiert wurden. 
Die erste Datei ist eine Kreuztabelle mit ID Verknüpfung, die zweite enthält eine der beiden IDs und eben den zugehörigen Datensatz, in meinem Fall Namen von Autoren.

Ich möchte jetzt anhand der Autoren-ID den Namen des Autors der Werk ID zuordnen. Die IDs sind nicht in der identischen Reihenfolge. Da manche Werke mehrere Autoren haben (Kreuztabelle: Verknüpft 1-n Autoren mit 1-n Werken per ID), sollen die Autoren in einer extra Spalte, passend zur Werke ID in einer Zelle mit "," getrennt stehen. 


.xlsx   Beispiel_Datensaätze.xlsx (Größe: 10,97 KB / Downloads: 8)

Gibt es Makros dafür oder eine Möglichkeit das mit SVERWEIS umzusetzen? Meine Recherche und versuche haben bisher nichts brauchbares zu Tage gefördert.  Undecided 

Viele Grüße
Jo
Antwortento top
#2
(08.06.2016, 16:05)Tipffehler schrieb: allerdings wollen jetzt Kreuztabellen aufgelöst werden
Daten normalisieren
-----------------------------------------------------------------------------------------
Videos
mit VBA / mit Pivot-Tabellen-Assistent von Andrea Thehos (de)
mit Pivot-Tabellen-Assistent von Debra Dalgliesh (Contextures) (en)
mit Power Query von Miguel Escoba (The Power User) (en)
mit Pivot-Tabellen-Assistent von Mike Girvin (ExcelIsFun) (en)
mit Pivot-Tabellen-Assistent von Bill Jelen (MrExcel) (en)
mit Power Query von Ken Puls (en)
mit Power Query von Bill Jelen (MrExcel) (en)
mit Pivot-Tabellen-Assistent von Andrea Thehos (de)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
-----------------------------------------------------------------------------------------
Aufgabe und Lösungen von Andreas Thehos (de)
Aufgabe
Lösung mit Power Query
Lösung mit Pivot-Tabellen-Assistent
Lösung mit WAHL()
Lösung mit VBA
-----------------------------------------------------------------------------------------
Artikel
mit INDEX() (excelformeln.de)
mit Makro oder Power Query (excel-ist-sexy.de)
How-to Convert an Existing Excel Data Set to a Pivot Table Format
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
  • Tipffehler
Antwortento top
#3
Tollte Tutorials, leider handelt sich sich bei mir nicht um derartige "Kreuztabellen" wie man am Beispiel sieht. Die Daten stehen nicht geordnet da.  22
Antwortento top
#4
Zitat:leider handelt sich sich bei mir nicht um derartige "Kreuztabellen"
Ich wusste nicht dass es verschieden Typen von Kreuztabellen gibt.

Nach ich mir nun die Datei angesehen habe stelle ich fest dass sie 0 Kreuztabellen enthält. Stattdessen enthält sie zwei Datenlisten. Eine der Listen ist merkwürdigerweise unterbrochen. Das solltest du mal beheben.

Zitat:Da manche Werke mehrere Autoren haben (Kreuztabelle: Verknüpft 1-n Autoren mit 1-n Werken per ID),
Vielleicht in deinem Kopf aber nicht in der Datei.

Ich würde es mal mit Power Query versuchen.
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Antwortento top
#5
Richtig netter Umgangston hier - bisher der reine Spaß, danke dafür.  Huh

Ich habe natürlich beide Dateien in eine kopiert, damit klar wird um was es geht.

In Spalte A und B ist die Verknüpfung von Werk ID und Autoren ID, in der Datenbank war das ein Eintrag. In Tabelle D steht die ID welche in der Autoren Tabelle ist, in E der zugehörige Datensatz ergo Name des Autors. 
Der Datensatz ist unterbrochen und unvollständig da es sich um sehr viele Einträge handelt und nicht alle vollständig sind. 
Mir geht es darum. wie ich mit Excel die Zuweisung des Tabelleninhaltes je nach ID automatisieren kann.

Beispiel:
ID-Werk  ID Autor  Autor
2             1,2,4        Konrad von Megenberg,Ulricus Ebardus,Johannes Melber

Ich lese mich mal in Power Query ein.
Antwortento top
#6
Zitat:Der Datensatz ist unterbrochen und unvollständig da es sich um sehr viele Einträge handelt und nicht alle vollständig sind.
Eine leere Zeile zeugt nicht von Unvollständigkeit sondern von einem Bruch in den Daten.
Bitte lesen: Regeln für Tabellen

Zitat:ID-Werk  ID Autor  Autor
2             1,2,4        Konrad von Megenberg,Ulricus Ebardus,Johannes Melber
Ach so. Das ist also dein Wunschergebnis. Ich hätte jetzt etwas anderes vermutet.
Wenn du das aktuelle Office365 abonniert hast geht es mit TEXTVERKETTEN().
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Antwortento top
#7
Ah, ok, Jetzt ist mir klar was du mit Bruch meinst. 
Da die Daten nur in Excel aufbereitet werden, sollte das kein Problem sein, bisher haben alle verwendeten Funktionen trotzdem funktioniert. Sollte es da mal einen Fehler auswerfen weiß ich jetzt aber bescheid. Wink

Mit der VERKETTEN-Funktion habe ich schon gearbeitet, wie bekomme ich es jetzt aber hin, dass eine Art Schleife die Liste durchläuft und jeweils in die Zeile mit der WERK ID 1 alle Autoren schreibt welche über die Autoren ID zugeordnet werden?
Kann ich da irgendwie einen Vergleich mit der WENN Funktion anstellen?

=WENN(AutorenID == N-Tablle_AutorenID, VERKETTE...)  Angel 

Ich müsste ja eine komplette Spalte angeben. Ist Excel überhaupt zu soetwas im Stande oder kann man das leichter per Python-Skript lösen?
Antwortento top
#8
Zitat:VERKETTEN-Funktion
Ich habe die Funktion TEXTVERKETTEN() genannt, die nur für Office365-Abonennten verfügbar ist. Die Funktion VERKETTEN() hilft hier nicht.
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Antwortento top
#9
Hallo,

könnte man aber auch so machen und dann jeweils den letzten Eintrag zu jeder ID zusammenfassen:

[html]
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHI
1Nummer_WerkNummer_Autor ID-AutorTitel    
2(Kreuztabelle: Verknüpft 1-n Autoren mit 1-n Werken per ID)        
321 1Konrad von Megenberg 1, 2Konrad von Megenberg, Ulricus Ebrardus 
431 2Ulricus Ebrardus 1, 2, 3Konrad von Megenberg, Ulricus Ebrardus, Wenceslaus Brack 
541 3Wenceslaus Brack 1, 2, 3, 4Konrad von Megenberg, Ulricus Ebrardus, Wenceslaus Brack, Johannes Melber 

ZelleFormel
G3=WENN(B3="";"";WENN(ZÄHLENWENN($B$2:B3;B3)=1;B3&WENN(A3<>B3;", "&A3;"");VERWEIS(9;1/($B$2:B2=B3);$G$2:G2)&", "&A3))
H3=WENN(B3="";"";WENN(ZÄHLENWENN($B$2:B3;B3)=1;SVERWEIS(B3;$D:$E;2;0)&WENN(A3<>B3;", "&SVERWEIS(A3;$D:$E;2;0);"");VERWEIS(9;1/($B$2:B2=B3);$H$2:H2)&", "&SVERWEIS(A3;$D:$E;2;0)))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
[/html]
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antwortento top


Gehe zu:


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