1Aug
W ostatnim artykule wprowadziliśmy funkcję programu Excel o nazwie VLOOKUP i wyjaśniono, w jaki sposób można jej użyć do pobierania informacji z bazy danych do komórki w lokalnym arkuszu roboczym. W tym artykule wspomnieliśmy, że były dwa zastosowania dla VLOOKUP, a tylko jeden z nich zajmował się wyszukiwaniem baz danych. W tym artykule, drugim i ostatnim z serii VLOOKUP, badamy to inne, mniej znane użycie funkcji VLOOKUP.
Jeśli jeszcze tego nie zrobiłeś, przeczytaj pierwszy artykuł VLOOKUP - ten artykuł zakłada, że wiele pojęć wyjaśnionych w tym artykule jest już znanych czytelnikowi.
Podczas pracy z bazami danych funkcja WYSZUKAJ.PIONOWO przekazuje "unikalny identyfikator" służący do identyfikowania rekordu danych, który chcemy znaleźć w bazie danych( np. Kod produktu lub identyfikator klienta).Ten unikalny identyfikator musi istnieć w bazie danych , w przeciwnym razie VLOOKUP zwróci błąd. W tym artykule przyjrzymy się sposobowi korzystania z VLOOKUP, w którym identyfikator nie musi w ogóle istnieć w bazie danych. To prawie tak, jakby VLOOKUP mógł przyjąć podejście "wystarczająco blisko jest wystarczająco dobre", aby zwrócić dane, których szukamy. W pewnych okolicznościach jest to
dokładnie , czego potrzebujemy.Zilustrujemy ten artykuł za pomocą przykładowego przykładu - obliczania prowizji, które są generowane na podstawie danych sprzedaży. Rozpoczniemy od bardzo prostego scenariusza, a następnie stopniowo zwiększymy jego złożoność, dopóki jedynym racjonalnym rozwiązaniem problemu nie będzie korzystanie z funkcji VLOOKUP.Początkowy scenariusz w naszej fikcyjnej firmie działa w następujący sposób: Jeśli sprzedawca tworzy w danym roku ponad 30 000 USD sprzedaży, prowizja, jaką zarabiają na tej sprzedaży, wynosi 30%.W przeciwnym razie ich prowizja wynosi tylko 20%.Jak dotąd jest to bardzo prosty arkusz kalkulacyjny:
Aby skorzystać z tego arkusza roboczego, sprzedawca wprowadza dane dotyczące sprzedaży w komórce B1, a formuła w komórce B2 oblicza prawidłową stawkę prowizyjną, którą mają prawo otrzymać, która jest używana w komórce B3 do obliczenia.całkowita prowizja, którą sprzedawca jest winien( co jest zwykłym mnożeniem B1 i B2).
Komórka B2 zawiera jedyną interesującą część tego arkusza roboczego - formułę określającą, która stawka prowizji powinna być używana: jedna poniżej próg 30 000 USD, lub jedna powyżej próg. Ta formuła korzysta z funkcji programu Excel o nazwie IF .Dla tych czytelników, którzy nie są zaznajomieni z IF, działa to tak:
IF( warunek , wartość true, wartość jeśli false )
Gdzie warunek jest wyrażeniem, które ocenia albo prawda albo fałszywe .W powyższym przykładzie warunek jest wyrażeniem B1
Jak widać, wykorzystanie łącznej sprzedaży w wysokości 20 000 USD daje nam stawkę prowizji w wysokości 20% w komórce B2.Jeśli wprowadzimy wartość 40 000 USD, otrzymamy inną stawkę prowizji:
Tak więc nasz arkusz kalkulacyjny działa.
Uczyńmy to bardziej złożonym. Wprowadźmy drugi próg: jeśli sprzedawca zarabia więcej niż 40 000 USD, wówczas ich stawka prowizji wzrasta do 40%:
Łatwo zrozumieć w rzeczywistym świecie, ale w komórce B2 nasza formuła staje się coraz bardziej złożona. Jeśli przyjrzysz się dokładnie tej formule, zobaczysz, że trzeci parametr oryginalnej funkcji IF( wartość , jeśli jest fałszywa ), jest teraz całą własną funkcją IF.Nazywa się to zagnieżdżoną funkcją ( funkcja w ramach funkcji).Jest to całkowicie poprawne w Excelu( nawet działa!), Ale jest trudniejsze do odczytania i zrozumienia.
Nie zamierzamy zagłębiać się w to, jak i dlaczego to działa, ani też nie zbadamy niuansów funkcji zagnieżdżonych. To jest samouczek dotyczący funkcji WYSZUKAJ.PIONOWO, a nie ogólnie programu Excel.
W każdym razie jest gorzej! A kiedy zdecydujemy, że jeśli zarobią więcej niż 50 000 $, wtedy mają prawo do 50% prowizji, a jeśli zarabiają więcej niż 60 000 $, to mają prawo do 60% prowizji?
Teraz formuła w komórce B2, mimo że poprawna, stała się praktycznie nieczytelna. Nikt nie powinien pisać formuł, w których funkcje są zagnieżdżone na czterech poziomach! Z pewnością musi być prostszy sposób?
Na pewno jest. VLOOKUP na ratunek!
Przeprojektujmy trochę arkusz roboczy. Zachowamy te same liczby, ale uporządkujemy je w nowy sposób, bardziej w tabelach :
Poświęć chwilę i sprawdź sam, czy nowa tabela Rate działa dokładnie tak samo jak seria progów powyżej.
Konceptualnie zamierzamy użyć funkcji WYSZUKAJ.PIONOWO, aby sprawdzić całkowitą wartość sprzedaży sprzedawcy( z B1) w tabeli stawek i zwrócić nam odpowiednią stawkę prowizyjną.Należy zauważyć, że sprzedawca mógł rzeczywiście utworzyć sprzedaż, która jest , a nie jedną z pięciu wartości w tabeli stawek( 0 USD, 30 000 USD, 40 000 USD, 50 000 USD lub 60 000 USD).Mogły one wygenerować sprzedaż w wysokości 34 988 $.Ważne jest, aby pamiętać, że 34 988 $ ma , a nie w tabeli stawek. Zobaczmy, czy funkcja VLOOKUP może rozwiązać nasz problem, tak czy inaczej. ..
Wybieramy komórkę B2( lokalizacja, którą chcemy umieścić naszą formułę), a następnie wstawiamy funkcję WYSZUKAJ.PIONOWO z karty Formulas :
Argumenty funkcji pojawia się pole dla funkcji WYSZUKAJ.Wypełniamy argumenty( parametry) jeden po drugim, zaczynając od wartość_typu wyszukiwania , która w tym przypadku jest sumą sprzedaży z komórki B1.Umieszczamy kursor w polu Lookup_value , a następnie jednokrotnie klikamy komórkę B1:
Następnie musimy określić, dla której tabeli mają być wyszukiwane dane. W tym przykładzie jest to oczywiście tabela stawek. Umieszczamy kursor w polu Tabela_aracie , a następnie podświetlamy całą tabelę częstości - z wyłączeniem nagłówków :
Następnie musimy określić, która kolumna w tabeli zawiera informacje, które chcemy, aby nasza formuła została zwrócona do nas. W tym przypadku chcemy uzyskać stawkę prowizji, która znajduje się w drugiej kolumnie tabeli, dlatego też wpisujemy 2 do pola Col_index_num :
Na koniec wpisujemy wartość w polu Range_lookup .
Ważne: Jest to użycie tego pola, które rozróżnia dwa sposoby korzystania z funkcji VLOOKUP.Aby użyć funkcji WYSZUKAJ.PIONOWO z bazą danych, ten końcowy parametr Przedział_zasięgowy musi zawsze mieć wartość FALSE , ale przy innym użyciu funkcji WYSZUKAJ.PIONOWO, należy pozostawić to pole puste lub wprowadzić wartość PRAWDA .Podczas korzystania z VLOOKUP ważne jest, aby dokonać poprawnego wyboru dla tego końcowego parametru.
Dla jawności wprowadzimy wartość true w polu Range_lookup .Równie dobrze byłoby pozostawić to pole puste, ponieważ jest to wartość domyślna:
Ukończyliśmy wszystkie parametry. Teraz klikamy przycisk OK , a Excel buduje dla nas formułę VLOOKUP:
Jeśli eksperymentujemy z kilkoma różnymi kwotami sprzedaży, możemy przekonać się, że formuła działa.
Wniosek
W wersji "bazy danych" funkcji VLOOKUP, gdzie parametrem Range_lookup jest FALSE , wartość przekazana w pierwszym parametrze( wartość_typu wyszukiwania ) musi mieć wartość w bazie danych. Innymi słowy, szukamy dokładnego dopasowania.
Ale w tym innym użyciu VLOOKUP niekoniecznie szukamy dokładnego dopasowania. W tym przypadku "wystarczająco blisko jest wystarczająco dobre".Ale co rozumiemy przez "wystarczająco blisko"?Wykorzystajmy przykład: podczas wyszukiwania stawki prowizji od łącznej sprzedaży 34 988 USD, nasza formuła VLOOKUP zwróci nam wartość 30%, co jest poprawną odpowiedzią.Dlaczego wybrał wiersz w tabeli zawierający 30%?Co w istocie oznacza "wystarczająco blisko"?Bądźmy precyzyjni:
Gdy Range_lookup jest ustawiony na PRAWDA ( lub pominięty), funkcja VLOOKUP będzie wyglądać w kolumnie 1 i dopasowywać najwyższą wartość, która nie jest większa niż parametru wartość_typu wyszukiwania .
Należy również zauważyć, że aby ten system działał, tabela musi być posortowana w porządku rosnącym w kolumnie 1 !
Jeśli chcesz ćwiczyć w VLOOKUP, plik przykładowy zilustrowany w tym artykule można pobrać stąd.