Office: Entpivotisierung dyn. Kreuztab. mittels FILTER(), SEQUENZ() und BEREICH.VERSCHIEBEN()

Helfe beim Thema Entpivotisierung dyn. Kreuztab. mittels FILTER(), SEQUENZ() und BEREICH.VERSCHIEBEN() in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Eine Kreuztabelle ist zwar keine geeignete Grundlage für eine flexible Auswertung der enthaltenen Daten über Pivot-Tabellen oder andere Instrumente.... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von lupo1, 20. Juni 2020.

  1. lupo1
    lupo1 Tutorial Guru

    Entpivotisierung dyn. Kreuztab. mittels FILTER(), SEQUENZ() und BEREICH.VERSCHIEBEN()


    Eine Kreuztabelle ist zwar keine geeignete Grundlage für eine flexible Auswertung der enthaltenen Daten über Pivot-Tabellen oder andere Instrumente. Wohl aber erleichtert sie sehr oft die Eingabe von Daten, da man in der zweiten Dimension SPALTE() Eingaben oft gleich oder vergleichbar gewährleisten kann, was in einer eindimensionalen Datensatzdarstellung erschwert bis unmöglich ist.

    Inhalt:
    1. Kreuztabelle (als bequeme Datenerfassung mit hohem Plausibilitätsfaktor)
    2. dynamische Entpivotisierung in legitime Datensätze (nur in neuerem Excel möglich, also in XL365, XL-Online und nach XL2019)
    3. Pivottabelle mit dynamischer Quelle Punkt 2.


    1. Ich verwende daher gern eine solche "kaputte" Datenerfassung A1: (bitte beachte die Feldnamen in A2, B1 und B2!) ...
    Code:
             Name   Anton Berta Cäsar
    ZZ-Datum Daten
    01.02.20         25    25    25
    01.03.20         50          45       
    01.04.20               50    10
    01.05.20         10           5
    2. ... und muss dann einen Zwischenschritt M2: (später nach rechts oder auf ein neues Blatt verschiebbar) für die dynamische Umstellung in richtige Datensätze einfügen, ...

    M2: =FILTER(WAHL(SEQUENZ(;3);
    INDEX(BEREICH.VERSCHIEBEN(A2;;;Q3;1);SEQUENZ(Q3;;Q2)/Q2);
    INDEX(BEREICH.VERSCHIEBEN(B1;;;1;Q2);REST(SEQUENZ(Q3;;Q2);Q2)+1);
    INDEX(BEREICH.VERSCHIEBEN(B2;;;Q3;Q2);SEQUENZ(Q3;;Q2)/Q2;REST(SEQUENZ(Q3;;Q2);Q2)+1));
    INDEX(BEREICH.VERSCHIEBEN(B2;;;Q3;Q2);SEQUENZ(Q3;;Q2)/Q2;REST(SEQUENZ(Q3;;Q2);Q2)+1)<>0)


    Q2: =ANZAHL2(1:1) als Spalten der Kreuztabelle, hier im Beispiel 3 von Spalte B bis D
    Q3: =ANZAHL2(A:A)*Q2 als Anzahl Werte der Kreuztabelle, hier im Beispiel 4 * 5 = 20 von Spalte B bis E und Zeile 2 bis 6
    Code:
    ZZ-Datum Name  Daten  leer   4
    01.02.20 Anton 25           20
    01.02.20 Berta 25
    01.02.20 Cäsar 25
    01.03.20 Anton 50
    01.03.20 Cäsar 45
    01.04.20 Berta 50
    01.04.20 Cäsar 10
    01.05.20 Anton 10
    01.05.20 Cäsar  5
    3. ... bevor es mit Pivot weiter geht. Die Zeile 1:1 des Blattes bleibt (außer vorne in B:E) leer, ebenso: Spalte A:A (außer in Zeile 2:6).

    Achtung: Ungewollte Leerzeichen in der Formel insb. in Funktionsnamen kommen hier in der Forensoftware-Darstellung vor und müssen daher nach dem Kopieren-Einfügen in die Zelle M2 manuell entfernt werden.

    Für die Pivot-Tabelle definieren wir vorab einen dynamischen Bezug (da die Quelle ein dynamisches Array darstellt) ...

    Namen definieren Quelle beziehtSichAuf: =$M$2#

    ... und platzieren sie, falls im gleichen Blatt, am besten in M20 oder tiefer (muss man dann manuell durch Zeileneinfügungen nachhalten oder eben auf ein neues Blatt verschieben, was sauberer ist). Sie bezieht sich dann natürlich nicht auf M2:O11, sondern auf die momentan gleichbedeutende =Quelle.

    Wie sonst allgemein bekannt, kann man nun auch noch die Pivot-Tabelle über ein Private Sub Worksheet_Change() automatisch updaten. Sollte man aber nicht, damit Massendatenerfassungen in der Kreuztabelle (Punkt 1) nicht sofort zu unnötigen Aktualisierungen führen.

    Angeregt durch Excel2010 - Tabelle neu sortieren - MS-Office-Forum
    _____________________________
    Die Formel und die Zellen Q2 und Q3 können ersetzt werden durch die interne-Variable-Funktion

    M2: =LET(
    Br;ANZAHL2(1:1);
    Hö;Br*ANZAHL2(A:A);
    i;SEQUENZ(Hö;;Br);
    x;i/Br;
    y;REST(i;Br)+1;
    Werte;INDEX(BEREICH.VERSCHIEBEN(B2;;;Hö;Br);x;y);
    FILTER(WAHL(SEQUENZ(;3);
    INDEX(BEREICH.VERSCHIEBEN(A2;;;Hö;1);x);
    INDEX(BEREICH.VERSCHIEBEN(B1;;;1;Br);y);
    Werte);Werte<>0))

    ____________________________
    F2:
    =WAHL(SPALTE(A1);
    INDEX($1:$1;(ZEILE(A1)-1)/ANZAHL2($A:$A)+2);
    INDEX($A:$A;REST(ZEILE(A1)-1;ANZAHL2($A:$A))+2);
    INDEX($A:$D;REST(ZEILE(A1)-1;ANZAHL2($A:$A))+2;(ZEILE(A1)-1)/ANZAHL2($A:$A)+2))


    ist für altes Excel und ein starres Gerüst (also auch leere Datensätze, 12 statt 9).

    Die Daten müssen dafür in A1 beginnen, wobei die Zelle A1 leer bleibt:
    Code:
             Anton Berta Cäsar
    01.02.20  25    25    25
    01.03.20  50          45       
    01.04.20        50    10
    01.05.20  10           5
    Hier das Forumsbeispiel dazu.
    ____________________________
    Hier der Weg über Power Query (von "Excel ist sexy") bzw. am Beispiel bei Herber vom 13.11.2020
     
    Zuletzt bearbeitet: 7. Mai 2021
    lupo1, 20. Juni 2020
    #1
