Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

13.08.2025 10:14:04

earlycon

Guten Morgen zusammen, ich könnte ein wenig Nachhilfe bei folgender Formel gebrauchen:

mit =TEILERGEBNIS(3; A3:A1000) ermittle ich die Anzahl der gefüllten Zeilen im Bereich (auch mit Autofilter)

mit =AGGREGAT(14;4;(A:A>"")*ZEILE(A:A);1)-2 ermittle ich die letzte Gefüllte Zeile in Spalte A

Nun habe ich versucht, beides wie folgt zu kombinieren, um in der Bereichsangabe "A3:A1000" flexibler zu sein,
erhalte aber immer ein "Es gibt ein Problem mit der Formel".

=TEILERGEBNIS(3; A3:A&AGGREGAT(14;4;(A:A>"")*ZEILE(A:A);1)-2)

Hat jemand eine Idee, wie die Formeln richtig zusammengesetzt werden können ?

Besten Dank und euch einen angenehmen Tag
Stefan

AW: Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

13.08.2025 10:33:46

daniel

Hi
Du kannst Zellbezüge nicht wie Texte zusammensetzen.
Wenn ein Zellbezug berechnet werden muss, kannst du

- mit Indirekt einen Text in einen Zellbezug wandeln:
A3:Indirekt("A"&Aggregat(...))

- Index eine Zelle über eine Zeilen- und Spaltennummer auswählen
A3:Index(A:A;Aggregat(...);1)

In deiner Execelversion kann man aber inzwischen diesen Sachverhalt eleganter lösen:
Weglassen(A3.:.A99999;-2)
Die Punkte um den Doppelpunkt geben an, dass aus diesem Bereich nur die tatsächlich verwendeten Zellen verwendet werden sollen und das Weglassen gibt an, das auch die letzten beiden Zeilen des genannten Bereichs nicht verwendet werden sollen.

Noch ein Tipp zum Aggregat:
Besser ist es, die unerwünschten Zeilen durch das provozieren eines Fehlers herauszunehmen.
Deine Version mit dem Multiplizieren mit 0 funktioniert nur in der Kombination mit 14 (Max), würdest du nach der ersten Zeile suchen (15 Min), würde es nicht funktionieren, weil du die 0 als Ergebnis bekommst

Daher besser: AGGREGAT(14;6;ZEILE(A:A)/(A:A>"");1)

Gruß Daniel

AW: Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

13.08.2025 11:48:48

earlycon

Moin Daniel, vielen Dank für Deine Rückmeldung. Deine Erläuterungen haben mir sehr geholfen das zu verstehen.

Dadurch konnte ich die Formel wie folgt ändern und so die Anzahl aller sichtbaren Zeilen
dynamisch (also auch bei neuen Einträgen) berechnen lassen 👍🏼

=TEILERGEBNIS(3;A3:INDIREKT("A"&AGGREGAT(14;6;ZEILE(A:A)/(A:A>"");1)))

Viele Grüße von der Nordsee (am gefühlt jetzt schon heißesten Tag des Jahres...🥵)
Stefan

AW: Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

13.08.2025 12:42:45

daniel

Hi
ich würde Index vorziehen.

Indirekt hat zwei Nachteile.
1. es ist volatil, dh es sorgt dafür, dass diese Formel bei jeder Änderung in Excel neu berechnet wird (und damit alle Formeln, die von dieser Formel abhängig sind).
Normale Formeln werden nur neu berechnet, wenn eine Zelle, die die Formel verwendet, geändert wird. Bei Indirekt kann Excel aber gar nicht so einfach bestimmen, welche Zellen die Formel verwendet ohne die Formel zu berechnen, also wird diese Formel immer berechnet. Das kann die Datei träge und langsam machen, insbesondere wenn damit andere Formeln mit hohem Rechenaufwand verknüpft sind.

2. die Formel kann diese Zellbezüge nicht mehr automatisch anpassen.
Wenn du beispielsweise ganz am Anfang noch eine Spalte einfügst, müssten ja alle Bezüge auf die Spalte A in einen Bezug auf die Spalte B umgewandelt werden.
das macht Excel auch, nur für den fixen Text im Indirekt ("A") ist das nicht möglich, das bleibt bei "A" und du müsstet die Formel dann noch von Hand korrigieren.

Gruß Daniel

Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

Forumthread: Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

Forumthread: Anzahl Zeilen und letzte Zeile per Aggregat ermitteln

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread