X.WYSZUKAJ w Excelu: dopasowanie, braki i kierunek szukania

WYSZUKAJ.PIONOWO wymaga klucza w pierwszej kolumnie zakresu i numeru kolumny z wynikiem. W X.WYSZUKAJ wskazuję osobno, gdzie szukać i skąd oddać odpowiedź. Dzięki temu mogę pobrać nazwę z lewej strony kodu, cenę z prawej albo wartość z wiersza ułożonego poziomo.

Angielska nazwa funkcji to XLOOKUP. W polskim Excelu piszę X.WYSZUKAJ, z kropką po X. Pokażę ją na syntetycznym katalogu produktów i osobnej tabeli progów. Zależy mi na świadomym doborze argumentów, szczególnie komunikatu o braku oraz kierunku wyszukiwania.

Dostępność i pełna składnia

X.WYSZUKAJ jest dostępne w Microsoft 365, Excelu 2021 i 2024 oraz w Excelu dla sieci Web. Nie ma jej w Excelu 2016 i 2019. Potwierdza to dokumentacja Microsoft. Przed przekazaniem skoroszytu sprawdzam wersję używaną przez odbiorcę. Sam fakt, że formuła działa u mnie, nie rozwiązuje problemu zgodności pliku.

Funkcja przyjmuje trzy wymagane i trzy opcjonalne argumenty:

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

Najpierw podajesz szukaną wartość, następnie zakres kluczy i zakres odpowiedzi. Czwarty argument określa wynik przy braku dopasowania. Dopiero piąty wybiera sposób dopasowania, a szósty kierunek lub metodę szukania. Pominięcie argumentu o braku w opisie składni przesuwa znaczenie kolejnych liczb i prowadzi do błędnych formuł.

W przykładach używam średników i polskich nazw. Angielskie formuły pokażę przy podstawowym wariancie. Separator argumentów zależy od ustawień regionalnych, więc w niektórych instalacjach potrzebny będzie przecinek. Nawiasy kwadratowe w opisie oznaczają opcjonalność; nie wpisujesz ich do właściwej formuły.

Pierwsze wyszukiwanie na trzech produktach

Wpisz do A1:C4 syntetyczny katalog. Nazwa znajduje się po lewej stronie kodu, cena po prawej. Wszystkie ceny są liczbami, a kody tekstem.

Do E2 wpisz P-20. W F2 umieść:

=X.WYSZUKAJ(E2;$B$2:$B$4;$A$2:$A$4)

Wynikiem jest „Segregator”. Excel znajduje P-20 na drugiej pozycji zakresu B2:B4 i oddaje drugą pozycję A2:A4. Nie przestawiam kolumn, mimo że odpowiedź znajduje się na lewo od klucza. Zmiana zakresu wyniku na C2:C4 zwróci cenę 18.

Angielska wersja pierwszej formuły, przy separatorze przecinkowym, to:

=XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4)

W tym podstawowym wywołaniu działa dokładne dopasowanie i szukanie od początku. Nie trzeba dopisywać odpowiednika FAŁSZ znanego z WYSZUKAJ.PIONOWO. Jawne zero w piątym argumencie może jednak ułatwić czytanie bardziej rozbudowanej formuły.

Zgodność zakresów jest ważniejsza od ich sąsiedztwa

Zakresy szukania i zwracania mogą leżeć w różnych miejscach arkusza. Muszą jednak opisywać odpowiadające sobie pozycje. Dla pionowego słownika pierwsza szukana pozycja powinna odpowiadać pierwszemu wierszowi odpowiedzi, druga drugiemu i tak dalej.

Jeśli wskażesz B2:B4 oraz A3:A5, zakresy mają taką samą długość, lecz są przesunięte. Dopasowanie P-20 pobierze wtedy zawartość A4 zamiast A3. Excel nie zna znaczenia danych i nie ostrzeże, że powiązałeś kod segregatora z nazwą teczki. Sama zgodność rozmiaru nie potwierdza poprawnego wyrównania.

