Excel-Filterfunktion - Erklärt mit Beispielen + Video

Video ansehen - Beispiele für Excel-Filterfunktionen

Office 365 bringt einige tolle Funktionen mit – wie XLOOKUP, SORT und FILTER.

Wenn es um das Filtern von Daten in Excel geht, waren wir in der Welt vor Office 365 hauptsächlich auf den in Excel integrierten Filter oder maximal auf den erweiterten Filter oder komplexe SUMPRODUCT-Formeln angewiesen. Falls Sie einen Teil eines Datensatzes filtern mussten, war dies normalerweise eine komplexe Problemumgehung (etwas, das ich hier behandelt habe).

Aber mit der neuen FILTER-Funktion ist es jetzt ganz einfach, einen Teil des Datensatzes basierend auf einer Bedingung schnell zu filtern.

Und in diesem Tutorial zeige ich Ihnen, wie toll die neue FILTER-Funktion ist und einige nützliche Dinge, die Sie damit machen können.

Aber bevor ich zu den Beispielen komme, lernen wir kurz die Syntax der FILTER-Funktion kennen.

Wenn Sie diese neuen Funktionen in Excel erhalten möchten, können Sie Upgrade auf Office 365 (Treten Sie dem Insider-Programm bei, um Zugang zu allen Funktionen/Formeln zu erhalten)

Excel-Filterfunktion - Syntax

Unten ist die Syntax der FILTER-Funktion:

=FILTER(array,einschließen,[if_leer])

• Array - Dies ist der Zellenbereich, in dem Sie die Daten haben und einige Daten daraus filtern möchten
• enthalten - Dies ist die Bedingung, die der Funktion mitteilt, welche Datensätze zu filtern
• [if_leer] - Dies ist ein optionales Argument, mit dem Sie angeben können, was zurückgegeben werden soll, falls von der FILTER-Funktion keine Ergebnisse gefunden werden. Standardmäßig (wenn nicht angegeben) gibt es das #CALC! Error

Werfen wir nun einen Blick auf einige erstaunliche Beispiele für Filterfunktionen und Dinge, die sie tun kann, was früher ziemlich komplex war, wenn sie nicht vorhanden waren.

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Beispiel 1: Filtern von Daten basierend auf einem Kriterium (Region)

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten alle Datensätze nur für die USA filtern.

Unten ist die FILTER-Formel, die dies tut:

=FILTER($A$2:$C$11,$B$2:$B$11="US")

Die obige Formel verwendet den Datensatz als Array und die Bedingung ist $B$2:$B$11=”US”

Diese Bedingung würde dazu führen, dass die FILTER-Funktion jede Zelle in Spalte B (eine mit der Region) überprüft und nur die Datensätze gefiltert werden, die diesem Kriterium entsprechen.

Auch in diesem Beispiel habe ich die Originaldaten und die gefilterten Daten auf demselben Blatt, aber Sie können diese auch in separaten Blättern oder sogar Arbeitsmappen haben.

Die Filterfunktion gibt ein Ergebnis zurück, bei dem es sich um ein dynamisches Array handelt (was bedeutet, dass anstelle eines Werts ein Array zurückgegeben wird, das auf andere Zellen übertragen wird).

Damit dies funktioniert, müssen Sie einen Bereich haben, in dem das Ergebnis leer wäre. In einer der Zellen in diesem Bereich (E2:G5 in diesem Beispiel) ist bereits etwas enthalten, die Funktion gibt Ihnen den #SPILL-Fehler aus.

Da es sich um ein dynamisches Array handelt, können Sie außerdem keinen Teil des Ergebnisses ändern. Sie können entweder den gesamten Bereich löschen, der das Ergebnis enthält, oder die Zelle E2 (in der die Formel eingegeben wurde). Beides würde das gesamte resultierende Array löschen. Sie können jedoch keine einzelne Zelle ändern (oder löschen).

In der obigen Formel habe ich den Regionswert hartcodiert, aber Sie können ihn auch in einer Zelle haben und dann auf die Zelle mit dem Regionswert verweisen.

Im folgenden Beispiel habe ich beispielsweise den Regionswert in Zelle I2 und dieser wird dann in der Formel referenziert:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Dies macht die Formel noch nützlicher und jetzt können Sie einfach den Regionswert in Zelle I2 ändern und der Filter würde sich automatisch ändern.

