[Calc] per Formel letzte benutzte Zelle ermitteln?

de-users
#1

Hallo miteinander,

lässt sich eigentlich auch per Formel (nicht per Makro) die letzte
verwendete Zelle in einem Arbeitsblatt ermitteln? Und falls ja, wie?

Mit bestem Dank im Voraus
und herzlichen Grüßen
Franklin

#2

Hallo Frank,

schau mal da (eben auf die Schnelle gefunden, selbst aber nicht getestet), vielleicht hilft Dir das weiter:

http://www.excelformeln.de/formeln.html?welcher=48

Gruß
Hans-Werner

------ Originalnachricht ------

#3

Hallo OoOHWHOoO,

schau mal da (eben auf die Schnelle gefunden, selbst aber nicht
getestet), vielleicht hilft Dir das weiter:

http://www.excelformeln.de/formeln.html?welcher=48

Ja, vielen Dank ... bei meinem Suchbegriff wurden mir durch die
Einschränkung auf LibO gar nicht erst Excel-Formel-Lösungen angezeigt.

Mit

=MAX((A1:A999<>"")*ZEILE(1:999))

eingegeben als Matrix-Formel, wird die hier gesuchte letzte belegte
Zeile korrekt ausgegeben.

Hans-Werner

Nochmals danke für den Link ...

.... und tschüss

            Franklin

#4

Hallo miteinander,

jetzt hab ich aber noch 'ne Zusatzfrage:

Wenn ich die letzte Zeile jetzt tatsächlich in irgendeiner Zelle
über die unten genannte Formel ermittelt habe, kann ich diese Zahl,
die dann dort als Ergebnis auftaucht, auch irgendwie noch in einer
anderen Formel mit verwenden, und falls ja, wie müsste ich dann
vorgehen?

Beispiel: In einer Formel wird der Bereich A3 bis letzte Zeile in
Spalte A gebraucht. In der separaten Zelle wird die letzte Zeile
bereits korrekt mit 171 ermittelt. Wie kann ich jetzt in der anderen
Formel erreichen, dass dort eben 'A3:A171' auftaucht?

Besten Dank im Voraus ...

.... und tschüss

            Franklin

#5

Hallo Franklin,

wenn deine Formel z.B. in Zelle B1 steht, also dort die letzte Zeile angezeigt wird, dann liefert
=INDIREKT("A"&B1;1)
den Inhalt der Zelle mit dieser Zeilennummer: A ist die Spalte, B1 liefert die Zeilennnummer, INDIREKT macht daraus einen Bezug.

Gruß

Gerhard

#6

Hallo Gerhard,

Hallo Franklin,

wenn deine Formel z.B. in Zelle B1 steht, also dort die letzte Zeile
angezeigt wird, dann liefert
=INDIREKT("A"&B1;1)
den Inhalt der Zelle mit dieser Zeilennummer: A ist die Spalte, B1
liefert die Zeilennnummer, INDIREKT macht daraus einen Bezug.

Super, vielen Dank!

Mit diesen Tipps von Hans-Werner und Dir konnte ich jetzt meine
Formel, die automatisch die Anzahl von gefilterten Treffern in einer
Spalte zählt, so verallgemeinern, dass ich sie bei jeder beliebig
großen Tabelle unverändert in Zelle A1 reinschreiben kann (bisher
habe ich da bei unterschiedlich langen Tabellen jeweils manuell die
letzte Zeile reingeschrieben) und dann einfach nur nach rechts zu
ziehen brauche.

Es war noch ein bisschen mehr "Formelzauberei" notwendig, weil ich
dafür ja die Spalte nicht als festen Buchstaben (wie oben), sondern
als variablen Buchstaben in der Formel brauchte.

Daher steht jetzt letztendlich in A1 folgendes drin:

=TEILERGEBNIS(3;A3:INDIREKT(ADRESSE(letzte_Zeile;SPALTE())))

Hinweis: Die gefilterten Anzahlen stehen in der ersten Zeile, die
Spalten-Überschriften in der zweiten Zeile, die eigentlichen Daten
beginnen in der dritten Zeile, daher oben 'A3' bis ...

Voraussetzung für die Verwendung der obigen Formel ist dann
lediglich, dass irgendwo in irgendeiner Zelle mit der unten
genannten Matrixformel

{=MAX((A1:A999<>"")*ZEILE(1:999))}

die höchste Zeile ermittelt wird.

Der Einfachheit halber habe ich diese Zelle dann als 'letzte_Zeile'
benamst, so dass ich nur deren Namen in der Zählformel verwenden muss.

Gerhard

Nochmals vielen Dank ...

.... und tschüss

            Franklin

#7

Hallo miteinander,

vor lauter Freude übers Funktionieren hab ich doch tatsächlich einen
kleinen Copy-and-paste-Fehler bei der Beschreibung der Lösung
gemacht -> Korrektur siehe unten.

Hallo Gerhard,

Hallo Franklin,

wenn deine Formel z.B. in Zelle B1 steht, also dort die letzte Zeile
angezeigt wird, dann liefert
=INDIREKT("A"&B1;1)
den Inhalt der Zelle mit dieser Zeilennummer: A ist die Spalte, B1
liefert die Zeilennnummer, INDIREKT macht daraus einen Bezug.

Super, vielen Dank!

Mit diesen Tipps von Hans-Werner und Dir konnte ich jetzt meine
Formel, die automatisch die Anzahl von gefilterten Treffern in einer
Spalte zählt, so verallgemeinern, dass ich sie bei jeder beliebig
großen Tabelle unverändert in Zelle A1 reinschreiben kann (bisher
habe ich da bei unterschiedlich langen Tabellen jeweils manuell die
letzte Zeile reingeschrieben) und dann einfach nur nach rechts zu
ziehen brauche.

Es war noch ein bisschen mehr "Formelzauberei" notwendig, weil ich
dafür ja die Spalte nicht als festen Buchstaben (wie oben), sondern
als variablen Buchstaben in der Formel brauchte.

Daher steht jetzt letztendlich in A1 folgendes drin:

=TEILERGEBNIS(3;A3:INDIREKT(ADRESSE(letzte_Zeile;SPALTE())))

Hinweis: Die gefilterten Anzahlen stehen in der ersten Zeile, die
Spalten-Überschriften in der zweiten Zeile, die eigentlichen Daten
beginnen in der dritten Zeile, daher oben 'A3' bis ...

Voraussetzung für die Verwendung der obigen Formel ist dann
lediglich, dass irgendwo in irgendeiner Zelle mit der unten
genannten Matrixformel

{=MAX((A1:A999<>"")*ZEILE(1:999))}

die höchste Zeile ermittelt wird.

[Korrektur Anfang]

Bei meinem oben beschriebenen Dateiaufbau darf diese Formel
natürlich NICHT bereits mit Zeile 1, sondern erst mit Zeile 3
beginnen (sonst Zirkelschluss) und muss daher lauten:

{=MAX((A3:A999<>"")*ZEILE(3:999))}

In meinem konkreten Fall lautet sie sogar noch ein bisschen anders,
weil in Spalte A die Anrede steht, die durchaus auch mal leer sein
kann, ebenso in Spalte B, wo nicht überall ein Vorname auftaucht,
lediglich die Spalte C mit dem Nachnamen ist auf jeden Fall immer
gefüllt, daher verwende ich hier:

{=MAX(INDEX((C3:C999<>"")*ZEILE(C3:C999);0))}

[Korrektur Ende]