Funkcja X.WYSZUKAJ w Office 365

Zdecydowana większość z nas bardzo dużo czasu spędza w Excelu pracując z danymi. Gromadzimy je, przekształcamy, wyszukujemy, a potem prezentujemy efekty końcowe. Jeśli mowa o wyszukiwaniu, to najczęściej kojarzy się nam ono z funkcją WYSZUKAJ.PIONOWO, która nie jest idealna, ale ułatwia nam codzienną pracę. Teraz ma się to zmienić na lepsze, ponieważ w pakiecie Office 365 pojawiła się nowa alternatywa, czyli funkcja X.WYSZUKAJ.

Pamiętam, że kiedy uczestnicząc w październiku 2018 roku w konferencji Develop Excel w Londynie pokazano nam nową funkcję, reakcja publiczności była bardzo pozytywna. Stwierdziliśmy wtedy, że jest to duży krok na przód i spore ułatwienie dla użytkowników Excela.

Przyspieszamy czas do przodu i oto w pakiecie Microsoft Office 365 funkcja X.WYSZUKAJ jest już dostępna. Czas więc omówić zarówno jej przeznaczenie, składnię, jak i pokazać kilka przykładów jej zastosowania.

Gotowy? W takim razie zaczynamy! ?

Do czego służy funkcja X.WYSZUKAJ i gdzie ją znajdziesz?

Funkcja X.WYSZUKAJ (ang. XLOOKUP) przeszukuje zakres lub tablicę pod kątem dopasowania i zwraca odpowiedni element z drugiego zakresu lub drugiej tablicy. Co ważne, domyślnie jest używane dokładne dopasowanie, czyli działa odwrotnie do funkcji WYSZUKAJ.PIONOWO, gdzie domyślnie szukana była wartość stanowiąca najlepsze dopasowanie.

Nowa funkcja działa zdecydowanie lepiej od poprzedniczek, czyli WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO oraz kombinacji funkcji INDEKS i PODAJ.POZYCJĘ (kto wyszukiwał wartości na lewo od kolumny przeszukiwania, ten wie o czym piszę), a w dodatku oferuje kilka ciekawych rozwiązań o czym przeczytasz już za chwilę.

Niestety na chwilę obecną funkcja X.WYSZUKAJ jest dostępna tylko dla użytkowników pakietu Microsoft Office 365.

Co to oznacza? Cóż, jeśli nie dysponujesz tym pakietem to w najbliższym czasie nie liczyłbym na to, że funkcja X.WYSZUKAJ pojawi się w używanej przez Ciebie wersji Excela. Nie liczyłbym również na to, że zostanie ona zaimplementowana do starszych wersji, jak np. 2007 czy 2010.

Niestety funkcja ta nie jest kompatybilna wstecz co oznacza, że jeśli otworzysz skoroszyt, w którym użyto funkcji X.WYSZUKAJ, w starszej wersji Excela, to najprawdopodobniej komórka zawierająca wspomnianą funkcję będzie wyświetlać błąd. W takim przypadku nie pozostaje Ci nic innego jak dalej opierać swoją pracę na dotychczas wykorzystywanych funkcjach WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO, INDEKS, PODAJ.POZYCJĘ.

Poznaj składnię funkcji i zrozum działanie argumentów

Funkcja X.WYSZUKAJ składa się z sześciu argumentów, z czego trzy pierwsze są obowiązkowe, a trzy pozostałe opcjonalne. Sama składnia funkcji wygląda następująco:

=X.WYSZUKAJ(szukana_wartość;szukana_tablica;zwracana_tablica;jeżeli_nie_znaleziono;
tryb_dopasowywania;tryb_wyszukiwania)

