Excel: Formel Filter Funktion?
Hallo zusammen,
Die Spalte W mit den Produkten, die anhand einer Formel sortiert werden, sollen nun noch mit der Filter Funktion ab Excel 2021ein Filter bekommen.
Kurze Einführung in mein Excel Blatt.
Die Tabelle von Spalte A bis N wird immer aktuell neu eingefügt.
In Spalte BA wird mit der Formel WENN und SVERWEIS alles aus der Spalte E rausgefiltert, was ich nicht brauche.
In der Spalte BB wird die Spalte BA mit der Formel Sortierung sortiert.
In der Spalte BC wird nochmal eine Formel WENN verwendet, für doppelte Einträge.
Die Spalte BD ist meine Hilfsspalte, für die Dropdown-Liste für Spalte V. Vielleicht kann man ja gleich die Dropdown-Liste von Spalte B abgreifen. Somit wird dann die Hilfsspalte nicht mehr benötigt.
Die Spalte W ist meine Hauptspalte, die ich brauche, wo nur noch die Filter Funktion eingebaut werden soll.
Wenn in der Spalte V Dropdown-Liste nichts drinsteht, soll in der Spalte W alles angezeigt werden. (Wie aktuell im Bild) Wenn in der Spalte V Dropdown-Liste ein Eintrag ausgewählt wird, soll nur noch das Produkt gefiltert werden.
Ein Beispiel: Ich wähle aus der Dropdown-Liste den Eintrag CP2001 aus, dann soll nur noch in der Spalte W das Produkt FF101861.02 gefiltert werden.
Oder ich wähle aus der Dropdown-Liste den Eintrag CP2004 aus, dann soll nur noch in der Spalte W die zwei Produkte FG102462.02 und FF101872.02 gefiltert werden.
Wie schon am Anfang beschrieben, wird in Spalte W alles gefiltert, was von Spalte E kommt und ich nicht benötige und in Spalte W soll auch die Formel Filter Funktion aufgebaut werden. (Sofern es auch möglich ist)
Die Bezüge werden von Spalte B und E immer vorgegeben.
Ich hoffe, dass ich alles so weit erklärt habe, um für mein Problem eine Lösung zu finden.
Vielen Dank schon mal im Voraus
Hallo und vielen Dank für die schnelle Antwort,
es tut mir leid, aber ich verstehen die Antwort nicht und bekomme die Formel nicht in meiner eingebunden.
Ich habe in meiner Spalte BA die ganze Formel anzeigen lassen der Zelle, bevor die Produkt-Codes sortiert werden. Ist es vielleicht möglich, schon in der Spalte BA4 die Formel mit einzubinden, bevor alles sortiert wird? Das Ganze soll ja abhängig von der Spalte B & E gemacht werden, wenn in Spalte V2 was ausgewählt wird.
Danke schon mal für die Antwort :-)
Siehe Bild 1
Siehe Bild 2
2 Antworten
Hallo,
falls du dein Problem nicht inzwischen selbst gelöst hast:
Du kannst die Formel in Spalte BA in der Art erweitern um das Dropdownfeld als weiteres Filterkriterium zu verwenden:
- in B4 und runterkopieren: =WENNS($D4="DE04";"X";K4="";"X";ODER($V$2="";INDEX(B:B;VERGLEICH("";$B$1:B4;-1))=$V$2);$E4;1=1;"X")
INDEX(B:B;VERGLEICH("";$B$1:B4;-1))=$V$2 gibt hierbei den letzten nicht leeren Wert in Spalte B bis zur aktuellen Zeile aus. Bei den verbundenen Zellen ist das ja die Zelle in der der tatsächliche Wert steht der dann mit V2 verglichen wird. Findet Wenns() keine Bedingung die WAHR ergibt, wird ein Fehler ausgegeben, diese Fälle werden mit der 1=1 am Ende abgefangen.
Bitte :D
:? hat sich bei einem der CP3005 und CP4001 in Spalte B / dem Dropdown ein unsichtbares Leerzeichen am Ende eingeschlichen oder sonst etwas durch das sie nicht exakt gleich geschrieben sind?
Sonst fällt mir spontan nichts ein, warum sie für diese nicht funktionieren sollten. Falls sie gleich sind könntest du daher einen Screenshot anfügen in dem die Formeln angezeigt werden (Registerkarte Formeln, Abschnitt Formelüberwachung -> Schaltfläche "Formeln anzeigen")?
Hallo, ja, es hat sich ein Leerzeichen bei der Spalte B eingeschlichen :-). Da es beim neu einfügen der Tabelle das Leerzeichen immer mit in Spalte B bei CP3005 und CP4001 eingefügt wird, habe ich für meine Dropdown Liste in Spalte BD bei den zwei Einträgen CP3005 und CP4001 auch ein Leerzeichen hinzugefügt am Ende. Nun funktioniert alles wie es soll. Vielen, vielen, vielen Dank dafür.
Wenn es mit einer Formel nicht anders gelöst werden kann, würde ich es so belassen mit dem Leerzeichen bei meiner Auswahl in Spalte V2. Wiederum würde das mit einer Formel besser gelöst sein. Der Plan wird am Tag mehrmals aktualisiert und neu eingefügt in Excel.
Danke schon mal im voraus für die Antwort.
Ok, bitte^^
Beim Dropdown ganz auf eine Hilfspalte zu verzichten schaffe ich auch nicht, aber zumindest die Hilfsspalte kann man aus Spalte B generieren:
- =WENNFEHLER(INDEX($B$1:$B$23;KKLEINSTE(WENN($B$4:$B$23<>"";WENN($B$4:$B$23<>"Sub total";ZEILE($B$4:$B$23);MAX(ZEILE($B$4:$B$23))+1);MAX(ZEILE($B$4:$B$23))+1);ZEILE(A1)));"")
- Matrixformel, muss in älteren Versionen über Strg+Umschalt+Enter abgeschlossen werden, Excel 2021 scheint von selbst zu verstehen das es eine ist
Dabei wird jedem nicht leeren bzw "Sub total"-Eintrag ihre Zeilennummer zugeordnet, den anderen eine Zahl größer als die letzte Zeile und diese werden nacheinander mittels KKleinste() und Zeile() an Index() übergeben.
Hallo, jetzt ist alles perfekt und es funktioniert wie es soll. Ich kann nur noch schreiben "vielen herzlichen Dank für tolle Unterstützung" ihr seit die besten :-)
Mfg Sveni195
Hallo, ich nochmal. Da es ja wunderbar funktioniert mit der Formel, ist bei mein Arbeitskollegen jetzt die Frage aufgekommen, ob es Möglich ist, eine Mehrfachauswahl mit einzubauen. Z.b. Ich nehme das richtige Steuerelement bei Excel mit der Funktion Mehrfachauswahl und sage Excel nicht wie jetzt nur eine Auswahl CP2001, sondern eine Kombination CP2004 und CP4001 zusammen. Ist das realisierbar?
Danke schon mal im voraus für die Antwort.
Hallo nochmal^^
Generell sollte es möglich sein weitere Suchfelder in derselben Art direkt innerhalb der Oder() hinzuzufügen, bspw. mit V2 & V3 als Auswahlfelder für die Produkte:
- ODER(UND($V$2="";$V$3="");INDEX(B:B;VERGLEICH("";$B$1:B4;-1))=$V$2;INDEX(B:B;VERGLEICH("";$B$1:B4;-1))=$V$3)
Mit den Steuerelementen habe ich mich bisher nicht wirklich auseinandergesetzt, da muss ich morgen selbst nochmal rumprobieren was mit diesen möglich ist.
Wie gestern angekündigt, habe ich mich ein wenig mehr mit den Steuerelementen auseinandergesetzt. Das am nächsten herankommende zur Mehrfachauswahl war das Formularsteuerelement "Listenfeld" bzw "Kombinationsfeld" (funktionieren prinzipiell gleich, nur das das Listenfeld eine Liste und das Kombinationsfeld ein Dropdown ist). Das Listenfeld hat zwar auch eine Option "Mehrfachauswahl", aber ich habe es nicht geschafft diese dann auszulesen :7, aber eine Auswahl nacheinander im Einfachauswahlmodus ist möglich.
Die Steuerelemente geben nebenbei die Position anstatt den Eintrag selbst zurück, aber das ist sogar recht praktisch, wir brauchen nur ein paar Hilfsspalten mehr:
bei mir sind das:
- Spalte U: hier werden die Auswahlen aufgezählt
- Spalte V: in der die Positionen in den Eintrag umgewandelt werden
- $BE$4:$BE$11: zur Aufbereitung der Daten des Steuerelements
- $BD$4:$BD$11 : weiterhin die aus Spalte B generierte Liste
Unter Formatierung des Steuerelements:
- Eingabebereich: $BE$4:$BE$11
- Zellverknüpfung: Listenausgabefeld
"Listenausgabefeld" ist ein definierter Name (Strg+F3 zum Öffnen des Namens- Managers) der diese Formel enthält:
- =BEREICH.VERSCHIEBEN(Tabelle1!$U$2;ANZAHL2(Tabelle1!$U:$U);;)
Damit wird der Bezug U2 zeilenweise um die Anzahl der Einträge in Spalte U verschoben um die nächste freie Zelle zu erreichen.
V2 enthält anstelle des bisherigen Dropdowns einen einfachen Verweis:
- =WENN(U2="";"";INDEX($BD$4:$BD$11;U2))
und BE4 diese Formel, die entsprechend bis BE11 runterkopiert wurde
- =WENN(ANZAHL(U:U)>=8;"keine weitere Auswahl möglich, bitte leeren";WENN(ZÄHLENWENN(V:V;BD4)>0;BD4 & " " & UNIZEICHEN(10004);BD4))
Dabei wird geprüft ob ein Eintrag des Dropdowns bereits ausgewählt wurde und falls ja ein ✔ dahinter gehangen. Die Idee hinter der äußeren Wenn() ist es einen Hinweis zu geben wenn die Anzahl der in Spalte V vorbereiteten Index() erreicht ist. Das Leeren selbst müsste noch manuell gemacht werden, da gibt es keine andere Möglichkeit wenn man nicht VBA bemühen will.
Und in Spalte BA habe ich anstatt die Oder() um jedes Suchfeld zu erweitern es umgedreht, sodass er nun die zugehörigen Einträge aus Spalte B in Spalte V sucht und die Prüfung ob nichts ausgewählt ist über ANZAHL(U:U) erledigt.
- =WENNS($D4="DE04";"X";K4="";"X";ODER(NICHT(ISTFEHLER(VERGLEICH(INDEX(B:B;VERGLEICH("";$B$1:B4;-1));V:V;0)));ANZAHL(U:U)=0);$E4;1=1;"X")
Hallo, ich habe es leider noch nicht geschafft, alles einzubauen. Möchte mich aber in aller Form bei dir recht herzlichen bedanken für die tolle Unterstützung. Einfach spitze von dir. DANKE :-)
Mfg Sven
Wenn ich Dich richtig verstanden habe, dürfte das zu Deinem Ziel führen
Du willst Eintragen anhand von Bedingungen vornehmen (Wenn-Funktion), das hat mit Filtern nichts zu tun.
Du kannst folgendes machen
... und danach nach Spalte W leere Zellen ausblenden
Hallo, ich habe in meine Frage Ergänzung hinzugefügt! MfG Sveni195
Ich würde an Stelle von Deinem Vergleich; =Zählenwenns nutzen
=ZÄHLENWENNS(B:B;B4;D:D;D4;E:E;E4)
=Wenn(ZÄHLENWENNS(B:B;B4;D:D;D4;E:E;E4);<0);E4
Hallo und Danke für die Antwort,
ich glaube nicht, dass es so zu meinem Ziel führt, oder ich verstehe deine Formel nicht. Ich würde es bei Wenn und Vergleich lassen.
Ich schreibe es mal aus der Wenn Sicht: Wenn in Spalte V2 ein Eintrag wie CP2004 ausgewählt wird, soll nur noch in Spalte W der oder die Produkt-Codes aus der Zelle BA8 und BA9 angezeigt werden.
In Spalte B wird CP2004 vorgegeben und in BA8, BA9 steht der Produkt-Code. Wenn ich also in Spalte V2 CP2004 auswähle, soll in der Spalte W nur noch die beiden Produkt-Codes angezeigt werden.
Siehe Bild 1 aus meiner Ergänzung
Wenn in Spalte V2 nichts ausgewählt wird, sollen alle Produkt-Codes angezeigt werden.
Siehe Bild 2 aus meiner Ergänzung
Danke schon mal für die Antwort
Ich kann mich irren, fürchte aber dass der Vergleich mit "Verkettung" nicht funktioniert, wenn die Verkettung nicht zuvor in einer sep. Zelle vorhanden ist und dies als Basis genutzt wird.
Hallo, ich weiß leider nicht mehr so recht, was ich noch schreiben soll. Wie ich schon geschrieben habe, soll anhand meiner Auswahl von Zelle V2 die Produkt-Codes in der Spalte W angezeigt werden oder wenn keine Auswahl getroffen wird, soll alles angezeigt werden. Siehe Bild 1 und 2 in meiner Frage / Ergänzung.
Ich versuche immer wieder verzweifelt in der Spalte BA4 =WENNS($D4="DE04";"X";K4="";"X";E4&B4=E4&V2;$E4;VERGLEICH($E4&$D4;$E:$E&$D:$D;0);$E4) bei meiner eigentlichen WENNS und Vergleich Formel noch eine Wenn Formel mit einzubauen. Da aber immer wieder Fehler auftauchen, "wie" es fehlt eine offene oder geschlossene Klammer oder zu viele Argumente sowie Wert oder es passiert einfach Garnichts. Ich komme einfach nicht auf die richtige Spur, um mein Problem lösen zu können.
Danke schon mal für die Antwort
Hallo, vielen Dank :-) Ich konnte, nachdem ich die Formel kopiert und gesehen hatte, dass es funktioniert, nichts mehr sehen vor Freude. Vielen Danke dafür :-)
Nur noch eine Kleinigkeit: Die Formel funktioniert perfekt aber nur bei der Auswahl von CP2001 bis CP2105. Wenn ich die Auswahl CP3005 oder CP4001 auswähle, steht nichts in meiner Spalte W. Eigentlich sollte ja mir dann die FW001613.08 bei meiner Auswahl CP3005 oder bei meiner Auswahl CP4001 sollte dann nur die FW002237.11 angezeigt werden was aber gerade nicht passiert. Ohne in Spalte V2 eine Auswahl, wird mir alles angezeigt, auch die FW001613.08 und die FW002237.11. Nur mit Auswahl geht das noch nicht.
Vielen Danke schon mal für die Antwort.