Funktion zur Ermittlung der letzten Zeile eines Bereichs

[Fendroun]

Liebe Community,

sorry für den unverständlichen Titel - ohne Erläuterung ist's schwierig. 

Ich habe viele riesige Excel-Dateien, die ich auswerten möchte. Um möglichst viel Aufwand zu sparen, möchte ich Formeln schreiben, die ich in alle neuen Tabellen (die immer gleich aufgebaut sind) kopieren kann. 

Im Bild unten steht in der blauen Zelle (C2) eine 15, da der Datenbereich in Spalte B bis Zeile 15 geht. Der nächste Datenbereich geht bis Zeile 20, deshalb steht in Spalte C die 20 in der ersten Zeile des zweiten Bereichs. Gelb ist eine 24, weil der dritte Bereich bis Zeile 24 geht.

[Edit: Bild wird nicht dargestellt, ist im Anhang. Ich habe die Hinweise zum Bild-Hochladen gelesen, hätte es aber trotzdem an dieser Stelle nützlich gefunden.]

Ist es möglich, eine einzelne Funktion in Spalte C zu schreiben, die diese Zahlen ausgibt? Am Besten ohne VBA; das kann ich auf dem Rechner, auf dem ich es bräuchte, nicht ausführen. Die Funktion sollte in ein anderes Dokument kopiert werden können, von dem man nicht weiß, wie groß die Bereiche in B sind. Ich habe WENN-Funktionen und SPALTE kombiniert, aber befürchte, auf (meine?) Grenzen gestoßen zu sein.

Ich habe zum Basteln ein konstruiertes Beispiel (die realen Datenbereiche sind wesentlich länger und zahlreicher) mit Zufallszahlen angehängt, das ähnlich wie im Bild aussieht.

Wen es interessiert (hat nichts mit der Frage zu tun): Diese Zahlen in Spalte C bräuchte ich dann für viele weitere Berechnungen. So könnte ich mir Beispielsweise immer das Maximum des Bereichs ausgeben lassen, wenn in Spalte C was steht, wobei ich mir dann den Datenbereich für die MAX Funktion mit INDIREKT, ADRESSE und TEXTKETTE zusammenbastel - das kann ich selbst, mir geht's nur um das oben beschriebene Problem.

[UweD]

Hallo

Versuch mal das hier

Formel in C2:   =WENN(B1="";MIN(WENN(B2:B2000="";ZEILE(B2:B2000)))-1;"")


die 2000 habe ich willkürlich gewählt und wäre hier als maximale Anzahl eines zusammenhängenden Blocks anzusehen


LG UweD

[LCohen]

  Datenbereiche.xlsx (Größe: 11,72 KB / Downloads: 16)

[Fendroun]

Hallo

Versuch mal das hier

Formel in C2:   =WENN(B1="";MIN(WENN(B2:B2000="";ZEILE(B2:B2000)))-1;"")


die 2000 habe ich willkürlich gewählt und wäre hier als maximale Anzahl eines zusammenhängenden Blocks anzusehen


LG UweD

Wow, danke! Darauf, Zellbereiche in einer Wenn-Funktion so anzuwenden, bin ich noch gar nicht gekommen.

---

@LCohen:
Vielen Dank!
Das ist eine interessante Lösung, die mir für meinen Zweck zwar nicht hilft, aber ich habe wieder etwas neues gelernt 

[BoskoBiati]

Hi,

als Tipp: Mit INDIREKT arbeiten ist sehr ungünstig.

Das was Du vorhast ginge ganz einfach so:



  Min_Max.xlsx (Größe: 10,87 KB / Downloads: 1)

[snb]

Zahlen in Spalte A

Code:

Sub M_snb()
   With Columns(1).SpecialCells(2, 1)
      ReDim st(.Areas.Count, 1)
      For j = 0 To UBound(st) - 1
         st(j, 0) = Application.Min(.Areas(j + 1))
         st(j, 1) = Application.Max(.Areas(j + 1))
      Next
    End With
    Cells(2, 3).Resize(UBound(st), 2) = st
End Sub