Kleinsten Zeile mit einem Wert außer Null finden

[Samson1964]

Meine Formel für die größte Zeile:

Code: Alles auswählen

{=INDIREKT("A"&MAX((ZEILE(E15:E1000)*(E15:E1000<>""))))}

In der Matrix E15:E1000 suche ich nach dem letzten Wert größer 0 (bzw. "") und gebe die Zeilennummer zurück. Wird z.B. in E20 der größte Wert gefunden (MAX gibt 5 zurück), lade ich den Wert aus A20 (A15+5) mittels INDIREKT.

Jetzt brauche ich aber eine Funktion für die niedrigste Zeilennummer.

Code: Alles auswählen

{=INDIREKT("A"&MIN((ZEILE(E15:E1000)*(E15:E1000<>""))))}

funktioniert nicht, weil immer 0 zurückgeliefert wird, wenn E15 leer ist.

Eine Alternativformel

Code: Alles auswählen

=INDEX(A15:A1000;VERGLEICH ("[:alnum:]?";E15:E1000;0))

ist in Calc unbrauchbar, weil es entweder in der INDEX- oder in der VERGLEICH-Funktion einen Bug in Calc gibt. Grundsätzlich funktioniert aber die letzte Formel.

[theindless]

Hallo,

mal aus der Hüfte und ungeprüft.

Ergänze doch die MIN-Formel um eine Bedingung <>0

also

{=INDIREKT("A"&MIN((ZEILE(E15:E1000)*(E15:E1000<>"")*(E15:E1000<>0))))}

[Samson1964]

Code: Alles auswählen

{=MIN((ZEILE(E15:E1000)*(E15:E1000<>"")*(E15:E1000<>0)))}

liefert 0, wodurch INDIREKT einen Referenzfehler auswirft. In E15 bis E1000 sind die Zellen E16, E18, E20 und E21 mit Zahlen gefüllt. Alle anderen Zellen sind leer. Ich möchte das E16 gefunden wird, MIN also 2 ausgibt.

[theindless]

Hallo Samson,

nach diversen Versuchen habe ich eine Formel, welche bei mir funktioniert:

Code: Alles auswählen

=INDIREKT("E"&VERGLEICH(MIN(E15:E1000);E15:E1000)+14)

VERGLEICH(MIN(E15:E1000);E15:E1000) ermittelt die Zeile mit dem geringsten Wert (wie bei dir E16), also Zeile = "2"

INDIREKT("E"&2+14) = E16

Probier mal

[Karolus]

Hallo
Die letzte Zelle aus A15:A1000 die in Spalte E nicht leer ist:

=INDEX(A15:A1000;VERGLEICH(".*";E15:E1000;-1))

und dito die erste Zelle:

=INDEX(A15:A1000;VERGLEICH(".*";E15:E1000;0))

Karo

[Samson1964]

@theindless: Deine Formel funktioniert - solange ich nicht den Wert in Zelle E16 lösche bzw. der Wert in der kleinsten Zeile auch der kleinste Wert ist...
Entferne ich diesen wirft Calc den Fehler #NV aus.

@Karolus: Deine Formel funktioniert - solange ich die Tabelle nicht kopiere. In der kopierten Tabelle ist die Funktion funktionslos. Anscheinend bezieht sie sich immer auf die Werte in der Ursprungstabelle. Wahrscheinlich ein Bug in den Funktionen INDEX oder VERGLEICH - siehe mein erstes Posting.

[theindless]

Hallo Samson,

der Fehler #NV entsteht dadurch, dass die Werte nicht aufsteigend sortiert sind.
Dies lässt sich wie folgt korrigieren:

Code: Alles auswählen

=INDIREKT("E"&VERGLEICH(MIN(E15:E1000);E15:E1000;0)+14))

Nun würde nur noch ein Fehler #NV ausgegeben, wenn keinerlei Werte in E15:E1000 stehen.
Dies lässt sich über ISTFEHLER abfangen.

Code: Alles auswählen

=WENN(ISTFEHLER(INDIREKT("E"&VERGLEICH(MIN(E15:E1000);E15:E1000;0)+14));"";INDIREKT("E"&VERGLEICH(MIN(E15:E1000);E15:E1000;0)+14))

Ich hoffe so funktionierts für dich!

[Samson1964]

@theindless:
Ich möchte nicht den kleinsten Wert finden, sondern die kleinste Zeile mit einem Wert ungleich 0 oder "".
Beispiel: In E16, E18, E20, E21 stehen 7, 5, 3 und 6
Deine Formeln spucken immer 3 aus. Ich muß aber den Wert 7 finden - also den ersten Wert!

[theindless]

Hallo Samson,

ich habe mich nochmal dran versucht...

Code: Alles auswählen

=INDIREKT("E"&VERGLEICH(1;ISTZAHL(E15:E1000);0)+14)

WICHTIG! Als Matrixformel abschliessen!

Hoffe ich habe das richtig umgesetzt!

[Karolus]

Hallo

@Karolus: Deine Formel funktioniert - solange ich die Tabelle nicht kopiere. In der kopierten Tabelle ist die Funktion funktionslos. Anscheinend bezieht sie sich immer auf die Werte in der Ursprungstabelle. Wahrscheinlich ein Bug in den Funktionen INDEX oder VERGLEICH - siehe mein erstes Posting.

Ich habe keine Idee wieso das nicht funktionieren sollte - hier tut es.
Vielleicht solltest du mal exakt beschreiben wie du kopierst und wohin ...

@Theindless: Vergiss für diesen Zweck einfach mal INDIREKT - INDIREKT ist im Vergleich zu INDEX oder VERSCHIEBUNG ineffizient.
ISTZAHL erfasst das Problem ebenfalls nicht - Samson möchte die erste/letzte Zelle die nicht leer ist.



Karo

[theindless]

Hallo Karo,

auch wenn dir meine Formel nicht gefällt...
nochmal angepasst daran, dass die Zellen nicht unbedingt Zahlen enthalten müssen!

Code: Alles auswählen

=INDIREKT("E"&VERGLEICH(0;ISTLEER(E15:E1000);0)+14)

Aber ich werde mir auch noch über INDEX und VERSCHIEBUNG Gedanken machen.
Lern ich ja noch was dabei...

[Samson1964]

@theindless: Jetzt funktioniert's - mit der Formel, als auch mit dem Tabellekopieren. Vielleicht liegt der Kopierfehler nicht bei der VERGLEICH- sondern bei der INDEX-Funktion.

Danke!

[theindless]

EDIT: Vergesst es.... dumme Zahlen - Bezug - Übereinstimmung!

[theindless]

Ausgehend von Karo's Formel könnte man meine Formel noch vereinfachen:

Code: Alles auswählen

=INDIREKT("B"&VERGLEICH(".*";B15:B1000;0)+14)

Wieder was dazugelernt! Danke Karo!