Formel verschmelzen! :)

[cysu11]

Hallo zusammen,

ich stehe absolut auf dem Schlauch bei folgender Aufgabe:

Mit =MAX(NICHT(ISTLEER(A2:A1000))*ZEILE(2:1000)) ermittle ich die letzte befüllte Zelle in Spalte A
Mit =ZEICHEN(MAX(WENN(NICHT(ISTLEER(A2:D6));SPALTE(A2:D6)))+64) ermittle ich die letzte befüllte Spalte im Bereich A2:A6 im meine Beispiel kommt hier die Spalte B raus

nun meine Frage, wie kann ich bei der ersten Formel die Spalte A dynamisch durch die Zweite Formel gestalten?

Vielen Dank
LG, Alexandra

[Ego]

Hallo Alexandra,

wenn ich es richtig verstanden habe anstelle von

"A2:A1000"

"INDEX(A2:D1000;;SPALTE)"

PS.
Hierbei ist SPALTE eine Zahl und nicht der Buchstabe.

[cysu11]

Hallo Helmut,

vielen Dank, ich habe das nun so gelöst und es macht was es soll:

=MAX(NICHT(ISTLEER(INDEX(A2:D1000;;MAX(WENN(NICHT(ISTLEER(A2:D11));SPALTE(A2:D11))))))*ZEILE(2:1000))

damit bekomme ich die Zeile ausgegeben, wo das "x" das letzte Mal vorkommt, wenn man von links nach rechts und von oben nach unten durchgeht.

Hiermit bekomme ich die Adresse der Zelle ausgegeben:
=ADRESSE(MAX(NICHT(ISTLEER(INDEX(A2:D1000;;MAX(WENN(NICHT(ISTLEER(A2:D11));SPALTE(A2:D11))))))*ZEILE(2:1000));MAX(WENN(NICHT(ISTLEER(A2:D11));SPALTE(A2:D11)));4)
Hiermit die Nummer:
=INDIREKT("A"&MAX(NICHT(ISTLEER(INDEX(A2:D1000;;MAX(WENN(NICHT(ISTLEER(A2:D11));SPALTE(A2:D11))))))*ZEILE(2:1000)))
Hiermit die Überschrift der dazugehörigen Spalte:
=INDIREKT(ZEICHEN(MAX(WENN(NICHT(ISTLEER(A2:D6));SPALTE(A2:D6)))+64)&"1")

kann man diese formeln etwas kürzen/vereinfachen, damit ich:
1. Die Adresse der letzten Zelle mit x bekomme
2. Die dazugehörige Nummer in Spalte A
3. Die dazugehörige Überschrift in Zeile 1
?

Vielen Dank im Voraus & LG, Alexandra

[BoskoBiati]

Hi,

Verständnisfrage: Wozu braucht man eine Zelladresse?

Dzu gibt es seit vielen Jahren schon Formeln:

https://www.herber.de/excelformeln/src/e...la_list.pl

Und auch für Dich gilt: Mustertabelle!

Wenn es nur um diese kleine Tabelle geht:

Code:

=LET(xa;XVERGLEICH("x";B2:B7;;-1);xb;XVERGLEICH("x";C2:C7;;-1);xc;XVERGLEICH("x";D2:D7;;-1);xd;MAX(xa;xb;xc);xe;SUCHEN(xd;xa&xb&xc);VSTAPELN("ID: "&xd;"Spalte:"&xe;"Überschrift:"&INDEX(B1:D1;xe)))

Für größere Tabellen:

Code:

=LET(xa;MAX(WENN(B2:K100<>"";SPALTE(B:K)))-1;xb;MAX((B2:K500<>"")*ZEILE(2:500))-1;xc;INDEX(B1:D1;xa);VSTAPELN("Spalte: "&xa;"ID: "&xb;"Überschrift: "&xc))

Die Formel muß aber hinter der Spalte K stehen!

[EA1950]

Hallo,

wenn es sich um ein Excel365 handelt, das bereits über die Funktion ABSCHNBEREICH(..) verfügt, stellt sich die Aufgabe, den kleinsten Zellbereich zu ermitteln, der Daten enthält und der sich innerhalb des Bereiches A1:G1000 befindet, ganz einfach dar:

=LET(b;A1:.G1000; HSTAPELN(SPALTEN(b);ZEILEN(b)))
oder mit vorgenannter Funktion
=LET(b;ABSCHNBEREICH(A1:G1000;2;2); HSTAPELN(SPALTEN(b);ZEILEN(b)))

