Ich habe kürzlich einen Artikel über wie man Zusammenfassungsfunktionen in Excel benutzt geschrieben, um große Datenmengen einfach zusammenzufassen, aber dieser Artikel berücksichtigte alle Daten auf dem Arbeitsblatt. Was ist, wenn Sie nur eine Teilmenge der Daten betrachten und die Teilmenge der Daten zusammenfassen möchten?
In Excel können Sie Filter für Spalten erstellen, die Zeilen ausblenden, die nicht mit Ihrem Filter übereinstimmen. Darüber hinaus können Sie auch spezielle Funktionen in Excel verwenden, um Daten nur mit den gefilterten Daten zusammenzufassen.
In diesem Artikel werde ich Sie durch die Schritte zum Erstellen von Filtern in Excel und auch mithilfe von integrierten ausführen fasst die gefilterten Daten zusammen.
Einfache Filter in Excel erstellen
In Excel können Sie einfache Filter und komplexe Filter erstellen. Beginnen wir mit einfachen Filtern. Wenn Sie mit Filtern arbeiten, sollten Sie immer eine Zeile oben haben, die für Labels verwendet wird. Es ist keine Voraussetzung für diese Zeile, aber es macht das Arbeiten mit Filtern ein bisschen einfacher.
Oben habe ich einige gefälschte Daten und ich Sie möchten einen Filter für die Spalte Stadterstellen. In Excel ist das wirklich einfach. Klicken Sie auf die Registerkarte Datenin der Multifunktionsleiste und dann auf die Schaltfläche Filter. Sie müssen die Daten auf dem Blatt nicht auswählen oder in die erste Zeile klicken.
Wenn Sie auf Filter klicken, wird jede Spalte angezeigt In der ersten Zeile wird automatisch eine kleine Dropdown - Schaltfläche ganz rechts hinzugefügt.
Gehen Sie nun voran und klicken Sie auf den Dropdown-Pfeil in der Stadtsäule. Sie sehen einige verschiedene Optionen, die ich im Folgenden erläutern werde.
Oben können Sie schnell alle Zeilen nach sortieren die Werte in der Spalte Stadt. Beachten Sie, dass beim Sortieren der Daten die gesamte Zeile verschoben wird und nicht nur die Werte in der Spalte "Stadt". Dies stellt sicher, dass Ihre Daten genauso wie zuvor intakt bleiben.
Es ist auch eine gute Idee, eine Spalte an der vordersten Front namens ID hinzuzufügen und sie von eins bis zu vielen Zeilen zu nummerieren Arbeitsblatt. Auf diese Weise können Sie immer nach der ID-Spalte sortieren und Ihre Daten in der ursprünglichen Reihenfolge wiederherstellen, wenn dies für Sie wichtig ist.
Wie Sie sehen, werden alle Daten in der Tabelle jetzt basierend auf den Werten in der Spalte "Stadt" sortiert. Bis jetzt sind keine Zeilen versteckt. Sehen wir uns nun die Kontrollkästchen im unteren Teil des Filterdialogs an. In meinem Beispiel habe ich nur drei eindeutige Werte in der Spalte "Stadt", und diese drei erscheinen in der Liste.
Ich ging voraus und deaktivierte das Kontrollkästchen zwei Städte und eine links geprüft. Jetzt habe ich nur 8 Zeilen mit Daten und der Rest ist ausgeblendet. Sie können leicht feststellen, dass Sie gefilterte Daten betrachten, wenn Sie die Zeilennummern ganz links überprüfen. Je nachdem, wie viele Zeilen ausgeblendet sind, sehen Sie ein paar zusätzliche horizontale Linien und die Farbe der Zahlen wird blau.
Nun sagen wir, ich möchte nach einer zweiten Spalte filtern, um die Anzahl weiter zu reduzieren von Ergebnissen. In Spalte C habe ich die Gesamtzahl der Mitglieder in jeder Familie und möchte nur die Ergebnisse für Familien mit mehr als zwei Mitgliedern sehen.
Gehen Sie weiter und klicken Sie auf den Dropdown-Pfeil in Spalte C, und für jeden eindeutigen Wert in der Spalte werden dieselben Kontrollkästchen angezeigt. In diesem Fall möchten wir jedoch auf Zahlenfilterklicken und dann auf Größer alsklicken. Wie Sie sehen, gibt es eine Reihe weiterer Optionen.
Ein neuer Dialog wird angezeigt und hier können Sie den Wert eingeben für den Filter. Sie können auch mehrere Kriterien mit einer UND- oder ODER-Funktion hinzufügen. Sie könnten sagen, Sie wollen Zeilen, wo der Wert größer als 2 und nicht gleich 5 ist.
Jetzt bin ich dran nur 5 Datenzeilen: Familien nur aus New Orleans und mit 3 oder mehr Mitgliedern. Leicht genug? Beachten Sie, dass Sie einen Filter für eine Spalte einfach löschen können, indem Sie auf das Dropdown-Menü klicken und dann auf den Link Filter löschen von "Spaltenname"klicken.
Also das ist es für einfache Filter in Excel. Sie sind sehr einfach zu bedienen und die Ergebnisse sind ziemlich einfach. Sehen wir uns nun komplexe Filter im Dialogfeld Erweitertan.
Erstellen Sie erweiterte Filter in Excel
Wenn Sie erweiterte Filter erstellen möchten, müssen Sie muss den Filterfilter Erweitertverwenden. Angenommen, ich möchte alle Familien in New Orleans mit mehr als 2 Mitgliedern in ihrer Familie sehen ODERalle Familien in Clarksville mit mehr als 3 Mitgliedern in ihrer Familie UNDnur die mit einer .EDUendenden E-Mail-Adresse. Jetzt können Sie das nicht mit einem einfachen Filter tun.
Dazu müssen wir das Excel-Blatt etwas anders einrichten. Gehen Sie voran und fügen Sie einige Zeilen über Ihre Datenmenge ein und kopieren Sie die Überschriftenbeschriftungen genau in die erste Zeile, wie unten gezeigt.
Now Hier sehen Sie, wie fortschrittliche Filter funktionieren. Sie müssen zuerst Ihre Kriterien in die oberen Spalten eingeben und dann auf die Schaltfläche Erweitertunter Sortieren & amp; Filtern Sieauf der Registerkarte Daten.
Was genau können wir in diese Zellen eingeben? OK, fangen wir mit unserem Beispiel an. Wir wollen nur Daten aus New Orleans oder Clarksville sehen, also geben wir diese in die Zellen E2 und E3 ein.
Wenn Sie Werte auf verschiedene Werte eingeben Zeilen, es bedeutet ODER. Jetzt wollen wir New Orleans Familien mit mehr als zwei Mitgliedern und Clarksville Familien mit mehr als 3 Mitgliedern. Geben Sie dazu & gt; 2in C2 und & gt; 3in C3 ein.
Da sich & gt; 2 und New Orleans in derselben Reihe befinden, wird es ein UND-Operator sein. Das Gleiche gilt für die obige Zeile 3. Schließlich möchten wir nur die Familien mit .EDU endender E-Mail-Adresse. Um dies zu tun, geben Sie einfach *. Eduin D2 und D3 ein. Das Symbol * steht für eine beliebige Anzahl von Zeichen.
Sobald Sie dies getan haben, klicken Sie auf eine beliebige Stelle in Ihrem Dataset und dann auf ErweitertSchaltfläche. Das Listen Range Feld ermittelt automatisch Ihren Datensatz, seit Sie darauf geklickt haben, bevor Sie auf die Schaltfläche Erweitert klicken. Klicken Sie nun auf die kleine Schaltfläche rechts neben der Schaltfläche Kriterienbereich.
Wählen Sie alles von A1 bis E3 und klicken Sie dann erneut auf die gleiche Schaltfläche, um zum Dialogfeld "Erweiterter Filter" zurückzukehren. Klicken Sie auf OK und Ihre Daten sollten jetzt gefiltert werden!
Wie Sie sehen können, habe ich jetzt nur noch 3 Ergebnisse, die alle diese Kriterien erfüllen. Beachten Sie, dass die Beschriftungen für den Kriterienbereich genau mit den Beschriftungen für das Dataset übereinstimmen müssen, damit dies funktioniert.
Sie können natürlich mit dieser Methode viel kompliziertere Abfragen erstellen, also spielen Sie damit herum um die gewünschten Ergebnisse zu erzielen. Abschließend sprechen wir über die Anwendung von Summierungsfunktionen auf gefilterte Daten.
Gefilterte Daten zusammenfassen
Nun möchte ich die Anzahl der Familienmitglieder in meinen gefilterten Daten zusammenfassen, wie würde ich das tun hast du das gemacht? Nun, lassen Sie uns unseren Filter löschen, indem Sie auf die Schaltfläche Löschenin der Multifunktionsleiste klicken. Keine Sorge, es ist sehr einfach, den erweiterten Filter erneut anzuwenden, indem Sie einfach auf die Schaltfläche Erweitert klicken und erneut auf OK klicken.
Beim Unten in unserem Dataset fügen wir eine Zelle mit dem Namen Gesamthinzu und fügen dann eine Summenfunktion hinzu, um die Gesamtzahl der Familienmitglieder zusammenzufassen. In meinem Beispiel habe ich einfach = SUM (C7: C31)eingegeben.
Wenn ich also alles anschaue Familien habe ich insgesamt 78 Mitglieder. Nun lassen Sie uns unseren erweiterten Filter erneut anwenden und sehen, was passiert.
Whoops! Anstatt die korrekte Zahl 11 anzuzeigen, sehe ich immer noch die Summe 78! Warum das? Nun, die Funktion SUMME ignoriert versteckte Zeilen nicht, also macht sie immer noch die Berechnung mit allen Zeilen. Glücklicherweise gibt es ein paar Funktionen, mit denen Sie versteckte Zeilen ignorieren können.
Das erste ist SUBTOTAL. Bevor wir eine dieser speziellen Funktionen verwenden, sollten Sie den Filter löschen und dann die Funktion eingeben.
Sobald der Filter gelöscht ist, geben Sie = SUBTOTAL (und Sie sollten eine Dropdown-Box mit einer Reihe von Optionen sehen.Mit dieser Funktion wählen Sie zuerst die Art der Summierungsfunktion, die Sie verwenden möchten, mit einer Zahl.
In unserem Beispiel möchte ich verwenden SUMME, also tippe ich die Zahl 9 ein oder klicke einfach auf das Dropdown-Menü, dann gebe ein Komma ein und wähle den Zellenbereich aus.
Wenn du die Eingabetaste drückst, solltest du sehen, dass der Wert von 78 der gleiche wie zuvor ist.Wenn du den Filter aber jetzt erneut anwählst, werden wir 11 sehen!
Excellent! Genau das wollen wir. Jetzt können Sie Ihre Filter anpassen und der Wert wird immer nur die Zeilen wiedergeben, die gerade angezeigt werden.
Die zweite Funktion, die genauso funktioniert wie die Funktion SUBTOTAL, ist AGGREGATEDer Unterschied besteht darin, dass in der Funktion AGGREGATE ein weiterer Parameter vorhanden ist, bei dem Sie angeben müssen, dass Sie ausgeblendete Zeilen ignorieren möchten.
Der erste Parameter ist die Summierungsfunktion, die Sie verwenden möchten, und wie bei SUBTOTAL steht 9 für die SUM-Funktion. Die zweite Option ist, wo Sie 5 eingeben müssen, um versteckte Zeilen zu ignorieren. Der letzte Parameter ist derselbe und ist der Bereich der Zellen.
Sie können auch meinen Artikel über Zusammenfassungsfunktionen lesen, um zu erfahren, wie Sie Verwenden Sie die AGGREGATE-Funktion und andere Funktionen wie MODE, MEDIAN, AVERAGE usw. genauer kennen lernen.
Hoffentlich, das Artikel gibt Ihnen einen guten Ausgangspunkt für das Erstellen und Verwenden von Filtern in Excel. Wenn Sie Fragen haben, zögern Sie nicht, einen Kommentar zu schreiben. Viel Spaß!