WYSZUKAJ.PIONOWO w Excelu: cennik, klucz i dopasowanie

Masz listę zamówień i osobny cennik. W zamówieniach zapisano kody produktów, a do raportu potrzebujesz jeszcze nazwy i ceny. Możesz przeklejać je ręcznie. Możesz też wskazać Excelowi kod i pozwolić mu znaleźć odpowiedni wiersz. Właśnie do takiej pracy służy WYSZUKAJ.PIONOWO, czyli angielskie VLOOKUP.

Pokażę tę funkcję na małym, syntetycznym cenniku. Najwięcej uwagi poświęcę ostatniemu argumentowi, duplikatom i kopiowaniu formuły. To w tych miejscach łatwo dostać wiarygodnie wyglądający wynik, który odpowiada na inne pytanie niż planowałeś.

Co robi WYSZUKAJ.PIONOWO

Funkcja szuka wartości w pierwszej kolumnie wskazanego zakresu. Gdy znajdzie dopasowanie, zwraca zawartość wybranej kolumny z tego samego wiersza. Numer tej kolumny liczysz wewnątrz zakresu, zaczynając od jedynki. Tak działanie i składnię opisuje dokumentacja Microsoft.

Ja zaczynam od ustalenia klucza. W cenniku będzie nim kod produktu. W zestawieniu pracowników mógłby to być identyfikator pracownika, a w słowniku oddziałów kod placówki. Nazwa produktu bywa słabym kluczem: dwie pozycje mogą mieć podobne nazwy, a jedna dodatkowa spacja potrafi utrudnić dopasowanie.

Ważna jest też jednostka danych. Cennik powinien mieć jeden aktualny wiersz na kod. Jeżeli zawiera historię cen, sam kod nie określa jeszcze właściwej pozycji. Potrzebujesz również daty obowiązywania. WYSZUKAJ.PIONOWO nie dopowie za Ciebie tej reguły.

Mały cennik do pierwszej formuły

Wpisz poniższe dane do A1:C4. Kody są tekstem, a ceny zwykłymi liczbami. To przykład syntetyczny, bez podatków, rabatów i innych zasad rozliczania sprzedaży.

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

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

Excel szuka P-20 w A2:A4. Dopasowanie znajduje się w drugim wierszu danych, czyli w wierszu arkusza numer 3. Trzecia kolumna zakresu A:C to C, więc wynikiem jest 18. Gdy zmienisz trzeci argument na 2, dostaniesz „Segregator”.

Ostatni argument FAŁSZ wymaga dopasowania dokładnego. Cennika nie trzeba wtedy sortować według kodów. W przykładach używam polskich nazw funkcji i średników. Angielski zapis tej samej formuły, przy ustawieniach używających przecinka jako separatora argumentów, wygląda tak:

=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

Język funkcji i separator argumentów zależą od konfiguracji Excela. Gdy formuła jest odrzucana już podczas wpisywania, sprawdź te ustawienia, zanim zaczniesz poprawiać sam cennik.

Pierwsza kolumna zakresu i numer wyniku

„Pierwsza kolumna” oznacza pierwszą kolumnę podanego zakresu, a nie zawsze kolumnę A arkusza. Gdy cennik zajmuje D:F, szukanie zaczyna się w D. Wtedy numer 3 wskazuje F. To przydaje się, kiedy pracujesz w większym arkuszu z kilkoma tabelami obok siebie.

Załóżmy, że kod masz w B, nazwę w C, a cenę w D. Zakres B2:D4 jest poprawny, a cena nadal ma numer 3. Wpisanie numeru 4 dlatego, że D jest czwartą kolumną arkusza, da błąd odwołania: wskazany zakres ma tylko trzy kolumny.

Zwykłe WYSZUKAJ.PIONOWO zwraca dane z prawej strony klucza lub z samej kolumny klucza. Jeżeli cena znajduje się w A, a kod w B, zakres A:B nie pomoże: Excel szukałby kodu w A. W nowym skoroszycie rozważyłbym wtedy X.WYSZUKAJ, które przyjmuje osobne zakresy szukania i zwracania.

Dlaczego wpisuję FAŁSZ jawnie

Pominięcie czwartego argumentu włącza domyślnie dopasowanie przybliżone. Microsoft wymaga dla niego rosnącego uporządkowania pierwszej kolumny. W niesortowanym słowniku wynik może wyglądać sensownie, mimo że nie jest właściwym dopasowaniem. Dlatego przy kodach produktów wpisuję FAŁSZ za każdym razem.

