Mehrere Suchmatrizen bei VERGLEICH

[Xophia]

Hallo zusammen,

mein Problem ist Folgendes:

Ich möchte für die in einem Dropdown ausgewählten Werte angezeigt bekommen, ob tatsächlich Werte aus dem hinterlegten Dropdown gewählt wurden, oder ob eventuell mittels Copy + Paste Fremdwerte eingefügt wurden. Die Anzeige eines Fremdwertes soll in einer separaten Spalte passieren (nicht im Dropdown an sich). Ich komme mit VERGLEICH bei nur einer Matrix (Tabelle) zum gewünschten Ergebnis. Die Schwierigkeit besteht für mich aber darin, dass es sich letztendlich nicht um nur eine zu Grunde liegende Tabelle handelt sondern um wenigstens zwei, die sich nicht direkt nebeneinander befinden.

Eine stark vereinfachte Beispieldatei meines Problems ist im Anhang.

Stark vereinfacht u. a. deshalb, weil es sich um abhängige Dropdowns handelt. Je nachdem, was an anderer Stelle für eine Auswahl getroffen wurde, wird die eine oder die andere Tabelle für das Dropdown angewählt. Ich gehe aber stark davon aus, dass dies für das eigentliche Problem nicht von Bedeutung ist. In meiner Beispieldatei habe ich zwei relevante Tabellen (in Spalten G und J), tatsächlich können es aber noch mehr sein. Ich könnte mir vorstellen, dass die Funktion WAHL irgendwie zur Lösung beitragen könnte, bekomme sie aber nicht entsprechend eingebaut.

Eines wäre noch wichtig:
Es müsste ohne Makros umzusetzen sein.


Habt ihr vielleicht einen Lösungsvorschlag?

Ich danke euch vielmals im Voraus!

Viele Grüße
Xophia

[Lutz Fricke]

Hallo Xophia,

geht natürlich so:

Code:

=WENNFEHLER(VERGLEICH(A5;Hauptfarbe[Hauptfarbe];0);WENNFEHLER(VERGLEICH(A5;Nebenfarbe[Nebenfarbe];0);"Fehler"))

Für jede weitere Spalte ersetzt Du "Fehler" durch WENNFEHLER(VERGLEICH(A5;Nebenfarbe[Nebenfarbe];0);"Fehler")
Das wird aber bei vielen Tabellen sehr schnell sehr unübersichtlich.

Gruß,
Lutz

[Xophia]

Hallo Lutz,

vielen Dank für deinen Lösungsvorschlag. Ich konnte es auf meine ausführliche Datei anwenden und es funktioniert. Also genau gesagt habe ich es erst mal auf 2 Tabellen (/ Matrizen, wie im Beispiel) angewendet. Nun ist es ja so, dass die Zahl der relevanten Tabellen auch höher sein kann und da kommt man genau an den Punkt, den du schon angesprochen hast - es wird unübersichtlich. Und ist es außerdem nicht auch so, dass ich nur bis zu 7 x verschachteln kann? Also maximal überhaupt 7 Tabellen überprüfen lassen kann? Bei mehr als 7 potenziellen Tabellen wäre das in diesem Fall dann leider nicht hilfreich.

Gibt es vielleicht noch eine Lösung, die mehr mit meinem Gedanken mit WAHL funktioniert? Ich stelle mir (durch meine rosarote Ahnungslosen-Brille) vor, dass es damit egal wäre, wie viele Tabellen (Matrizen) geprüft werden, weil ja keine unübersichtliche Verschachtelung erfolgt, sondern man es "kleiner" halten kann und mithilfe von Zahlen angibt.

Viele Grüße
Xophia

[echo]

Hallo zusammen
du könntest jede Suche in einer Hilfsspalte einzeln durchführen und die Ergebnisse mit =textverketten(Bereich) ausgeben.
oder
jede einzelne Suche direkt in Textverketten einfügen
=TEXTVERKETTEN(";";;WENNFEHLER(VERGLEICH(A5;Hauptfarbe[Hauptfarbe];"");"");WENNFEHLER(VERGLEICH(A5;Nebenfarbe[Nebenfarbe];0);"")))

Gruß Holger

[Lutz Fricke]

Hallo Xophia,

probier mal:

Code:

=WENNFEHLER(AGGREGAT(15;6;SPALTE(G:J)/(G:J=Tabelle6[@Farbe]);1);"Fehler")

