Liste dynamisch nach Zahlenbereich filtern

Die Datensätze einer Tabelle sollen auf Basis ihrer Zahlenwerte dynamisch an eine andere Stelle gefiltert aufgelistet werden.
Dabei wird ein Wert vorgegeben und dann noch ein +/-Prozentbereich, um den die Werte abweichen dürfen.

Wie immer kein Problem für AGGREGAT! AGGREGAT steht seit Excel 2010 zur Verfügung und bietet noch mehr Möglichkeiten als die Sammelfunktion TEILERGEBNIS.

Hier verwende ich den Parameter 15, um den kleinsten Zeilenwert zu ermitteln, der gleichzeitig größer oder gleich meinem von-Kriterium in Zelle I2 ist und gleichzeitig kleiner oder gleich dem Wert in J2. Wenn beides zutrifft, sind beide Bedingungen WAHR und ergeben multipliziert den Wert 1. Ist ein Wert nicht WAHR, wird mit FALSCH multipliziert und es resultiert der Wert Null. Somit wird durch Null dividiert, was einen Fehler ergibt und somit wird diese Zeile von der Betrachtung ausgeschlossen.

Formel aus Zelle E5:

=WENNFEHLER(INDEX(tab_Artikel[Artikelnummer];AGGREGAT(15;3;ZEILE(tab_Artikel[Menge])/((tab_Artikel[Menge]>=$I$2)*(tab_Artikel[Menge]<=$J$2));ZEILE()-4)-1);"")

Die Formel wird dann nach unten kopiert und sucht den k-kleinsten Wert. Dies wird durch den hinteren Teil ZEILE()-4 errechnet. Da die Formel in Zeile 5 steht, wird dort der 5-4 (also 1) -kleinste Werte gesucht. Zwei Zeilen tiefer dann also der 7-4 (also 3) der drittkleinste Wert und so weiter. Diese Werte lasse ich dann mit INDEX aus der Spalte Artikelnummer auslesen.

Gefolgt wird in F6 mit einem SVERWEIS, um auch den Artikelnamen und in G6 die Menge auszulesen:

=WENNFEHLER(SVERWEIS(E5;tab_Artikel;2;FALSCH);"")

Weiter oben wird gezählt, wie oft denn ein Artikel mit der gewünschten Menge E2 in der Liste vorkommt:

=ZÄHLENWENNS(tab_Artikel[Menge];">="&E2*(1-H2);tab_Artikel[Menge];"<="&E2*(1+H2))

Viel Spaß beim Nachbasteln und beste Grüße

Andreas

 

 

Download der Datei:

Excel 867 Liste filtern

Hier geht es zum Video Excel 867:

Videolink: https://youtu.be/_MZCFmK299k

Und zum Vorgängervideo Excel 359, in dem Texte aus der Artikelnummer gesucht werden:

Videolink: https://www.youtube.com/watch?v=L99bbA8W0sU