Sam brak błędu nie potwierdza poprawności raportu. Jeśli zniknął kod produktu, oczekuję sygnału o braku dopasowania. Cena sąsiedniego produktu byłaby znacznie gorszym wynikiem, bo mogłaby spokojnie przejść do sumy i prezentacji.

Dopasowanie dokładne nie rozróżnia wielkości liter, co potwierdza dokumentacja funkcji arkuszowej. Jeżeli Twoje identyfikatory odróżniają ab od AB, trzeba zaplanować inne rozwiązanie. „Dokładne” w nazwie trybu ma określone znaczenie techniczne; nie zastępuje wszystkich reguł tożsamości używanych w systemie źródłowym.

Przy tekstowym kluczu tryb FAŁSZ dopuszcza też znaki wieloznaczne: gwiazdkę i znak zapytania. Jeśli te znaki są częścią rzeczywistego kodu, wymagają poprzedzenia tyldą, zgodnie z dokumentacją. Nasze kody P-10, P-20 i P-30 nie zawierają takich znaków. To kolejny powód, żeby przed wyszukiwaniem poznać format identyfikatora.

Kiedy dopasowanie przybliżone ma sens

Przybliżone szukanie jest użyteczne dla progów. Zbuduj drugi, syntetyczny słownik w H1:I4:

Wpisz w K2 liczbę 14 i użyj:

=WYSZUKAJ.PIONOWO(K2;$H$2:$I$4;2;PRAWDA)

Wynik to „Średnie”. Największy próg nieprzekraczający 14 wynosi 10. Dla 20 wynikiem jest „Duże”, a dla 9 „Małe”. W tym modelu kategoria „Średnie” obejmuje wartości od 10 włącznie do 20 wyłącznie.

Zauważ, że szukasz dolnej granicy przedziału. Gdy biznes dostarczył tabelę z górnymi granicami, ta formuła nie realizuje automatycznie tej samej reguły. Najpierw przepisz definicję przedziałów i rozstrzygnij, do której grupy trafia wartość na granicy.

Wartość mniejsza od najniższego progu spowoduje błąd braku dopasowania. W naszym przykładzie liczba ujemna powinna zresztą skłonić do sprawdzenia danych. Może oznaczać zwrot, pomyłkę albo inny rodzaj dokumentu, którego słownik nie obejmuje.

Kopiowanie formuły bez przesuwania cennika

Dolar w $A$2:$C$4 blokuje zarówno kolumny, jak i wiersze zakresu. Po skopiowaniu formuły z F2 do F3 odwołanie E2 zmieni się na E3, ale cennik pozostanie w tym samym miejscu. To oczekiwane zachowanie, jeśli kolejne wiersze E zawierają następne zamówienia.

Bez dolarów zakres A2:C4 przesunąłby się do A3:C5. Pierwszy produkt wypadłby z wyszukiwania, a na dole pojawiłaby się dodatkowa komórka. Pierwszy wynik mógłby nadal być dobry, podczas gdy kolejne zaczęłyby się psuć. Dlatego sprawdzam formułę również w środku i na końcu kolumny.

Jeśli źródło regularnie rośnie, warto przekształcić je w tabelę Excela. Odwołania strukturalne ułatwiają wskazywanie jej kolumn. Sam mechanizm dolarów wyjaśniam szerzej w tekście o tym, jak blokować odwołania w Excelu. Przy małym przykładzie zakres komórek pozwala lepiej zobaczyć, co dokładnie jest przeszukiwane.

Duplikaty: pierwszy wynik może być za mało

Do cennika dopisz drugi wiersz P-20 z ceną 19 i rozszerz zakres formuły tak, żeby go obejmował. Dokładne wyszukiwanie zwróci pierwsze dopasowanie od góry. Nie pokaże ostrzeżenia, że ten sam kod występuje dwukrotnie. Nie wybierze też automatycznie najnowszej ceny.

Ja traktuję to jako pytanie o dane. Czy oba rekordy opisują ten sam produkt? Czy jeden jest archiwalny? Czy cena zależy od oddziału? Dopiero odpowiedź pozwala określić właściwy klucz. Czasem trzeba dodać drugą kolumnę identyfikującą, czasem odfiltrować historię, a czasem zwyczajnie poprawić słownik.

Nie usuwaj duplikatów wyłącznie po kodzie, jeśli niosą różne informacje. Możesz stracić prawidłowe warianty produktu. Z drugiej strony lista sprzedaży ma prawo zawierać wiele wierszy tego samego kodu. Tam pytanie o łączną wartość wymaga agregacji, na przykład SUMA.WARUNKÓW, a nie pobrania jednej ceny z pierwszego wiersza.

Dwa warunki i odpowiedzialność za klucz

Czasem po pierwszym udanym wyszukiwaniu pada pytanie: „A teraz weź cenę dla konkretnego magazynu”. Jeśli kod P-20 występuje w dwóch magazynach, dotychczasowa formuła przestaje wystarczać. Nie naprawi jej zamiana numeru kolumny ani rozszerzenie zakresu. Zmieniło się pytanie: szukasz teraz pary kod i magazyn.

Jedna z możliwości to przygotowanie pomocniczego, jednoznacznego klucza dla takiej pary. Wtedy słownik musi mieć dokładnie jeden wiersz na tę kombinację. Przy sklejaniu tekstów warto uważać na kolizje: zestawy AB i C oraz A i BC po prostym połączeniu dają ten sam ciąg. Separator również musi być dobrany do dozwolonych znaków w danych. W większych procesach wolę zachować warunki jako osobne pola i użyć narzędzia, które obsługuje łączenie po kilku kolumnach.

To samo dotyczy cen historycznych. Wybór ostatniego wiersza nie gwarantuje ceny obowiązującej w dniu zamówienia. Możesz mieć późniejsze korekty, daty przyszłe albo kilka wersji umowy. Zapisz regułę, na przykład „cena aktywna na dzień zamówienia dla danego oddziału”, zanim wybierzesz funkcję. Bez tej definicji nawet sprytna formuła tylko sprawniej wybierze przypadkowy rekord.

Nie próbuję upchnąć każdej relacji w jednej komórce. Gdy trzeba połączyć dwie rozbudowane tabele, zachować wiele dopasowań i raportować rekordy bez pary, rozważam Power Query. WYSZUKAJ.PIONOWO pozostaje wygodne dla jasno określonego słownika, który ma oddać jedną wartość dla jednego klucza.

Jak rozumiem brak dopasowania

Dla P-99, którego nie ma w cenniku, dokładne wyszukiwanie zwróci #N/D!. Zanim zamienię ten błąd na komunikat, sprawdzam, czy kod rzeczywiście nie istnieje. Przyczyną może być też liczba przechowywana jako tekst, dodatkowa spacja albo zbyt krótki zakres.

Szczególnie uważaj na identyfikatory z zerami na początku. Kod tekstowy 0012 może mieć inne znaczenie niż liczba 12. Automatyczna konwersja wszystkich kodów na liczby usuwa tę informację. Typ danych powinien wynikać z definicji klucza, a nie z chęci szybkiego pozbycia się błędu.

Gdy brak jest przewidzianym przypadkiem, mogę obsłużyć sam błąd #N/D! przez JEŻELI.ND. Dokumentacja tej funkcji opisuje właśnie tak zawężoną obsługę:

=JEŻELI.ND(WYSZUKAJ.PIONOWO(E2;$A$2:$C$4;3;FAŁSZ);"Brak w cenniku")

Inne błędy pozostają widoczne. To pomaga odróżnić nieznany kod od uszkodzonego odwołania. Komunikat jest tekstem, więc kolumna przestaje być wyłącznie liczbowa. Do dalszych obliczeń często wolę osobną kolumnę statusu i osobną cenę, żeby użytkownik widział brak, zamiast bezwiednie go pomijać.

Czy cena zero oznacza brak produktu

W cenniku może istnieć prawidłowa cena równa zero, na przykład dla elementu dodawanego bez opłaty. Nie używam więc zera jako uniwersalnego znacznika braku dopasowania. Gdy zastąpisz nim każdy brak, raport przestanie odróżniać bezpłatny produkt od nieznanego kodu.

Podobnie traktuję pustą komórkę w kolumnie ceny. Znalezienie kodu i znalezienie użytecznej ceny to dwa osobne warunki. Dobrze przygotowany słownik powinien określać, czy brak ceny jest dozwolony, kto go uzupełnia i co ma wtedy pokazać raport. Samo wyszukiwanie nie sprawdza kompletności znalezionego wiersza. Przed sumowaniem wartości zamówień sprawdzam więc także brakujące ceny, nawet jeśli wszystkie kody udało się dopasować do słownika.

Zmiany układu i drugi arkusz

Numer kolumny wyniku jest zapisany w formule. Jeśli do zakresu wstawisz nową kolumnę przed ceną, trzeba sprawdzić, czy trzeci argument nadal wskazuje właściwe pole. Arkusz może zwracać nazwę albo kategorię produktu bez żadnego komunikatu o błędzie. To ryzyko szczególnie łatwo przeoczyć po kosmetycznym porządkowaniu pliku.

Cennik może znajdować się w osobnym arkuszu. Dla arkusza o nazwie „Cennik produktów” odwołanie wygląda tak:

=WYSZUKAJ.PIONOWO(E2;'Cennik produktów'!$A$2:$C$4;3;FAŁSZ)

Apostrofy obejmują nazwę ze spacją. Najbezpieczniej wskazać zakres myszą podczas budowania formuły, żeby Excel sam wpisał nazwę arkusza. Oddzielny arkusz pomaga uporządkować słownik, ale nie gwarantuje jego aktualności.

Link do innego skoroszytu dokłada zależność od pliku źródłowego, jego ścieżki i dostępności. W raporcie przekazywanym innym osobom sprawdzam, czy odbiorca ma również dostęp do źródła. Sam fakt, że u autora widać zapisaną wartość, nie potwierdza, że odbiorca będzie potrafił odświeżyć dane.

Jak sprawdzić własny raport

Wybieram kilka znanych kodów i ręcznie wskazuję oczekiwany wiersz słownika. Chcę zobaczyć pierwszy produkt, ostatni produkt i pozycję ze środka. Dodaję również kod nieobecny, bo poprawna obsługa braku jest częścią działania raportu. Jeden dobry przykład mówi niewiele o całej kolumnie.

Przy progach sprawdzam wartość dokładnie na granicy, tuż poniżej i tuż powyżej. W naszym syntetycznym słowniku użyłbym 9, 10, 19 i 20. Oczekiwane klasy to kolejno „Małe”, „Średnie”, „Średnie” i „Duże”. Te przypadki pozwalają zauważyć pomyloną definicję przedziałów.

Na koniec opisuję, skąd pochodzi cennik i z jakiej daty są ceny. Wyszukiwanie może być technicznie poprawne, a raport nadal nieaktualny. Właściciel słownika i data obowiązywania są tu równie potrzebni jak dobrze ustawiony argument FAŁSZ.

Jak przekazać arkusz kolejnej osobie

Przed oddaniem pliku sprawdzam, czy nagłówki mówią, co właściwie zostało pobrane. „Cena z cennika” jest lepszym opisem niż „Wynik”. Jeżeli raport zawiera też cenę faktycznie zapłaconą, różnica pomiędzy tymi polami musi być widoczna. Równe liczby w kilku wierszach nie oznaczają, że obie kolumny mają tę samą definicję.

Warto również wskazać komórki wejściowe. Odbiorca powinien wiedzieć, gdzie może dopisać zamówienie i gdzie aktualizuje się słownik. Nadpisanie formuły ręcznie wpisaną ceną jest szybkie, ale kolejny raport może już jej nie przeliczyć. Przy sprawdzaniu wybieram więc również kilka komórek z dalszej części kolumny, żeby zobaczyć, czy nadal zawierają formuły.

Jeżeli zapisuję wyniki jako wartości, na przykład do jednorazowego zestawienia, zaznaczam datę takiego eksportu. Zamrożony raport przestaje reagować na zmianę cennika. To może być całkowicie poprawne, o ile odbiorca wie, że ogląda stan z konkretnego momentu. Zapisane wartości i formuły odświeżające dane odpowiadają na różne potrzeby i wymagają innego opisu przy przekazaniu.

Do samodzielnego przećwiczenia dopisz do wcześniejszego zadania kolumnę „Źródło ceny” oraz krótką notatkę o brakujących kodach. Spróbuj tak opisać plik, żeby ktoś mógł go użyć bez rozmowy z Tobą. Jeśli potrafisz wskazać klucz, zakres, kolumnę wyniku, tryb dopasowania i znaczenie braku, formuła staje się czytelną częścią procesu.

Ćwiczenie: nazwa, cena i brakujący kod

Wróć do trzech produktów z początku. W E2:E5 wpisz kolejno P-10, P-30, P-99 i P-20. Zbuduj obok kolumnę z nazwą i kolumnę z ceną. W obu formułach użyj tego samego zablokowanego cennika i dokładnego dopasowania.

Dla pierwszego wiersza oczekujesz „Notes” i 12. Dla drugiego „Teczka” i 7. Trzeci powinien zgłosić brak dopasowania, a czwarty zwrócić „Segregator” i 18. Następnie dopisz nowy produkt na końcu cennika i sprawdź, czy zakres go obejmuje.

Jeżeli umiesz wyjaśnić każdy z tych wyników, masz dobry fundament do dalszej pracy. Następny krok to porównanie tej samej operacji z X.WYSZUKAJ oraz świadomy wybór funkcji pod wersję Excela odbiorcy i strukturę danych.