Minuty czytania: 4

Funkcja WYSZUKAJ PIONOWO: 5 sposobów użycia, o których pewnie nie wiesz

Funkcja WYSZUKAJ PIONOWO chyba powoli odchodzi do lamusa… W każdym razie, jeśli jesteś analitykiem, tudzież potrzebujesz bardziej elastycznych narzędzi – pewnie używasz relacji w Power Pivot. A jeżeli dysponujesz nową wersją Excela – funkcji XLOOKUP.

Dla całej reszty z nas (większości?) funkcja WYSZUKAJ PIONOWO, nadal może być i będzie użytecznym narzędziem. Jeśli używasz jej na co dzień, to ten artykuł pomoże Ci poszerzyć zakres możliwości. A jeśli masz z nią jakieś trudności – polecam najpierw lekturę innych moich artykułów.

Przeczytaj również:

• WYSZUKAJ PIONOWO – krok po kroku
• WYSZUKAJ PIONOWO nie działa?

Ściągnij plik początkowy, jeżeli chcesz przećwiczyć rozwiązania opisane w artykule.

Policz prowizję, dzięki najlepszemu dopasowaniu

Musisz policzyć odpowiednią wartość prowizji, na podstawie wartości sprzedaży. Na przykład sprzedawca, który zrealizuje sprzedaż do 10 000 zł – nie otrzyma prowizji. Za to ten, który zrealizuje sprzedaż od 10 000 zł włącznie do 20 000 zł – otrzyma prowizję w wysokości 1% sprzedaży.

Zazwyczaj takie zadanie realizujemy budując zagnieżdżoną formułę z funkcją JEŻELI. I to jest ok, dopóki masz mało warunków. Im więcej warunków, tym bardziej złożona staje się formuła. A dla higieny naszych arkuszy (a raczej ograniczaniu ryzyka popełnienia błędów) powinniśmy upraszczać formuły.

Niewiele osób wie, że w takich przypadkach można wykorzystać WYSZUKAJ PIONOWO, używając w czwartym argumencie: 1 (PRAWDA) – najlepsze dopasowanie, zamiast 0 (FAŁSZ) – dokładne dopasowanie.

W powyższym przykładzie, dla wartości >= 10 000 zł i mniejszej niż 20 000 zł, funkcja zwróci prowizję 1%. Z kolej dla sprzedaży >=20 000 zł i mniejszej niż 30 000 zł, prowizja wyniesie 2%. I tak dalej. Tak właśnie działa WYSZUKAJ PIONOWO z najlepszym dopasowaniem.

Jak pewnie zauważyłeś wartości wyszukiwane (pierwsza lewa kolumna w tabeli źródłowej) są posortowane rosnąco. To nie przypadek, tylko warunek konieczny, aby funkcja zwracała prawidłowe wartości.

Wyszukaj niekompletną wartość

Zdarza Ci się, że dane źródłowe w których wyszukujesz mają nieco inną postać niż byś sobie życzył? Mnie tak 🙂 Jest sposób i na to.

W przykładzie poniżej, chcemy znaleźć ceny dla naszych produktów: krzesła, stołu, ławki i kanapy. Ale dane źródłowe z cenami, ściągnięte z systemu, zawierają jeszcze dodatkowe oznaczenia. Mamy zatem nazwę naszego produktu – Krzesło – wpisaną jako fragment większego ciągu znaków 1_Krzesło_WXX_2020.

Mógłbyś podzielić dane źródłowe, za pomocą narzędzia TEKST JAKO KOLUMNY, ze wstążki DANE ale jak zwykle – jest lepszy sposób. W Excelu (i nie tylko) – „*” (gwiazdka) – zastępuje dowolny ciąg znaków. Okazuje się, że jeżeli użyjesz jej w WYSZUKAJ PIONOWO to spełni dokładnie taką rolę. Wpisując:

"*"&"Krzesło"&"*"

dajesz znać, że szukasz dowolnego ciągu znaków, który w środku zawiera słowo „krzesło”. Zobacz jak to zrobić na poniższym przykładzie.

Wyszukiwanie częściowe z zamianą tekstu na liczbę

