Kreuztabelle in Liste umwandeln ohne VBA

[Tyros73]

Moin zusammen,
ich habe eine Herausforderung an die Excel-Spezialisten hier im Forum. Das Umwandeln einer Kreuztabelle in eine Liste ist auch für mich kein Problem. In diesem Fall bin ich als Gelegenheitsanwender von Excel jedoch aufgeschmissen.

Der Screenshot und die XLS Datei im Anhang zeigen die Aufgabe: In der Datenbank habe ich in den Spalten Mitarbeiter, in den Zeilen diverse Projekte. Die Mitarbeiter sind jeweils nur in diversen Projekten verplant und ich möchte eine Liste generieren (ohne VBA), welche für die einzelnen Mitarbeiter (nur) die Projekte auflistet, in denen Stunden für diese geplant wurden. Das heiß aktuell sind die ersten zwei Spalten der Liste die Herausforderung für mich.

Schon im Voraus Danke für eure Tipps!

[Warkings]

Powerquery und Entpivotieren

[Tyros73]

Hallo Warkings, vielen Dank für diesen Hinweis.

Mein Fehler, ich habe das was ich tun möchte etwas falsch beschrieben!

Ich möchte die Kreuztabelle nicht umwandeln (diese muss bestehen bleiben), sondern "nur" die enthaltenen Werte der Kreuztabelle wie beschrieben auf einem anderen Blatt in der Liste auslesen.

[scorefun]

Schau Dir die Videos von Andreas Theos auf YouTube an

PQ: https://www.youtube.com/watch?v=_ZuvBjhCu9A
Formeln: https://www.youtube.com/watch?v=wO0IZmGIq0k&t=92s
Pivot Konsolidierung: https://www.youtube.com/watch?v=a2Dxpx38bJI

[Tyros73]

Tolle Tutorials, aber ich konnte nicht herausfinden, wie ich Zellen ohne Inhalte überspringe.

[Cadmus]

Das macht PQ automatisch.

[Fred11]

Hallo Tyros73,

hier ein Formellösung, die allerdings recht aufwendig/komplex ist:

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G
2	Projekt:	Mitarbeiter / Stunden
3		X1	X2	X3	X4
4	A	20
5	B		5	99	4
6	C	30
7	D			22
8	E		8
9	F
10	G	30
11	H			10
12	I		4		500
13	XXX	2,5

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

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

Arbeitsblatt mit dem Namen 'Tabelle1'
	I	J	K
15	Mitarbeiter:	Projekt:	Stunden:
16	X1	A	20
17	X1	C	30
18	X1	G	30
19	X1	XXX	2,5
20	X2	B	5
21	X2	E	8
22	X2	I	4
23	X3	B	99
24	X3	D	22
25	X3	H	10
26	X4	B	4
27	X4	I	500

Zelle	Formel
I16	=D3
I17	=WENN(ZEILE(A1)>=ANZAHL($D$4:$G$13);"";WENN(ZEILE(A1)=ANZAHL(BEREICH.VERSCHIEBEN($D$4:$D$13;;;;VERGLEICH(I16;$D$3:$G$3;0)));INDEX($D$3:$G$3;VERGLEICH(I16;$D$3:$G$3;0)+1);I16))
J16	=WENN(I16<>"";INDEX($C$4:$C$13;AGGREGAT(15;6;ZEILE($C$4:$C$13)/(INDEX($D$4:$G$13;0;VERGLEICH(I16;$D$3:$G$3;0))<>"")-ZEILE($C$3);ZÄHLENWENN($I$16:I16;I16)));"")
K16	=WENN(I16<>"";INDEX($D$4:$G$13;VERGLEICH(J16;$C$4:$C$13;0);VERGLEICH(I16;$D$3:$G$3;0));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

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

Gruß
Fred

[LCohen]

Beide folgenden für neues Excel:

office-hilfe.com/support/threads/entpivotisierung-dyn-kreuztab-mittels-filter-sequenz-und-bereich-verschieben.33821/

office-hilfe.com/support/threads/lambda-udf-ben-def-tabellenfunktionen-ohne-vba-xl4m.36909/#UNPIVOT

[Mikecell]

Hallo Fred,

nach langem Suchen bin ich auf Deine Formel gestoßen und die löst fast mein Problem. Ich bräuchte „nur“ die Achsen vertauscht, bekomme das aber leider nicht hin. Bei mir stehen senkrecht die Namen und horizontal die 52 Kalenderwochen. Das Ergebnis sollte dann ähnlich wie bei dieser Lösung die Name und die KW senkrecht nebeneinander sein, anschließend der entsprechende Zelleninhalt, falls was eingetragen ist. Ein reines Ersetzen der ZEILE durch SPALTE bringt mich leider nicht zum Ziel. Tauschen möchte ich deshalb, da die KWs im Jahr fix sind, die Anzahl der Namen aber variieren kann.
Hast Du da eine Idee?

Grüße

Michael

[Fred11]

Hallo Michael,

die Formeln, um die Kreuztabelle in Listenform umzuwandeln ("entpivotieren") sind recht komplex; mit Ersetzen von ZEILE durch SPALTE ist es da nicht getan; da muss man schon einiges mehr anpassen (Bereiche anpassen...).
Ich habe in der angehängten Beispieldatei die Formel mal entsprechend umgebaut.

  Kreuztabelle in Liste umwandeln _umgekehrt_fs2.xlsx (Größe: 13,62 KB / Downloads: 5)
Allerdings hat die Formel eine Schwachstelle: In dem Fall, wenn ein oder mehrere Mitarbeiter bei keiner KW einen Eintrag hat (also eine oder mehrere Zeilen komplett leer sind), funktioniert die Formel nicht und gibt ein falsches Ergebnis zurück. Wie man das in der Formellösung noch abfangen könnte, habe ich aktuell keine Idee.

Laut deinem Profil hast du Excel 2016; da ist PowerQuery (Menü Daten -> Daten abrufen und transformieren) integriert.
Damit geht es ganz einfach, eine Kreuztabelle in eine Datensatzliste umzuwandeln.
Excel-Tipp: Aus einer Kreuztabelle eine Liste erstellen – entpivotieren (business-wissen.de)
3 Methods to unpivot in Excel using Power Query | Excel Off The Grid (youtube.com)

  Kreuztabelle in Liste umwandeln __PQ.xlsx (Größe: 20,25 KB / Downloads: 2)

Gruß
Fred