Das Tutorial zeigt, wie der erweiterte Filter in Excel verwendet wird, und bietet eine Reihe von Beispielen für nicht-triviale Kriterienbereiche, um einen Fallempfindungsfilter zu erstellen, übereinstimmungen und Unterschiede zwischen zwei Spalten zu finden, Datens?tze zu extrahieren, die mit einer kleineren Liste übereinstimmen, und mehr.

In unserem vorherigen Artikel haben wir verschiedene Aspekte des Excel Advanced Filters er?rtert und wie man ihn verwendet, um Zeilen mit und oder oder logisch zu filtern. Nachdem Sie die Grundlagen kennen, schauen wir uns eine komplexere Beispiele für Kriterien an, die sich für Ihre Arbeit als hilfreich erweisen k?nnen.

Einrichten eines Formel-basierten Kriterienbereichs

Da die meisten in diesem Tutorial diskutierten Beispiele für Kriterien verschiedene Formeln enthalten, beginnen wir mit der Definition der wesentlichen Regeln, um sie ordnungsgem?? einzurichten. Vertrauen Sie mir, diese kleine Theorie spart Ihnen viel Zeit und sparen Sie die Kopfschmerzen bei der Fehlerbehebung bei Ihren komplexen Kriterien, die mehrere Bedingungen basieren, die auf Formeln basieren.

• Die Formel, die Sie im Kriterienbereich verwenden, muss auf True oder False bewertet werden.
• Der Kriterienbereich sollte mindestens 2 Zellen enthalten: Formelzelle und Kopfzellzelle.
• Die Header-Zelle der Formel-basierten Kriterien sollte entweder leer oder von einer der überschriften der Tabelle (Listenbereich) unterscheiden.
• Damit die Formel für jede Zeile im Listenbereich bewertet werden kann, finden Sie in der obersten Zelle mit Daten unter Verwendung einer relativen Referenz wie A1.
• Damit die Formel nur für eine bestimmte Zelle oder einen bestimmten Zellbereich bewertet werden kann, beziehen Sie sich auf diese Zelle oder den Bereich unter Verwendung einer absoluten Referenz wie $ a $ 1.
• Verwenden Sie bei Bezug auf den Listenbereich in der Formel immer absolute Zellreferenzen.
• Geben Sie bei der Lieferung mehrerer Bedingungen alle Kriterien in derselben Zeile ein, um sie mit einem und einem Operator anzuschlie?en, und legen Sie jedes Kriterium in eine separate Zeile, um sie mit dem oder dem Operator zu verbinden.

Excel Advanced Filter Criteria -Bereiche Beispiele

In den folgenden Beispielen wird unterrichtet, wie Sie Ihre eigenen Filter in Excel erstellen, um komplexere Aufgaben zu erledigen, die mit dem regul?ren Excel -Autofilter nicht ausgeführt werden k?nnen.

Fallempfindlicher Filter für Textwerte

Das erweiterte Filter-Tool ist nicht als Excel-Autofilter von Natur aus unempfindlich, was bedeutet, dass es beim Filtern von Textwerten nicht zwischen Gro?- und Kleinbuchstaben unterscheidet. Sie k?nnen jedoch einfach eine Fallempfindlichkeitsuche durchführen, indem Sie die genaue Funktion in den erweiterten Filterkriterien verwenden.

Geben Sie beispielsweise Zeilen, die Banane enthalten, Banane und Banane ignorieren, in die folgende Formel in die Kriterienbereich ein:

=EXACT(B5, "Banana")

Wobei B die Spalte mit den Elementnamen ist und Zeile 5 die erste Datenzeile ist.

Anschlie?end den Excel Advanced Filter anwenden, indem Sie auf der Registerkarte Daten auf die erweiterte Schaltfl?che klicken und den Listenbereich und den Kriterienbereich konfigurieren, wie im folgenden Screenshot gezeigt. Bitte achten Sie darauf, dass der Kriterienbereich 2 Zellen enth?lt - die Kopfzeilezelle und die Formelzelle .

Notiz. Das obige Bild auch alle weiteren Screenshots in diesem Tutorial zeigen Formeln in den Kriterienbereichszellen ausschlie?lich zur Klarheit. In Ihren realen Arbeitsbl?ttern sollte die Formelzelle entweder True oder False zurückgeben, je nachdem, ob die erste Datenreihe den Kriterien übereinstimmt oder nicht:

Filterwerte über oder unterdurchschnittlich in einer Spalte

