Dynamische benannte Bereiche in Excel

Bei Veröffentlichung von Excel Reports am SharePoint via Excel Web App muss auch immer ein benannter Bereich angegeben werden, der dann im SharePoint angezeigt wird.

Dies ist bei statischen Berichten kein Problem. Verwendet man allerdings Pivot Tabellen kann die Dynamik dazu führen, dass Daten außerhalb des benannten Bereichs dargestellt werden da Excel den Bereich beim Drillen nicht automatisch vergrößert. Ist der Bericht in SharePoint eingebunden, sind diese Daten für User nicht mehr ersichtlich.Als Lösung kann der Bereich wie folgt dynamisch definiert werden:

  1. Identifizieren einer Spalte zur Bestimmung der Größe der Pivot Tabelle, zumeist ist das die Kopfspalte.
  2. Bestimmung der Größe eines etwaigen Überschriftenbereichs
  3. Über FORMELN > Namens-Manager folgende Formel für den betreffenden Bereich hinterlegen

=OFFSET($A$1;0;0;COUNTA($A:$A)+5;10)

bzw. auf Deutsch

=BEREICH.VERSCHIEBEN($A$1;0;0;ANZAHL2($A:$A)+5;10)

wobei Spalte A die Kopfspalte der Pivot Tabelle ist, die Pivot Tabelle in Zeile 5 beginnt und die Bereite fest 10 Spalten beträgt.

Ich finde es hilfreich zur Überprüfung das Ergebnis der COUNTA-Funktion in einer Zelle anzuzeigen. Möchte man auch die Breite des benannten Bereichs dynamisch setzen, so kann im obigen Beispiel die feste Breite von 10 Spalten durch COUNTA auf Basis einer Zeile einsetzen. Zur Berücksichtigung einer etwaigen Mindestbreite kann zusätzlich die MAX-Funktion einsetzen:

=OFFSET($A$1;0;0;COUNTA($A:$A)+5;MAX(COUNTA($6:$6);10))

 

 

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s