Artykuł ten to odpowiedź na pytanie, jakie zadał uczestnik mojego kursu online Excel w codziennej pracy cz. 2. 

Sama odpowiedź jest dość krótka: WYSZUKAJ.PIONOWO przybliżone wykorzystuje tzw. szukanie binarne… tylko prawdopodobnie nic Ci to nie mówi i musisz googlać dalej ;). Żeby zatem oszczędzić Ci dalszego szukania – opowiem co to znaczy.

Jak zwykle posłużę się praktycznym przykładem: tabelą rabatową. Załóżmy, że w zależności od wartości zakupów, klienci otrzymują rabat, określony w tabeli rabatowej (na obrazku: po prawej). Oto formatka:

Jak zatem WYSZUKAJ.PIONOWO znalazło wartość 1900 w tabeli rabatowej, mimo że tej wartości tam nie było? Skąd wiedziało, że 1900 zawiera się w przedziale od 1500 do 1999,99?

Użyło szukania przybliżonego.

A jak działa ten rodzaj szukania? Czytaj dalej 🙂

Formuła

Po pierwsze, aby w ogóle użyć szukania przybliżonego (choć ja wolę określenie szukanie przedziałami/wpasowywanie liczby w przedział), w ostatnim argumencie WYSZUKAJ.PIONOWO należy wpisać PRAWDA (lub np. 1 lub nic nie wpisywać). O tak:

=WYSZUKAJ.PIONOWO(C5;$G$6:$H$14;2;PRAWDA)

Ten ostatni argument decyduje o sposobie szukania, jaki ma być użyty:

• FAŁSZ = szukanie dokładne
• PRAWDA = szukanie przybliżone

Szukanie dokładne wykorzystuje najprostszy na świecie sposób szukania, tzw. szukanie liniowe, czyli po prostu, po kolei porównuje szukaną wartość z każdą wartością zakresu.  Jest przez to strasznie wolne. Ten sposób przeszukiwania, szuka tylko identycznych wartości.

Szukanie przybliżone z kolei, wykorzystuje tzw. szukanie binarne + potrafi zwrócić wynik dla wartości, której nie ma w przeszukiwanym zakresie (dlatego nazywa się szukaniem „przybliżonym” ;)):

• uwzględnia wtedy największą wartość, która jest jednocześnie mniejsza niż szukana
• nie zwraca wartości, gdy szukana wartość jest mniejsza niż najmniejsza w przeszukiwanym zakresie

Jak działa szukanie binarne?

Mi zawsze kojarzy się ono z szukaniem słowa w słowniku czy szukaniem osoby na liście obecności. Dzięki temu, że słowa są posortowane, my-ludzie wiemy, w którym miejscu słownika/listy szukać, dzięki czemu zawężamy sobie pole poszukiwań. Zaczynamy od pierwszej litery słowa, potem patrzymy na drugą itd. Za każdym razem idziemy albo w jedną ze stron: do góry listy lub w dół.

Szukanie binarne działa podobnie: posortowany rosnąco zakres dzieli na połowy na podstawie środkowej wartości. Jeśli jest ona większa niż szukana – przeszukuje dalej już tylko pierwszą połowę, a drugą odrzuca. Tę pierwszą połowę znowu dzieli na pół, potem znowu, i tak aż znajdzie szukaną wartość. Zakres musi być posortowany rosnąco – inaczej takie szukanie nie ma sensu.

Brzmi trochę skomplikowanie, więc zobacz na przykładzie naszej tabeli rabatowej.

Tak to wygląda w skrócie, a za chwilę dokładny opis:

Szukanie binarne krok po kroku

Jak działa szukanie binarne – przykład krok po kroku

Załóżmy, że w tabeli rabatowej szukamy wartości zakupu = 1900 zł. Ja-człowiek widzę, że należy się za to rabat 2%, gdyż kwota 1900 jest między 1500 a 2000.

Ale prześledźmy „tok rozumowania” Excela w tej sytuacji.

1. W pierwszej kolumnie przeszukiwanego zakresu, Excel znajduje jego środkową wartość. W naszym wypadku jest to 3000:

Krok 1

Ponieważ nasza szukana wartość, 1900, jest mniejsza niż 3000. Wartości większe niż 3000 nie będą brane pod uwagę.

Eliminacja dolnej połówki zakresu

Zostaje zatem do przeszukania górna połówka zakresu:

Górna połówka do przeszukania

2. Tę połówkę dzielimy zatem dalej na pół i porównujemy 1900 do jej środkowej wartości: 1900 > 1500:

Dzielenie kolejnej połówki

Zostaje dolna połówka do przeszukania:

Dolna, coraz mniejsza, połówka do przeszukania

3. Ostatni krok wygląda identycznie: porównanie 1900 do środkowej wartości: 1900<1500, zatem zostaje wartość 1500:

WYSZUKAJ.PIONOWO (przybliżone), zwróci wartość identyczną do szukanej lub , jeśli nie znajdzie takiej: największą, ale mniejszą od szukanej

Ponieważ interesuje nas wyświetlenie drugiej kolumny przeszukiwanego zakresu, czyli stawki rabatu, WYSZUKAJ.PIONOWO wyświetli wynik 2%.

Na koniec warto jeszcze dodać, że:

• Szukanie binarne jest bardzo szybkie, właśnie ze względu zawężanie obszaru poszukiwań poprzez eliminację „połówek”
• Zwykłe szukanie, wykorzystywane w dokładnym dopasowaniu WYSZUKAJ.PIONOWO, szuka po kolei, od początku tabeli (dlatego nie musi ona być wtedy sortowana).
• Nowa funkcja X.WYSZUKAJ (dostępna w Excelu 2021, w wersji www i subskrypcyjnej 365) nie wymaga sortowania danych przez użytkownika w szukaniu przybliżonym. Funkcja ta sortuje dane w pamięci, czyli użytkownik tego po prostu nie widzi. Dodatkowo, umożliwia również szukanie w drugą stronę: czyli jeśli nie znajdzie identycznej wartości – wyświetla wartość najmniejszą z większych. Ale ta funkcja to temat na zupełnie inną okazję :).

 Plik do pobrania:
MalinowyExcel Jak działa szukanie przybliżone w WP dw

I film: