WYSZUKAJ.POZIOMO w Excelu
Funkcja WYSZUKAJ.POZIOMO jest w Excelu bliźniaczą funkcją do WYSZUKAJ.PIONOWO. jest przydatna, gdy potrzebujesz wyszukać wartość w poziomie zamiast w pionie. Może być używana w różnych celach, np. do tworzenia zaawansowanych arkuszy kalkulacyjnych, w których dane są dynamicznie aktualizowane na podstawie wprowadzonych wartości.
Jeśli znasz już WYSZUKAJ.PIONOWO, to wszystkie najważniejsze założenia są takie same dla WYSZUKAJ.POZIOMO i możesz przejść do czytania sekcji „WYSZUKAJ.POZIOMO, gdy znam już WYSZUKAJ.PIONOWO”.
WYSZUKAJ.POZIOMO w Excelu – do czego jej używać?
Funkcja WYSZUKAJ.POZIOMO w programie Microsoft Excel służy do wyszukiwania wartości w wierszu i zwracania wartości z tego samego lub innego wiersza w tej samej tabeli. Innymi słowy, ta funkcja pozwala na wyszukanie wartości w wierszu i zwrócenie wartości z określonej kolumny w tym samym wierszu lub w innym wierszu.
Na rysunku poniżej zostało to zobrazowane. W pierwszym wierszy Excel szuka kluczowej wartości (identyfikatora). Gdy go znajdzie, to przechodzi wtedy do wskazanego przez Ciebie wiersza i to jest właśnie ta komórka (wartość), której szukaliśmy.
Rysunek 1. Schemat działania w Excelu funkcji WYSZUKAJ.POZIOMO.
Przeszukiwanie szerokich tabel za pomocą WYSZUKAJ.POZIOMO
Wyobraź sobie, że masz tabelę, która jest bardzo szeroka, a niekoniecznie długa (można powiedzieć, że jest „pozioma”). Takie tabele zdarzają się dużo rzadziej niż tabele „pionowe”, ale czasami taki układ okazuje się przydatny. Przykładem, może być tabela, w której kolejne kolumny to kolejne daty (dni) i wówczas tabela przedstawia coś w rodzaju osi czasu.
W tabeli poniżej kolejne kolumny to kolejne dni roku. Zapisany tam jest status pracy poszczególnych pracowników („Praca”, „Urlop”, itp.). W pewnym miejscu (na początku arkusza w tym przykładzie) wyszukujesz informacji na temat statusu pracy dla wybranego dnia. WYSZUKAJ.POZIOMO wówczas szuka kolumny ze wskazaną datą, a następnie przesuwa się do wiersza wskazanego wewnątrz funkcji.
Szczegółowe informacje znajdziesz w kolejnych sekcjach tego artykułu, opisujących konkretnie działanie tej funkcji w Excelu.
Rysunek 2. Tabela ze statusem pracy w kolejnych dniach.
Wybór jednej z podanych możliwości w Excelu za pomocą WYSZUKAJ.POZIOMO
Działa podobnie jak w przypadku WYSZUKAJ.PIONOWO, ale w przypadku, gdy tabela pomocnicza jest „pozioma”. Na rysunku poniżej znajdziesz tabelę z różnymi stawkami za nadgodziny.
W tabeli głównej należy obliczyć całkowite wynagrodzenie za nadgodziny, które zależy od ilości godzin (podane są w tabeli), od podstawowej stawki wynagrodzenia (podana w komórce obok tabeli) oraz od stawki nadgodzin, zależnej od rodzaju tych nadgodzin -> tę informację należy wyszukać w tabelce pomocniczej, która w tym przypadku ma układ poziomy – dlatego użyjemy właśnie funkcji WYSZUKAJ.POZIOMO.
Rysunek 3. W tabeli pomocniczej znajdują się stawki przypisane do różnego rodzaju nadgodzin.
Oto działanie funkcji WYSZUKAJ.POZIOMO w Excelu
Funkcja WYSZUKAJ.POZIOMO działa w sposób podobny do funkcji WYSZUKAJ.PIONOWO w Excelu, ale wyszukuje wartości w poziomie zamiast w pionie. Funkcja ta pobiera cztery argumenty: szukaną wartość, zakres komórek do wyszukania, numer wiersza, z którego ma zostać zwrócona wartość oraz flagę dokładnego dopasowania (opcjonalnie).
=WYSZUKAJ.PIONOWO(1. szukana wartość ; 2. tabela ; 3. numer wiersza ; 4. rodzaj dopasowania)
Funkcja WYSZUKAJ.POZIOMO zaczyna od poszukiwania szukanej wartości w pierwszym wierszu zakresu komórek określonego jako drugi argument funkcji. Gdy funkcja znajdzie szukaną wartość, pobiera wartość z komórki o określonym numerze wiersza (w tej samej kolumnie), określonym jako trzeci argument funkcji. Jeśli szukana wartość nie zostanie odnaleziona w pierwszym wierszu, funkcja przeszukuje kolejne wiersze zakresu komórek aż do momentu znalezienia szukanej wartości.
Rysunek 4. Przykład działania WYSZUKAJ.POZIOMO w Excelu.
- Wartość kluczowa – innymi słowy jest to identyfikator, czyli coś co jednoznacznie identyfikuje nam szukany element. Może to być np. data, numer produktu, numer zamówienia, itd.
- Tabela – wskazujesz tabelę, w której znajdują się potrzebne Ci dane. Ma ona zazwyczaj układ „poziomy” – nie różni się technicznie niczym od tabeli pionowej, ale zazwyczaj tabela „pozioma” ma nagłówki w pierwszej kolumnie, a każdy wiersz to osobny rodzaj informacji.
- Numer wiersza – WYSZUKAJ.POZIOMO przeszukuje tabelę z lewej do prawej, aż znajdzie wartość kluczową (to co podaliśmy w argumencie nr 1). Wtedy wiadomo, że cała kolumna opisuje ten szukany przez nas element. Ale tabela ma np. 30 wierszy, a funkcja może wyświetlić wartość tylko jednej z nich, więc ten argument, to jest wskazanie numeru wiersza w ramach zaznaczonej tabeli.
- Rodzaj wyszukiwania – jeśli flaga dokładnego dopasowania jest ustawiona na wartość TRUE, funkcja WYSZUKAJ.POZIOMO zwróci wartość tylko wtedy, gdy szukana wartość będzie dokładnie odpowiadała wartości w komórce wiersza. Jeśli flaga jest ustawiona na wartość FALSE lub jest pominięta, funkcja zwróci wartość, która jest najbliższą wartością szukanej.
WYSZUKAJ POZIOMO – ćwiczenia i przykłady
Wyszukiwanie statusu pracy dla wskazanego dnia
Ten przykład dotyczyć będzie sytuacji z rysunku nr 2. Mamy w tabeli listę pracowników (w kolejnych wierszach) oraz kolejne dni (kolumny). Dla każdego z nich notowany jest status pracy („Praca”, „L4”, itp.).
Rysunek 5. Funkcja WYSZUKAJ.POZIOMO w komórce C2.
Wyobraźmy sobie, że teraz musimy znaleźć dla wskazanego pracownika status pracy z podanego dnia. Włączamy do tego funkcję WYSZUKAJ.POZIOMOW, którą zapisujemy w komórce C2:
=WYSZUKAJ.PIONOWO(
Pierwszy argument, to będzie wartość, za pomocą której szukamy, czyli w tym przypadku data (w komórce C1):
=WYSZUKAJ.PIONOWO(C1
Drugi argument, to tabela, w której pierwszy wiersz może zawierać wartość przed chwilą przez nad podaną, czyli datę:
=WYSZUKAJ.PIONOWO(C1;E1:ZA11
Trzeci argument, to numer wiersza, w którym zapisany jest szukany przez nas pracowników. w Funkcji WYSZUKAJ.POZIOMO musimy podać, konkretny numer. Więc sprawdzam, że podany pracownik P59007 znajduje się (w ramach naszej tabeli) w wierszy 6-tym z kolei (w obliczeniach uwzględniamy też nagłówek tabeli). Jeśli chcesz, aby Excel automatycznie wyliczał odpowiedni wiersz, to najlepiej użyć funkcji PODAJ.POZYCJĘ i INDEKS:
=WYSZUKAJ.PIONOWO(C1;E1:ZA11;6
Ostatni argument to zazwyczaj 0 albo inaczej FAŁSZ”
=WYSZUKAJ.PIONOWO(C1;E1:ZA11;6;0)
Rysunek 6. Gotowa funkcja WYSZUKAJ.POZIOMO w komórce Excela.
Wybór jednej z kilku wartości w poziomie
To będzie przykład, zobrazowany wcześniej na rysunku nr 3. Mamy tutaj listę wykonanych nadgodzin i naszym zadaniem jest obliczyć całkowity koszt wynagrodzeń za te nadgodziny. Istnieją jednak różne typy nadgodziny (w tygodniu, w weekendy, i inne), i ten typ został określony za każdym razem w tabeli. Dla tych typów zostały przypisane określone stawki wynagrodzenia (czyli o ile jest ono wyższe od standardowego wynagrodzenia).
Rysunek 7. Lista pracowników z nadgodzinami i z informacją o rodzaju nadgodzin.
Rysunek 8. Różne stawki nadgodzin.
Aby obliczyć całkowite wynagrodzenie, należy przemnożyć przez siebie 3 elementy:
- Liczbę przepracowanych nadgodzin.
- Podstawową stawkę (podaną w komórce G5).
- Stawką nadgodzin, wyszukaną z tabelki obok, za pomocą WYSZUKAJ.POZIOMO.
Zajmijmy się więc kluczowym elementem, czyli funkcją WYSZUKAJ.POZIOMO, którą zapisujemy w komórce D2:
=WYSZUKAJ.PIONOWO(
Jej pierwszy argument, to będzie rodzaj nadgodzin, dla którego mamy znaleźć stawkę. Wskazujemy rodzaj z tego samego wiersza, czyli:
=WYSZUKAJ.PIONOWO(B2
Drugi argument, to tabela, gdzie stawki przypisany są do rodzajów nadgodzin – czyli nasza pomocnicza tabela po prawej stronie. Pamiętaj jednak, aby wstawić do jej adresów dolary, bo nasza formuła zaraz będzie przeciągana:
=WYSZUKAJ.PIONOWO(B2;$G$2:$J$3
Trzeci argument, to wskazanie numeru wiersza (liczonego w ramach naszej tabeli, a nie w ramach całego arkusza Excela), w którym znajdują się szukane przez nas stawki nadgodzin:
=WYSZUKAJ.PIONOWO(B2;$G$2:$J$3;2
Na końcu zapisujemy standardowo 0 albo FAŁSZ:
=WYSZUKAJ.PIONOWO(B2;$G$2:$J$3;2;0)
Teraz możesz formułę przeciągnąć do końca tabeli, czyli do komórki D11. W ten sposób, WYSZUKAJ.POZIOMO dla każdego z pracowników z osobna wyszuka odpowiedniej stawki nadgodzin.
Rysunek 9. Wyszukanie stawek nadgodzin.
Na koniec musimy jeszcze te stawki przemnożyć przez dwa pozostałe elementy, czyli przez podstawową stawkę wynagrodzenia oraz przez liczbę przepracowanych nadgodzin:
=WYSZUKAJ.PIONOWO(B2;$G$2:$J$3;2;0)*$G$5*C2
Przeciągnij formułę ponownie do samego końca tabeli 🙂
