Excel trotz formel in weiteren Zeilen die Zeilen als leer ansehen

...komplette Frage anzeigen

3 Antworten

Wenn die Formel in z.B. J100 steht, kannst du die Bereiche statt der ganzen Spalte J.J auf J1:J99 eingrenzen.

mit der einen formel funktioniert es auch nicht. habe jetzt in einer beispieldatei mal versucht mein problem näher zu definieren. ich möchte in E2 die "12" stehen haben auch wenn ich in B die Formel schon weiter runter ziehe. auch mit "ausblenden" der 0 hat es nicht geklappt

Bsp datei - (Excel, Formel)

ich hoffe man kann es noch erkennen

0
@Rara1990

mit welcher "einen Formel" funktioniert es nicht (bzw mit welchen vielen Formeln?)
(Die Zuordnung zur Antwort ist nicht gegeben!)

0

Das geht mit einer Matrixformel. Die letzte beschriebene (auch mit Formeln, die "" ergeben) in J:J erhältst Du mit

{=MAX(NICHT(ISTLEER(J:J))*ZEILE(J:J))}

Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken! - - -

Willst Du die Adresse dieser Zelle, dann

{=ADRESSE(MAX(NICHT(ISTLEER(J:J)) * ZEILE(J:J));10)}

mit Indirekt() siehst Du in dem Fall zwar nichts, aber die Formel heißt:
{=INDIREKT("J"&MAX(NICHT(ISTLEER(J:J)) * ZEILE(J:J));10)}
oder
{=INDIREKT(ADRESSE(MAX(NICHT(ISTLEER(J:J)) * ZEILE(J:J));10))}

mit der von Dir verwendeten Index()-Formel schließlich

{=INDEX(J:J;MAX(NICHT(ISTLEER(J:J)) * ZEILE(J:J)))}

alles Matrixformeln!
Dabei spielt es keine Rolle, ob sich darüber auch leere Zellen befinden (im Gegensatz zu Anzahl2() !.

hab mir Deine Frage nochmal durchgelesen, könnte sein, dass Du was anderes willst:
die letzte beschriebene Zelle soll das heissen: die letzte Zelle, in der was sichtbar ist bzw. die letzte Zelle, in der ein Text oder eine Zahl steht?
Da muss ich erst nochmal üben!

0
@Iamiam

Letzteres geht nur mit einer MakroFunktion:

Public Function UnterstenFestwertAbsFinden(einSpaltBereich As Range) Dim c, Notierung For Each c In einSpaltBereich If Not IsEmpty(c) Then If Not c.HasFormula Then Notierung = c.Row Next UnterstenFestwertAbsFinden = Notierung End Function

Die Formel rufst du auf wie jede andere auch: =UnterstenFestwertAbsFinden(J:J)

Anweisung zur Installation eines Makros:

Klicke mit der RMT auf die Blattnamensfahne(hängt unten am Blatt) und wähle aus dem Kontextmenü "Code anzeigen". Es öffnet sich der VBA-Editor. Im Menü EINFÜGEN wählen: MODUL, [Es erscheint ein (fast) leeres Makroblatt (wahrscheinlich steht oben Option Explicit, das lässt Du drin stehen und gehst mit dem Cursor unterhalb rein).] Kopiere da hinein den obigen Sub:...-Text (heisst Sub, weil es xl untergeordnet ist, Abk. f. Subroutine, auch mit Unterschleife übersetzbar) _ Geh ins Blatt zurück. Mit

Alt+F8

erhältst Du eine Liste der verfügbaren Makros. Bei Fehlfunktion (zB, wenn sichs aufhängt, also endlos läuft): Esc.

Speichere diese und alle anderen offenen Dateien jedesmal, bevor Du das Makro startest, es könnte sein, dass was schief geht oder es sich auch mit Esc nicht mehr stoppen lässt!

in diesem Fall musst Du mit dem "Affengriff" Strg+Alt+Entf(=del) den Task-mgr ...

Eine Funktion wird ganz genauso installiert.
(zu früh losgechickt, muss Schluss machen!

0
@Iamiam

Bei Makro-Funktionen ist das nicht ganz so kritisch. Bei mir verlangt VBA jedesmal nach einer Datei, gibt sich aber dann doch mit einem ok zufrieden und die Formel funktioniert.

Eine gute Anweisung zur Installation eines Makros/einer Funktion kam mal von Suboptimierer in

www.gutefrage.net/frage/formel-problem-bei-ms-excel
(als Kommentar ziemlich weit unten in der hilfreichsten Antwort)

0
@Iamiam

noch die Einbindung in Deine Index-Funktion:

=INDEX(J:J;UnterstenFestwertAbsFinden(J:J))

(ist keine Matrixfunktion mit { }, obwohl Index selbst natürlich unter dem Begriff Matrixfunktionen eingruppiert ist)

nimmst Du =INDEX(J:J;UnterstenFestwertAbsFinden(J3:J20)), dann wird nur der Suchbereich eingegrenzt, die Ausgabe der Zeile bleibt gleich
(deshalb das Abs im Formelnamen). Bei Nicht index(J:J) sondern zB

=INDEX(J3:J20;UnterstenFestwertAbsFinden(J3:J20))

bekommst Du dann ein falsches Ergebnis! (um 2 Zeilen verschoben!), trotzdem denke ich, ist es so sinnvoller programmiert, darüber ließe sich freilich streiten.

und bedenke: auch =5 etc ist eine Formel, oder =1=1 (WAHR)

0

habe die formel versucht und bin gescheitert. unten habe ich mal ein bild eingefügt wo hoffentlich deutlicher wird was ich will

0
@Rara1990

Da ist mal wieder ein Kommentar verlorengegangen: Das Bild kann ich leider nicht entziffern Du musst MINDESTENS Zoom 150% (besser 167 oder 200 einstellen zum Kopieren)
Dem Formelcode fehlen die Umbrüche, ohne funktioniert er nicht:
Public Function UnterstenFestwertAbsFinden(einSpaltBereich As Range)
Dim c, Notierung
For Each c In einSpaltBereich
If Not IsEmpty(c) Then If Not c.HasFormula Then Notierung = c.Row
Next
UnterstenFestwertAbsFinden = Notierung
End Function

Die Einbindung in eine Index-Funktion hab ich oben schon beschrieben.

0

Was möchtest Du wissen?