W dzisiejszej poradzie omówimy wyszukiwanie binarne, czyli wyszukiwanie przybliżone w Excelu. Temat ten omówimy na podstawie przykładowych danych z rysunku nr 1. Wpis ten jest niejako kontynuacją tematu z poprzedniego postu, dotyczącego funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym. Opisaliśmy wtedy obrazowo jak działa dopasowanie przybliżone.

rys. nr 1 — Przykładowe dane

Dopasowanie przybliżone jest trochę bardziej skomplikowane od dokładnego, ale działa szybciej. Musimy pamiętać, żeby zawsze mieć posortowane dane (rosnąco, od najmniejszej do największej lub od A do Z). Kiedy nasze dane nie będą posortowane to funkcja WYSZUKAJ.PIONOWO z dopasowaniem dokładnym znajdzie wynik dla Miś (szuka od góry do dołu), ale jeśli mamy tych wyników mamy bardzo dużą ilość zajmie to sporo czasu. Funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym działa dużo szybciej, ale wymogiem jest, aby dane były posortowane, bo inaczej funkcja może zwrócić nam nieprawidłowy wynik.

Aby dobrze zrozumieć dlaczego może pojawić się nieprawidłowy wynik opiszemy to na konkretnym przykładzie. Wyszukiwanie binarne można powiedzieć, że dzieli na pół nasze tablice, listy danych. Załóżmy, że szukamy Misia, w dopasowaniu przybliżonym nie zaczynamy go szukać od początku tylko od środka listy danych. W naszym przykładzie na środku jest Kowboj, K jest mniejsze od M (bo w alfabecie K jest przed M), więc wszystkie wartości powyżej przestają nas interesować (rys. nr 2).

rys. nr 2 — Działanie dopasowania przybliżonego

Po pominięciu wyników powyżej zostaje nam mniejsza tabelka, dokładnie Pastereczka, Dinozaur i Lalka. Teraz na środku znajduje się Dinozaur, D jest mniejsze od M, więc po raz kolejny pomijamy wszystkie wyniki powyżej Dinozaura (czyli tego który aktualnie sprawdzamy). Zostaje nam jeden wynik – Lalka i otrzymujemy jako wynik całej funkcji cenę Lalki (rys. nr 3). 

rys. nr 3 — Sposób działania funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym

Dzieląc tak listy na pół, możemy bardzo szybko znaleźć szukaną wartość, bo nie musimy sprawdzać niejako wszystkich wyników. Pamiętajmy, że funkcja WYSZUKAJ.PIONOWO znajduje tylko jedną wartość – pierwszą na którą trafi.

Podsumowując funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym może zwrócić nam nieprawidłowy wynik dla nieposortowanych danych.

W drugim przykładzie mamy posortowane dane. Zaczynamy szukać od polowy listy i trafiamy akurat na szukaną nazwę, więc nie kontynuujemy tej drogi z poprzedniego przypadku, bo Miś=Miś (rys. nr 4).

rys. nr 3 — Wyszukiwanie binarne

Należy pamiętać, że Excel robi coś jeszcze przy dopasowaniu przybliżonym  nie kończy wyszukiwania gdy znajdzie wartość tylko sprawdza dane poniżej, czy nie powtórzyła się szukana wartość. Jeśli pod spodem jest więcej danych o takiej samej nazwie (Miś), to Excel zawsze jako ostateczny wynik poda nam wartość przypisaną dla ostatniego wystąpienia tej nazwy (rys. nr 5), ale takie same wartości muszą być tuż pod sobą. Jeśli nazwa Miś powtórzy się drugi raz, ale w odstępie kilku nazw to Excel już jej nie znajdzie.

rys. nr 5 — Ostatnia znaleziona wartość jako stateczny wynik

Analogicznie wygląda sytuacja z liczbami, a nawet jest dużo prostsza do zrozumienia (rys. nr 6).

rys. nr 6 — Wyszukiwanie z dopasowanie przybliżonym na wartościach liczbowych

Szukamy wartości 62, aby wystawić ocenę. Sprawdzamy wartość w połowie tabelki, otrzymujemy 70. Widzimy, iż 70>62, więc pomijamy wyniki poniżej wartości którą rozpatrywaliśmy (rys. nr 7). Skracamy nasze dane do wartości powyżej 70.

rys. nr 7 — Zasada działania wyszukiwania z dopasowaniem przybliżonym na liczbach

Rozpatrujemy teraz górną połowę wyników, sprawdzamy wartość 50 (w środku danych), wiemy że 50<60, więc pomijamy wartości powyżej 50 w tabeli. Pozostaje nam jedna wartość 60, w ten sposób otrzymujemy wynik – Przeciętnie. Myślę, że wyjaśniłem wystarczająco temat wyszukiwania binarnego. Jeśli chcesz poznać bardziej precyzyjnie zasady działania wyszukiwania przybliżonego w Excelu przeczytaj post na forum Billa Szysz https://www.excelforum.pl/topics1/formula-zwracajaca-tekst-ze-zbioru-komorek-vt51013.htm


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama