Pobierz plik zawierający przykładowe zadania z tego wpisu.
Gdy po raz pierwszy zobaczyłem w pomocy Excela opis funkcji WYSZUKAJ.PIONOWO, to się załamałem. Totalnie nic z niego nie zrozumiałem 😉 Dlatego też w tym wpisie omówię dokładnie tę bardzo przydatną (i często niedocenianą) funkcję.
Ponieważ owa funkcja może mieć kilka zastosowań, to ten wpis będzie nieco dłuższy.
Wyszukiwanie jednej z kilku wartości (alternatywa dla zagnieżdżonej funkcji JEŻELI)
Zadanie znajduje się w arkuszu JEŻELI i WYSZ.PIONOWO
Załóżmy, że oferujesz usługi telewizji kablowej. Na powyższym rysunku znajduje się fragment listy klientów. Widzimy, że oferujesz trzy różne pakiety tj. złoty, srebrny i brązowy różniące się liczbą dostarczanych kanałów. Każdy klient ma stałą cenę w zależności od wybranego pakietu (tj. każdy kto ma pakiet złoty płaci 100 zł, srebrny 50 zł i brązowy 25 zł, nie faworyzujemy nikogo oferując mu jakąś lepszą cenę, gdyż każdego klienta traktujemy tak samo).
Ręczne wypełnianie kolumny D (cena) cenami pakietów jest bezsensowne. Po pierwsze przy tysiącu klientów sporo się namęczysz, po drugie istnieje ryzyko pomyłki a po trzecie, gdy zechcesz zmienić ceny pakietów to będzie trochę zabawy z aktualizacją cen w kolumnie z klientami.
Jeśli czujesz się na siłach, to możesz napisać formułę korzystając z zagnieżdżonej funkcji JEŻELI:
=JEŻELI(C2="złoty";100; JEŻELI(C2="srebrny";50; JEŻELI(C2="brązowy";25;"nieznany pakiet")))Widać, że wyszedł nam niezły „potworek”, a w ofercie mamy tylko 3 różne pakiety! Wyobraź sobie, jakby musiała wyglądać ta formuła gdybyś w ofercie miał 30 pakietów (wtedy dopiero widać piękno funkcji WYSZUKAJ.PIONOWO).
Aby skorzystać z funkcji WYSZUKAJ.PIONOWO musimy najpierw gdzieś na boku utworzyć prostą tabelkę pomocniczą, która będzie zawierała minimum dwie kolumny: nazwa pakietu i jego cena. Ja dodatkowo dodałem kolumnę liczba kanałów (o czym wspomnę nieco później). W moim przykładzie ta tabelka pomocnicza znajduje się w komórkach F1:H4 w tym samym arkuszu, ale nic nie stoi na przeszkodzie, aby takie pomocnicze tabelki znajdowały się w osobnych arkuszach (wręcz to jest wskazane, gdyby się okazało, że nasza tabelka z danymi klientów miałaby wiele kolumn).
Aby zrozumieć działanie tej funkcji umieść kursor w komórce D2 i wpisz następującą formułę:
=WYSZUKAJ.PIONOWO(C2; $F$2:$H$4; 2; FAŁSZ)Funkcja ta przyjmuje aż cztery argumenty (z czego trzy pierwsze są obowiązkowe)
- Pierwszy argument to jest poszukiwana wartość w naszej tabelce pomocniczej. My będziemy w tej tabelce szukać wiersza w którym znajduje się słowo srebrny, gdyż taka wartość znajduje się w komórce C2 (nasza klientka Jadwiga wykupiła u nas pakiet srebrny)
- Drugi argument jest to ta tabelka pomocnicza, w której będziemy szukać cenę usługi dla pakietu srebrny. Tutaj dwie uwagi: jako zakres wpisałem komórki od F2 do H4; należy zapamiętać, że w funkcji WYSZUKAJ.PIONOWO nie bierzemy pod uwagę wiersza tytułowego owej tabelki pomocniczej. Druga uwaga: ponieważ owa formuła będzie powielana w innych wierszach, więc musiałem użyć adresowania bezwzględnego. Jeśli nie wiesz, „co robią te dolary”, zapoznaj się z wpisem adresowanie względne i bezwzględne.
- Trzeci argument oznacza z którego indeksu kolumny tabelki pomocniczej pobieramy dane. Widzimy, że cena abonamentu znajduje się w drugiej kolumnie tabelki pomocniczej. Gdybym zamiast 2 wpisał 3 to nasza formuła zwróci nie cenę pakietu (50) a informację, ile kanałów posiada owy pakiet srebrny (90)
- Czwarty argument to tryb wyszukiwania. Ten argument jest opcjonalny. Oznacza on rodzaj wyszukiwania (PRAWDA – wyszukiwanie przybliżone; FAŁSZ – wyszukiwanie dokładne). Ja tutaj użyłem argumentu FAŁSZ, gdyż w tabelce pomocniczej w pierwszym wierszu szukam dokładnie słowa „srebrny” a nie czegoś podobnego. O co chodzi z wyszukiwaniem przybliżonym dowiesz się nieco później. Jeśli pominiemy ten argument to Excel zakłada, że wybraliśmy wyszukiwanie przybliżone i wtedy w naszej tabelce pomocniczej w kolumnie pakiet musielibyśmy nazwy pakietów posortować alfabetycznie (brązowy, srebrny, złoty)
Uwaga: funkcja WYSZUKAJ.PIONOWO zawsze wyszukuje nazwę pakietu względem pierwszej kolumny w naszej tabelce pomocniczej. Gdyby w tej tabelce w pierwszej kolumnie była cena pakietu a w drugiej kolumnie nazwa pakietu, to ta funkcja nie zadziała!
Błąd #N/D
Zadanie znajduje się w arkuszu WYSZ.PIONOWO – brak
Jeśli w tabelce z naszymi klientami w kolumnie Pakiet wpiszemy jakąś wartość, która nie znajduje się w tabelce pomocniczej to funkcja WYSZUKAJ.PIONOWO zwróci nam błąd #N/D. W naszym przypadku oferujemy klientom pakiety: złoty, srebrny i brązowy. Jeśli przy jakimś nazwisku wpiszemy Platynowy to funkcja zwróci nam błąd. Taki błąd nie wygląda zbyt ładnie, więc naszą funkcję WYSZUKAJ.PIONOWO należy „opakować” w funkcję JEŻELI.BŁAD. Funkcja JEŻELI.BŁAD w przypadku gdyby funkcja WYSZUKAJ.PIONOWO zwróciła błąd #N/D pozwoli nam wyświetlić własny nieco bardziej przyjazny komunikat błędu.
Oto składnia tej funkcji:
JEŻELI.BŁĄD(funkcja którą chcemy opakować; komunikat jaki ma się pojawić gdy wystąpi błąd)Pierwszy argument funkcji to po prostu nasza funkcja WYSZUKAJ.PIONOWO a drugi argument tej funkcji to komunikat wpisz poprawny pakiet. Oto nasza funkcja WYSZUKAJ.PIONOWO opakowana w funkcję JEŻELI.BŁAD
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(C7; $F$2:$G$4; 2; FAŁSZ);
"wpisz poprawny pakiet")Oczywiście zamiast komunikatu wpisz poprawny pakiet funkcja ta mogłaby zwrócić np. cenę. Załóżmy, że każdy klient, który ma inny pakiet niż złoty, srebrny lub brązowy będzie płacił za telewizję 500 zł. W tym celu możemy wpisać taką oto formułę:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(C7; $F$2:$G$4; 2; FAŁSZ); 500)Wyszukiwanie przybliżone
W tym przykładzie policzymy wskaźnik masy ciała BMI (ang. Body Mass Index). W tym celu należy podzielić masę ciała (podawaną w kg) przez wzrost (podawany w metrach) do kwadratu.
Podobne ćwiczenie opisałem we wpisie Nadawanie nazw komórkom, ale tam skupiłem się na innym zagadnieniu. Po podstawieniu naszej masy i wzrostu do powyższego wzoru otrzymamy wynik. Jeśli jest on z przedziału od 18,5 do 25 to oznacza, że nasza masa jest optymalna. Jeśli mniej niż 18,5 to znaczy, że mamy niedowagę, a jeśli wynik jest wyższy niż 25, to oznacza że należy zrezygnować z kolacji. Oto dokładna tabelka, która informuje nas co oznacza dany wynik:
Źródło: wikipedia
I w tym ćwiczeniu będziemy chcieli, aby w zależności od otrzymanego wyniku Excel podał komentarz. Tak może wyglądać przykładowy arkusz liczący nasze BMI.
W komórce B3 (licząca BMI) należy wpisać taką oto prostą formułę:
=B1/B2^2
Natomiast w komórce B4 użyjemy funkcji WYSZUKAJ.PIONOWO:
=WYSZUKAJ.PIONOWO(B3; E2:F9; 2)Zwróć uwagę, że nie wpisałem ostatniego argumentu (FAŁSZ gdyby wyszukiwanie miało by być dokładne lub PRAWDA gdy wyszukiwanie jest przybliżone). Ponieważ nie było podanego ostatniego argumentu to efekt działania jest taki sam, gdybym wpisał PRAWDA (wyszukiwanie przybliżone). Dlaczego wyszukiwanie jest przybliżone? Otóż po wpisaniu przykładowych danych wyszedł wynik 24,69136. Takiej wartości nie ma w naszej tabelce pomocniczej, ale najbliższa tej liczbie jest wartość 18,5 (gdyż 24,69136 jest liczbą większą niż 18,5 ale mniejszą niż 25). Wniosek jest taki, że dla powyższych danych waga jest optymalna. Jeśli na podstawie wpisanych danych wyszedłby wynik 18,4999 to pojawiłby się komunikat niedowaga, jeśli wynik by wyszedł idealnie 18,5 to pojawi się komunikat optimum, a jeśli wynik by wyszedł 24,999 to nadal mamy optymalną wagę (dopiero od idealnie 25 jesteśmy już w progu nadwaga).
Uwaga 1: dane w tabelce pomocniczej muszą być posortowane rosnąco względem pierwszej kolumny, inaczej funkcja nie będzie działać prawidłowo.
Uwaga 2: zwróć uwagę, że w funkcji WYSZUKAJ.PIONOWO w drugim argumencie funkcji (czyli zakres danych tabelki pomocniczej) nie wpisałem „dolarów” (adresowanie bezwzględne). Jest to celowe działanie z mojej strony, gdyż wiem, że tej formuły nie będę kopiował do innych komórek. Natomiast „dodanie dolarów” w niczym nam by nie zaszkodziło.
Uwaga 3: użycie zagnieżdżonej funkcji JEŻELI zamiast WYSZUKAJ.PIONOWO byłoby męczarnią:
=JEŻELI(B3<16;"wygłodzenie"; JEŻELI(B3<17;"wychudzenie"; JEŻELI(B3<18,5;"niedowaga"; JEŻELI(B3<25;"optimum" ;JEŻELI(B3<30;"nadwaga"; JEŻELI(B3<35;"pierwszy stopień otyłości"; JEŻELI(B3<40;"drugi stopień otyłości";"trzeci stopień otyłości")))))))(A teraz życzę powodzenia w edycji tej formuły gdybyś chciał znaleźć w niej ewentualny błąd, lub np. dodać dodatkowy przedział).
WYSZUKAJ.POZIOMO
Wróćmy do przykładu z pierwszego ćwiczenia. Tym razem jednak nasza tabelka pomocnicza nie będzie w układzie pionowym a poziomym (czyli będziemy szukać nazwę pakietu telewizji kablowej „idąc w prawo” a nie „w dół”). Osobiście wolę, gdy tabelka pomocnicza jest w układzie pionowym a nie poziomym (jak „obrócić tabelę pomocniczą o 90 stopni” opisałem w artykule: Transpozycja czyli zamieniamy wiersze z kolumnami. No ale gdy wykonasz ćwiczenie z transpozycją, to wtedy nie będzie pretekstu do przećwiczenia funkcji WYSZUKAJ.POZIOMO 😉
Oto przykładowy arkusz:
Aby dla danego klienta sprawdzić, ile wynosi koszt jego abonamentu należy w komórce D2 wpisać następującą formułę:
=WYSZUKAJ.POZIOMO(C2; $B$12:$D$14; 2; FAŁSZ)Zwróć uwagę, że argumenty funkcji WYSZUKAJ.POZIOMO są takie, jak dla funkcji WYSZUKAJ.PIONOWO, więc nie muszę się tutaj jakoś specjalnie rozpisywać.
