Video ansehen - Erweiterter Excel-Filter
Excel Advanced Filter ist eine der am meisten unterschätzten und am wenigsten genutzten Funktionen, die mir begegnet sind.
Wenn Sie mit Excel arbeiten, haben Sie sicher den regulären Excel-Filter verwendet (oder zumindest davon gehört). Es filtert schnell einen Datensatz basierend auf Auswahl, angegebenem Text, Anzahl oder anderen solchen Kriterien.
In diesem Handbuch zeige ich Ihnen einige coole Dinge, die Sie mit dem erweiterten Excel-Filter tun können.
Aber zuerst… Was ist der erweiterte Excel-Filter?
Excel Advanced Filter ist – wie der Name schon sagt – die erweiterte Version des regulären Filters. Sie können dies verwenden, wenn Sie komplexere Kriterien zum Filtern Ihres Datensatzes verwenden müssen.
Hier sind einige Unterschiede zwischen dem regulären Filter und dem erweiterten Filter:
- Während der reguläre Datenfilter den vorhandenen Datensatz filtert, können Sie den erweiterten Excel-Filter verwenden, um den Datensatz auch an einen anderen Speicherort zu extrahieren.
- Mit dem erweiterten Excel-Filter können Sie komplexe Kriterien verwenden. Wenn Sie beispielsweise über Verkaufsdaten verfügen, können Sie Daten nach einem Kriterium filtern, bei dem der Vertriebsmitarbeiter Bob ist und die Region entweder Nord oder Süd ist (wir werden dies in Beispielen sehen). Der Office-Support hat dazu einige gute Erklärungen.
- Sie können den erweiterten Excel-Filter verwenden, um eindeutige Datensätze aus Ihren Daten zu extrahieren (mehr dazu gleich).
EXCEL ERWEITERTER FILTER (Beispiele)
Sehen wir uns nun ein Beispiel zur Verwendung des erweiterten Filters in Excel an.
Beispiel 1 – Extrahieren einer eindeutigen Liste
Sie können den erweiterten Excel-Filter verwenden, um schnell eindeutige Datensätze aus einem Datensatz zu extrahieren (oder mit anderen Worten Duplikate zu entfernen).
In Excel 2007 und höheren Versionen gibt es eine Option zum Entfernen von Duplikaten aus einem Dataset. Aber das ändert Ihren bestehenden Datensatz. Um die Originaldaten intakt zu halten, müssen Sie eine Kopie der Daten erstellen und dann die Option Duplikate entfernen verwenden. Mit dem erweiterten Excel-Filter können Sie einen Standort auswählen, um eine eindeutige Liste zu erhalten.Sehen wir uns an, wie Sie erweiterte Filter verwenden, um eine eindeutige Liste zu erhalten.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Wie Sie sehen, gibt es in diesem Datensatz doppelte Datensätze (orange hervorgehoben). Diese können auf einen Fehler bei der Dateneingabe oder das Ergebnis der Datenzusammenstellung zurückzuführen sein.
In einem solchen Fall können Sie das Excel Advanced Filter-Tool verwenden, um schnell eine Liste aller eindeutigen Datensätze an einem anderen Ort zu erhalten (damit Ihre Originaldaten intakt bleiben).
Hier sind die Schritte, um alle eindeutigen Datensätze zu erhalten:
- Wählen Sie den gesamten Datensatz (einschließlich der Überschriften) aus.
- Gehen Sie zur Registerkarte Daten -> Sortieren und filtern -> Erweitert. (Sie können auch die Tastenkombination verwenden - Alt + A + Q). Dadurch wird das Dialogfeld Erweiterter Filter geöffnet.
- Verwenden Sie im Dialogfeld Erweiterter Filter die folgenden Details:
- Aktion: Wählen Sie die Option „An einen anderen Ort kopieren“. Auf diese Weise können Sie den Speicherort angeben, an dem Sie die Liste der eindeutigen Datensätze abrufen können.
- Listenbereich: Stellen Sie sicher, dass es sich auf das Dataset bezieht, aus dem Sie eindeutige Datensätze finden möchten. Stellen Sie außerdem sicher, dass Header im Datensatz enthalten sind.
- Kriterienbereich: Lassen Sie diese leer.
- Kopieren nach: Geben Sie die Zellenadresse an, unter der Sie die Liste der eindeutigen Datensätze abrufen möchten.
- Nur eindeutige Datensätze kopieren: Aktivieren Sie diese Option.
- OK klicken.
Dadurch erhalten Sie sofort eine Liste aller einzigartigen Datensätze.
Vorsicht: Wenn Sie den erweiterten Filter verwenden, um die eindeutige Liste zu erhalten, stellen Sie sicher, dass Sie auch die Kopfzeile ausgewählt haben. Wenn Sie dies nicht tun, wird die erste Zelle als Kopfzeile betrachtet.
Beispiel 2 – Verwenden von Kriterien im erweiterten Excel-Filter
Das Abrufen einzigartiger Datensätze ist eines der vielen Dinge, die Sie mit dem erweiterten Excel-Filter tun können.
Sein Hauptnutzen liegt in seiner Fähigkeit, komplexe Kriterien zum Filtern von Daten zu ermöglichen.
Hier ist, was ich mit komplexen Kriterien meine. Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten schnell alle Datensätze abrufen, bei denen die Verkäufe über 5000 liegen und die Region die USA ist.
So können Sie den erweiterten Excel-Filter verwenden, um die Datensätze basierend auf den angegebenen Kriterien zu filtern:
- Der erste Schritt bei der Verwendung von Excel Advanced Filter mit komplexen Kriterien besteht darin, die Kriterien anzugeben. Kopieren Sie dazu die Überschriften und fügen Sie sie irgendwo im Arbeitsblatt ein.
- Geben Sie die Kriterien an, nach denen Sie die Daten filtern möchten. Da wir in diesem Beispiel alle Datensätze für die USA mit einem Umsatz von mehr als 5000 abrufen möchten, geben Sie "US" in die Zelle unter "Region" und >5000 in die Zelle unter "Umsatz" ein. Dies würde nun als Eingabe in Advanced Filter verwendet werden, um die gefilterten Daten abzurufen (wie in den nächsten Schritten gezeigt).
- Wählen Sie den gesamten Datensatz (einschließlich der Überschriften) aus.
- Gehen Sie zur Registerkarte Daten -> Sortieren und filtern -> Erweitert. Dadurch wird das Dialogfeld Erweiterter Filter geöffnet.
- Verwenden Sie im Dialogfeld Erweiterter Filter die folgenden Details:
- Aktion: Wählen Sie die Option „An einen anderen Ort kopieren“. Auf diese Weise können Sie den Speicherort angeben, an dem Sie die Liste der eindeutigen Datensätze abrufen können.
- Listenbereich: Stellen Sie sicher, dass es sich auf das Dataset bezieht, aus dem Sie eindeutige Datensätze finden möchten. Stellen Sie außerdem sicher, dass Header im Datensatz enthalten sind.
- Kriterienbereich: Geben Sie die Kriterien an, die wir in den obigen Schritten erstellt haben. In diesem Beispiel wäre es F1:I3.
- Kopieren nach: Geben Sie die Zellenadresse an, unter der Sie die Liste der eindeutigen Datensätze abrufen möchten.
- Nur eindeutige Datensätze kopieren: Aktivieren Sie diese Option.
- OK klicken.
Dies würde Ihnen sofort alle Datensätze anzeigen, in denen die Region die USA ist und die Verkäufe mehr als 5000 betragen.
Das obige Beispiel ist ein Fall, in dem die Filterung nach zwei Kriterien erfolgt (USA und Verkäufe über 5000).
Mit dem erweiterten Excel-Filter können Sie viele verschiedene Kombinationen von Kriterien erstellen.
Hier sind einige Beispiele, wie Sie diese Filter erstellen können.
Verwenden der UND-Kriterien
Wenn Sie UND-Kriterien verwenden möchten, müssen Sie dies unter der Kopfzeile angeben.
Beispielsweise:
- Um Datensätze zu filtern, wenn die Region die USA ist UND der Vertriebsmitarbeiter Joe ist.
- Um Datensätze zu filtern, wenn die Region die USA ist UND der Verkaufswert größer als 5000 ist.
- Wenn die Region die USA ist UND die Umsätze nach dem 31.03.2017 erfasst werden.
Verwenden der ODER-Kriterien
Wenn Sie ODER-Kriterien verwenden möchten, müssen Sie die Kriterien in derselben Spalte angeben.
Beispielsweise:
- Um Datensätze zu filtern, wenn die Region die USA ODER die Region Asien ist.
- Um Datensätze zu filtern, wenn der Vertriebsmitarbeiter Bob ODER Martha ist.
Beispiel 3 - Verwenden von WILDCARD-Zeichen im erweiterten Filter in Excel
Der erweiterte Excel-Filter ermöglicht auch die Verwendung von Platzhalterzeichen beim Erstellen der Kriterien.
In Excel gibt es drei Platzhalterzeichen:
- * (Sternchen) - Es steht für eine beliebige Anzahl von Zeichen. Ex* könnte beispielsweise Excel, Excel, Beispiel, Experte usw. bedeuten.
- ? (Fragezeichen) - Es repräsentiert ein einzelnes Zeichen. Tr?mp könnte zum Beispiel Trump oder Tramp bedeuten.
- ~ (Tilde) - Es wird verwendet, um ein Platzhalterzeichen (~, *, ?) im Text zu identifizieren.
Sehen wir uns nun an, wie wir diese Platzhalterzeichen verwenden können, um eine erweiterte Filterung in Excel durchzuführen.
- Um Datensätze zu filtern, bei denen der Name des Vertriebsmitarbeiters mit J beginnt.
Beachten Sie, dass * eine beliebige Anzahl von Zeichen darstellt. Jeder Repräsentant, dessen Name mit J beginnt, wird also nach diesen Kriterien gefiltert.
Ebenso können Sie auch die anderen beiden Platzhalterzeichen verwenden.
Hinweis: Falls Sie Office 365 verwenden, sollten Sie die FILTER-Funktion ausprobieren. Es kann viele Dinge tun, die der erweiterte Filter mit einer einfachen Formel tun kann.
HINWEIS:
- Denken Sie daran, dass die Überschriften in den Kriterien genau mit denen im Datensatz übereinstimmen sollten.
- Die erweiterte Filterung kann beim Kopieren an andere Speicherorte nicht rückgängig gemacht werden.
