Seite 1 von 1
Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 09:34
von 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.
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 10:04
von 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))))}
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 10:16
von 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.
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 10:37
von theindless
Hallo Samson,
nach diversen Versuchen habe ich eine Formel, welche bei mir funktioniert:
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
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 10:40
von 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
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:02
von 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.
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:09
von 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!
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:23
von 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!
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:48
von theindless
Hallo Samson,
ich habe mich nochmal dran versucht...
WICHTIG! Als Matrixformel abschliessen!
Hoffe ich habe das richtig umgesetzt!
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:54
von 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
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 11:59
von theindless
Hallo Karo,
auch wenn dir meine Formel nicht gefällt...
nochmal angepasst daran, dass die Zellen nicht unbedingt Zahlen enthalten müssen!
Aber ich werde mir auch noch über INDEX und VERSCHIEBUNG Gedanken machen.
Lern ich ja noch was dabei...
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 12:03
von 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!
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 12:08
von theindless
EDIT: Vergesst es.... dumme Zahlen - Bezug - Übereinstimmung!
Re: Kleinsten Zeile mit einem Wert außer Null finden
Verfasst: Di, 17.04.2012 12:16
von theindless
Ausgehend von Karo's Formel könnte man meine Formel noch vereinfachen:
Wieder was dazugelernt! Danke Karo!