Wer in Excel Formulare erstellt, hat immer die Gelegenheit Prozesse im Unternehmen zu verbessern und die Datenqualität zu steigern, indem er seinen User durch eine vorab eingebaute Intelligenz im Formular das Eingeben von Antworten erleichtert.

Kostenbudgetformular als Beispiel

In diesem Beitrag zeige ich in einem Beispiel wie man mit einfachen Dropdownfelder ein Formular bereits viel Intelligenter gestalten kann. Wer die Beispieldatei selber in Excel anschauen möchte kann das hier tun:

Das Ziel

Wir wollen unser Kostenbudgetformular so verbessern, dass das Eingeben des Budgets für den Benutzer einfacher und die Qualität der Budgeteingaben aber gleichzeitig besser wird.

Wir entscheiden uns dies zu tun in dem wir den User drei Optionen anbieten:

1. Eine gleichmässige Verteilung der Kosten über das ganze Jahr. z.B. für die Mietkosten.Dass ein Bereichsleiter die Ausgaben für die Miete nicht monatlich eingeben will ist verständlich. Speziell wenn er dutzende von Zeilen auf dieser Ebene festlegen muss. Die Eingabe des Gesamtwertes für das Jahr muss also reichen. Mit unserem Dropdown Menü geben wir Ihm nun die Möglichkeit den Gesamtbetrag für das Jahr einzugeben und diesen dann ebenmässig auf alle Monate zu verteilen.
2. Eine saisonalisierte Verteilung anhand der Vorjahreswerte. z.B. für Heizkosten.Dasselbe gilt für Kosten die schwer zu beeinflussen sind aber einen saisonalen Charakter besitzen. Hier will der User auch nicht selber die Sasionalität berechnen, weshalb wir es ihm gleich im Menü anbieten.
3. Eine eigene Gewichtung für Kosten die sich nicht nach den oben genannten Werten richten.Die Möglichkeit selber einen Verteilschlüssel festzulegen ist ebenfalls wichtig, weshalb wir es auch anbieten.

Dropdown als Lösung

Dropdownfelder eignen sich hervorragend um solche Optionen anzubieten, denn sie sind sehr benutzerfreundlich und für die meisten User sofort verständlich. Wir entscheiden uns deshalb den Usern eine Auswahl von 3 Standard Kostenverteilungsschlüssel anzubieten die mit Formeln berechnet sind und via Dropdownfelder gesteuert werden.

Wie ein ActiveX-Steuerelement Dropdownfeld in einem Formular eingefügt wird

Hier nun die einzelnen Schritte die notwendig sind um ein Dropdownfeld in einem Formular einzubetten.

Vorab: Entwicklertools Einschalten

Wenn im Ribbon nicht bereits das Entwicklertools Menü sichtbar ist, so muss man es zuerst einblenden. Dies macht man in dem man auf

Datei>Optionen>Menüband anpassen geht und beim Entwicklertools Menü einen Hacken setzt.

Schritt 1: Dropdownfeld im Entwicklermenü auswählen

Im Entwicklertools Menü auf Einfügen gehen und das ActiveX Dropdownfeld auswählen.

ActiveX-Steuerelement einfügen

Schritt 2: Dropdownfeld erstellen mit der Maus

In Excel kann das neue Dropdownfeld nun eingefügt werden. Im Arbeitsblatt auf die Zelle klicken wo das Dropdownfeld sein soll und ohne den Mausbutton loszulassen mit der Maus ein wenig nach rechts ziehen. Wenn man den Knopf dann loslässt so hat man ein neues Steuerelement.

Der Entwurfsmodus ist direkt nach dem Erstellen eines Steuerelements bereits aktiviert.

Dies ist notwendig damit das Steuerelement zum Bearbeiten ausgewählt werden kann.

Schritt 3: Dropdownfeld Eigenschaften setzen

In unserem Beispielformular kann man mit dem Entwurfsmodus, der auch einfach per Mausklick aktiviert werden kann, so ein Dropdownfeld selektieren. Mit dem selektierten Element und einem rechts Klick öffnen sich die Steuerelement Eigenschaften.

Das Eigenschaften Fenster öffnet sich:

Im Eigenschaftsfenster, welches hier nach Kategorien dargestellt wird, interessieren uns in unserem Beispiel in erster Linie die beiden Punkte LinkedCell und ListFillRange unter der Rubrik”Verschiedenes“.

Was machen diese beiden Eigenschaften?

• LinkedCell ist die Zelle welche verändert wird wenn ein User eine neue Auswahl trifft im Dropdownmenü.
• ListFillRange wie der Name auf Englisch schon sagt, wird dazu benutzt um die Zellen auszulesen in der die Liste welche im Dropdown erscheinen soll auszuwählen.

Bei beiden Eigenschaften muss darauf geachtet werden dass man den korrekten Zellenverweis hineinschreibt. Leider ist es nicht möglich den Bereich mit der Maus zu markieren und so einzufügen.

In Beispiel habe ich für den ListFillRange zwei Spalten markiert. Mit einem ActiveX Steuerelement haben wir die Möglichkeit dem User einen Auswahl Text zu zeigen, als Rückgabewert (der in LinkedCell erscheint) aber einen anderen Wert zu zeigen.

Hier der eingetragene Bereich in ListFillRange: B26:C28

Im Beispiel habe ich in der ersten Spalt in ListFillRange einen Text und in der zweiten Spalte einen Wert. Mit den beiden Punkten BoundColumn und ColumnCount in der Rurik “Daten” können wir nun sicherstellen dass Excel im Dropdown Menü die erste Spalte mit dem Text den User zeigt, aber die zweite Spalte als Wert in die LinkedCell schreibt.

Wir setzen ColumnCount auf 1. So sieht der User nur den Text aus der ersten Spalte im Dropdownbereich (im Beispiel also die Werte von B26:B28).

BoundColumn bekommt den Wert 2. Excel weiss nun dass es für den Rückgabewert die zweite Spalte benutzen soll (im Beispiel die Werte aus dem Bereich C26:C28).

Verbindung zu den Eingabewerten

In unserer Beispieldatei sind die LinkedCell Eigenschaften für jedes Dropdownfeld im Formular individuell gesetzt, so dass jedes Dropdownfeld auf eine eigene Zelle verweist.

Diese Zellen müssen noch mit den Formeln welche die Gewichtungen Berechnen Verbunden werden. Verbindung zu den Eingabewerten machen wir im Beispiel Formular mit der WAHL() Funktion welche als ihr erstes Argument die LinkedCells haben des jeweiligen Dropdownfeldes.

So steht denn in der Zelle D9 im Beispiel Formular die Formel:

=$Q9*WAHL($D$18;D$21;D$20;D$7)

Alle Eingabezeilen werden analog dieser Formel aufgebaut.

Die drei Dropdownfelder in der Beispieldatei

So können die User nun Ihre Werte schneller erfassen. Zudem bietet das Wechseln zwischen den Kostenschlüsselvarianten die Möglichkeit schnell einen Vergleich zu machen wie die Verteilung aussehen würde wenn Sie mit einer der anderen Eingabevarianten erstellt würden.

Dank dem Einsatz des Dropdownfeldes kann der User nun auf einfache Weise seine Budgetwerte einfacher und zugleich im Verbund mit den Kostenschlüssel in höherer Qualität abgeben.