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.
- Das Original: Besser als SVERWEIS: Alle Werte finden
- Alternative 1: Die FILTER-Funktion – davon handelt der heutige Artikel
- Alternative 2: Der eingebaute Spezialfilter
- Alternative 3: Eine Pivot-Tabelle
- Alternative 4: Power Query
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?
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
Und mit dieser einfachen Formel löst du das Problem:
=FILTER(B2:C309;A2:A309=$F$1)
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
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
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
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
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
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
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:
- Das Original: Besser als SVERWEIS: Alle Werte finden
- Alternative 1: Die FILTER-Funktion (nur Office 365) – das war der heutige Artikel
- Alternative 2: Der eingebaute Spezialfilter
- Alternative 3: Eine Pivot-Tabelle
- Alternative 4: Power Query
Die Entscheidung liegt bei dir!
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.
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.
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
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
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
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
Danke Martin, da haben sich unsere Antworten wohl gekreuzt.
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
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
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
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
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
Hallo Volker,
das Problem lässt sich mit einer WENN-Funktion lösen:
=WENN(A2>B2;A2;B2)
Schöne Grüße,
Martin
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
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
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 ?
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
Hallo Martin,
mega tolle Seite hier, ich hab es jetzt hinbekommen nach nur einem Wort in der Zelle zu suchen, allerdings würde ich gerne zusätzlich nach mehreren Wörtern suchen und ich bekomme es leider nicht hin.
Gibt es hierzu eine Lösungsmöglichkeit?
=FILTER(Tabelle6;ISTZAHL(SUCHEN(„Land“;Tabelle6[description]));Wenn_leer)
Es wird in der Tabelle das Wort Land gesucht (Wenn in der Zelle steht “ ein großes Land“ findet es diese.
Nun möchte ich aber auch noch die Zellen mit „Welt“ finden und auch diese Tabellenzeilen ausgeben.
Danke schonmal.
Liebe Grüße
Ines
Hallo Ines,
mehrere verschiedene alternative Kriterien lassen sich mit einem Pluszeichen verwenden. Beispiel:
=FILTER(Tabelle6;ISTZAHL(SUCHEN(„Land“;Tabelle6[description]))+ISTZAHL(SUCHEN(„Welt“;Tabelle6[description]));“–„)
Das + ist ein logisches ODER. Also entweder „Land“ oder „Welt“
Mit einem * erreicht man ein logisches UND. Das heißt, es müssen dann immer alle Bedingungen erfüllt sein.
Schöne Grüße,
Martin
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
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
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
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
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
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
Hallo Konni,
wenn du die Formel einfach noch mit der MTRANS-Funktion umschließt, dann erfolgt die Ausgabe horizontal.
Schöne Grüße,
Martin
Hallo Martin,
Kann ich auch die Ausgaberichtung ändern? Ich hätte die Ergebnisse gerne in einer Zeile geschrieben.
MfG Patrick
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
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
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
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
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
Besten Dank, Martin. Ich suche und probiere es weiter… auf jeden Fall hast du mir mit der Filterfunktion sehr geholfen.
Viele Grüsse
Stefan
du bist der King. Ich suche seit einer Woche so eine Funktion. Sogar in meinem Synology Office ist diese Funktion drin, danke danke danke
Dankeschön, freut mich, wenn der Artikel weitergeholfen hat.
Schöne Grüße,
Martin
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
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
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?
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
Perfekt! Das hat geholfen. Vielen Dank!
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!
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
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!
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
Hallo,
kann man hier irgendwie einen Screenshot anhängen? Denn ich habe eine Frage, kann die aber ohne Screenshot als Grundlage schwer erklären.
Hallo Fiona,
nein, direkt lässt sich hier kein Screenshot anhängen. Du könntest ihn höchstens auf einem Online-Speicher veröffentlichen und hier nur den Link dazu einfügen.
Schöne Grüße,
Martin
Hallo Martin,
ok, danke für die Info. Habe es jetzt mit dem Link hinbekommen.
https://www.dropbox.com/scl/fi/q0mzuvg5hioz8qr8prg2q/Bild-1.png?rlkey=rn2inv1zsx9yvyv8d7y6s2kb5&dl=0
Ich habe so eine Datei erhalten. Das liest man folgendermaßen, in der linken Spalte ist Produktionsartikel und in der rechten Komponenten, die bei der Herstellung benötigt werden. Das heißt bspw. Röstkaffee Vierjahreszeiten braucht die vier rechts genannten Sorten Rohkaffee. So wie diese Tabelle gemacht ist, kann ich die jedoch nicht gebrauchen. Bräuchte für jeden Produktionsartikel nur eine Zeile, links müsste der Name des Produktionsartikels stehen (wie jetzt) und rechts davon jede Komponente in einer Spalte, das heißt Spalte C Komponente 1, Spalte D Menge dieser Komponente (Menge steht bei mir derzeit in der Spalte E, das zeigt der Screenshot nicht), Spalte E Komponente 2, Spalte F Menge dieser Komponente usw. Hast Du eine Idee, ob man das in Excel irgendwie mithilfe von Formeln erreichen kann, diese Tabelle so umzubauen? Manuell kann ich das nicht machen, denn es sind über 4000 Zeilen drin.
Hallo Fiona,
das wird mit Formeln ziemlich schwierig bis unmöglich. Was deiner Anforderung vielleicht am nächsten käme, wäre eine Pivot-Tabelle. Die könnte dann beispielsweise so aussehen:
Sie hat dann allerdings so viele Spalten, wie es unterschiedliche Stücklistenkomponenten gibt. Aber vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo Martin!
Ich suche nach einer Möglichkeit die gefilterten Werte als Summe in einer Zelle widerzugeben. SVERWEIS gibt ja immer nur den ersten Wert aus, mit der Filter-Funktion kriegt man jetzt schon alle Werte, aber ich bräuchte alle Werte addiert. Hast du da eventuell einen schönen „Zauberspruch“ für mich? 🙂
Hallo Mara,
der Zauberspruch ist ganz einfach: Du musst nur die SUMME-Funktion um die FILTER-Funktion herumpacken und hast dann das gewünschte Ergebnis.
=SUMME(FILTER(…))
Das funktioniert natürlich nur, wenn die Filterfunktion nur Werte zurückliefert, und keine anderen Infos.
Schöne Grüße,
Martin