Tabelle in Excel filtern

Datensätze in Excel mit dynamischer Arrayfunktion filtern

Wenn Sie sich nur für bestimmte Datensätze in einer Tabelle interessieren, werden Sie in der Regel die Filterfunktionen nutzen und damit alle anderen Datensätze ausblenden. Durch die Aktivierung des Spezialfilters und die Eingabe eines Kriterienbereichs können Sie bei Bedarf die gefilterten Datensätze auch an eine andere Stelle in der Arbeitsmappe platzieren. Mit der neuen dynamischen Array-Funktion FILTER ist das Filtern von Datensätzen an eine andere Stelle erheblich einfacher geworden.

Datensätze nach einem Kriterium filtern

Aus einer Tabelle mit Kundenbestellungen sollen Datensätze, die eine bestimmte Bedingung erfüllen, an eine andere Stelle im Tabellenblatt dargestellt werden, d. h.  die Tabelle selbst wird nicht gefiltert. Die Tabelle hat den Namen Bestellungen und sieht folgendermaßen aus:

Excel-Tabelle mit Bestellungen

Um alle Bestellungen eines Kunden anzuzeigen, können Sie die dynamische Arrayfunktion FILTER einsetzen. Diese Funktion steht Ihnen bzw. wird Ihnen demnächst nur zur Verfügung stehen, wenn Sie Excel über Office 365 abonnieren. Gehen Sie in unserem Beispiel folgendermaßen vor:

  1. Geben Sie in der Zelle F1 den Namen des gewünschten Kunden ein.
  2. Aktivieren Sie die Zelle E4 und geben Sie die folgende Formel ein:

    =FILTER(Bestellungen;Bestellungen[Kunde]=F1)

Das Tabellenblatt mit dem Formelergebnis sieht folgendermaßen aus:

Tabelle nach einer Bedingung filtern

Es wird ab der Zelle E4 ein sog. Überlaufbereich eingerichtet. Der Überlaufbereich hat in unserem Beispiel so viel Spalten wie die Tabelle Bestellungen. Die Anzahl der Zeilen entspricht der Anzahl der Bestellungen des Kunden Schulte. Die Daten in den Spalten Bestelldatum und Umsatz werden in der verwendeten Excel-Version nicht formatiert.

Grundlegende Ausführungen zum Überlaufbereich finden Sie hier.

Funktionsassistent Filter

Der Funktionsassistent für die Funktion FILTER sieht so aus:

Funktionsassistent für die Funktion FILTER

Die Funktion FILTER kennt insgesamt 3 Argumente, wobei nur die ersten beiden Argumente angegeben werden müssen:

=FILTER(Matrix;Einschließen;[Wenn_leer])

In unserem obigen Beispiel wurde als Matrix die Tabelle Bestellungen angegeben. In dem Argument Einschließen geben Sie eine einzelne Bedingung oder mehrere Bedingungen ein, die die Datensätze erfüllen sollen. Mit dem Ausdruck Bestellungen[Kunde]=F1 werden nur die Datensätze berücksichtigt, bei denen in der Spalte Kunde der Name steht, der in Zelle F1 eingegeben wurde.

Mit dem dritten Parameter legen Sie fest, welcher Text in der Formelzelle erscheinen soll, wenn keiner der Datensätze die eingegebene Bedingung erfüllt.

Wenn Sie in der Zelle F1 den Namen Kramer eingeben, wird kein Datensatz gefunden werden.

Im Tabellenblatt sieht das so aus:

Funktion FILTER mit Argument Wenn leer

Wenn Sie in dem Überlaufbereich statt dem Fehlerwert #KALK einen anderen Hinweis wünschen, wie z.B. den Ausdruck Keine Datensätze gefunden, brauchen Sie in der Formel nur das dritte Argument anzugeben:

Die Formel würde in diesem Fall so aussehen:

=FILTER(Bestellungen;Bestellungen[Kunde]=F1;”Keine Datensätze gefunden”)

Datensätze nach mehreren Kriterien filtern

Beim Einsatz der Funktion FILTER können Sie auch mehrere Bedingungen eingeben, die erfüllt sein sollen. Die Angabe der Bedingungen unterscheidet sich, je nachdem, ob Sie eine UND-Bedingung oder eine ODER-Bedingung formulieren möchten.

Eine UND-Bedingung

Bei einer UND-Bedingung müssen alle Bedingungen erfüllt sein, damit ein Datensatz aus der Tabelle Bestellungen berücksichtigt wird.

In unserem Beispiel sollen alle Bestellungen des Kunden Schulte angezeigt werden, deren Umsatz größer oder gleich 300 € beträgt.

Geben Sie in der Zelle E5 folgende Formel ein:

=FILTER(Bestellungen;(Bestellungen[Kunde]=F1)*(Bestellungen[Umsatz]>=F2))

Das Tabellenblatt sieht nach den Formatierungen der Spalten Bestelldatum und Umsatz im Überlaufbereich folgendermaßen aus:

Funktion FILTER mit UND-Bedingung

Im Gegensatz zu der WENNS-Funktion geben Sie bei der Funktion FILTER die beiden Bedingungen nicht als zwei Argumente, sondern als ein Argument an. Achten Sie darauf, dass die beiden Bedingungen jeweils in runden Klammern stehen müssen und dass Sie mit dem Multiplikationszeichen * verbunden werden.

Erläuterung:

Wenn für einen Datensatz eine Bedingung erfüllt ist, wird in Excel intern der Wert 1 angesetzt. Ansonsten der Wert 0. Da bei einer UND-Bedingung alle angegebenen Bedingungen erfüllt sein müssen, ist die Bedingung nur wahr, wenn keine der Bedingungen falsch ist, d.h. den Wert 0 hat. Die Multiplikation von mehreren Zahlen ist immer 0, sobald ein Multiplikand den Wert 0 hat. Die Funktion FILTER berücksichtigt nur die Datensätze, bei denen die Bedingungsprüfung den Wert 1 bzw. einen Wert größer als 0 ergibt.

Die folgende Formulierung einer UND-Bedingung in der Funktion FILTER ist nicht möglich und führt zu einem Fehlerwert:

=FILTER(Bestellungen;UND(Bestellungen[Kunde]=F1;Bestellungen[Umsatz]>=F2)

Ein weiteres Beispiel für UND-Bedingungen ist die Berücksichtigung von Bestellungen eines bestimmten Zeitraums:

Geben Sie in der Zelle E5 folgende Formel ein:

=FILTER(Bestellungen;(Bestellungen[Bestelldatum]>=F1)*(Bestellungen[Bestelldatum]<=F2))

Das Tabellenblatt sieht dann folgendermaßen aus:

Funktion FILTER mit Zeitraumabfrage

Eine ODER-Bedingung

Da bei einer ODER-Bedingung nur eine der Bedingungen wahr sein muss, damit die gesamte Bedingung wahr wird, wird bei der FILTER-Funktion das mathematische +-Zeichen als Verbindungsoperator verwendet, d.h. wenn bereits eine Bedingung erfüllt ist und dafür der interne Wert 1 angesetzt wird, ergibt die Ergebnisprüfung einen Wert größer als 0 und der Datensatz wird berücksichtigt.

Als Beispiel sollen alle Bestellungen angezeigt werden, die entweder vom Kunden Schulte stammen oder bei denen der Umsatz größer oder gleich 300 € ist. Die Formel in Zelle E5 ist folgendermaßen aufgebaut:

=FILTER(Bestellungen;(Bestellungen[Kunde]=F1)+(Bestellungen[Umsatz]>=F2))

Im Tabellenblatt ist der Überlaufbereich größer geworden:

Funktion FILTER mit ODER-Bedingung

Kombinierte UND- und ODER-Bedingung

Mit der FILTER-Funktion können Sie auch UND- und ODER-Bedingungen kombinieren. Achten Sie dabei auf die richtigen Klammersetzungen.

In unserem Beispiel sollen alle Bestellungen der Kunden Schulte oder Müller angezeigt werden, deren Umsatz größer oder gleich 300 € ist:

Die Formel lautet:

=FILTER(Bestellungen;((Bestellungen[Kunde]=F1)+(Bestellungen[Kunde]=F2))*(Bestellungen[Umsatz]>=F3))

Das Tabellenblatt sieht dann so aus:

Funktion FILTER mit UND- und ODER-Bedingung

FILTER-Funktion mit anderen Array-Funktionen kombinieren

Die FILTER-Funktion können Sie gut mit anderen dynamischen Array-Funktionen kombinieren, wie Sie in den folgenden Beispielen sehen werden.

Im ersten Beispiel sollen einmalig alle Kunden angezeigt werden, die mindestens eine Bestellung größer oder gleich 300 € platziert haben. Die Formel lautet:

=EINDEUTIG(FILTER(Bestellungen[Kunde];Bestellungen[Umsatz]>=F1))

Es werden nur die Datensätze in der Spalte Kunde der Tabelle Bestellungen gefiltert. Mit der Funktion EINDEUTIG werden alle gefilterten Kundennamen nur einmal aufgelistet. Die Kundennamen erscheinen in der Reihenfolge, wie Sie in der Tabelle Bestellungen in der Spalte Kunde aufgeführt sind.

Wenn Sie diese Liste noch nach den Kundennamen aufsteigend sortieren möchten, geben Sie folgende Formel mit der dynamischen Array-Funktion SORTIEREN ein:

=SORTIEREN(EINDEUTIG(FILTER(Bestellungen[Kunde];Bestellungen[Umsatz]>=F1)))

Das Tabellenblatt sieht dann so aus:

Funktion FILTER kombiniert mit EINDEUTIG und SORTIEREN

Im zweiten Beispiel sollen alle Bestellungen aufgelistet werden, deren Umsatz größer oder gleich 300 € ist. Die Datensätze sollen nach dem Umsatz absteigend sortiert werden:

Die Formel lautet:

=SORTIEREN(FILTER(Bestellungen;Bestellungen[Umsatz]>=F1);3;-1)

Der durch die Funktion FILTER erzeugte Überlaufbereich wird nach der 3. Spalte, in der der Umsatz steht, absteigend sortiert. Dazu wurde der Ausdruck -1 im dritten Argument der Funktion SORTIEREN angegeben.

Im Tabellenblatt werden die Bestellungen so angezeigt:

Funktion FILTER und SORTIEREN

Hinweise

  • Wenn Sie nur die Spalten Kunde bis Bestelldatum aus der Tabelle Bestellungen im Überlaufbereich sehen möchten, können Sie in der Funktion FILTER für das Argument Matrix den folgenden Ausdruck angeben:

    Bestellungen[[Kunde]:[Bestelldatum]]
  • Es ist möglich, mit der Funktion FILTER für jede Spalte im Überlaufbereich einen Ausdruck anzugeben, falls kein Datensatz die geforderten Bedingungen erfüllt.

    =FILTER(Bestellungen;Bestellungen[Kunde]=F1;{“kein Kunde”.”kein Bestelldatum”.0})

    Als drittes Argument ist eine Matrix, die in geschweiften Klammern stehen muss, anzugeben. Die einzelnen Matrixelemente werden durch einen Punkt getrennt, weil sie in einer Zeile und verschiedenen Spalten stehen sollen. Die Anzahl der Elemente in der Matrix muss mit der Anzahl der Spalten im Überlaufbereich übereinstimmen.
Funktion FILTER mit Matrix als Argument Ween leer
Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

10 Kommentare

  1. Ich möchte gerne mehrere Filter in eine Formel aufnehmen. Wie kann ich diese Formel bauen, dass wenn z.B. nur in 3 von 4 Feldern eine Bedingung zum Filtern eingetragen wurde, dann aber die übrigen 3 Filter angewendet werden? Ist das möglich? In dem Beispiel hier wäre es z.B., wenn der Name Schulte nicht vergeben wird. In dem Moment kommt die Fehlermeldung #KALK! anstatt die übrigen Filter anzuwenden.

    Antworten
    • Interessante Frage.
      Ändern Sie Formel folgendermaßen:
      =FILTER(Bestellungen;(WENN(ISTLEER(F1);Bestellungen[Kunde]<>“”;Bestellungen[Kunde]=F24))*(Bestellungen[Umsatz]>=F2))

      Antworten
  2. Gibt es in älteren Excel-Versionen die Möglichkeit, über andere Funktionen das gleiche Ergebnis zu erzielen?

    Antworten
    • Ja. Die Formeln müssen Sie für jede Spalte als Matrixformel mit der Tastenkombination STTRG + SHIFT + ENTER eingeben. Markieren Sie vorher genügend Zellen in der Spalte mit dem Ergebnis. Für die Spalte Bestelldatum lautet die Formel folgendermaßen:
      =WENNFEHLER(INDEX(Bestellungen[Bestelldatum];AGGREGAT(15;7;ZEILE(Bestellungen[Kunde])-1/(Bestellungen[Kunde]=$F$1);ZEILE()-1));””)
      Der gesuchte Kunde steht im Feld F1 und das erste Bestelldatum soll in Zeile 2 erscheinen.

      Antworten
  3. Vielen Dank für diese Erklärung zur Arrayfunktion in Excel. Interessant, dass mit der Funktion FILTER mehrere Bedingungen eingegeben werden können. Wir haben seit kurzem technisch aufgerüstet im Unternehmen und überlegen, einen IT Service zu beauftragen.

    Antworten
  4. Ist es möglich mit der Funktion FILTER() das Teilergebnis eines Filters auszugeben? Also zum Beispiel nur die Summe aller Umsätze von dem Kunden “Lehmann” ausgeben, ohne das alle Bestellungen dieses Kunden angezeigt werden. Ich möchte mir im Prinzip in einer Zelle nur die Summe (hier des Umsatzes) eines Filters (hier z.B. der Kunde “Lehmann”) ausgeben lassen.

    Antworten
    • Hallo Herr Gilleßen,
      um die Summe für einen Kunden auszugeben, können Sie die Funktionen SUMMEWENN bei einem Kriterium und SUMMEWENNS bei mehreren Kriterien verwenden.
      Beispiel für Tabelle Bestellungen:
      =SUMMEWENN(Bestellungen[Kunde];”Lehmann”;Bestellungen[Umsatz])

      Antworten
  5. Vielen Dank! Heute habe ich gelernt, dass ISTFEHLER()=FALSCH ein logisches “RICHTIG” ergibt 🙂
    Eine kleine Erkenntnis – ein riesiger Motivationsschub. Ein schönes Wochenende nach Hattingen wünsche ich.

    Antworten
  6. Wie kann ich die Funktion FILTER() anwenden, wenn ich nach Text oder Textteilen in Datenbankspalten suche? Beispielsweise nach Regionen mittels Suchkriterium *falen*, um Westfalen und Nordwestfalen oder Südwestfalen abzudecken?

    Antworten
    • Hallo Herr Gebauer,

      wenn Sie im obigen Beispiel in der Zelle F1 den Ausdruck mann eingeben, können Sie mit der folgenden Formel alle Kunden suchen, deren Name das Wort mann enthält:
      =FILTER(Bestellungen;ISTFEHLER(SUCHEN(F1;Bestellungen[Kunde]))=FALSCH)
      Alle Kunden, die mit mann enden, können Sie folgendermaßen filtern:
      =FILTER(Bestellungen;RECHTS(Bestellungen[Kunde];LÄNGE(F1))=F1)

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This