Excel jako system CRM, cz. VI – wyszukiwanie w Excelu za pomocą funkcji WYSZUKAJ.PIONOWO oraz PODAJ.POZYCJĘ i INDEKS

Gdy pracujemy z danymi, często musimy sprawdzać połączenia pomiędzy różnymi tabelkami, czyli wyszukiwać powiązanych informacji. W tym artykule omówimy najważniejsze funkcje Excela związane z wyszukiwaniem informacji, czyli funkcje WYSZUKAJ.PIONOWO oraz INDEKS i PODAJ.POZYCJĘ.

WYSZUKAJ.PIONOWO

Zacznijmy od najbardziej standardowego przykładu sprzedaży, gdzie w jednej tabeli mamy informacje o dacie sprzedaży, produkcie, ilości itp., a w drugiej cenę produktu (cennik) (rys. 1).

Potrzebujemy dopisać cenę do każdej transakcji sprzedażowej (do każdej linijki). Funkcja WYSZUKAJ.PIONOWO idealnie się nadaje do tego celu, tylko musimy zrozumieć, jak ona dokładnie działa, czyli musimy poznać jej składnię:

WYSZUKAJ.PIONOWO (szukana_wartość; tabela_tablica; nr_kolumny; [przeszukiwany_zakres])

Zanim jednak omówimy dokładnie składnię, zastanówmy się, jak byśmy szukali ceny ręcznie. Po pierwsze, sprawdzalibyśmy nazwę produktu dla konkretnej transakcji sprzedaży, następnie przeszlibyśmy do tabeli cennika i tam odszukali nazwę produktu, który nas interesuje, żeby odnaleźć cenę mu przypisaną. Analogicznie działa funkcja WYSZUKAJ.PIONOWO. Potrzebuje do tego 4 argumentów, z których ostatni jest opcjonalny. Jakie wartości musi ona przyjmować, żeby udało nam się w tabeli sprzedażowej dopasować cenę do produktu?

Zacznijmy od pierwszego argumentu (szukana_wartość). Dla niektórych nazwa argumentu jest myląca, bo nie chodzi o cenę – gdybyśmy ją znali, to nie potrzebowalibyśmy żadnej funkcji. W argument szukana_wartość wpisujemy wartość (tekst lub liczbę), która łączy obie tabele (jest w obu tabelach). Chcemy znaleźć cenę produktu, a właśnie produkt znajduje się zarówno w tabeli sprzedażowej, jak i w tabeli cennika, czyli dla konkretnego wiersza naszą szukaną wartością będzie konkretny produkt (np. Tęcza – komórka C2).

Kolejny argument nazywa się tabela_tablica – jest to tabelka, w której szukamy interesującej nas informacji (ceny). W naszym przykładzie jest to mała tabela cennika ($G$4:$H$10 – nagłówki nigdy nie będą nam potrzebne przy wyszukiwaniu, a w jednej sytuacji na sto może się zdarzyć, że zaburzą nam szukanie, tym samym funkcja zwróci niepoprawny wynik, więc lepiej nauczyć się ograniczyć tabelę do samych danych, bez nagłówków). Ważne jest, że Excel w funkcji WYSZUKAJ.PIONOWO przeszukuje tylko pierwszą kolumnę tabeli podanej w drugim argumencie. Oznacza to, że jeśli nie znajdziesz szukanej wartości (w naszym przykładzie nazwy produktu) w pierwszej kolumnie argumentu tabela_tablica (tabela cennika), to zwróci błąd. Nie będzie przeszukiwał innych kolumn!

Na szczęście mamy dobrze przygotowane dane i możemy przejść do trzeciego argumentu – nr_kolumny. Funkcja WYSZUKAJ.PIONOWO znajdzie wiersz, w którym jest nasz produkt, ale jeszcze potrzebuje informacji, z której kolumny tego wiersza tabeli ma zwrócić wartość. W naszej sytuacji wydaje się oczywiste, że chcemy uzyskać cenę, ale nie zawsze tabela, w której będziemy szukać wartości będzie się składać tylko z dwóch kolumn, dlatego musimy wyraźnie podać numer kolumny, w której jest interesująca nas wartość (cena – 2).

Ostatni argument ([przeszukiwany_zakres]) jest argumentem opcjonalnym, czyli nie musimy go wpisywać – wtedy zostanie przyjęta dla niego domyślna wartość – PRAWDA – dopasowanie przybliżone. Ten argument jest najtrudniejszy do zrozumienia. W naszym przykładzie zakładamy, że wybierzemy dla niego dopasowanie dokładne (wpiszemy wartość logiczną FAŁSZ lub 0).

Główne założenie przy tym argumencie jest takie, że jeśli robimy dopasowanie przybliżone, musimy mieć dane posortowane po pierwszej kolumnie przeszukiwanej tabeli od najmniejszych wartości do największych (od A do Z) inaczej zwrócony wynik może być niepoprawny, a czasami nawet błędny. Zaletą tego sposobu wyszukiwania jest jego szybkość.

Przy dopasowaniu dokładnym funkcja WYSZUKAJ.PIONOWO zaczyna szukać wartości od pierwszego wiersza i schodzi w dół. Gdy znajdzie szukaną wartość zatrzymuje się i nie szuka dalej (w niższych wierszach). Stąd ważna informacja – funkcja WYSZUKAJ.PIONOWO znajduje tylko jedną wartość. Nawet jeśli w przeszukiwanej tabeli szukana wartość pojawia się więcej niż jeden raz.

Podsumowując, nasza funkcja WYSZUKAJ.PIONOWO do odnalezienia ceny produktu będzie wyglądać następująco (rys. 2):

=WYSZUKAJ.PIONOWO (C2;$G$4:$H$10;2;0)

Rozważmy kolejną sytuację dotyczącą sprzedaży – mamy listę sprzedaży wygenerowanej przez konkretnych pracowników, od każdej sprzedaży należy się premia w zależności od wielkości sprzedaży (dla uproszczenia jest to konkretna wartość liczbowa, rys. 3).

Jeśli sprzedawca wygenerował sprzedaż pomiędzy 1000 a 5000 zł, należy mu się 100 zł premii, jeśli pomiędzy 5000 a 10 000 zł to należy mu się 250 zł premii itd. zgodnie z tabelką (rys. 3). Ważne są tutaj znaki równości – nasze przedziały są domknięte od dołu do Excela i funkcji WYSZUKAJ.PIONOWO.