Wenn Sie numerische Werte filtern, m?chten Sie m?glicherweise h?ufig nur diejenigen Zellen anzeigen, die in der Spalte über oder unter einem Durchschnittswert liegen. Zum Beispiel:

Verwenden Sie die folgende Formel im Kriterienbereich, um Zeilen mit dem überdurchschnittlichen Unterschnitt zu filtern:

=F5>AVERAGE($F$5:$F$50)

Verwenden Sie die folgende Formel, um Zeilen mit Unterdurchschnitt unterdurchschnittlich zu filtern:

=F5<average></average>

Bitte achten Sie darauf, dass wir einen relativen Verweis verwenden, um auf die Top-Cell mit Daten (F5) zu verweisen, und absolute Verweise, um den gesamten Bereich zu definieren, für den Sie den Durchschnitt berechnen m?chten, ausgenommen die Spaltenüberschrift ($ F $ 5: $ F $ 50).

Der folgende Screenshot zeigt die überdurchschnittliche Formel in Aktion:

Diejenigen unter Ihnen, die mit Excel-Number-Filtern vertraut sind, fragen sich vielleicht, warum sich jemand die Mühe machen sollte, einen fortgeschrittenen Filter zu verwenden, w?hrend die integrierten Zahlenfilter bereits die überdurchschnittlichen und unterdurchschnittlichen Optionen haben? Das stimmt, aber die eingebauten Excel -Filter k?nnen nicht mit der oder Logik verwendet werden!

Um dieses Beispiel weiter zu nehmen, filtern wir Zeilen, an denen der Umsatz ( Spalte F) oder September (Spalte E) überdurchschnittlich ist. Richten Sie dazu den Kriterienbereich mit der oder Logik ein, indem Sie jede Bedingung in einer separaten Zeile eingeben. Als Ergebnis erhalten Sie eine Liste von Elementen mit den überdurchschnittlichen Werten in Spalte E oder F:

Filterreihen mit Leerzeichen oder Nichtblücken

Wie jeder wei?, verfügt Excel Filter über eine eingebaute Option zum Filtern von leeren Zellen. Durch Ausw?hlen oder Deaktivieren des Kontrollk?stchens (Rohlinge) im AutoOFILTER-Menü k?nnen Sie nur die Zeilen anzeigen, die in einer oder mehreren Spalten leere oder nicht leere Zellen haben. Das Problem ist, dass der eingebaute Excel-Filter für Rohlinge nur mit der und der Logik arbeiten kann.

Wenn Sie Blanko- oder Nicht-Blank-Zellen mit der oder Logik filtern oder die leeren / nicht-Blank-Bedingungen zusammen mit einigen anderen Kriterien verwenden m?chten, richten Sie einen erweiterten Filterkriterienbereich mit einem der folgenden Formeln ein:

Filterlücken :

FILTER NICHTSBRACHTE:

Filtere leere Zellen mit der oder Logik

Um Zeilen zu filtern, die eine leere Zelle entweder in Spalte A oder B oder in beiden Spalten haben, konfigurieren Sie den Bereich der erweiterten Filterkriterien auf diese Weise:

• =A6=""
• =B6=""

Wobei 6 die oberste Datenreihe ist.

Filterung von Nicht-Blank-Zellen mit oder auch Wells AS und Logik

Um mehr Verst?ndnis dafür zu erlangen, wie der fortgeschrittene Filter von Excel mit mehreren Kriterien funktioniert, filtern wir Zeilen in unserer Stichprobenentabelle mit den folgenden Bedingungen:

• Entweder Region (Spalte A) oder Element (Spalte B) sollte nicht unscharf sein und
• Unter-Total (Spalte C) sollte gr??er als 900 sein.

Um es anders auszudrücken, m?chten wir Zeilen anzeigen, die den folgenden Bedingungen erfüllen:

( Subtotal > 900 und Region = Nicht-Blank) oder ( subtotal > 900 und Item = Nicht-Blank)

Wie Sie bereits wissen, sollten im Bereich Excel Advanced Filter Criteria die Bedingungen mit der und Logik in derselben Zeile eingegeben werden, und die Bedingungen, die mit der oder Logik verbunden sind - auf verschiedenen Zeilen:

Da ein Kriterium in diesem Beispiel mit einer Formel (Nichtblanks) ausgedrückt wird und das andere einen Vergleichsbetreiber (Unter-Total> 900) enth?lt, lassen Sie mich daran erinnern:

