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:
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:
Nach diesen Vorarbeiten taucht die neue Registerkarte in Deinem Menüband auf:
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
Der Einfachheit halber habe ich für diesen Bereich den Namen „Mitarbeiter“ vergeben:
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:
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:
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)
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:
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:
Wenn du nacheinander verschiedene Einträge auswählst, werden dir 2 Dinge auffallen:
- Es wird nicht der Name, sondern die laufende Nummer in E2 eingetragen
- 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:
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;;)
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:
Und schon funktioniert unser Kombinationsfeld in der gewünschten Weise und schreibt jede neue Auswahl in eine neue Zeile:
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.
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.
Hallo Martin,
Du kannst mich (uns) doch immer wieder verblüffen,
Danke
Dieter
Es ist mir jedesmal ein Vergnügen 🙂
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
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
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
Hallo Martin
Ist es auch möglich mit dem Kombinationsfeld auch eine 3-stufige Liste zu erstellen wie bei der Dreistufige Dropdown-Listen? Wenn Ja, wie muss ich vorgehen.
Besten Dank für Deine Rückmeldung.
Gruss
Markus
Hallo Markus,
die Vorgehensweise ist ähnlich wie bei „normalen“ Dropdownfeldern über die Datenprüfung. Du musst mit definierten Namen arbeiten, die abhängig von der Auswahl im vorhergehenden Kombinationsfeld auf einen anderen Bereich verweisen. Der Unterschied ist hier jedoch, dass die Auswahl eines Kombinationsfeldes einen numerischen Wert zurückliefert. Den müsstest Du über eine Zwischentabelle in den gewünschten Namen übersetzen.
Schöne Grüße,
Martin
krass alter
Hallo Martin,
ich helfe manchmal meiner Frau in ihrem Schreibbüro …, lese sonst interessiert deine Tricks …, hier bin ich aber neu.
Wir haben ein Problem, zu dem du mich vielleicht auf eine einfache Lösung stoßen kannst, an Lösung, die ich selbst nicht sehe.
Vorhandenes fremdes, ggf. zu änderndes Dokument liegt vor:
Eine Bewertung mit vielen Kriterien die jeweils differenziert bewertet werden. Realisiert mit jeweils vier einzelnen Steuerelementen „Kontrollkästchen“ in der Zeile (gut/befriedigend/kritisch/unakzeptabel). Kästchen liefern die Werte (W bzw. F) in eine Auswertungsliste usw.. Das manko ist, dass mehrere Häkchen in einer Zeile gesetzt werden können!
Kann man sie doch mit irgendeinem Trick nachträglich koppeln und dann nur ein Kreuzchen pro Zeile zulassen, klar, mit der Erkennung welches das war …
oder müssen die gesamten Blätter auf Steuerelemente „Optionsfeld“ umgebaut werden (was meine einzige Idee war …)?
Vielen Dank
Hallo Ivo,
Kontrollkästchen haben genau die Eigenschaft, dass man mehrere davon ankreuzen kann/darf/soll. Wenn wirklich nur eine Option zulässig sein soll, bleibt meines Erachtens tatsächlich nur der Weg, die Kontrollkästchen durch Optionsfelder zu ersetzen. Eine bessere Lösung kann ich hier leider auch nicht bieten.
Schöne Grüße,
Martin
Sorry, verspätet, war unterwegs, also vielen Dank. Hab ich erwartet, bin schon am Umbauen zu Optionsfeldern, wollte aber eine Expertenmeinung hören, bevor ich beim Ersteller meckere und es dann doch geht auf einem der „Tausend Excel-Pfade“.
Nette Grüße.
Ivo
Hallo Martin,
besteht die Möglichkeit in/mit der Formel =BEREICH.VERSCHIEBEN($E$2;ANZAHL2($E:$E)-1;;)
die möglichen Einträge auf z.B. 20 zu begrenzen.
mit freundlichem Gruß
Eberhard
Hallo Eberhard,
ja, Du musst nur den Bereich in der ANZAHL2-Funktion entsprechend begrenzen, z.B.
=BEREICH.VERSCHIEBEN($E$2;ANZAHL2($E$1:$E$20)-1;;)
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Information
mit freundlichem Gruß
Eberhard
Hallo Martin,
ich habe in einem Tabellenblatt (1) Abwesenheitstage (Urlaub, Gleitzeit, Krankheit etc.) von den Kollegen visualisiert.
Die Abwesenheit gebe ich in Tabellenblatt 2 in eine 4 spaltige Tabelle mit den Spaltenüberschriften Name, Beginn, Ende und Art (Art der Abwesenheit z.B. „U“ für Urlaub, „G“ für Gleitzeit)
Es kann nun vorkommen, dass z.B. Kollege Müller versehentlich eine Abwesenheit für einen Zeitraum plant der sich mit einem bereits von ihm geplanten Zeitraum überschneidet.
In Tabellenblatt 1 wird nur der erste Eintrag berücksichtigt und eventuelle Überschneidungen werden nicht dargestellt.
Meine Vorstellung, zu der ich leider noch keine Lösung habe wäre, dass ich in Tabelle 2 über Bedingte Formatierung angezeigt bekomme wenn es personenbezogen eine Überschneidung für bereits schon geplante Abwesenheitszeiträume gibt.
Da es einige Kollegen und dementsprechend viel Termine/Zeiträume sind möchte ich den Überblick behalten und bei möglichen Überschneidungen den Kollegen zeitnah zu Informieren.
Für Hilfestellung und Lösungsansätze wäre ich dankbar.
mit freundlichem Gruß
Hallo Eberhard,
das ist eine ziemlich kniffelige Aufgabe, für die ich spontan leider auch keine Lösung habe. Und ich vermute, mit einer einfachen Tabelle in dem von Dir beschriebenen Format lässt sich so eine Überschneidung auch nicht ermitteln. Aber vielleicht hat ja ein anderer Leser doch einen Tipp, das würde mich nämlich auch interessieren.
Schöne Grüße,
Martin
Hallo Martin,
ich stehe grade vor dem Problem das ich zwei Kombinationslisten auf einander bezehen möchte und das nicht so recht hinbekommen. In der ersten soll man eine Hauptkategorie auswählen (z.B. Getränke, Lebensmittel, Obst). In der zweiten soll noch genauer augesucht werden, aber immer die dazu gehörigen Name angezeigt werden. (z.B. Getränke ausgewählt nur: Wasser, Cola, Fanta; Lebensmittel ausgewählt, dann nur: Brot, Eier, Käse)
Wenn Sie mir da ein paar Tipps geben könntest wäre das super.
Danke
Hallo Lea,
es geht also um voneinander abhängige Dropdownlisten. Das ist nicht ganz einfach erklärt, aber ich versuche es trotzdem mal. Im Grunde brauchst Du für jedes Element der Hauptkategorie eine Liste mit den dazugehörigen Elementen. Für jede dieser Listen legst du im Namensmanager einen Namen fest, der identisch ist mit dem Namen der Hauptkategorie. In der Datenüberprüfung für das zweite Feld verwendest du als Quelle die INDIREKT-Funktion mit dem Bezug auf das Eingabefeld der Hauptkategorie. Beispiel:
Du hast irgendwo eine Liste mit allen Getränken angelegt und dafür den Namen „Getränke“ vergeben.
In Zelle A2 wird jetzt die Hauptkategorie „Getränke“ ausgewählt.
Für Zelle B2 legst du die Datenüberprüfung an (Zulassen = Liste) und gibst im Feld Quelle die Formel =INDIREKT(A2) ein.
Damit sollte im Dropdown-Feld die Liste aller Getränke angezeigt werden.
Vielleicht hilft das ja weiter. Ansonsten kann ich dir nur mein E-Book empfehlen:
https://www.tabellenexperte.de/der-grosse-dropdown-leitfaden/
Dort sind solche Dinge ziemlich ausführlich beschrieben (und natürlich einiges mehr)
Schöne Grüße,
Martin
Hallo, habe folgende Frage:
Ich habe ein VBA Script aus einem anderen Excel File übernommen und angepasst!
Irgendwie läuft es nicht.
Private Sub Worksheet_Change(ByVal Target As Range)
‚?ber Steuerfeld „Level“ im Worksheet „Profil“ werden je nach Auswahl die jeweiligen Zeilen ausgeblendet,
‚wenn eine Frage innerhalb des Levels mit „0“ gekennzeichnet ist
Application.ScreenUpdating = False
Dim i As Integer, j As Integer, lastRow As Long, thisValue As String
With Worksheets(„Fragen_DE“)
‚Ermitteln der letzten beschriebenen Zeile
lastRow = .Cells(Rows.Count, „K“).End(xlUp).Row
‚Aufrufen der Prozedur, wenn im Steuerfeld „Level“ ein Wert ausgew?hlt wird
If Target.Address = „$L$15“ Then
‚Zur?cksetzen des Worksheets „Fragen_DE“ auf den Urzustand ohne ausgeblendete Zeilen
.Rows(„15:“ & lastRow).Hidden = False
‚Fallunterscheidung des jeweiligen Levels w?hrend der Laufzeit
If Target.Value = „F_All“ Then
For i = 15 To lastRow
‚Ausblenden der aktuellen Zeile, wenn in der Spalte „Level“ eine 0 eingetragen ist
thisValue = .Cells(i, „AA“).Value
If thisValue = „0“ Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ElseIf Target.Value = „F_L1“ Then
For i = 15 To lastRow
thisValue = .Cells(i, „AB“).Value
If thisValue = „0“ Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ElseIf Target.Value = „F_L2“ Then
For i = 15 To lastRow
thisValue = .Cells(i, „AC“).Value
If thisValue = „0“ Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ElseIf Target.Value = „F_L3“ Then
For i = 15 To lastRow
thisValue = .Cells(i, „AD“)
If thisValue = „0“ Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
Else
‚Fehlermeldung, wenn im Datenblatt „Calc-Work“ die Daten ge?ndert werden, mit denen die Dropdown-Liste des Steuerfelds „Level“ gef?llt wird.
‚Falls ?nderungen gemacht werden, m?ssen in dieser Prozedur die Fallunterscheidungen nach „If Target.Value“ angepasst werden.
MsgBox „Daten?nderung der Dropdown-Auswahlliste registriert. Prozedur zur dynamischen Tabellenerstellung muss angepasst werden.“, vbExclamation, „Fehler: Daten?nderung registriert“
End If
End If
End With
Application.ScreenUpdating = True
End Sub
Ich glaube, er greift hier im Steuerelement vorbei?
Ich arbeite mit zwei Tabellen: In der Tabelle „Profile“ soll ein Level ausgelesen werden und in Tabelle „Fragen_DE“ sollen entsprechend dem Level nur die Fragen angezeigt werden, die den Level entsprechen.
Kann ich hier noch einen Tipp bekommen warum es in diesem Sheet nicht funktioniert im alten Sheet doch?
Gruß
Stephan
Hallo Stephan,
einer meiner Grundsätze hier auf dem Blog ist, dass es keine VBA-Tipps von mir gibt (habe ich hier beschrieben).
Daher bitte ich um Verständnis, dass ich dir an dieser Stelle nicht weiterhelfen kann.
Schöne Grüße,
Martin
Hallo Martin,
wirklich eine tolle Lösung für ein altes Problem.
Ich verstehe nicht, wohin ich die Funktion INDEX schreiben soll.
Kannst Du mir da noch einmal behilflich sein?
Herzlichen Dank!
Lars Thomsen
Hallo Lars,
ja, das war vielleicht etwas undeutlich erklärt. Die INDEX-Funktion war für Spalte F bestimmt, also rechts neben der Zahl in der „Dienst“-Spalte. Damit wird zu der ausgewählten Nummer auch der Name des Mitarbeiters angezeigt.
Schöne Grüße,
Martin
Danke Martin für die vielen guten Tips. Ich gebrauche Deine Webseite sehr gerne. Gutes neues Jahr!
Hallo Thomas,
vielen Dank für das Feedback, das freut mich zu hören. Auch ich wünsche Dir ein gutes neues Jahr!
Schöne Grüße,
Martin
Hallo lieber Martin, Deine Beiträge sind mehr als wertvoll! Vielen lieben Dank dafür!!!!!!!!! :o)
Jetzt habe ich ein kleines, bzw. für mich größeres Problem. Ich soll nun für meinem Arbeitgeber ein neues Formular entwickeln, für Beschaffungen des ganzen Unternehmens. Wir haben verschiedene Abteilungen die ich im Kopf per Formularsteuerungselemente gerne zur Auswahl bereitstelle. Weiter unten möchte ich aber, dass sich nur für den ausgewählten Bereich (der oben Ausgewählt wurde), die für das Sachgebiet zugeteilte Kostenstelle angezeigt werden. Geht das überhaupt mit einem Formularsteuerungselement, dass man sich auf verschiedene Auswahlen darunter ein Wenn-Dann einrichtet…..auch im Formularsteuerelement?????
Leider bin ich damit gerade total überfragt…….*pienz*…
Kannst Du mir vielleicht helfen…..das wäre total toll!!!!! LG Anja mit qualmendem KOPF…..ahhhaaaaa
Hallo Anja,
freut mich sehr zu hören, wenn die Blogartikel weiterhelfen. Formulare und vor allem Steuerfelder sind natürlich immer sehr individuell, daher ist es natürlich schwer, eine pauschale Antwort zu geben, denn sehr viel hängt einfach vom Aufbau der Tabellen ab. Beispielsweise: Wo ein „normales“ Dropdownfeld (Datenüberprüfung) ausreicht, würde ich auch keine Formularsteuerelemente verwenden. So oder so, auch die Formularsteuerelemente liefern ja Ergebnisse zurück, die in irgendwelchen Zellen gespeichert werden. Und damit lassen sich natürlich auch abhängig von diesen Ergebnissen an anderer Stelle weitere Werte anzeigen. Hier kommen dann z.B. SVERWEIS, INDEX, VERGLEICH und solche Kandidaten zum Einsatz.
Schöne Grüße,
Martin
Hallo Martin
Ich bin immer wieder begeistert von Deine Tipps, die haben mir schon viel weitergeholfen. Danke!
Aber jetzt habe ich ein Problem bei dem mir bis jetzt niemand weiterhelfen konnte. Daher meine Frage an den besten Excelér:
Ich verwende Excel 365. Seit heuer (1.1.2021) funktionieren die Makros nicht mehr die ich im Menüband angelegt habe und normalerweise von dort aus starte. Diese Makros sind in der PERSONAL.XLSB gespeichert. Ich habe am Pfad oder sonst wo nichts verändert.
Gab es von Microsoft eine Änderung? was ist heuer anders?
Vielen Dank im Voraus und schöne Grüße
Christian
Hallo Christian,
danke für die Lorbeeren, aber an dieser Stelle kann ich dir leider nicht weiterhelfen. Makros werden von mir auf dem Blog nicht behandelt, mir ist allerdings jetzt auch nichts bekannt, dass sich hier in O365 kürzlich etwas geändert hätte. Könnten höchstens irgendwelche Sicherheitseinstellungen sein, aber das ist nur so ins Blaue geraten.
Schöne Grüße,
Martin
Cool!
— und wenn sich im Dienstplan dann was ändert, kannst du aber mit dem Dropdownfeld nur die letzte freie Zelle in Spalte E ändern;
ginge das auch, dass das Eingabeziel die jeweils markierte Zelle ist und ich bei einem Diensttausch einen Mitarbeiter mit einem anderen überschreiben kann?
2) Mitarbeiter statt der laufenden Nummer aus dem Dropdownfeld eintragen: geht das auch ohne zweite Spalte F für die Index-Funktion?
Hallo Karin,
was Du beschreibst, ist im Grunde ein ganz normales Dropdownfeld über eine Gültigkeitsprüfung, die man in Spalte E einrichten würde (Menü Daten | Datenüberprüfung). Das könnte dann auf jeder Zelle in Spalte E liegen und würde direkt den Namen in die Zelle schreiben. Also ganz klassisch, ohne Formularsteuerfeld.
Schöne Grüße,
Martin
Hi, ich fand Deinen Hinweis zu Power Query einfach excellent.
Danke
Hi Cornelia,
dankeschön, manchmal muss man einfach neue Wege beschreiten 🙂
Schöne Grüße,
Martin
Guten Tag Martin, ich habe das Problem das ich bei Excell 2016 welches ich nutze den Entwurfsmodus nicht einschalten kann, welchen Fehler mache ich???? Bin absolut ratlos. Viele Grüße Joe
Hallo Joachim,
der Entwurfsmodus wird nur bei den ActiveX-Steuerelementen angeboten. Für die normalen Formularsteuerelemente greift er nicht. Um ein solches Steuerelement nachträglich wieder zu bearbeiten, kannst du es entweder per Rechtsklick auswählen, oder bei gedrückter Strg-Taste mit der linken Maustaste anklicken.
Schöne Grüße,
Martin