Wer schon mit Pivot-Tabellen gearbeitet hat, der kennt vermutlich die Datenschnitte. Mit diesen grafischen Filterelementen lassen sich Pivot-Tabellen sehr bequem und anwenderfreundlich filtern.
Heute werde ich dir zeigen, wie man eine sehr dynamische Auswertung erstellt, die nur aus Formeln besteht – also ohne Pivot-Tabellen – und die trotzdem über Datenschnitte gesteuert wird! Voraussetzung für mein Beispiel ist allerdings, dass Microsoft 365 oder Excel 2021 zum Einsatz kommt. Denn die verwendeten Array-Funktionen sind nur dort verfügbar.
Und so geht’s:
Beispieldatei herunterladen
Was ist ein Datenschnitt
Wie eingangs erwähnt, handelt es sich bei einem Datenschnitt um ein grafisches Filterobjekt, das man in der Regel in Pivot-Tabellen verwendet. In einer vorhandenen Pivot-Tabelle wird dieser Filter über das Menü „Einfügen | Datenschnitt“ hinzugefügt.
Einen neuen Datenschnitt anlegen
So kann man einfach durch Anklicken die gewünschten Elemente in der Pivot-Tabelle filtern. Wer noch nie damit gearbeitet hat, dem empfehle ich den Artikel Bequemer filtern mit Datenschnitten. Dort findest du auch ein Beispiel für einen Datenschnitt in einer formatierten („intelligenten“) Tabelle.
Das Ziel
Ich habe für das heutige Beispiel eine Rohdatentabelle vorbereitet, welche für verschiedene Länder das Bruttoinlandsprodukt nach Jahren enthält. Es handelt sich dabei um eine intelligente Tabelle mit dem Namen tblBIP:
Die Quelltabelle namens tblBIP
Diese Datentabelle möchte ich nun in einer Kreuztabelle zusammenfassen, für die folgende Besonderheiten gelten soll:
- Die Tabelle soll sich dynamisch erweitern, wenn neue Länder oder neue Jahre dazukommen
- Die Tabelle soll mit Datenschnitten nach einzelnen Ländern und/oder Jahren gefiltert werden können
- Und: Die Auswertung soll nicht über eine Pivot-Tabelle, sondern nur über Formeln erfolgen
Das Endergebnis wird dann folgendermaßen aussehen:
Das gewünschte Endergebnis
Schritt 1: Die dynamische Kreuztabelle
Zunächst brauche ich eine Liste aller Länder, die in meiner Rohdatentabelle tblBIP vorkommen. Mit Excel aus M365 (oder Excel 2021) und den darin verfügbaren Funktionen EINDEUTIG und SORTIEREN ist das kein Problem. In einem leeren Arbeitsblatt gebe ich folgende Formel ein:
A3: =SORTIEREN(EINDEUTIG(tblBIP[Land]))
Die eindeutige Liste aller Länder
Über die EINDEUTIG-Funktion wird jedes vorkommende Land nur ein einziges Mal angezeigt, die SORTIEREN-Funktion listet das Ergebnis alphabetisch sortiert auf. Da es sich bei den beide Funktionen eben um dynamische Array-Funktionen handelt, wird die Liste automatisch länger, sobald neue Länder in der Quelltabelle dazukommen.
Auch die Jahre sollen sich dynamisch erweitern, so dass auch hierfür wieder EINDEUTIG und SORTIEREN zum Einsatz kommen. Da diese Funktionen ihre Ergebnisse zeilenweise liefern, ich die Jahre aber spaltenweise benötige, kommt eine zusätzliche Funktion zum Einsatz: Die relativ unbekannte, aber schon seit langer Zeit verfügbare MTRANS-Funktion (ich habe hier einen Artikel darüber geschrieben):
B2: =MTRANS(SORTIEREN(EINDEUTIG(tblBIP[Jahr])))
Die transformierte eindeutige Liste aller Jahre
Jetzt müssen wir uns nur noch die Beträge holen. Dazu greife ich auf SUMMEWENNS zurück:
B3: =SUMMEWENNS(tblBIP[BIP in Mrd €];tblBIP[Land];A3#;tblBIP[Jahr];B2#)
Mit SUMMEWENNS die passenden Werte ermitteln
Das Besondere in der Formel sind die Rautensymbole hinter den beiden Verweise auf die Suchkriterien (A3# und B2#). Damit bringt man zum Ausdruck, dass es sich bei A3 und B2 jeweils um die erste Zelle eines dynamischen Überlauf-Bereichs handelt. Dadurch wird auch die SUMMEWENNS-Funktion plötzlich zu einer dynamischen Funktion, die sich automatisch in so viele Zeilen und Spalten ausbreitet, wie eben gerade notwendig ist.
Meine Excel-Kollegin Hildegard Hügemann hat ein ganz ähnliches Szenario übrigens vor einiger Zeit schon mal in diesem Artikel bei office-kompetenz vorgestellt.
Sobald nun in meiner Rohdatentabelle tblBIP neue Länder oder Jahre dazukommen, erweitert sich automatisch meine Kreuztabelle. Dies ist schon mal ein kleiner Vorteil im Vergleich zu einer Pivot-Tabelle, wo neue Daten erst dann erscheinen, wenn die Pivot z.B. per Rechtsklick aktualisiert wurde.
Schritt 2: Datenschnitte einfügen
Wechseln wir in das Blatt mit der Rohdatentabelle. Ich habe ja oben schon erwähnt, dass man Datenschnitte auch mit formatierten („intelligenten“) Tabellen verwenden kann. Und genau das machen wir jetzt:
Positioniere die aktive Zelle irgendwo innerhalb der Datentabelle und füge über das Menü „Einfügen | Datenschnitt“ zwei Datenschnitte ein:
Zwei Datenschnitte für die intelligente Quelltabelle
Damit kann die intelligente Tabelle schon gefiltert werden. Wir wollen die Datenschnitte aber im Blatt mit der Kreuztabelle. Daher schneiden wir sie mit Strg+X aus und fügen sie mit Strg+V an der gewünschten Position wieder ein. Ein kurzer Test ist allerdings ernüchternd: Obwohl die Datentabelle gefiltert wird, spiegelt sich das leider überhaupt nicht auf meine Kreuztabelle wider.
Enttäuschend: Datenschnitte funktionieren (noch) nicht
Die Formeln liefern immer alle Daten, auch wenn sie in der Quelltabelle ausgefiltert wurden.
Schritt 3: Eine kleine Geheimwaffe
Wir müssen den Formeln in der Kreuztabelle also irgendwie mitteilen, dass sie ausgeblendete Zeilen in der Quelltabelle ignorieren sollen. Zu diesem Zweck fügen wir in der Quelltabelle noch eine weitere Spalte dazu, die ich „sichtbar“ nenne. Und in dieser Spalte kommt meine Geheimwaffe zum Einsatz, die AGGREGAT-Funktion:
=AGGREGAT(3;5;[@Land])
Die Geheimwaffe: AGGREGAT-Funktion
Wenn du diese geniale Funktion noch nicht kennst oder sie bisher großzügig ignoriert hast, solltest du unbedingt diesen Artikel lesen.
Die AGGREGAT-Funktion vereinigt in sich nämlich 19(!) verschiedene Funktionen, wie z.B. SUMME, MIN oder MAX. Oder eben auch ANZAHL2. Dies wird mit dem ersten Parameter gesteuert. Darüber hinaus ist sie aber deutlich flexibler als die jeweilige individuelle Funktion, dann über 7 verschiedene Optionen kann man im zweiten Parameter die Berechnung noch finetunen. So lassen sich mit der Option 5 ausgeblendete Zellen ignorieren.
Genau das, was wir hier brauchen!
Die oben eingegebene AGGREGAT-Formel zählt nämlich das Land in der jeweiligen Zeile, ignoriert dabei aber ausgeblendete Zeilen. Das bedeutet, dass für jede sichtbare Zeile immer der Wert 1 und für jede ausgeblendete Zeile der Wert 0 geliefert wird (den man natürlich nicht sehen kann, weil die betreffende Zeile dann ja ausgeblendet ist).
Und somit habe ich ein Filterkriterium, das ich in meine Formeln in der Kreuztabelle einbauen kann!
Schritt 4: Anpassen der Formeln in der Kreuztabelle
Wir müssen nur die beiden Formeln für Länderliste und die Jahresspalten um eine FILTER-Funktion erweitern. Dabei sollen nur noch Länder bzw. Jahre gefiltert werden, für die in der Spalte „sichtbar“ der Wert 1 steht.
A3: =SORTIEREN(EINDEUTIG(FILTER(tblBIP[Land];tblBIP[sichtbar]=1)))
und
B2: =MTRANS(SORTIEREN(EINDEUTIG(FILTER(tblBIP[Jahr];tblBIP[sichtbar]=1))))
Die SUMMEWENNS-Formel kann unverändert bleiben, denn sie bezieht ihre Kriterien ja aus der Länder- und Jahresliste.
Ein kurzer Test der Datenschnitte liefert jetzt endlich die gewünschten Ergebnisse:
Die Datenschnitte funktionieren wie gewünscht
Zum Schluss ein wenig Feinschliff
Für eine bessere Optik sollen die jeweils sichtbaren Zellen noch mit einem Rahmen versehen werden. Das erledigen wir über eine einfache bedingte Formatierungsregel.
Dazu müssen erst sämtliche Filter in den Datenschnitten entfernt werden, so dass die komplette Tabelle angezeigt wird. Danach markieren wir die Tabelle von A2:F12 (du kannst natürlich auch einen kleinen Sicherheitspuffer für zukünftige weitere Länder und Jahre einbauen und gleich ein paar Spalten und Zeilen mehr markieren).
Über das Menü „Start | Bedingte Formatierung | Neue Regel…“ legen wir eine neue Formatierungsregel an:
Bedingte Formatierung anlegen
=A2<>""A2 deshalb, weil die aktive Zelle in meinem markierten Bereich in Zelle A2 steht. Darauf musst du unbedingt achten, ansonsten können bedingte Formatierungen durchaus Kopfschmerzen bereiten.
Dann legst du über die „Formatieren“-Schaltfläche noch die äußeren Rahmenlinien fest – fertig! Jetzt werden immer nur die Zellen eingerahmt, die auch wirklich Daten enthalten.
Die fertige Tabelle mit bedingter Formatierung im Einsatz
Natürlich wäre das hier gezeigte Beispiel auch einfach mit einer Pivot-Tabelle zu lösen gewesen. Ich wollte aber ein wenig deine Fantasie anregen und zeigen, dass auch andere Varianten möglich sind, falls dies einmal notwendig oder gewünscht sein sollte.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Guten Morgen Martin,
vielen Dank für diesen super Beitrag.
Ich habe den Schritt 4 gleich mal ausprobiert für einen anderen Zweck nämlich eine dynamische Werteliste. Bisher hatte ich das mit einen Pivot Tabelle gelöst. Das dumme war aber: bei Änderungen in der Quelltabelle musste die Pivot Tabelle per einzeiligem Makro aktualisiert werden. Das fällt nun weg.
Die Formel für die dynamische Werteliste lautet:
=SORTIEREN(EINDEUTIG(FILTER(<<>>;<<>>=<<>>)))
Die Formel in der Datenüberprüfung lautet:
=BEREICH.VERSCHIEBEN(<<>>;0;0;ZÄHLENWENN(<<>>;<<>>))
Super wäre gewesen, wenn die erste Formel direkt in die Datenüberprüfung eingetragen hätte werden können. Aber das scheint Excel nicht zu mögen.
Noch einmal: vielen Dank für die Anregung.
Gruß Andreas
oh meine Variablen sind verloren:
=SORTIEREN(EINDEUTIG(FILTER(V1;V2=V3)))
=BEREICH.VERSCHIEBEN(V4;0;0;ZÄHLENWENN(V2;V3))
V1: Spalte mit potenziellen Werten, die in der dynamischen Werteliste erscheinen sollen
V2: Spalte mit Filterkriterien, die die Menge der Werte in der dynamischen Werteliste einschränken soll
V3: Filterkriterium
V4: Zelle in der die Formel die dynamische Werteliste steht
Und hier noch ein konkretes Beispiel:
=SORTIEREN(EINDEUTIG(FILTER(TabBeispiel[Beispiel Titel];TabBeispiel[Filterkriterien]=“Filtertext“)))
=BEREICH.VERSCHIEBEN(BlattMitDerDynamischenWerteliste!$BB$3;0;0;ZÄHLENWENN(BlattMitTabBeispiel!$Q$12:$Q$167;“Filtertext“))
Hinweis: in BEREICH.VERSCHIEBEN muss leider die dargestellte Schreibweise verwendet werden. Das funktioniert aber trotzdem dynamisch.
Hallo Andreas,
freut mich zu hören, dass dich der Artikel zu einer eigenen konkreten Idee inspiriert hat, so soll es sein!
Schöne Grüße,
Martin
Vielen Dank für die vielen schönen Anregungen hier im Blog.
Ich nutze das immer gerne, um zu sehen, wie weit ich selber au fdie Lösung komme. Schade, dass sich Excel dagegen streubt, die Kreuztabelle selbst als intelligente Tabelle anzulegen. das hätte das ganze wesentlich leichter gemacht.
AGGREGAT habe ich vor ahren schon vor Jahren auf diese Weise benutzt, als ich einen bedingten Mittelwert über gefilterte Daten brauchte.
FILTER in der Kreuztabelle zu benutzen war der Punkt, an dem ich spontan nicht weiter gekommen war.
Ich freu mich schon auf die nächsten Artikel
Viele Grüße Jochen
Hallo Jochen,
vielen Dank für das schöne Feedback. Es stimmt, die dynamischen Array-Funktionen und intelligente Tabellen schließen sich leider gegenseitig aus. Hier muss man sich leider für einen Weg entscheiden.
Schöne Grüße,
Martin
Was läuft hier Schief,
beim download der Beispieldatei?
Fehlermeldung:
Misdirected Request
The client needs a new connection for this request as the requested host name does not match the Server Name Indication (SNI) in use for this connection.
Apache/2.4.57 (Debian) Server at service.tabellenexperte.de Port 443
Hallo Werner,
ich habe den Download gerade nochmal in drei verschiedenen Browsern getestet (Edge, Opera, Chrome) und in allen dreien hat es bei mir problemlos funktioniert.
Vielleich kannst du es nochmal testen.
Schöne Grüße,
Martin