Office: Automatische Anpassung der Summenformel

Hallo liebe Helfer,

ich hätte da eine wichtige Frage:
Wie kann ich Summefunktion für eine dynamische Anzahl an Zellen anwenden?

Wenn z.B. in der Zelle A1 steht =Summe(A2:A4) ist ja bekanntlich in Ordnung. Nur wenn dieser Bereich sich weiter durch die VBA-Codes erweitert, wie kann dann auch diese Formel angepasst werden, z.B. auf A2:A3?

Mit dankenden Grüße aus Sachsen

Ag.man

 

Hallo Ag.man!

Ich kenne deinen Code nicht, daher kann das "simple"
=SUMME(INDIREKT("A2:A"&G17))
schon reichen.
(Wenn in G17 der Wert "123" steht, dann ergibt das =SUMME(A2:A123)
Sonst schau doch mal ins Tutorial dynamischer Bereichsname.

 

Hallo Schatzi (im wahrsten Sinne des Wortes!),

ich habe mal die Sache mit der Summe und Indirekt ausprobiert, funktioniert nur bedingt, da ich mehrere Summen habe, die vertikal angeordnet sind.
Diese Formel funktioniert leider nur bei der untersten Summenformel.

Mit dankbaren Grüßen

Ag.man

 

Hallo!

Diese Formel kannst du in allen Summenzellen anwenden:

=SUMME(INDIREKT("F"&ZEILE()+1&":F"&VERGLEICH("?*";INDIREKT("A"&ZEILE()+1&":A1000");)+ZEILE()-1))

Damit sie auch bei der untersten Summe funktioniert, musst du in A1000 einen beliebigen Eintrag setzen, z.B. "x".

 

Schatzi, wie kommt man auf solche ungeheuere Formeln? That is crazy! It works. Ich kann es nicht glauben.

Lieben Dank Schatzi!!!

 

Hallo Schatzi,

ich will Dir jetzt nicht auf die Nerven gehen, aber was ist wenn ich die Tabelle erweitere in dem die automatische Erweiterung in Anspruch nehme, dann verlagert sich auch x aus A1000 in A1001?

Liebe Grüße

Ag.man

 

Hallo!

Das hatte ich nicht bedacht...
Dann ändere in der Formel einfach den Bereich auf 2000.
(Mehr als 1000 Zeilen wirst du ja wohl nicht einfügen, oder?)

 

Danke für die schnelle Antwort.
Ich meinte Schatzi was ganz anderes. Wenn, wie in der Tabelle die Wochentage mittels der DropDownListe automatisch erweitert werden, verschiebt es jede darunterliegende Zeile um eine Stelle nach unten.
Wenn also die Wochentage hinzugefügt werden, dann ist x nicht mehr auf 1000 sondern auf A1001-Position.

Verstehst Du mich jetzt?

Liebe Grüße

Alex

 

Ja, das habe ich schon richtig verstanden!
Lass das x in Zeile 1000 und ändere die Formel in

=SUMME(INDIREKT("F"&ZEILE()+1&":F"&VERGLEICH("?*";INDIREKT("A"&ZEILE()+1&":A2000");)+ZEILE()-1))

Wo genau das "x" steht, ist der Formel egal; es muss nur irgendwo weiter unten irgendein Eintrag sein; ob in Zeile 1000, Zeile 1001 oder Zeile 1479 spielt keine Rolle...
So sucht die Formel bis Zeile 2000 und das sollte genügend Spielraum zum Zeileneinfügen sein.

 

Danke für den Hinweis.
Ich verstehe, vorhin schien das ganze nicht zu funktionieren doch jetzt funktioniert es wohl doch.

Da ich diese Formel, eigentlich zeigt mir excel, dass das keine richtige Formel ist, nicht genau entschlüsseln kann, möchte noch eins fragen: an welche Voraussetzungen ist diese Formel geknüpft. Worauf muss ich beim Einfügen in andere Projekte achten?

Liebe Grüße

Alex

 

Hallo!

Wie bitte??

Wir können sie ja mal aufdröseln (am Beispiel F4):
=SUMME(INDIREKT("F"&ZEILE()+1&":F"&VERGLEICH("?*";INDIREKT("A"&ZEILE()+1&":A2000");)+ZEILE()-1))

Von innen nach außen:
INDIREKT("A"&ZEILE()+1&":A2000")
ergibt den Bereich A5:A2000

VERGLEICH("?*";A5:A2000;)
sucht den ersten Eintrag in diesem Bereich und gibt die Zeile zurück.
Der erste Eintrag in diesem Bereich ist in A12, also liefert dieser Teil 8 (8. Zeile von A5:A2000).
Da dieser Bereich aber erst in Zeile 5 beginnt, müssen wir noch ein bisschen tricksen:
VERGLEICH("?*";A5:A2000;)+ZEILE()-1 = 8+4-1
ergibt 11.
Also haben wir
=SUMME(INDIREKT("F"&ZEILE()+1&":F"&11))
Das ergibt
=SUMME(F5:F11)

Und das ist genau der Bereich, den du summieren möchtest.

Ich hoffe, das war halbwegs verständlich.

 

Danke Schatzi für die Aufklärung.
Mit "keine richtige Formel" meinte ich, dass Excel diese Formel als ungültig angesehen hat.

Deinen ausführlichen Ausführungen konnte ich folgen und ich hoffe ich dieses System auch in andere Sheets einbauen.
Gibt es eigentlich eine noch einfachere Lösung für dieses Problemstellung?
Ach und bei dieser Lösung muss man natürlich auch darauf achten, dass man die unter der Spalte F keine Zahlen einträgt, sonst könnte es natürlich zu unlogischen Summierungen kommen.

Liebe Grüße

Alex

 

Tja, wenn mir etwas einfacheres eingefallen wäre, dann hätte ich das sicher gepostet...

Apropos einfach:
In F3 kannst du die Gesamtsumme errechnen mit
=SUMME(F4:F1000)/2

 

Also Schatzi,

danken kann ich Dir wohl nie genug. Bist ja besser als der Bernd Held. ;)

Diese Formel ist ja wirklich logisch. Die Zahlen kommen ja doppelt vor und einfach durch 2 Teilen, da ist schon die Summe. Kompliziert kann jeder, aber einfache Zusammenhänge herstellen, das ist die wahre Kunst.

Einen wunderschönen Abend wünsche ich Dir und bis zum nächsten maaaaaaaaaaaal liebe Schatzi.

Alex

 

Hallo nochmal!

Wenn du die Zwischensummen nach Spalte G "auslagern" kannst, dann geht's doch einfacher:
Lösche alle "INDIREKT-Formeln" aus Spalte F und schreibe in G4

=WENN(A4="";"";SUMME(F5:F$999)-SUMME(G5:G$999))
(großzügig runterkopieren)