Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1468to1472
1468to1472
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

kleinster Wert bei Autofilter

Forumthread: kleinster Wert bei Autofilter

kleinster Wert bei Autofilter
18.01.2016 10:13:19
Sebastian
Hallo,
ich habe eine Tabelle, welche über den gesetzten Autofilter entsprechend Werte anzeigt. Eine Spalte beinhaltet Jahreszahlen, für die ich den kleinsten Wert ermitteln möchte. Dazu habe ich nun folgende Formel eingesetzt, welche auch super funktioniert.
=TEILERGEBNIS(105;N6:N60000)
Nun möchte ich aber, dass im Ergebnis der Wert nicht nur für alle durch den Filter ausgewählten Jahre ermittelt wird, sondern nur für Werte, welche zusätzlich noch in einem von mir definierten Bereich liegen.
Angenommen der gesetzte Autofilter (in einer anderen Spalte!) ergibt Werte zwischen 1999 und 2015 (nicht zwangsläufig jedes Jahr vorkommend), soll nun der kleinste Wert für diese Auswahl zwischen 2005 und 2010 ermittelt werden. Da z.B. 2005 in der gesetzten Autofilterauswahl nicht vorkommt, sollte dann 2006 als kleinster Wert ermittelt auftreten.
Folgende Formel wurde mir schon als Lösungsvorschlag angeboten, aber die funktioniert nicht richtig. Der Autofilter wird dabei nicht berücksichtigt.
=AGGREGAT(15;3;N6:N60000/(N6:N60000&lt=A3)/(N6:N60000&gt0);1)
Ich hoffe, ich konnte das Problem halbwegs deutlich formulieren ;-)
Vielleicht hat jemand eine Idee.
Besten Dank schon mal im Voraus.
Grüße Sebastian

Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: kleinster Wert bei Autofilter
18.01.2016 10:45:55
ingoG
Hallo Sebastian,
versuch es mal mit folgender Matrixformel:
=MIN(WENN(TEILERGEBNIS(3;INDIREKT("N"&ZEILE(6:60000)));N6:N60000;9^99))
(Eingabe abschließen mit Strg-Shift-Enter)
Gruß Ingo
PS eine Rückmeldung wäre nett...

PS...
18.01.2016 10:52:07
ingoG
die Formel funktioniert auch ohne Konstante:
=MIN(WENN(TEILERGEBNIS(3;INDIREKT("N"&ZEILE(6:60000)));N6:N60000))
(Eingabe abschließen mit Strg-Shift-Enter)

Anzeige
AW: PS...
18.01.2016 11:07:38
Sebastian
Hallo Ingo,
erst mal danke für deine Rückmeldung. Deine Formel funktioniert erst mal soweit. Allerdings möchte ich eben gern noch einen konstanten Jahresbereich mit als Bedingung formulieren. Also z.B. als Bereich von 1999 bis 2005. Den Autofilter setze ich dann natürlich nicht mehr bei Spalte N (Jahreszahlen), sondern nur noch bei allen anderen Kriterien.
Gruß Sebastian

Anzeige
AW: PS...
18.01.2016 12:56:12
Daniel
Hi
ich nehme für sowas immer eine Hilfsspalte mit folgender Formel (Formel für Zeile 2) =Teilergebnis(103;A2)
der zweite Parameter sollte auf eine Zelle in der gleichen Zeile verweisen, welche auf jeden Fall einen Wert hat.
das Ergebnis dieser Formel ist 1, wenn die Zeile sichtbar ist und 0, wenn sie ausgeblendet ist.
diese Hilfsspalte kannst du dann einfach in deine Formeln mit Einbinden, sei es als zusätzliche Bediungng bei SummeWenn/ZählenWenn oder sonst wie.
Gruß Daniel

Anzeige
AW: PS...
18.01.2016 13:52:59
Sebastian
Hallo Daniel,
danke für die Antwort. Leider komme ich mit deiner Erklärung nicht so richtig klar. Kannst Du mal genau erläutern was das bringen soll bzw. wie das funktioniert?
Gruß Sebastian

AW: PS...
18.01.2016 14:05:08
Daniel
Hi
hier mal ein Beispiel für so eine Auswertung.
Angezeigt wird die Summme für eine Artikelnummer im über den angezeigten Bereich, dh die Summe ändert sich, wenn du zusätzlich über den autofilter nach dem Verkäufernamen filterst.
https://www.herber.de/bbs/user/102858.xlsx
Gruß Daniel

