Abhängiges Kombinationsfeld

Für diesen Beitrag habe ich mir ein Thema ausgesucht, dass ich schon einmal abgehandelt habe. Allerdings nicht auf https://clevercalcul.wordpress.com/, sondern auf http://www.anleitung24.com/ als Gastautor.

Mein Beitrag dort ist unterschiedlich angekommen, von “einfach schlecht und unverständlich” bis hin zu “Sensationell” und “das Beste was ich finden konnte”.

“Schlecht und unverständlich” möchte ich so nicht stehen lassen, deshalb nehme ich mir das Thema wieder vor und versuche, es besser zu machen, “Gut und verständlich”.

Wenn alte Beiträge wieder verwertet und aufgefrischt werden, wird von Content-Recycling gesprochen. Dies ist mein erster Versuch in dieser Richtung.

Im Beitrag wird es ausschließlich um Kombinationsfelder aus den Formularsteuerelementen gehen. Ein Kombinationsfeld ist eine Kombination aus Listenfeld und Textfeld.

1. Die Ausgangsdaten

Deine Arbeitsmappe sollte mindestens zwei Arbeitsblätter enthalten. Nenne das erste z.B. “Auswertung” und das zweite “Listen”.

Im Blatt “Listen” befinden sich namentlich definierte Bereiche. Du findest sie in der Downloaddatei.

Welt   B6:E11
Afrika   G4:H20
Asien   J4:K33

Mit einem Kombinationsfeld willst du im Allgemeinen einen Wert aus einer Liste auswählen. Die Listen sind nun vorhanden.

2. Das Kombinationsfeld formatieren

Die Formularsteuerelemente findes du im Menüpunkt Entwicklertools unter Einfügen. Wähle dort zunächst ein Kombinationsfeld aus und positioniere es auf deinem Arbeitsblatt “Auswertung”. So sieht es unbehandelt aus:

Um etwas zu verändern, klicke mit der rechten Maustaste auf das Element und wähle “Steuerelement formatieren”.

Verändere zunächst die Größe (Höhe und Breite) auf dir Maße deiner Wahl.

Als nächstes formatierst du die Steuerung. Das Kombinationsfeld soll eine Auswahl aus der Liste “Welt” ermöglichen, der Eingabebereich ist also “Welt”.

Als Zellverknüpfung gibst du D4 ein. Dort erscheint der Listenindex der Auswahl.

Die Liste “Welt” zeigt fünf Erdteile: Asien, Afrika, Amerika, Europa und Ozeanien. Das Kombinationsfeld benötigt daher mindestens fünf Dropdownzellen.

Nach einem Klick auf OK ist das Steuerelement formatiert und sieht so aus, Asien ist gewählt:

3. Das Kombinationsfeld nutzen

Was machst du nun mit dem auf dem Arbeitsblatt positionierten und formatierten Kombinationsfeld?

Die Tabelle Welt im Arbeitsblatt “Listen” enthält neben den Erdteilen auch noch Angaben über die Bevölkerung. [2]

Im Arbeitsblatt “Auswertung” soll in Zelle B7 die Bevölkerung des ausgewählten Erdteils für das Jahr 2016 abgebildet werden.

In B7 schreibst du z.B. diese Formel:

=INDEX(Welt;D4;2)

und erhältst das richtige Ergebnis:

In Zelle C7 schreibst du dann noch “Mio. EW”.

Die Nutzung der Funktion INDEX habe ich ausführlich in zwei Artikeln beschrieben. [1]

4. Ein weiteres, abhängiges Kombinationsfeld einrichten

Du willst nun in dein Arbeitsblatt “Auswertung” ein weiteres Kombinationsfeld aufnehmen. Dieses soll, abhängig vom gewählten Erdteil, jeweils Länder des Erdteils zur Auswahl haben.

Im Beispiel ist Asien gewählt. Im zweiten Kombinationsfeld sollen nun ausgewählte Länder des Erdteils Asien auswählbar sein.

Das ist mit den Formularsteuerelementen nicht ganz so einfach, denn es können im Eingabebereich des Kombinationsfeldes keine Formeln hinterlegt werden. Dort sind Bereiche, wie A1:A15 oder namentliche Bereiche, wie “Welt” einzutragen.

Der Eintrag soll außerdem variabel sein. Deshalb verwendest du einen kleinen Trick. Du schreibst in den Eingabebereich des zweiten Steuerelementes den Namen “Land”.

Dann gehst du ins Menü Formeln / Namen definieren, vergibst den Namen “Land” und schreibst unter “Bezieht sich auf” diese Formel:

=INDIREKT(INDEX(Welt;Auswertung!$D$4;1))

Damit ist der im Eingabebereich des Kombinationsfeldes eigetragene Name fest, aber dennoch variabel, denn die Formel bezieht sich auf die Zellverknüpfung des ersten Kombinationsfeldes.

Steht in D4 die 1, ist Asien gemeint, steht dort die 2, ist Afrika gemeint und steht dort die 4, ist Europa gemeint.

Jetzt steht dort die 1, denn du hast Asien ausgewählt. Somit greift das zweite Kombinationsfeld auf die Listen “Asien” zu und dort hast du z.B. Indonesien ausgesucht.

In F7 schreibst du diese Formel:

=INDEX(Land;$H$4;2)

und zeigst damit die Einwohnerzahl des ausgewählten >Landes an. Wählst du z.B. Iran, werden 79,5 Mio. EW angezeigt.

Liste mit ausgewählten Ländern sind in der Downloaddatei nur für die Erdteile Asien und Afrika angelegt. Lege dir, wenn du Spaß daran hast, Listen für die fehlenden Erdteile an. Daten findest du im Internet. [3]

In B9 und F9 schließlich kannst du noch den prozentualen Anteil ausweisen.

Schreibe dazu in B9 diese Formel:

=B9/$B$9

und in F9 diese Formel:

=F9/$B$9

Formatiere beide Zellen mit dem Zahlenformat Prozent.

5. Zusammenfassung

Richte ein erstes Kombinationsfeld für die Auswahl eines Erdteils in deinem Arbeitsblatt ein. Verfahre dazu gemäß Abschnitt 2.

Richte darunter oder daneben Ergebniszellen für die Einwohnerzahl ein. Die Formel findest du in Abschnitt 3.

Richte ein zweites Kombinationsfeld für die Auswahl eines Landes in Abhängigkeit vom ersten Kombinationsfeld ein. Verfahre dazu gemäß Abschnitt 4.

Richte darunter oder daneben Ergebniszellen für die Einwohnerzahl ein. Die Formel findest du in Abschnitt 4.

So könnte das kleine Modell fertig aussehen:

Leider können die Kombinationsfelder aus den Formularsteuerelementen nicht farblich gestaltet werden. Gestalte deshalb z.B. den Hintergrund andersfarbig, damit sich die Kombinationsfelder etwas abheben.

Hier die Downloaddatei: Abhängige DropDown-Formularsteuerelemente