Dopasowanie dokładne a przybliżone

Ostatni argument funkcji VLOOKUP, czyli range_lookup, określa sposób działania wyszukiwania. Jest niewielki, ale ma duże znaczenie:

• FALSE (lub 0) oznacza dopasowanie dokładne
• TRUE (lub 1) oznacza dopasowanie przybliżone

Wybranie niewłaściwej wartości jest jednym z najczęstszych błędów w arkuszach kalkulacyjnych. W tej lekcji dokładnie wyjaśniono, kiedy należy używać każdej z nich.

Dopasowanie dokładne wyszukuje wartość, która pasuje dokładnie. Jeśli danej wartości nie ma w tabeli, funkcja VLOOKUP zwraca błąd #N/A zamiast zgadywać.

Wartości FALSE należy używać podczas wyszukiwania unikatowych identyfikatorów, takich jak kody produktów, identyfikatory pracowników czy adresy e-mail, gdy poprawne jest tylko idealne dopasowanie.

W przypadku dopasowania dokładnego tabela nie musi być posortowana. Funkcja VLOOKUP przeszukuje ją aż do znalezienia wartości.

W przypadku naszej tabeli cen (A100 Apple 0.50, B200 Banana 0.30, C300 Cherry 1.20) dokładne wyszukanie istniejącego kodu działa bez problemu:

Zwraca wartość 1.20. Jeśli jednak wyszukają Państwo kod, którego nie ma w tabeli, na przykład "Z999", otrzymają Państwo błąd #N/A. Ten błąd jest w rzeczywistości przydatny: informuje, że elementu naprawdę brakuje, zamiast zwracać dane z niewłaściwego sąsiedniego wiersza.

Dopasowanie przybliżone wyszukuje największą wartość, która jest mniejsza lub równa szukanej wartości. Jest przeznaczone do zakresów i przedziałów, a nie do dokładnych identyfikatorów.

Typowym zastosowaniem jest tabela progów: przedziały podatkowe, stawki wysyłki, progi ocen lub rabaty ilościowe, gdy wartość mieści się między dwoma progami.

Z wartością TRUE wiąże się jedna niezwykle ważna zasada, omówiona w następnej części.

Aby dopasowanie przybliżone działało poprawnie, pierwsza kolumna musi być posortowana rosnąco (od najmniejszej do największej wartości). Funkcja VLOOKUP przechodzi w dół kolumny i zatrzymuje się na ostatniej wartości, która nie przekracza szukanej wartości.

Jeśli kolumna nie jest posortowana, TRUE zwraca nieprzewidywalne, błędne wyniki bez żadnego komunikatu. To ciche niepowodzenie sprawia, że wiele osób unika wartości TRUE, chyba że rzeczywiście potrzebuje przedziałów.

Załóżmy, że tabela ocen znajduje się w zakresie A1:B5 i jest posortowana rosnąco według minimalnego wyniku:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Wynik 76 powinien zwrócić ocenę C, ponieważ 76 mieści się w przedziale 70–79. Formuła używa wartości TRUE, aby znaleźć najwyższy próg, który nie przekracza 76:

Dla wartości wyszukiwania 76 i TRUE funkcja VLOOKUP odczytuje kolejne posortowane progi: 0, 60, 70, 90... Porównuje każdy z nich.

• 0 jest mniejsze lub równe 76: należy szukać dalej
• 60 jest mniejsze lub równe 76: należy szukać dalej
• 70 jest mniejsze lub równe 76: należy szukać dalej
• 90 jest większe od 76: należy się zatrzymać

Funkcja wraca do ostatniego poprawnego wiersza (70) i zwraca przypisany do niego przedział: C. Dopasowanie przybliżone to w praktyce wyszukiwanie między progami.

Jeśli całkowicie pominą Państwo czwarty argument, funkcja VLOOKUP domyślnie przyjmuje wartość TRUE (dopasowanie przybliżone). Zaskakuje to wiele osób, które oczekują dopasowania dokładnego.

Formuła taka jak =VLOOKUP(A2, Data!A:B, 2) użyta na nieposortowanej liście może po cichu zwrócić błędną wartość. Bezpieczny nawyk to zawsze wpisywać FALSE, chyba że celowo dopasowują Państwo wartości do przedziałów w posortowanej tabeli.

Oto najważniejsze różnice:

• FALSE / dokładne: tabela nie musi być posortowana, brakująca wartość zwraca #N/A, najlepsze rozwiązanie dla identyfikatorów i kodów
• TRUE / przybliżone: tabela musi być posortowana rosnąco, dla wartości mieszczących się w zakresie nigdy nie zwraca #N/A, najlepsze rozwiązanie dla progów i przedziałów

Wybór zależy od pytania, na które chcą Państwo odpowiedzieć: „czy ten konkretny element istnieje?” oznacza użycie FALSE, a „do którego przedziału należy ta wartość?” — użycie TRUE.

Waga w komórce D2 wymaga obliczenia kosztu wysyłki na podstawie posortowanej tabeli przedziałów w zakresie A2:B6 (progi 0, 1, 5, 10 i 20 kg). Dopasowanie przybliżone wybiera właściwy przedział:

Jeśli D2 ma wartość 7, waga trafia do przedziału 5 kg, a funkcja zwraca koszt przypisany do tego przedziału. Po zmianie wagi przedział zostaje natychmiast zaktualizowany — nie trzeba tworzyć listy każdej możliwej wagi.

Istnieje również subtelny aspekt wydajności. W przypadku bardzo dużych, posortowanych tabel dopasowanie przybliżone (TRUE) może działać szybciej, ponieważ arkusz może szybko przeskakiwać między posortowanymi wartościami zamiast przeszukiwać każdy wiersz.

Szybkość nigdy jednak nie jest ważniejsza od poprawności. Jeśli dane nie są posortowane lub potrzebują Państwo dokładnych identyfikatorów, zawsze należy wybrać FALSE. Szybka błędna odpowiedź jest gorsza od nieco wolniejszej, ale poprawnej. W nowoczesnych arkuszach i przy typowych rozmiarach tabel różnica jest rzadko zauważalna, dlatego ze względów bezpieczeństwa należy domyślnie używać FALSE.

Wybierz właściwy typ dopasowania dla danej sytuacji.

Najważniejsze informacje:

• FALSE = dopasowanie dokładne, nieposortowana tabela nie stanowi problemu, brakujące wartości zwracają błąd #N/A
• TRUE = dopasowanie przybliżone, pierwsza kolumna musi być posortowana rosnąco, funkcja znajduje największą wartość, która nie przekracza wartości docelowej
• Pominięcie argumentu powoduje domyślne użycie wartości TRUE, dlatego należy zawsze ją określać
• Dopasowania dokładnego należy używać dla identyfikatorów i kodów, a przybliżonego dla progów i przedziałów

W następnej części zmienią Państwo kierunek wyszukiwania i poznają funkcję HLOOKUP, która przeszukuje wiersze.