WYSZUKAJ.PIONOWO – jak znajdować dane sprzedażowe w tabeli?

Pierwszym zadaniem, które rozwiążemy przy użyciu funkcji WYSZUKAJ.PIONOWO jest odnalezienia ceny dla konkretnego produktu (arkusz w pliku do pobrania). Zadanie to wyjaśnimy na przykładowych danych z rysunku nr 1.

Przedstawmy nasze zadanie bardziej obrazowo, krok po kroku np. dla produktu Mysz Mini. Musimy odnaleźć produkt z Tabeli sprzedażowej (Mysz Mini) w tabeli Cennik (punkt 1 i 2 na rysunku nr 2), z tej tabeli pobrać cenę przypisaną naszemu produktowi (Mysz Mini – 10 zł, punkt nr 3 na rysunku nr 2) i zwrócić ją w kolumnie Cena w Tabeli sprzedażowej (punkt nr 4 na rysunku nr 2).

Wyjaśnijmy nasze zadanie krok po kroku. W pierwszej tabeli mamy wypisanych sprzedawców i kwoty sprzedaży jakie osiągnęli. W drugiej tabeli mamy podaną wysokość premii w zależności od wysokości sprzedaży (Progi) - np. kiedy konkretny Sprzedawca osiągnie dany próg to należy mu się konkretna premia, przypisana do tego progu w drugiej tabeli.

Zajmijmy się pierwszym zadaniem. Omówimy je na podstawie danych zapisanych w arkuszu Dokładne w pliku do pobrania. Formuła końcowa będzie wyglądać następująco:

=WYSZUKAJ.PIONOWO(B3;$F$3:$G$14;2;0)

Pierwszym argumentem funkcji jest Szukana_wartość, czyli w naszym przykładzie będzie to komórka B3 zawierająca nazwę szukanego produktu. Komórki tej nie blokujemy bezwzględnie, bo będziemy naszą formułę kopiować (przeciągać) na wiersze poniżej i chcemy aby argument Szukana_wartość zmieniał się na odpowiednią dla każdego wiersza (np. w szóstym wierszu formuła odwoła się do komórki B6).

Drugi argument funkcji to Tabela_tablica, czyli zakres (tabela), w którym będziemy szukać wartości. Zakres ten możemy zaznaczyć ręcznie (dla Excela nie ma znaczenia od której strony zaczynamy zaznaczanie) lub użyć skrótów klawiszowych takich jak Ctrl+Shift+ Strzałka w dół lub Shift+ strzałka w prawo. Zaznaczony zakres blokuje bezwzględnie przy użyciu klawisza F4, ponieważ chcemy, aby ten zakres nie ulegał zmianie.

Trzeci argument funkcji to Nr_indeksu_kolumny. Aby dobrze zrozumieć jaki numer mamy podać musimy zrozumieć działanie funkcji. Funkcja WYSZUKAJ.PIONOWO będzie szukała produktu tylko w pierwszej kolumnie, a kiedy go znajdzie, musi znaleźć przypisaną do niego cenę. Musimy pomóc w tym funkcji WYSZUKAJ.PIONOWO i podać jej numer kolumny, z której ma pobrać szukaną wartość. W naszym przykładzie mamy tylko dwie kolumny, ale często tych kolumn z danymi jest dużo więcej. Nr_indeksu_kolumny dla naszego przykładu wynosi 2 ponieważ szukana wartość znajduje się w kolumnie Cena (jest to druga kolumna z rzędu w przeszukiwanym zakresie). Numerowanie kolumn zostało przedstawione na rysunku nr 4.

Czwartym i zarazem ostatnim argumentem funkcji WYSZUKAJ.PIONOWO jest Przeszukiwany_zakres, który zakłada dwa rodzaje dopasowania danych: wartość logiczna PRAWDA dla dopasowania przybliżonego lub liczba 1, która w Excelu jest w wielu sytuacjach tożsama z wartością logiczną PRAWDA) oraz wartość logiczna FAŁSZ dla dopasowania dokładnego (lub liczba 0, która w Excelu jest w wielu sytuacjach tożsama z wartością logiczną FAŁSZ).

W naszym przykładzie chcemy użyć dopasowania dokładnego, ponieważ nasze dane nie zostały posortowane alfabetycznie. Dopasowanie przybliżone wymaga takiego posortowania danych, aby działało poprawnie, ale o tym więcej przy omawianiu drugiego zadania. Tak przygotowaną formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter. Otrzymamy wynik dla wiersza, w którym wpisaliśmy formułę. Musimy ją skopiować na wiersze poniżej i otrzymamy wyniki przedstawione na rysunku nr 5.

Podsumowując funkcja WYSZUKAJ.PIONOWO z dopasowaniem dokładnym przeszukuje określony zakres od góry do dołu, a kiedy trafi na szukaną nazwę, zwraca nam wartość jej przypisaną z kolumny, którą podaliśmy jako trzeci argument funkcji.

Drugie zadanie będzie polegało na wykorzystaniu funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym. Zagadnienie to omówimy na podstawie danych zapisanych w arkuszu w pliku do pobrania.

Na pierwszy rzut oka widać, że wartości sprzedaży w pierwszej tabeli nie są dokładnie równe wartości progów, na podstawie których przyznawane są premie. W dopasowaniu przybliżonym Excel będzie się starał wyszukać wartości jak „najbardziej zbliżonej”. Obrazowo będzie zakres danych dzielił na połówki, żeby jak najszybciej odnaleźć szukany „przedział”.

Omówmy sytuację dla pierwszego sprzedawcy (Magda). Excel podzieli zakres danych na pół, czyli sprawdzi komórkę F5. Jeśli sprzedaż będzie większa od tej wartości, zejdzie w dół i znowu podzieli zakres danych na pół, czyli sprawdzi komórkę F7 itd. Analogicznie dla sytuacji, kiedy wartość z komórki F5 będzie większa od naszej wartości sprzedaży to Excel zacznie w ten sam sposób sprawdzać wartości powyżej połowy (rys. nr 6).

Najważniejsze przy użyciu dopasowania przybliżonego jest posortowanie danych od najmniejszej do największej. Przy budowaniu tabeli z progami bardzo ważne jest aby podać minimalną wartość jaka może nam się przytrafić, w naszym przykładzie jest to liczba 0, natomiast pierwsza premia należy się dopiero po osiągnięciu sprzedaży w wysokości 50 000 zł. Natomiast w sytuacji kiedy sprzedawca osiągnie sprzedaż powyżej 500 000 zł to otrzyma maksymalną premię, nie ważne ile przekroczy ten próg – premia będzie wynosiła 4000 zł.

Zapis końcowy funkcji będzie wyglądał następująco:

=WYSZUKAJ.PIONOWO(C2;$F$2:$G$8;2;1)

Pierwszym argumentem funkcji jest Szukana_wartość, czyli w naszym przykładzie jest to komórka C2 – wartość z kolumny Sprzedaż. Tak jak w poprzednim przykładzie nie blokujemy odwołania bezwzględnie, bo będziemy kopiować formułę na wiersze poniżej i chcemy aby szukana wartość również się przesuwała.

Drugi argument funkcji to Tabela_tablica, czyli zakres, który będziemy przeszukiwać. Zakres ten blokujemy bezwzględnie za pomocą klawisza F4, ponieważ nie chcemy aby uległ zmianie. [Ważne!] Excel będzie szukał wartości tylko w pierwszej kolumnie.

Kolejny argument funkcji to Nr_indeksu_kolumny, czyli kolejny numer kolumny w przeszukiwanej tabeli/zakresie, z której chcemy wyciągnąć wynik.

Czwarty argument funkcji to Przeszukiwany_zakres, czyli rodzaj dopasowania danych. W tym przykładzie wybieramy dopasowanie przybliżone, a więc wpisujemy liczbę 1 zamiast wartości logicznej PRAWDA, bo jest krótsza w zapisie, a w tym przykładzie daje taki sam wynik. Jak wspominaliśmy wyżej, nie mogliśmy wybrać dopasowania dokładnego, bo nie ma tutaj dokładnie takich wartości sprzedaży (takich liczb), jakie osiągnęli sprzedawcy, są natomiast zakresy od..do. Funkcja WYSZUKAJ.PIONOWO patrzy na kolejny próg wtedy, kiedy szukana wartość będzie większa lub równa jemu.

Zatwierdzamy formułę skrótem klawiszowym Ctrl+Enter i kopiujemy na wiersze poniżej. Otrzymamy wyniki przedstawione na rysunku nr 8.

Widać od razu, że formuła działa prawidłowo, np. dla sprzedawców których sprzedaż nie przekroczyła wartości 50 000 zł premia wynosi 0 zł, a przy wartościach powyżej maksymalnego progu mamy premię maksymalną 4000 zł.

Omówiliśmy działanie funkcji WYSZUKAJ.PIONOWO w zależności od rodzaju dopasowania wyszukiwania. Dopasowanie dokładne wykorzystuje się kiedy w przeszukiwanym zakresie występują dokładnie te dane których szukamy. Natomiast dopasowanie przybliżone używa się w sytuacji, kiedy mamy progi, przedziały wartości, w których mieści się nasza szukana wartość.

Polecamy także:

• Funkcje tekstowe – jak wykonywać operacje na wyrazach?
• 3 najciekawsze sposoby na wyznaczenie wartości maksymalnej