WYSZUKAJ.POZIOMO w Excelu: HLOOKUP, kod i próg

WYSZUKAJ.POZIOMO pomaga wtedy, gdy identyfikatory leżą w górnym wierszu tabeli, a potrzebne informacje poniżej. W angielskim Excelu ta funkcja nazywa się HLOOKUP. Ja zaczynam od sprawdzenia układu danych i rodzaju dopasowania. Najwięcej kłopotów powoduje pominięty argument, który pozwala funkcji zwrócić przybliżony wynik zamiast zgłosić brak produktu.

Pokażę dwa różne zadania: znalezienie produktu po kodzie oraz wybór stawki z uporządkowanych progów. Wszystkie dane są syntetyczne. Rozdzielenie tych sytuacji jest ważniejsze niż zapamiętanie samej składni, bo każda wymaga innej umowy z tabelą źródłową.

Składnia i kierunek szukania

Dokumentacja WYSZUKAJ.POZIOMO opisuje cztery argumenty:

=WYSZUKAJ.POZIOMO(szukana_wartość;tabela_tablica;nr_indeksu_wiersza;[przeszukiwany_zakres])

Funkcja przeszukuje pierwszy wiersz wskazanego zakresu. Po znalezieniu kolumny zwraca wartość z tej samej kolumny i wiersza określonego numerem. Wiersz 1 oznacza górny wiersz zakresu, nawet gdy tabela zaczyna się w połowie arkusza. Numer w formule nie jest numerem wiersza widocznym na lewym brzegu okna.

Przykłady PL używają średników między argumentami i przecinków dziesiętnych. Angielskie odpowiedniki mają nazwę HLOOKUP, przecinki między argumentami oraz kropki dziesiętne. Rzeczywisty zapis zależy również od ustawień regionalnych. Przy kopiowaniu cudzej formuły sprawdź zarówno nazwę funkcji, jak i separatory.

Dokładne dopasowanie kodu produktu

Wpisz poniższy układ w B2:E4. Pierwsza kolumna tabeli w artykule pokazuje tylko numer wiersza arkusza, nie jest częścią danych do wyszukania.

Wiersz 2 zawiera kody, wiersz 3 cenę w umownych jednostkach, a wiersz 4 zapas. W G2 wpisz P-30. W G3 umieść:

=WYSZUKAJ.POZIOMO(G2;$B$2:$E$4;2;FAŁSZ)

Oczekiwany wynik to 25. Kod występuje w D2, a drugi wiersz zakresu to wiersz 3 arkusza, więc odczytujemy D3. Zmiana trzeciego argumentu na 3 zwróci zapas 8 z D4. W ten sposób można prześledzić odwołanie bez zgadywania, co oznacza indeks.

Znaki dolara utrzymują zakres podczas kopiowania. Odwołanie G2 pozostaje względne. Jeśli przygotowujesz listę wielu wyszukiwanych kodów, skopiuj formułę w sposób odpowiadający położeniu tej listy i sprawdź drugi wynik. Poprawny pierwszy wiersz nie dowodzi, że przesuwanie odwołań jest zgodne z planem.

Brak produktu, zero i niepoprawny zakres

Dla P-99 dokładne dopasowanie zwróci błąd #N/D!. Dla P-20 i indeksu 3 wynik wyniesie 0, bo taki zapas zapisaliśmy w C4. Brak kodu oraz zerowy zapas oznaczają różne sytuacje. W raporcie zostawiam tę różnicę widoczną, zamiast automatycznie zamieniać każdy błąd na zero.

Jeżeli chcesz pokazać czytelny komunikat przy braku kodu, rozważ JEŻELI.ND. Najpierw jednak obejrzyj sam wynik wyszukiwania. Ogólne przechwytywanie błędów może ukryć także zły indeks albo przypadkowo skrócony zakres. Informacja o usterce jest przydatna, dopóki wyjaśniasz przyczynę.

W naszym trzywierszowym zakresie indeks 4 wykracza poza tabelę i daje #ADR!. To inny problem niż brak produktu. Sprawdź kolejno: szukany kod, górny wiersz zakresu, wysokość tabeli i numer wyniku. Taka kolejność zwykle pozwala zawęzić miejsce pomyłki bez przebudowy całego arkusza.

Przybliżone dopasowanie oznacza próg

Pominięcie czwartego argumentu lub ustawienie PRAWDA włącza przybliżone dopasowanie. Wymaga ono rosnącego porządku pierwszego wiersza. Gdy nie ma równej wartości, wybierany jest największy próg mniejszy od szukanej wartości. Określenie „najbliższe dopasowanie” bez tego wyjaśnienia łatwo prowadzi do błędu.

W osobnym obszarze B8:E9 umieść syntetyczne progi liczby sztuk i stawki rabatu. Stawki opisują wyłącznie ćwiczenie, bez odniesienia do rzeczywistej oferty.

=WYSZUKAJ.POZIOMO(18;$B$8:$E$9;2;PRAWDA)

Wynik to 2%, bo ostatni osiągnięty próg wynosi 10. Próg 20 jest liczbowo bliższy 18, ale nie został osiągnięty. Dla 20 otrzymasz 5%. Właśnie dlatego tabela progowa i tabela kodów potrzebują różnych ustawień, nawet gdy obie mieszczą się w dwóch wierszach.

Granice, duplikaty i porządek danych

W tabeli progów sprawdź wartości dokładnie na granicy oraz po obu jej stronach. Dla 9 obowiązuje stawka 0%, dla 10 już 2%, a dla 11 nadal 2%. Wartość mniejsza od pierwszego progu daje #N/D!. Gdy tabela zaczynałaby się od 10, zapytanie o 9 nie uzyskałoby stawki z żadnego wcześniejszego przedziału.

Sortuj całe kolumny tabeli progowej razem. Przestawienie samych progów bez odpowiadających im stawek niszczy powiązanie danych. Sam rosnący pierwszy wiersz nie wystarczy wtedy do uzyskania sensownego wyniku. Ja sprawdzam również opis granic i datę obowiązywania reguł, bo poprawna formuła może odczytać nieaktualną tabelę.

Dla dokładnego dopasowania dopilnuj jednoznaczności kodów. Jeśli dwa produkty mają ten sam klucz, trzeba ustalić regułę wyboru albo poprawić słownik. Nie traktuj pojedynczego zwróconego wyniku jako dowodu, że kod jest unikalny. Osobno sprawdź spacje, tekstowe zapisy liczb i zgodność identyfikatora pomiędzy źródłami.

WYSZUKAJ.POZIOMO nie rozróżnia wielkości liter. Przy dokładnym wyszukiwaniu tekstu obsługuje też symbole wieloznaczne: ? i *, z tyldą jako znakiem ucieczki. Jeżeli identyfikatory zawierają takie znaki dosłownie, uwzględnij te reguły z dokumentacji. W prostym słowniku z tego artykułu unikam ich, żeby ćwiczenie dotyczyło samego kierunku i trybu wyszukiwania.

Kiedy rozważyć X.WYSZUKAJ

X.WYSZUKAJ pozwala osobno wskazać zakres kluczy i zakres wyników, a domyślnie szuka dokładnie. Odpowiednik naszego wyszukania ceny wygląda tak:

=X.WYSZUKAJ(G2;$B$2:$E$2;$B$3:$E$3;"brak kodu")

Sprawdź jednak wersję odbiorcy: funkcja nie jest dostępna w Excelu 2016 i 2019. Przy utrzymywaniu starszego pliku HLOOKUP pozostaje przydatną umiejętnością. Szersze porównanie opisuję w artykule o funkcjach wyszukiwania.

Ćwiczenie przed użyciem własnych danych

Wróć do tabeli produktów. Odczytaj cenę P-10, zapas P-20 i cenę P-99. Oczekiwane wyniki to odpowiednio 12, 0 oraz #N/D!. Następnie na tabeli progowej sprawdź 19, 20 i 21. Opisz słowami, dlaczego stawka zmienia się właśnie przy 20.

Na końcu zapisz obok formuły, czy szukasz identyfikatora, czy progu, oraz z którego wiersza odczytujesz wynik. To mała dokumentacja, która pomaga przyszłemu użytkownikowi. Zamiast odgadywać znaczenie ostatniego argumentu, zobaczy decyzję, na której opiera się raport.