Thema:

Entpivotisierung dyn. Kreuztab. mittels FILTER(), SEQUENZ() und BEREICH.VERSCHIEBEN()

Die Seite wird geladen...
  1. Entpivotisierung dyn. Kreuztab. mittels FILTER(), SEQUENZ() und BEREICH.VERSCHIEBEN() - Similar Threads - Entpivotisierung dyn Kreuztab

  2. Zählenwenn Ergebnis als dyn. Array

    in Microsoft Excel Hilfe
    Zählenwenn Ergebnis als dyn. Array: Hallo, In beiliegender Tabelle soll ein dyn. Array ausgegeben werden, welches die Ergebnisse wie in Spalte C beinhaltet. Das Array darf dabei nicht kürzer sein, als die Tabelle1 selbst. Also nicht...
  3. dyn. array in Let

    in Microsoft Excel Hilfe
    dyn. array in Let: Tach gesagt, Mit =HEUTE()+{-1;0;1} wird das Datum "gestern", "heute", "morgen" ausgegeben. Jetzt bei einer Let-Funktion das gleiche: Beispiel: =LET(H_1;-1; H_2;0; H_3;1; HEUTE()+{H_1;H_2;H_3}...
  4. VBA dyn. Zellen kopieren und am ende der Liste einfügen

    in Microsoft Excel Hilfe
    VBA dyn. Zellen kopieren und am ende der Liste einfügen: Guten Morgen zusammen, ich hoffe ihr könnt mir helfen, da ich mich zwar mit Excel auskenne aber mit Makros nichts zu tun habe. Ich habe folgendes Problem: Ich möchte einen Zelleneinträge...
  5. Dyn. Dropdown funktioniert/funktioniert nicht

    in Microsoft Excel Hilfe
    Dyn. Dropdown funktioniert/funktioniert nicht: Hallo, aus der Aufgabenstellung eines anderen Beitrags, ergab sich die Anforderung einer dynamischen Dropdownliste, die aus der Gesamtliste immer nur noch die Einträge als auswählbar zeigt, die...
  6. Dyn. VERGLEICHS-Bereiche innerhalb der INDEX-Funktion für die Matrizenmultiplikation

    in Microsoft Excel Hilfe
    Dyn. VERGLEICHS-Bereiche innerhalb der INDEX-Funktion für die Matrizenmultiplikation: Hallo Experten, Vor wenigen Wochen noch, wusste ich gar nicht, dass es so etwas gibt, geschweige denn, wie ich es anwenden könnte. Aber dank dieses Forums habe ich nun innerhalb einer...
  7. VBA - Textbox in UF mit Inhalt eines dyn. Bereiches füllen

    in Microsoft Excel Hilfe
    VBA - Textbox in UF mit Inhalt eines dyn. Bereiches füllen: Hallo, meine Frage bezieht sich auf eine sehr umfangreiche Firmendatei, weshalb ich leider "nicht mal eben" ein Beispiel posten kann. Aber da ich nur eine bestimmte Schreibweise in VBA suche,...
  8. dyn. Dropdown mit Info erstellen?!

    in Microsoft Excel Hilfe
    dyn. Dropdown mit Info erstellen?!: Nach 4 stündigem in Tuts rumlesen und experimentieren etc. melde ich mich nochmal bei euch! Ich würde gerne ein(mehrere) Dropdown menü(s) erstellen die dann ein zweites DD fühlen bzw. nicht...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden