Eine Funktion liefert alle Treffer 42

Eine neue Tabellenfunktion in Excel liefert ALLE gesuchten Werte
 

Vor etwa 4 Jahren hatte ich einen Artikel unter dem Titel „Besser als SVERWEIS: Alle Werte finden“ veröffentlicht. Damit habe ich offensichtlich einen Schmerzpunkt bei vielen Excel-Anwendern angesprochen, denn dieser Beitrag ist einer der beliebtesten und am meisten kommentierten überhaupt.

Die dort gezeigte Formel, um das Problem zu lösen, ist jedoch relativ kompliziert und für Otto-Normal-Excelanwender nur schwer durchschaubar. Mittlerweile sind 4 Jahre vergangen und Excel hat sich weiterentwickelt. Und bekanntlich führen viele Wege nach Rom. Grund genug also, in diesem und den folgenden Artikeln ein paar Alternativen aufzuzeigen.

Den Anfang macht eine geradezu unfassbar einfache Lösung. Worin genau das genannte Problem besteht und wie die heutige Lösung aussieht, erfährst du in diesem Beitrag.

Und so geht’s:

Das Problem

Wie jeder Anwender weiß, der sich mit dem SVERWEIS schon einmal auseinandersetzen durfte, lassen sich mit dieser Funktion zwei Tabellen gegeneinander abgleichen, die wenigstens ein gemeinsames Kriterium aufweisen. Hat man beispielsweise eine Liste mit den Lagerbeständen nach Artikelnummer, lassen sich dazu aus einer zweiten Liste die Verkaufspreise pro Artikel einlesen, indem über die Artikelnummer aus der ersten Liste die Preisliste durchsucht wird.

Die SVERWEIS-Funktion liefert also den gefundenen Verkaufspreis. Das funktioniert in dem beschriebenen Beispiel wunderbar, denn es gibt zu jeder Artikelnummer üblicherweise genau einen Verkaufspreis.

Wenn man aber Daten hat, für die es mehrere Treffer gibt, hilft der SVERWEIS nicht weiter, denn der kann immer nur den ersten gefundenen Wert ausgeben. Er scheitert also bei Fragestellungen wie:

  • Zeige mir aller Adressen zu PLZ 85465
  • Zeige mir alle Millionenstädte eines bestimmten Landes
  • Zeige mir alle Aufträge für den Artikel XYZ

Im eingangs erwähnten Beitrag Besser als SVERWEIS: Alle Werte finden hatte ich dafür ein echtes Formelmonster vorgestellt, welches die Funktionen INDEX, KGRÖSSTE, ZÄHLENWENN und ZEILE miteinander kombinierte und obendrein noch als Array-Funktion eingegeben werden musste (Strg+Umschalt+Eingabe). Aber die Formel funktioniert und schließlich zählt ja das Ergebnis.

Die Lösung im Jahr 2020

Excel bietet in der aktuellsten Version im Rahmen von Office 365 einige geniale neue Funktionen, die mittelfristig alles auf den Kopf stellen werden, was man bisher aus Formeln so kennt. Sogenannte dynamische Array-Funktionen. Einen kleinen Vorgeschmack darauf hatte ich in den Artikeln hier und hier schon einmal gegeben. Und jetzt kommt leider auch die schlechte Nachricht:

Die für die heutige Lösung vorgestellten Funktionen FILTER und SORTIEREN stehen dir heute (Stand April 2020) nur zur Verfügung, wenn du Office 365 im monatlichen Kanal nutzt. Ob das bei dir der Fall ist, siehst du im Menü Datei | Konto:

Welche Office-Version ist im Einsatz?

Welche Office-Version ist im Einsatz?

Anwender des halbjährlichen Kanals müssen sich voraussichtlich noch bis Juli gedulden, mehr dazu findest du auf dieser Seite von Microsoft: FILTER-Funktion

Alle Nicht-Office-365-Anwender kommen leider überhaupt nicht in den Genuss dieser Funktionen. In diesem Fall musst du entweder auf meine klassische Variante oder ein paar andere Alternativen zurückgreifen.

Ok, genug der langen Vorrede, wie sieht jetzt die neue Lösung aus?

Ich verwende das gleiche Beispiel wie in meinem ursprünglichen Artikel, um dir den Vorher-Nachher-Effekt besser demonstrieren zu können. Die Datei kannst du dir hier herunterladen. Ausgangspunkt ist eine Liste mit Ländern und den dazugehörigen Millionenstädten. Die Aufgabe ist es, zu einem angegebenen Land in einer Tabelle daneben sämtliche dazu passenden Städte aufzulisten:

Die Ausgangsliste

Die Ausgangsliste

Und mit dieser einfachen Formel löst du das Problem:
=FILTER(B2:C309;A2:A309=$F$1)

Lösung mit der FILTER-Funktion

Lösung mit der FILTER-Funktion

Ganz allgemein hat die FILTER-Funktion folgende Syntax:
=FILTER(Matrix;Einschließen;Wenn_leer)

„Matrix“ enthält den zu filternden Quellbereich.
„Einschließen“ gibt das Filterkriterium an. In meinem Bereich also der Vergleich von Spalte A mit dem Land in Zelle F1.
Mit dem letzten Parameter „Wenn_leer“ könnte man noch einen Wert definieren, der zurückgeliefert wird, wenn nichts gefunden wird. Also beispielsweise:

Optionaler Parameter

Optionaler Parameter

Ohne diesen optionalen Parameter gibt die Formel einen #KALK!-Fehler aus.

Das Besondere an der FILTER-Funktion ist, dass die Funktion automatisch mehrere Ergebnisse zurückliefern kann, denn es handelt sich um eine dynamische Array-Funktion. Alle bisher bekannten Funktionen, wie beispielsweise SUMME, SVERWEIS usw. können zwar mehrere Zellen als Eingabe verarbeiten, aber immer nur ein einzelnes Ergebnis ausgeben.

Die FILTER-Funktion hingegen wird nur in die jeweils erste Zelle eingegeben und läuft automatisch in alle anderen Zellen über, je nachdem, wie viel Platz benötigt wird. Man erkennt das an dem blauen Rahmen, der den Ausgabebereich umgibt, sobald sich die aktive Zelle innerhalb des Bereichs befindet:

Die Formel läuft über

Die Formel läuft über

Wenn man die Formel nachträglich anpassen möchte, ist dies auch nur in der jeweils ersten Zelle möglich. Alle anderen Zellen im Ausgabebereich sind gesperrt und die Formel wird dort leicht ausgegraut in der Bearbeitungszeile dargestellt:

Alle anderen Zellen sind unveränderbar

Alle anderen Zellen sind unveränderbar

Wichtig zu wissen: Wenn nicht genügend leere Zellen für die komplette Ausgabe zur Verfügung stehen, erscheint stattdessen ein #ÜBERLAUF!-Fehler:

#ÜBERLAUF!-Fehler bei Platzmangel

#ÜBERLAUF!-Fehler bei Platzmangel

Sobald das Hindernis jedoch beseitigt wurde, breitet sich die Formel automatisch wieder aus. Sehr genial!

  • Eine einzige, unfassbar simple Funktion, eingegeben in einer einzigen Zelle!
  • Kein verschachteltes Formelmonster!
  • Keine komplizierte Erfassung über Strg+Umschalt+Eingabe!
  • Dynamische Anpassung des Ausgabebereichs

Zum Vergleich:
Um die gleiche Aufgabe mit herkömmlichen Mitteln per Formel zu lösen, ist dieses Konstrukt notwendig:
{=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)));"")}

Lösung mit bisherigen Mitteln

Lösung mit bisherigen Mitteln

Einschließlich der damit verbundenen Nachteile:

  • Schwer nachvollziehbares Monster
  • Erfassung als Array-Formel mit Strg+Umschalt+Eingabe
  • Zusätzliche SVERWEIS-Funktion für die Ausgabe der dazugehörigen Einwohnerzahlen
  • Kopieren der Formel nach unten „auf Verdacht“ in so viele Zeilen, wie vermutlich maximal benötigt werden

Noch eine Schippe obendrauf

Der aufmerksame Leser wird bemerkt haben, dass meine Ausgangsliste nach Einwohnerzahl absteigend sortiert war. Dementsprechend wird auch die Ergebnisliste in der gleichen Weise sortiert ausgegeben.

Was jedoch, wenn du lieber eine alphabetische Sortierung nach Städtenamen hättest? Kein Problem, dafür gibt es die (ebenfalls neue) SORTIEREN-Funktion.

=SORTIEREN(Matrix;Sortierindex;Sortierreihenfolge;Nach_Spalte)
„Matrix“ ist der zu sortierende Tabellenbereich.
„Sortierindex“ ist eine Zahl, die die Zeilen- oder Spaltennummer angibt, nach der sortiert werden soll.
Im Parameter „Sortierreihenfolge“ steht 1 (oder keine Angabe) für aufsteigende, -1 für absteigende Reihenfolge.
Für den letzten, ebenfalls optionalen Parameter „Nach_Spalte“ gibt man FALSCH an, um nach Zeilen zu sortieren oder WAHR, wenn horizontal nach Spalten sortiert werden soll.

In meinem Beispiel mache ich es mir aber ganz einfach und verwende nur den ersten Parameter mit der Matrix, was ja wiederum die FILTER-Funktion ist:
=SORTIEREN(FILTER(B2:C309;A2:A309=$F$1))

Die Kombination von FILTER mit SORTIEREN

Die Kombination von FILTER mit SORTIEREN

Einfacher geht es wohl kaum, oder?

Fazit

Natürlich kann ich den Aufschrei vieler Leser schon hören und die zu erwartende Kritik ist berechtigt:
Ohne Office 365 sieht es schlecht aus. Abwärtskompatibilität zu anderen Excel-Versionen ist nicht gegeben und Probleme beim Datenaustausch sind damit vorprogrammiert.

Aber es gibt ja auch verschiedene Alternativen:

Die Entscheidung liegt bei dir!

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Moritz Antworten abbrechen

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

