Suche mehrer Werte in einer Zeile, dann Ausgabe eines anderen Wertes

[Maren90]

Hallo zusammen,
ich habe nochmal ein Anliegen und hoffe Ihr könnt mir dabei wieder helfen!


Ich habe:
-Eine Liste mit Biotopen
-Jedem Biotop sind viele Pflanzenarten zugewiesen

Ich möchte:
-nach Biotopen suchen auf denen bestimmte Arten vorkommen
-ein Drop Down Menü benützen (mit hinterlegten Arten)
-Eine oder mehrere Arten damit auswählen können

Ziel:
-Liste mit Biotopen wo Bedingungen erfüllt sind


In der angehängten Datei habe ich versucht gut darzustellen wie diese Auswahl aussehen könnte. Leider übersteigt die Aufgabe meine Skills bei Weitem. Vielleicht kann mir hier jemand helfen.

[Klaus-Dieter]

Hallo,

so ungünstig wie die Liste aufgebaut ist, würde mir da nur eine VBA-Lösung einfallen.

[snb]

Verwende eine dynamische Tabelle

[Maren90]

Leider habe ich am Ende nicht 6 Spalten mit Werten, sondern an die 200. Damit fällt die dynamische Tabelle wohl weg...

[Klaus-Dieter]

Hallo,

wie sieht es mit der VBA-Lösung aus?

[EA1950]

Hallo,

ich habe dir in der beiliegenden Excel-Datei eine Lösung nur mit Arbeitsblattfunktionen (ohne VBA) eingefügt.
Dazu habe ich 3 Hilfsspalten (in Spalte K, Spalte L, Spalte M) im Bereich K2:M11 eingefügt. Sie enthalten jeweils in Spalte K eine Array-Formel.
Ferner habe ich in F25 (für den Spaltenbereich ab Zelle F25) eine weitere Array-Formel eingefügt.

Insbesondere diese letzte Formel in F25 setzt voraus, dass Excel2019 bzw. Excel365 zur Verfügung steht.

Liegen diese Excel-Versionen nicht vor, sind die Formeln in der Spalte K jeweils zeilenweise in den Spalten K, L und M als Array-Funktion einzugeben mit geschweiften Klammern.
Weiters müsste die Array-Formel ab F25 umgeschrieben werden (Ersatz der FILTER()-Funktion).

In rot habe ich die Formelzellen eingefärbt
in blau habe ich die verwendeten Formeln eingefärbt
in grün habe ich die Kommentare eingefärbt.

[Klaus-Dieter]

Hallo,

habe mal eine Beispiellösung mit einem UserForm erstellt. Das kann man noch optimieren, wollte aber nicht zu viel Arbeit reinstecken.

[Fred11]

Hallo Maren90,

anbei ein Lösungsvorschlag mit einer Hilfsspalte.

Mit der Formel
=(ZÄHLENWENN(C2:H2;$D$19)+($D$19=""))*(ZÄHLENWENN(C2:H2;$D$20)+($D$20=""))*(ZÄHLENWENN(C2:H2;$D$21)+($D$21=""))
wird in der Hilfsspalte geprüft, ob die drei in D19:D21 eingetragenen Arten alle in der jeweiligen Zeile vorkommen.
Der Formelteil " …+(D19="")..." bewirkt, dass wenn ein Feld leer ist, dieses dann in der Auswertung ignoriert wird

Mit der Formel
=WENNFEHLER(INDEX($A$2:$A$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)/($B$2:$B$11>0)-1;ZEILE(A1)));"")
werden dann diejenigen Biotope aufgelistet, bei denen in der Hilfsspalte eine Zahl größer 0 steht.

Erklärung dieser Formel siehe hier:
https://thehosblog.com/2013/05/08/excel-...-aggregat/

Ein Dropdown in D19:D21 zur Auswahl der Arten aus der Liste auf dem Tabellenblatt "Liste Arten" kannst du einrichten über
-> Daten -> Datenüberprüfung -> Liste

Da deine Liste der Arten allerdings sehr lang ist, kann es etwas mühsam sein, die gewünschte Art im Dropdown zu suchen.
Das wäre es sinnvoll, deine Liste der Arten alphabetisch zu sortieren; dann wäre das suchen im Dropdown etwas einfacher.

Gruß
Fred

[Maren90]

Hammer! Vielen Dank @Fred!
Danke auch Euch @Anton und @Klaus-Dieter für die guten Tipps! Wow ich muss schon sagen!

[snb]

Wenn man die Daten besser strukturiert reicht eine einfache Pivottable.