MAXIMUM und VERGLEICH nur aus gefilterten Daten

[Scotty7]

Ein Hallo in die Runde!

Ich habe eine umfangreiche Tabelle mit über 10.000 Datensätzen. Jede Spalte ist filterbar. Oben habe ich für kleine Auswertungen Zeilen reserviert. In Zeile 1 wird die SUMME von Werten aus der gesamten Tabelle ermittelt. In Zeile 2 die SUMME der gefilterten Datensätzen mit der Formel TEILERGEBNIS. Analog hierzu werden in Zeile 3 und 4 MAXIMUM-Werte abgebildet und in Zeile 5 und 6 die MITTELWERTE. Alle sechs Ergebniswerte stehen direkt über der Spalte mit den Quellenwerten, also in Spalte E. Zeile 7 beinhaltet die Spaltenbezeichnungen mit den versehenen Autofiltern. Ab Zeile 8 beginnen die eigentlichen Daten. Hier ein Beispiel

ID  Name  Kategorie  Datum        Wert
1    A1                1    01.01.2001    10
2    A2                1    15.02.2003     7
3    B1                1    13.03.2004     5
4    B2                2    07.04.2000    10
5    B3                2    11.06.2004     3

Bei den MAXIMUM-Werten möchte ich nun sowohl bei den Gesamtwerten als auch eine Zeile darunter bei den gefilterten Werten Angaben darüber machen, bei welchem Namen und welchem Datum die MAXIMUM-Werte aufgetreten sind. Hierzu habe ich die VERGLEICH-Funktion ins Spiel gebracht. Nun gibt es aber Werte, die gleich sind, wie z.B. die 10 bei ID 1 und ID 4. Egal, ob ich bei "gesamt" oder "gefiltert" den VERGLEICH anstelle, es wird immer bei der ersten gefundenen 10 der dazugehörige [Name] und das dazugehörige [Datum] angegeben. Nun meine Frage ins Forum:

Wie lasse ich einen einen Allgemeinhinweis ausgeben, z.B. "Es liegen mehrere Namen und Datumsangaben mit der gleichen Maximalzahl vor", sofern die MAXIMALZAHL mehrfach vorkommt? Und wie gebe ich den richtigen Namen und das richtige Datum aus, wenn gefiltert ist? Ich filtere z.B. nach [Kategorie] = 2. Dann sind in der Tabelle nur noch ID 4 und 5 sichtbar. Die MAXIMAL-Zahl ist dabei 10. Aber mittels der VERGLEICH-Funktion wird [Name] = A1 und [Datum] = 01.01.2001 ausgegeben (bei der ersten 10). Mitgeteilt aber soll werden: [Name] = B2 und [Datum] = 07.04.2000.

Hier meine dynamischen VERGLEICH-Funktionen:

'fuer den Namen:
'gesamt, in Zelle F3:
=INDEX(INDIREKT("$B$8:$B$"&ANZAHL2($A:$A)+6);VERGLEICH($E$3;INDIREKT("$E$8:$E$"&ANZAHL2($A:$A)+6);0))
'gefiltert, in Zelle F4:
=INDEX(INDIREKT("$B$8:$B$"&ANZAHL2($A:$A)+6);VERGLEICH($E$4;INDIREKT("$E$8:$E$"&ANZAHL2($A:$A)+6);0))

'fuer das Datum:
'gesamt, in Zelle G3:
=INDEX(INDIREKT("$D$8:$D$"&ANZAHL2($A:$A)+6);VERGLEICH($E$3;INDIREKT("$E$8:$E$"&ANZAHL2($A:$A)+6);0))
'gefiltert, in Zelle G4:
=INDEX(INDIREKT("$D$8:$D$"&ANZAHL2($A:$A)+6);VERGLEICH($E$4;INDIREKT("$E$8:$E$"&ANZAHL2($A:$A)+6);0))

Vielen Dank für Unterstützung!

Scotty

[BoskoBiati]

Hi,

zeige mal eine Mustertabelle.

Vergleich bringt immer nur den ersten gefunden Wert,

Ansonsten würde ich erst einmal abfragen, ob es das Maximum mehrfach gibt.

[{Boris}]

Hi,

bezogen auf Spalte E:

=MAX(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(8:10000)))*E8:E10000)