Anzeige
AW: AGGREGAT() hier möglich, doch ...
18.01.2016 16:06:45
...
Hallo Sebastian,
... der Dir die AGGREGAT()-Formel aufgezeigt hatte, wusste entweder nicht, dass Du den Bereich zusätzlich filtern willst und dabei die ausgefilterten Werte auch nicht berücksichtigt haben möchtest oder er hat nicht beachtet, dass AGGREGAT() in der hier vorliegenden Matrixversion nicht die ausgeblendeten und/oder ausgefilterten Werte berücksichtigt.
Das ist derzeit leider ein Manko der AGGREGAT()-Funktion in dieser spez. Anwendungsform.
Doch in einer Kombination mit TEILERGEBNIS() und INDIREKT() kann AGGREGAT() das gewünschte Ergebnis ermitteln.
Und zwar filtere mal nach "x" in Spalte M in folgendem kleinem Beispiel:
 ALMN
201.01.1995   
331.12.2010  09.09.1995
4    
5    
6  TextDatum
7  a09.09.1987
8  a09.09.1989
9  x09.09.1991
10  x09.09.1993
11  a09.09.1995
12  a09.09.1997
13  a09.09.1999
14  a09.09.2001
15  x09.09.2003
16  x09.09.2005
17  a09.09.2007
18  x09.09.2009
19  x09.09.2011
20  x09.09.2013
21  a09.09.2015
22    

Formeln der Tabelle
ZelleFormel
N3=AGGREGAT(15;6;N6:N6000/(TEILERGEBNIS(105;INDIREKT("N"&ZEILE(N6:N6000)))*(N6:N6000<=A3)*(N6:N6000>=A2)>0); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
Anzeige

Infobox / Tutorial

Kleinster Wert bei Autofilter ermitteln

Schritt-für-Schritt-Anleitung

Um den kleinsten Wert aus einer gefilterten Liste in Excel zu ermitteln, kannst du folgende Schritte befolgen:

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in einer Tabelle mit einem gesetzten Autofilter organisiert sind.
  2. Hilfsspalte einfügen: Füge eine Hilfsspalte hinzu, die angibt, ob eine Zeile sichtbar ist oder nicht. Verwende dafür die folgende Formel in der ersten Zeile der Hilfsspalte (z.B. Spalte O): 
    =TEILERGEBNIS(103; N2)

    Diese Formel gibt 1 zurück, wenn die Zeile sichtbar ist, und 0, wenn nicht.

  3. Kleinsten Wert ermitteln: Verwende die folgende Formel, um den kleinsten Wert aus der gefilterten Liste zu erhalten: 
    =MIN(WENN(TEILERGEBNIS(3;INDIREKT("N"&ZEILE(6:60000)));N6:N60000))

    Achte darauf, die Eingabe mit Strg + Shift + Enter abzuschließen, um es als Matrixformel zu bestätigen.

Häufige Fehler und Lösungen

  • Fehlerhafte Matrixformel: Wenn die Formel nicht als Matrixformel eingegeben wird (ohne Strg + Shift + Enter), erhältst du möglicherweise einen Fehler oder falsche Ergebnisse. Überprüfe die Eingabe.
  • Falsche Zellreferenz: Stelle sicher, dass die Zellreferenzen in den Formeln korrekt sind und die Daten in der richtigen Spalte liegen.

Alternative Methoden

Eine andere Möglichkeit, den kleinsten Wert unter Berücksichtigung eines definierten Jahresbereichs zu ermitteln, ist die Verwendung der AGGREGAT-Funktion in Kombination mit TEILERGEBNIS. Hier ist ein Beispiel für eine solche Formel:

=AGGREGAT(15;6;N6:N60000/(TEILERGEBNIS(3;INDIREKT("N"&ZEILE(6:60000)))*(N6:N60000<=A3)*(N6:N60000>=A2));1)

Diese Formel berücksichtigt nur die sichtbaren Zeilen und filtert die Werte entsprechend deinem gewünschten Bereich.

Praktische Beispiele

Angenommen, du hast eine Tabelle, in der die Spalte N die Jahreszahlen enthält und du möchtest den kleinsten Wert zwischen 2005 und 2010 ermitteln, während andere Filter (z.B. nach Verkäufernamen) angewendet sind. Setze die oben genannten Formeln an den entsprechenden Stellen ein, um das gewünschte Ergebnis zu erhalten.

Tipps für Profis

  • Verwendung von Namen: Statt Zellreferenzen zu verwenden, kannst du auch benannte Bereiche für eine bessere Übersichtlichkeit nutzen.
  • Dynamische Bereiche: Wenn du deine Daten regelmäßig aktualisierst, überlege, dynamische Bereiche (z.B. mit der OFFSET-Funktion) zu erstellen, um die Formeln automatisch anzupassen.

FAQ: Häufige Fragen

1. Kann ich die Formeln auch in Excel 365 verwenden? Ja, die beschriebenen Formeln funktionieren auch in Excel 365 und sind mit den neuesten Funktionen kompatibel.

2. Was mache ich, wenn die Formeln nicht funktionieren? Überprüfe die Eingabe der Formeln, insbesondere die Verwendung von Strg + Shift + Enter für Matrixformeln. Stelle auch sicher, dass die richtige Zellreferenz verwendet wird.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige