WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i X.WYSZUKAJ: przykład od początku

Mam listę zamówień i osobną listę produktów. W zamówieniu widzę identyfikator, a potrzebuję liczby sztuk dostępnych w magazynie. Mogę kopiować wartości ręcznie, ale wtedy każde odświeżenie danych zamienia się w kolejną rundę przeklejania. Wolę zapisać regułę: znajdź ten identyfikator w słowniku i zwróć wartość z tego samego wiersza.

Do tego służą funkcje wyszukiwania. Trudność zaczyna się wtedy, gdy identyfikatora brakuje, pojawia się dwa razy albo wygląda jak liczba, choć w jednej tabeli jest tekstem. Excel potrafi bardzo posłusznie wykonać błędnie sformułowane polecenie. Sama obecność wyniku w komórce jeszcze mnie nie uspokaja.

Przejdę przez jeden syntetyczny przykład. Pokażę trzy funkcje, a potem celowo zepsuję dane. Dzięki temu zobaczysz zarówno poprawną formułę, jak i sposób sprawdzenia, czy nadaje się do Twojego raportu. Wszystkie podane liczby są ćwiczeniowe i nie opisują rzeczywistego magazynu.

Nazwy funkcji i wersja Excela

W polskim Excelu używam nazw WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i X.WYSZUKAJ. W angielskim są to odpowiednio VLOOKUP, HLOOKUP i XLOOKUP. W przykładach PL zapisuję argumenty ze średnikiem. Jeżeli Twój Excel oczekuje przecinka, sprawdź ustawienia regionalne oraz język formuł, zamiast zmieniać dane w tabeli.

Zaczynam od sprawdzenia wersji programu u osoby, która będzie korzystała z pliku. Według dokumentacji Microsoft X.WYSZUKAJ nie jest dostępna w Excelu 2016 i 2019. Jest dostępna między innymi w Microsoft 365, Excelu 2021 i 2024. Nowsza funkcja w moim komputerze nie zapewnia zgodności z każdym komputerem w firmie.

W nowym skoroszycie, którego odbiorcy mają odpowiednią wersję, zwykle wybieram X.WYSZUKAJ. Starsze funkcje nadal warto rozumieć: występują w istniejących raportach, a ich ograniczenia pomagają zauważyć zależność między formułą a układem danych. Nie przepisywałbym działającego raportu wyłącznie po to, żeby miał modniejszą nazwę funkcji.

Przygotowuję słownik i klucz zamówienia

W nowym arkuszu wprowadź poniższą tabelę dokładnie do zakresu A1:C5. Litery A, B i C wskazują kolumny, a pierwsza linia tabeli odpowiada nagłówkom w pierwszym wierszu. W tym ćwiczeniu każdy produkt ma jeden identyfikator i jedną liczbę dostępnych sztuk.

Do F2 wpisz P02. Ta komórka reprezentuje identyfikator produktu w pojedynczej pozycji zamówienia. Na razie szukam samego stanu magazynowego. Nie oceniam, czy wystarczy go do obsłużenia całej kolejki zamówień, ponieważ wymagałoby to jeszcze ilości zamówionych sztuk i ustalonej kolejności rezerwacji.

Celowo rozdzielam te pytania. Wyszukanie stanu odpowiada na pytanie o wartość zapisaną przy produkcie. Decyzja o realizacji zamówienia jest kolejnym etapem analizy. Jeśli pomieszam te etapy, łatwo zbuduję raport, w którym te same siedem sztuk obiecam kilku klientom jednocześnie.

Przed rozpoczęciem sprawdzam ręcznie, że P02 występuje raz i ma stan 7. Ten mały punkt odniesienia przyda się przy każdej kolejnej formule. Zamiast od razu kopiować obliczenie do dziesięciu tysięcy wierszy, najpierw chcę rozumieć jeden wynik.

WYSZUKAJ.PIONOWO: szukam w pierwszej kolumnie

W pustej komórce wpisuję poniższą formułę. Wynik dla początkowych danych to 7.

=WYSZUKAJ.PIONOWO(F2;$A$2:$C$5;3;FAŁSZ)

F2 zawiera szukany identyfikator. Zakres obejmuje trzy kolumny słownika. Liczba 3 oznacza trzecią kolumnę tego zakresu, czyli stan. Ostatni argument wymaga dokładnego dopasowania. Funkcja przeszukuje pierwszą kolumnę przekazanego zakresu, dlatego w tym układzie identyfikatory muszą znajdować się w kolumnie A.

