Professionelles Berichtswesen: Variable Daten mit dynami ... / 2 Dynamisierung mittels Gültigkeitslisten

Wir haben Ihnen in anderen Praxisbeiträgen bereits einige Präsentations- und Berichtslösungen in Excel vorgestellt, die ganz ohne Programmierung einen hohen Grad an Flexibilität und Dynamik aufweisen. Die dafür verantwortlichen Komponenten waren jeweils Formeln, deren Argumente sich mit Steuerelementen variieren lassen. Der Einsatz von Steuerelementen ist, jedenfalls wenn es sich um die sog. Formularsteuerelemente handelt, nicht besonders anspruchsvoll, kann aber dennoch zu eindrucksvollen Ergebnissen führen.

Im vorliegenden Beispiel kommt nun allerdings eine noch einfachere Methode zum Einsatz: Wenn Ihre Dynamisierungsaufgabe den Charakter einer einfachen Listenauswahl hat, können Sie, mit geringfügigen Einschränkungen des Anwendungskomforts, statt Steuerelemente auch das Feature Datenüberprüfung (früher: Gültigkeitsprüfung) benutzen. Dabei ist der Umfang Ihrer Vorgabenliste kaum limitiert: Im Beispiel-Erlösdiagramm können Sie per Mausklick eine von 13 Zeilen einer Datenquelle zur Visualisierung bestimmen. Es könnten aber auch 130 Zeilen oder 1.300 sein. In den Erlöstabellen erzeugen Sie mit der gleichen Methodik verschiedene Farbformatierungen oder verschiedene Zahlenformate, deren beider Anzahl ebenso nach oben offen wäre.

2.1 Gültigkeitslisten einrichten

Mit einer in Excel definierten Gültigkeitsliste können Sie festlegen, welche Inhalte in von Ihnen ausgewählten Zellen der Arbeitsmappe eingegeben werden können. Dazu ist an beliebiger Stelle eine Liste einzurichten, in der alle gültigen Inhalte hinterlegt werden. Anschließend bestimmen Sie jene Eingabezellen, deren Aufnahmefähigkeit entsprechend der Listeninhalte reduziert sein soll. Wie das technisch gelingt, ist weiter unten beschrieben.

Grundsätzlich werden an die Lokalisierung und die Beschaffenheit von Gültigkeitslisten keine speziellen Ansprüche gestellt. Dennoch ist eine klare, übersichtliche Gestaltung, wie sie hier im Arbeitsblatt Listen vorgenommen wurde (s. Abb. 3), von Vorteil.

Abb. 3: Diese 3 Listen ermöglichen – auf Umwegen – die Dynamik des Modells

In den Listenköpfen (Zeile 5) sind die Bereichsnamen dokumentiert, die den darunter befindlichen Listen zugewiesen wurden; z. B. hat der Zellbereich C6:C9 den Bereichsnamen ListeMaxMin. Eine derartige Festlegung von Namen ist optional, sie erzeugt aber – in diesem Modell besonders wichtig – ein verbessertes Verständnis der Konstruktion.

Hinweis

Zugriff auf den Namensmanager

Den Namensmanager erreichen Sie auf dem Befehlsweg Formeln → Definierte Namen → Namensmanager oder schneller mit der Tastenkombination Strg + F3.

Nach der Beschreibung der vorbereitenden Arbeiten nun aber zum technischen Kern des vorliegenden Beispiels: Sie erzeugen in einer der gelben Zellen des Blattes Tabellen 2020 einen Text, der in einer mit dieser Zelle verbundenen Liste enthalten ist. Dann kommt das Wichtigste: Eine Formel in einer Formatierungsregel oder in einer anderen Zelle entnimmt dem aktuell in der Gültigkeitszelle befindlichen Text einen bestimmten Teil. In unserem Beispiel sind es jeweils ein oder 2 Zeichen:

• Von den Texten der Liste mit dem Bereichsnamen ListeMaxMin wird zur Weiterverarbeitung das jeweils letzte Zeichen entnommen, also x, n, s oder e.
• Von den Texten der Liste mit dem Bereichsnamen ListeZahlenFormat wird das jeweils erste Zeichen entnommen, also A, B, C oder D.
• Von den Texten der Liste mit dem Bereichsnamen ListeProdukte werden die jeweils ersten beiden Zeichen entnommen, also 01, 02 usw. bis 13.

Eine detaillierte Beschreibung der verwendeten Formeln finden Sie unter Kapitel 2.3. Solche Festlegungen sind willkürlich, jede andere Ihnen günstig erscheinende Verwendung wäre ebenso möglich und effektiv. Selbstverständlich wäre es auch denkbar, die Volltexte der Liste zur Weiterverarbeitung mittels Formeln zu gebrauchen. Allerdings wäre das, wie Sie bei den relevanten Formelkonstruktionen sehen werden, unpraktisch.

Praxis-Tipp

Listeninhalte systematisieren

Grundsätzlich sind bei den hier vorgestellten, sehr ungewöhnlichen Verwendungen der Gültigkeitslisten streng systematische, leicht verständliche Textgestaltungen vorteilhaft. Eine Verwendung von A, B, C usw. oder von 1, 2, 3 usw. ist natürlich sehr viel eingängiger als die hier ebenfalls demonstrierte Verwendung von x, n, s, e.

Letztere kommt nur deswegen zum Einsatz, um deutlich zu machen, dass bei dem hier praktizierten Verfahren letztlich alles nutzbar ist, was nützlich erscheint und was eindeutig ist – in der Liste also auf diese Art nur einmal vorkommt.

2.2 Zellen mit beschränkter Gültigkeit einrichten

Die Datenüberprüfung ist ein interessantes und recht mächtiges Tool, das in Excel leider ein Schattendasein führt, ja vielen Anwendern nicht einmal bekannt ist. Auch hier kann von den zahlreich möglichen Einstellungen leider nur eine Einzige vorgestellt werden.

Angenommen, sie wollen die Eingabegültigkeit einer Zelle beschränken. Zulässige Inhalte sollen nur jene sein, die in einer von Ihnen bereits erstellten und benannten Liste enthalten sind. Gehen Sie dazu im Zusammenhang mit der Nummerierung in Abbildung 4 wie folgt vor:

Step by Ste...