Datenschnitte sind wirklich eine coole Sache: Man kann damit einfach durch Anklicken von Schaltflächen seine Pivot-Tabellen ganz bequem filtern, ohne sich mühsam durch irgendwelche Dropdown-Listen zu klicken. Für den Anwender eine wirklich praktische Angelegenheit.
Ich werde jedoch immer mal wieder gefragt, ob man die Datenschnitte auch auslesen kann, so dass die ausgewählten Filterkriterien an anderer Stelle ausgegeben werden können. Zum Beispiel als Beschreibung in einem Diagramm oder als erläuternde Information in einem Dashboard.
Meine Antwort: Ja, das geht!
Beispieldatei herunterladen
Datenschnitte
Solltest du bisher noch nie mit Datenschnitten gearbeitet haben, empfehle ich dir dringend, das zu ändern. Wie man sie einsetzt, habe ich vor längerer Zeit in diesem Artikel beschrieben.
Denn damit hebst du die Benutzerfreundlichkeit deiner Auswertungen auf ein. Ein weiterer Vorteil von Datenschnitten ist, dass man mit ihnen auch gleich mehrere Pivot-Tabellen gleichzeitig filtern kann. Wie das geht, kannst du im Quickie 116 nachlesen.
Wie liest man jetzt also einen Datenschnitt aus?
In meiner Beispieldatei habe ich eine Umsatztabelle und darauf aufbauend eine Pivot-Tabelle vorbereitet. Über einen Datenschnitt lässt sich nach Vertriebsregionen filtern. Und das Ziel ist es nun, die ausgewählten Regionen zusätzlich als Überschrift über der Pivot-Tabelle anzuzeigen, so wie im folgenden Bild:
Dynamische Überschrift auf Basis eines Datenschnitts
Der Trick hierfür ist relativ banal. Wir kopieren dazu einfach die vorhandene Pivot-Tabelle, die ja bereits mit dem Datenschnitt verknüpft ist und fügen sie an anderer Stelle nochmal ein:
Die erste Pivot-Tabelle wird einfach kopiert
Dadurch, dass die erste Pivot-Tabelle schon eine Verbindung zum Datenschnitt hatte, gibt es diese Verbindung auch in der Kopie. Hättest du die zweite Pivot-Tabelle von Grund auf neu erstellt, müsstest du im Datenschnitt noch die Berichtsverbindung darauf setzen (Menü „Datenschnitt | Berichtsverbindungen“).
In der kopierten Version entfernen wir alle Felder und ziehen dann nur noch das Region-Feld in den Zeilenbereich. Also das Feld, das wir auch im Datenschnitt verwenden. Dann rufen wür das Menü „Entwurf“ auf und deaktivieren alle Gesamtergebnisse:
Ergebniszeilen und Spalten werden ausgeblendet
Übrig bleibt eine minimalistische Pivot-Tabelle, die nur den Feldnamen und die gerade gewählten Regionen enthält. Wenn nun im Datenschnitt andere Regionen gewählt werden, spiegelt sich das auch in unser Mini-Pivot. Jetzt muss nur noch die Überschrift über der eigentlichen Pivot-Tabelle erstellt werden.
Die Überschrift erstellen
Alle gewählten Regionen sollen durch Komma getrennt angezeigt werden. Dafür gibt es verschiedene Möglichkeiten. Wer Excel 2019 oder neuer einsetzt, hat es ganz besonders einfach, denn hier steht die Funktion TEXTVERKETTEN zur Verfügung.
=TEXTVERKETTEN(Trennzeichen;Leer_ignorieren;Text1;Text2;...)
Für die zu verkettenden Text kann man auch einen Zellbereich angeben. Da es in unserem Beispiel maximal 4 Regionen gibt, würde das so aussehen:
=TEXTVERKETTEN(", ";WAHR;J2:J5)
Das zweite Argument WAHR bedeutet, dass leere Zellen ignoriert werden sollen (das Argument hätte man auch weglassen können)
Regionen per TEXTVERKETTEN ausgeben
Anwender mit Excel 2016 oder älter müssen sich mit etwas weniger Komfort begnügen, das es hier noch kein TEXTVERKETTEN gibt. Hier lassen sich einzelne Text nur über die VERKETTEN-Funktion oder über das &-Zeichen zusammenfügen:
=J2&", "&J3&", "&J4&", "&J5
Das hat allerdings den Nachteil von unnötigen Kommas, wenn nicht alle Regionen ausgewählt sind
Variante ohne TEXTVERKETTEN
Um das zu vermeiden, muss mit mehreren WENN-Verschachtelungen gearbeitet werden:
=J2&WENN(J3<>"";", "&J3;"")&WENN(J4<>"";", "&J4;"")&WENN(J5<>"";", "&J5;"")
Verbesserte Variante ohne TEXTVERKETTEN
Es wird also jedes mal geprüft, ob die betreffende Zelle einen Wert enthält. Falls ja, wird ein Komma und der jeweilige Wert ausgegeben, falls nein, dann wird eben nichts ausgegeben. Nicht ganz so schön, aber bei einer überschaubaren Anzahl an Elementen noch machbar.
Damit haben wir das Pflichtprogramm schon erfüllt.
Die Kür
Wenn es noch etwas eleganter werden soll, dann können wir noch unterscheiden, ob nur eine Region oder ob mehrere Regionen ausgewählt wurden und die Überschrift entsprechend anpassen. Dazu wird einfach die Anzahl der Elemente in der Pivot-Tabelle ermittelt und der passende Text („Region“ oder „Regionen“) ausgegeben:
=WENN(ANZAHL2(J2:J5)>1;"Umsätze für die Regionen "&TEXTVERKETTEN(", ";WAHR;J2:J5);"Umsätze für die Region "&J2)
Unterscheidung: Eine oder mehrere Regionen
Wie du siehst, ist es nicht so dramatisch, einen Datenschnitt auszulesen. Selbstverständlich funktioniert das auch mit mehreren Datenschnitten, du musst nur jeweils eine eigene Mini-Pivot-Tabelle erstellen.
Die hier vorgestellte Technik funktioniert allerdings nur mit Datenschnitten, die auf einer Pivot-Tabelle basieren. Setzt du den Datenschnitt direkt auf einer formatierten („intelligenten“) Tabelle auf, funktioniert dieser Trick leider nicht.
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.