W niedawnym artykule wprowadziliśmy funkcję Excel o nazwie WYSZUKAJ.PIONOWO i wyjaśnił, w jaki sposób można go 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 koncepcji wyjaśnionych w tym artykule jest już znanych czytelnikowi.
Podczas pracy z bazami danych funkcja VLOOKUP otrzymuje "unikalny identyfikator" służący do identyfikacji rekordu danych, który chcemy znaleźć w bazie danych (np. Kod produktu lub identyfikator klienta). Ten unikalny identyfikator musi istnieją w bazie danych, w przeciwnym razie VLOOKUP zwraca 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 tak jest dokładnie Czego potrzebujemy.
Zilustrujemy ten artykuł rzeczywistym przykładem - 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 całkiem prosty arkusz roboczy:
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ę prowizji, do której otrzymania jest uprawniona, która jest używana w komórce B3 do obliczenia łącznej prowizji, którą sprzedawca jest winien (co jest prostym mnożeniem B1 i B2).
Komórka B2 zawiera tylko interesującą część tego arkusza roboczego - formułę określającą, którą stawkę prowizji użyć: tą poniżej próg 30 000 USD lub jeden powyżej próg. Ta formuła korzysta z funkcji programu Excel o nazwie JEŚLI. Dla tych czytelników, którzy nie znają IF, działa to tak:
JEŚLI(warunek, wartość true, wartość false)
Gdzie stan jest wyrażeniem, które ocenia albo prawdziwe lub fałszywy. W powyższym przykładzie stan jest wyrażeniem B1<>, który można odczytać jako "Czy B1 jest mniejszy niż B5?", lub inaczej: "Czy całkowita sprzedaż jest niższa od progu". Jeśli odpowiedź na to pytanie brzmi "tak" (prawda), wówczas używamy wartość, jeśli jest prawdziwa parametr funkcji, a mianowicie B6 w tym przypadku - stawka prowizji, jeśli całkowita sprzedaż była poniżej próg. Jeśli odpowiedź na pytanie brzmi "nie" (fałsz), wówczas używamy wartość false parametr funkcji, a mianowicie B7 w tym przypadku - stawka prowizji, jeśli całkowita sprzedaż była powyżej próg.
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:
Nasz arkusz kalkulacyjny działa.
Zróbmy to bardziej skomplikowanym. 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 prawdziwym ś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 trzeci parametr oryginalnej funkcji IF ( wartość false) jest teraz pełną funkcją IF. To się nazywa funkcja zagnieżdżona (funkcja w 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 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 zorganizujemy je w nowy sposób, więcej tabelaryczny sposób:
Poświęć chwilę i sprawdź na własne oczy, że nowy Tabela stawek 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ą. Pamiętaj, że sprzedawca rzeczywiście mógł utworzyć sprzedaż, która jest nie jedna 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 dolarów robi nie pojawiają się w tabeli stawek. Zobaczmy, czy mimo to VLOOKUP może rozwiązać nasz problem ...
Wybieramy komórkę B2 (miejsce, w którym chcemy umieścić naszą formułę), a następnie wstawiamy funkcję VLOOKUP z pliku Formuły patka:
The Argumenty funkcji pojawi się okno dla WYSZUKIWANIA. Wypełniamy argumenty (parametry) jeden po drugim, zaczynając od Wartość_skrzynka, która w tym przypadku jest sumą sprzedaży z komórki B1. Umieszczamy kursor w Wartość_skrzynka pole, a następnie raz kliknij komórkę B1:
Następnie musimy określić w VLOOKUP, która tabela będzie przeglądać te dane. W tym przykładzie oczywiście jest to tabela stawek. Umieszczamy kursor w Table_array pole, a następnie podświetl całą tabelę stawek - bez 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 w tabeli, dlatego też wprowadzamy a 2 do Col_index_num pole:
Na koniec wpisujemy wartość w Zakres wyszukiwania pole.
Ważne: To użycie tego pola odróżnia dwa sposoby korzystania z funkcji VLOOKUP. Aby użyć funkcji WYSZUKAJ.PIONOWO z bazą danych, ten ostatni parametr, Zakres wyszukiwania, musi zawsze być ustawione na FAŁSZYWY, ale przy innym użyciu funkcji WYSZUKAJ.PIONOWO, musimy pozostawić puste pole lub wprowadzić wartość PRAWDZIWE. Podczas korzystania z VLOOKUP ważne jest, aby dokonać poprawnego wyboru dla tego końcowego parametru.
Aby być jednoznacznym, wpiszemy wartość prawdziwe w Zakres wyszukiwania pole. Byłoby dobrze zostawić go puste, ponieważ jest to wartość domyślna:
Ukończyliśmy wszystkie parametry. Teraz klikamy dobrze , a Excel tworzy 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" VLOOKUP, gdzie Zakres wyszukiwania parametr to FAŁSZYWY, wartość przekazana w pierwszym parametrze (Wartość_skrzynka) musi być obecnym 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 Zakres wyszukiwania jest ustawione na PRAWDZIWE (lub pominięte), funkcja VLOOKUP będzie wyglądała w kolumnie 1 i była zgodna najwyższa wartość, która nie jest większa niż Wartość_skrzynka parametr.
Ważne jest również, aby pamiętać, ż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.