Argumenty funkcji są następujące:

  • szukana_wartość – wartość do wyszukania;
  • szukana_tablica – tablica lub zakres wyszukiwania;
  • zwracana_tablica – tablica lub zakres do zwrócenia;
  • jeżeli_nie_znaleziono – argument zwracany w przypadku, kiedy nie znaleziono żadnego dopasowania (jeśli użytkownik nie określi tego argumentu, to w przypadku nieznalezienia wartości pojawi się błąd #N/D);
  • tryb_dopasowania – określa sposób dopasowania elementu szukana_wartość do wartości w elemencie szukana_tablica;
    • 0 – dokładne dopasowanie (to jest domyślne ustawienie);
    • -1 – dokładne dopasowanie lub następny mniejszy element;
    • 1 – dokładne dopasowanie lub następny większy elementy;
    • 2 – dopasowanie przy użyciu symbolu wieloznacznego (*);
  • tryb_wyszukiwania – określ tryb wyszukiwania do użycia. Domyślnie będzie używane wyszukiwanie od pierwszego do ostatniego elementu.
    • 1 – wyszukaj od pierwszego do ostatniego elementu (domyślne ustawienie);
    • -1 – wyszukaj od ostatniego elementu do pierwszego elementu;
    • 2 – wyszukiwanie binarne (posortowane w kolejności rosnącej);
    • -2 – wyszukiwanie binarne (posortowane w kolejności malejącej).

X.WYSZUKAJ w akcji

A teraz przejdźmy od teorii do praktyki, bo tylko tak zapoznasz się z nową funkcją i od razu będziesz wiedział jak ją zastosować w swojej pracy.

Podstawowe wyszukiwanie

Jeśli zależy Ci na podstawowej funkcjonalności, to używając funkcji X.WYSZUKAJ określasz trzy podstawowe argumenty. Są to szukana_wartość (tutaj komórka A12 zawierająca nazwę produktu), szukana_tablica (zakres komórek A2:A9) gdzie funkcja będzie próbowała odnaleźć wartość Zamrażarka oraz trzeci argument, czyli zwracana_tablica (zakres C2:C9), z którego pobrany zostanie wynik dla Zamrażarki i wyświetlony jako rezultat działania funkcji.

Funkcja X.WYSZUKAJ w Excelu

Zauważ, że w przeciwieństwie do funkcji WYSZUKAJ.PIONOWO nie zaznaczasz jako przeszukiwanej tablicy całego zakresu komórek A2:C9 i nie wskazujesz numeru kolumny, z której ma być pobrany wynik.

W przypadku funkcji X.WYSZUKAJ te dwa elementy są rozdzielone, dzięki czemu ta funkcja działa również w lewo od szukanej_tablicy i to jest jej pierwsza duża zaleta. ?

Rozlana formuła, czyli funkcja X.WYSZUKAJ zwraca wiele wartości jednocześnie

Nic nie stoi na przeszkodzie, aby wyszukując informacji w zgromadzonych danych jako zwracaną_tablicę wskazać zakres komórek nie z jednej, ale z wielu kolumn.

Efektem takiego zabiegu będzie zwrócenie przez funkcję X.WYSZUKAJ nie jednego wyniku (wartości), ale całego zestawu, który zostanie “rozlany” do komórki zawierającej funkcję oraz do sąsiednich pustych komórek.

W prezentowanym przykładzie funkcja będzie wyszukiwać wartości sprzedaży dla robota kuchennego w kolejnych trzech miesiącach. Formuła została wprowadzona do komórki B12, ale wynik został rozlany do zakresu B12:D12.

Funkcja X.WYSZUKAJ w Excelu

Gdybyś teraz chciał usunąć wartość z komórki C12 lub D12, to nie będzie to możliwe. Excel poinformuje, że wartość w tej komórce została rozlana i modyfikacji możesz dokonać tylko w komórce zawierającej formułę.

W tym przypadku będzie to komórka B12.

Wbudowana obsługa błędów

Czy pamiętasz, jak używając funkcji WYSZUKAJ.PIONOWO lub WYSZUKAJ.POZIOMO w niektórych komórkach zwracała ona błąd #N/D? Pojawiał się on zawsze wtedy, kiedy w przeszukiwanej tablicy nie znaleziono poszukiwanej wartości.

Jeśli chciałeś uniknąć wyświetlania błędów, to najczęściej zagnieżdżałeś funkcję WYSZUKAJ.PIONOWO w funkcji JEŻELI.BŁĄD.

Tym razem nie jest to konieczne, ponieważ czwarty (opcjonalny argument) funkcji X.WYSZUKAJ pozwala z góry określić co się stanie, jeśli szukana wartość nie zostanie odnaleziona.

W tym przypadku możesz zdefiniować tekst do wyświetlenia (pamiętaj o umieszczeniu go w cudzysłowie), odwołać się do wartości konkretnej komórki lub zagnieździć inną formułę, która zostanie wykonana.

Dopasowanie przybliżone, nawet jeśli dane są nieposortowane

Jeśli kiedykolwiek stosowałeś funkcję WYSZUKAJ.PIONOWO z wyszukiwaniem przybliżonym i tablica przeszukiwana zawierała przedziały, np. określające wysokość prowizji w zależności od przedziału uzyskanej sprzedaży, to wiesz, że taka tablica musi zawsze zawierać posortowane dane.

W przypadku funkcji X.WYSZUKAJ nie ma już takiej konieczności, ponieważ radzi sobie ona świetnie nawet w przypadku nieuporządkowanych wartości.

W prezentowanym przykładzie obliczamy wysokość prowizji dla handlowców bazując na wielkości sprzedaży jaką wygenerowali.

Zauważ, że piąty argument funkcji X.WYSZUKAJ, czyli tryb_dopasowania został określony na -1, czyli dokładne dopasowanie lub następny mniejszy element.

Dzięki temu wartość prowizji liczona w PLN zgadza się w każdym przypadku.

Wyszukiwanie z zastosowaniem symbolu wieloznacznego

W tym przykładzie chodzi o wyszukanie kwoty brutto oraz waluty przypisanej do konkretnych dostawców. Problem polega na tym, że użytkownik nie wprowadza całej nazwy dostawcy, a jedynie jej część, przy czym przeszukiwana tablica zawiera pełne nazwy.

Aby uzyskać prawidłowe wyniki, stosujesz funkcję X.WYSZUKAJ z uwzględnieniem piątego argumentu o wartości 2, który mówi, że dopasowanie ma nastąpić przy użyciu tzw. symbolu wieloznacznego (*).

Jak widzisz na zrzucie ekranu, pierwszy argument funkcji (komórka B17) to połączenie symbolu gwiazdki w cudzysłowie umieszczonego przed komórką A17, zawierającą częściową nazwę dostawcy, oraz po niej.

Druga formuła umieszczona w komórce C17 różni się od pierwszej tylko zakresem komórek dla zwracanej_tablicy. Reszta pozostaje bez zmian.

Wyszukiwanie ostatniej pasującej wartości

Ta opcja jest po prostu EXTRA! ?

Wyobraź sobie, że dział kadr i płac dysponuje listą pracowników (posortowaną według daty zatrudnienia od najstarszej do najnowszej).

Teraz należy wskazać dane ostatnio zatrudnionych pracowników w działach Produkcja, IT oraz Księgowość.

Oczywiście i tutaj z pomocą przychodzi nowa funkcja X.WYSZUKAJ.

Funkcja X.WYSZUKAJ w Excelu

Określając wartość ostatniego argumentu funkcji na -1 wskazujesz, że interesuje Cię ostatni element tablicy, który pasuje do poszukiwanej wartości.

Tym samym bardzo łatwo jest wyznaczyć nazwisko ostatniego pracownika oraz datę jego zatrudnienia.

Gdzie mógłbyś wykorzystać tą funkcjonalność?

Dla przykładu, jeśli w Excelu prowadzisz plan projektu to również za pomocą funkcji X.WYSZUKAJ możesz wskazywać bieżący status działania dla każdego podzespołu projektowego.

Podsumowanie

Funkcja X.WYSZUKAJ działa bardzo sprawnie i dostarcza nowych rozwiązań, których tak brakowało do tej pory, lub które wymagały znajomości i użycia dodatkowych funkcji.

Mam nadzieję, że przedstawione przykłady zainspirują Ciebie do pracy z tą funkcją.

A jeśli jedyną przeszkodą jest brak tej funkcji w używanej wersji Excela, to warto pomyśleć o zakupie pakietu Microsoft Office 365, ale to już temat na inny artykuł. ?