Summe von bestimmten k letzten Elementen

[turbo]

Da muss ich das Thema doch nochmal rausholen, bin nämlich auf folgendes Problem gestoßen: wenn ich in der folgenden Formel

Code:

=SUMMEWENN(INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););60)):
INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););1));B3;
INDEX([Datenbasis_FA.xlsm]FA04!F:F;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););60)):
INDEX([Datenbasis_FA.xlsm]FA04!F:F;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););1)))+
SUMMEWENN(INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););60)):
INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););1));B3;
INDEX([Datenbasis_FA.xlsm]FA04!G:G;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););60)):
INDEX([Datenbasis_FA.xlsm]FA04!G:G;KGRÖSSTE(INDEX(([Datenbasis_FA.xlsm]FA04!$E:$E=B3)*ZEILE($A:$A););1)))

in der Zelle B3 den Suchbegriff mit * als Platzhalter hinten dran stehen hab, wird die Summe von allen Werten zum Suchbegriff (bzw. den Suchbegriffen) angezeigt und nicht die der letzten 60. Was ist da verkehrt?

[Elex]

Hi

die Verwendung von Platzhaltern in der Formel funktioniert da glaube so nicht.
Hier mal ein Bsp. wie es mit Platzhalter funktionieren kann.

____|_A_|__B_|C|_D|E|_F|

   1|xyz|  10| |50| |x*|
   2|xyz|  10| |  | |  |
   3|aaa|1000| |  | |  |
   4|bbb|1000| |  | |  |
   5|xyz|  10| |  | |  |
   6|xcc|  10| |  | |  |
   7|aaa|1000| |  | |  |
   8|xdd|  10| |  | |  |
   9|aaa|1000| |  | |  |

D1    {=SUMME(WENN(ISTFEHLER(SUCHEN(F1;A1:A9));0;B1:B9))}  ;Matixformel

[neopa]

Hallo,

... ich hab jetzt mir jetzt lediglich den letzten Beitrag von Elex angeschaut. In F1 bedarf es da weder des * noch bedarf es einer klassischen Matrixformel . Mit folgender einfachen Formel würde für die Beispieldaten von Elex das gleiche Ergebnis erzielt:   =SUMME(INDEX(ISTZAHL(SUCHEN(F1;A1:A9))*B1:B9;))

[turbo]

Komischerweise bekomme ich unterschiedliche Ergebnisse mit den 2 Formeln.
Außerdem seh ich da noch keine Möglichkeit die letzten 60 Ergebnisse summieren zu lassen.

[neopa]

Hallo turbo,

Komischerweise bekomme ich unterschiedliche Ergebnisse mit den 2 Formeln.

dann kann Dir nur ein Tipp- oder Kopierfehler  unterlaufen sein. Die Ergebnisse beider Formeln sind gleich.

seh  da noch keine Möglichkeit die letzten 60 Ergebnisse summieren zu lassen.

Ich schrieb ja schon, dass ich im thread nur auf den zuvor letzten Beitrag (von Elex) Bezug genommen hatte. 

Für mich ist nun allerdings nicht eindeutig, was Du mit "letzten 60 Ergebnisse" genau meinst?
Ich interpretiere das nachfolgend so, dass Du damit die Datenzeilen meinst, wo in Spalte A ein "x" vorkommt. Oder? Da ich auch nicht weiß, was ausgewertet werden soll, wenn weniger als 60 Datensätze ein "x" in Spalte A stehen, nehme ich des weiteren an, dass Du dann wieder alle Datensätze mit einem "x" in Spalte A auswerten willst. Oder?

Dafür nun mein Formelvorschlag (für die von Elex angenommene Datenstruktur) so:

Code:

=SUMME(INDEX(ISTZAHL(SUCHEN(F1;A1:A999))*B1:B999*(ZEILE(A1:A999)>=AGGREGAT(14;6;ZEILE(A1:A999)/SUCHEN(F1;A1:A999)^0;MIN(ZÄHLENWENN(A:A;"*x*");60)));))

[Elex]

Hi

habe dir noch einmal einen Formelmix aus den vielen Beiträgen zusammengestellt. Sie sollte deine bisherige ersetzten und für B3 Teiltexte ermöglichen.

Code:

=SUMMENPRODUKT((ISTZAHL(SUCHEN($B$3;INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(ISTZAHL(SUCHEN($B$3;[Datenbasis_FA.xlsm]FA04!$E$2:$E$200000))*ZEILE($A$2:$A$200000););60)):[Datenbasis_FA.xlsm]FA04!$E$200000)))*INDEX([Datenbasis_FA.xlsm]FA04!F:F;KGRÖSSTE(INDEX(ISTZAHL(SUCHEN($B$3;[Datenbasis_FA.xlsm]FA04!$E$2:$E$200000))*ZEILE($A$2:$A$200000););60)):[Datenbasis_FA.xlsm]FA04!$G$200000)

Du hast die Formeln immer selbst noch angepasst auf die ganze Spalte. Ist das wirklich nötig.
Teste und melde dich dann wieder.

[turbo]

Es läuft! Hab ein Weilchen gebraucht die Formel nachzuvollziehen und an eine andere Mappe anzupassen und Excel auch bis nicht mehr #NV angezeigt wurde (ganz plötzlich ohne Formeländerung).
Bin jetzt auch davon abgekommen, die ganze Spalte nach dem Wert abzusuchen. Die Formel war halt kürzer ohne Zeilenangabe.

Vielen Dank an Elex.

[turbo]

Jetzt läuft das wunderbar mit der Spalte F, deren Werte summiert werden. Wenn ich aber die Formel auf die Spalte G anwende, in der es auch Zellen mit dem Inhalt "" gibt, kommt #WERT raus, weil an den jeweiligen Stellen das Ergebnis von ISTZAHL(...) [WAHR/FALSCH] mit "" multipliziert wird. Gibt es da eine Abhilfe?

[Elex]

Bisher hattest du zwei Formeln die dann addiert wurden. Bei der neuen Formel sollte eigentlich wenn E zutrift sofort F und G addiert sein. Wenn du es für F und G extra benötigst, schau dir die roten Stellen an.

SUMMENPRODUKT((ISTZAHL(SUCHEN($B$3;INDEX([Datenbasis_FA.xlsm]FA04!E:E;KGRÖSSTE(INDEX(ISTZAHL(SUCHEN($B$3;[Datenbasis_FA.xlsm]FA04!$E$2:$E$200000))*ZEILE($A$2:$A$200000););60)):[Datenbasis_FA.xlsm]FA04!$E$200000)))*INDEX([Datenbasis_FA.xlsm]FA04!F:F;KGRÖSSTE(INDEX(ISTZAHL(SUCHEN($B$3;[Datenbasis_FA.xlsm]FA04!$E$2:$E$200000))*ZEILE($A$2:$A$200000););60)):[Datenbasis_FA.xlsm]FA04!$G$200000)

F:F und G200000 für F und G
F:F und F200000 für nur F
G:G und G200000 nur G

[turbo]

Bisher hattest du zwei Formeln die dann addiert wurden. Bei der neuen Formel sollte eigentlich wenn E zutrift sofort F und G addiert sein. Wenn du es für F und G extra benötigst, schau dir die roten Stellen an.

Die Spalten F und G hab ich einzeln ausgerechnet um das oben genannte Problem besser untersuchen zu können. Für F gibt die Formel das gesuchte Ergebnis raus, bei G gibt's #WERT (siehe oben). Folglich kommt bei F:F und G200000 auch #WERT raus.