Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Funkcja WYSZUKAJ.PIONOWO w Excelu w programie Excel, aby wyszukać wartość.
Przegląd funkcji WYSZUKAJ.PIONOWO
Funkcja VLOOKUP Vlookup oznacza wyszukiwanie pionowe. Szuka wartości w skrajnej lewej kolumnie tabeli. Następnie zwraca wartość określoną liczbę kolumn na prawo od znalezionej wartości. Jest to to samo co hlookup, z tą różnicą, że wyszukuje wartości w pionie zamiast w poziomie.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
Składnia i argumenty funkcji WYSZUKAJ.PIONOWO:
| 1 | = WYSZUKAJ.PIONOWO(wyszukiwana_wartość,tablica_tabeli,num_indeksu_kolumny,przeszukiwanie_zakresu) |
szukana_wartość - Wartość, którą chcesz wyszukać.
tablica_tabeli - Zakres danych, który zawiera zarówno wartość, którą chcesz wyszukać, jak i wartość, którą ma zwrócić funkcja wglądu. Kolumna zawierająca szukane wartości musi być pierwszą od lewej.
col_index_num - Numer kolumny zakresu danych, z którego chcesz zwrócić wartość.
Zakres wyszukiwania - Prawda czy fałsz. FALSE wyszukuje dokładne dopasowanie. TRUE wyszukuje najbliższe dopasowanie, które jest mniejsze lub równe szukanej_wartości. W przypadku wybrania TRUE kolumna znajdująca się najbardziej po lewej stronie (kolumna wyszukiwania) musi być posortowana rosnąco (od najniższej do najwyższej).
Co to jest funkcja WYSZUKAJ.PIONOWO?
Jako jedna ze starszych funkcji w świecie arkuszy kalkulacyjnych, funkcja WYSZUKAJ.PIONOWO służy do wykonywania Vpionowa Wyszukiwania. Ma kilka ograniczeń, które często są pokonywane przez inne funkcje, takie jak INDEKS/PODZIELANIE. Jednak ma tę zaletę, że jest pojedynczą funkcją, która może sama wykonać wyszukiwanie dokładnego dopasowania. Jest to również jedna z pierwszych funkcji, o których ludzie się uczą.
Podstawowy przykład
Przyjrzyjmy się próbce danych z dziennika ocen. Omówimy kilka przykładów wyodrębniania informacji dla konkretnych uczniów.
Jeśli chcemy dowiedzieć się, w jakiej klasie znajduje się Bob, napisalibyśmy formułę:
| 1 | =WYSZUKAJ.PIONOWO("Robert"; A2:C5; 2; FAŁSZ) |
Ważną rzeczą do zapamiętania jest to, że szukany przedmiot (Bob) musi znajdować się w pierwszej kolumnie naszego zakresu wyszukiwania (A2:C5). Powiedzieliśmy funkcji, że chcemy zwrócić wartość z 2NS kolumna, którą w tym przypadku jest kolumna B. Na koniec wskazaliśmy, że chcemy wykonać an dokładne dopasowanie umieszczając False jako ostatni argument. Tutaj odpowiedzią będzie „Czytanie”.
Porada boczna: Możesz również użyć liczby 0 zamiast False jako ostatniego argumentu, ponieważ mają one tę samą wartość. Niektórzy wolą to, ponieważ pisanie jest szybsze. Po prostu wiedz, że oba są akceptowalne.
Przesunięte dane
Aby dodać wyjaśnienie do naszego pierwszego przykładu, wyszukiwany element nie musi znajdować się w kolumnie A arkusza kalkulacyjnego, tylko w pierwszej kolumnie zakresu wyszukiwania. Użyjmy tego samego zestawu danych:
Teraz znajdźmy ocenę dla klasy Nauka. Nasza formuła będzie
| 1 | =WYSZUKAJ.PIONOWO("Nauka"; B2:C5; 2; FAŁSZ) |
Jest to nadal poprawna formuła, ponieważ pierwsza kolumna naszego zakresu wyszukiwania to kolumna B, w której znajduje się wyszukiwane hasło „Nauka”. Zwracamy wartość z 2NS kolumna zakresu wyszukiwania, którą w tym przypadku jest kolumna C. Odpowiedź brzmi „A-”.
Użycie symboli wieloznacznych
Funkcja WYSZUKAJ.PIONOWO obsługuje użycie symboli wieloznacznych „*” i „?” podczas wyszukiwania. Załóżmy na przykład, że zapomnieliśmy, jak przeliterować imię Franka, i po prostu chcieliśmy wyszukać imię, które zaczyna się na „F”. Moglibyśmy napisać formułę
| 1 | =WYSZUKAJ.PIONOWO("F*"; A2:C5; 2; FAŁSZ) |
Będzie to w stanie znaleźć imię Frank w wierszu 5, a następnie zwrócić wartość z 2NS kolumna względna. W tym przypadku odpowiedzią będzie „Nauka”.
Dopasowanie niedokładne
W większości przypadków będziesz chciał się upewnić, że ostatni argument w funkcji WYSZUKAJ.PIONOWO to Fałsz (lub 0), aby uzyskać dokładne dopasowanie. Jednak jest kilka sytuacji, w których możesz szukać niedokładnego dopasowania. Jeśli masz listę posortowanych danych, możesz również użyć funkcji WYSZUKAJ.PIONOWO, aby zwrócić wynik dla elementu, który jest taki sam lub następny najmniejszy. Jest to często stosowane, gdy mamy do czynienia z rosnącymi zakresami liczb, na przykład w tabeli podatkowej lub premiach prowizyjnych.
Załóżmy, że chcesz znaleźć stawkę podatku dla dochodu wprowadzonego do komórki D2. Wzór w D4 może mieć postać:
| 1 | =WYSZUKAJ.PIONOWO(D2, A2:B6; 2; PRAWDA) |
Różnica w tej formule polega na tym, że nasz ostatni argument to „Prawda”. W naszym konkretnym przykładzie widzimy, że kiedy nasza osoba wprowadzi dochód w wysokości 45 000 USD, będzie miała stawkę podatkową w wysokości 15%.
Notatka: Chociaż zwykle zależy nam na dokładnym dopasowaniu z argumentem False, jeśli zapomnisz podać 4NS argument w funkcji WYSZUKAJ.PIONOWO, wartość domyślna to True. Może to spowodować nieoczekiwane wyniki, zwłaszcza w przypadku wartości tekstowych.
Kolumna dynamiczna
WYSZUKAJ.PIONOWO wymaga podania argumentu mówiącego, z której kolumny chcesz zwrócić wartość, ale może wystąpić sytuacja, gdy nie wiesz, gdzie będzie kolumna, lub chcesz zezwolić użytkownikowi na zmianę kolumny, z której ma powrócić. W takich przypadkach pomocne może być użycie funkcji PODAJ.POZYCJĘ w celu określenia numeru kolumny.
Rozważmy ponownie nasz przykład dziennika ocen, z pewnymi danymi wejściowymi w E2 i E4. Aby uzyskać numer kolumny, moglibyśmy napisać formułę
| 1 | =DOPASUJ(E2, A1:C1, 0) |
To spróbuje znaleźć dokładną pozycję „Stopień” w zakresie A1:C1. Odpowiedź będzie 3. Wiedząc o tym, możemy podłączyć ją do funkcji WYSZUKAJ.PIONOWO i napisać formułę w E6 w następujący sposób:
| 1 | =WYSZUKAJ.PIONOWO(E4, A2:C5; PODAJ.POZYCJĘ(E2, A1:C1, 0); 0) |
Tak więc funkcja PODAJr & D kolumna w zakresie A2:C5. Ogólnie rzecz biorąc, otrzymujemy pożądany wynik „C”. Nasza formuła jest teraz dynamiczna, ponieważ możemy zmienić kolumnę do przeglądania lub nazwę do wyszukania.
Ograniczenia WYSZUKAJ.PIONOWO
Jak wspomniano na początku artykułu, największą wadą funkcji WYSZUKAJ.PIONOWO jest to, że wymaga ona znalezienia wyszukiwanego hasła w skrajnej lewej kolumnie zakresu wyszukiwania. Chociaż istnieje kilka wymyślnych sztuczek, które możesz zrobić, aby to przezwyciężyć, powszechną alternatywą jest użycie indeksu i dopasowania. Ta kombinacja zapewnia większą elastyczność, a czasami może być nawet szybszą kalkulacją.
WYSZUKAJ.PIONOWO w Arkuszach Google
Funkcja WYSZUKAJ.PIONOWO działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
Dodatkowe uwagi
Użyj funkcji WYSZUKAJ.PIONOWO, aby przeprowadzić wyszukiwanie PIONOWE. Funkcja WYSZUKAJ.PIONOWO wyszukuje dokładne dopasowanie (Zakres wyszukiwania = FALSE) lub najbliższe dopasowanie, które jest równe lub mniejsze niż szukana_wartość (Zakres wyszukiwania = TRUE, tylko wartości liczbowe) w pierwszym wierszu table_array. Następnie zwraca odpowiednią wartość, liczbę n wierszy poniżej dopasowania.
Korzystając z funkcji WYSZUKAJ.PIONOWO w celu znalezienia dokładnego dopasowania, najpierw zdefiniuj wartość identyfikującą, którą chcesz wyszukać jako szukana_wartość. Ta wartość identyfikująca może być numerem SSN, identyfikatorem pracownika, nazwiskiem lub innym unikalnym identyfikatorem.
Następnie definiujesz zakres (zwany tablica_tabeli), który zawiera identyfikatory w górnym wierszu i dowolne wartości, które ostatecznie chcesz wyszukać w wierszach poniżej. WAŻNE: Unikalne identyfikatory muszą znajdować się w górnym rzędzie. Jeśli tak nie jest, musisz albo przenieść wiersz na górę, albo użyć funkcji PODAJ./INDEKSU zamiast funkcji WYSZUKAJ.PIONOWO.
Po trzecie, zdefiniuj numer wiersza (row_index) z tablica_tabeli że chcesz wrócić. Pamiętaj, że pierwszy wiersz zawierający unikalne identyfikatory to wiersz 1. Drugi wiersz to wiersz 2 itd.
Na koniec musisz wskazać, czy szukać dokładnego dopasowania (FAŁSZ) lub najbliższego dopasowania (PRAWDA) w Zakres wyszukiwania. Jeśli wybrano opcję dokładnego dopasowania, a dokładne dopasowanie nie zostanie znalezione, zostanie zwrócony błąd (#N/A). Aby formuła zwracała wartość pustą lub „nie znaleziono” lub dowolną inną wartość zamiast wartości błędu (#N/A), użyj funkcji IFERROR z funkcją WYSZUKAJ.PIONOWO.
Aby użyć funkcji WYSZUKAJ.PIONOWO do zwrócenia przybliżonego zestawu dopasowań: Zakres wyszukiwania = PRAWDA. Ta opcja jest dostępna tylko dla wartości liczbowych. Wartości muszą być posortowane w kolejności rosnącej.
Aby wykonać wyszukiwanie poziome, użyj funkcji WYSZUKAJ.POZIOMO.
Przykłady VLOOKUP w VBA
Możesz także użyć funkcji WYSZUKAJ.PIONOWO w VBA. Rodzaj:application.worksheetfunction.vlookup(szukana_wartość,tablica_tabeli,col_index_num,range_lookup)
W przypadku argumentów funkcji (wyszukiwana_wartość itp.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które mają być używane w zamian.
Wróć do listy wszystkich funkcji w Excelu
