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

Formel f Teilergebnis der ersten Werte

Forumthread: Formel f Teilergebnis der ersten Werte

Formel f Teilergebnis der ersten Werte
21.01.2022 11:13:16
Fred
Hallo Excel Experten,
Ich filter eine Tabelle (Header in Zeile 13, die ersten Werte ab Zeile 14)
Ich möchte nun die Summe der ersten 8 Werte in Spalte "AH" (in gef. Tabelle) durch eine Formel ermitteln.
Kann mir bitte ein Experte auf die Sprünge helfen?!
Gruss
Fred
Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel f Teilergebnis der ersten Werte
21.01.2022 11:26:27
{Boris}
Hi,
ich gehe davon aus, dass das Filterergebnis in Spalte AH lückenlos ist:
=SUMME(INDEX(AH14:AH100;KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("AH"&ZEILE(14:100)));ZEILE(1:87)); ZEILE(1:8))))
In Deiner Excelversion als {Arrayformel} eingeben: Formelabschluss nicht mit Enter allein, sondern mit gleichzeitig Strg+Shift+Enter.
VG, Boris
Anzeige
AW: Formel f Teilergebnis der ersten Werte
21.01.2022 12:20:54
Fred
Hallo Boris,
deine Arrayformel 

=SUMME(INDEX(AH14:AH100;KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("AH"&ZEILE(14:100)));ZEILE(1:87));  ZEILE(1:8))))
gibt mir lediglich den ersten Wert der gefilt. Daten zurück. Kannst du die Formel nochmals überarbeiten?
Gruss
Fred
Anzeige
Arrayformel!
21.01.2022 12:25:03
{Boris}
Hi,
ich hatte doch dazugeschrieben:
In Deiner Excelversion als {Arrayformel} eingeben: Formelabschluss nicht mit Enter allein, sondern mit gleichzeitig Strg+Shift+Enter.
Hast Du das so gemacht?
VG, Boris
AW: Arrayformel!
21.01.2022 12:29:47
Fred
Hallo Boris,
ja, habe ich als Arrayformel (Strg+Shift+Enter) eingegeben. Es wird nur der erste Wert der gefilterten Daten ausgegeben.
Gruss
Fred
Anzeige
Dann zeig mal...
21.01.2022 12:31:02
{Boris}
Hi,
...bitte Deine Datei MIT der Formel.
VG, Boris
Das ist etwas "spooky"...
21.01.2022 13:04:38
{Boris}
Hi,
denn in xl365 läuft die Formel als NORMALE Formel (wird ja intern als Matrixformel interpretiert) - aber als Arrayformel nicht:
Userbild
Diese {Array}formel hier sollte aber so nun auch in Deiner Excelversion laufen:
=SUMME(N(INDIREKT("AH"&KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("AH"&ZEILE(14:100)));ZEILE(14:100)); ZEILE(1:8)))))
VG, Boris
Anzeige
Vielen Dank Boris
21.01.2022 13:17:09
Fred
Hallo Boris,
nun funzt es wie gewünscht.
Vielen Dank für deine Mühe und Kompetenz!
Gruss
Fred
Es bleibt das Rätsel...
21.01.2022 13:18:45
{Boris}
Hi,
...weshalb die erste Version außerhalb xl365 nicht läuft - aber Hauptsache, es geht bei Dir jetzt! :-)
VG, Boris
AW: für das Beispiel ist es ok, doch ...
21.01.2022 13:20:32
neopa
Hallo Boris,
... wenn mal weniger als 8 Datenzeilen gefiltert sind, gibt es garantiert eine Fehlermeldung.
Einfacher wird es mit einer Hilfsspalte.
Gruß Werner
.. , - ...
Anzeige
Auch das...
21.01.2022 13:50:44
{Boris}
Hi Werner,
...könnte man natürlich noch in die Formel integrieren:
=SUMME(N(INDIREKT("AH"&KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("AH"&ZEILE(14:100)));ZEILE(14:100)); ZEILE(INDIREKT("1:"&MIN(8;TEILERGEBNIS(3;AH14:AH100))))))))
Aber vielleicht möchte er ja gar kein Ergebnis haben, wenn es nicht mindestens 8 sind...?
Deine Aversion gegen die { } ist übrigens frappierend.
VG, Boris
Anzeige
AW: natürlich, aber ...
21.01.2022 14:16:00
neopa
Hallo Boris,
... ich hab eher eine "Aversion" gegen INDIREKT() als gegen {} -Formeln. Persönlich setze ich INDIREKT() nur da ein, wo ich diese im Sinne dessen ursprünglich vorgesehen Funktionalität wirklich benötige und wenn ich diese zu einer Matrixbildung (wie z.B. in Deiner Formel hier) unbedingt so haben möchte (um z.B. eine Hilfsspalte zwingend zu vermeiden). Ich hab aber schon seit Jahren immer bedauert, dass derartiges in den XL Versionen zumindest bis einschließlich XL2019 nicht bzw. nicht komplett durch den Einsatz von INDEX() ersetzt werden kann.
Gruß Werner
.. , - ...
Anzeige
AW: ich würde hier mit ner Hilfsspalte arbeiten...
21.01.2022 13:21:15
neopa
Hallo Fred.
... in AI14: =TEILERGEBNIS(103;C14) und diese nach unten kopieren.
Dann z.B. folgende Auswertungsformel:
=SUMMENPRODUKT(AH14:AH999*AI14:AI999*(ZEILE(D14:D999)&lt=WENNFEHLER(AGGREGAT(15;6;ZEILE(D14:D999)/AI14:AI999;8);AGGREGAT(14;6;ZEILE(D14:D999)/AI14:AI999;1))))
Die Formel kann normal abgeschlossen werden und ermittelt auch ein Ergebnis für weniger als 8 gefilterte Werte.
Gruß Werner
.. , - ...
Anzeige
;

Beliebteste Forumthreads (12 Monate)

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