Formuła ogólna
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Podsumowanie
Aby znaleźć najbliższe dopasowanie w danych liczbowych, możesz użyć funkcji INDEKS i PODAJ.POZYCJĘ z pomocą funkcji ABS i MIN. W pokazanym przykładzie formuła w F5 skopiowana w dół to:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
gdzie podróż (B5: B14) i koszt (C5: C14) to nazwane zakresy.
W F5, F6 i F7 formuła zwraca podróż najbliższą kosztem odpowiednio 500, 1000 i 1500.
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Wyjaśnienie
W istocie jest to formuła INDEKS i PODAJ.POZYCJĘ: PODAJ.POZYCJĘ lokalizuje pozycję najbliższego dopasowania, podaje pozycję do INDEKSU, a INDEKS zwraca wartość z tej pozycji w kolumnie Trip. Ciężka praca jest wykonywana za pomocą funkcji MATCH, która jest starannie skonfigurowana, aby dopasować „minimalną różnicę” w następujący sposób:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Biorąc rzeczy krok po kroku, wartość wyszukiwania jest obliczana za pomocą MIN i ABS w następujący sposób:
MIN(ABS(cost-E5)
Najpierw wartość w E5 jest odejmowana od kosztu zakresu nazwanego (C5: C14). To jest operacja tablicowa, a ponieważ zakres zawiera 10 wartości, wynikiem jest tablica zawierająca 10 wartości, takich jak ta:
(899;199;250;-201;495;1000;450;-101;500;795)
Te liczby przedstawiają różnicę między każdym kosztem w C5: C15 a kosztem w komórce E5, 700. Niektóre wartości są ujemne, ponieważ koszt jest niższy niż liczba w E5. Aby zamienić wartości ujemne na wartości dodatnie, używamy funkcji ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
która zwraca:
(899;199;250;201;495;1000;450;101;500;795)
Szukamy najbliższego dopasowania, więc używamy funkcji MIN, aby znaleźć najmniejszą różnicę, która wynosi 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
To staje się wartością wyszukiwania wewnątrz MATCH. Tablica wyszukiwania jest generowana jak poprzednio:
ABS(cost-E5) // generate lookup array
która zwraca tę samą tablicę, którą widzieliśmy wcześniej:
(899;199;250;201;495;1000;450;101;500;795)
Mamy teraz to, czego potrzebujemy, aby znaleźć pozycję najbliższego dopasowania (najmniejszej różnicy) i możemy przepisać część MATCH wzoru w następujący sposób:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Mając 101 jako wartość wyszukiwania, PODAJ.POZYCJĘ zwraca 8, ponieważ 101 znajduje się na ósmej pozycji w tablicy. Na koniec ta pozycja jest wprowadzana do INDEKSU jako argument wiersza, z nazwanym tripem jako tablicą:
=INDEX(trip,8)
a INDEKS zwraca ósmą podróż z zakresu „Hiszpania”. Gdy formuła jest kopiowana do komórek F6 i F7, znajduje najbliższe dopasowanie do wartości 1000 i 1500, „Francja” i „Tajlandia”, jak pokazano.
Uwaga: jeśli jest remis, ta formuła zwróci pierwsze dopasowanie.
Dzięki XLOOKUP
Funkcja XLOOKUP zapewnia interesujący sposób rozwiązania tego problemu, ponieważ typ dopasowania 1 (dokładne dopasowanie lub następny największy) lub -1 (dokładne dopasowanie lub następny najmniejszy) nie wymaga sortowania danych. Oznacza to, że możemy napisać taką formułę:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Jak wyżej, używamy wartości bezwzględnej (koszt-E5) do utworzenia tablicy odnośników:
(899;199;250;201;495;1000;450;101;500;795)
Następnie konfigurujemy XLOOKUP tak, aby szukał zera, z typem dopasowania ustawionym na 1, dla dopasowania ścisłego lub następnego największego. Podajemy nazwany trip zakresu jako tablicę zwrotną, więc wynikiem jest „Hiszpania”, jak poprzednio.
