SUMME und TEILERGEBNIS mit variabler letzter Zeile

[Scotty7]

Werte Fachkundige,

es ist schon eine Zeit lang her, dass ich mich in einem Forum bewegt habe. Aktuell kämpfe ich an einer wahrscheinlich kleinen Problemfront. Ich habe oben auf einem Exceltabellenblatt ein paar Zeilen reserviert, in denen ich ein paar Kurzauswertungen integriert habe. Ich lasse auswerten nach SUMME (beim gesamten Datenbestand in der gleichen Spalte), nach TEILERGEBNIS (Summe, beim gefilterten Datenbestand), nach MAXIMUM (gesamt), nach TEILERGEBNIS (Maximum, gefiltert) und nach MITTELWERT (gesamt) sowie TEILERGEBNIS (Mittelwert, gefiltert). Die Formeln hierzu funktionieren gut.

Nun sollen diese Formeln aber dynamisch funktionieren. Das heißt, das Ende der Datensatzes, sprich die letzte befüllte Zelle sollen in diesen Formeln einfließen. Und dies weiß ich nicht, wie ich es hinbekomme. Der eigentliche Datensatz fängt in Zeile 8 an. In Zeile 7 stehen die Überschriften der Spalten.

Derzeit sehen die Formeln wiefolgt aus:

In Zelle S1: =SUMME(S8:S1000)
In Zelle S2: =TEILERGEBNIS(9;S8:S1000)
In Zelle S3: =MAX(S8:S1000)
In Zelle S4: =TEILERGEBNIS(4;S8:S1000)
In Zelle S5: =MITTELWERT(S8:S1000)
In Zelle S6: =TEILERGEBNIS(1;S8:S1000)

Künftig werden aber noch mehr Zeilen mit Daten dazu kommen. Und ich will nicht jedes Mal Hand anlegen und die Formeln anpassen müssen. Derzeit S1000 kann also auch mal S2500 heißen.  Und es kann vorkommen, dass in Spalte [S] auch mal leere Zellen sind, sowohl irgendwo mitten im Datensatz, als auch am Ende.

Besten Dank für Lösungsansätze!

Scotty

[shift-del]

Moin Scotty

Verwende eine Excel-Tabelle (STRG-T) und strukturierte Verweise.

[RPP63]

Moin!
Natürlich ist das Tabellenobjekt mit strukturierten Verweisen erste Wahl.
Dennoch vertragen alle Deine Funktionen problemlos einen riesigen Datenbereich, weil mMn nur der "used rage" tatsächlich berechnet wird.
Dann doch gleich Nägel mit Köpfen:
=SUMME(S8:S1048576)

Gruß Ralf

[Scotty7]

Moin Detlef, moin Ralf,

ich habe noch nie etwas mit strukturierten Verweisen gemacht. Wie geht das? Ansonsten ist die S1048676 die derzeit maximale Zeilenzahl bei Excel, oder? Aber schon mal danke vielmals für die ersten, raschen Antworten! Ich muss jetzt leider außer Haus und kann erst morgen Vormittag rein schauen. Ich melde mich dann.

[shift-del]

ich habe noch nie etwas mit strukturierten Verweisen gemacht. Wie geht das?

https://support.microsoft.com/de-de/offi...ae6d2b276e

Ansonsten ist die S1048676 die derzeit maximale Zeilenzahl bei Excel, oder?

Vom Übergang von Excel 2003 auf Excel 2007 erhöhte sich überraschenderweise die Anzahl der Zeilen (von 2^16 auf 2^20) und Spalten (von 2^8 auf 2^14).
Also Vorsicht bei solchen Annahmen.

[HKindler]

Hi,

strukturierte Verweise habe ich noch nie gelernt. Ich kann (und will) mir einfach nicht die Syntax merken. Steht der Tabellenname bereits in den eckigen Klammern oder nicht? Wann wird die eckige Klammer verdoppelt? Kommt das @ für die aktuelle Zeile vor oder in die Klammer? Das sind die Fragen, die ich partout nicht beantworten kann. Dennoch verwende diese Verweise laufend und gerne.

Wie das zusammenpasst? Ganz einfach: man klickt sich die Formeln zurecht. Excel selbst sorgt für die korrekte Syntax. In deinem Fall z.B. tippst du =SUMME( und klickst dann einfach auf den Spaltenkopf.

Falls du nicht mit den strukturierten Tabellen arbeiten willst/kannst (ja, sie haben auch Nachteile!), dann kann man statt =SUMME(S8:S1048576) auch =SUMME(S:S) verwenden. Sollten in S1:S7 auch Zahlen vorkommen, dann wird es minimal aufwändiger: =SUMME(S:S)-SUMME(S1:S7) oder kürzer =SUMME(S:S;-S1:S7). Wobei die 2.Variante bei TEILERGEBNIS nicht funktioniert, da hier bei den Bezugsangaben keine Berechnungen akzeptiert werden.

[Scotty7]

Ja vielen Dank an alle! Ich habe mich fürs erste für die 1048576-Variante entschieden. Mit der Tabellenvariante (STRG+T) und strukturierten Verweisen spiele ich mich mal herum, wenn die Datei erst mal an einen Anfragenden heraus ist und ich hinterher Zeit habe.

Beste Grüße
Scotty

[HKindler]

Setz dich lieber gleich damit auseinander. Die Strg-T-Tabellen bieten so viele Vorteile, dass man schon bald nicht mehr darauf verzichten will. Insbesondere wenn "wenn die Datei erst mal an einen Anfragenden heraus" geht sollte man darauf bauen, da man u.a. keine Zeilen/Formeln auf Vorrat braucht und Formeln über die strukturierten Verweise immer passen.

Bei der 1048576-Variante kannst du Probleme bekommen, wenn Zeilen eingefügt/gelöscht werden.