Dynamischer Druckbereich in Excel-Pivotberichten mit Bereich.Verschieben-Formel

Excel dynamischer Druckbereich TitelDer Druckbereich kann mit einer Bereich.Verschieben-Formel und einem Trick perfekt dynamisiert - also aktiv über Formeln im Bericht gesteuert - werden.

Ein wichtiger Anwendungsfall ist die Steuerung des Druckbereichs in Pivotberichten bei denen beispielsweise eine Slicer-Leiste nicht mitgedruckt werden soll. Der Blogbeitrag zeigt, wie in diesem Szenario abhängig vom aktuellen Drill-Status des Pivotberichts der Druckbereich dynamisch ermittelt wird. Vielen Dank an Holger Gerths von der HI-CHART GmbH in Berlin für den wertvollen Input zu dieser Lösung!

Ausgangssituation

Wir haben einen Pivotbericht in Excel mit einer Slicer-Leiste am linken Rand ...

Excel dynamischer Druckbereich 01

... diese Slicer-Leiste möchten wir lieber aus dem Druckbereich ausschließen:

Excel dynamischer Druckbereich 02

Natürlich kann der gewünschten Druckbereich manuell markiert werden und mit der Funktion Seitenlayout -> Druckbereich -> Druckbereich festlegen als solcher definiert werden ...

Excel dynamischer Druckbereich 03

... jedoch müßte dieser Druckbereich jedes Mal neu konfiguriert werden, sobald sich die Anzahl der Zeilen im Pivotbericht durch einen Drilldown / Drillup ändert. Hier werden beispielsweise durch den Drilldown des Knotens "3000000 Gewinn- und Verlustrechnung" die Zeilen nur bis zum definierten Druckbereich in Zeile 37 angedruckt, der untere Bereich des Pivotberichts fehlt also am Ausdruck:

Excel dynamischer Druckbereich 04

Lösung mit Bereich.Verschieben-Formel

Zuerst fügen wir am linken Rand die beiden neuen Spalten A und B ein. Die Spalte B dient zur Ermittlung der relevanten Zeilen des dynamischen Druckbereichs. Die Zeilen 9 bis 21 gehören fix zum Druckbereich, ab Zeile 22 wird mit einer Formel die letzte aktive Zeile im Pivotbericht ermittelt (hier gibt es sicherlich viele Varianten, hier wird eine mögliche gezeigt). Diese Formel von Zelle B22 wird bspw. bis Zelle B200 (= maximal angenommene Länge des Pivotberichts) nach unten auskopiert:

Excel dynamischer Druckbereich 05

In Zelle B8 wird mit einer MAX-Formel die höchste Zeilennummer des Bereich B9:B200 ermittelt, diese Zelle wird mit dem (sheet-spezifischen) Bereichsnamen FLEXprintLines versehen. Entsprechend dem Drillstatus des Pivotberichts besteht der Berichtsbereich aktuell aus 28 druck-relevanten Zeilen:

Excel dynamischer Druckbereich 06

Jetzt wird die Basis für den dynamischen Druckbereich gelegt, indem die erste Zeile des relevanten Druckbereichs markiert wird und der sheet-spezifische Bereichsname FLEXprintArea für diesen Zellbereich definiert wird:

Excel dynamischer Druckbereich 07

Hinweis: die Verwendung von sheet-spezifischen Bereichsnamen ist in diesem Szenario sehr zu empfehlen, da dann in jedem Berichtssheet die gleiche Namenskonvention bzw. der gleiche Grundaufbau des Formelwerks verwendet werden kann und die Formeln ggfs. sogar 1:1 weiterkopiert werden können.

Jetzt geht es an das Herzstück, nämlich die Dynamisierung des Druckbereichs FLEXprintArea mit dem zuvor ermittelten Zeilenparameter FLEXprintLines. Dazu wird aus der Rohformel

=Kennzahlenkatalog!$I$9:$O$9

mittels einer Bereich.Verschieben-Formel der umspannte Bereich um die Anzahl der relevanten Zeilen dynamisiert:

=Kennzahlenkatalog!$I$9:BEREICH.VERSCHIEBEN(Kennzahlenkatalog!$O$9;Kennzahlenkatalog!FLEXprintLines;0)

Excel dynamischer Druckbereich 08

Abschließend wird der Namensbereich Druckbereich - dieser wird bei der manuellen Definition eines Druckbereichs im Menü Seite Einfügen -> Druckbereich -> Druckbereich einfügen von Excel selbst erzeugt -  auf den dynamischen Namensbereich FLEXprintArea "verdrahtet":

Excel dynamischer Druckbereich 09

Jetzt kann der Pivotbericht beliebig nach unten auf- und zugedrilled werden, der Druckbereich paßt sich volldynamisch an (somit wurde der unerwünschte Slicer-Block links erfolgreich aus der Druckansicht eliminiert):

Excel dynamischer Druckbereich 10

Beachten Sie bitte, daß im Feld Druckbereich im Seite einrichten Dialog nicht die Formel sondern das jeweilige Resultat der Bereich.Verschieben Formel angezeigt wird (dieser Bezug darf nicht überschrieben werden, da sonst der Bezug zum Namensbereich FLEXprintArea verloren geht und wieder neu hergestellt werden müßte):

Excel dynamischer Druckbereich 11

Zusammenfassung

Der Trick besteht darin, die Bereich.Verschieben-Formel nicht direkt in den Namensbereich Druckbereich und auch nicht direkt in den Seite einrichten Dialog in das Feld Druckbereich einzufügen (das würde nicht funktionieren) sondern in einen eigenen Namensbereich (hier: FLEXprintArea) zu ermitteln und diesen Namen als Quelle für den Druckbereich im Namens-Manager einzusetzen.

Natürlich kann die hier gezeigte Dynamisierung für die Zeilen des Pivotberichts auf den Drillstatus in den Spalten ausgedehnt werden.

Quellennachweis

Holger Gerths von HI-CHART GmbH, www.hi-chart.com

Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Kennen Sie schon data1.io?

data1.io ist unsere innovative Cloud Anwendung zum Sammeln, Aggregieren und Teilen von Geschäftsdaten innerhalb und außerhalb des Unternehmens.

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!