Sie können auch ein Dropdown-Menü in Zelle I2 haben, in dem Sie einfach die Auswahl treffen und die gefilterten Daten sofort aktualisieren.

Beispiel 2: Filtern von Daten basierend auf einem Kriterium (mehr als oder weniger als)

Sie können auch Vergleichsoperatoren innerhalb der Filterfunktion verwenden und alle Datensätze extrahieren, die über oder unter einem bestimmten Wert liegen.

Angenommen, Sie verfügen über das unten gezeigte Dataset und möchten alle Datensätze filtern, deren Verkaufswert mehr als 10000 beträgt.

Die folgende Formel kann dies tun:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Das Array-Argument bezieht sich auf den gesamten Datensatz und die Bedingung ist in diesem Fall ($C$2:$C$11>10000).

Die Formel überprüft jeden Datensatz auf den Wert in Spalte C. Wenn der Wert mehr als 10000 beträgt, wird er gefiltert, andernfalls wird er ignoriert.

Falls Sie alle Datensätze mit weniger als 10000 erhalten möchten, können Sie die folgende Formel verwenden:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

Mit der FILTER-Formel können Sie auch kreativer werden. Wenn Sie beispielsweise die ersten drei Datensätze basierend auf dem Verkaufswert filtern möchten, können Sie die folgende Formel verwenden:

=FILTER($A$2:$C$11,($C$2:$C$11>=GROSS(C2:C11,3)))

Die obige Formel verwendet die LARGE-Funktion, um den drittgrößten Wert im Dataset zu erhalten. Dieser Wert wird dann in den Kriterien der Funktion FILTER verwendet, um alle Datensätze abzurufen, bei denen der Verkaufswert größer oder gleich dem drittgrößten Wert ist.

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Beispiel 3: Filtern von Daten mit mehreren Kriterien (UND)

Angenommen, Sie haben das folgende Dataset und möchten alle Datensätze für die USA filtern, in denen der Verkaufswert mehr als 10000 beträgt.

Dies ist eine UND-Bedingung, bei der Sie zwei Dinge überprüfen müssen - die Region muss die USA sein und der Umsatz muss mehr als 10000 betragen. Wenn nur eine Bedingung erfüllt ist, sollten die Ergebnisse nicht gefiltert werden.

Unten ist die FILTER-Formel, die Datensätze mit den USA als Region und Verkäufen von mehr als 10000 filtert:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

Beachten Sie, dass das Kriterium (das Include-Argument genannt wird) ist ($B$2:$B$11=”US”)*($C$2:$C$11>10000).

Da ich zwei Bedingungen verwende und beide wahr sein müssen, habe ich den Multiplikationsoperator verwendet, um diese beiden Kriterien zu kombinieren. Dies gibt ein Array von 0 und 1 zurück, wobei eine 1 nur zurückgegeben wird, wenn beide Bedingungen erfüllt sind.

Falls keine Datensätze vorhanden sind, die die Kriterien erfüllen, würde die Funktion das #CALC! Error.

Und falls Sie etwas Bedeutungsvolles (anstelle des Fehlers) zurückgeben möchten, können Sie eine Formel wie unten gezeigt verwenden:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nichts gefunden")

Hier habe ich „Not Found“ als drittes Argument verwendet, das verwendet wird, wenn keine Datensätze gefunden werden, die den Kriterien entsprechen.

Beispiel 4: Filtern von Daten mit mehreren Kriterien (ODER)

Sie können auch das Argument „include“ in der Funktion FILTER ändern, um nach einem ODER-Kriterium zu suchen (wobei eine der gegebenen Bedingungen wahr sein kann).

Angenommen, Sie verfügen über das unten gezeigte Dataset und möchten die Datensätze filtern, in denen das Land entweder die USA oder Kanada ist.

Unten ist die Formel, die dies tut:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Kanada"))

Beachten Sie, dass ich in der obigen Formel einfach die beiden Bedingungen mit dem Additionsoperator hinzugefügt habe. Da jede dieser Bedingungen ein Array von TRUEs und FALSE zurückgibt, kann ich hinzufügen, um ein kombiniertes Array zu erhalten, bei dem es TRUE ist, wenn eine der Bedingungen erfüllt ist.

Ein weiteres Beispiel könnte sein, wenn Sie alle Datensätze filtern möchten, bei denen entweder das Land die USA ist oder der Verkaufswert mehr als 10000 beträgt.

Die folgende Formel wird dies tun:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

