Kann ein Ergebnis das in Spalte A gefiltert wird, in einer Zelle über einen "Verweis" angezeigt werden?
Es handelt sich um folgendes Beispiel:
Spalte A2=1, A3=3, A4=2, A5=1, ...
Matrix: 1=A, 2=B, ...
Wenn die Werte in Spalte A gefiltert werden, soll nur der gefilterte Wert "übersetzt" und in einer beliebigen Zelle (z.B. D11) angezeigt werden.
Welche Formel kann hierfür verwendet werden?
2 Antworten
Meines Wissens kannst du nicht nach dem gefilterten Wert suchen, da Excel nur die Zeilen ausblendet, die nicht benötigt werden und die Einzige Möglichkeit bestimmte Zeilen anzugeben ist Bezug+-xy, aber da ich nicht weis welche Zellen jetzt durch den Filter ein oder ausgeblendet sind, kann ich auch nicht wissen welche Zellen jetzt die noch eingeblendeten sind, ich würde daher eher mit der Funktion =Filter arbeiten z.B. so:
Nachtrag: Dies benötigt mindestens die Excel Version 2021
Nachtrag2:
Es gibt doch eine Möglichkeit:
Die Formel die dazu benötigt wird ist Teilergebnis:
=SVERWEIS(TEILERGEBNIS(5;Tabelle1[ID]);F14:G17;2;0)
Tipp, dies zeigt immer nie niedrigste Zahl innerhalb der eingeblendeten Tabelle ein, hast du jedoch keinen Filter drin wird dort auch A angezeigt, um dies zu umschiffen müsstest du noch abgleichen ob die Anzahl der Sichtbaren Zellen der Anzahl der Gesamtzellen entspricht.
Z.B. so:
=WENNS(ANZAHL(Tabelle1[ID])<>TEILERGEBNIS(2;Tabelle1[ID]);SVERWEIS(TEILERGEBNIS(5;Tabelle1[ID]);F14:G17;2;0);1;"kF")
Antwort auf Rückfrage:
Ich hab mich mal hingesetzt und mir eine Lösung für deine Rückfrage überlegt und die Tabelle wie folgt abgeändert:
Zusätzlich wurden die Formeln optimiert, damit diese auch Fehlerhafter Eingabe keinen Fehler mehr ausgeben, zuvor wäre falls man z.B. nach 1275 gesucht hätte als Ergebnis #Wert rausgekommen
Beispeil 1 Filtern nach 3:
Beispiel 2 Filtern nach 321:
Beispiel 3 Filtern nach 123:
Inwiefern die Rückgabewert in Feld Bezeichnung noch Sinnvoll ist weiß ich nicht, maximal der in Spalte I, da ja nun alles angezeigt wird, was die oben gezeigte Zeichenkette enthält egal ob in der Mitte oder am Anfang.
Möchtest du hingegen nur die suchen, die mit der oben geschriebenen Zeichenkette anfangen, müsstest du alternativ anstatt mit suchen(Suchtext;Tabellenspalte)
Entweder mit:
suchen(Suchtext;links(Tabellenspalte;länge(Suchfilter))) oder mit wenn(wennfehler(suchen(Suchtext;Tabellenspalte)=1;0);suchen(Suchtext;Tabellenspalte);0)
ausformuliert also so: (Variante 1)
=WENNFEHLER(FILTER(Tabelle1;ISTZAHL(SUCHEN(E2;LINKS(Tabelle1[ID];LÄNGE(E2)))));"")
oder (Variante 2)
=WENNFEHLER(FILTER(Tabelle1;WENN(WENNFEHLER(SUCHEN(E2;Tabelle1[ID])=1;0);ISTZAHL(SUCHEN(E2;Tabelle1[ID]));0));"")
wie man erkennen kann, fehlt hier gegenüber der dem obigen Beispiel 3 das 4 Ergebnis, da in A8 die Zeichenkette 123 erst ab dem 2 Zeichen beginnt.
Es bleibt wie immer die Frage was ist für dich sinnvoller, ggf. ließe sich auch zwischen beiden Varianten mittels einer Checkbox umschalten je nach dem ob man eben nur am dem 1 Zeichen suchen will oder die gesamte ID egal wo.
Tipp meinerseits, falls du mit der letzten Variante arbeiten willst, würde ich dir empfehlen im Feld E2 ggf. ein abhängiges Listendropdownfeld zu machen, da du so automatisch alles angezeigt bekommst, was es an Möglichkeiten gibt, lohnt sich aber nur, wenn du öfters nach Teilen suchst also nicht immer die vollständige ID, sondern eben 123 oder 21 oder ähnliches.
Ich hoffe dies konnte dir weiterhelfen.
ich schaue mir das mal die Tage in ruhe an, dann gebe ich Rückmeldung
Ich habe es oben ab dem Abschnitt - "Antwort auf Rückfrage:" ergänzt, wäre schön wenn du mir mitteilen könntest, ob dir das weitergeholfen hat.
@Gipfelstuermer Super tausend Dank, die Funktion erfüllt Ihren Zweck zu 100% Großes merci!
Hallo @gipfelstuermer Danke für dein Feedback. Funktioniert wie gewünscht!
Hast du noch eine Idee, wie es umsetzbar wäre, wenn die Zahlenbeispiele so aussehen würden: A2= 1231, A3= 1232, A4= 1233, A8= 1231. Es sollen alle Zahlen die mit 123 beginnen berücksichtigt werden.
Vielen Dank schon vorab für dein Feedback :)
- Gefiltert = Teilergebnis
- Summe = ungefiltert
Vielen Dank für die Rückmeldung. Leider fehlt hier der Verweis? Wie kann ich aus der gefilterten Spalte den entsprechenden "Name" anzeigen?
Hallo @gipfelstuermer Danke für dein Feedback. Funktioniert wie gewünscht!
Hast du noch eine Idee, wie es umsetzbar wäre, wenn die Zahlenbeispiele so aussehen würden: A2= 1231, A3= 1232, A4= 1233, A8= 1231. Es sollen alle Zahlen die mit 123 beginnen berücksichtigt werden.
Vielen Dank schon vorab für dein Feedback :)