Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel | Exceljet

WYSZUKAJ.PIONOWO to funkcja programu Excel służąca do pobierania danych z tabeli zorganizowanej pionowo. Wartości wyszukiwania muszą pojawić się w pierwszej kolumnie tabeli przekazanej do funkcji WYSZUKAJ.PIONOWO. WYSZUKAJ.PIONOWO obsługuje przybliżone i dokładne dopasowanie oraz symbole wieloznaczne (*?) Dla częściowych dopasowań.

V jest dla branży pionowej

Celem funkcji WYSZUKAJ.PIONOWO jest uzyskanie informacji z tabeli zorganizowanej w ten sposób:

Używając numeru zamówienia w kolumnie B jako wartości wyszukiwania, funkcja WYSZUKAJ.PIONOWO może uzyskać identyfikator klienta, kwotę, nazwę i stan dla dowolnego zamówienia. Na przykład, aby uzyskać nazwę klienta dla zamówienia 1004, wzór wygląda następująco:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

W przypadku danych poziomych możesz użyć HLOOKUP, INDEX and MATCH lub XLOOKUP.

WYSZUKAJ.PIONOWO jest oparte na numerach kolumn

Korzystając z funkcji WYSZUKAJ.PIONOWO, wyobraź sobie, że każda kolumna w tabeli jest numerowana, zaczynając od lewej. Aby uzyskać wartość z określonej kolumny, podaj odpowiednią liczbę jako „indeks kolumny”. Na przykład indeks kolumny do pobrania imienia poniżej to 2:

Nazwisko i adres e-mail można pobrać za pomocą kolumn 3 i 4:

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

WYSZUKAJ.PIONOWO wygląda tylko w prawo

WYSZUKAJ.PIONOWO może wyglądać tylko w prawo. Dane, które chcesz pobrać (wartości wyników), mogą pojawić się w dowolnej kolumnie po prawej stronie wartości wyszukiwania:

Jeśli musisz wyszukać wartości po lewej stronie, zobacz INDEKS i PODAJ.LUB lub XLOOKUP.

Dopasowanie ścisłe i przybliżone

WYSZUKAJ.PIONOWO ma dwa tryby dopasowywania, dokładne i przybliżone. Nazwa argumentu kontrolującego dopasowywanie to „wyszukiwanie_zakresu”. To myląca nazwa, ponieważ wydaje się, że ma coś wspólnego z zakresami komórek, takimi jak A1: A10. W rzeczywistości słowo „zakres” w tym przypadku odnosi się do „zakresu wartości” – gdy wyszukiwanie_zakresu ma wartość PRAWDA, WYSZUKAJ.PIONOWO dopasuje zakres wartości, a nie dokładną wartość. Dobrym tego przykładem jest użycie funkcji WYSZUKAJ.PIONOWO do obliczania ocen.

Ważne jest, aby zrozumieć, że wyszukiwanie_zakresu ma domyślnie wartość PRAWDA, co oznacza, że funkcja WYSZUKAJ.PIONOWO domyślnie używa przybliżonego dopasowania, co może być niebezpieczne. Ustaw parametr range_lookup na FALSE, aby wymusić dokładne dopasowanie:

Uwaga: możesz również podać zero (0) zamiast FALSE, aby uzyskać dokładne dopasowanie.

Dokładne dopasowanie

W większości przypadków prawdopodobnie będziesz chciał użyć funkcji WYSZUKAJ.PIONOWO w trybie dopasowania ścisłego. Ma to sens, gdy masz unikalny klucz do użycia jako wartość wyszukiwania, na przykład tytuł filmu w tych danych:

Wzór w H6 do znalezienia roku, na podstawie dokładnego dopasowania tytułu filmu, to:

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

Przybliżone dopasowanie

W przypadkach, gdy chcesz uzyskać najlepsze dopasowanie, niekoniecznie dokładne, możesz użyć trybu przybliżonego. Na przykład poniżej chcemy sprawdzić stawkę prowizji w tabeli G5: H10. Wartości wyszukiwania pochodzą z kolumny C. W tym przykładzie musimy użyć funkcji WYSZUKAJ.PIONOWO w trybie przybliżonego dopasowania, ponieważ w większości przypadków dokładne dopasowanie nigdy nie zostanie znalezione. Formuła WYSZUKAJ.PIONOWO w D5 jest skonfigurowana do wykonywania przybliżonego dopasowania przez ustawienie ostatniego argumentu na PRAWDA:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

WYSZUKAJ.PIONOWO przeskanuje wartości w kolumnie G w poszukiwaniu wartości wyszukiwania. Jeśli zostanie znalezione dokładne dopasowanie, funkcja WYSZUKAJ.PIONOWO użyje go. Jeśli nie, funkcja WYSZUKAJ.PIONOWO „cofnie się” i dopasuje poprzedni wiersz.

Uwaga: dane muszą być sortowane w kolejności rosnącej według wartości wyszukiwania, gdy używasz trybu przybliżonego dopasowania z WYSZUKAJ.PIONOWO.

Pierwsze dopasowanie

W przypadku zduplikowanych wartości funkcja WYSZUKAJ.PIONOWO znajdzie pierwsze dopasowanie, gdy tryb dopasowania jest dokładny. Na poniższym ekranie funkcja WYSZUKAJ.PIONOWO jest skonfigurowana, aby znaleźć cenę za kolor „Zielony”. Istnieją trzy wpisy w kolorze zielonym, a WYSZUKAJ.PIONOWO zwraca cenę pierwszego wpisu, 17 USD. Formuła w komórce F5 to:

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

Dopasowanie wieloznaczne

