Formeln anwenden in Bezug auf größte Datumswerte

Bild

Betrifft: Formeln anwenden in Bezug auf größte Datumswerte
von: Christian
Geschrieben am: 12.05.2015 13:32:04

Hallo zusammen,
erst einmal Hallo ins Forum- Ich benötige eine Excel-Formel und bin kurz vor dem Verzweifeln.
Excel soll mir auf Basis der letzten 5 größten Datumswerte in Spalte C sagen:
- wie hoch der durchschnittliche Max.-Pot. Wert aus Spalte D ist (Das wären hier vereinfacht die letzten 5 Zeilen)
- wie hoch der durchschnittliche Mehrwert der letzten 5 Fälle ist, die eine Änderung in Spalte A haben. dies allerdings jedoch auch in Bezug auf die letzten 5 (größten) Datumswerte (Das wäre hier der Durchschnittswerte aus Spalte E der Zeilen 4, 5, 7, 9 und 10)
- wie viele der letzten 5 größten Datumswerte ein "ja" in Spalte A haben
Ich komme hier einfach nicht weiter, von VBA und Makros habe ich leider überhaupt keine Ahnung. Vielleicht kann mir ja hier freundlicherweise jemand helfen.
Wie man an die Durchschnittswerte etc. kommt ist mir mit meinem Excel-Basis-Wissen soweit klar, allerdings verzweifel ich daran, Excel zu "sagen" er soll dies nur in Bezug auf die letzten 5 "größten" Datumswerte tun, sich also nur die Zeilen und Spalten angucken und rechnen, die zu den letzten 5, 10 oder was auch immer größten Datumswerten gehören.
Kann mir jemand weiterhelfen? Die entsprechende Exceltabelle habe ich als Anhang zugefügt. Vielleicht kann sich ja jemand meiner verzweifelten Excel-Selle annehmen. ;-)
Gruß und Dank,
Christian
https://www.herber.de/bbs/user/97594.xlsx

Bild

Betrifft: AGGREGAT in Matrixfunktion(alität)sformeln ...
von: der neopa C
Geschrieben am: 12.05.2015 15:05:56
Hallo Christian,
... Deine geschilderte Problemstellung ist bezogen auf Deine Datenwerte nicht ganz eindeutig, weil Du teils identische Datumswerte hast. Ich hab nun für die ersten beiden Deiner Fragestellungen angenommen, dass bei gleichen Datum der Datensatz mit der höheren Zeilennummer ein Tick jünger ist, um wirklich nur immer 5 Datensätze auszuwerten. Für die Beantwortung Deiner dritten Fragestellung hab ich da herausgelassen, könnte ich aber auch dort einbinden. In diesem Fall ergibt sich in I4=3
Tabelle1

 ABCDEFGHI
1ÄnderungBearbeiterDatumMax.-Pot.Mehrwert fürgrößte Datumswerte; Ermitllung:Ergebnis
2jaABC08.05.2015100008000 5Durchschnitt:  Max.-Pot.5500
3 ABC08.05.201510000  Durchschnitt:  Mehrwert3800
4jaXYZ08.05.201550003000 Anzahl mit "ja"4
5jaXYZ09.05.201550003000    
6 ABC09.05.20155000     
7jaXYZ10.05.201550005000    
8 ABC10.05.20157500     
9jaXYZ11.05.201550005000    
10jaABC12.05.201550003000    

Formeln der Tabelle
ZelleFormel
I2=MITTELWERT(INDEX(AGGREGAT(14;6;D$2:D$99/(C$2:C$99+ZEILE(A$2:A$99)%%>=AGGREGAT(14;6;C$2:C$99+ZEILE(A$2:A$99)%%;G$2)); ZEILE(A$1:INDEX(A:A;G$2))); ))
I3=MITTELWERT(INDEX(AGGREGAT(14;6;E$2:E$99/(A$2:A$99="ja")/(C$2:C$99+ZEILE(A$2:A$99)%%>=AGGREGAT(14;6;(C$2:C$99+ZEILE(A$2:A$99)%%)*(A$2:A$99="ja"); G$2)); ZEILE(A$1:INDEX(A:A;G$2))); ))
I4=ANZAHL(INDEX(AGGREGAT(14;6;ZEILE(A$2:A$99)/(A$2:A$99="ja")/(C$2:C$99>=AGGREGAT(14;6;C$2:C$99;G$2)); ZEILE(A$1:INDEX(A:A;G$2))); ))

Gruß Werner
.. , - ...

Bild

Betrifft: AW: Formeln anwenden in Bezug auf Datumswerte
von: Christian
Geschrieben am: 13.05.2015 15:28:59
Hallo Werner,
ich hatte es im anderen Forum ja schon geschrieben, dass du mein Held bist. :-) Ja, die Annahme ist völlig korrekt, es muss so sein, dass bei gleichem Datum der höchste Zeile diejenige ist, die ausgewertet wird. Die letzten 5 sind sozusagen die Zellen C6 bis C10. Wäre es im vorlegenden Fall so, dass wenn man die letzten 8 nehmen würde, er die Zellen C3 bis C10 nimmt?
Ich komme leider auf ein anderes Ergebnis: In I4 sollte der Wert 4333,33 sein, in I5 "3", da ja in den Zeilen 6 bis 10, welche die 5 größten Datumswerte sind, 3x ja steht, und der Mittelwert von E6:E10 4333,33 ist. Bekommst du das eventuell so hin?
Vielen Dank und herzlichen Gruß,
Christian

Bild

Betrifft: die Anpassung dafür ist leicht möglich ...
von: der neopa C
Geschrieben am: 13.05.2015 16:56:36
Hallo Christian,
... siehe untenstehend. Deine Eingangsfrage ist mit ja zu Beantworten.

 ABCDEFGHI
1ÄnderungBearbeiterDatumMax.-Pot.Mehrwert fürgrößte Datumswerte; Ermitllung:Ergebnis
2jaABC08.05.2015100008000 5Durchschnitt:  Max.-Pot.4500
3 ABC08.05.201510000  Durchschnitt:  Mehrwert4333,33
4jaXYZ08.05.201550003000 Anzahl mit "ja"3
5jaXYZ09.05.201550003000    
6 ABC09.05.2015      
7jaXYZ10.05.201550005000    
8 ABC10.05.20157500     
9jaXYZ11.05.201550005000    
10jaABC12.05.201550003000    
11         

Formeln der Tabelle
ZelleFormel
I2=MITTELWERT(INDEX(AGGREGAT(14;6;D$2:D$99/(C$2:C$99+ZEILE(A$2:A$99)%%>=AGGREGAT(14;6;C$2:C$99+ZEILE(A$2:A$99)%%;G$2)); ZEILE(A$1:INDEX(A:A;G$2))); ))
I3=MITTELWERT(INDEX(AGGREGAT(14;6;E$2:E$99/(A$2:A$99="ja")/(C$2:C$99+ZEILE(A$2:A$99)%%>=AGGREGAT(14;6;(C$2:C$99+ZEILE(A$2:A$99)%%)*(A$2:A$99="ja"); G$2)); ZEILE(A$1:INDEX(A:A;I4))); ))
I4=ANZAHL(INDEX(AGGREGAT(14;6;E$2:E$99/(A$2:A$99="ja")/(C$2:C$99+ZEILE(A$2:A$99)%%>=AGGREGAT(14;6;(C$2:C$99+ZEILE(A$2:A$99)%%); G$2)); ZEILE(A$1:INDEX(A:A;G$2))); ))

... bin wahrscheinlich erst Pfingstdienstag wieder online.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formeln anwenden in Bezug auf größte Datumswerte"