• Die mit Vergleichsoperatoren gebildeten Kriterien sollten genau den Tischüberschriften entsprechen, wie die Untertot- Kriterien im obigen Screenshot.
• Formel-basierte Kriterien sollten entweder eine leere Zelle oder eine überschrift haben, die keine der Tischüberschriften entspricht, wie die Nichtblachenkriterien im obigen Screenshot.

So extrahieren Sie obere/untere n -Datens?tze

Wie Sie wahrscheinlich wissen, haben die Excel-Nummernfilter der Build-In die Option, die Top 10 oder unter 10 Elemente anzuzeigen. Aber was ist, wenn Sie die oberen 3- oder unteren 5 -Werte filtern müssen? In diesem Fall ist Excel Advanced Filter mit den folgenden Formeln nützlich:

Top n Elemente extrahieren:

BEWEISE N -Elemente extrahieren:

Erstellen Sie zum Beispiel, um die Top 3 -Subtotale zu filtern, den Kriterienbereich mit dieser Formel:

=F5>=LARGE($F$5:$F$50,3)

Verwenden Sie diese Formel, um untere 3 Subtotale zu extrahieren:

=F5>=SMALL($F$5:$F$50,3)

Wobei F5 die oberste Zelle mit Daten in der subtotalen Spalte ist (ohne die Spaltenüberschriften).

Der folgende Screenshot zeigt die Top 3 -Formel in Aktion:

Notiz. Wenn der Listenbereich einige Zeilen mit den gleichen Werten enth?lt, die in die obere/untere N -Liste fallen, werden alle dieser Zeilen angezeigt, wie im folgenden Screenshot gezeigt:

Filter für übereinstimmungen und Unterschiede zwischen zwei Spalten

In einem unserer vorherigen Artikel wurde eine Vielzahl von M?glichkeiten erl?utert, zwei Spalten in Excel zu vergleichen und übereinstimmungen und Unterschiede zwischen ihnen zu finden. Zus?tzlich zu Excel -Formeln, bedingten Formatierungsregeln und dem im obigen Tutorial behandelten doppelten Entfernungswerkzeug k?nnen Sie auch den erweiterten Filter von Excel verwenden, um Zeilen zu extrahieren, die die gleichen oder unterschiedlichen Werte in zwei oder mehr Spalten haben. Geben Sie dazu eines der folgenden einfachen Formeln im Kriterienbereich ein:

• Filter für übereinstimmungen (Duplikate) in 2 Spalten: =B5=C5
• Filter für Unterschiede (eindeutige Werte) in 2 Spalten: =B5C5

Wobei B5 und C5 die obersten Zellen mit Daten in den beiden Spalten sind, die Sie vergleichen m?chten.

Notiz. Das erweiterte Filter -Tool kann nur nach übereinstimmungen und Unterschieden in derselben Zeile suchen. Verwenden Sie diese Formel, um alle Werte zu finden, die sich in Spalte A befinden, aber in Spalte B nirgends in Spalte B befinden.

Filterreihen basierend auf passenden Elementen in einer Liste

Angenommen, Sie haben einen gro?en Tisch mit Hunderten oder Tausenden von Zeilen, und Sie haben eine kürzere Liste erhalten, die nur die zu einem bestimmten Zeitpunkt relevanten Elemente enthielt. Die Frage ist: Wie finden Sie alle Eintr?ge in Ihrer Tabelle, die in der kleineren Liste sind oder nicht?

Filterreihen, die Elemente in einer Liste übereinstimmen

Um alle Elemente in der Quellentabelle zu finden, die auch in einer kleineren Liste vorhanden sind, unter Verwendung der folgenden Countif -Formel:

Unter der Annahme, dass sich die kleinere Liste im Bereich D2: D7 befindet und die Elemente der Tabelle, die mit dieser Liste verglichen werden sollen, beginnen in Spalte B, beginnend mit Zeile 10, lautet die Formel wie folgt (bitte beachten Sie die Verwendung von absoluten und relativen Referenzen):

=COUNTIF($D$2:$D$7,B10)

Natürlich sind Sie nicht darauf beschr?nkt, Ihren Tisch mit nur einem Kriterium zu filtern.

Zum Beispiel geben Sie zum Filter von Zeilen, die der Liste entsprechen. Geben Sie jedoch nur für die Nordregion zwei Kriterien in derselben Zeile ein, damit sie mit der und Logik arbeiten:

• Region: ="=North"
• übereinstimmende Artikel: =COUNTIF($D$2:$D$7,B10)

Wie Sie im folgenden Screenshot sehen k?nnen, gibt es in der Tabelle nur zwei Datens?tze, die beide Kriterien entsprechen:

