So verwenden Sie die Excel-FILTER-Funktion

Office 365 bringt einige großartige Funktionen wie XLOOKUP, SORT und FILTER.

Beim Filtern von Daten in Excel haben wir uns in der Welt vor Office 365 hauptsächlich auf in Excel integrierte Filter oder höchstens auf erweiterte Filter oder komplexe SUMPRODUCT-Formeln verlassen.Dies ist oft eine komplizierte Problemumgehung, wenn Sie einen Teil des Datensatzes filtern müssen.

Aber mit der neuen FILTER-Funktion ist es jetzt wirklich einfach, Teile eines Datensatzes basierend auf Bedingungen schnell zu filtern.

In diesem Tutorial zeige ich Ihnen, wie großartig die neue FILTER-Funktion ist, und einige nützliche Dinge, die Sie damit machen können.

Aber bevor ich auf das Beispiel eingehe, werfen wir einen kurzen Blick auf die Syntax der FILTER-Funktion.

Wenn Sie diese neuen Features in Excel erhalten möchten, können Sie dies tunAktualisieren Sie auf Office 365(Treten Sie dem internen Programm bei, um auf alle Funktionen/Formeln zuzugreifen)

Excel-Filterfunktion – Syntax

Das Folgende ist die Syntax der FILTER-Funktion:

=FILTER(array,include,[if_empty])

• Array - Dies ist der Zellbereich, in dem Sie die Daten haben und einige Daten daraus filtern möchten
• das - Dies ist die Bedingung, die der Funktion mitteilt, welche Datensätze gefiltert werden sollen
• [wenn_leer] – Dies ist ein optionaler Parameter, mit dem Sie angeben können, was zurückgegeben werden soll, wenn die FILTER-Funktion keine Ergebnisse findet.Standardmäßig (wenn nicht angegeben) wird #CALC zurückgegeben!Fehler

Schauen wir uns nun einige erstaunliche Beispiele für Filterfunktionen an und was sie tun können, was früher ohne sie sehr kompliziert war.

Beispiel 1: Daten basierend auf einer Bedingung (Region) filtern

Angenommen, Sie haben ein Dataset wie das unten stehende und möchten nur alle Datensätze in den Vereinigten Staaten filtern.

Hier ist die FILTER-Formel, um dies zu tun:

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

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

Diese Bedingung bewirkt, dass die FILTER-Funktion jede Zelle in Spalte B (Zellen mit dem Bereich) überprüft und nur die Datensätze filtert, die dieser Bedingung entsprechen.

Außerdem habe ich in diesem Beispiel die ursprünglichen und gefilterten Daten auf demselben Blatt abgelegt, aber Sie können sie auch in separate Blätter oder sogar Arbeitsmappen einfügen.

Die Filterfunktion gibt ein dynamisches Array von Ergebnissen zurück (was bedeutet, dass sie statt eines Werts ein Array zurückgibt, das in andere Zellen überläuft).

Dazu benötigen Sie einen Bereich, in dem das Ergebnis leer ist.Es gibt bereits etwas in irgendeiner Zelle des Bereichs (in diesem Beispiel E2:G5) und die Funktion gibt Ihnen den Fehler #SPILL aus.

Da es sich um ein dynamisches Array handelt, können Sie außerdem keinen Teil des Ergebnisses ändern.Sie können den gesamten Bereich mit dem Ergebnis oder der Zelle E2 (wo die Formel eingegeben wird) löschen.Beides löscht das gesamte Ergebnisarray.Sie können jedoch keine einzelne Zelle ändern (oder löschen).

In der obigen Formel habe ich den Bereichswert fest codiert, aber Sie können ihn auch in eine Zelle einfügen und diese Zelle mit dem Bereichswert referenzieren.

Im folgenden Beispiel habe ich beispielsweise den Bereichswert in Zelle I2 und verweise dann in der Formel darauf:

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

Dadurch wird die Formel noch nützlicher, jetzt müssen Sie nur noch den Bereichswert in Zelle I2 ändern und der Filter ändert sich automatisch.

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

Beispiel 2: Daten nach einem Kriterium filtern (größer oder kleiner als)

Sie können Vergleichsoperatoren auch in Filterfunktionen verwenden und alle Datensätze extrahieren, die größer oder kleiner als ein bestimmter Wert sind.

Angenommen, Sie haben das unten gezeigte Dataset und möchten alle Datensätze mit Verkäufen über 10000 filtern.

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, in diesem Fall auf die Bedingung ($C$2:$C$11>10000).

Die Formel überprüft jeden Datensatz auf den Wert in Spalte C.Wenn der Wert größer als 10000 ist, wird er gefiltert, andernfalls wird er ignoriert.

Wenn Sie alle Datensätze kleiner 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 Verkäufen filtern möchten, können Sie die folgende Formel verwenden:

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

Die obige Formel verwendet die LARGE-Funktion, um den drittgrößten Wert im Datensatz zu erhalten.Verwenden Sie dann diesen Wert in einer FILTER-Funktionsbedingung, um alle Datensätze mit Verkäufen zu erhalten, die größer oder gleich dem drittgrößten Wert sind.

