Spis treści
- Funkcja WYSZUKAJ.PIONOWO w Excelu – ogólny opis i składnia
- Przykłady WYSZUKAJ.PIONOWO
- Dokładne lub przybliżone dopasowanie w funkcji WYSZUKAJ.PIONOWO
- WYSZUKAJ.PIONOWO w Excelu – musisz o tym pamiętać!
Dziś rozpoczynamy serię artykułów opisujących jedną z najbardziej przydatnych funkcji Excela − VPR (WYSZUKAJ.PIONOWO). Ta funkcja jest jednocześnie jedną z najbardziej złożonych i najmniej rozumianych.
W tym samouczku na VPR Postaram się przedstawić podstawy tak prosto, jak to możliwe, aby proces uczenia się był jak najbardziej przejrzysty dla niedoświadczonych użytkowników. Ponadto przestudiujemy kilka przykładów z formułami Excela, które zademonstrują najczęstsze przypadki użycia funkcji VPR.
Funkcja WYSZUKAJ.PIONOWO w Excelu – ogólny opis i składnia
Więc co to jest VPR? Cóż, przede wszystkim jest to funkcja Excela. Co ona robi? Wyszukuje określoną wartość i zwraca odpowiednią wartość z drugiej kolumny. Z technicznego punktu widzenia, VPR wyszukuje wartość w pierwszej kolumnie danego zakresu i zwraca wynik z innej kolumny w tym samym wierszu.
W najczęstszej aplikacji funkcja VPR przeszukuje bazę danych pod kątem danego unikalnego identyfikatora i wydobywa z bazy pewne informacje z nim związane.
Pierwsza litera w nazwie funkcji VPR (WYSZUKAJ.PIONOWO) oznacza Вpionowy (Vpionowy). Dzięki temu możesz odróżnić VPR od GPR (HLOOKUP), która wyszukuje wartość w górnym wierszu zakresu − Гpoziomy (Hpoziomo).
Funkcjonować VPR dostępne w Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP i Excel 2000.
Składnia funkcji WYSZUKAJ.PIONOWO
Funkcjonować VPR (WYSZUKAJ.PIONOWO) ma następującą składnię:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Jak widać funkcja VPR w Microsoft Excel ma 4 opcje (lub argumenty). Pierwsze trzy są obowiązkowe, ostatnie opcjonalne.
- lookup_value (lookup_value) — wartość do wyszukania. Może to być wartość (liczba, data, tekst) lub odwołanie do komórki (zawierające wartość wyszukiwania) lub wartość zwrócona przez inną funkcję programu Excel. Na przykład ta formuła będzie szukać wartości 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Jeśli wartość wyszukiwania jest mniejsza niż najmniejsza wartość w pierwszej kolumnie wyszukiwanego zakresu, funkcja VPR zgłosi błąd #W (#Nie dotyczy).
- table_array (tabela) – dwie lub więcej kolumn danych. Pamiętaj, funkcja VPR zawsze szuka wartości w pierwszej kolumnie zakresu podanego w argumencie table_array (stół). Widoczny zakres może zawierać różne dane, takie jak tekst, daty, liczby, wartości logiczne. W funkcji nie jest rozróżniana wielkość liter, co oznacza, że duże i małe litery są traktowane tak samo. Więc nasz wzór będzie szukał wartości 40 w komórkach z A2 do A15, ponieważ A jest pierwszą kolumną z zakresu A2:B15 podanego w argumencie table_array (stół):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (numer_kolumny) to numer kolumny w danym zakresie, z którego zostanie zwrócona wartość w znalezionym wierszu. Skrajna lewa kolumna w podanym zakresie to 1, druga kolumna to 2, trzecia kolumna to 3 i tak dalej. Teraz możesz przeczytać całą formułę:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Formuła szuka wartości 40 w zasięgu A2: A15 i zwraca odpowiednią wartość z kolumny B (ponieważ B jest drugą kolumną w zakresie A2:B15).
Jeśli wartość argumentu col_index_num (numer_kolumny) mniej niż 1następnie VPR zgłosi błąd #WARTOŚĆ! (#WARTOŚĆ!). A jeśli jest więcej niż liczba kolumn w zakresie table_array (tabela), funkcja zwróci błąd #ADR! (#POŁĄCZYĆ!).
- Zakres wyszukiwania (range_lookup) – określa, czego szukać:
- dokładne dopasowanie, argument musi być równy FAŁSZYWY (FAŁSZYWY);
- dopasowanie przybliżone, argument równy PRAWDZIWY KOD (PRAWDA) lub w ogóle nie określono.
Ten parametr jest opcjonalny, ale bardzo ważny. W dalszej części tego samouczka VPR Pokażę kilka przykładów wyjaśniających, jak pisać formuły do wyszukiwania dokładnych i przybliżonych dopasowań.
Przykłady WYSZUKAJ.PIONOWO
Mam nadzieję, że funkcja VPR stają się dla ciebie trochę jaśniejsze. Spójrzmy teraz na kilka przypadków użycia VPR w formułach z rzeczywistymi danymi.
Jak używać funkcji WYSZUKAJ.PIONOWO do wyszukiwania w innym arkuszu Excela
W praktyce formuły z funkcją VPR są rzadko używane do wyszukiwania danych w tym samym arkuszu. Najczęściej będziesz wyszukiwać i pobierać odpowiednie wartości z innego arkusza.
W celu wykorzystania VPR, szukaj w innym arkuszu Microsoft Excel, Musisz w argumencie table_array (tabela) określ nazwę arkusza za pomocą wykrzyknika, po którym następuje zakres komórek. Na przykład poniższy wzór pokazuje, że zakres A2: B15 znajduje się na arkuszu o nazwie Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Oczywiście nazwy arkusza nie trzeba wpisywać ręcznie. Po prostu zacznij wpisywać formułę, a jeśli chodzi o argument table_array (tabela), przejdź do żądanego arkusza i wybierz żądany zakres komórek za pomocą myszy.
Formuła pokazana na poniższym zrzucie ekranu wyszukuje tekst „Produkt 1” w kolumnie A (jest to pierwsza kolumna zakresu A1:B2) w arkuszu Cennik.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Pamiętaj, że szukając wartości tekstowej, musisz ją ująć w cudzysłów („”), jak to zwykle robi się w formułach Excela.
Dla argumentu table_array (tabela) wskazane jest, aby zawsze używać odwołań bezwzględnych (ze znakiem $). W takim przypadku zakres wyszukiwania pozostanie niezmieniony podczas kopiowania formuły do innych komórek.
Wyszukaj w innym skoroszycie za pomocą funkcji WYSZUKAJ.PIONOWO
Funkcjonować VPR pracował między dwoma skoroszytami programu Excel, musisz podać nazwę skoroszytu w nawiasach kwadratowych przed nazwą arkusza.
Na przykład poniżej znajduje się formuła, która wyszukuje wartość 40 na prześcieradle Sheet2 W książce Liczby.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Oto najprostszy sposób na utworzenie formuły w programie Excel za pomocą VPRktóry łączy się z innym skoroszytem:
- Otwórz obie książki. Nie jest to wymagane, ale w ten sposób łatwiej jest stworzyć formułę. Nie chcesz ręcznie wprowadzać nazwy skoroszytu, prawda? Dodatkowo uchroni Cię przed przypadkowymi literówkami.
- Zacznij pisać funkcję VPRa jeśli chodzi o kłótnię table_array (tabela), przejdź do innego skoroszytu i wybierz w nim wymagany zakres wyszukiwania.
Poniższy zrzut ekranu przedstawia formułę z wyszukiwaniem ustawionym na zakres w skoroszycie Cennik.xlsx na prześcieradle Cennik.
Funkcjonować VPR będzie działać nawet po zamknięciu przeszukiwanego skoroszytu, a pełna ścieżka do pliku skoroszytu pojawi się na pasku formuły, jak pokazano poniżej:
Jeśli nazwa skoroszytu lub arkusza zawiera spacje, musi być ujęta w apostrofy:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Jak używać nazwanego zakresu lub tabeli w formułach za pomocą funkcji WYSZUKAJ.PIONOWO
Jeśli planujesz używać tego samego zakresu wyszukiwania w wielu funkcjach VPR, możesz utworzyć nazwany zakres i wpisać jego nazwę do formuły jako argument table_array (stół).
Aby utworzyć nazwany zakres, po prostu zaznacz komórki i wprowadź odpowiednią nazwę w polu Imię, po lewej stronie paska formuły.
Teraz możesz zapisać następujący wzór na znalezienie ceny produktu Produkt 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Większość nazw zakresów działa dla całego skoroszytu programu Excel, więc nie ma potrzeby określania nazwy arkusza dla argumentu table_array (tabela), nawet jeśli formuła i zakres wyszukiwania znajdują się w różnych arkuszach. Jeśli znajdują się w różnych skoroszytach, przed nazwą zakresu musisz podać nazwę skoroszytu, na przykład w następujący sposób:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Więc formuła wygląda o wiele jaśniej, zgadza się? Ponadto używanie nazwanych zakresów jest dobrą alternatywą dla odwołań bezwzględnych, ponieważ nazwany zakres nie zmienia się podczas kopiowania formuły do innych komórek. Oznacza to, że możesz mieć pewność, że zakres wyszukiwania w formule zawsze pozostanie poprawny.
Jeśli przekonwertujesz zakres komórek do pełnoprawnego arkusza kalkulacyjnego Excel za pomocą polecenia Stół (Tabela) zakładka Wprowadzenie (Wstaw), a następnie po wybraniu zakresu za pomocą myszy Microsoft Excel automatycznie doda nazwy kolumn (lub nazwę tabeli, jeśli zaznaczysz całą tabelę) do formuły.
Gotowa formuła będzie wyglądać mniej więcej tak:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
A może nawet tak:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Podczas korzystania z nazwanych zakresów linki będą wskazywać te same komórki bez względu na to, gdzie skopiujesz funkcję VPR w skoroszycie.
Używanie symboli wieloznacznych w formułach WYSZUKAJ.PIONOWO
Podobnie jak w przypadku wielu innych funkcji, VPR Możesz użyć następujących symboli wieloznacznych:
- Znak zapytania (?) – zastępuje dowolny pojedynczy znak.
- Gwiazdka (*) – zastępuje dowolny ciąg znaków.
Używanie symboli wieloznacznych w funkcjach VPR może być przydatny w wielu przypadkach, na przykład:
- Kiedy nie pamiętasz dokładnie tekstu, który musisz znaleźć.
- Gdy chcesz znaleźć słowo, które jest częścią zawartości komórki. Wiedz, że VPR przeszukuje zawartość komórki jako całości, tak jakby opcja była włączona Dopasuj całą zawartość komórki (Cała komórka) w standardowym wyszukiwaniu Excel.
- Gdy komórka zawiera dodatkowe spacje na początku lub na końcu treści. W takiej sytuacji możesz długo męczyć się, próbując dowiedzieć się, dlaczego formuła nie działa.
Przykład 1: Szukam tekstu, który zaczyna się lub kończy na określone znaki
Załóżmy, że chcesz wyszukać konkretnego klienta w przedstawionej poniżej bazie danych. Nie pamiętasz jego nazwiska, ale wiesz, że zaczyna się od „ack”. Oto formuła, która dobrze sobie poradzi:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Teraz, gdy masz pewność, że znalazłeś poprawną nazwę, możesz użyć tej samej formuły, aby znaleźć kwotę zapłaconą przez tego klienta. Aby to zrobić, po prostu zmień trzeci argument funkcji VPR do żądanego numeru kolumny. W naszym przypadku jest to kolumna C (3. w zakresie):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Oto kilka przykładów z symbolami wieloznacznymi:
~ Znajdź imię kończące się na „man”:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Znajdź imię, które zaczyna się od „ad” i kończy na „syn”:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Znajdujemy imię na liście składającej się z 5 znaków:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Funkcjonować VPR z symbolami wieloznacznymi działały poprawnie, jako czwarty argument należy zawsze używać FAŁSZYWY (FAŁSZYWY). Jeśli zakres wyszukiwania zawiera więcej niż jedną wartość, która pasuje do wyszukiwanych haseł z symbolami wieloznacznymi, zostanie zwrócona pierwsza znaleziona wartość.
Przykład 2: Połącz symbole wieloznaczne i odwołania do komórek w formułach WYSZUKAJ.PIONOWO
Przyjrzyjmy się teraz nieco bardziej złożonemu przykładowi wyszukiwania za pomocą funkcji VPR według wartości w komórce. Wyobraź sobie, że kolumna A to lista kluczy licencyjnych, a kolumna B to lista nazw, które posiadają licencję. Ponadto masz część (kilka znaków) pewnego rodzaju klucza licencyjnego w komórce C1 i chcesz znaleźć nazwę właściciela.
Można to zrobić za pomocą następującego wzoru:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Ta formuła wyszukuje wartość z komórki C1 w podanym zakresie i zwraca odpowiednią wartość z kolumny B. Zauważ, że w pierwszym argumencie używamy znaku ampersand (&) przed i po odwołaniu do komórki, aby połączyć ciąg tekstowy.
Jak widać na poniższym rysunku, funkcja VPR zwraca „Jeremy Hill”, ponieważ jego klucz licencyjny zawiera sekwencję znaków z komórki C1.
Zauważ, że argument table_array (tabela) na powyższym zrzucie ekranu zawiera nazwę tabeli (Tabela 7) zamiast określania zakresu komórek. To właśnie zrobiliśmy w poprzednim przykładzie.
Dokładne lub przybliżone dopasowanie w funkcji WYSZUKAJ.PIONOWO
Na koniec przyjrzyjmy się bliżej ostatniemu argumentowi podanemu dla funkcji VPR - Zakres wyszukiwania (widok_interwału). Jak wspomniano na początku lekcji, ten argument jest bardzo ważny. Możesz uzyskać zupełnie inne wyniki w tej samej formule z jej wartością PRAWDZIWY KOD (Prawda lub FAŁSZYWY (FAŁSZYWY).
Najpierw dowiedzmy się, co oznacza Microsoft Excel przez dokładne i przybliżone dopasowania.
- Jeśli argument Zakres wyszukiwania (wyszukiwanie_zakresu) jest równe FAŁSZYWY (FAŁSZ), formuła szuka dokładnego dopasowania, tj. dokładnie takiej samej wartości jak podana w argumencie lookup_value (wyszukiwana_wartość). Jeśli w pierwszej kolumnie zakresu tzdolny_tablica (tabela) napotyka dwie lub więcej wartości pasujących do argumentu lookup_value (wartość_wyszukiwania), wtedy zostanie wybrany pierwszy. Jeśli nie zostaną znalezione żadne dopasowania, funkcja zgłosi błąd #W (#Nie dotyczy). Na przykład poniższa formuła zgłosi błąd #W (#N/A) jeśli nie ma wartości w zakresie A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Jeśli argument Zakres wyszukiwania (wyszukiwanie_zakresu) jest równe PRAWDZIWY KOD (PRAWDA), formuła szuka przybliżonego dopasowania. Dokładniej, najpierw funkcja VPR szuka dokładnego dopasowania, a jeśli nie zostanie znalezione, wybiera przybliżone. Dopasowanie przybliżone to największa wartość, która nie przekracza wartości określonej w argumencie. lookup_value (wyszukiwana_wartość).
Jeśli argument Zakres wyszukiwania (wyszukiwanie_zakresu) jest równe PRAWDZIWY KOD (PRAWDA) lub nie określono, to wartości w pierwszej kolumnie zakresu należy posortować w kolejności rosnącej, czyli od najmniejszej do największej. W przeciwnym razie funkcja VPR może zwrócić błędny wynik.
Aby lepiej zrozumieć znaczenie wyboru PRAWDZIWY KOD (PRAWDA) lub FAŁSZYWY (FAŁSZ), spójrzmy na więcej formuł z funkcją VPR i spójrz na wyniki.
Przykład 1: znajdowanie dokładnego dopasowania za pomocą funkcji WYSZUKAJ.PIONOWO
Jak pamiętasz, aby wyszukać dokładne dopasowanie, czwarty argument funkcji VPR powinno mieć znaczenie FAŁSZYWY (FAŁSZYWY).
Wróćmy do tabeli z pierwszego przykładu i dowiedzmy się, które zwierzę może poruszać się z dużą prędkością 50 mile na godzinę. Wierzę, że ta formuła nie sprawi Ci żadnych trudności:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Zwróć uwagę, że nasz zakres wyszukiwania (kolumna A) zawiera dwie wartości 50 – w ogniwach A5 и A6. Formuła zwraca wartość z komórki B5. Czemu? Ponieważ szukając dokładnego dopasowania, funkcja VPR używa pierwszej znalezionej wartości, która pasuje do szukanej.
Przykład 2: Używanie funkcji WYSZUKAJ.PIONOWO do znajdowania przybliżonego dopasowania
Kiedy korzystasz z funkcji VPR szukać przybliżonego dopasowania, tj. kiedy argument Zakres wyszukiwania (wyszukiwanie_zakresu) jest równe PRAWDZIWY KOD (PRAWDA) lub pominięte, pierwszą rzeczą, którą musisz zrobić, to posortować zakres według pierwszej kolumny w kolejności rosnącej.
Jest to bardzo ważne, ponieważ funkcja VPR zwraca następną największą wartość po podanej, a następnie wyszukiwanie się zatrzymuje. Jeśli zaniedbasz prawidłowe sortowanie, otrzymasz bardzo dziwne wyniki lub komunikat o błędzie. #W (#Nie dotyczy).
Teraz możesz użyć jednej z następujących formuł:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Jak widać, chcę się dowiedzieć, które ze zwierząt ma prędkość najbliższą 69 mile na godzinę. A oto wynik funkcji zwróconej do mnie VPR:
Jak widać, formuła zwróciła wynik Antylopa (Antylopa), którego prędkość 61 mil na godzinę, chociaż lista zawiera również Cheetah (Gepard), który biegnie szybko 70 mil na godzinę, a 70 jest bliżej 69 niż 61, prawda? Dlaczego to się dzieje? Ponieważ funkcja VPR podczas wyszukiwania przybliżonego dopasowania zwraca największą wartość, która nie jest większa niż wyszukiwana.
Mam nadzieję, że te przykłady rzucą trochę światła na pracę z funkcją VPR w Excelu i nie patrzysz już na nią jak na outsidera. Teraz nie zaszkodzi pokrótce powtórzyć kluczowe punkty materiału, który przestudiowaliśmy, aby lepiej go utrwalić w pamięci.
WYSZUKAJ.PIONOWO w Excelu – musisz o tym pamiętać!
- Funkcjonować VPR Excel nie może patrzeć w lewo. Zawsze szuka wartości w skrajnej lewej kolumnie zakresu podanego przez argument table_array (stół).
- W działaniu VPR wszystkie wartości są niewrażliwe na wielkość liter, tzn. małe i duże litery są równoważne.
- Jeśli szukana wartość jest mniejsza niż minimalna wartość w pierwszej kolumnie szukanego zakresu, funkcja VPR zgłosi błąd #W (#Nie dotyczy).
- Jeśli 3. argument col_index_num (numer_kolumny) mniej niż 1funkcjonować VPR zgłosi błąd #WARTOŚĆ! (#WARTOŚĆ!). Jeśli jest większa niż liczba kolumn w zakresie table_array (tabela), funkcja zgłosi błąd #ADR! (#POŁĄCZYĆ!).
- Użyj bezwzględnych odwołań do komórek w argumencie table_array (tabela), aby podczas kopiowania formuły zachować prawidłowy zakres wyszukiwania. Spróbuj użyć nazwanych zakresów lub tabel w programie Excel jako alternatywy.
- Przeprowadzając przybliżone wyszukiwanie, pamiętaj, że pierwsza kolumna w szukanym zakresie musi być posortowana w porządku rosnącym.
- Na koniec pamiętaj o znaczeniu czwartego argumentu. Użyj wartości PRAWDZIWY KOD (PRAWDA) lub FAŁSZYWY (FAŁSZ) celowo, a pozbędziesz się wielu bólów głowy.
W kolejnych artykułach naszego samouczka funkcji VPR w Excelu poznamy bardziej zaawansowane przykłady, takie jak wykonywanie różnych obliczeń przy użyciu VPR, wyodrębnianie wartości z wielu kolumn i nie tylko. Dziękuję za przeczytanie tego samouczka i mam nadzieję do zobaczenia w przyszłym tygodniu!