Excel 2007: Gültigkeiten (dynamische Dropdown-Listen)

Einfache Gültigkeit

In Excel 2007 finden sich die Gültigkeiten unter:

Daten \ Datentools \ Datenüberprüfung \ Datenüberprüfung

 

Nach Aufruf der Datenüberprüfungen können dann im Reiter "Einstellungen" die Gültigkeitskriterien zugewiesen werden.

 

Am interessantesten ist wahrscheinlich unter "zulassen" der Auswahlpunkt "Liste". Unter "Quelle" werden die Auswahlkriterien, getrennt mit Strichpunkt (;), eingetragen. Damit werden die Inhalte der Auswahllisten oder sog. Drop-down-Listen erzeugt.

 

Damit werden z.B. Auswertungen verbessert, weil bei der Dateneingabe Fehleingaben (z.B. Schreibfehler) vermieden werden.

 

Dynamische/abhängige Gültigkeiten

Eine Besonderheit ist es dann, sog. "dynamische Gültigkeitslisten" zu erzeugen. Dabei werden in nacheinanderfolgenden Spalten jeweils Gültigkeiten vorgegeben, wobei die Anzeige/Auswahl der Gültigkeitskriterien in der zweiten Tabellenspalte über die Auswahl der vorhergehenden Spalte gesteuert wird.

 

In der folgenden Tabelle werden z.B. in der 1.Spalte deutsche Automarken aufgeführt und in der 2. Spalte werden dazu passende Modelle aufgeführt.

 

Spalte 1 - Automarken Spalte 2 - Automodelle
Audi A1
BMW A3
Mercedes A4
Porsche 1er
Volkswagen 3er
  5er
  A-Klasse
  B-Klasse
  C-Klasse
  911
  Boxter
  Cayman
  Cayenne
  Polo
  Golf
  Passat
  Tiguan

In einem Datenblatt sollen in der ersten Spalte die Automarken ausgewählt werden können und in der zweiten Spalte nur die jeweils zur ersten Auswahl passenden Modelle in der Auswahlliste angezeigt werden.

 

Wurde also in der 1. Spalte z.B. "Volkswagen" ausgewählt, sollen in der 2. Spalte über die Drop-Down-Liste nur noch die Modelle Polo, Golf, Passat und Tiguan zur Auswahl stehen.

 

Was ist zu tun?

1. Schritt:

Zunächst werden die Gültig-keitskriterien in einem Tabellenblatt nach dem neben stehenden Schema eingetragen: in der ersten Spalte also die Automarken und in den folgenden Spalten die Automodelle, sortiert nach den Automarken: also: die Audi-Modelle (A1, A2, A3) in einer Spalte, die BMW-Modelle (1er, 3er, 5er) in der nächsten Spalte, etc. 

2. Schritt:

Nun werden den einzelnen Daten- bereichen in den verschiedenen Spalten Namen zugewiesen. Der jeweilige Bereich wird markiert: also z.B. A2:A6. Über den Menü- bereich Formeln \ Definierte Namen\ Namen definieren wird für den Bereich ein Name definiert. Der erste Name ist eigentlich egal, sollte aber "sprechend" sein... in unserem Fall also z.B. "Automarken".

 

Für die folgenden Datenbereiche (B2:B4; C2:C4; D2:D4; E2:E5; F2:F5) werden ebenfalls Namen definiert. Hier ist es aber wichtig, dass für diese abhängigen Bereiche dieselben Namen gewählt werden, wie in dem Bereich A2:A6, also: Audi, BMW, Mercedes etc.

 

Übersicht der definierten Namen im Namens-Manager
Übersicht der definierten Namen im Namens-Manager

 

3. Schritt:

Nun wird in dem Tabellenblatt in dem die Dateneingabe erfolgen soll der Bereich (Zelle(n) oder Spalte) markiert, in dem die Automarken auswählbar sein sollen.

 

Diesem markierten Bereich wird über Daten \ Datentools \ Datenüberprüfung \ Datenüberprüfung \ Einstellungen eine Liste mit der Quelle "=Automarken" zugewiesen. Die Quelle muss natürlich dem entsprechen, was im vorhergehenden Schritt bereits definiert worden war (siehe 2. Schritt: "Automarken" in oranger Farbe).

 

Der Spalte "A" wird die Gültigkeit für die Automarken zugewiesen
Der Spalte "A" wird die Gültigkeit für die Automarken zugewiesen

 

4. Schritt

Nun wird der abhängige Bereich, in dem die Automodelle auswählbar sein sollen (in unserem Fall: Spalte B), markiert und nach der im vorhergehenden Schritt genannten Vorgehensweise eine Liste mit der Quelle "=INDIREKT(A1)" zugewiesen.

 

Der Spalte "B" wird die abhängige Gültigkeit für die Automodelle zugewiesen
Der Spalte "B" wird die abhängige Gültigkeit für die Automodelle zugewiesen

 

Falls Excel bei der Gültigkeitszuweisung zur Spalte "B" eine Fehlermeldung ausgibt, liegt das daran, dass in der vorhergehenden Spalte ("A") kein Wert ausgewählt war, also die Zelle leer war (hier im Beispiel wurde bereits "Volkswagen" ausgewählt - daher würde hier kein Fehler auftreten. Falls dieser Fehler auftritt, kann er einfach ignoriert werden.

 

Ergebnis

Abhängige/dynamische Gültigkeiten
Abhängige/dynamische Gültigkeiten

Abhängig von der Auswahl in der ersten Spalte werden in der zweiten Spalte nur die zugehörigen Kriterien zur Auswahl gestellt.

 

Auf dieselbe Weise können dynamische Gültigkeiten auch über weitere Spalten bereit gestellt werden. So könnten z.B. abhängig von der Modellauswahl in der nächsten Spalte ("C") die möglichen Motorvarianten auswählbar gemacht werden.

 

 

 

 

 

 

Download des Beispiels

Um das Beispiel besser nachvollziehbar zu machen, stelle ich es zum Download bereit. Im Tabellenblatt "Gültigkeiten" ist die Gültigkeitsstruktur zu sehen. Im Datenblatt "Beispiel" können die Daten ausgewählt werden.

 

Im Normalfall würde man das Gültigkeitsblatt ausblenden (rechter Mausklick auf das Register unten und "Ausblenden" wählen. Eingeblendet kann das Blatt werden, in dem auf einem beliebigen Tabellenblatt wiederum mit der rechten Maustaste geklickt wird und "Einblenden" gewählt wird.

 

 

Dynamische Dropdown-Listen
Das Beispiel ist von mir selbst erstellt und darf kostenlos heruntergeladen werden.
Beispiel_DynamischeGültigkeit.xlsx
Microsoft Excel Tabelle 9.6 KB
See my photos on flickr!

Jimdo-Startseite

 

Neues Layout: F4052

 

Bisheriges Layout: F535

 

testhalber (manchmal)
Layout: F533 bzw. "Verona"