Der 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 ...

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

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

... 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:

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:

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:

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:

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)

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":

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):

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):

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