Notiz. In diesem Beispiel verwenden wir die exakten übereinstimmungskriterien für Textwerte: <em>="=North</em> " um nur die Zellen zu finden, die genau dem angegebenen Text entsprechen. Wenn Sie die Regionkriterien einfach als Norden eingeben (ohne das gleiche Zeichen und die doppelten Zitate), findet Microsoft Excel alle Elemente, die mit dem angegebenen Text, z. B. Nordosten oder Nordwesten , beginnen. Weitere Informationen finden Sie in Excel Advanced Filter für Textwerte.

Filterreihen, die die Elemente in einer Liste nicht übereinstimmen

Um alle Elemente in der Tabelle zu finden, die sich nicht in der kleineren Liste befinden, prüfen Sie, ob das Ergebnis unserer Countif -Formel gleich Null ist:

Verwenden Sie beispielsweise die folgenden Kriterien, um die Elemente der Nordregion in der Tabelle zu filtern, die in der Liste angezeigt werden:

• Region: ="=North"
• Nichtübereinstimmende Artikel: =COUNTIF($D$2:$D$7,B10)=0

Anmerkungen:

• Wenn sich die übereinstimmende Liste in einem anderen Arbeitsblatt befindet, geben Sie den Blattnamen in der Formel, z =COUNTIF(Sheet2!$A$2:$A$7,B10) .
• Wenn Sie die Ergebnisse in ein anderes Blatt extrahieren m?chten, starten Sie den erweiterten Filter vom Zielblatt aus, wie in der Art und Weise, wie man gefilterte Zeilen auf ein anderes Arbeitsblatt extrahiert, erl?utert.

Filter für Wochenenden und Wochentage

Bisher haben sich unsere Beispiele für fortgeschrittene Filterkriterien haupts?chlich mit numerischen und Textwerten befasst. Jetzt ist es Zeit, denjenigen unter Ihnen, die an Daten arbeiten, einige Hinweise zu geben.

Die integrierten Excel-Datumsfilter bieten eine breite Palette von Optionen, die viele Szenarien abdecken. Viele, aber nicht alle! Wenn Sie beispielsweise eine Liste von Daten erhalten und gebeten wurden, Wochentage und Wochenenden zu filtern, wie würden Sie es dann vorgehen?

Wie Sie wahrscheinlich wissen, bietet Microsoft Excel eine spezielle Wochentagsfunktion, die den Wochentag zurückgibt, der einem bestimmten Datum entspricht. Und es ist diese Funktion, die wir im Bereich Excel Advanced Filter Criteria verwenden werden.

Wie man Wochenenden in Excel filtert

Beachten Sie, dass 1 für Sunday und 6 für Saturday die Formel zum Filterwochenenden wie folgt lautet:

In diesem Beispiel filtern wir die Daten in Spalte B, beginnend mit Zeile 5, sodass unsere Wochenenden die folgende Form annimmt:

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

So filtern Sie Wochentage in Excel

Um die Wochentage zu filtern, ?ndern Sie die obige Formel so, dass sie einen (Sonntag) und 7 (Samstag) auslassen:

Für unsere Stichprobentabelle wird die folgende Formel einen Genuss erleiden:

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

Zus?tzlich k?nnen Sie eine weitere Bedingung hinzufügen, um leere Zellen herauszufiltern: =B5""

Um die Daten in Ihren Arbeitsbl?ttern auf andere Weise zu filtern, finden Sie einfach die relevante Datumsfunktion und z?gern Sie nicht, sie in Ihrem erweiterten Filterkriterienbereich zu verwenden.

So verwenden Sie den erweiterten Filter in Excel mit komplexen Kriterien. Natürlich sind Ihre Optionen nicht auf die in diesem Tutorial besprochenen Beispiele beschr?nkt. Unser Ziel war es nur, Ihnen ein paar inspirierende Ideen zu geben, die Sie auf den richtigen Weg bringen. Wenn Sie sich daran erinnern, dass der Weg zur Meisterschaft durch die übung gepflastert ist, m?chten Sie unsere Beispiele mit dem folgenden Link herunterladen und sie erweitern oder umgekehrt, um ein besseres Verst?ndnis zu erhalten. Ich danke Ihnen für das Lesen und hoffe, Sie n?chste Woche in unserem Blog zu sehen!

Arbeitsbuch üben

Excel Advanced Filter Beispiele (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonSo verwenden Sie erweiterte Filter in Excel - Kriterienbereich Beispiele mit Formeln. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!