Beispiel 3: Filtern von Daten mit mehreren Bedingungen (AND)

Angenommen, Sie haben das folgende Dataset und möchten alle Datensätze in den Vereinigten Staaten mit einem Verkaufswert von mehr als 10000 filtern.

Dies ist eine UND-Bedingung, Sie müssen zwei Dinge überprüfen - die Region muss in den USA liegen und der Umsatz muss über 10000 liegen.Wenn nur eine Bedingung erfüllt ist, sollten die Ergebnisse nicht gefiltert werden.

Hier ist die Filterformel, die Datensätze mit US als Region und mit mehr als 10000 Verkäufen filtert:

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

請注意，標準（稱為包含參數）是 ($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 die beiden Bedingungen zu kombinieren.Dies gibt ein Array aus Nullen und Einsen zurück, wobei 0 nur zurückgegeben wird, wenn beide Bedingungen erfüllt sind.

Wenn es keine übereinstimmenden Datensätze gibt, gibt die Funktion #CALC!Fehler.

Wenn Sie etwas Sinnvolles (keinen Fehler) zurückgeben möchten, können Sie eine Formel wie diese verwenden:

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

Hier habe ich „nicht gefunden“ als dritten Parameter verwendet, der verwendet wird, wenn keine übereinstimmenden Datensätze gefunden werden.

Beispiel 4: Filtern von Daten anhand mehrerer Kriterien (ODER)

Sie können auch den Parameter „enthält“ in der FILTER-Funktion ändern, um nach ODER-Bedingungen zu suchen (wobei jede gegebene Bedingung wahr sein kann).

Angenommen, Sie haben das unten gezeigte Dataset und möchten Datensätze filtern, in denen das Land die Vereinigten Staaten oder Kanada ist.

Hier ist die Formel dazu:

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

Beachten Sie, dass ich in der obigen Formel nur die beiden Bedingungen mit dem Additionsoperator addiere.Da jede dieser Bedingungen ein Array von TRUE und FALSE zurückgibt, kann ich ein kombiniertes Array hinzufügen, das TRUE ist, wenn eine der beiden Bedingungen erfüllt ist.

Ein weiteres Beispiel könnte sein, wenn Sie alle Datensätze filtern möchten, in denen das Land die Vereinigten Staaten ist oder der Verkaufswert über 10000 liegt.

Die folgende Formel wird dies tun:

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

HINWEIS: Verwenden Sie bei Verwendung der UND-Bedingung in der FILTER-Funktion den Multiplikationsoperator (*) und bei Verwendung der ODER-Bedingung den Additionsoperator (+).

Beispiel 5: Daten nach Datensätzen über/unter dem Durchschnitt filtern

Sie können Formeln in der FILTER-Funktion verwenden, um Datensätze mit Werten über oder unter dem Durchschnitt zu filtern und zu extrahieren.

Angenommen, Sie haben das unten gezeigte Dataset und möchten alle Datensätze mit einem Verkaufswert über dem Durchschnitt filtern.

Sie können dies mit der folgenden Formel tun:

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

Auch hier können Sie für unterdurchschnittlich die folgende Formel verwenden:

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

Beispiel 6: Filtern Sie nur gerade Datensätze (oder ungerade Datensätze)

Wenn Sie alle Datensätze in geraden oder ungeraden Zeilen schnell filtern und extrahieren müssen, können Sie dazu die FILTER-Funktion verwenden.

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

Angenommen, Sie haben einen Datensatz wie unten und ich möchte nur gerade Datensätze aus diesem Datensatz extrahieren.

Hier ist die Formel dazu:

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

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

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 2 vom Teil ROW(A11:A1) subtrahiert habe, da sich der erste Datensatz in der zweiten Zeile befindet, wodurch die Zeilennummern so angepasst werden, dass die zweite Zeile als erster Datensatz behandelt wird.

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

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

Beispiel 7: Sortieren von gefilterten Daten mithilfe einer Formel

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

Wenn Sie beispielsweise die FILTER-Funktion verwenden, um einen Datensatz zu filtern, können Sie die SORT-Funktion verwenden, um sortierte Ergebnisse zu erhalten.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle Datensätze mit Verkäufen über 10000 filtern.Mit der Funktion SORTIEREN können Sie bei dieser Funktion dafür sorgen, dass die Ergebnisdaten nach Umsätzen sortiert werden.

Die folgende Formel wird dies tun:

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

Die obige Funktion verwendet die FILTER-Funktion, um die Daten in Spalte C mit Verkäufen über 10000 abzurufen.Verwenden Sie dann das von der FILTER-Funktion zurückgegebene Array in der SORT-Funktion, um die Daten basierend auf Verkäufen zu sortieren.

Der zweite Parameter in der SORT-Funktion ist 3, der nach der dritten Spalte sortieren soll.Der vierte Parameter ist -1, der die Daten in absteigender Reihenfolge sortiert.

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

Ich hoffe, Sie fanden dieses Tutorial nützlich.