Excel - Ähnliche Werte in Spalte suchen und Zeile ausgeben
Hallo,
ich möchte in einer Spalte mit stetig steigenden Zahlen genau die Zeilennummer finden, in der die Zahl steht, die zu meiner vorgebenen die kleinste Abweichung aufweist. Mit der Funktion VERGLEICH() erhalte ich leider nur die nächst Kleinere, obwohl evtl. die nächst Größere einen geringeren Abstand aufweist. Also im Beispiel liefert Vergleich() mir Zeile "7" obwohl ich Zeile "8" gesucht habe.
Existiert dafür eine fertige Excel-Funktion?
2 Antworten
Das müsste Dir die richtige Zeilennummer liefern:
=wenn(
index(A:A;Vergleich(123;A:A;1);1)-index(A:A;Vergleich(123;A:A;1)-1;1)>
index(A:A;Vergleich(123;A:A;1)+1;1)-index(A:A;Vergleich(123;A:A;1);1);
Vergleich(123;A:A;1);
Vergleich(123;A:A;1)+1)
relativ langatmige Formel, aber trotzdem nicht schwer zu verstehen. Anstatt 123 natürlich auch ein Bezug mit der gewünschten Zahl.
Und die Spalte natürlich anpassen.
Kannst Du A:A nicht brauchen (weil Du die Zahl für die Suche unbedingt in der gleichen Spalte oberhalb haben MUSST), musst Du eben zB A5:A99 schreiben und dann die vier Zellen davor mit jeweils +4 Kompensieren.
Wenn ich das richtig sehe (Trockenübung) "schaut" Deine Formel nicht, welcher Wert am nächsten an 123 ist, sondern bei welchem Wert in der "Nähe" von 123 das Intervall zum nächsten Wert der Werteliste am kleinsten ist.
Oder "lese" ich das falsch?
Man müsste doch (so wie ich (:o)) die beiden Differenz zum Suchwert, also zu 123 berechnen und DAVON die kleiner nehmen.
@Oubyi: Bin bei der ersten Formel davon ausgegangen, dass 123 existiert, entweder als Wert oder über einen Bezug. Und ich hätte das Typ-Attribut 0 setzen müssen für den exakten Wert. dann müsste die Formel stimmen. Ich muss da immer erst rumprobieren und dann hatte ich dafür eine fallende Liste: VON HINTEN DURCH DIE BRUST INS AUGE!
aber er hat ja jetzt Deine Formel, kam fast gleichzeitig!
Hab aber mittlerweile ohnehin eine Formel für einen nicht existenten Wert kreiert. Da war für mich der Screenshot missverständlich, ob 9 als Wert oder als Zeilennummer. Und ich sehe, Du hast auch nochmal dran geschliffen!
Wollt ich auch nochmal, bin aber nicht dazu gekommen: so in diesem Stil:
=Vergleich(...)+wenn(index(...)-index(...)>index()...)-index(...);1;-1)
Aber doch eine Bestätigung, dass wir beide zu sehr ähnlichen Lösungen kommen!
@Selachiii:
wird mit einem festen Wert sogar ein wenig kürzer: bei steigenden Listen:
=wenn(
Wert-index(A:A;Vergleich(Wert;A:A;1);1)>
index(A:A;Vergleich(Wert;A:A;1)+1;1)-Wert;
Vergleich(Wert;A:A;1)+1;
Vergleich(Wert;A:A;1))
sprich: ist die Differenz zum kleineren Nachbarn(oberhalb) größer als die zum größeren Nachbarn(unterhalb), dann nimm den größeren Nachbarn unterhalb, sonst den kleineren oberhalb.
Bei fallenden Listen muss dann/sonst andersrum gebildet werden und das Typ-Attribut in Vergleich(was;wo;-1) gesetzt werden.
(ich muss das auch immer erst ausprobieren!)
Ich denke, das müsste so klappen:
=WENN(B11-INDEX(A1:A10;VERGLEICH(B11;A1:A10;1))<(INDEX(A1:A10;VERGLEICH(B11;A1:A10;1)+1))-B11;VERGLEICH(B11;A1:A10;1);VERGLEICH(B11;A1:A10;1)+1)
Teste mal genau.
Ich schau aber nochmal, ob mir nicht was einfacheres einfällt.
Ich wusste doch, es geht auch kompakter.
Diese Formel hier sollte genauso klappen:
=VERGLEICH(B11;A1:A10;1)+(B11-INDEX(A1:A10;VERGLEICH(B11;A1:A10;1))>(INDEX(A1:A10;VERGLEICH(B11;A1:A10;1)+1))-B11)
Test aber auch nochmal genau.
Klappt es?
hab mir wieder mal zu spät Deinen Screenshot angeschaut.
Meine Formel liefert Dir die jeweils größere Zeile (als Basis für die Differenzbildung). Du scheinst aber alternativ zur größeren die kleinere Zeile zu suchen. Dann heißt das Wenn-Argument (vorletzte Zeile der Formel):
Vergleich(123;A:A;1)-1;
Ist 9 aber ein Wert (und keine Zeilennummer), der gesucht wird, der aber nicht exakt vorhanden ist, muss ich mir nochmals Gedanken machen! (also: suche die Zeile, deren Wert am nächsten bei 123 liegt, aber 123 gibts in der Liste nicht, sondern nur 122 und 125)