Wert "letzte gefüllte Zeile" in Formel verwenden

Question

Moin,
ich habe folgende Frage:

Ich ermittle allmonatlich eine Datei, deren letzte gefüllte Zeile variiert.
Die letzte gefüllte Zeile ermittle ich mit der Formel:
=VERWEIS(2;1/ZÄHLENWENN(BEREICH.VERSCHIEBEN(1:1;ZEILE(A:A););"<>");ZEILE(A:A)+1)

Das klappt.
Nun möchte ich den WERT dieser Zeile in weiteren Formeln für SVERWEISE nutzen.

Bisheriger SVERWEIS (Bsp.):
=SUMMEWENN($A$1:$A$100;"Auto";$B$1:$B$100)
100 = letzte gefüllte Zeile

Wie bekomme ich den Wert "100" variabel in diese Formel hinein, damit ich die Formel nicht jedes Mal manuell anpacken muss, wenn die letzte gefüllte Zeile im nächsten Monat z.B. 235 ist?

Mein Versuch:
Name definieren: LetzteZeile = VERWEIS(2;1/ZÄHLENWENN(BEREICH.VERSCHIEBEN(1:1;ZEILE(A:A););"<>");ZEILE(A:A)+1)
Das funktioniert und gibt korrekten Wert aus.

Wie verwende ich diesen Wert nun im SVERWEIS?
Bin für jeden Tipp dankbar!

Answer 1

Hallo,

du schreibst was von Sverweis und dann kommt eine Formel für Summewenn, was den nun ?


Gruß

Helmut

Answer 2

Okay, fail.

Aber ob ich es in einer SummeWenn- oder einer SVERWEIS-Formel verwende, das Problem bleibt das Gleiche:

wie definiere ich den Datenbereich so, dass (in diesem Bsp.) die 100 durch eine Variable ersetzt wird, die auf die letzte gefüllte Zeile verweist?

Answer 3

Hallo Mimadel,

das musst du mit Indirekt lösen. Siehe z.B. hier.

Beispiel:
Wenn du deine letzte Zeile z.B. in Zelle A1 ermittelst, dann würde die Formel so aussehen:
=SUMMEWENN(INDIREKT("$A$1:$A$"&A1);"Auto";INDIREKT("$B$1:$B$"&A1))

Wenn du die Formel einfügen willst, dann so:
=SUMMEWENN(INDIREKT("$A$1:$A$"&VERWEIS(2;1/ZÄHLENWENN(BEREICH.VERSCHIEBEN(1:1;ZEILE(A:A););"<>");ZEILE(A:A)+1));"Auto";INDIREKT("$B$1:$B$"&VERWEIS(2;1/ZÄHLENWENN(BEREICH.VERSCHIEBEN(1:1;ZEILE(A:A););"<>");ZEILE(A:A)+1)))

Aber Vorsicht! Wenn du es ausprobierst, wirst du sehen, dass sich die Zeit für die Berechnung der Tabelle erheblich erhöhen wird.

Gruß

M.O.

Answer 4

Hi,
klasse, das funktioniert genau so, wie ich es braucht!
Danke für den Tipp!!

Die letzte Zeile ermittle ich separat und weise den Wert über "Namen - definieren" zu; habe jetzt mal eben knapp 800 Zeilen mit dieser Formel versorgt und es ging perfekt und blitzschnell!

Bsp:
=SVERWEIS("Auto";INDIREKT("$A$1:$G$"&LetzteZeile);4;FALSCH)

Vielen Dank nochmal!