WYSZUKAJ.PIONOWO: jak działa szukanie przybliżone?

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 wyszukaj.pionowo

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:

WYSZUKAJ.PIONOWO zukanie binarne krok 1

Krok 1

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

WYSZUKAJ.PIONOWO szukanie binarne

Eliminacja dolnej połówki zakresu

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

WYSZUKAJ.PIONOWO szukanie binarne

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:

WYSZUKAJ.PIONOWO szukanie binarne

Dzielenie kolejnej połówki

Zostaje dolna połówka do przeszukania:

WYSZUKAJ.PIONOWO szukanie binarne

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 szukanie binarne

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:

 

 

Tagi , , , , , , .Dodaj do zakładek Link.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *