Większość użytkowników Excela uznaje formułę WYSZUKAJ.PIONOWO za najważniejszą formułę. W różnych rankingach stawiana jest wyżej nawet od najprostszej sumy. Wyszukiwanie wartości to czynność, którą wykonujemy codziennie, a samej formuły używamy tysiące razy. WYSZUKAJ.PIONOWO ma niestety swoje ograniczenia, główne to wyszukiwanie tylko w prawą stronę oraz sztywny numer kolumny, w której szukamy wyniku. X.WYSZUKAJ poza tym że wyszukuje w lewą stronę to dodatkowo pozwala na wiele więcej, potrafi zastąpić kilka formuł, a do tego działa bardzo szybko. X.WYSZUKAJ to takie WYSZUKAJ.PIONOWO na sterydach, po prostu miażdży naszą najważniejszą formułę.

Dlaczego prawdopodobnie nie znasz X.WYSZUKAJ?

Główny powód jest taki, że stosunkowo od niedawna możemy korzystać z tej formuły. Zapowiedziana została w połowie 2019, a na początku 2020 została udostępniona pierwszym użytkownikom. Funkcja dostępna jest obecnie tylko w pakiecie 365 oraz docelowo w Excelu 2021. Czyli jest to funkcja bardzo młoda, a jednocześnie chyba najbardziej oczekiwana ze wszystkich. X.WYSZUKAJ ma ogromne możliwości, ale nie stała się od razu bardzo popularna z dwóch powodów. Po pierwsze większość osób tak przyzwyczaiła się do funkcji WYSZUKAJ.PIONOWO, że używa je odruchowo i już nie szuka, czy pojawiła się jej nowsza lepsza wersja. Po drugie wiele osób nie chce jej używać, gdy z ich plików mogą korzystać osoby ze starszymi wersjami Excela.

Składnia funkcji X.WYSZUKAJ

X.WYSZUKAJ jako formuła, która ma o wiele większe możliwości niż WYSZUKAJ.PIONOWO naturalnie musi mieć też bardziej rozbudowaną składnię. Możemy wprowadzić do niej aż 6 parametrów, ale nie trzeba się martwić, tu też pomyślano nad optymalizacją działania, ponieważ tylko 3 z nich są wymagane.  A 3 parametry to już mniej niż wymaga WYSZUKAJ.PIONOWO.

Parametry wymagane to:

• Szukana wartość – dokładnie to samo co w WYSZUKAJ.PIONOWO
• Szukana tablica – tu już nie podajemy tablicy, która zaczyna się od kolumny, w której szukamy wartości oraz zakresu tak szerokiego, że zawiera szukane wyniki. Tu zaznaczamy tylko zakres, w którym będzie szukana wartość.
• Zwracana tablica – to zakres wyników jakie chcemy wyszukać, możemy wyszukać wartości z jednej lub z kilku sąsiadujących kolumn. Nie musimy już podawać numeru kolumny, po prostu zaznaczamy w której kolumnie jest nasz wynik. Co ważne nie ma znaczenia czy ten zakres jest po prawej czy po lewej stronie szukanej tablicy. Oczywiście może to być ten sam zakres w której jest szukana wartość.

Parametry opcjonalne to:

• Jeśli nie znaleziono – podajemy wartość jaka ma być zwrócona w przypadku, gdy nie udało się znaleźć szukanej wartości.
• Tryb dopasowania – możemy tu określić, czy chcemy wyszukać dokładny wynik, dokładny lub pierwszy mniejszy/większy wynik lub też wartość z uwzględnieniem znaków specjalnych „?”, „*”, „~” czyli np. fragmentu tekstu.
• Tryb wyszukiwania – określa czy funkcja ma przeszukiwać zakres od początku czy od końca. Można też zastosować o wiele szybsze wyszukiwanie binarne, ale wymagane jest wcześniejsze posortowanie danych.

Przykłady standardowego wykorzystania formuły X.WYSZUKAJ

Do przykładu wykorzystamy prosty zestaw danych dotyczących zamawianych produktów. Mamy produkt, datę zamówienia, nazwę miesiąca oraz wartość zamówienie.

1. Standardowe wyszukanie pierwszej wartości

Aby wyszukać wartość w ten sam sposób jak najczęściej szukamy w WYSZUKAJ.PIONOWO wystarczy, że użyjemy 3 wymaganych parametrów. Podajemy wartość jaką szukamy, zakres w jakim mamy ją znaleźć oraz zakres, z którego chcemy pobrać wynik. Nie musimy określać parametru na dokładne wyszukiwanie wartości, ponieważ jest on domyślnie ustawiony.

2. Wyszukaj ostatnią wartość

Żeby formuła przeszukiwała podany zakres od końca wystarczy w ostatnim, szóstym parametrze podać wartość -1. Czwarty i piąty parametr pozostaje pusty, są opcjonalne. W ten prosty sposób nie musimy już dodatkowo sortować danych lub używać połączenia kilku formuł.

3. Wyszukiwanie w lewą stronę – największa zmora WYSZUKAJ.PIONOWO

WYSZUKAJ.PIONOWO w żaden sposób nie potrafiło podać wartości, które znajdowały się po lewej stronie od kolumny, w której są szukane wartości. Rzecz zdawałoby się tak banalna, że aż dziwne, że nie było to uwzględnione w pierwszej wersji formuły. Z problemem tym radzono sobie na różne sposoby takie jak dodawanie/przesuwanie kolumn, powielanie kolumn lub kombinacje różnych formuł, które nie ingerowały w dane. Na szczęście X.WYSZUKAJ nie ma ograniczeń, gdzie znajduje się zwracana tablica.

4. Podaj wartość, gdy nie znaleziono szukanej wartości

Kolejna wadą WYSZUKAJ.PIONOWO było to, że zwracała błąd #N/D, gdy nie znalazła szukanej wartości. Wyświetlanie błędów nie jest dobrą praktyką, nie dość, że nie wiemy z czego wynika błąd (mogliśmy źle napisać formułę, podać złe parametry), to dodatkowo przekłada się to na błędy w komórkach odwołujących się do komórki z błędem. Dlatego zawsze warto wprowadzać obsługę błędów i dlatego zazwyczaj formułę WYSZUKAJ.PIONOWO łączy się z formułą JEŻELI.BŁĄD. Przy X.WYSZUKAJ odpowiada za to czwarty parametr dzięki czemu obsłużymy wszystkie przypadki, gdy nie znajdujemy szukanych wartości.

5. Wyszukiwanie niedokładnego dopasowania

WYSZUKAJ.PIONOWO bez podania ostatniego parametru domyślnie nie wyszukiwało dokładnego dopasowania. Można powiedzieć, że też był to błąd na poziomie projektowania formuły, ponieważ najczęściej właśnie dokładne dopasowanie nas interesuje. Piąty parametr przy X.WYSZUKAJ pozwala na niedokładne wyszukiwania, czyli najczęściej przypisywania wyniku do wartości z danego przedziału. Dla przykładu mamy rabat udzielony w zależności od wielkości zamówienia.

6. Niedokładne wyszukiwanie dla nieuporządkowanego zestawu przedziałów wartości

X.WYSZUKAJ idzie krok dalej w wyszukiwaniu niedokładnym. Dzięki temu, że działa na innych zasadach niż standardowe formuły (jest rodzajem formuł tablicowych) to potrafi sobie sama posortować odpowiednio kolejność przedziałów. Teraz jeżeli pomieszamy kolejność przedziałów to nie wpłynie to na otrzymane wyniki. Dla przykładu pomieszane zakresy dla kosztów dostawy w zależności od wartości zamówienia.

Przykłady zaawansowanego wykorzystania X.WYSZUKAJ

1. Wyszukiwanie wartości pasujących do wzoru

Zwykłe wyszukiwanie wymaga do poprawnego działania pełnej zgodności pomiędzy szukanym elementem a znajdowanym. Jeśli szukany tekst wprowadzamy ręcznie do komórki, to wpisywanie pełnej nazwy może być marnowaniem czasu, skoro wiemy, że już określony fragment jednoznacznie określa to czego szukamy. Dzięki znakom „*”, „?” oraz „~” możemy wyszukiwać teksty pasujące do odpowiedniego schematu. Znak „*” zastępuje dowolny ciąg znaków, „?” to pojedynczy dowolny znak, natomiast użycie „~” przed „*” lub „?” określa, że te znaki mają być zwyczajnie użyte w szukanym tekście.

Poniżej przykład jak funkcja znajduje nazwę produktu po podaniu tylko fragmentu nazwy. Kiedy taka funkcja może być jeszcze bardzo przydatna? Np. przy używaniu odmian polskich słów np. „duży”, „duża”, „duże” czy też przy nazwiskach „Kowalski”, „Kowalska”.

2. Sumowanie zakresu wyszukanych wartości

Tworząc raporty czy zestawienia często tworzy się formuły, które sumują określony zakres danych w wybranym przedziale czasu. Standardowo wykorzystywaliśmy do tego kombinację takich formuł jak SUMA, PRZESUNIĘCIE, PODAJ.POZYCJĘ. Mogło to sprawić spore problemy wielu użytkownikom Excela.

Przy X.WYSZUKAJ sprawa jest o wiele prostsza, używamy funkcji SUMA, a jako jest parametry wyszukanie zamówienia dla pierwszego miesiąca oraz ostatniego miesiąca szukanego przedziału czasu. Obie te wartości łączymy dwukropkiem jako określenie nie pojedynczych wartości a zakresu komórek. W taki sam sposób możemy wyliczać inne miary, np. średnią wartość zamówienia.

3. Wyszukanie wielu wartości jednocześnie.

Jeśli potrzebowaliśmy znaleźć kilka sąsiadujących wartości to musieliśmy dla każdej wartości wprowadzić formułę w osobnej komórce. Oczywiście odpowiednio usztywniając zakresy polegało to na przeciągnięciu formuły. Zmiennym elementem był numer wyszukiwanej kolumny. To też można było określić jako parametr pobierany z innej komórki. Jakby tego nie robić wymagało to wykonania kilku czynności.

Jak już wspominałem X.WYSZUKAJ ma naturę funkcji tablicowych, a to oznacza, że wykorzystuje nowy silnik obliczeniowy Excela, który jest swojego rodzaju rewolucją. Więcej o tym przeczytasz w artykule dotyczącym dynamicznych formuł tablicowych. Teraz jeżeli chcemy wyszukać informacje z sąsiadujących kolumn to wystarczy, że podamy je jako zwracaną tablicę. W ten sposób w podanym przykładzie wprowadzamy formułę w komórkę I17 i otrzymujemy od razu datę zamówienia, nazwę miesiąca oraz wartość zamówienia. Choć formuła jest tylko w jednej komórce to rozlewa się na kolejne dwie, które są tylko polem do wyświetlenia wyników.

4. Wyszukanie wartości spełniających kilka warunków – wykorzystanie warunków logicznych

Wyszukanie pierwszej, ostatniej czy jedynej wartości to nie problem. Ale co, jeśli musimy znaleźć konkretny produkt z określoną datą zamówienia? Nasz wynik musi spełniać dwa warunki. Wcześniej musielibyśmy łączyć wiele formuł. Teraz wystarczy X.WYSZUKAJ z wprowadzonym iloczynem warunków. Szukana wartość to 1, czyli prawda dla warunku logicznego, Szukana tablica to iloraz dwóch zakresów z warunkami. Pierwszy to produkty o określanej nazwie, drugi to zakres dat i określona data zamówienia. W ten sposób wyszukujemy wartość, która spełnia dwa warunki.

5. Jednoczesne wyszukanie w pionie i w poziomie

Część osób od początku zastanawia się, dlaczego X.WYSZUKAJ nie ma swojego odpowiednika dla WYSZUKAJ.POZIOMO. Otóż w ogóle go nie potrzebuje, bo ze względu na swoją tablicową naturę zastępuje zarówno WYSZUKAJ.PIONOWO jak i WYSZUKAJ.POZIOMO.

Używając dwukrotnie X.WYSZUKAJ możemy wyszukać dowolną wartość, która znajduje się na przecięciu określonego wiersza i kolumny. Nie potrzeba do tego żadnej innej formuły. Jeśli chcemy z tabeli wyszukać wartość zamówienia w określonym roku dla określonego produktu to wystarczy najpierw wyszukać rok, a następnie jako zwracaną tablicę wyszukać produkt. Formuła bardzo prosta, choć zrozumienie jak działa to wyszukiwanie jest o wiele trudniejsze.

Podsumowanie

Jak widać możliwości X.WYSZUKAJ są ogromne. Tablicowa natura tej formuły pozwala zdziałać cuda w bardzo prosty sposób. Nowe formuły w Excelu to nie tylko nowe sposoby obliczania wartości, to zupełnie nowe możliwości zastosowania oraz ogromne uproszczenie dotychczasowych rozwiązań. Nie dość, że możemy zrobić więcej, to jeszcze prościej i szybciej.

Funkcja ta z czasem przejmie zajmowane przez WYSZUKAJ.PIONOWO miejsce na podium najważniejszych funkcji. Nie stanie się to niestety prędko, ponieważ dostęp do niej mają użytkownicy Office 365. Dopóki ta wersja lub Excel 2021 nie rozpowszechni się to sama formuła nie stanie się popularna. Jeśli nasze skoroszyty udostępniamy osobom, które działają na innych wersjach Excela to niestety musimy wrócić do standardowych formuł. Kolejny problem to wytarte przyzwyczajenia użytkowników, wielu będzie stosować to co stosuje od lat i przekazywać takie nawyki innym.

Mimo to znajomość nowych formuł w Excelu to rzecz konieczna, za kilka lat ich nieznajomość będzie porównywana do sytuacji, gdy teraz ktoś nie zna tabel przestawnych. Nie będzie można nazywać siebie zaawansowanym użytkownikiem Excela bez dobrej znajomości dynamicznych formuł tablicowych, Power Query i Power Pivota.

Pobierz plik z omawianymi zastosowaniami funkcji X.WYSZUKAJ