Excel: Index und Vergleich bei Näherungswerte?
Hi, mal wieder eine Excel Frage:
Folgende Ausgangssituation (vgl. Screenshot) :
In Spalte A stehen unterschiedliche Daten (steigend, aber nicht stetig).
In Spalte B stehen dazugehörige Farben als Zuordnung.
Wenn in Zelle E2 ein Datum eingegeben wird, soll in Zelle E4 die jeweilige Farbe erscheinen. Das bekomme ich mit INDEX/VERGLEICH hin.
Nun aber zum Problem: Wenn in E2 ein Datum eingegeben wird, das nicht in der Spalte A existiert, möchte ich, dass die Zuordnung des Datums angezeigt wird, das am nächsten am Eingabewert liegt (egal ob größer oder kleiner). Wird zB der 11.01.2021 angegeben, soll als Zuordnung "grün" erscheinen, also der Wert vom 12.01.2021. Wird der 03.01.2021 eingegeben, wäre es egal, ob die Zuordnung vom 01.01.2021 oder vom 05.01.2021 ausgegeben wird.
Gibt es hierfür eine Formel - Lösung ohne zusätzliche Makros?
Vielen Dank schonmal!
5 Antworten
Teste diese Formel bitte mal ausführlich:
=WENNFEHLER(INDEX(B2:B5;VERGLEICH(E2;A2:A5;0));WENN(E2-INDEX(A2:A5;VERGLEICH(E2;A2:A5;1))<INDEX(A2:A5;VERGLEICH(E2;A2:A5;1)+1)-E2;INDEX(B2:B5;VERGLEICH(E2;A2:A5;1));INDEX(B2:B5;VERGLEICH(E2;A2:A5;1)+1)))
Klappt es? Wenn nicht, welche Fehler treten auf?
Ja, alle Fehlermöglichkeiten hatte ich noch nicht einbezogen.
Sollte sich SO "reparieren" lassen:
=WENN(E2>MAX(A2:A5);INDEX(B2:B5;VERGLEICH(MAX(A2:A5);A2:A5;0));WENNFEHLER(INDEX(B2:B5;VERGLEICH(E2;A2:A5;0));WENN(E2-INDEX(A2:A5;VERGLEICH(E2;A2:A5;1))<INDEX(A2:A5;VERGLEICH(E2;A2:A5;1)+1)-E2;INDEX(B2:B5;VERGLEICH(E2;A2:A5;1));INDEX(B2:B5;VERGLEICH(E2;A2:A5;1)+1))))
Gibt aber immer noch einen Fehler, wenn das Datum VOR a2 liegt. Da müsste man dann noch mal was entsprechendes drumherum basteln.
Excel macht das, was Du vorgibst / erwartest.
Es gibt zwar die Formel "Oder", dies hat aber nicht zur Folge, dass, wie von Dir gewünscht sowohl das eine mögliche Ergebnis als auch das andere mögliche Ergebnis in einer Zelle angezeigt wird.
Du kannst es mit der Funktion "Und" versuchen. eine Hilfsspalte anliegen die von 1 bis xxx nummeriert ist und mit "kleiner / größer" als die Nummer, die zur aktuellen Auswahl ist.
Danke, aber es geht eigentlich gar nicht um das entweder/oder. Das hab ich nur dazu geschrieben um zu verdeutlichen, dass mir hier egal wäre, was für ein Ergebnis ausgegeben wird.
Der Punkt, an dem ich hänge ist, wie ich die index/Vergleiche Formel anpassen kann, dass bei der Eingabe eines nicht existenten Datums automatisch die Zuordnung des Datums ausgegeben wird, das dem Eingabedatum am nächsten liegt.
Du gibst bei vergleich() als drittes einfach eine 1 anstatt eine 0 ein. möglicherweise musste noch 1 addieren oder subtrahieren, musste ausprobieren. vergleiche Rübezahls Beitrag
z.B.so
eine Hilfsspalte um die Nähe des gesuchten Datums zu ermitteln
per SVerweis den gesuchten Wert anzeigen lassen
Die Bezüge der Zeiten / Auflistungen in der Formel bezüglich der Spalten B; C und D entsprechend anpassen / erweitern.
Es wird zunächst das "gesuchte" Datum ermittelt, wenn es nicht vorhanden ist, das dem am naheliegendsten.
und A2 immer mit "1" beginnen.
Danke ich habs jetzt mit der Formel von Oubyi hinbekommen. Aber auch Deinen Tipp mit sverweis in einer Hilfsspalte merk ich mir, kann man immer wieder mal brauchen! Danke!
Hast du schon mal die sverweis-Formel ausprobiert? In dieser Formel kann man mit dem letzten Parameter wahr / falsch steuern, ob der nächstliegende oder der exakte Vergleichswert genommen werden soll.
Also in E4 die Formel
=sverweis(E2;A:B;2;wahr) oder
=sverweis(E2;A:B;2;falsch)
Probier mal aus, was davon deine Anforderungen erfüllt.
Danke. Hier müsste ich aber für jedes erdenkliche Datum eine entsprechende Formel einbauen, oder?
Nein, wieso?
In dieser Formel wird das in Zelle E2 eingegebene Datum mit allen Datumswerten in Spalte A verglichen und bei Übereinstimmung wird der danebenstehende Eintrag aus Spalte B übernommen.
Ok, da muss ich ein wenig rumprobieren. Vielen Dank!!
Was passiert denn, wenn in E2 ein Datum eingegeben ist und du meine Formel in Zelle E4 eingibst?
Habs nun mit dem Tipp von Oubyi hinbekommen. Sverweis in ner Hilfsspalte kann ich aber gut in nem anderen Fall brauchen! Danke nochmal für die schnelle Hilfe!
Wow, vielen Dank.
Befindet sich das eingegebene Datum innerhalb dem Bereich a2:a5, funktioniert die Formel. Sollte aber ja mit 2 Wenn-Formeln ausbaubar sein,oder (Also wenn Eingabedatum größer als das letzte Datum, dann letztes Datum, sonst diese Formel).?
Jetzt muss ich aber erstmal genau analysieren, was hier genau gemacht wurde. Aber das bekomme ich hin. Vielen Dank!!