Znaki dolara blokują granice słownika podczas kopiowania formuły. Odwołanie F2 pozostawiam względne, aby po skopiowaniu w dół zmieniło się na F3, F4 i kolejne klucze. To prosta różnica, ale bez niej słownik może zacząć przesuwać się razem z formułą, po cichu gubiąc początkowe produkty.

Najważniejszy szczegół to FAŁSZ. Pominięty czwarty argument oznacza wyszukiwanie przybliżone, które ma inne zastosowanie i wymaga właściwego uporządkowania danych. Przy identyfikatorach nie chcę produktu podobnego ani poprzedniego na liście. Chcę dokładnie P02. Zasady argumentów opisuje Microsoft dla WYSZUKAJ.PIONOWO.

X.WYSZUKAJ: wskazuję osobno klucz i wynik

Teraz zapisuję to samo zadanie za pomocą nowszej funkcji. Nadal oczekuję 7, ale zakres wyszukiwania i zakres odpowiedzi wskazuję oddzielnie.

=X.WYSZUKAJ(F2;$A$2:$A$5;$C$2:$C$5;"Brak ID";0)

Zero jawnie ustawia dokładne dopasowanie, chociaż w X.WYSZUKAJ jest ono domyślne. Tekst w czwartym argumencie określa wynik przy braku identyfikatora. To świadoma informacja dla odbiorcy raportu. Nie wpisuję tutaj zera, ponieważ zero już oznacza prawidłowy stan magazynowy produktu P03.

Oba zakresy mają po cztery komórki i odpowiadają sobie pozycjami. Jeżeli klucz znajdzie się w drugiej pozycji zakresu A2:A5, wynik pochodzi z drugiej pozycji C2:C5. Właśnie tę relację sprawdzam, gdy ktoś przesunął jedną kolumnę niezależnie od pozostałych. Ładna formuła nie naprawi słownika, którego kolumny przestały opisywać te same produkty.

Przy braku argumentu opisującego nieznalezioną wartość zobaczysz błąd #N/D. Nie traktuję każdego błędu jako awarii Excela. Czasem jest to najuczciwsza informacja, że danych potrzebnych do wykonania zadania po prostu nie ma.

Wyszukiwanie w lewo i zmiana układu kolumn

Do F3 wpisuję teraz Mysz. Chcę znaleźć odpowiadający jej identyfikator, czyli wartość z kolumny po lewej stronie nazwy. Dla tego ćwiczenia nazwy są jednoznaczne.

=X.WYSZUKAJ(F3;$B$2:$B$5;$A$2:$A$5;"Brak nazwy";0)

Wynik to P02. X.WYSZUKAJ pozwala wskazać kolumnę odpowiedzi po dowolnej stronie kolumny klucza. W podstawowym użyciu WYSZUKAJ.PIONOWO zwraca kolumnę z przekazanego zakresu po prawej stronie jego pierwszej kolumny. Przy takim odwrotnym pytaniu nowsza funkcja jest więc wygodniejsza.

W rzeczywistym modelu nadal preferuję stabilny identyfikator. Nazwa produktu może się zmienić, powtórzyć albo dostać dodatkowy opis. Przykład z nazwą służy pokazaniu kierunku wyszukiwania, a nie zaleceniu, żeby każdą nazwę traktować jak trwały klucz.

Sprawdzam też zwykłe wstawienie nowej kolumny do słownika. WYSZUKAJ.PIONOWO zawiera stałą liczbę 3, która po zmianie układu może wskazywać inne pole. X.WYSZUKAJ odwołuje się bezpośrednio do zakresu odpowiedzi, więc standardowe wstawienie kolumny aktualizuje odwołanie. Nadal testuję rezultat po zmianie struktury; przeniesienie samych wartości lub niezależne sortowanie jednej kolumny może zepsuć obie metody.

WYSZUKAJ.POZIOMO: ten sam pomysł w wierszach

Czasami dostaję raport, w którym produkty biegną w poprzek arkusza. Żeby odtworzyć taki układ, wpisz identyfikatory do A9:D9 i stany do A10:D10. W F2 pozostaje P02.

=WYSZUKAJ.POZIOMO(F2;$A$9:$D$10;2;FAŁSZ)

Wynik znów wynosi 7. Teraz funkcja szuka w pierwszym wierszu zakresu i zwraca wartość z drugiego wiersza, w tej samej kolumnie. Numer wiersza jest liczony względem przekazanego zakresu. Szczegóły opisuje dokumentacja WYSZUKAJ.POZIOMO.

Nie przebudowuję całego zestawu danych tylko po to, żeby użyć konkretnej funkcji. Jeżeli jednak projektuję słownik od początku, zwykle zapisuję jeden produkt w jednym wierszu. Łatwiej wtedy dopisywać rekordy, kontrolować klucze i przechodzić do narzędzi, które pracują na tabelach. Poziomy układ bywa wygodnym widokiem raportu, ale nie musi być najlepszym magazynem danych.

