Spis treści
Niedawno poświęciliśmy artykuł jednej z najbardziej przydatnych funkcji programu Excel o nazwie VPR i pokazał, jak można go wykorzystać do wyodrębnienia wymaganych informacji z bazy danych do komórki arkusza roboczego. Wspomnieliśmy również, że istnieją dwa przypadki użycia funkcji VPR i tylko jeden z nich zajmuje się zapytaniami do bazy danych. W tym artykule poznasz inny mniej znany sposób korzystania z funkcji VPR w programie Excel.
Jeśli jeszcze tego nie zrobiłeś, koniecznie przeczytaj ostatni artykuł o funkcji VPR, ponieważ wszystkie poniższe informacje zakładają, że znasz już zasady opisane w pierwszym artykule.
Podczas pracy z bazami danych funkcje VPR przekazywany jest unikalny identyfikator, który służy do identyfikacji informacji, które chcemy znaleźć (na przykład kod produktu lub numer identyfikacyjny klienta). Ten unikalny kod musi znajdować się w bazie danych, w przeciwnym razie VPR zgłosi błąd. W tym artykule przyjrzymy się temu sposobowi wykorzystania funkcji VPRgdy identyfikator w ogóle nie istnieje w bazie danych. Jakby funkcja VPR przełącza się w tryb przybliżony i wybiera jakie dane nam podać, gdy chcemy coś znaleźć. W pewnych okolicznościach właśnie to jest potrzebne.
Przykład z życia. Stawiamy zadanie
Zilustrujmy ten artykuł prawdziwym przykładem – obliczanie prowizji na podstawie szerokiego zakresu metryk sprzedaży. Zaczniemy od bardzo prostej opcji, a następnie będziemy ją stopniowo komplikować, aż jedynym racjonalnym rozwiązaniem problemu będzie użycie funkcji VPR. Wyjściowy scenariusz dla naszego fikcyjnego zadania jest następujący: jeśli sprzedawca zarobi rocznie więcej niż 30000 USD, to jego prowizja wynosi 30%. W przeciwnym razie prowizja wynosi tylko 20%. Ułóżmy to w formie tabeli:
Sprzedawca wprowadza swoje dane sprzedaży w komórce B1, a formuła w komórce B2 określa prawidłową stawkę prowizji, jakiej może oczekiwać sprzedawca. Z kolei uzyskana stawka jest wykorzystywana w komórce B3 do obliczenia całkowitej prowizji, jaką powinien otrzymać sprzedawca (po prostu mnożąc komórki B1 i B2).
Najciekawsza część tabeli znajduje się w komórce B2 – jest to wzór na określenie stawki prowizji. Ta formuła zawiera funkcję Excela o nazwie IF (JEŚLI). Dla tych czytelników, którzy nie są zaznajomieni z tą funkcją, wyjaśnię, jak to działa:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Stan jest argumentem funkcji, który przyjmuje wartość jednego lub drugiego PRAWDZIWY KOD (Prawda lub FAŁSZYWY (FAŁSZYWY). W powyższym przykładzie wyrażenie B1
Czy to prawda, że B1 jest mniejsze niż B5?
Możesz też powiedzieć inaczej:
Czy to prawda, że łączna kwota sprzedaży za rok jest mniejsza niż wartość progowa?
Jeśli odpowiemy na to pytanie TAK (PRAWDA), funkcja zwraca wartość, jeśli prawda (wartość PRAWDA). W naszym przypadku będzie to wartość komórki B6, czyli stawka prowizji, gdy całkowita sprzedaż jest poniżej progu. Jeśli odpowiemy na pytanie NIE (FAŁSZ), a następnie zwraca wartość, jeśli fałsz (wartość FAŁSZ). W naszym przypadku jest to wartość komórki B7, czyli stawka prowizji, gdy łączna sprzedaż jest powyżej progu.
Jak widać, jeśli weźmiemy całkowitą sprzedaż 20000 USD, otrzymamy 2% prowizji w komórce B20. Jeżeli wprowadzimy wartość 40000$, to stawka prowizji zmieni się o 30%:
Tak działa nasz stół.
Komplikujemy zadanie
Uczyńmy sprawy trochę trudniejszymi. Ustawmy kolejny próg: jeśli sprzedawca zarabia więcej niż 40000 USD, to stawka prowizji wzrasta do 40%:
Wszystko wydaje się proste i jasne, ale nasza formuła w komórce B2 staje się zauważalnie bardziej skomplikowana. Jeśli przyjrzysz się uważnie tej formule, zobaczysz, że trzeci argument funkcji IF (IF) zamienił się w kolejną pełnoprawną funkcję IF (JEŚLI). Ta konstrukcja nazywana jest zagnieżdżaniem funkcji w sobie. Excel szczęśliwie zezwala na te konstrukcje, a nawet działają, ale są znacznie trudniejsze do odczytania i zrozumienia.
Nie będziemy zagłębiać się w szczegóły techniczne – dlaczego i jak to działa, nie będziemy też zagłębiać się w niuanse pisania funkcji zagnieżdżonych. W końcu jest to artykuł poświęcony funkcji VPR, a nie kompletny przewodnik po programie Excel.
W każdym razie formuła staje się bardziej skomplikowana! Co jeśli wprowadzimy inną opcję stawki prowizji w wysokości 50% dla tych sprzedawców, którzy zarabiają więcej niż 50000 USD na sprzedaży. A jeśli ktoś sprzedał więcej niż 60000 $, czy zapłaci 60% prowizji?
Teraz formuła w komórce B2, nawet jeśli została napisana bez błędów, stała się całkowicie nieczytelna. Myślę, że mało kto chce używać w swoich projektach formuł z 4 poziomami zagnieżdżenia. Musi być prostszy sposób?!
I jest taki sposób! Funkcja nam pomoże VPR.
Aby rozwiązać problem, stosujemy funkcję WYSZUKAJ.PIONOWO
Zmieńmy nieco wygląd naszego stołu. Zachowamy wszystkie te same pola i dane, ale uporządkujemy je w nowy, bardziej zwarty sposób:
Poświęć chwilę i upewnij się, że nowy stół Tabela stawek zawiera te same dane, co w poprzedniej tabeli progów.
Główną ideą jest wykorzystanie funkcji VPR aby określić żądaną stawkę taryfową zgodnie z tabelą Tabela stawek w zależności od wielkości sprzedaży. Należy pamiętać, że sprzedający może sprzedawać towary za kwotę, która nie jest równa jednemu z pięciu progów w tabeli. Na przykład mógłby sprzedać za 34988 dolarów, ale nie ma takiej kwoty. Zobaczmy, jak funkcja VPR poradzi sobie w takiej sytuacji.
Wstawianie funkcji WYSZUKAJ.PIONOWO
Wybierz komórkę B2 (gdzie chcemy wstawić naszą formułę) i znajdź WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO) w bibliotece funkcji programu Excel: Wzory (wzory) > Biblioteka funkcji (Biblioteka funkcji) > Wyszukiwanie i odniesienie (Odwołania i tablice).
Pojawi się okno dialogowe Argumenty funkcji (Argumenty funkcji). Wartości argumentów wpisujemy po kolei, zaczynając od Wyszukaj_wartość (Wyszukaj_wartość). W tym przykładzie jest to łączna kwota sprzedaży z komórki B1. Umieść kursor w polu Wyszukaj_wartość (Wyszukaj_wartość) i wybierz komórkę B1.
Następnie musisz określić funkcje VPRgdzie szukać danych. W naszym przykładzie jest to stół Tabela stawek. Umieść kursor w polu Tablica_tabeli (Tabela) i wybierz całą tabelę Tabela stawekz wyjątkiem nagłówków.
Następnie musimy określić, z której kolumny wyodrębnić dane za pomocą naszej formuły. Interesuje nas stawka prowizji, która znajduje się w drugiej kolumnie tabeli. Dlatego dla argumentu numer_indeksu_kolumny (Numer_kolumny) wprowadź wartość 2.
I na koniec przedstawiamy ostatni argument – Zakres wyszukiwania (Przeglądanie_interwału).
Ważne: to użycie tego argumentu czyni różnicę między dwoma sposobami zastosowania funkcji VPR. Podczas pracy z bazami danych argument Zakres wyszukiwania (range_lookup) musi zawsze mieć wartość FAŁSZYWY (FAŁSZ), aby wyszukać dokładne dopasowanie. W naszym korzystaniu z funkcji VPR, musimy pozostawić to pole puste lub wpisać wartość PRAWDZIWY KOD (PRAWDA). Niezwykle ważne jest prawidłowe wybranie tej opcji.
Aby było jaśniej, przedstawimy PRAWDZIWY KOD (PRAWDA) w terenie Zakres wyszukiwania (Przeglądanie_interwału). Chociaż pozostawienie tego pola pustego nie będzie błędem, ponieważ PRAWDZIWY KOD jest jego wartością domyślną:
Wypełniliśmy wszystkie parametry. Teraz wciskamy OK, a Excel tworzy dla nas formułę z funkcją VPR.
Jeśli poeksperymentujemy z kilkoma różnymi wartościami całkowitej kwoty sprzedaży, upewnimy się, że formuła działa poprawnie.
Wnioski
Gdy funkcja VPR współpracuje z bazami danych, argument Zakres wyszukiwania (range_lookup) musi zaakceptować FAŁSZYWY (FAŁSZYWY). A wartość wpisana jako Wyszukaj_wartość (Lookup_value) musi istnieć w bazie danych. Innymi słowy, szuka dokładnego dopasowania.
W przykładzie, który omówiliśmy w tym artykule, nie ma potrzeby uzyskiwania dokładnego dopasowania. Dzieje się tak, gdy funkcja VPR musi przejść do trybu przybliżonego, aby zwrócić żądany wynik.
Na przykład: Chcemy określić, jaką stawkę zastosować do obliczenia prowizji dla sprzedawcy o wolumenie sprzedaży 34988 USD. Funkcjonować VPR zwraca nam wartość 30%, co jest absolutnie poprawne. Ale dlaczego formuła wybrała wiersz zawierający dokładnie 30%, a nie 20% lub 40%? Co oznacza wyszukiwanie przybliżone? Powiedzmy sobie jasno.
Kiedy argument Zakres wyszukiwania (interval_lookup) ma wartość PRAWDZIWY KOD (PRAWDA) lub pominięta, funkcja VPR iteruje przez pierwszą kolumnę i wybiera największą wartość, która nie przekracza wartości wyszukiwania.
Ważny punkt: Aby ten schemat działał, pierwsza kolumna tabeli musi być posortowana w porządku rosnącym.