1Aug
VLOOKUP to jedna z najbardziej użytecznych funkcji programu Excel, a także jedna z najmniej zrozumiałych. W tym artykule demodrujemy VLOOKUP na podstawie rzeczywistego przykładu. Stworzymy wzór do faktur dla fikcyjnej firmy.
VLOOKUP jest funkcją programu Excel .W tym artykule założymy, że czytelnik ma już wiedzę na temat funkcji programu Excel i może korzystać z podstawowych funkcji, takich jak SUMA, ŚREDNIA i DZISIAJ.W najczęstszym użyciu funkcja VLOOKUP jest funkcją , co oznacza, że działa z tabelami bazy danych - lub prostszym, zawiera listę zadań w arkuszu programu Excel. Jaki rodzaj rzeczy? Cóż, coś w rodzaju .Możesz mieć arkusz roboczy zawierający listę pracowników, produktów, klientów lub płyt CD z kolekcji CD lub gwiazd na nocnym niebie. To naprawdę nie ma znaczenia.
Oto przykład listy lub bazy danych. W tym przypadku jest to lista produktów, które sprzedaje nasza fikcyjna firma:
Zazwyczaj listy takie mają jakiś unikalny identyfikator dla każdej pozycji na liście. W takim przypadku unikalny identyfikator znajduje się w kolumnie "Kod towaru".Uwaga: Aby funkcja WYSZUKAJ.PIONOWA działała z bazą danych / listą, lista
musi mieć kolumnę zawierającą unikalny identyfikator( lub "klucz" lub "ID") i , która kolumna musi być pierwszą kolumną w tabeli.Nasza przykładowa baza danych spełnia powyższe kryterium.Najtrudniejszą częścią korzystania z VLOOKUP jest dokładne zrozumienie, do czego służy. Zobaczmy więc, czy najpierw możemy to wyjaśnić:
VLOOKUP pobiera informacje z bazy danych / listy na podstawie dostarczonej instancji unikalnego identyfikatora.
W powyższym przykładzie wstawiałbyś funkcję WYSZUKAJ.PIONOWO do innego arkusza kalkulacyjnego z kodem przedmiotu, a następnie zwróciłaby ci opis odpowiedniego przedmiotu, jego cenę lub jego dostępność( ilość "W magazynie") zgodnie z opisem woryginalna lista. Które z tych informacji spowodują, że wrócisz? Cóż, decydujesz o tym, kiedy tworzysz formułę.
Jeśli potrzebujesz tylko jednej informacji z bazy danych, byłoby bardzo trudno przejść do niej, aby utworzyć formułę z funkcją VLOOKUP.Zazwyczaj używa się tego rodzaju funkcji w arkuszu kalkulacyjnym wielokrotnego użytku, takim jak szablon. Za każdym razem, gdy ktoś wprowadzi poprawny kod przedmiotu, system pobierze wszystkie niezbędne informacje o odpowiednim elemencie.
Stwórzmy przykład: Szablon faktury , który możemy wielokrotnie wykorzystywać w naszej fikcyjnej firmie.
Najpierw uruchamiamy program Excel, a my tworzymy sobie pustą fakturę:
Tak to będzie działać: osoba korzystająca z szablonu faktury wypełni serię kodów pozycji w kolumnie "A", a system pobierze każdy elementopis i cena z naszej bazy danych produktów. Informacje te zostaną wykorzystane do obliczenia sumy linii dla każdej pozycji( zakładając, że wprowadzimy prawidłową ilość).
Aby ten przykład był prosty, zlokalizujemy bazę danych produktu na oddzielnym arkuszu w tym samym skoroszycie:
W rzeczywistości jest bardziej prawdopodobne, że baza danych produktów byłaby umieszczona w oddzielnym skoroszycie. Nie ma to większego znaczenia dla funkcji VLOOKUP, która tak naprawdę nie obchodzi, czy baza danych znajduje się na tym samym arkuszu, innym arkuszu lub zupełnie innym skoroszycie.
Stworzyliśmy więc naszą bazę danych produktów, która wygląda następująco:
Aby przetestować formułę VLOOKUP, którą zamierzamy napisać, najpierw wprowadzamy poprawny kod towaru do komórki A11 naszej pustej faktury:
Następnieprzenieś aktywną komórkę do komórki, w której chcemy przechowywać informacje pobrane z bazy danych przez VLOOKUP.Co ciekawe, jest to krok, który większość ludzi robi źle. Aby wyjaśnić dalej: Zamierzamy utworzyć formułę VLOOKUP, która pobierze opis odpowiadający kodzie pozycji w komórce A11.Gdzie chcemy umieścić ten opis, gdy go otrzymamy? Oczywiście w komórce B11.Właśnie tam zapisujemy formułę VLOOKUP: w komórce B11.Wybierz teraz komórkę B11.
Musimy znaleźć listę wszystkich dostępnych funkcji, które Excel ma do zaoferowania, abyśmy mogli wybrać WYSZUKAJ.PIONOWO i uzyskać pomoc w wypełnieniu formuły. Można to znaleźć, klikając najpierw kartę Formulas , a następnie klikając Insert Function :
Pojawia się okno, które pozwala nam wybrać dowolne funkcje dostępne w Excelu.
Aby znaleźć szukaną, możemy wpisać wyszukiwany termin, taki jak "lookup"( ponieważ funkcja, którą jesteśmy zainteresowani, to funkcja ).System zwróci nam listę wszystkich funkcji związanych z wyszukiwaniem w programie Excel. VLOOKUP jest drugim na liście. Wybierz to kliknięcie OK .
Pojawia się pole Argumenty , które monituje nas o wszystkie argumenty ( lub parametry ) potrzebne do ukończenia funkcji WYSZUKAJ.PIONOWO.Możesz myśleć o tym polu jako funkcji z pytaniem nas o następujące pytania:
- Jakiego unikalnego identyfikatora szukasz w bazie danych?
- Gdzie jest baza danych?
- Które informacje z bazy danych, powiązane z unikalnym identyfikatorem, chcesz pobrać dla siebie?
Pierwsze trzy argumenty są oznaczone pogrubioną czcionką , co oznacza, że są to argumenty ( funkcja VLOOKUP jest niekompletna bez nich i nie zwróci poprawnej wartości).Czwarty argument nie jest pogrubiony, co oznacza, że jest opcjonalny:
Uzupełnimy argumenty w kolejności od góry do dołu.
Pierwszym argumentem, który musimy wykonać, jest argument Lookup_value. Funkcja musi nam powiedzieć, gdzie znaleźć unikatowy identyfikator( w tym przypadku), że powinien zwracać opis. Musimy wybrać kod przedmiotu, który wpisaliśmy wcześniej( w A11).
Kliknij ikonę selektora po prawej stronie pierwszego argumentu:
Następnie kliknij komórkę zawierającą kod pozycji( A11) i naciśnij Wpisz :
Wartość "A11" jest wstawiona do pierwszego argumentu.
Teraz musimy wprowadzić wartość dla argumentu Table_array. Innymi słowy, musimy poinformować VLOOKUP, gdzie znaleźć bazę danych / listę.Kliknij ikonę wyboru obok drugiego argumentu:
Teraz znajdź bazę danych / listę i wybierz całą listę - nie zawiera linii nagłówka .W naszym przykładzie baza danych znajduje się w osobnym arkuszu roboczym, więc najpierw klikamy tę kartę arkusza:
Następnie wybieramy całą bazę danych, bez wiersza nagłówka:
. .. i naciśnij Wprowadź .Zakres komórek reprezentujących bazę danych( w tym przypadku "Baza danych produktów"! A2: D7 ") jest wprowadzany automatycznie dla nas w drugim argumencie.
Teraz musimy wprowadzić trzeci argument Col_index_num .Używamy tego argumentu do określenia VLOOKUP, która informacja z bazy danych, powiązana z naszym kodem towaru w A11, chcemy zwrócić do nas. W tym konkretnym przykładzie chcielibyśmy, aby zwrócono nam opis przedmiotu .Jeśli spojrzysz na arkusz roboczy bazy danych, zauważysz, że kolumna "Opis" jest kolumną drugiej w bazie danych. Oznacza to, że musimy wprowadzić wartość "2" do pola Col_index_num :
Należy zauważyć, że nie wpisujemy tutaj "2", ponieważ kolumna "Opis" znajduje się w kolumnie B ten arkusz roboczy. Gdyby baza danych zaczęła się w kolumnie K arkusza roboczego, w tym polu wpisalibyśmy "2", ponieważ kolumna "Opis" jest drugą kolumną w zbiorze komórek, które wybraliśmy, określając "Tablica_tabeli".
Na koniec musimy zdecydować, czy wprowadzić wartość do końcowego argumentu VLOOKUP, Range_lookup .Ten argument wymaga wartości true lub false lub też należy ją pozostawić pustą.Podczas korzystania z VLOOKUP z bazami danych( tak jak jest to prawdą 90% czasu), sposób decydowania o tym, co umieścić w tym argumencie, można sobie wyobrazić w następujący sposób:
Jeśli pierwsza kolumna bazy danych( kolumna zawierająca unikalne identyfikatory)jest posortowana alfabetycznie / numerycznie w porządku rosnącym, wówczas można wprowadzić wartość true do tego argumentu lub pozostawić ją pustą.
Jeśli pierwsza kolumna bazy danych jest nie posortowana lub jest posortowana w porządku malejącym, to musi wpisać wartość false do tego argumentu
Ponieważ pierwsza kolumna naszej bazy danych jest nie posortowana,wprowadzamy false do tego argumentu:
To wszystko! Podaliśmy wszystkie informacje wymagane dla VLOOKUP, aby zwrócić potrzebną wartość.Kliknij przycisk OK i zauważ, że opis odpowiadający kodem pozycji "R99245" został poprawnie wprowadzony do komórki B11:
Formuła, która została stworzona dla nas wygląda tak:
Jeśli wprowadzimy inny kod pozycji do komórkiA11, zaczniemy widzieć moc funkcji WYSZUKAJ.PIONOWO: Komórka opisu zmienia się tak, aby pasowała do nowego kodu pozycji:
Możemy wykonać podobny zestaw kroków, aby uzyskać cenę produktu zwróconą do komórki E11.Zauważ, że nowa formuła musi zostać utworzona w komórce E11.Wynik będzie wyglądał następująco:
. .. a formuła będzie wyglądać następująco:
Zwróć uwagę, że jedyna różnica między tymi dwoma wzorami to trzeci argument( Col_index_num ) zmienił się z "2" na "3"( ponieważchcemy danych pobranych z 3. kolumny w bazie danych).
Jeśli zdecydujemy się na zakup 2 z tych przedmiotów, wpiszemy "2" w komórkę D11.Następnie wprowadzilibyśmy prostą formułę do komórki F11, aby uzyskać sumę linii:
= D11 * E11
. .. która wygląda tak. ..
Kończenie szablonu faktury
Dowiedzieliśmy się już dużo o VLOOKUP.W rzeczywistości nauczyliśmy się wszystkiego, czego się nauczymy w tym artykule. Należy pamiętać, że funkcja VLOOKUP może być używana w innych okolicznościach niż bazy danych. Jest to mniej powszechne i może być omówione w przyszłych artykułach How-To Geek.
Nasz szablon faktury nie jest jeszcze kompletny. Aby to zrobić, wykonamy następujące czynności:
- Usunęlibyśmy przykładowy kod przedmiotu z komórki A11 i "2" z komórki D11.Spowoduje to, że nasze nowo utworzone formaty VLOOKUP będą wyświetlać komunikaty o błędach:
Możemy temu zaradzić przez rozważne użycie funkcji i programu Excel ( ).Zmieniamy naszą formułę z tego. .. = VLOOKUP( A11, 'Baza danych produktów'! A2: D7,2, FAŁSZ) . .. do tego. .. = JEŻELI( ISBLANK( A11), "", WYSZUKAJ.PIONOWO( A11, 'Baza danych produktów"! A2: D7,2, FALSE))
- Kopiujemy formuły w komórkach B11, E11 i F11 w dół do reszty wierszy pozycji faktury. Zwróć uwagę, że jeśli to zrobimy, wynikowe formuły nie będą poprawnie odnosić się do tabeli bazy danych. Możemy to naprawić, zmieniając odwołania do komórek dla bazy danych na odwołania do komórek absolute .Alternatywnie - a nawet lepiej - możemy utworzyć o nazwie dla całej bazy danych produktów( np. "Produkty") i używać tej nazwy zakresu zamiast odwołań do komórek. Wzór zmieni się od tego. .. = JEŻELI( ISBLANK( A11), "", WYSZUKAJ.PIONOWO( A11, "Baza danych produktów"! A2: D7,2, FAŁSZ)) . .. do tego. .. = JEŻELI( ISBLANK( A11), "", WYSZUKAJ.PIONOWO( A11, Produkty, 2, FAŁSZ)) . .. i następnie skopiować formuły do reszty wierszy pozycji faktury.
- Prawdopodobnie "zablokujemy" komórki zawierające nasze formuły( lub raczej odblokujemy inne komórki ), a następnie zabezpieczysz arkusz roboczy, aby zapewnić, że nasze starannie skonstruowane formuły nie zostaną przypadkowo nadpisane, gdy ktoś przyjdzie wypełnićna fakturze.
- Zapisywalibyśmy plik jako szablon , aby mógł on zostać ponownie użyty przez wszystkich w naszej firmie
Gdybyśmy mieli poczucie, że jest naprawdę , utworzymy bazę danych wszystkich naszych klientów w innym arkuszu, a następnie użyjemyidentyfikator klienta wprowadzony w komórce F5, aby automatycznie wpisać nazwisko i adres klienta w komórkach B6, B7 i B8.
Jeśli chcesz ćwiczyć w VLOOKUP lub po prostu zobaczyć nasz wynikowy szablon faktury, możesz go pobrać stąd.