ermittelt Dir das Maximum aus Spalte E aus den gefilterten (sichtbaren) Werten.

Wenn diese Formel in E5 steht, dann ermittelt

=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(8:10000)))*(E8:E10000=E5))

die Häufigkeit dieses Wertes. Alles größer 1 bedeutet dann, dass es das Maximum mehrmals gibt.

Ansonsten wäre eine Beispieldatei - wie von Edgar angeregt - hilfreich.

[Scotty7]

Danke für das erste Statement zu meiner Problematik! Ich versuche eine Musterdatei hochzuladen.

[{Boris}]

Hi,

schreibe in F8:

=TEILERGEBNIS(3;E8)

und kopier das runter.

Die Datümer beispielhaft zum gefilterten Tages-Maximum erhältst Du mit:

=MTRANS(FILTER(D8:D10000;(E8:E10000=E4)*F8:F10000))

[BoskoBiati]

Hi,

im Übrigen halte ich alle INDIREKT-Formeln hier für überflüssig. Zum Einen geht es auch mit INDEX, zum Andern kann man auch ohne jegliche Begrenzung auf gefüllte Zellen die gleichen Ergebnisse erzielen:


  Auswertungen_Template.xlsx (Größe: 12,86 KB / Downloads: 5)

[Scotty7]

Hoi Boris,

danke schon mal. Ich habe mich mit Deinen Formeln nun schon gespielt und es funktioniert echt klasse. Kommen mehr als ein Datum vor, hängt Excel nun weitere Datumsangaben in der bzw. den folgenden Spalte(n) in der gleichen Zeile an. Das muss ich schauen, wie ich das handhabe. Vielleicht baue ich eine WENN-Funktion ein, sobald ein MAX-Wert mehrfach vorkommt, dass ich dann in die Zelle lediglich darauf hinweise, dass es mehrere Fälle gibt. Das erst recht, weil ich ja auch die Namen mit ausgeben möchte. Und wenn die ebenfalls mehrfach sind, würden ja auch mehrere Spalten befüllt werden. Ich muss mir nun klar werden, was ich schlussendlich sehen will. Vielleicht lege ich einfach ein neues Tabellenblatt dahinter an, wo ich beliebig die Werte für die Namen und Datums untereinander setzen kann, sowohl bei den Gesamtdaten, als auch bei den gefilterten. Dann müsste ich vermutlich eine andere Funktion als MTRANS hernehmen. Vielleicht werde ich fündig.

Nun muss ich außer Haus und komme morgen erst dazu es weiter zu verfolgen. Vielen herzlichen Dank einstweilen. Bis dann. Ich melde mich auf alle Fälle.

Scotty

---

Und vielen Dank auch Edgar!

[Scotty7]

Hallo Boris,

ich habe versucht, die Formeln zum Zählen lassen, wie oft die Maximalzahl in den gefilterten Werten vorkommt versucht nachzuvollziehen und bin nicht wirklich durch gestiegen. Hinzu habe ich dann erfolglos versucht, die INDIREKT-Funktion durch eine INDEX-Funktion zu ersetzen und dann noch den Bereich dynamisch zu gestalten, also

E8:INDEX(E:E;ANZAHL2(A:A)+6)

Und da kommen dann kryptische Ergebnisse heraus.

Was mache ich falsch?

Danke für weitere Tipps.

Scotty

[Scotty7]

Das mit dem Untereinanderschreiben der mehrfach vorkommenden MAXIMAL-Werten habe ich dahingehend lösen können, dass ich auf einem neuen Tabellenblatt einfach die MTRANS-Funktion weg genommen habe. Jetzt sieht das gut aus. Schön übersichtlich werden untereinander die Namen und Datums abgebildet. Danke für all die Unterstützungen.

Scotty

[Scotty7]

=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(8:10000)))*(E8:E10000=E5))

lautet die Formel von Boris. Wie kann ich die Zahl 10000 durch eine dynamische Zahl ersetzen? Ich habe hierzu mit dem Namensmanger eine lastRowUsed gebastelt, die an sich auch den richtigen Rückgabewert liefert. Setze ich statt 10000 nun lastRowUsed ein und es kommt eine Fehlermeldung, als wollte ich keine Formel verwenden, mit Apostroph usw.

Danke für Hilfe zur o.g. Formel. Ich möchte es einfach dynamisch haben. Danke!