WYSZUKAJ.PIONOWO w programie Excel, część 2 Korzystanie z funkcji WYSZUKAJ.PIONOWO bez bazy danych
W ostatnim artykule wprowadziliśmy funkcję programu Excel o nazwie WYSZUKAJ.PIONOWO i wyjaśniono, 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, analizujemy 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", który służy 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 użycia 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. Zaczniemy 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 roboczy:
Aby skorzystać z tego arkusza roboczego, sprzedawca wprowadza dane o 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óra stawka prowizji powinna być użyta: ta 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
Jak widać, wykorzystanie łącznej sprzedaży w wysokości 20 000 USD daje nam prowizję w wysokości 20% w komórce B2. Jeśli wprowadzimy wartość 40 000 USD, otrzymamy inną stawkę prowizji:
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 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. Nazywa się to 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 VLOOKUP, a nie ogólnie programu Excel.
W każdym razie jest gorzej! A jeśli 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 $, wtedy 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ź sam, czy to nowe 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ż 34 988 $. Ważne jest, aby pamiętać, że 34.988 dolarów robi nie pojawiają się w tabeli stawek. Zobaczmy teraz, czy 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ę pole dla WYSZUKIWANIA. Wypełniamy argumenty (parametry) jeden po drugim, zaczynając od Wartość_skrzynka, w tym przypadku całkowita sprzedaż 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, w jakiej tabeli szukać tych danych. W tym przykładzie oczywiście jest to tabela stawek. Umieszczamy kursor w Tabela_array pole, a następnie wyróżnij całą tabelę stawek - z wyłączeniem pozycji:
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ż 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ć VLOOKUP z bazą danych, ten ostatni parametr, Zakres wyszukiwania, zawsze musi być ustawiony na FAŁSZYWY, ale przy innym użyciu funkcji WYSZUKAJ.PIONOWO, musimy pozostawić puste pole lub wprowadzić wartość PRAWDZIWE. Podczas korzystania z funkcji 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 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.