Die Formel zeigt die erste Spalte an, in der der Begriff auftaucht (auch bei mehreren Nennungen).

Brauchst Du auch noch die Position in der Spalte geht's so:

Code:

=VERGLEICH(Tabelle6[@Farbe];INDIREKT(WECHSELN(ADRESSE(1;AGGREGAT(15;6;SPALTE(G:J)/(G:J=Tabelle6[@Farbe]);1);4);"1";"")&":"&WECHSELN(ADRESSE(1;AGGREGAT(15;6;SPALTE(G:J)/(G:J=Tabelle6[@Farbe]);1);4);"1";""));0)

Wobei das wahrscheinlich noch einfacher geht, nur ich zu blöd bin....

Gruß,
Lutz

[Fred11]

Hallo Xophia,

Nun ist es ja so, dass die Zahl der relevanten Tabellen auch höher sein kann

Wieviele Tabellen sind denn hier zu erwarten ?
Und woher soll Excel wissen, welche relevant sind ?
Willst du die immer manuell angeben ?

Also mit WAHL funktioniert das was du möchtest, denke ich, nicht .
(zumindest waren meine Experimente mit WAHL nicht erfolgreich)

Eine Möglichkeit wäre stattdessen, die einzelnen relevanten Matrizen (Spalten) mit & zu verketten und das Suchkriterium der Funktion VERGLEICH mit "*" zu umrahmen,

Etwa so in der Art:
VERGLEICH("*"&[@Farbe]&"*";Hauptfarbe[Hauptfarbe]&Nebenfarbe[Nebenfarbe];0)

Formel in B2:
=WENN([@Farbe]<>"";WENN(ISTFEHLER(VERGLEICH("*"&[@Farbe]&"*";Hauptfarbe[Hauptfarbe]&Nebenfarbe[Nebenfarbe];0));"Fehler";"ok");"")
Matrixformel =>bei älterem Excel Eingabe mit Strg+Shift+Enter

Oder du nutzt die Funktionalitäten deines Excel 365:

Formel in B2:
=WENN(A2<>"";SUMMENPRODUKT(--(INDEX($G$2:$J$11;SEQUENZ(ZEILEN($G$2:$G$11));{1.4})=A2))>0;"")
Da kannst du dann (wie bei deinem WAHL) die Matrix dynamisch zusammenbauen und die relevanten Spalten "mithilfe von Zahlen" angeben.

Siehe angehängte Beispieldatei

Gruß
Fred

[Xophia]

Hallo zusammen und danke für eure Lösungsvorschläge.

echo -
Es schaut mir vom Prinzip her ähnlich dem Vorschlag von Lutz aus, sodass es am Ende, angenommen es ginge um 7 oder 8 Tabellen (Matrizen), auf die geschaut werden soll, sehr unübersichtlich wäre. Hier auch noch mal die Frage, ob es, egal ob verschachtelte WENN-Funktion oder Verkettung, nicht eh nur max. 7x möglich ist?

Lutz -
Ich habe deine beiden neuen Formeln ausprobiert und als Ergebnis kommt immer "0". Ich verstehe es aber so, dass zumindest in den Fällen der gültigen Eingaben (Farben) eine Zahl größer 0 als Ergebnis kommen müsste. Abgesehen davon, gibst du als Matrixbereich, wenn ich das richtig verstehe, "G:J" an. Das ist ja genau das, was ich ausschließen wollte, was ich versucht habe mit dem Aufbau in der Datei zu verdeutlichen, indem ich die beiden relevanten Tabellen nicht nebeneinander gepackt habe sondern mit anderen Tabellen dazwischen. Es sollen ja genau nicht die Werte aus den Tabellen dazwischen (H und I) als möglicherweise richtig angezeigt werden, daher auch meine Frage im vorigen Post nach "mithilfe von Zahlen" (sorry, kann es nicht besser ausdrücken).

Im Grunde hat es Fred mit seiner Formel so getroffen, wie ich es mir ungefähr vorgestellt habe.

Fred -
Deine SEQUENZ-Formel und die "in Zahlen angegebene Matrix" lässt sich gut auf mein Beispiel anwenden und ich habe es als Alternative auch außerdem probiert mit "1.3.4" (statt wie in deiner Formel "1.4") und es hat beides funktioniert, wie es soll! Ich gehe davon aus, da ja meine 3-Spalten-Version deiner Formel funktioniert hat, dass ich das also mit theoretisch beliebig vielen Spalten, die sich nicht nebeneinander befinden, umsetzen kann. Das habe ich in meiner "großen" Datei noch nicht ausprobiert, werde es aber natürlich tun. Aber in der kleinen Datei war es ja schon mal prima! Vielen Dank dafür!

