Funkcja Wyszukaj.Pionowo
Funkcja Wyszukaj.pionowo w Excelu należy do kategorii funkcji Wyszukiwania i adresu. Funkcja przyda się wszędzie tam, gdzie skończą się możliwości zagnieżdżania funkcji Jeżeli lub gdy samo zagnieżdżanie będzie zbyt pracochłonne. Wyszukaj.pionowo sprawdza się idealnie, gdy wartości jaka występuje w tabeli musimy przypisać inną wartość, bądź komunikat. Jej zastosowanie można wyobrazić sobie na podstawie przykładu wyboru oceny na podstawie okreslonej liczby punktów zdobytych w teście.
Aby zrozumieć różnicę pomiędzy zagnieżdżaniem funkcji Jeżeli a specyfiką funkcji Wyszukaj.Pionowo nalepiej zacząć lekturę od tutoriali omawiających temat funkcji Jeżeli i jej zagnieżdżania.
O co tu chodzi??
Niestety w zrozumieniu sposobu działania tej funkcji niewiele pomoże jej opis, jaki można znaleźć w pomocy programu Excel. Aby się o tym przekonać wystarczy nacisnąć klawisz F1 i odszukać instrukcję do Wyszukaj.Pionowo.
Trzeba przyznać, że dla przeciętnego użytkownika większość tego opisu będzie nieprzydatna, co gorzej może zniechęcić do skorzystania z dobrodziejstw funkcji Wyszukaj.Pionowo.
Sposób numer 1
Sposób zastosowania tej funkcji postanowiłam opisać na podstawie tych samych przykładów, jakie użyłam w tutorialu o zagnieżdżaniu funkcji Jeżeli. Czyli w pierwszym przykładzie wybrana marka telefonu ma przyznany określony rabat. Pozostałe zaś produkty mają rabat zerowy.
Pracując z funkcją Wyszukaj.Pionowo oprócz głównej tabeli, w której znajdują się dane, potrzebna jest tabela pomocnicza zbudowana z przynajmniej dwóch kolumn. Muszą znajdować się w niej wszystkie sprawdzane wartości, teksty, przedziały liczbowe (kolumna 1) oraz wartości wynikowe, jakie są przypisane do nich (kolumna 2). Wartości te będą ostatecznie wyświetlane w głównej tabeli jako wynik działania funkcji.
Na powyższym zrzucie jest widoczna tabela pomocnicza, jaka bedzie potrzebna w tym przypadku. Kolumna 1 od lewej to wartości, jakie ma sprawdzić funkcja. Kolumna 2 od lewej zawiera rabaty, które są przyporządkowane do konkretnych marek telefonów znajdujących się w kolumnie nr 1.
Przygotowując taką tabelkę pomocniczą nie musimy się martwić wielkością liter. Dla funkcji nie ma to znaczenia. Warto natomiast zauważyć, że tabela nie posiada wiersza nagłówkowego, wewnątrz niej nie ma też pustych wierszy, czy kolumn. Tabelka ta musi zawierać niestety wszystkie możliwe do sprawdzenia wartości, jakie występują w głównej tabeli. Czyli jeśli zamiast 5 nazw firm produkujących telefony byłoby ich w tabeli głównej 25 to wszystkie muszą wystąpić też w tabeli pomocniczej. W przypadku pominięcia jakiejś opcji funkcja wyświetli komunikat błędu #N/D! zamiast wyniku.
Co dalej?
Tabelę pomocniczą już mamy przygotowaną, więc pozostaje "tylko" wywołanie nazwy funkcji Wyszukaj.Pionowo w tym miejscu arkusza, w którym chcemy otrzymać wyniki. W tym przykładzie będzie to komórka B2.
Po wywołaniu funkcji konieczne jest jej bezbłędne wypełnienie. Jak ono wygląda w tym przypadku można sprawdzić na poniższym zrzucie:
Czarna magia, prawda?
Spójrzmy zatem na poniższy zrzut z opisami.
Szukana wartość: to pole z tajemniczych powodów zawsze jest najbardziej problematyczne i sprawia najwięcej problemów. A tymczasem wystarczy podać tu pierwszy adres komórki zakresu, jaki ma sprawdzić funkcja w głównej tabeli. W tym przypadku jest to adres A2. Pozostałych adresów komórek w tym miejscu nie podajemy, ponieważ jeśli funkcja zadziała to trzeba ją po prostu skopiować do pozostałych komórek tego zakresu.
Tabela_tablica czyli bardzo dokładny adres całej tabelki pomocniczej. Jak widać w adresach komórek tego zakresu są jeszcze znaki dolara $, które są niezbędne. Dzięki nim podczas kopiowania funkcji w tabeli głównej nie zmienią się adresy komórek zakresu tabeli pomocniczej, co normalnie by się zdarzyło, gdyby tych znaków $ zabrakło w tym miejscu. Trzeba zauważyć, że zablokowany jest zarówno numer wiersza, jak i numer kolumny ($D$7) w obu adresach.
Nr indeksu kolumny: zawsze podajemy numer kolumny tabelki pomocniczej, z której funkcja będzie pobierała wartości. W tym przypadku jest to kolumna numer 2 (patrząc od lewej strony na tabelę).
Przeszukiwany zakres: Fałsz wpisujemy zawsze, gdy funkcja musi odszukać dokładny odpowiednik, czyli np. wyraz jak w tym przypadku. Jeśli funkcja ma tylko określić przedział liczb, w jakim znajduje się wartość trzeba wpisać Prawda lub pozostawić to okienko puste.
Działa - nie działa?
Po wypełnieniu okna funkcji klikamy OK i dostajemy pierwszy wynik funkcji w komórce B2. Teraz wystarczy przekopiować funkcję do pozostałych komórek w kolumnie B, aby Wyszukaj.Pionowo bezbłędnie zadziałało. No prawie...
Co się stało w ostatniej komórce tabeli? Komunikat #N/D! świadczy o błędzie, który w tym przypadku był zamierzony. Nazwy Panasonic nie ma w tabelce pomocniczej, skąd funkcja mogłaby pobrać odpowiednią wartość. W rezultacie funkcja nie wie co zrobić i wyświetla komunikat o błędzie. Ot taka ilustracja tego, o czym pisałam nieco wyżej.
Sposób numer 2
Drugi przykład na zastosowanie możliwości tej funkcji, będzie opierał się na tabeli z pakietami dla użytkowników telefonów (przykład ten też pochodzi z tutoriala o zagnieżdżaniu Jeżeli, dzięki czemu można porównać wyniki otrzymane na dwa różne sposoby).
Dla przypomnienia - klientowi trzeba zaproponować korzystny pakiet w oparciu o średnią ilość wykorzystanych minut w ciągu ostatnich 3 miesięcy. Pakiety są przyporządkowane do odpowiednich zakresów minut.
Niestety tabelka pomocnicza, jaka jest widoczna na powyższym zrzucie jest całkowicie bezużyteczna dla funkcji Wyszukaj.Poziomo.
Aby funkcja była w stanie "przeczytać" prawidłowo dane w tabelce, trzeba ją zmodyfikować tak, aby wyglądała w następujący sposób:
Kolumna numer 2 (z opisami pakietów) pozostała bez zmian, natomiast w kolumnie nr 1 (patrząc od lewej) zmieniony został sposób zapisania zakresów liczbowych.
W przypadku tabel z zakresami liczb należy zapis zakresu uprościć i podać wyłącznie najmniejszą (dolną) wartość danego zakresu. Dodatkowo liczby te muszą występować w kolejności rosnącej.
Po przygotowaniu tabeli pomocniczej tak, jak w poprzednim przykładzie ustawiamy komórę aktywną w tym miejscu arkusza, w którym chcemy mieć wyniki i wywołujemy funkcję Wyszukaj.Pionowo, następnie wpisujemy kolejno parametry.
Szukana wartość to tak poprzednio adres tylko pierwszej komórki zakresu, jaki ma sprawdzić funkcja.
Tabela_tablica - zablokowany znakami dolara $ dokładny adres tabeli pomocniczej.
Nr indeksu kolumny: 2 - dane jakie ma pobrać funkcja znajdują się w drugiej kolumnie od lewej strony.
Przeszukiwany zakres: w tym przykładzie funkcja ma znalezioną liczbę wpasować w odpowiedni zakres w kolumnie nr 1 w tabelce pomocniczej. Nie jest nam tu potrzebny dokładny odpowiednik (jak było w poprzednim pierwszym przykładzie). Wpisujemy więc w to pole Prawda lub pozostawiamy puste.
Po przekopiowaniu funcji do pozostałych komórek funkcja bezbłędnie określa przedział, w jakim znajdują się liczby ze sprawdzanej kolumny Średnia ilość minut i wyświetla adekwatne nazwy pakietów.
Trudne?
Na początku pewnie tak, ale warto spróbować możliwości tej funkcji, bo jest ona wygodniejsza niż kilkukrotnie zagnieżdżana Jeżeli, nie ma ograniczenia ilości możliwych opcji. Wbrew pozorom jest też mniejsze ryzyko zrobienia błędu logicznego jak w przypadku funkcji Jeżeli zwłaszcza kilkukrotnie zagnieżdżonej.