42 Gedanken zu “Eine Funktion liefert alle Treffer

  • Avatar-Foto
    Andi

    Hallo Martin,
    das ist eine weitere nette Funktion, die praktische Möglichkeiten eröffnet. Wie du schon schreibst, ist der eingeschlagene Weg von MS aber bedenklich aus Sicht der Anwender. Eine Fragementierung auf verschiedene Versionen bringt nur Ärger für alle, die Excel ein wenig mehr nutzen als für =Summe() und Autofilter. Ich warte schon drauf, wie es mit VBA weitergeht. Offline dürfte alles beim Alten sein, aber mit Excel Online fängt der Spass schon an. Ab und an dürften manche Entwickler (bzw diejenigen, die den Fahrplan für die Entwickler entwerfen) vergessen, dass es auch User gibt, die auf gewachsene Dinge angewiesen sind und die nicht ständig gut funktionierende Abläufe neu entwickeln können.

    • Avatar-Foto
      Martin Weiß

      Hallo Andi,

      ich gebe dir schon Recht, dass solche neuen Funktionen immer ein zweischneidiges Schwert sind, gerade am Anfang, wenn sie neu eingeführt werden. Trotzdem finde ich es wichtig, dass man sie zumindest kennenlernt. Aber im Einzelfall muss natürlich jeder selbst entscheiden, ob man sie nutzen kann/darf/möchte oder eben nicht.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Bruno Schlecht

    Hallo Martin,
    diese Filter-Funktion ist der Hammer und deiner Erklärung dazu auch, danke dafür.
    Wie sooft in solchen Situationen kommen bei gravierenden Neuerungen gleich neue Wünsche auf. Meine Tabelle umfasst > 3000 Einträge und die möchte ich nach 3 Kriterien filtern, also bräuchte ich die Funktion FILTERS mit mehr als einem Kriterium ähnlich der SummewennS-Funktion. Ist so etwas in der näheren Zukunft zu erwarten oder wie kann man diese Problem elegant lösen?
    Beste Grüße
    Bruno

    • Avatar-Foto
      Martin Weiß

      Hallo Bruno,

      da habe ich gute Nachrichten für dich, denn Filtern mit mehreren Kriterien ist jetzt schon möglich! Du musst nur die Kriterien mit + (entspricht einer Oder-Verknüpfung) oder * (entspricht einer Und-Verknüpfung) eingeben. Bezogen auf die Städteliste aus dem Artikel sieht das wie folgt aus.

      Alle Städte aus Brasilien oder aus Deutschland:
      =FILTER(B2:C309;(A2:A309=“Brasilien“)+(A2:A309=“Deutschland“))

      Alle Städte aus Brasilien mit mehr als 2.000.000 Einwohnern:
      =FILTER(B2:C309;(A2:A309=$F$1)*(C2:C309>2000000))

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Bruno Schlecht

      Hallo Martin,
      Sorry, man sollte erst mal alles genau lesen und testen bevor man was sagt, oder schreibt.
      Die FILTER-Funktion kann natürlich mehrere Kriterien gleichzeitig bearbeiten:
      =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);““). Dazu müssen die einzelnen Filter in Klammer gesetzt und mit „*“ verkettet werden.
      Ich brauche also kein „FILTERS“. Damit erfüllt die Funktion alles was ich brauche und löst mein Problem auf elegante Weise: Cool.
      Beste Grüße
      Bruno

  • Avatar-Foto
    Martin

    Vielen Herzlich Dank
    für die Beschreibung der Filter Funktion das hat mir wahnsinnig weitergeholfen bei meinen Office365 Excel geht es zwar nicht aber in der Anwendung für iche es gesucht habe Google Tabellen funktioniert es super

    • Avatar-Foto
      Martin Weiß

      Hallo Martin,

      es irritiert mich zwar jetzt, dass es bei Dir mit Office 365 nicht funktioniert. Denn gerade dort ist diese Funktion ja überhaupt erst verfügbar. Aber wenn es mit Google Tabellen auch klappt, soll es mir Recht sein 😉

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Denise Burmann

    Lieber Martin
    Du hast hier eine wirklich informative und hilfreiche Seite aufgebaut. Ich komme immer mehr auf den Geschmack von Excel. Nur beisse ich mir gerade die Zähne aus.
    Ich bin ziemlich sicher, dass du eine gute Idee aus dem Hut zaubern kannst.
    In Tabelle2 sind folgende Daten:
    Spalte A Spalte B
    1.1 Text1.1
    1.2 Text1.2
    2.1 Text2.1
    2.2 Text2.2
    In Tabelle1 habe ich in Spalte B5:B70 eine Liste mit den Nummern der Spalte A der Tabelle2 erstellt. Wenn der Nutzer nun in Tabelle1 die Nummer 1.1 auswählt, soll auf der derselben Zeile in Spalte D der Text1.1 automatisch eingefügt werden. Auch sollten die Nutzer in der Tabelle1 bei Bedarf Zeilen hinzufügen können ohne dass die Formel „zerstört“ wird. Hast du eine Idee? Vielen Dank und
    herzlicher Gruss aus der Schweiz
    Denise

    • Avatar-Foto
      Martin Weiß

      Hallo Denise,

      ich glaube, ich weiß was du meinst. Aber diese Variante ist zum Scheitern verurteilt und ich würde dringend von einer solchen zeilenabhängigen Lösung abraten. Denn nur eine unbedacht gelöschte Zeile lässt alles zusammenfallen wie ein Kartenhaus. Verwende stattdessen besser einen SVERWEIS oder etwas in der Art, das ist bedeutend zuverlässiger.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Volker Kestler

    Hallo
    Martin finde deine Internetseite super
    Habe einfache Frage hoffe ich 🙂
    habe zwei Spalten Wechselweise stehen dort größere und kleinere Wert –> Auswahl immer den kleineren Wert nehmen von Spalten A und Spalte B als Ergebnis in Spalte C
    Beispiel
    Spalte A / Spalte B / Spalte C –> Ergebnis
    3,3 4 –> 3,3
    6 5,2 –> 5,2
    Es wäre nett wenn es hierfür ein Lösung gibt. Vielleicht Denke ich zu kompliziert

    • Avatar-Foto
      Martin Weiß

      Hallo Volker,

      das Problem lässt sich mit einer WENN-Funktion lösen:
      =WENN(A2>B2;A2;B2)

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Sue

        Hallo Martin,
        mein erster Gedanke war: da nimmt man ganz simpel
        C1 = MIN(A1;B1).
        Bei Deiner Lösung komme ich jetzt echt ins Grübeln… Wer von uns hat die Frage nicht verstanden?;-)

        Bei der Variante mit WENN müsste im ersten Parameter der Vergleichsoperator umgedreht werden, alternativ könnte man die then- und else-Parameter vertauschen:
        However: Es führen immner viele Wege nach Rom und manchmal ist der gerade Weg stolperfreier.

        Gruß
        Sue

        • Avatar-Foto
          Martin Weiß

          Hallo Sue,

          du hast natürlich vollkommen Recht: die Variante mit der MIN-Funktion ist wirklich einfacher und direkter, genau dafür ist sie ja eigentlich da 🙂
          Manchmal sehe ich den Wald auch vor lauter Bäumen nicht…

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Arno

    Hallo Martin,
    hierzu eine Frage:
    das Land „Brasilien“ muss genau richtig geschrieben werden.
    Aber kann ich die Formel auch irgendwie verändern, damit ich Platzhalterzeichen verwenden kann?
    also das ich nur noch bei Land: „Bras*“ einzugeben brauche ?

    • Avatar-Foto
      Martin Weiß

      Hallo Arno,

      eine sehr interessante Frage. Standardmäßig kann die FILTER-Funktion so etwas nicht, aber in Kombination mit ein paar anderen geht es dann doch:
      =FILTER(B2:C309;ISTZAHL(SUCHEN(F1;A2:A309)))

      Vielleicht schreibe ich da nochmal einen Artikel dazu…

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Michele Jerabek

    Hallo lieber Martin,
    ich schließe mich den vielen vielen Positiven Kommentaren bezüglich dieses Themas mehr als nur gerne an.
    Du erklärst wirklich toll, auch wenn ich nicht alles sofort verstehe – teils benötige ich noch etwas Zeit.
    Wie dem auch sei möchte ich Dir meinen absoluten Dank aussprechen für Deine tolle Seite.
    Ganz herzliche Grüße
    Michele

    • Avatar-Foto
      Martin Weiß

      Hallo Michele,

      Ihr seid alle so nett zu mir 🙂
      Vielen Dank also für Deinen lieben Kommentar und es freut mich immer zu hören, wenn ich mit meinen Artikeln weiterhelfen kann.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Moritz

    Hallo Martin,

    auch ich schließe mich den tollen Kommentaren zu deiner Arbeit hier absolut an!
    Deine Seite hat mir in Excel-Fragestellung schon mehrfach geholfen und was soll ich sagen die FILTER Funktion ist echt ein großer Fortschritt für Normalo-User, die vor dem Computer sitzen, so wie ich.

    Eine Frage hätte ich aber auch noch.
    Wie kann ich auf „einfache“ Art und Weise über die FILTER Funktion die Werte Rückgabe in Spalten auf nur ausgewählte Spalten festsetzen. Ich schaffe es nicht über eine Matrix Klammer wie zB: wenn eine Tabelle A-H Spalten hätte, nur einzelne Spalten als Ergebnis zum Suchkriterium auszugeben:

    =FILTER((A1:B10;D1:F10);A2:A309=$J$1) -> Fehler: #Bezug -> welche Operatoren müsste ich für die Matrix-Ausgabe verwenden, damit das funktioniert? Ich könnte zwar eine zweite Filterfunktion in einer anderen Zelle mit neuer Matrix ergänzen, aber das finde ich eher kontra produktiv. Vielleicht ein Tipp?, wahrscheinlich habe ich nur ein Brett vor dem Kopf und die einfache Lösung liegt irgendwie direkt vor mir.

    Beste Grüße
    Moritz

    • Avatar-Foto
      Martin Weiß

      Hallo Moritz,

      erst einmal dankeschön für das tolle Feedback! Und nein, du hast kein Brett vor dem Kopf. Die FILTER-Funktion sieht nicht vor, dass nicht zusammenhängende Spalten ausgegeben werden. Es gibt trotzdem eine Lösung dafür, aber die ist nicht ganz trivial und die werde ich in einem der nächsten Artikel vorstellen. Daher muss ich dich hier leider noch ein wenig vertrösten.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Moritz

        Hi Martin,

        besten Dank, dann bin ich mal gespannt auf den Artikel und behelfe mir erstmal mit einem Workaround im Sinne, das ich erstmal eine Hilfstabelle aus der Mastertabelle erstelle und nur auf diese Filtere. Es gibt ja noch ein Workaround in dem Ich mehrere Pivots erstelle und dann in einer weiteren Tabelle mit nur auf die Zellen der Pivots beziehe, wodurch auch Aktualisierungen funktionieren.

        Beste Grüße
        Moritz

  • Avatar-Foto
    Konni

    Hallo Martin,
    die Formel ist eigentlich perfekt für das was ich aus meiner Tabelle auswerten will. Das hat mir schon sehr weitergeholfen.
    Jetzt habe ich nur das Problem, dass ich die Werte gerne hintereinander, anstatt untereinander angezeigt bekommen möchte. Gibt es da eine Möglichkeit das noch auf irgendeine Weise einzubauen?
    Ich weiß, dass das bei der „Mega-Formel“ aus dem Original Artikel funktioniert, hatte aber gehofft auf so eine lange Formel verzichten zu können.

    Grüße
    Konni

    • Avatar-Foto
      Martin Weiß

      Hallo Konni,

      wenn du die Formel einfach noch mit der MTRANS-Funktion umschließt, dann erfolgt die Ausgabe horizontal.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Patrick M.

    Hallo Martin,
    Kann ich auch die Ausgaberichtung ändern? Ich hätte die Ergebnisse gerne in einer Zeile geschrieben.
    MfG Patrick

    • Avatar-Foto
      Martin Weiß

      Hallo Patrick,

      ja, das geht. Du musst die Formel nur mit der MTRANS-Funktion umschließen. Für die gefilterte und sortierte Liste aus dem Beispiel sähe das so aus:
      =MTRANS(SORTIEREN(FILTER(B2:C309;A2:A309=$F$1)))

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stefan

    Hallo Martin

    Du hast mir mit der Filterfunktion eine Lösung aufgezeigt, nachder ich lange gesucht habe. Zudem wurde noch das Office 365 installiert. Genial. Vielen Dank.

    Nun habe ich folgendes Problem: wenn ich den exakten Suchbereiche von Zelle A2:T3000 angebe, wie auch den exakten Vergleichsbereich (zB W2:w3000), erhalte ich die gewünschten Rückgabeergebnisse. Da ich eine Vorlage für die monatlichen Auswertungen erstellen möchte, würde ich jeweils nur die Spaltenbereiche angeben wollen (zB A:T bzw. W:W), da die Datenbasis und somit die Anzahl Zeilen mit den Daten monatlich unterschiedlich sein werden. Mit den Spaltenbereich eerhalte ich nur #Werte.

    Für jeglichen Tipp bin ich sehr dankbar.
    Beste Grüsse
    Stefan

    • Avatar-Foto
      Martin Weiß

      Hallo Stefan,

      grundsätzlich sollte die FILTER-Funktion auch mit kompletten Spalten zurechtkommen, ich habe das bei mir auch gerade nochmal erfolgreich getestet. Die Fehlermeldung scheint also eine andere Ursache zu haben, für die ich im Moment aber auch keine Erklärung habe.

      Unabhängig davon rate ich von der Verwendung von kompletten Spaltenbezügen in Formeln aber generell ab, da sich dies unter Umständen stark auf die Performance auswirken kann.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Stefan

        Hallo Martin

        Vielen Dank für deine Antwort. Ich entschuldige mich, dass ich mich nicht früher gemeldet habe. Ich war längere Zeit abwesend.

        Ich verstehe es sehr gut, dass ein Bereichsbezug nur auf ganze Zeilen bzw. Spalten zu viele Daten für Berechnungen wie auch Filterfunktionen eine Performanceproblematik sein könnte. Ich habe es natürlich auch mit einer Eingrenzung gemacht. Wenn ich den Zeilenbereich in der Vorlage (ohne Daten) z.B. auf 3000 berücksichtige und ich dann z.B 2000 Datensätze einfülle, erkennt die Filterfunktion den mit Daten gefüllten Bereich (2000 Zeilen) nicht. Die nach dem Einfüllen der 2000 Datenzeilen angepasste Filterfunktion eben mit diesem 2000-Zeilenbereich wird der neudefinierte Bereich korrekt erkannt. Also ich bin immer noch auf der Suche, wie ich in einer Vorlage (ohne Daten) einen noch unbekannten Bereich definieren kann, so dass mir nach dem Einfüllen der Daten doch alle Datenzeilen erfasst werden (deshalb die Überlegung nach den undefinierten Spaltenbereiche wie z.B für den Filter A:T bzw. für den Vergleichsbereich z.B. die Spalte W:W).

        Für einen weiteren Tipp bedanke ich mich sehr.
        Viele Grüsse
        Stefan

        • Avatar-Foto
          Martin Weiß

          Hallo Stefan,

          ich kann das beschriebene Problem leider immer noch nicht nachvollziehen. Bei mir funktioniert es einwandfrei, wenn ich eine FILTER-Formel auf einen zunächst leeren Bereich erstelle und diesen Bereich später mit Daten fülle. FILTER liefert dann ohne Probleme die relevanten Daten. Daher kann ich dir im Moment beim besten Willen keinen Tipp geben.

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Stefan

            Besten Dank, Martin. Ich suche und probiere es weiter… auf jeden Fall hast du mir mit der Filterfunktion sehr geholfen.

            Viele Grüsse
            Stefan

  • Avatar-Foto
    Houdap

    du bist der King. Ich suche seit einer Woche so eine Funktion. Sogar in meinem Synology Office ist diese Funktion drin, danke danke danke

  • Avatar-Foto
    Andreas R.

    Die Filterfunktion ist einfach nur genial !
    DANKE für diesen grandiosen Tip ! Eine riesige Erleichterung für meine komplexen Abfragen mehrerer Tabellen.
    (Versuche auch gerade den Einstieg in PowerBI. Ebenfalls ein mächtiges Tool, scheint mir.)
    Grüsse

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      gern geschehen. Ja, FILTER und die vielen anderen neuen dynamischen Array-Funktionen in M365 sind wirklich eine geniale Sache!

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stephan

    Hi,
    danke für die super Anleitung. Ich nutze die Filter gerne um Arbeitspakete zuzuordnen. Es gibt ein Masterblatt und die Ergebnisse auf den Filtern wird auf weitere Blätter ausgegeben.
    Ich habe jedoch ein Problem wenn ich der Quelle eine leere Zelle ist, dann wird im Filter „0“ angezeigt. Wenn kein Datum vorhanden ist wird „00.01.1900“ angezeigt. Kann man das unterbinden?

    • Avatar-Foto
      Martin Weiß

      Hallo Stephan,

      du könntest das Ganze in eine WENN-Funktion packen. Also etwa in der Art:
      =WENN(FILTER(B2:C309;A2:A309=$F$1)=0;““;FILTER(B2:C309;A2:A309=$F$1))

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Jo

    Wie kann ich das Ergebnis einer FILTER-Funktion „kürzen“ bzw. nur die ersten N Zeilen erhalten?

    Ich benutze die FILTER-Funktion um eine große Tabelle zu filtern und erhalte eine ebenfalls große Tabelle, von der ich aber nur z. B. die ersten 100 Zeilen anzeigen möchte.

    Auf jeden Fall hier auch ein großes Lob und Vielen Dank für die erstklassigen Erläuterungen zur FILTER-Funktion!

    • Avatar-Foto
      Martin Weiß

      Hallo Jo,

      du musst nur um die FILTER-Funktion noch die ÜBERNEHMEN-Funktion herumlegen:
      =ÜBERNEHMEN(FILTER(…..);100)

      Damit werden genau die ersten 100 Datensätze aus dem FILTER-Ergebnis ausgegeben.

      Schöne Grüße und Danke für das Lob!
      Martin

  • Avatar-Foto
    Irene

    Die Filter-Funktion ist genial! Danke für die nachvollziehbare Erläuterung.
    Ich habe aber nun eine spezielle Aufgabe zu lösen und bekomme das mit FILTER noch nicht gelöst.
    Ich möchte nämlich gerne alle Spalten (ab B) einer Tabelle nach dem gleichen Begriff durchsuchen und nicht nur eine oder zwei. Da es eine größere Tabelle ist, ist die Formel dann extrem lang, wenn ich jede Spalte mit + in die Formel einfüge.
    Etwa so sieht es momentan aus (also nur Spalte C und E werden nach Begriff O1 durchsucht):
    =FILTER(A1:E23;(C1:C3=$O$1)+(E1:E23=$O$1))
    Weiters soll bei der Ergebnistabelle die erste Spalte (A) immer angezeigt werden, bei den übrigen alles außer dem gesuchten Begriff aber ausgeblendet werden. Zweiteres lässt sich ja mit WENN Bedingung lösen, aber dann wird ja auch die erste Spalte ausgeblendet.
    Hättest du – oder jemand anderer hier einen Rat?
    Vielen Dank schon einmal!

    • Avatar-Foto
      Martin Weiß

      Hallo Irene,

      zunächst einmal ist es wirklich so: Wenn du sehr viele Spalten zum Prüfen hast, dann wird es eine sehr lange Formel genau in dem Stil, den du hier als Beispiel verwendest. Daran lässt sich leider nichts ändern.
      Spalten dynamisch ein- und auszublenden abhängig davon, ob in der jeweiligen Spalte ein Treffer vorhanden ist, das wird wohl sehr kniffelig. Dafür habe ich spontan auch keine elegante Lösung. Ich würde daher vermutlich mit ein paar Hilfsberechnungen arbeiten:
      Oberhalb der zu durchsuchenden Tabelle würde ich für jede Spalte mit ZÄHLENWENN berechnen, wie oft der gesuchte Wert in der Spalte enthalten wird. Wenn der Wert größer als Null ist, dann soll die Spaltennummer ausgegeben werden, ansonsten Null:
      =WENN(ZÄHLENWENN(B3:B24;$O$2)>0;SPALTE();0)
      Über der Spalte A würde ich den Wert 1 eintragen. Diese erste Zeile mit den Spaltennummern würde ich dann über eine separate FILTER-Funktion auswerten und eine Liste aller Werte ungleich Null erzeugen:
      Zum Beispiel in Zelle P1: =FILTER(A1:E1;A1:E1>0)
      Diese Liste liefert mir also die benötigten Spaltennummern.
      Und dann könnte man deine ursprüngliche FILTER-Funktion mit der Funktion SPALTENWAHL umschließen und auf die gerade erstellte Liste der Spaltennummern verweisen:
      =SPALTENWAHL(FILTER(A2:E24;(C2:C24=$O$2)+(E2:E24=$O$2));P1#)

      Das ist nicht gerade sehr elegant, käme dem gewünschten Ergebnis aber vermutlich ziemlich nahe.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Fiona

    Hallo,

    kann man hier irgendwie einen Screenshot anhängen? Denn ich habe eine Frage, kann die aber ohne Screenshot als Grundlage schwer erklären.