Workshop Profi-Berichte: Dynamische Präsentationen mit "extrahierenden" Funktionen wie INDEX oder BEREICH.VERSCHIEBEN

Einführung

Wer will das nicht: auf jede Frage in einer Präsentation sofort die richtige Antwort wissen. Und das am besten mit einer passenden Grafik belegen.

Möglich machen dies "extrahierende" Funktionen wie INDEX oder BEREICH.VERSCHIEBEN, die sich in verschiedenen Kombinationen verwenden lassen, um Quelldaten aus Arbeitsblättern gezielt zu übernehmen und in hochflexibel gestaltbaren Diagrammen und Berichtstabellen zu präsentieren.

Extrahierende Formeln lassen sich mit Excel-Funktionen erzeugen, von denen ein Teil in Abbildung 1 aufgelistet ist. Die für unser Thema 4 wichtigsten und in diesem Praxisbeitrag behandelten Funktionen, nämlich INDEX, BEREICH.VERSCHIEBEN, VERGLEICH und SVERWEIS sind dort zusammen mit ihrer jeweiligen Syntax optisch hervorgehoben.

Die hier beschriebenen Beispiele sind nach Regeln der sogenannten rS1.Methode aufgebaut, die auf Basis solcher Vorgaben entwickelt wurde.

Alle Beispiele können Sie anhand einer Musterlösungsdatei nachvollziehen .

[1]

[2]

1 Versionsabhängigkeit und Struktur

Die nachfolgenden Beschreibungen und Funktionalitäten betreffen die Excel-Version 2010 und somit – obgleich mit kleinen technischen Unterschieden im Detail – auch die Nachbarversionen 2007 und 2013. Die beschriebenen Modelle können mit geringen formalen Einschränkungen, funktional aber gleichartig, auch unter Excel 2003 angefertigt werden.

Zum Benutzen der Lösungen sind keine speziellen Kenntnisse erforderlich. Eine individuelle Anpassung oder eine eigene gleichartige Anfertigung erfordert jedoch solide Excel-Kenntnisse. Dabei geht es dann im Wesentlichen um die Einrichtung und Detailformatierung von Tabellen und einfachen Diagrammen, um die Festlegung von Bereichsnamen und deren Verwendung in komplexen Formeln sowie um die Einrichtung bedingter Formatierungen, wie sie seit der Version Excel 2007 verfügbar sind.

Abb. 1: Diese Funktionen erlauben dynamische Zugriffe auf statische und auf variable Datenquellen

2 Art der Beispiele und Handhabung des Modells

Beim Aufbau der hier geschilderten Visualisierungslösungen sind 2 wesentliche Unterscheidungen bezüglich Inhalten und Strukturen der Datenquellen wichtig:

• Der einfache Fall: Berichtsstruktur und Quelldatenstruktur sind identisch und statisch. Die Matrix, die Anordnung und die Sortierung der Berichtsdaten entspricht 1:1 den Quelldaten. Monat für Monat werden die Werte in unveränderlicher Art und Weise geliefert und dargestellt. Ein typisches Beispiel wäre etwa ein Geschäftsführerbericht, in dem ausgewählte Unternehmensdaten der wichtigsten Konten zusammengefasst sind (Beispiel 1, Arbeitsblatt Focus 1, hier ohne Abbildung).
• Der kompliziertere Fall: Die Berichtsstruktur ist statisch, aber nicht oder nur teilweise identisch mit der Quelldatenstruktur, die zudem ihrerseits variabel ist. Die Quelldaten enthalten z. B. mehr Informationen als berichtet werden müssen, ihre Anzahl kann sich jederzeit ändern und die Abrufstruktur des Berichts entspricht nicht jener Ordnung, die aktuell in den Quelldaten herrscht. Ein typisches Beispiel wäre etwa ein Bericht, der die Erlösdaten aller Kunden eines Unternehmens enthält, also sowohl Zugänge und Abgänge berücksichtigen muss wie auch unterschiedliche Sortierungen der Zugriffsdaten verarbeiten soll (Beispiel 2, Arbeitsblatt Focus 2, s. Abb. 2).

Abb. 2: Berichtsblatt mit Diagramm, Datenzeile(n) und Steuerkonsole zur Datenauswahl

Die Visualisierungen der beiden angesprochenen Fälle finden Sie in den Arbeitsblättern Focus 1 und Focus 2. Beide zeigen ein Berichtsblatt als Bestandteil eines adressatenorientierten Berichtswesens. Sie sind sehr ähnlich aufgebaut und einfach zu nutzen:

• Im Arbeitsblatt Focus 1 finden Sie in der Datenzeile unten links ein Dropdown-Steuerelement, mit dessen Hilfe Sie die Monatsdaten verschiedener Unternehmenskonten in die Datenzeile übernehmen und im Diagramm darstellen können.
• Im Arbeitsblatt Focus 2 (vgl. Abb. 2) finden Sie rechts eine Steuerkonsole mit einem Listen-Steuerelement zur Auswahl von Kunden und 2 Optionsfelder. Mit den Optionsfeldern entscheiden Sie, ob die Liste zur Kundenauswahl nach dem Alphabet der Kundennamen angeboten wird oder nach Kundennummern. Die Erlösdaten aus den letzten 7 Jahren des aktuell selektierten Kunden werden in 2 Datenzeilen – sie sind im Ergebnis identisch, nach Formelart jedoch unterschiedlich – und in ein Diagramm übernommen.

3 Aufbau und Funktionalitäten

In der Beispieldatei sind 10 Arbeitsblätter vorhanden. Davon sind jeweils 4 einem der vorstehend erwähnten Beispiele zugeordnet. Zusätzlich sind vorhanden:

Arbeitsblatt Startseite mit einigen Kurzinformationen und Hyperlinks zu den restlichen Arbeitsblättern. Von diesen aus kehren Sie mit Klick auf den Eintrag Startseite dorthin zurück.

Arbeitsblatt Namensliste mit einer kurzen, für Dokumentationszwecke eingerichteten Liste der in der Arbeitsmappe eingerichteten Bereichsnamen und ihrer Bezüge.

Die funktionale Fassade des Beispiels 1 (Präsentation ausgewählter Unternehmensdaten) ist das Arbeitsblatt Focus 1 (ohne Abbildung). Das Beispiel 2 (Präsentation erlösspezifischer Kundendaten) zeigt seine Informationen im Arbeitsblatt Focus 2 (s. Abb. 2)....