Clever-Excel-Forum

Hallo zusammen!

Ich bin auf der Suche nach einer (hoffentlich auch möglichen) Formel.
Ich arbeite derzeit gerade an einer recht großen Tabelle, bei der sich die Formeln - je nachdem welche Zeilen ausgeblendet sind - ändert.
Sind in der Tabelle z.B. in der Spalte B z.B. 100 (B1 bis B100) Zahlenwerte angeführt, so soll in der Spalte C in jeder Zeile jeweils die Summe der letzten 20 eingeblendeten Zahlenwerte von Spalte B gebildet werden.
Mit der Funktion "Teilergebnis" kann ich zwar die ausgeblendeten Zellen ignorieren, aber nicht den Bezug erweitern, sodass am Ende 20 Zellen berücksichtigt wären....
Sobald ich also beliebige Zeilen ausblende, soll die Formel in Spalte C nicht mehr =Teilergebnis(109;B81:B100) sondern entsprechend z.B. =Teilergebnis(109;B71:B100) wenn in den letzten Zeilen 10 Zeilen ausgeblendet wurden.

Gibt es hierfür eine Option bei Excel? Irgend eine andere Art von relativem Bezug, bei dem z.B. Excel einfach 20 Zellen hochzählt und daraus dann die Summe bildet, ohne dass ich einen eindeutigen Zellbezug herstellen muss?

Ich hoffe, ich habe die Frage verständlich genug formuliert und dass sich da eine Lösung finden lässt :)

Vielen Dank!
LG
Thralia

Hallo,

eventuell kann man da mit der Funktion Teilergebnis was erreichen. Zeige doch mal deine Datei, dann kann ich konkrete Vorschläge unterbreiten.

---

Hallo, 


ups, jetzt hatte ich nicht zu Ende gelesen, aber schicke trotzdem mal eine Beispieldatei.

Danke für das rasche Feedback!
Ist natürlich nur eine vereinfachte Darstellung, aber würde mir anhand von diesem Beispiel schon sehr weiterhelfen.

LG
Thralia

Hallo,

üblicherweise arbeitet man mit Teilergebnis über ganze Spalten, dann wird das berechnet, was noch zu sehen ist. (Also eine Formel für die Spalte). Was du da machen willst, hat sich für mich noch nicht erschlossen.

Hi

Meinst du so. Hier die Summe immer für die letzten vier eingeblendeten.

____|____A___|____B___|_C_|_D|
   1|Werte   |Summe   |HS |  |
   2|       1|       1|  1|  |
   3|       5|       6|  2|  |
   4|      10|      16|  3|  |
   5|      50|      66|  4|  |
   6|     100|     165|  5|  |
   7|     200|     360|  6|  |
   8|     500|     850|  7|  |
   9|    1000|    1800|  8|  |
  10|    1500|    3200|  9|  |
  11|    2000|    5000| 10|  |
  12|    5000|    9500| 11|  |
  13|   10000|   18500| 12|  |
  14|   20000|   37000| 13|  |
  15|   50000|   85000| 14|  |
  16|  100000|  180000| 15|  |
  17|  200000|  370000| 16|  |
  18|  500000|  850000| 17|  |
  19| 1000000| 1800000| 18|  |
  20| 2000000| 3700000| 19|  |

B2    =SUMMEWENN($C$2:$C2;">"&MAX($C$2:$C2)-4;$A$2:A2)
C2    =TEILERGEBNIS(103;A2)*(MAX($C$1:C1)+1)

Gruß Elex

Ja genau so hab ich es bisher auch immer gemacht, über die ganze Spalte. 
Wir verwenden die tabelle an der ich arbeite aber im Labor als regelkarte. Sprich in der tabelle hab ich auch einen filter eingebaut für z.B. die alkoholbestimmung. Wenn ich den filter anlege habe ich etwa 200 werte der letzten 10 jahre. Da aber die analytik immer besser wird und mir damit die Resultate früherer jahre die statistik "versauen", möchte ich nicht mehr die Berechnungen  über die ganze Spalte und somit 10 jahre machen, sondern nurmehr dass die letzten 20 messungen (für den jeweiligen filter) für die Auswertung verwendet werden. Da wir mit sehr vielen methoden arbeiten und z.b. 50 tabellenBlätter (eines je methode) unpraktisch wäre, haben wir das mit dem filter und dem teilergebnis über die ganze Spalte und somit die ganze Zeitreihe gelöst - ist aber suboptimal in der Form....

LG thralia

Hallo Elex,

Hab grad deine Formeln ausprobiert, das klappt wunderbar!
Vielen herzlichen Dank, probier ich gleich morgen bei der großen File aus :D

LG
Thralia

Hi

oder evtl. besser noch so.
____|____A___|____B___|_C_|_D|_E|
   1|Werte   |Summe   |HS |  |  |
   2|       1|        |  1| 7|  |
   3|       5|        |  2|  |  |
   4|      10|        |  3|  |  |
   5|      50|        |  4|  |  |
   6|     100|        |  5|  |  |
   7|     200|        |  6|  |  |
   8|     500|     866|  7|  |  |
   9|    1000|    1865|  8|  |  |
  10|    1500|    3360|  9|  |  |
  11|    2000|    5350| 10|  |  |
  12|    5000|   10300| 11|  |  |
  13|   10000|   20200| 12|  |  |
  14|   20000|   40000| 13|  |  |
  15|   50000|   89500| 14|  |  |
  16|  100000|  188500| 15|  |  |
  17|  200000|  387000| 16|  |  |
  18|  500000|  885000| 17|  |  |
  19| 1000000| 1880000| 18|  |  |
  20| 2000000| 3870000| 19|  |  |

B2    =WENN(MAX($C$2:$C2)-$D$2<0;"";SUMMEWENN($C$2:$C2;">"&MAX($C$2:$C2)-$D$2;$A$2:A2))
C2    =TEILERGEBNIS(103;A2)*(MAX($C$1:C1)+1)

In D2 kann die Anzahl der letzten Werte flexibel gewählt werden.
Wenn weniger als Anzahl bleibt B leer.

Oooh perfekt :) danke!

Soweit hat es in unserer Tabelle sehr gut geklappt, aber kannst du mir vielleicht noch bei einer Frage weiterhelfen?
Für Mittelwerte und Summen klappen die Formeln super, aber gibt es auch eine Option die Formel in B2 von Summe auf auf STABW umzuformulieren? Hab keine StabwWenn-Funktion oder ähnliches gefunden und das wäre das letzte bisschen, das mir noch fehlen würde.

Danke und LG
Thralia