Auch wenn das jetzt vielleicht nicht mehr notwendig ist, hattest du nach weiteren Infos zur Datei gefragt. Ich habe die Beispieldatei noch mal etwas ergänzt, sodass es von der Funktionalität, was die Auswahl der Tabelle anbelangt, etwas deutlicher wird. Die jeweilige Tabelle wird mithilfe von INDIREKT angesteuert, das ist in der neuen Spalte A ersichtlich.

Viele Grüße
Xophia

[echo]

echo -
Es schaut mir vom Prinzip her ähnlich dem Vorschlag von Lutz aus, sodass es am Ende, angenommen es ginge um 7 oder 8 Tabellen (Matrizen), auf die geschaut werden soll, sehr unübersichtlich wäre. Hier auch noch mal die Frage, ob es, egal ob verschachtelte WENN-Funktion oder Verkettung, nicht eh nur max. 7x möglich ist?

Hallo Xophia
Du hast recht, die Übersicht leidet, auch wenn jeder Bereich sich in einer eigenen Zeile im Formeleditor wieder findet
Ich hätte vermutliche alle die verschiedenen Bereiche nicht direkt in Textverketten eingefügt sondern Hilfsspalten bemüht und dann den Bereich verkettet.
Hast ja auch schon weitere alternativen bekommen.

Was deine Frage angeht, zitiere ich mal aus der Excel-Hilfe für Textverketten:

Für die Textelemente sind maximal 252 Textargumente zulässig, einschließlich Text1. 
Dabei kann es sich jeweils um eine Textzeichenfolge oder ein Array von Zeichenfolgen (z. B. um einen Zellbereich) handeln.

Gruß Holger

[Xophia]

Fred, mir ist eben noch eine Sache aus deiner ISTFEHLER-Formel (in deiner Datei in Spalte C) aufgefallen:
Die funktioniert prima, solange der ausgewählte Wert in der Tabelle in einer (Tabellen-)Zeile steht, die es in einer anderen relevanten Tabelle auch gibt, also bspw. "Rot", weil "Rot" in der 4. (Tabellen-)Zeile steht und es in der anderen Tabelle eine 4. Zeile gibt. Wähle ich jetzt aber bspw. "Silber", was in der 8. (Tabellen-)Zeile steht, dann funktioniert es nicht - es wird ein Fehler angezeigt, obwohl es ja eigentlich ein gültiger Wert ist. Ich nehme an, es liegt daran, dass es keinen "Vergleichswert" aus der anderen Tabelle gibt, also halt keinen Wert in der 8. (Tabellen-)Zeile. Da zeigt die Formelauswertung #NV an, genau genommen generell ab (Tabellen-)Zeile 7, weil die eine relevante Tabelle nur über 6 Zeilen verfügt.

Ließe sich das wohl noch irgendwie umgehen?

[Fred11]

Hallo Xophia,

...Ich nehme an, es liegt daran, dass es keinen "Vergleichswert" aus der anderen Tabelle gibt, also halt keinen Wert in der 8. (Tabellen-)Zeile….

So ist es.

Das Prinzip mit dem Verketten funktioniert nur bedingt, wenn die zu verkettenden Bereiche (in deinem Fall einzelne intelligente Tabellen) unterschiedlich groß sind.

Umgehen läßt sich das, indem in der Formel nicht die strukturierten Verweise ("...Hauptfarbe[Hauptfarbe]…") verwendet werden, sondern normale Zellbezüge und hier dann die Bereiche jeweils gleich groß (und groß genug…) festgelegt werden.

Also z.B. so (Formel in C2):
=WENN([@Farbe]<>"";WENN(ISTFEHLER(VERGLEICH("*"&@Farbe]&"*";$H$1:$H$19&$K$1:$K$19;0));"Fehler";"ok");"")

oder so:
=WENN([@Farbe]<>"";WENN(ISTFEHLER(VERGLEICH("*"&@Farbe]&"*";INDEX($H$1:$K$18;0;1)&INDEX($H$1:$K$18;0;4);0));"Fehler";"ok");"")

Siehe angehängte Beispieldatei

Gruß
Fred