12.12.2020, 08:54
UNPIVOT(standardisierte_Kreuztabelle)
Mit Bezug auf die LET-Funktion in
office-hilfe.com/support/threads/entpivotisierung-dyn-kreuztab-mittels-filter-sequenz-und-bereich-verschieben.33821/
nun das naheliegende und lageunabhängige =UNPIVOT(A1:E6)
=LAMBDA(a;LET(
y;SPALTEN(a)-1;
x;y*ZEILEN(a)-1;
i;SEQUENZ(x;;y);
m;i/y;
n;REST(i;y)+1;
Werte;INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;2);;;x;y);m;n);
FILTER(WAHL(SEQUENZ(;3);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;1);;;x;1);m);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;1;2);;;1;y);n);
Werte);Werte<>"")))
Die zu behandelnde Kreuztabelle (im Beispiel A1:E6) muss (wie z.B. eine MEHRFACHOPERATIONs-Tabelle auch) dafür auf zwingende Weise angeordnet sein (Formelkonvention zur Vermeidung von Stress):
Wichtig: Rechts von und unter Feld3 befinden sich Leerzellen, siehe Beispiel!
Erläuterung, "als wäre die umzuwandelnde Kreuztabelle eine Pivot-Tabelle":
Feld1 ("ZEILEN") wird in der entpivotierten Tabelle zu Spalte 1
Feld2 ("SPALTEN") wird in der entpivotierten Tabelle zu Spalte 2
Feld3 ("WERTE") wird in der entpivotierten Tabelle zu Spalte 3
(mit der Bezeichung entsprechender Pivot-Felder in Anführungszeichen)
Das Ergebnis sieht dann pivot- oder autofilter-auswertbar wie folgt aus:
Ausblick: Was ist bei Kreuztabellen, die mehr als 3 Spalten entpivotisiert ergeben sollen, z.B. mit Jahr oberhalb Monat? Dafür fasst man beides in Feld2 zusammen, also z.B. 2021-Jan, und trennt entsprechend am Ziel (ist noch nicht enthalten). Damit wird jede Kreuztabelle auf 2 Dimensionen (zuzüglich Werte) zurückgeführt, da eine Exceltabelle auch nur 2 Dimensionen hat.
Mit Bezug auf die LET-Funktion in
office-hilfe.com/support/threads/entpivotisierung-dyn-kreuztab-mittels-filter-sequenz-und-bereich-verschieben.33821/
nun das naheliegende und lageunabhängige =UNPIVOT(A1:E6)
=LAMBDA(a;LET(
y;SPALTEN(a)-1;
x;y*ZEILEN(a)-1;
i;SEQUENZ(x;;y);
m;i/y;
n;REST(i;y)+1;
Werte;INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;2);;;x;y);m;n);
FILTER(WAHL(SEQUENZ(;3);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;2;1);;;x;1);m);
INDEX(BEREICH.VERSCHIEBEN(INDEX(a;1;2);;;1;y);n);
Werte);Werte<>"")))
Die zu behandelnde Kreuztabelle (im Beispiel A1:E6) muss (wie z.B. eine MEHRFACHOPERATIONs-Tabelle auch) dafür auf zwingende Weise angeordnet sein (Formelkonvention zur Vermeidung von Stress):
Code:
(leer)Feld2 Jan Feb Mrz
Feld1 Feld3
A 1 4
B 2 5
C 6
D 7Wichtig: Rechts von und unter Feld3 befinden sich Leerzellen, siehe Beispiel!
Erläuterung, "als wäre die umzuwandelnde Kreuztabelle eine Pivot-Tabelle":
Feld1 ("ZEILEN") wird in der entpivotierten Tabelle zu Spalte 1
Feld2 ("SPALTEN") wird in der entpivotierten Tabelle zu Spalte 2
Feld3 ("WERTE") wird in der entpivotierten Tabelle zu Spalte 3
(mit der Bezeichung entsprechender Pivot-Felder in Anführungszeichen)
Das Ergebnis sieht dann pivot- oder autofilter-auswertbar wie folgt aus:
Code:
Feld1 Feld2 Feld3
A Jan 1
A Feb 4
B Feb 2
B Mrz 5
C Jan 6
D Feb 7Ausblick: Was ist bei Kreuztabellen, die mehr als 3 Spalten entpivotisiert ergeben sollen, z.B. mit Jahr oberhalb Monat? Dafür fasst man beides in Feld2 zusammen, also z.B. 2021-Jan, und trennt entsprechend am Ziel (ist noch nicht enthalten). Damit wird jede Kreuztabelle auf 2 Dimensionen (zuzüglich Werte) zurückgeführt, da eine Exceltabelle auch nur 2 Dimensionen hat.