Der Syntaxausdruck A1:.G1000 (mit dem zusätzlichen Punkt nach dem Doppelpunkt) bewirkt, dass von Zeile 1000 bzw. von Spalte G ausgehend, nach oben bzw. nach links gesucht wird, bis ein Bereich mit Daten gefunden wird.
Dasselbe macht auch der Ausdruck ABSCHNBEREICH(A1:G1000;2;2)

[BoskoBiati]

Hi Anton,

leider funktioniert von den hier vorgestellten Formeln nur eine richtig:


  Cysu11.xlsm (Größe: 17,26 KB / Downloads: 1)
In Tabelle2 mal eine veränderte Ausgangssituation, bei 3 der Formeln versagen.
Bitte beachten: es werden nicht die tatsächlichen Spalten-/Zeilennummern angezeigt, sondern die Spalten/Zeilen innerhalb der Tabelle!

Hiermit funktioniert es:

Code:

=LET(xaa;B2:K1000;xa;REST(XVERGLEICH("x";ZUSPALTE(xaa;0;);0;-1);SPALTEN(B:K));xb;XVERGLEICH("x";INDEX(xaa;;xa);0;-1);VSTAPELN("Spalte: "&xa;"ID: "&xb;"Überschrift: "&INDEX(1:1;xa+1)))

[EA1950]

Hallo Edgar,

wieso sollen die Formeln versagen? Ich habe deine Formeln überprüft. Sie funktionieren erwartungsgemäß.

Folgende Formel
=LET(b;B2:.G1000; VSTAPELN("Spalte: "&SPALTEN(b);"ID: "&ZEILEN(b);"Überschrift: "&INDEX(1:1;SPALTEN(b)+1)))

sucht von Zeile 1000 bzw. Spalte G solange nach oben bzw. nach links, bis Daten gefunden werden. Dadurch ergibt sich ein Bereich, der der Variablen b zugewiesen wird. 
Da dein Bereich in B2 beginnt, muss 1 Zeile bzw. 1 Spalte für b abgezogen werden. Wenn der Bereich in A1 beginnen würde, hätte b um 1 Zeile bzw. Spalte mehr. Analoges gilt auch für ABSCHNBEREICH(B2:G1000;2;2).

Vermutlich willst du Folgendes:
=LET(b;A1:.G1000; VSTAPELN("Spalte: "&SPALTEN(b);"ID: "&ZEILEN(b)-1;"Überschrift: "&INDEX(1:1;SPALTEN(b))))
=LET(b;ABSCHNBEREICH(A1:G1000;2;2);x;SPALTEN(b);y;ZEILEN(b)-1;VSTAPELN("Spalte: "&x;"ID: "&y;"Überschrift: "&INDEX(1:1;x)))
Du darfst den Ausgangsbereich A1:G1000 nicht auf B2:G1000 verkleinern, wenn in Spalte A mehr Datenzeilen enthalten sind als in B:G

[BoskoBiati]

Hi Anton,

dann habe ich wohl die Anfrage falsch verstanden. Ich bin davon ausgegangen, dass das am weitesten unten stehende x gesucht wird. Für den Fall funktionieren drei der Formeln nicht korrekt.

Die Geschichte mit dem geänderten Bereich ist mir schon bewußt und eingearbeitet.

Dann warten wir mal auf eine Antwort von Alexandra.

[EA1950]

Hallo,

vermutlich willst du Folgendes:
=LET(b;A1:.G1000; VSTAPELN("Spalte: "&SPALTEN(b);"ID: "&ZEILEN(b)-1;"Überschrift: "&INDEX(1:1;SPALTEN(b))))
=LET(b;ABSCHNBEREICH(A1:G1000;2;2);x;SPALTEN(b);y;ZEILEN(b)-1;VSTAPELN("Spalte: "&x;"ID: "&y;"Überschrift: "&INDEX(1:1;x)))
Du darfst den Ausgangsbereich A1:G1000 nicht auf B2:G1000 verkleinern, wenn in Spalte A mehr Datenzeilen enthalten sind als in B:G

[BoskoBiati]

Hi Anton,

eigentlich will was Anderes:

   

Ich möchte als Ergebnis für dieses Bild  das:

Spalte: 2
ID: 5
Überschrift: Bot

oder auch das:

Spalte: C
Zeile: 6
Überschrift: Bot