Brak produktu, zero i duplikat oznaczają różne rzeczy

Zmieniam F2 na P99. WYSZUKAJ.PIONOWO zwraca #N/D, a moja formuła X.WYSZUKAJ pokazuje „Brak ID”. Następnie wpisuję P03 i dostaję zero. Oba rezultaty wymagają innego działania: w pierwszym przypadku sprawdzam słownik lub zamówienie, w drugim wiem, że produkt istnieje i zapisany stan wynosi zero.

Teraz przywracam P02 i celowo psuję słownik: zmieniam A5 na P02, a C5 na 99. Domyślne wyszukiwanie zwraca pierwsze pasujące wystąpienie, czyli 7. Gdybym chciał przeszukiwać od końca, użyłbym ostatniego argumentu o wartości minus jeden.

=X.WYSZUKAJ(F2;$A$2:$A$5;$C$2:$C$5;"Brak ID";0;-1)

Ta formuła zwraca 99. Nie oznacza to automatycznie najnowszego stanu, ponieważ kolejność wierszy nie jest datą. Takie wyszukiwanie ma sens dopiero wtedy, gdy ustaliłem i kontroluję regułę porządkowania historii. W zwykłym słowniku produktów drugi P02 traktuję jako problem jakości danych.

=LICZ.JEŻELI($A$2:$A$5;F2)

Dla naszego prostego identyfikatora wynik wynosi teraz 2. Taka kontrola pomaga mi odróżnić znalezienie jednej pozycji od znalezienia wielu. Przy kluczach zawierających znaki wieloznaczne potrzebna jest dodatkowa uwaga, ponieważ LICZ.JEŻELI interpretuje kryterium. Tutaj klucze zawierają wyłącznie literę i cyfry. Po ćwiczeniu przywróć w A5 P04, a w C5 liczbę 5.

Dopasowanie przybliżone stosuję do progów

Przybliżone wyszukiwanie jest przydatne, gdy chcę przypisać zamówienie do przedziału wielkości. W A14:B17 wpisuję poniższe progi bez dodatkowego wiersza nagłówków. Do F4 wpisuję liczbę 37, czyli przykładową liczbę sztuk w zamówieniu.

=WYSZUKAJ.PIONOWO(F4;$A$14:$B$17;2;PRAWDA)

=X.WYSZUKAJ(F4;$A$14:$A$17;$B$14:$B$17;"Brak progu";-1)

Obie formuły zwracają „Średnie”: 37 mieści się od 10 do wartości mniejszej niż 50. Pierwsza wersja wymaga rosnącego porządku progów. W drugiej minus jeden oznacza dokładne dopasowanie albo kolejną mniejszą wartość. Nie należy mylić tego argumentu z ostatnim argumentem przeszukiwania od końca z poprzedniej sekcji.

Sprawdzam szczególnie granice: 10 należy do grupy średniej, a 50 do dużej. Dla wartości minus jeden brakuje dolnego progu. Dla 150 otrzymam grupę zbiorczą, ponieważ w tym modelu ostatni przedział jest otwarty w górę. Jeżeli biznes oczekuje górnego limitu, muszę dopisać osobną kontrolę zakresu.

Tryb dopasowania 1 w X.WYSZUKAJ wybiera dokładną albo kolejną większą wartość. Dla 37 wskazałby próg 50. To może być poprawne przy tabeli górnych granic, ale nie przy naszych dolnych granicach. Zawsze najpierw opisuję przedziały słowami, a dopiero potem wybieram argument funkcji.

Gdy identycznie wyglądające klucze nie pasują

Kolejny test robię na kopii ćwiczeniowego arkusza. W jednej komórce zapisuję liczbę 2, w drugiej tekst „2”. Mogą wyglądać tak samo, ale dokładne wyszukiwanie nie musi ich uznać za ten sam klucz. Sprawdzam rzeczywisty typ i sposób importu danych, zamiast zgadywać na podstawie wyrównania komórki.

Nie zamieniam automatycznie wszystkich kodów na liczby. Kod 002 może mieć inne znaczenie niż 2. Przy identyfikatorach ustalam format z właścicielem danych i stosuję go po obu stronach. Przy ilościach liczba zapisana jako tekst zwykle wymaga innej naprawy niż tekstowy kod produktu.

