Ich schrieb vor kurzem einen Artikel auf, wie zu verwenden Zusammenfassung von Funktionen in Excel problemlos große Datenmengen zusammenfassen, aber, der Artikel berücksichtigte alle Daten auf dem Arbeitsblatt. Was ist, wenn Sie nur möchten Blick auf eine Teilmenge von Daten und fassen Sie die Teilmenge der Daten?
In Excel können Sie Filter erstellen, die auf Spalten, Zeilen ausblenden, die nicht dem filter entsprechen. Darüber hinaus können Sie auch spezielle Funktionen in Excel zum zusammenfassen von Daten verwenden nur die gefilterten Daten.
In diesem Artikel werde ich gehen Sie durch die Schritte zum erstellen von filtern in Excel und auch über integrierte Funktionen zum zusammenfassen, dass die gefilterten Daten.
Erstellen von Einfachen Filtern in Excel
In Excel können Sie erstellen einfache Filter und komplexe Filter. Beginnen wir mit einfachen filtern. Wenn Sie mit filtern arbeiten, sollten Sie immer eine Zeile an der Spitze, der für Beschriftungen genutzt. Es ist keine Voraussetzung, um diese Reihe, aber es macht das arbeiten mit filtern ein bisschen einfacher.
Oben habe ich ein paar fake-Daten und ich möchte einen filter erstellen, der auf die Stadt Spalte. In Excel ist dies sehr einfach zu tun. Gehen Sie vor und klicken Sie auf die Daten – Registerkarte in der Multifunktionsleiste und klicken Sie dann auf die Filter – Schaltfläche. Sie müssen nicht wählen Sie die Daten auf der Platte, oder klicken Sie in die erste Zeile.
Wenn Sie klicken Sie auf “Filter”, in der jede Spalte in der ersten Zeile werden automatisch über ein kleines dropdown-button auf der rechten Seite.
Jetzt gehen Sie vor und klicken Sie auf den dropdown-Pfeil in der Spalte City. Sie werden sehen, ein paar verschiedene Optionen, die ich unten erklären.
An der Spitze, können Sie schnell Sortieren Sie alle Zeilen nach den Werten in der Spalte City. Beachten Sie, dass wenn Sie die Daten Sortieren, es wird die gesamte Zeile, nicht nur die Werte in der Spalte City. Dadurch wird sichergestellt, dass Ihre Daten intakt bleibt, so wie es vorher war.
Auch, es ist eine gute Idee, fügen Sie eine Spalte ganz vorne an der namens-ID und Zahl, von eins bis wie viele Reihen Sie in Ihrem Arbeitsblatt. Auf diese Weise können Sie immer die Sortierung nach der ID-Spalte und erhalten Sie Ihre Daten wieder in der gleichen Reihenfolge war es ursprünglich, wenn das für Sie wichtig ist.
Wie Sie sehen können, werden alle Daten in der Tabelle ist jetzt sortiert, basierend auf den Werten in der Spalte City. So weit, keine Zeilen ausgeblendet sind. Werfen wir nun einen Blick auf die Kontrollkästchen am unteren Rand der filter-dialog. In meinem Beispiel habe ich nur drei eindeutige Werte in der Spalte City und die drei in der Liste angezeigt.
Ich ging weiter und deaktiviert die zwei Städte und die Links geprüft. Jetzt habe ich nur noch 8 Zeilen von Daten, die zeigen, und der rest sind ausgeblendet. Sie können einfach sagen, Sie sind auf der Suche gefilterte Daten wenn Sie das Kontrollkästchen Zeilennummern ganz Links. Je nachdem, wie viele Zeilen ausgeblendet sind, Sie werden sehen, ein paar zusätzliche horizontale Linien und die Farbe der zahlen werden blau.
Jetzt sagen wir, ich will, um filter auf eine zweite Spalte zu einer weiteren Verringerung der Anzahl der Ergebnisse. In Spalte C habe ich die Gesamtzahl der Mitglieder in jeder Familie, und ich möchte nur die Ergebnisse sehen für Familien mit mehr als zwei Mitgliedern.
Go ahead, und klicken Sie auf den dropdown-Pfeil in der Spalte C und du wirst sehen, die gleichen Checkboxen für jeden eindeutigen Wert in der Spalte. Jedoch, in diesem Fall, wir möchten, klicken Sie auf Anzahl Filter, und klicken Sie dann auf Größer Als. Wie Sie sehen können, gibt es eine Reihe von anderen Möglichkeiten zu.
Ein neues Dialogfeld wird pop-up und geben Sie hier den Wert für den filter ein. Sie können auch mehrere Kriterien mit einer UND-oder ODER-Funktion. Könnten Sie sagen, Sie wollen die Zeilen, wo der Wert größer ist als 2 und nicht gleich 5, zum Beispiel.
Jetzt bin ich nur noch 5 Reihen von Daten: Familien, die nur aus New Orleans und mit 3 oder mehr Mitgliedern. Einfach genug? Beachten Sie, dass können Sie ganz einfach deaktivieren Sie einen filter auf eine Spalte, indem Sie auf die Dropdownliste, und klicken Sie anschließend auf den Filter Löschen Aus “Spaltenname” – link.
Damit ist es für einfache Filter in Excel. Sie sind sehr einfach zu bedienen und die Ergebnisse sind ziemlich straight-forward. Werfen wir nun einen Blick auf komplexe Filter mit der Erweiterten Filter-dialog.
Erstellen von Erweiterten Filtern in Excel
Wenn Sie mehr anlegen möchten erweiterte Filter, müssen Sie die Erweiterte filter-dialog. Zum Beispiel, sagen wir, ich wollte sehen, alle Familien Leben in New Orleans mit mehr als 2 Mitglieder in Ihrer Familie ODER alle Familien in Clarksville mit mehr als 3 Mitglieder in der Familie, UND nur diejenigen, die mit einem .EDU – Ende E-Mail-Adresse. Jetzt können Sie nicht tun, dass mit einem einfachen filter.
Um dies zu tun, müssen wir, um den setup-Excel-Blatt ein wenig anders. Gehen Sie voran und fügen Sie ein paar Zeilen über Ihre Daten und kopieren Sie die überschrift labels genau in der ersten Zeile, wie unten gezeigt.
Nun, hier ist, wie erweiterte Filter arbeiten. Du musst zuerst geben Sie Ihre Kriterien in den Spalten am oberen und klicken Sie dann auf den Erweitert – button unter Sort & Filter auf die Daten – Registerkarte.
Also, was genau können wir geben Sie in die Zellen? OK, beginnen wir also mit unserem Beispiel. Wir wollen nur, um zu sehen, Daten aus New Orleans oder Clarksville, so lassen Sie diese in die Zellen E2 und E3.
Wenn Sie Werte in verschiedenen Reihen, es bedeutet, ODER. Jetzt wollen wir New Orleans Familien mit mehr als zwei Mitgliedern und Clarksville Familien mit mehr als 3 Mitgliedern. Um dies zu tun, geben Sie in >2 in C2 und >3 in C3.
Da >2 und New Orleans sind in der gleichen Zeile, es wird eine UND-Verknüpfung. Das gleiche gilt für Zeile 3 oben. Schließlich wollen wir nur die Familien mit .EDU-Ende E-Mail-Adresse. Um dies zu tun, geben Sie einfach *.edu in D2 und D3. Das * – symbol bedeutet, dass eine beliebige Anzahl von Zeichen.
Sobald Sie das tun, klicken Sie irgendwo in Ihrem dataset, und klicken Sie dann auf die Erweiterte Schaltfläche. Die Liste Klingelte dase-Feld wird automatisch herauszufinden, Ihre datasets, da Sie geklickt haben, in die Sie vor dem klicken auf die Schaltfläche “Erweitert”. Klicken Sie nun auf die kleine Schaltfläche rechts neben den Kriterien Auswahl – Taste.
Wählen Sie alles von A1 bis E3 und klicken Sie dann auf die gleiche Taste erneut, um wieder auf die Advanced-Filter-dialog. Klicken Sie auf OK und die Daten sollen nun gefiltert werden!
Wie Sie sehen können, jetzt habe ich nur 3 Ergebnisse, die passen alle diese Kriterien. Beachten Sie, dass die Bezeichnungen für die Kriterien der Auswahl müssen exakt übereinstimmen mit den Bezeichnungen für das dataset, um für diese zu arbeiten.
Sie können natürlich eine Menge komplizierter Fragen mit dieser Methode, so spielen, um mit es, um Ihre gewünschten Ergebnisse. Schließlich reden wir über die Anwendung Summierung Funktionen, um die gefilterten Daten.
Zusammenfassung Der Gefilterten Daten
Jetzt sagen wir, ich möchte die Summe der Anzahl der Familienmitglieder auf meine gefilterten Daten, wie würde ich ungefähr machen? Nun, lasst uns klar unsere filter durch klicken auf die Löschen – Schaltfläche in der Multifunktionsleiste. Keine Sorge, es ist sehr einfach die erweiterten filter wieder, indem Sie einfach auf die Schaltfläche “Erweitert” und klicken Sie erneut auf OK.
Am Ende unserer dataset, fügen wir eine Zelle genannt Gesamt und fügen Sie dann eine sum-Funktion auf die Summe der gesamten Familie. In meinem Beispiel habe ich eingegeben =SUMME(C7:C31).
Also, wenn ich schaue auf alle Familien, die ich haben 78 Mitglieder insgesamt. Nun lassen Sie uns fortfahren und erneut unseren Erweiterten filtern und sehen, was passiert.
Hoppla! Anstatt zu zeigen, die richtige Zahl, 11, sehe ich immer noch die insgesamt 78! Warum ist das so? Gut, das die SUM-Funktion ignoriert ausgeblendete Zeilen, so ist es noch immer tun, die Berechnung über alle Zeilen. Zum Glück gibt es ein paar Funktionen, die Sie verwenden können, um die ausgeblendeten Zeilen.
Die erste TEILSUMME. Bevor wir eine dieser speziellen Funktionen, die Sie wollen, deaktivieren Sie die filter und geben Sie dann in der Funktion.
Sobald der filter deaktiviert ist, gehen Sie vor und geben Sie =ZWISCHENSUMME( und Sie sollten sehen, ein dropdown-Feld mit einer Reihe von Optionen. Mit dieser Funktion, müssen Sie zuerst wählen Sie die Art der Summierung Funktion, die Sie verwenden möchten, indem Sie eine Anzahl.
In unserem Beispiel möchte ich die Verwendung der SUMME, so würde ich den Typ in der Nummer 9, oder klicken Sie auf Sie aus der dropdown-Liste. Dann geben Sie ein Komma und wählen Sie den Bereich von Zellen.
Wenn Sie enter drücken, sollten Sie sehen, der Wert von 78 ist die gleiche wie zuvor. Jedoch, wenn Sie nun den filter anwenden, dann werden wir sehen, 11!
Ausgezeichnet! Das ist genau das, was wir wollen. Sie können nun passen Sie Ihre Filter und der Wert wird immer spiegeln nur die Zeilen, die sich derzeit zeigen.
Die zweite Funktion, das funktioniert so ziemlich genau das gleiche wie die TEILERGEBNIS-Funktion ist AGGREGATE. Der einzige Unterschied ist, dass es einen anderen parameter in der Aggregatfunktion wo Sie müssen angeben, dass Sie möchten, um die ausgeblendeten Zeilen.
Der erste parameter ist die Summe-Funktion, die Sie verwenden möchten, und wie mit ZWISCHENSUMME 9 stellt die SUMME-Funktion. Die zweite option ist, wo Sie eingeben müssen, in 5 bis ausgeblendeten Zeilen. Der Letzte parameter ist die gleiche, und ist der Bereich von Zellen.
Lesen Sie auch meinen Artikel über die Zusammenfassung von Funktionen zu erfahren, wie die AGGREGAT-Funktion und andere Funktionen wie MODUS, MEDIAN, DURCHSCHNITT, etc. in mehr detail.
Ich hoffe, dieser Artikel gibt Ihnen einen guten Ausgangspunkt für die Erstellung und Benutzung von filtern in Excel. Wenn Sie irgendwelche Fragen haben, fühlen Sie sich frei, um einen Kommentar zu schreiben. Genießen Sie!