Generische Formel
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Zusammenfassung
Um die engste Übereinstimmung in numerischen Daten zu finden, können Sie mithilfe der ABS- und MIN-Funktionen INDEX und MATCH verwenden. In dem gezeigten Beispiel lautet die heruntergeschriebene Formel in F5:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
wobei Reise (B5: B14) und Kosten (C5: C14) als Bereiche bezeichnet werden.
In F5, F6 und F7 gibt die Formel die Fahrt zurück, deren Kosten 500, 1000 bzw. 1500 am nächsten kommen.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365.
Erläuterung
Im Kern ist dies eine INDEX- und MATCH-Formel: MATCH sucht die Position der nächsten Übereinstimmung, gibt die Position an INDEX weiter und INDEX gibt den Wert an dieser Position in der Trip-Spalte zurück. Die harte Arbeit wird mit der MATCH-Funktion erledigt, die sorgfältig so konfiguriert ist, dass sie der "minimalen Differenz" entspricht:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Schritt für Schritt wird der Suchwert mit MIN und ABS wie folgt berechnet:
MIN(ABS(cost-E5)
Zunächst wird der Wert in E5 abgezogen aus den benannten Bereich Kosten (C5: C14). Dies ist eine Array-Operation. Da der Bereich 10 Werte enthält, ist das Ergebnis ein Array mit 10 Werten wie folgt:
(899;199;250;-201;495;1000;450;-101;500;795)
Diese Zahlen stellen die Differenz zwischen den Kosten in C5: C15 und den Kosten in Zelle E5, 700 dar. Einige Werte sind negativ, da die Kosten niedriger sind als die Zahl in E5. Um negative Werte in positive Werte umzuwandeln, verwenden wir die ABS-Funktion:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
was zurückgibt:
(899;199;250;201;495;1000;450;101;500;795)
Wir suchen nach der engsten Übereinstimmung, daher verwenden wir die MIN-Funktion, um den kleinsten Unterschied zu ermitteln, nämlich 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Dies wird der Suchwert in MATCH. Das Lookup-Array wird wie zuvor generiert:
ABS(cost-E5) // generate lookup array
Dies gibt das gleiche Array zurück, das wir zuvor gesehen haben:
(899;199;250;201;495;1000;450;101;500;795)
Wir haben jetzt das, was wir brauchen, um die Position der nächsten Übereinstimmung zu finden (kleinster Unterschied), und wir können den MATCH-Teil der Formel wie folgt umschreiben:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Mit 101 als Suchwert gibt MATCH 8 zurück, da 101 im Array an 8. Stelle steht. Schließlich ist diese Position in INDEX als Zeilen Argument zugeführt wird, mit der benannten Bereich Reise wie das Array:
=INDEX(trip,8)
und INDEX gibt die 8. Reise im Bereich "Spanien" zurück. Wenn die Formel in die Zellen F6 und F7 kopiert wird, findet sie die engste Übereinstimmung mit 1000 und 1500, "Frankreich" und "Thailand", wie gezeigt.
Hinweis: Wenn es ein Unentschieden gibt, gibt diese Formel die erste Übereinstimmung zurück.
Mit XLOOKUP
Die XLOOKUP-Funktion bietet eine interessante Möglichkeit, dieses Problem zu lösen, da für einen Übereinstimmungstyp von 1 (genaue Übereinstimmung oder nächstgrößere Übereinstimmung) oder -1 (genaue Übereinstimmung oder nächstkleinere Übereinstimmung) keine Daten sortiert werden müssen. Das heißt, wir können eine Formel wie diese schreiben:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Wie oben verwenden wir den absoluten Wert von (cost-E5), um ein Lookup-Array zu erstellen:
(899;199;250;201;495;1000;450;101;500;795)
Dann konfigurieren wir XLOOKUP so, dass nach Null gesucht wird, wobei der Übereinstimmungstyp auf 1 gesetzt ist, um eine genaue Übereinstimmung oder eine nächstgrößere Übereinstimmung zu erzielen. Wir liefern die benannte Range Trip als Return Array, daher ist das Ergebnis wie bisher "Spanien".
