Erweiterter Filter und etwas Magie

Für die allermeisten Excel-Anwender kommt beim Stichwort „Datenfilterung“ nur noch der übliche klassische Filter aus dem Reiter Daten – Filtern (Daten — Filter):

Ein solcher Filter ist zweifellos eine vertraute Sache, und für die meisten Fälle wird er ausreichen. Es gibt jedoch Situationen, in denen Sie nach einer großen Anzahl komplexer Bedingungen in mehreren Spalten gleichzeitig filtern müssen. Der übliche Filter hier ist nicht sehr praktisch und ich möchte etwas Stärkeres. Ein solches Werkzeug könnte sein erweiterter Filter, vor allem mit einem kleinen „Finish mit der Feile“ (nach Tradition).

Verpflegung

Fügen Sie zu Beginn ein paar leere Zeilen über Ihrer Datentabelle ein und kopieren Sie die Tabellenüberschrift dorthin – dies wird ein Bereich mit Bedingungen (der Übersichtlichkeit halber gelb hervorgehoben):

Zwischen den gelben Zellen und der Originaltabelle muss mindestens eine Leerzeile stehen.

In den gelben Zellen müssen Sie die Kriterien (Bedingungen) eingeben, nach denen dann gefiltert wird. Wenn Sie beispielsweise Bananen im Moskauer „Auchan“ im III. Quartal auswählen müssen, sehen die Bedingungen so aus:

Wählen Sie zum Filtern eine beliebige Zelle im Bereich mit den Quelldaten aus und öffnen Sie die Registerkarte Daten-Management Und klicke auf das zusätzlich (Daten – Erweitert). In dem sich öffnenden Fenster sollte bereits automatisch ein Bereich mit Daten eingegeben werden und wir müssen nur den Bereich der Bedingungen angeben, dh A1:I2:

Bitte beachten Sie, dass der Bedingungsbereich nicht „mit Rand“ zugeordnet werden kann, dh Sie können keine zusätzlichen leeren gelben Zeilen auswählen, da eine leere Zelle im Bedingungsbereich von Excel als fehlendes Kriterium und als ganz leer wahrgenommen wird Zeile als Aufforderung, alle Daten unterschiedslos anzuzeigen.

Schalter Kopieren Sie das Ergebnis an einen anderen Ort ermöglicht es Ihnen, die Liste nicht direkt auf diesem Blatt zu filtern (wie bei einem normalen Filter), sondern die ausgewählten Zeilen in einen anderen Bereich zu entladen, der dann im Feld angegeben werden muss Ergebnis in Reichweite setzen. In diesem Fall verwenden wir diese Funktion nicht, wir verlassen sie Filterliste vorhanden und klicken auf OK. Die ausgewählten Zeilen werden auf dem Blatt angezeigt:

Hinzufügen eines Makros

"Nun, wo ist hier die Bequemlichkeit?" du fragst und du wirst recht haben. Sie müssen nicht nur Bedingungen in die gelben Zellen mit Ihren Händen eingeben, sondern auch ein Dialogfeld öffnen, dort Bereiche eingeben und drücken OK. Schade, da stimme ich zu! Aber „alles ändert sich, wenn sie kommen ©“ – Makros!

Die Arbeit mit einem erweiterten Filter kann stark beschleunigt und vereinfacht werden, indem ein einfaches Makro verwendet wird, das den erweiterten Filter automatisch ausführt, wenn Bedingungen eingegeben werden, dh wenn eine gelbe Zelle geändert wird. Klicken Sie mit der rechten Maustaste auf die Registerkarte des aktuellen Blatts und wählen Sie den Befehl aus Quellentext (Quellcode). Kopieren Sie in dem sich öffnenden Fenster den folgenden Code und fügen Sie ihn ein:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Dieses Verfahren wird automatisch ausgeführt, wenn eine Zelle im aktuellen Arbeitsblatt geändert wird. Wenn die Adresse der geänderten Zelle in den gelben Bereich (A2:I5) fällt, entfernt dieses Makro alle Filter (falls vorhanden) und wendet den erweiterten Filter erneut auf die Quelldatentabelle an, beginnend mit A7, dh alles wird sofort und sofort gefiltert nach Eingabe der nächsten Bedingung:

Also alles viel besser, oder? 🙂

Komplexe Abfragen implementieren

Jetzt, da alles spontan gefiltert wird, können wir etwas tiefer in die Nuancen gehen und die Mechanismen komplexerer Abfragen im erweiterten Filter zerlegen. Neben der Eingabe exakter Übereinstimmungen können Sie verschiedene Platzhalterzeichen (* und ?) und mathematische Ungleichheitszeichen in einer Reihe von Bedingungen verwenden, um eine ungefähre Suche zu implementieren. Die Groß- und Kleinschreibung spielt keine Rolle. Der Übersichtlichkeit halber habe ich alle möglichen Optionen in einer Tabelle zusammengefasst:

Subtile Punkte:

• Das *-Zeichen bedeutet eine beliebige Anzahl beliebiger Zeichen, und ? – ein beliebiges Zeichen.
• Die Logik bei der Verarbeitung von Text- und numerischen Abfragen ist etwas anders. So bedeutet zum Beispiel eine Bedingungszelle mit der Nummer 5 nicht, dass alle Zahlen gesucht werden, die mit fünf beginnen, aber eine Bedingungszelle mit dem Buchstaben B ist gleich B*, dh sucht nach jedem Text, der mit dem Buchstaben B beginnt.
• Wenn die Textabfrage nicht mit dem =-Zeichen beginnt, dann können Sie gedanklich ein * ans Ende setzen.
• Daten müssen im US-Format Monat-Tag-Jahr und durch einen Bruch eingegeben werden (auch wenn Sie Excel- und Regionaleinstellungen haben).

Logische Verknüpfungen UND-ODER

Bedingungen, die in verschiedenen Zellen, aber in derselben Zeile geschrieben sind, werden als durch einen logischen Operator miteinander verbunden betrachtet И (UND):

Diese. Filterbananen für mich im dritten Quartal, genau in Moskau und gleichzeitig von Auchan.

Wenn Sie Bedingungen mit einem logischen Operator verknüpfen müssen OR (Oder), dann müssen sie nur noch in verschiedenen Zeilen eingetragen werden. Wenn wir beispielsweise alle Bestellungen von Manager Volina für Moskauer Pfirsiche und alle Bestellungen für Zwiebeln im dritten Quartal in Samara finden müssen, dann kann dies in einer Reihe von Bedingungen wie folgt angegeben werden:

Wenn Sie einer Spalte zwei oder mehr Bedingungen auferlegen müssen, können Sie einfach die Spaltenüberschrift im Kriterienbereich duplizieren und die zweite, dritte usw. darunter eingeben. Bedingungen. So können Sie beispielsweise alle Transaktionen von März bis Mai auswählen:

Im Allgemeinen entpuppt sich ein erweiterter Filter nach dem „Fertigstellen mit einer Datei“ als recht ordentliches Werkzeug, an manchen Stellen nicht schlechter als ein klassischer Autofilter.

• Superfilter auf Makros
• Was sind Makros, wo und wie fügt man Makrocode in Visual Basic ein
• Intelligente Tabellen in Microsoft Excel