Dopasowanie przybliżone w tabelach progowych

Tabela progowa przyporządkowuje wartości ciągłe do przedziałów. Przykładami są progi podatkowe, opłaty za wysyłkę zależne od masy, rabaty ilościowe oraz oceny literowe zależne od wyniku.

Nie trzeba tworzyć wiersza dla każdej możliwej wartości — przechowuje się tylko wartość początkową każdego przedziału. Wynik 87 nie ma dokładnego wpisu, ale należy do przedziału zaczynającego się od 80.

Właśnie tutaj najlepiej sprawdza się dopasowanie przybliżone: znajduje właściwy przedział, zamiast wymagać dokładnego dopasowania.

Do tej pory używaliśmy funkcji MATCH(value, range, 0) do dopasowania dokładnego. Trzeci argument 0 oznacza „znajdź dokładnie tę wartość albo zwróć #N/A”.

W przypadku progów używamy zamiast tego typu dopasowania 1. Znajduje on największą wartość mniejszą lub równą wyszukiwanej wartości. Dokładnie tak powinno działać wyszukiwanie przedziału.

Jedna ważna zasada: przy typie dopasowania 1 lista progów musi być posortowana rosnąco.

Wyobraźmy sobie tabelę ocen. Kolumna E zawiera dolne progi w kolejności rosnącej: 0, 60, 70, 80, 90. Kolumna F zawiera oznaczenia: F, D, C, B, A.

Wynik od 0 do 59 oznacza F, od 60 do 69 — D i tak dalej. Przechowujemy tylko początek każdego przedziału, a nie każdy wynik.

Naszym celem jest zwrócenie oceny literowej na podstawie wyniku w komórce G1.

Użyj przybliżonego dopasowania MATCH, aby znaleźć przedział, do którego należy wynik. MATCH(G1, E2:E6, 1) dla wyniku 87 wyszukuje największy próg nieprzekraczający wartości 87.

Progi to 0, 60, 70, 80, 90. Największy z nich, który nie przekracza 87, to 80 — znajduje się na pozycji 4. Dlatego MATCH zwraca 4.

Ta pozycja wskazuje właściwy przedział, mimo że liczby 87 nie ma na liście.

Teraz przekaż tę pozycję do INDEX dla kolumny oznaczeń F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) przyjmuje pozycję 4 i zwraca czwarte oznaczenie, „B”.

Wynik 87 zostaje więc prawidłowo przyporządkowany do oceny B. Po zmianie G1 na 95 funkcja MATCH zwróci 5, czyli „A”, a po zmianie na 55 zwróci 1, czyli „F”.

Przybliżone dopasowanie MATCH (typ 1) wymaga kolejności rosnącej w zakresie wyszukiwania. Zakłada, że dane zwiększają się, i zatrzymuje się, gdy tylko przekroczy wyszukiwaną wartość.

Jeśli progi nie są uporządkowane, MATCH może zatrzymać się zbyt wcześnie i zwrócić błędną, ale pozornie poprawną pozycję — bez komunikatu ostrzegawczego. Przed użyciem wyszukiwania progowego zawsze sortuj kolumnę progów od najmniejszej do największej wartości.

XLOOKUP również może wykonywać dopasowanie przybliżone. Jego piąty argument, czyli tryb dopasowania, przyjmuje wartość -1 oznaczającą „dokładne dopasowanie albo następny mniejszy element” — idealne rozwiązanie dla tabel progowych.

Funkcja znajduje największy próg mniejszy lub równy wartości G1 i zwraca odpowiadające mu oznaczenie, bez konieczności używania INDEX. W przypadku wyszukiwania przedziałów jest często czytelniejsza niż INDEX-MATCH.

Przyjrzyjmy się teraz rabatowi ilościowemu. Progi w kolumnie E (zamówiona liczba sztuk) to: 0, 10, 50, 100. Rabaty w kolumnie F: 0%, 5%, 10%, 15%.

• Zamówienie obejmujące 7 sztuk: największy próg nieprzekraczający 7 to 0, pozycja 1, zwracany rabat 0%.
• Zamówienie obejmujące 60 sztuk: największy próg nieprzekraczający 60 to 50, pozycja 3, zwracany rabat 10%.
• Zamówienie obejmujące 200 sztuk: największy próg nieprzekraczający 200 to 100, pozycja 4, zwracany rabat 15%.

Jedna formuła obsługuje każdą liczbę sztuk.

Co się stanie, jeśli wartość będzie mniejsza niż każdy próg? W przypadku przybliżonego dopasowania MATCH nie ma wartości mniejszej lub równej tej wartości, więc MATCH zwraca #N/A.

Aby tego uniknąć, należy upewnić się, że pierwszy próg obejmuje dolną granicę (często jest to 0), albo opakować formułę funkcją IFERROR, aby wyświetlić jasny komunikat, gdy dane wejściowe są poza zakresem.

Zwróć uwagę na następujące pułapki związane z tabelami progowymi:

• Nieposortowane progi: główna przyczyna błędnych, ale niewykrywanych wyników.
• Użycie typu dopasowania 0: wymusza dopasowanie dokładne i zwraca #N/A dla każdej wartości pośredniej.
• Przechowywanie końców przedziałów zamiast ich początków: MATCH typu 1 oczekuje dolnej granicy każdego przedziału, a nie górnej.
• Progi tekstowe: liczby zapisane jako tekst zakłócają porównanie; należy przechowywać je jako liczby.

Można połączyć dopasowanie przybliżone z techniką dwukierunkową. Wyobraźmy sobie koszt wysyłki zależny zarówno od przedziału wagowego (wiersze), jak i od przedziału strefy (kolumny).

Użyj jednego przybliżonego MATCH (typu 1), aby znaleźć wiersz odpowiadający wadze, oraz drugiego, aby znaleźć kolumnę strefy, a następnie przekaż obie pozycje do INDEX. Ponieważ progi na obu osiach są posortowane, każda funkcja MATCH trafi do właściwego przedziału.

W ten sposób INDEX-MATCH-MATCH łączy się z logiką progów, umożliwiając obsługę rozbudowanych tabel stawek.

Sprawdź swoje rozumienie przybliżonego wyszukiwania progów.

W przypadku wyszukiwania progów i przedziałów:

• Przechowuj dolny próg każdego przedziału i sortuj je rosnąco.
• Użyj MATCH(value, thresholds, 1), aby znaleźć pozycję przedziału (największą wartość mniejszą lub równą danym wejściowym).
• Opakuj tę funkcję w INDEX(labels, ...), aby zwrócić oznaczenie przedziału, albo użyj XLOOKUP(..., -1), aby uzyskać ten sam wynik.

Uwzględnij dolną granicę, umieszczając próg 0, albo użyj IFERROR dla danych wejściowych spoza zakresu. Nigdy nie pozostawiaj progów nieposortowanych.