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

Excel w małych i średnich firmach jest czasem wykorzystywany jako system CRM. Nie dziwi to, ponieważ pozwala łatwo zapisać informacje o klientach, transakcjach sprzedaży itp., a także ułatwia analizę tych danych, np. pomaga w obliczeniu, ile przychodu uzyskała firma w poszczególnych miesiącach. „Excel jako system CRM” to cykl artykułów, w których pokazujemy, jak sprawnie poruszać się w Excelu, żeby stał się pomocą w codziennej pracy.

AS_17_55.jpg

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.

Wykorzystałeś swój limit bezpłatnych treści

Pozostałe 56% artykułu dostępne jest dla zalogowanych użytkowników portalu. Zaloguj się, wybierz plan abonamentowy albo kup dostęp do artykułu/dokumentu.

Kilka wariantów prenumeraty Pokaż opcje
Dwutygodniowy dostęp bez zobowiązań Wybieram

Abonament już od 83 zł miesięcznie

Dwutygodniowy dostęp bez zobowiązań

Pełen dostęp do wszystkich treści portalu
to koszt 83 zł miesięcznie
przy jednorazowej płatności za rok

WYBIERAM

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM

Pełen dostęp do wszystkich treści portalu
to koszt 83 zł miesięcznie
przy jednorazowej płatności za rok

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM

Polityka cookies

Dalsze aktywne korzystanie z Serwisu (przeglądanie treści, zamknięcie komunikatu, kliknięcie w odnośniki na stronie) bez zmian ustawień prywatności, wyrażasz zgodę na przetwarzanie danych osobowych przez EXPLANATOR oraz partnerów w celu realizacji usług, zgodnie z Polityką prywatności. Możesz określić warunki przechowywania lub dostępu do plików cookies w Twojej przeglądarce.

Usługa Cel użycia Włączone
Pliki cookies niezbędne do funkcjonowania strony Nie możesz wyłączyć tych plików cookies, ponieważ są one niezbędne by strona działała prawidłowo. W ramach tych plików cookies zapisywane są również zdefiniowane przez Ciebie ustawienia cookies. TAK
Pliki cookies analityczne Pliki cookies umożliwiające zbieranie informacji o sposobie korzystania przez użytkownika ze strony internetowej w celu optymalizacji jej funkcjonowania, oraz dostosowania do oczekiwań użytkownika. Informacje zebrane przez te pliki nie identyfikują żadnego konkretnego użytkownika.
Pliki cookies marketingowe Pliki cookies umożliwiające wyświetlanie użytkownikowi treści marketingowych dostosowanych do jego preferencji, oraz kierowanie do niego powiadomień o ofertach marketingowych odpowiadających jego zainteresowaniom, obejmujących informacje dotyczące produktów i usług administratora strony i podmiotów trzecich. Jeśli zdecydujesz się usunąć lub wyłączyć te pliki cookie, reklamy nadal będą wyświetlane, ale mogą one nie być odpowiednie dla Ciebie.