W poprzednim akapicie pisałem, o problemie jaki mamy w użyciem WYSZUKAJ PIONOWO, gdy poszukiwane przez nas wartości mają zmienioną formę. Tak jest w sytuacji, gdy szukasz tylko fragmentu jakiegoś tekstu – powiedzmy identyfikatora produktu, a w danych źródłowych ten identyfikator jest „zaszyty” w identyfikatorze transakcji.

I ponownie, dzięki połączeniu WYSZUKAJ PIONOWO z inną funkcją jesteś w stanie sobie doskonale poradzić. W naszym przykładzie id produktu występuje jako pierwsze cztery znaki w id transakcji. Możemy zatem użyć funkcji LEWY, aby wyciągnąć te pierwsze 4 znaki i użyć ich jako argumentu w WYSZUKAJ PIONOWO.

Gdyby okazało się, że poszukiwany przez Ciebie fragment jest w innym miejscu (na przykład w środku) możesz analogicznie użyć funkcji FRAGMENT TEKSTU.

Oglądając przykład, zauważysz, że po fragmencie z funkcją LEWY dodaje zero. Dlaczego? Ponieważ wyciągnąłbym id produktu jako tekst, a w danych źródłowych ten sam identyfikator występuje jako liczba. Jak wiesz dla WYSZUKAJ PIONOWO to znacząca różnica. Użycie operacji matematycznej, w tym przypadku dodawania zamienia tekst na liczby, a żeby nie zmieniać wartości id – po prostu dodaję zero. Dla wnikliwych – odejmowanie zadziała podobnie, ale mnożenie już nie. Jeżeli wiesz dlaczego nie – pochwal się w komentarzu 😉

Uniknij zera w wyniku wyszukiwania

Jeżeli w danych źródłowych nie będzie poszukiwanej przez WYSZUKAJ PIONOWO wartości, to funkcja zwróci błąd: N/D! – błąd wartości niedostępnej. Jeśli natomiast poszukiwana wartość będzie, ale nie będzie do niej przypisanej „korespondującej” wartości, której poszukujemy (pusta komórka) – to w wyniku dostaniemy 0 (zero).

Aby tego uniknąć (zera), do funkcji WYSZUKAJ PIONOWO wystarczy „dokleić” tzw. pusty znak, czyli dwa cudzysłowy „”. Będzie to wyglądało następująco:

=WYSZUKAJ.PIONOWO(E11;$B$11:$C$18;2;0)&""

WYSZUKAJ PIONOWO z dwoma warunkami wyszukiwania

Standardowo, funkcja WYSZUKAJ PIONOWO działa z jednym warunkiem wyszukiwania. Co jeśli masz dwa warunki? Przykładowo, masz zamiar zrobić podwyżki pracownikom na podstawie oceny ich wynagrodzenia (A – oznacza wynagrodzenie poniżej benchmarku, B – jesteśmy w benchmarku, a C – powyżej benchmarku) Jeśli mielibyśmy na tym poprzestać, to standardowe podejście nam wystarczy.

Jednak, gdy chcielibyśmy różnicować podwyżki także w zależności od działu w jakim ktoś pracuje, to już musimy pokombinować. Jedną z metod jest wykorzystanie: nazw oraz funkcji ADR.POŚR.

la każdego działu definiujemy inną tabelę podwyżkową, nazywamy jej zakres tak jak nazwane są nasze działy: Sprzedaż, Finanse, Logistyka. Za pomocą funkcji ADR.POŚR przekształcisz tekst „Sprzedaż” na zdefiniowaną wcześniej nazwę. Żeby lepiej zrozumieć działanie całego mechanizmu, pobierz plik w którym znajdziesz końcowe rozwiązania i więcej szczegółów dotyczących funkcji ADR.POŚR.

Całość wygląda tak:

{=WYSZUKAJ.PIONOWO(D11;ADR.POŚR(C11);2;0)}

Nawiasy klamrowe oznaczają, że jest to formuła tablicowa (musisz ja zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER) Jest to konieczne, ponieważ funkcja ADR.POŚR zwraca zakres komórek, a nie pojedynczą komórkę.

Podsumowanie

Jestem ciekawy, czy znałeś takie zastosowania funkcji WYSZUKAJ PIONOWO. Nie zawsze są one popularne i powszechne, a jak widzisz ułatwiają życie.

A może znasz jeszcze inne? Podziel się z nami w komentarzu.

---

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.