Excel-Tricks mit Steuerelementen (Teil 1) 5

Mit einem kleinen Trick lässt sich das Ergebnis eines Steuerelements flexibler ausgeben
 

Excel eignet sich hervorragend dafür, eigene Formulare zu gestalten. Über eine Vielzahl von vordefinierten Steuerelementen lassen sich Auswahllisten, Checkboxen, Optionsfelder und einiges mehr realisieren.

Üblicherweise wird dabei mit diesen Steuerelementen eine fixe Zelle verknüpft, in der das ausgewählte Ergebnis eingetragen wird.

Im heutigen Artikel zeige ich dir am Beispiel des Kombinationsfeldes (= Dropdown-Feld), wie man den Ausgabebereich für solche Steuerelemente ein wenig flexibler gestalten kann.

Und so geht’s:

Die Entwicklertools aktivieren

Um die hier besprochenen Steuerelemente nutzen zu können, musst du gegebenenfalls erst die Entwicklertools einblenden lassen – standardmäßig wird diese Registerkarte nämlich nicht angezeigt.

Du findest sie in den Excel-Optionen im Bereich “Menüband anpassen”. Hier wählst du oben die Kategorie “Hauptregisterkarten” und kannst dann die Registerkarte “Entwicklertools” über die entsprechende Schaltfläche zum Menüband hinzufügen:

Entwicklertools aktivieren (ab Excel 2010)

Entwicklertools aktivieren (ab Excel 2010)

Unter Excel 2007 geht das leider noch nicht, hier muss stattdessen in der Kategorie “Häufig verwendet” der Haken bei der Option “Entwicklerregisterkarte in der Multifunktionsleiste anzeigen” gesetzt werden:

Entwicklertools aktivieren (Excel 2007)

Entwicklertools aktivieren (Excel 2007)

Nach diesen Vorarbeiten taucht die neue Registerkarte in Deinem Menüband auf:

Die neue Registerkarte

Die neue Registerkarte

Ein Eingabefeld für mehrere Ausgabefelder

In meinem ersten Beispiel geht es um einen einfachen kleinen Dienstplan. In Spalte D stehen die verschiedenen Tage, in Spalte E soll der am jeweiligen Tag diensthabende Mitarbeiter eingetragen werden. Die Liste mit den verfügbaren Mitarbeitern liegt im Bereich H2:H6

Einfacher Dienstplan

Einfacher Dienstplan

Der Einfachheit halber habe ich für diesen Bereich den Namen “Mitarbeiter” vergeben:

Definierter Name für die Mitarbeiterliste

Definierter Name für die Mitarbeiterliste

Die Befüllung des Dienstplans soll nun mit Hilfe eines einzigen Eingabefeldes erfolgen.

Dazu fügen wir aus den Entwicklertools aus der Gruppe der Formularsteuerelemente ein sogenanntes Kombinationsfeld ein:

Kombinationsfeld einfügen

Kombinationsfeld einfügen

Der Mauszeiger verwandelt sich in ein Fadenkreuz, und bei gedrückter linker Maustaste lässt sich das neue Feld in der gewünschten Größe an der gewünschten Stelle einfügen.

Noch ist das Feld jedoch ohne Funktion. Damit ich meine Mitarbeiter tatsächlich zur Auswahl angeboten bekomme, muss ich mit einem Rechtsklick auf das Kombinationsfeld die Option “Steuerelement formatieren…” aufrufen:

Steuerelement formatieren

Steuerelement formatieren

Im Register “Steuerung” gebe ich im Feld “Eingabebereich” den zuvor definierten Namen meiner Mitarbeiterliste ein (solltest du keinen Namen vergeben haben, dann kannst du hier natürlich einfach die Zelladressen eingeben, also z.B. $H$2:$H$6)

Den Eingabebereich festlegen

Den Eingabebereich festlegen

Nun muss Excel noch wissen, in welche Zelle der ausgewählte Mitarbeiter geschrieben werden soll. Dies Information wird im Feld “Zellverknüpfung” eingetragen. Da das erste Feld im Dienstplan die Zelle E2 ist, trage ich das mal hier ein:

Die verknüpfte Zelle angeben

Die verknüpfte Zelle angeben

Probieren wir unser Dropdown-Feld einmal aus. Zuerst musst du irgendwo in deine Tabelle klicken (oder die ESC-Taste drücken), damit das Feld nicht mehr markiert ist. Nun lässt sich die Auswahlliste öffnen und der gewünschte Mitarbeiter auswählen:

Das Dropdown-Feld funktionert

Das Dropdown-Feld funktioniert… fast.

Wenn du nacheinander verschiedene Einträge auswählst, werden dir 2 Dinge auffallen:

  1. Es wird nicht der Name, sondern die laufende Nummer in E2 eingetragen
  2. Bei jeder neuen Auswahl wird die Nummer immer wieder überschrieben

Zu Punkt 1 ist zu sagen, dass beim Kombinationsfeld aus den Formularsteuerelementen tatsächlich nur die laufende Nummer des gewählten Eintrags zurückgeliefert wird, und nicht der ausgewählte Wert selbst. Dies soll uns momentan aber nicht weiter stören, denn über die INDEX-Funktion lässt sich der dazu passende Name ja jederzeit ermitteln:
=INDEX(Mitarbeiter;E2)

Ärgerlicher ist Punkt 2, bei dem der einmal ausgewählte Wert immer wieder überschrieben wird. Wie bringen wir Excel also dazu, nach jeder getroffenen Auswahl den Wert in die nächste freie Zeile zu schreiben?

Ein Eingabefeld befüllt mehrere Ausgabefelder

Der Trick dabei ist, keine fixe Zelle anzugeben, sondern einen dynamischen Bereich. Diesen erzeugen wir über einen definierten Namen und eine damit verbundene Formel.

Über “Formeln – Namen definieren” legen wir einen neuen Namen fest:

Neuen Namen definieren

Neuen Namen definieren

In meinem Beispiel verwende ich als Namen “Eingabeziel”. Im Feld “Bezieht sich auf” tragen ich jedoch keinen festen Zellbezug ein, sondern die folgende Formel:

=BEREICH.VERSCHIEBEN($E$2;ANZAHL2($E:$E)-1;;)

Als Bezug eine Formel eingeben

Als Bezug eine Formel eingeben

Damit haben wir einen dynamischen Eingabebereich definiert, der sich immer auf die nächste leere Zelle in Spalte E bezieht.

ANZAHL2($E:$E)-1
berechnet immer, wieviele Einträge es in Spalte E bereits gibt und zieht davon den Wert 1 ab (für die Überschrift).

Dieses Ergebnis verwenden wir in der BEREICH.VERSCHIEBEN-Funktion um festzulegen, um wieviele Zeilen die Zelle E2 nach unten verschoben werden soll. Jetzt müssen wir in unserem Kombinationsfeld nur noch den definierten Namen als Zellverknüpfung angeben:

Die Zellverknüpfung ändern

Die Zellverknüpfung ändern

Und schon funktioniert unser Kombinationsfeld in der gewünschten Weise und schreibt jede neue Auswahl in eine neue Zeile:

Das fertige, flexible Eingabefeld

Das fertige, flexible Eingabefeld



 
Nächste Woche werde ich dir zeigen, wie man ohne Umwege auch den Namen in die Dienstplanliste bekommt und wie man z.B. gezielt einzelne Tage in der Liste anspringen kann.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Excel-Tricks mit Steuerelementen (Teil 1)

  • Hans-Peter Follmann

    Hallo Martin,
    für dieses spezielle Problem habe ich eine etwas einfachere Lösung ohne Steuerelemente. Ich wähle die Zelle E2 an, klicke dann oben auf “Daten” und dort auf “Datenüberprüfung”. Dann erscheint ein Fenster, in dem ich bei Einstellungen “Liste” wähle und bei Quelle “$H$2:$H$6” angebe. Nun brauche ich nur noch die Zelle E2 herunterzuziehen bis zum Ende der Datenspalte.
    Klicke ich nun eine beliebige Zelle der Spalte E an, werden mir, beim Klicken auf den Pfeil, alle Namen angezeigt, die in Frage kommen.
    So können auch in mehreren Spalten verschiedene Posten des Dienstplanes mit unterschiedlichen Namen versorgt werden.

    Das ist vielleicht eine Anregung zur Erweiterung.
    mit freundlichem Gruß
    HP Follmann

    • Martin Weiß Beitragsautor

      Hallo Herr Follmann,

      vielen Dank für den Hinweis und Sie haben natürlich Recht: Die von Ihnen beschriebenen Schritte entsprechen der üblichen Vorgehensweise mit Dropdown-Listen. Ich wollte mit den Steuerelementen lediglich eine weitere, nicht ganz so bekannte Möglichkeit vorstellen. Aber in Excel führen immer viele Wege zum Ziel.

      Schöne Grüße,
      Martin

      • Hans-Peter Follmann

        Hallo Martin,
        das ist wahr. Meine Wege sind meist etwas komplizierter oder umständlicher, weil ich Bekanntes mit neuen Erkenntnissen erweitere. Erst wenn es zu umständlich wird, setze ich mich daran, eine Datei neu zu gestalten. ☺
        Ich warte schon ungeduldig auf den 2. Teil. Da habe ich nämlich eine Datei, die zu dem Thema passt und die ich schon einmal verbessert habe. Ich hoffe, durch Dich wieder neue Erkenntnisse einsetzen zu können.
        In diesem Sinne schon mal vielen Dank im Voraus
        mit freundlichem Gruß
        HP Follmann