Im heutigen Beispiel zeige ich wie man Daten mit zwei Listenfelder verbinden kann um einen Wert zurückzugeben.

Excel bietet drei Varianten Listenfelder einzusetzen:

1. Formularsteuerelemente
2. ActiveX-Steuerelemente
3. VBA

Im aktuellen Beispiel setze ich die Listenfelder via den ActiveX-Steuerelementen ein. Das Beispiel wäre jedoch genau so gut machbar mit den anderen beiden Varianten der Optionsfelder.

Listenfelder via Steuerelemente

Das Beispiel könnt Ihr hier als Excel Datei herunterladen:

Die Matrix mit den relevanten Informationen sieht so aus:

Wir setzen die relevanten Kriterien die der User auslesen kann fest: Er soll anhand von dem Kanton, der Ortschaft, und des Geschäftsstellen Typ auslesen können.

Die Optionsfelder setzen wir via den AktiveX-Steuerelementen ein. (Wer das Entwicklertools Menü bei sich nicht sieht, muss es zuerst einblenden via den Excel-Optionen >Menübandanpassen>Hauptregisterkarten und bei Entwicklertools ein Hacken setzen.)

Nach dem wir ein leeres Listbox-Feld erstellt haben, wollen wir es mit unseren Daten füllen. Hierfür müssen wir mit einem rechten Mausklick das erstellen Objekte anwählen und auf die Eigenschaften gehen. Im neu geöffneten Menü wählen wir unter “ListFillRange” nun die Daten aus mit denen wir die Listen füllen möchten. In unserem Beispiel sind diese in B21:B26: Bei “LinkedCell” geben wir eine Zelle an in der die Auswahl der Liste wiedergegeben wird: E7

Hier das Listbox-Eigenschaftsmenü aus unserem Beispiel für die Auswahl der Kantone:

Wir machen auch noch zwei kleine Darstellungsänderungen (siehe oben). Die ListBox für Ort und Center wird gleich aufgesetzt.

Verschachtelung der Listenfelder

Mit den INDEX und VERGLEICH Formeln können wir die Werte welche in den Listenfelder ausgewählt werden gezielt abfragen und zusammenführen.

=E9&INDEX($B$20:$D$26;VERGLEICH(E7;$B$20:$B$26;0);VERGLEICH(E8;$B$20:$D$20;0))

Am Anfang der Formel wird auf die Zelle E9 verwiesen. In dieser steht lediglich eine einfache WENN Formel die wir benutzen um ein Text Element beizufügen, so dass der User die Informationen etwas leserlicher präsentiert bekommt.

=WENN(E8=C20;”Die Geschäftsstelle ist in “;”Dies ist ein “)

INDEX und VERGLEICH Formel in Worten erklärt

Mit der INDEX Formel können wir in der Tabelle in B20:D26 eine genaue Position abfragen. Damit die Formel aber weiss welche Position gewünscht ist, benötigt sie sowohl einen Spalten wie auch einen Zeilen Index.

INDEX($B$20:$D$26;Zeilen Index; Spalten Index)

Diese beiden Index erstellen wir mit der VERGLEICH Formel welche direkt auf die “LinkedCell” Zellen verweisen. Die VERGLEICH Formel welche die Zeile prüft verweist auf E7, welche mit der Kantons Listenfelder verknüpft ist, und findet die Stelle wo dieser Werte in der Matrix vorkommt. Die VERGLEICH’s Formel welche die Spalte prüft macht das selbe für das zweite Listenfeld.

Zeile = VERGLEICH(E7;$B$20:$B$26;0)

Spalte = VERGLEICH(E8;$B$20:$D$20;0)

Formatierungen

Durch das Ausblenden der Gitternetzlinien und der Erstellung einer einfachen Form via Einfügen>Formen können wir ein Objekt mit der Zelle E10 verlinken. In dem wir das Objekt direkt über die Zellen mit den Formeln schieben haben wir eine nette Übersicht erstellt.

Das Resultat:

Nun können wir nach belieben die Auswahl ändern und die Daten werden entsprechend angezeigt!