Z tego powodu zaznaczam oba zakresy świadomie i sprawdzam pierwszy oraz ostatni wiersz. Przy kopiowaniu w dół blokuję je dolarami, a E2 pozostawiam względne. Jeżeli źródło jest tabelą Excela, odwołania do nazw jej kolumn mogą ułatwić utrzymanie formuły, ale nadal trzeba zachować właściwą relację wierszy.

Co pokazać, gdy kod nie istnieje

Dla P-99 domyślne wywołanie zwróci #N/D!. Gdy brak produktu jest przewidzianym przypadkiem, mogę dodać komunikat w czwartym argumencie:

=X.WYSZUKAJ(E2;$B$2:$B$4;$C$2:$C$4;"Brak kodu")

Dla P-20 wynikiem nadal jest 18, a dla P-99 „Brak kodu”. Ten argument dotyczy nieznalezienia dopasowania. Nie jest ogólną instrukcją ukrywania wszystkich błędów. Błąd w znalezionej komórce lub wadliwe zakresy trzeba rozpatrzyć osobno.

Nie wpisuję tutaj automatycznie zera. Jeśli zero jest prawidłową ceną produktu, stracę rozróżnienie między ceną bez opłaty a nieznanym kodem. Pusty tekst również może ukryć brak przed osobą czytającą raport. Do obliczeń często przygotowuję osobną kolumnę statusu, zamiast mieszać liczby i komunikaty w jednej kolumnie.

Trzeba też odróżnić brak klucza od pustej wartości w znalezionym wierszu. To, że Excel odnalazł P-20, nie potwierdza kompletności ceny. Właściciel katalogu powinien ustalić, czy puste ceny są dopuszczalne i jak raport ma je przedstawiać.

Pierwsze i ostatnie dopasowanie

Domyślne szukanie zaczyna się od pierwszej pozycji. Gdy kod występuje kilka razy, dostajesz pierwszy znaleziony wynik. Szósty argument -1 odwraca kierunek:

=X.WYSZUKAJ(E2;$B$2:$B$4;$C$2:$C$4;"Brak kodu";0;-1)

Na pierwotnej tabeli wynik się nie zmieni, bo każdy kod występuje tylko raz. Aby prześledzić różnicę, dopisz w wierszu 5 drugi P-20 z ceną 19 i rozszerz oba zakresy do wiersza 5. Zwykłe szukanie odda 18, a szukanie od końca 19.

Ostatni wiersz w zakresie nie musi oznaczać najnowszego zdarzenia. Jeśli plik nie jest uporządkowany według czasu, -1 wybiera ostatnią pozycję, a nie największą datę. Przed pobieraniem aktualnej ceny ustalam regułę dat obowiązywania. Później dopiero decyduję, czy kierunek wyszukiwania ją realizuje.

Tryby dopasowania: zero, minus jeden i jeden

Piąty argument rozstrzyga, co zrobić, gdy brakuje dokładnej wartości. Zero oznacza dopasowanie dokładne, -1 dopasowanie dokładne lub najbliższą mniejszą wartość, a 1 dopasowanie dokładne lub najbliższą większą. Nie myl tych wartości z szóstym argumentem określającym sposób przeszukiwania.

Zbuduj syntetyczną tabelę w H1:I4. Dolne progi liczby sztuk to 0, 10 i 20, a przypisane klasy to „Małe”, „Średnie” i „Duże”. W K2 wpisz 14. Formuła z trybem -1 wybierze próg 10:

=X.WYSZUKAJ(K2;$H$2:$H$4;$I$2:$I$4;"Poza zakresem";-1)

Wynik to „Średnie”. Dla 20 dostaniesz „Duże”, a dla 9 „Małe”. Gdyby tabela opisywała górne granice, potrzebna byłaby inna definicja i być może tryb 1. Nie wybieram go na zasadzie „zobaczmy, czy wynik wygląda lepiej”. Najpierw zapisuję granice kategorii.

W zwykłym kierunku wyszukiwania te przybliżone tryby nie wymagają sortowania jak przybliżone WYSZUKAJ.PIONOWO. Dla czytelności nadal układam progi rosnąco. Sortowanie staje się wymogiem przy wyszukiwaniu binarnym, które ma osobne kody w szóstym argumencie.

Wyszukiwanie binarne ma warunek wstępny

Szósty argument 2 wybiera szukanie binarne w danych uporządkowanych rosnąco, a -2 w danych uporządkowanych malejąco. Microsoft ostrzega, że bez odpowiedniego sortowania wyniki mogą być nieprawidłowe. Nie traktuję więc tych opcji jako bezpłatnego przyspieszenia dowolnego słownika.

W małym katalogu nie mam powodu komplikować formuły. Zostawiam domyślny tryb, dopóki nie mam konkretnego problemu i gwarancji porządku danych. Nie podaję też obietnic szybkości: czas obliczeń zależy od skoroszytu, liczby formuł i rozmiaru zakresów.

Jeśli plik pozwala innym osobom dopisywać wiersze w dowolnym miejscu, wymóg sortowania może być trudny do utrzymania. Wtedy reguła działania powinna być widoczna dla właściciela procesu, a nie schowana w jednym parametrze formuły. Dobry wynik dzisiaj nie daje gwarancji po następnym imporcie.

Znaki wieloznaczne i dosłowne kody

Tryb dopasowania 2 w piątym argumencie uruchamia znaki wieloznaczne. Gwiazdka odpowiada dowolnemu ciągowi znaków, znak zapytania pojedynczemu znakowi, a tylda pozwala potraktować te znaki dosłownie. To osobna decyzja wobec domyślnego dopasowania dokładnego.

Dla naszego katalogu wyszukanie tekstu P-2* w tym trybie znajdzie P-20. Jeśli jednak masz wiele kodów rozpoczynających się od P-2, otrzymasz jeden z nich zgodnie z kierunkiem szukania. Taka formuła nie tworzy listy wszystkich pasujących produktów.

Nie używałbym dopasowania wzorca do pobierania ceny, jeśli kod ma być jednoznaczny. Do listy wielu wyników lepiej dobrać narzędzie filtrujące. Dopasowanie fragmentu tekstu może być przydatne przy eksploracji słownika, ale łatwo pomylić je z ustaleniem tożsamości rekordu.

Zwracanie kilku kolumn i rozlanie

Zamiast pojedynczej kolumny odpowiedzi możesz wskazać kilka sąsiednich. Wróć do katalogu A1:C4 i wpisz formułę poza jego obszarem, na przykład w F2:

=X.WYSZUKAJ(E2;$B$2:$B$4;$A$2:$C$4)

Dla P-20 wyniki zajmą F2:H2: „Segregator”, P-20 i 18. Pierwszy dopasowany wiersz jest nadal jeden, ale ma trzy zwracane pola. Komórki po prawej muszą być wolne, żeby wynik mógł się rozlać.

Zasady rozlania i blokowania obszaru opisuje Microsoft. Formułę rozlewającą wstaw poza tabelą Excela; same zakresy źródłowe mogą być kolumnami takiej tabeli. Przy problemie z rozlaniem sprawdzam zajęte komórki i scalone pola, zamiast zamieniać błąd na pusty tekst.

Wyszukiwanie poziome

Mechanizm działa również dla wierszy. W osobnym arkuszu wpisz do B1:D1 kody P-10, P-20 i P-30, a do B2:D2 ceny 12, 18 i 7. Szukanie P-20 w pierwszym wierszu i zwrócenie odpowiadającej pozycji z drugiego da 18.

=X.WYSZUKAJ("P-20";B1:D1;B2:D2)

Warto sprawdzić orientację obu zakresów. Dla poziomego szukania pozycje odpowiadają kolumnom, dla pionowego wierszom. Ta sama zasada wyrównania obowiązuje w obu przypadkach. Funkcja nie odgaduje, czy raport został obrócony ani gdzie zaczyna się właściwy nagłówek.

Poziome wyszukiwanie przydaje się do czytania istniejącego zestawienia, ale dla rosnącego źródła rozważyłbym tabelę z rekordami w wierszach. Dopisywanie kolejnych okresów jako nowych kolumn może utrudnić dalsze przetwarzanie. Wybór orientacji funkcji nie zastępuje decyzji o strukturze danych.

Jak przygotowuję słownik do wyszukiwania

Sprawdzam typ klucza, jego kompletność i powtarzalność. Tekstowy identyfikator z zerami na początku zachowuję jako tekst. Jeżeli źródło odróżnia wielkie i małe litery, pamiętam, że zwykłe X.WYSZUKAJ nie zapewnia takiego rozróżnienia.

Przed kopiowaniem formuły porównuję kilka par źródło i wynik. Biorę pierwszą, środkową i ostatnią pozycję oraz kod nieobecny. Potem sprawdzam powtórzony kod i ustalam, czy pierwszy lub ostatni wynik ma sens biznesowy. Te przypadki pomagają zauważyć błędy, których nie pokaże samo otwarcie pliku.

Gdy potrzebuję sumy wielu transakcji dla produktu, przechodzę do SUMA.WARUNKÓW. X.WYSZUKAJ pobiera dopasowaną odpowiedź, a nie sumuje automatycznie wszystkich wierszy tego samego kodu. To rozróżnienie jest szczególnie ważne, gdy słownik i lista transakcji wyglądają podobnie.

Przekazanie pliku i znaczenie odpowiedzi

Opisuję pobrane pole jako „Cena katalogowa”, żeby odróżnić je od ceny rzeczywiście zapłaconej. Dopisuję też, co ma zrobić odbiorca komunikatu o braku: poprawić identyfikator, uzupełnić słownik czy przekazać rekord do wyjaśnienia.

Przy kopiowaniu formuły do raportu miesięcznego sprawdzam, czy nadal wskazuje właściwy okres słownika. Stara nazwa arkusza może po cichu zachować ceny z poprzedniego miesiąca. Nazwane kolumny pomagają czytać formułę, ale nie wybierają za mnie odpowiedniej wersji źródła.

Jeżeli zamieniam wyniki na wartości, oznaczam datę utrwalenia. Taki raport przestaje reagować na zmiany katalogu. To bywa właściwe dla archiwalnego zestawienia, pod warunkiem że odbiorca wie, jaki moment ono przedstawia.

Ćwiczenie z pięcioma przypadkami

Na pierwotnym katalogu pobierz nazwy dla P-10 i P-30, cenę dla P-20 oraz komunikat dla P-99. Oczekiwane odpowiedzi to „Notes”, „Teczka”, 18 i wybrany tekst braku. Następnie dopisz drugi P-20 z ceną 19 i porównaj kierunki szukania po rozszerzeniu zakresów.

Na osobnej tabeli progów sprawdź liczby 9, 10, 19 i 20. Wybierając najbliższy mniejszy próg, otrzymasz „Małe”, „Średnie”, „Średnie” i „Duże”. Spróbuj wyjaśnić każde przypisanie bez patrzenia na formułę. Jeżeli definicja przedziałów jest niejasna na kartce, arkusz tego nie naprawi.

Na końcu zapisz przy słowniku jego właściciela i datę aktualizacji. Funkcja może bezbłędnie pobierać nieaktualną cenę. Dobra formuła jest potrzebna, ale do użytecznego raportu potrzebujesz jeszcze aktualnego źródła i jasnej definicji dopasowania.