Funkcja WYSZUKAJ.PIONOWO obsługuje symbole wieloznaczne, co umożliwia częściowe dopasowanie wartości wyszukiwania. Na przykład możesz użyć funkcji WYSZUKAJ.PIONOWO, aby pobrać wartości z tabeli po wpisaniu tylko części wartości wyszukiwania. Aby użyć symboli wieloznacznych z WYSZUKAJ.PIONOWO, musisz określić tryb dopasowania ścisłego, podając FALSE lub 0 dla ostatniego argumentu, wyszukiwanie_zakresu. Formuła w H7 pobiera imię „Michael” po wpisaniu „Aya” w komórce H4:

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

Przeczytaj bardziej szczegółowe wyjaśnienie tutaj.

Wyszukiwanie dwukierunkowe

Wewnątrz funkcji WYSZUKAJ.PIONOWO kolumna argument indeksu jest zwykle zakodowany na stałe jako liczba statyczna.Jednak można również utworzyć dynamiczny indeks kolumn, używając funkcji PODAJ.POZYCJĘ, aby zlokalizować prawą kolumnę. Ta technika umożliwia tworzenie dynamicznego wyszukiwania dwukierunkowego, dopasowującego zarówno wiersze, jak i kolumny. Na poniższym ekranie funkcja WYSZUKAJ.PIONOWO jest skonfigurowana do wykonywania wyszukiwania na podstawie nazwy i miesiąca. Wzór w H6 to:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

Aby uzyskać więcej szczegółów, zobacz ten przykład.

Uwaga: Ogólnie rzecz biorąc, INDEKS i PODAJ.POZYCJĘ to bardziej elastyczny sposób przeprowadzania wyszukiwania dwukierunkowego.

Wiele kryteriów

Funkcja WYSZUKAJ.PIONOWO nie obsługuje natywnie wielu kryteriów. Możesz jednak użyć kolumny pomocnika, aby połączyć wiele pól razem i używać tych pól jako wielu kryteriów w funkcji WYSZUKAJ.PIONOWO. W poniższym przykładzie kolumna B jest kolumną pomocniczą, która łączy imiona i nazwiska razem z następującą formułą:

=C5&D5 // helper column

WYSZUKAJ.PIONOWO jest skonfigurowany tak, aby robić to samo, aby utworzyć wartość wyszukiwania. Wzór w H6 to:

=VLOOKUP(H4&H5,B5:E13,4,0)

Aby uzyskać szczegółowe informacje, zobacz ten przykład.

Uwaga: INDEX i MATCH i XLOOKUP to bardziej niezawodne sposoby obsługi wyszukiwań w oparciu o wiele kryteriów.

WYSZUKAJ.PIONOWO i błędy # N / D

Jeśli używasz funkcji WYSZUKAJ.PIONOWO, nieuchronnie napotkasz błąd # N / D. Błąd # N / D oznacza po prostu „nie znaleziono”. Na przykład na poniższym ekranie wartość wyszukiwania „Toy Story 2” nie istnieje w tabeli przeglądowej, a wszystkie trzy formuły funkcji WYSZUKAJ.PIONOWO zwracają # N / A:

Jednym ze sposobów „złapania” błędu NA jest użycie funkcji IFNA w następujący sposób:

Wzór w H6 to:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

Wiadomość można dostosować do własnych potrzeb. Aby nic nie zwrócić (tj. Wyświetlić pusty wynik), gdy WYSZUKAJ.PIONOWO zwraca # N / A, możesz użyć pustego ciągu, takiego jak ten:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

Błąd # N / D jest przydatny, ponieważ informuje, że coś jest nie tak. W praktyce istnieje wiele powodów, dla których możesz zobaczyć ten błąd, w tym:

Wartość wyszukiwania nie istnieje w tabeli
Wartość wyszukiwania jest błędnie wpisana lub zawiera dodatkowa spacja
Tryb dopasowania jest dokładny, ale powinien być przybliżony
Zakres tabeli nie został wprowadzony poprawnie
Kopiujesz funkcję WYSZUKAJ.PIONOWO, a odwołanie do tabeli nie zablokowane

Więcej informacji: WYSZUKAJ.PIONOWO bez błędów # N / D

Więcej informacji o WYSZUKAJ.PIONOWO

Więcej przykładów WYSZUKAJ.PIONOWO
Filmy z WYSZUKAJ.PIONOWO
23 wskazówki dotyczące korzystania z funkcji WYSZUKAJ.PIONOWO

Inne uwagi

Range_lookup określa, czy wartość musi być dokładnie dopasowana, czy nie. Wartość domyślna to TRUE = zezwalaj na dopasowanie niedokładne.
Ustaw wartość argument_zakresu na FALSE, aby wymagać dopasowania ścisłego, i TRUE, aby zezwolić na dopasowanie niedokładne.
Jeśli argument przeszukiwany_zakres ma wartość TRUE ( ustawienie domyślne), niedokładne dopasowanie spowoduje, że funkcja WYSZUKAJ.PIONOWO będzie pasować do najbliższej wartości w tabeli, która jest nadal mniejsza niż wartość.
W przypadku pominięcia przeszukiwanego_zakresu funkcja WYSZUKAJ.PIONOWO zezwoli na dopasowania, ale użyje dokładnego dopasowania, jeśli istnieje.
Jeśli argument przeszukiwany_zakres ma wartość PRAWDA (ustawienie domyślne), upewnij się, że wartości wyszukiwania w pierwszym wierszu tabeli są posortowane w kolejności rosnącej. W przeciwnym razie funkcja WYSZUKAJ.PIONOWO może zwrócić niepoprawną lub nieoczekiwaną wartość.
Jeśli argument przeszukiwany_zakres ma wartość FAŁSZ (wymaga dokładnego dopasowania), wartości w pierwszej kolumnie tabeli nie muszą być sortowane.

Funkcja WYSZUKAJ.PIONOWO w programie Excel