Die FILTER-Funktion in Microsoft Excel ist eine der wichtigsten Funktionen, die es zu beherrschen gilt. Ohne sie wird es Ihnen schwer fallen, die benötigten Daten zu finden. Hier ist ein Crashkurs zur Verwendung von FILTER in Excel.
Es ist auch nützlich zu beachten, dass die Funktion nicht die einzige Möglichkeit ist, Daten in MS Excel zu filtern. Sie verfügen über Tools wie den automatischen Filter und den erweiterten Filter, um dasselbe zu erreichen, mit einigen wichtigen Einschränkungen, die wir in diesem Leitfaden besprechen werden.
Was ist die FILTER-Funktion?
Funktionen oder Excel-Formeln sind das A und O von Excel und ermöglichen es Ihnen, beispielsweise den Durchschnitt eines großen Datensatzes oder Erstellen eines Glockenkurvendiagramms zu ermitteln. Jede Funktion hat ihre eigene Syntax, die Sie normalerweise überprüfen können, indem Sie einfach den Funktionsnamen in Excel eingeben.
Die Excel-Funktion FILTER wird, wie der Name schon sagt, verwendet, um die Werte eines angegebenen Bereichs nach bestimmten Bedingungen zu „filtern“. Sowohl der Bereich als auch die zu verwendenden Bedingungen werden mit der Funktion eingegeben, wodurch sie äußerst anpassbar ist.
Mit den richtigen Parametern können Sie genau die Informationen, die Sie benötigen, aus einer Tabelle extrahieren, ohne den gesamten Vorgang manuell durchgehen zu müssen, um passende Einträge zu finden. Und da die Ausgabe in einer Zelle enthalten ist, können Sie weitere Funktionen verketten, um Berechnungen durchzuführen oder die Ergebnisse in einem Diagramm anzuzeigen.
Warum wird die FILTER-Funktion gegenüber der bevorzugt? Erweiterter Filter?
Die meisten Excel-Anfänger bleiben bei den integrierten Datenfiltertools in Excel, anstatt zu versuchen, die Syntax einer Funktion zu lernen. Der automatische Filter ist am einfachsten und ermöglicht es Ihnen, Spalten auszuschließen und Filterkriterien über eine menübasierte Oberfläche festzulegen. Dann gibt es noch den erweiterten Filter mit seiner Fähigkeit, mehrere Kriterien zur Implementierung komplexer Filterschemata anzuwenden.
Warum sollte man sich dann überhaupt die Mühe machen, die FILTER-Funktion zu verwenden?
Der Hauptvorteil der Verwendung von Excel-Funktionen gegenüber der manuellen Ausführung von Vorgängen (mit einem anderen Excel-Tool oder sogar einem anderen Programm) besteht darin, dass die Funktionen dynamisch sind. Mit dem Auto-Filter oder dem erweiterten Filter erhalten Sie einmalige Ergebnisse, die sich nicht ändern, wenn die Quelldaten geändert werden. Die FILTER-Funktion hingegen aktualisiert ihre Ergebnisse entsprechend, wenn sich die Daten ändern.
Syntax der FILTER-Funktion
Die Syntax der FILTER-Formel ist recht einfach:
=FILTER(array, include, [if_empty])
Ein Array ist eine rechteckige Teilmenge der Tabelle, die durch die Angabe eines Bereichs zwischen der oberen linken Zelle und der unteren rechten Zelle angegeben wird. Beispielsweise ist A3:E10 ein Array bestehend aus den Spalten A bis E und den Zeilen 3 bis 10..
Der nächste Parameter ist einfach das zu verwendende Kriterium oder technisch gesehen ein boolesches Array. Dies wird in Form eines Ausdrucks eingegeben, der den Wert eines Zellbereichs (normalerweise einer Spalte) auswertet, der WAHR oder FALSCH zurückgibt. Beispielsweise gibt A3:A10=“Pass“ TRUE zurück, wenn der Wert der Zelle mit der angegebenen Zeichenfolge übereinstimmt.
Schließlich können Sie einen Wert eingeben, der von der FILTER-Funktion zurückgegeben werden soll, wenn keine Zeilen den Bedingungen entsprechen. Dies kann eine einfache Zeichenfolge wie „Keine Datensätze gefunden“ sein.
Verwandt: Möchten Sie sehen, wie genau Beispieldaten zu einem größeren Datensatz passen? Schauen Sie sich unseren Leitfaden zu Berechnung des Standardfehlers in Excel an.
Verwenden der FILTER-Funktion
Da wir nun die Syntax der FILTER-Funktion kennen, schauen wir uns an, wie man FILTER tatsächlich in einer Tabelle verwendet.
Die Beispieldaten, die wir für diese Demonstration verwenden, umfassen ein Array von A2 bis F11, in dem die Biologieergebnisse von zehn Schülern zusammen mit der Normalverteilung tabellarisch aufgeführt sind.
Lassen Sie uns eine Funktion schreiben, um die Einträge basierend auf ihren Prüfungsergebnissen (aufgelistet in der Spalte D) zu filtern und nur diejenigen zurückzugeben, die weniger als 30 Punkte erzielt haben. Dies sollte die Syntax sein:
=FILTER(A2:F11,D2:D11<30,“Keine Übereinstimmungen gefunden“)
Da die gefilterten Ergebnisse eine Teilmenge des Arrays sind, verwenden Sie die Funktion in einer Zelle mit genügend Platz dahinter. Wir werden es unter der Originaltabelle tun:
Und wir erhalten die erwarteten Ergebnisse. Alle Einträge mit einer Punktzahl unter 30 werden ausgewählt und im gleichen Tabellenformat angezeigt.
Sie sind auch nicht auf eine einzige Bedingung beschränkt. Verwenden Sie den AND-Operator (*), um mehrere Ausdrücke als einen einzigen Parameter zu verketten und so einen komplexeren Filter zu erstellen.
Lassen Sie uns eine Funktion konstruieren, die die Einträge zurückgibt, die zwischen 30 und 70 Markierungen liegen. Hier ist die Syntax und die Ergebnisse:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),„Keine Übereinstimmungen gefunden“)
Für nicht-exklusive Kriterien können Sie auch den ODER-Operator (+) verwenden. Dies entspricht dem Filter, auch wenn nur eine der enthaltenen Bedingungen als TRUE ausgewertet wird.
In der folgenden Formel verwenden wir sie, um die Ausreißer zu finden, indem wir nach Ergebnissen unter 15 oder über 70 filtern.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70)„Keine Datensätze gefunden“)
Anstatt einen einzelnen Wert oder eine einzelne Zeichenfolge zu verwenden, die zurückgegeben wird, wenn die FILTER-Funktion nichts findet, können Sie schließlich Werte für jede Spalte angeben, um sicherzustellen, dass die Ausgabe immer ein konsistentes Format behält..
Lassen Sie uns zunächst eine Bedingung ausprobieren, von der wir wissen, dass sie falsch ist, um zu sehen, wie sie standardmäßig aussieht:
=FILTER(A2:F11,D2:D11>90,“Keine Übereinstimmungen gefunden“)
Wie Sie sehen, enthält das Ergebnis nur eine einzige Zeichenfolge, was im Widerspruch zum erwarteten Format steht. Dies stellt normalerweise kein Problem dar, es sei denn, Sie möchten die Ergebnisse (oder einige Werte daraus) in eine andere Formel einspeisen.
Versuchen wir also, Standardwerte im gleichen Format wie ein Eintrag im Array anzugeben. Dies erreichen wir, indem wir durch Kommas getrennte Werte in geschweiften Klammern angeben. So:
=FILTER(A2:F11,D2:D11>90,{„No Record“, „No Record“, „No Record“, 0})
Dadurch erhalten wir ansprechendere Ergebnisse, die mit dem restlichen Format der Tabelle übereinstimmen.
Lohnt sich die FILTER-Funktion?
Auch wenn Sie MS Excel nur zum Verwalten von Aufzeichnungen verwenden und keine ausgefallenen Berechnungen durchführen möchten, ist die FILTER-Funktion eine der wenigen, die Sie sich dennoch ansehen sollten.
Denn sobald Ihre Arbeitsmappe eine bestimmte Größe erreicht, Das manuelle Auffinden von Daten kann mühsam sein. Und obwohl die Tools „Automatischer Filter“ und „Erweiterter Filter“ praktisch sind, ist die Verwendung einer Funktion auf lange Sicht tatsächlich bequemer, da die Ergebnisse sich selbst aktualisieren und mit anderen Funktionen gekoppelt werden können.
.