MAXIMUM und VERGLEICH nur aus gefilterten Daten

[schauan]

Hallöchen,

erst mal keine Lösung, nur etwas Theorie.

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

könnte man auch so schreiben
=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(E8:E10000)))*(E8:E10000=E5))

Eine Zelle kann man auch mit INDEX ermitteln, INDEX also versuchen, da einzubauen.
Also
ZEILE(E8:INDEX(E8:E10000;100))
und
=(E8:INDEX(E8:E10000;100))

bzw. eingesetzt dann
=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(E8:INDEX(E8:E10000;100))))*(E8:INDEX(E8:E10000;100))=E5)

Die 100 wäre dann hier das "flexible" Ende bzw. könnte berechnet werden. Hm, funktioniert zwar einzeln, aber nicht in der Zusammensetzung - da ergibt es 0

Mit VERWEIS(2;1/(E1:E10000<>"");ZEILE(E1:E10000)), um nur die Zeilennummer zu ermitteln, geht es wohl auch nicht


Was z.B. gehen würde, wäre die Berechnung in einer intelligenten Tabelle/Liste, z.B. mit

=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(Tabelle1[wo])))*(Tabelle1[wo]=E5))

"wo" wäre bei mir ein fiktiver Spaltenname.
Nun könnte man ja mal experimentieren, ob es nicht auch mit benannten Bereichen geht. Oder auch nicht ...

[BoskoBiati]

Hi André,

die Formel funktioniert, wenn Du sie auf die richtige Zelle beziehst:

Code:

=SUMME(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(E8:INDEX(E8:E10000;100))))*(E8:INDEX(E8:E10000;100)=E4))

  Auswertungen_Template.xlsx (Größe: 13,59 KB / Downloads: 4)

[HKindler]

Hi,

da ich INDIREKT() doof finde, mache ich bei ausführlichen Auswertungen zu gefilterten Listen immer eine Hilfsspalte.
Überschrift: "sichtbar?"
Formel: =TEILERGEBNIS(103;ZelleInSicherGefüllterSpalte)
Jetzt steht in dieser Spalte eine 1, wenn die Zeile sichtbar ist und eine 0, wenn sie nicht sichtbar ist. Somit kann man diese wunderbar für alle möglichen Berechnungen einsetzen. Diese Spalte kann man auch ausblenden, falls sie stören sollte.

Wenn du in deiner Tabelle z.B. den Namen haben willst (F ist die Hilfsspalte):
=INDEX(B:B;VERGLEICH($E$4&1;E8:E10000&F8:F10000;0)+7)

Übrigens: wieso verwendest du keine Strg-T-Tabelle? Dann hast du das Problem mit dem Berechnen der Bereiche gar nicht. Die "sichtbar?"-Formel lautet dann: =TEILERGEBNIS(103;[@ID])
Und die Formel für den gefilterten Maximum-Namen: =INDEX(Tabelle1[Name];VERGLEICH($E$4&1;Tabelle1[Wert]&Tabelle1[sichtbar?];0))

[schauan]

@Edgar,

bei mir war eine Klammer an der falschen Stelle

))=E5)

müsste so aussehen

)=E5))

Wie es halt' so ist. Man schaut x mal auf eine bestimmte Stelle, wundert sich, das müsste doch gehen, sieht den Fehler nicht und denkt, wer weiß, was man da für Vorstellungen hat

[EA1950]

Hallo,

setzt man die entscheidenden Hinweise und Anregungen von Helmut @HKindler um, erhält man nachfolgende Datei.

[HKindler]

Hi,

danke fürs Umsetzen, aber so ganz korrekt ist es noch nicht.

Die Formel in G4 zeigt immer noch ausgeblendete Namen an, wenn sie den Max-Wert enthalten.
Wenn nur sichtbare Namen angezeigt werden sollen, dann sollte man besser
G4: =TEXTVERKETTEN("; ";1;MTRANS(FILTER(tbDaten[Name];(tbDaten[Wert]=E4)*tbDaten[Sichtbar])))
verwenden.

Selbes Prinzip gilt für H4.

Und eine winzige Kleinigkeit: in E4 könnte man statt =MAX(tbDaten[Wert]*tbDaten[Sichtbar]) auch =MAXWENNS(tbDaten[Wert];tbDaten[Sichtbar];1) verwenden. Ist hier zwar völlig nebensächlich, aber wieso nicht eingebaute Funktionen verwenden? Bei MIN() würde es nämlich mit der ersten Methode nicht funktionieren und bei MAX() auch nicht, wenn die Werte alle negativ wären. In beiden Fällen käme immer 0 raus.

[EA1950]

Hallo Helmut,

danke für den Hinweis! Ich hatte nicht bedacht, dass es zu einem MAX-Filterwert (für die sichtbaren Tabelleneinträge) in der Tabelle trotzdem immer noch Einträge geben kann, die entweder sichtbar oder ausgeblendet sein können.

[Scotty7]

Ein Hallo in die Runde!

Mein Anliegen mit einer dynamischen Zellenansprache und des Zählen Lassens, wie oft ein Tages-MAXIMUM unter den gefilterten Daten vorkommt, habe ich nun dank Euch und weiterer Recherchen im Internet lösen können. Die Formel lautet nun:

=SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT("E"&ZEILE(E8:INDEX(E:E;lastRowUsed))))=1)*(E8:INDEX(E:E;lastRowUsed)=E4)*1)

Steht in Zelle F4

[lastRowUsed] habe ich als Namen definiert. Der Bezug hierzu lautet:

=ANZAHL2(Tabelle1!$A:$A)+6

Offen gestanden, habe gefühlt Tage und Nächte damit verbracht, für dieses, eigentlich leichtes Anliegen Hirnschmalz zu verbraten. Nun denn, ich stufe mich als Kleinkönner in Excel ein. Da ist noch unsäglich viel Platz nach oben! Auf alle Fälle mal mehr, als schon mit Können besetzt ist!

HERZLICHEN DANK an alle!!!

Scotty

[EA1950]

Hallo,

die Formel in Zelle F4, die das Gleiche leistet, lautet schlicht und ergreifend:
=ANZAHL2(H4#)

oder, falls man die Formel in H4 direkt in die Formel einbaut:
=ANZAHL2(FILTER(tbDaten[Datum];(tbDaten[Wert]=E4)*tbDaten[Sichtbar]))