Podobnie postępuję ze spacjami. Klucz P02 ze spacją na końcu może nie pasować do P02 bez spacji. Funkcja USUŃ.ZBĘDNE.ODSTĘPY pomaga przy zwykłych spacjach, ale nie jest uniwersalnym środkiem czyszczącym. Microsoft zwraca uwagę, że TRIM nie usuwa samodzielnie spacji nierozdzielającej. Najpierw rozpoznaję znak, później dobieram operację.

Nie przykrywam całej formuły szeroką obsługą błędów, zanim ustalę przyczynę. Komunikat „Brak ID” jest użyteczny przy brakującym kluczu. Ten sam komunikat dla uszkodzonego odwołania sugerowałby zupełnie inny problem niż rzeczywisty. Raport powinien pomagać naprawić dane, a nie jedynie wyglądać spokojnie.

Kopiowanie formuły też jest częścią logiki

Gdy pierwsza formuła działa, przygotowuję osobne miejsce na kilka kolejnych pozycji zamówień. Wpisuję kolejno P01, P02, P03 i P99. Oczekuję stanów 12, 7, 0 oraz komunikatu o braku produktu. Dopiero po takiej kontroli rozszerzam obliczenia na cały plik. Ten krok jest szczególnie przydatny, jeśli formułę przygotował ktoś inny albo skopiowałem ją ze starszego raportu.

Zwracam uwagę na każdą część odwołania. Blokada kolumny i blokada wiersza to osobne decyzje. W tym artykule blokuję cały słownik, ponieważ kopiuję wyszukiwanie w dół listy zamówień. W rozbudowanym raporcie kopiowanym także w prawo potrzeba może być inna. Zamiast mechanicznie wciskać klawisz zmiany odwołań, zapisuję, co ma pozostać stałe po przeniesieniu formuły.

Kontroluję też komórki, które ktoś zastąpił ręcznie wpisaną wartością. Jedna poprawka wykonana na szybko potrafi przetrwać kilka kolejnych aktualizacji raportu. Wtedy część wierszy korzysta z nowego słownika, a część nadal pokazuje stare liczby. Dlatego przy cyklicznym odświeżaniu oddzielam dane wejściowe od obliczeń i opisuję miejsce, w którym wolno wprowadzać korekty.

Jeżeli słownik zawiera stan na konkretny dzień, dopisuję tę datę do opisu wyniku. Samo odświeżenie formuły nie pobierze nowej dostawy z systemu magazynowego. Najpierw musi zaktualizować się źródło. To drobiazg, który warto wyjaśnić odbiorcy przed pierwszym użyciem pliku, szczególnie gdy raport wygląda na automatyczny i budzi większe zaufanie niż ręczna lista.

Jak sprawdzam gotowe połączenie zamówień ze słownikiem

Po skopiowaniu formuł sprawdzam kilka znanych produktów oraz wszystkie nietypowe wyniki. Liczę brakujące identyfikatory i niejednoznaczne klucze. Porównuję liczbę pozycji zamówień przed dodaniem kolumny i po nim. Samo wyszukiwanie nie powinno usuwać ani dopisywać zamówień.

Sprawdzam również zakres słownika. Dodanie P05 pod ostatnim wierszem niczego nie zmieni w formule zamkniętej na A2:A5. W powtarzalnym raporcie rozważam tabelę Excela i odwołania strukturalne albo kontrolowane rozszerzanie zakresu. Najpierw jednak ustalam, kto dopisuje produkty i jak raport wykryje nowy rekord. Funkcja nie zgadnie procedury aktualizacji.

Przed przekazaniem pliku zapisuję krótki opis: skąd pochodzi słownik, co oznacza stan, kiedy dane odświeżono i jak obsłużyć brak klucza. Odbiorca powinien wiedzieć, czy widzi bieżący stan, czy fotografię sytuacji z określonego dnia. Nawet bezbłędne dopasowanie może dać nieaktualną odpowiedź.

Przykłady z tego artykułu sprawdzono w lokalnym Microsoft Excel, łącznie z brakami, zerem, duplikatem, progami i różnicą tekst/liczba. To potwierdza wyniki na pokazanych danych. Własny skoroszyt nadal testuję po zmianie źródła, struktury lub wersji programu. Przy większym łączeniu tabel warto też rozumieć relacje i rodzaje JOIN w SQL, bo problem jednoznaczności klucza wraca niezależnie od narzędzia.

Na początek proponuję jedno ćwiczenie: dopisz zamówienie z nieistniejącym produktem, a potem drugi wpis tego samego produktu w słowniku. Jeśli potrafisz wyjaśnić oba wyniki i wskazać osobę odpowiedzialną za poprawkę danych, rozumiesz już więcej niż samą składnię funkcji. W raporcie właśnie ta wiedza zwykle okazuje się najcenniejsza.