Hinweis: Verwenden Sie bei Verwendung von UND-Kriterien in einer FILTER-Funktion den Multiplikationsoperator (*) und verwenden Sie bei Verwendung der ODER-Kriterien den Additionsoperator (+).

Beispiel 5: Filtern von Daten, um über/unter dem Durchschnitt zu liegen

Sie können Formeln innerhalb der FILTER-Funktion verwenden, um Datensätze zu filtern und zu extrahieren, bei denen der Wert über oder unter dem Durchschnitt liegt.

Angenommen, Sie verfügen über das unten gezeigte Dataset und möchten alle Datensätze filtern, bei denen der Verkaufswert über dem Durchschnitt liegt.

Sie können dies mit der folgenden Formel tun:

=FILTER($A$2:$C$11,C2:C11>DURCHSCHNITT(C2:C11))

Ebenso können Sie für einen unterdurchschnittlichen Durchschnitt die folgende Formel verwenden:

=FILTER($A$2:$C$11,C2:C11<>

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Beispiel 6: Filtern nur der GERADE-Nummern-Datensätze (oder der ungeraden Nummern-Datensätze)

Falls Sie schnell alle Datensätze aus geraden oder ungeraden Zeilen filtern und extrahieren müssen, können Sie dies mit der FILTER-Funktion tun.

Dazu müssen Sie die Zeilennummer innerhalb der FILTER-Funktion überprüfen und nur Zeilennummern filtern, die den Zeilennummernkriterien entsprechen.

Angenommen, Sie haben den unten gezeigten Datensatz und ich möchte nur Datensätze mit geraden Nummern aus diesem Datensatz extrahieren.

Unten ist die Formel, die dies tut:

=FILTER($A$2:$C$11,MOD(ZEILE(A2:A11)-1,2)=0)

Die obige Formel verwendet die MOD-Funktion, um die Zeilennummer jedes Datensatzes zu überprüfen (die von der ROW-Funktion angegeben wird).

Die Formel MOD(ROW(A2:A11)-1,2)=0 gibt TRUE zurück, wenn die Zeilennummer gerade ist und FALSE, wenn sie ungerade ist. Beachten Sie, dass ich 1 vom ROW(A2:A11)-Teil abgezogen habe, da sich der erste Datensatz in der zweiten Zeile befindet, und dies passt die Zeilennummer an, um die zweite Zeile als den ersten Datensatz zu betrachten.

Ebenso können Sie alle ungeradzahligen Datensätze mit der folgenden Formel filtern:

=FILTER($A$2:$C$11,MOD(ZEILE(A2:A11)-1,2)=1)

Beispiel 7: Sortieren der gefilterten Daten mit Formel

Die Verwendung der FILTER-Funktion mit anderen Funktionen ermöglicht es uns, viel mehr zu erledigen.

Wenn Sie beispielsweise einen Datensatz mit der Funktion FILTER filtern, können Sie mit der Funktion SORT das bereits sortierte Ergebnis abrufen.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle Datensätze filtern, deren Verkaufswert mehr als 10000 beträgt. Sie können die SORT-Funktion mit der Funktion verwenden, um sicherzustellen, dass die resultierenden Daten basierend auf dem Verkaufswert sortiert sind.

Die folgende Formel wird dies tun:

=SORTIEREN(FILTER($A$2:$C$11,($C$2:$C$11>10000),3,-1)

Die obige Funktion verwendet die FILTER-Funktion, um die Daten abzurufen, bei denen der Verkaufswert in Spalte C mehr als 10000 beträgt. Dieses von der FILTER-Funktion zurückgegebene Array wird dann innerhalb der SORT-Funktion verwendet, um diese Daten basierend auf dem Verkaufswert zu sortieren.

Das zweite Argument in der SORT-Funktion ist 3, das nach der dritten Spalte sortiert werden soll. Und das vierte Argument ist -1, um diese Daten in absteigender Reihenfolge zu sortieren.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Dies sind also 7 Beispiele für die Verwendung der FILTER-Funktion in Excel.

Ich hoffe, Sie fanden dieses Tutorial nützlich!

Vielleicht gefallen Ihnen auch die folgenden Excel-Tutorials:

1. So filtern Sie Zellen mit fetter Schriftformatierung in Excel
2. Dynamisches Excel-Filter-Suchfeld
3. So filtern Sie Daten in einer Pivot-Tabelle in Excel