Letzte Zeile/Zelle eines definierten Bereiches

[joshua]

Hey Leute!

Wie man die letzte Zeile eines Bereiches mittels VBA bestimmt oder mittels kombinierter Funktion ist mir bewusst.
Wie kann ich jedoch anhand eines definierten Kriteriums(dynamisch mittels Dropdownliste), also eines dynamischen Bereiches, jeweils die letzte Zelle einer bestimmten Spalte bestimmen?

Also die tabelle hat folgende Struktur:

X 5
X 5
X 6
X 6
X 6
X 6
X 7
Y 2
Y 4
Y 6
Y 8
Y 5
H 8
H 1
H 2

Die Buchstaben stellen das Kriterium für den Bereich dar, wovon jeweils die letzte Zelle definiert werden soll

X=7
Y=5
H=2

Wie ist dies handzuhaben, ohne eine weitere Hilfsspalte?
Freue mich über jeden Beitrag.


Grüße!

[Jonas0806]

Hallo,

meinst Du so?

PHP-Code:

=INDEX(C:C;MAX(INDEX((B1:B130=F6)*ZEILE(1:130);))) 


[WillWissen]

Hi,

meinst du das so?

Wert=

Code:

=VERWEIS(2;1/(B:B=$F$6);C:C)

[neopa]

Hallo joshua.

beide vorgeschlagene Formeln führen in Deiner Beispieldatei zum richtigen Ergebnis. Allerdings sollte man eine Matrixfunktion(alität)sformel wie die VERWEIS()-Formel möglichst nie über den gesamten Spaltenbereich auswerten (also den Datenvergleich) lassen, weil das nur unnötig Ressourcen bindet. Die INDEX(MAX()) greift dagegen zu kurz, wenn z.B. in F6 das "x" gelöscht wird.

Deshalb erstelle aus Deinen Daten in A:C mittels der Funktion "Als Tabelle formatieren" eine intelligente Tabelle.

Dann brauchst Du Dich bei Datenerweiterung nicht mehr selbst um die richtige Definition des auszuwertenden Bereiches kümmern.

Dann würde die VERWEIS()-Formel z.B. so geschrieben: =VERWEIS(9;1/(Tabelle1[Kriterium]=F6);Tabelle1[Zahl])

und die INDEX()-Formel dann entsprechend so: =INDEX(C:C;MAX(INDEX((Tabelle1[Kriterium]=F6)*ZEILE(Tabelle1[Kriterium]);)))

[joshua]

Vielen Dank für diesen schönen Ansatz (:

Ich glaube damit würde ich mir eine menge Zeit sparen.
Aber was ist, wenn es Spalten bzw. Überschriften gibt, welche den gleichen Titel tragen? Diese können leider nicht geändert werden.

[neopa]

Hallo joshua,

Aber was ist, wenn es Spalten bzw. Überschriften gibt, welche den gleichen Titel tragen? Diese können leider nicht geändert werden.

Unterschiedliche Daten sollten im Prinzip "von Haus aus" nicht mit den gleichen Titel/ Überschriften versehen werden. Wenn eine derartige Datenliste vor der Benutzung "Als Tabelle formatieren" trotzdem solche hat (z.B.: Datum Werte Werte Werte ...)  dann werden diese durch Excel mit der Anwendung dieser Funktion automatisch umbenannt (im Beispiel in: Datum Werte Werte2 Werte3 ...).
Du hast danach immer noch die Möglichkeit die Überschriften neu um zu benennen. Aber auch dabei muss gewährleistet bleiben, dass keine Überschrift doppelt vorkommt.

[joshua]

PHP-Code:

=INDEX(C:C;MAX(INDEX((B1:B130=F6)*ZEILE(1:130);))) 


Vielen Dank für diese Lösung!
Sie funktioniert prima (:


Wie schaut es jedoch aus, wenn ich die erste Zahl, also MIN haben möchte?
So trivial wie hier:

Code:

=INDEX(C:C;MIN(INDEX((B1:B130=F6)*ZEILE(1:130);)))

funktioniert leider nicht

[steve1da]

Hola,

edit: gelöscht.

Gruß,
steve1da

[Jonas0806]

Hallo,

z.B.

PHP-Code:

=INDEX(C:C;AGGREGAT(15;6;ZEILE(1:130)/(B1:B130=F6);1)) 


[neopa]

Hallo joshua,

Wie schaut es jedoch aus, wenn ich die erste Zahl, also MIN haben möchte?

hierzu reicht AGGREGAT() allein völlig aus.

Für eine formatierte Tabelle so: =AGGREGAT(15;6;Tabelle1[Zahl]/(Tabelle1[Kriterium]=F6);1)

Für eine "unformatierte" Liste z.B. so: =AGGREGAT(15;6;C2:C999/(B2:B999=F6);1)