Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel

Czy kiedykolwiek miałeś duży arkusz kalkulacyjny z danymi w programie Excel i potrzebujesz łatwego sposobu na filtrowanie i wyodrębnianie z niego określonych informacji? Jeśli nauczysz się korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel, możesz wykonać to wyszukiwanie za pomocą jednej, potężnej funkcji programu Excel.

Funkcja WYSZUKAJ.PIONOWO w Excelu przeraża wiele osób, ponieważ ma wiele parametrów i istnieje wiele sposobów jej użycia. W tym artykule poznasz wszystkie sposoby korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel i dlaczego ta funkcja jest tak potężna.

Parametry WYSZUKAJ.PIONOWO w programie Excel

Gdy zaczniesz pisać = WYSZUKAJ.PIONOWO( w dowolnej komórce w programie Excel, zobaczysz wyskakujące okienko pokazujące wszystkie dostępne parametry funkcji.

Zbadajmy każdy z tych parametrów i ich znaczenie.

• lookup_value : wartość, której szukasz z arkusza kalkulacyjnego
• table_array : zakres komórek w arkuszu, który chcesz przeszukać
• col_index_num : kolumna, z której chcesz pobrać wynik
• [range_lookup] : tryb dopasowania (TRUE = przybliżony, FALSE = dokładny)

Te cztery parametry umożliwiają przeprowadzanie wielu różnych, przydatnych wyszukiwań danych w bardzo dużych zbiorach danych.

Prosty przykład VLOOKUP Excel

WYSZUKAJ.PIONOWO nie jest jedną z podstawowych funkcji programu Excel, których możesz się nauczyć, więc spójrzmy na prosty przykład, aby rozpocząć.

W poniższym przykładzie użyjemy dużego arkusza kalkulacyjnego wyników SAT dla szkół w Stanach Zjednoczonych. Ten arkusz kalkulacyjny zawiera ponad 450 szkół wraz z indywidualnymi wynikami SAT z czytania, matematyki i pisania. Zapraszam do pobrania, aby śledzić dalej. Istnieje połączenie zewnętrzne, które pobiera dane, więc podczas otwierania pliku otrzymasz ostrzeżenie, ale jest to bezpieczne.

Przeszukiwanie tak dużego zbioru danych w celu znalezienia szkoły, którą jesteś zainteresowany, byłoby bardzo czasochłonne.

Zamiast tego możesz utworzyć prosty formularz w pustych komórkach z boku tabeli. Aby przeprowadzić to wyszukiwanie, po prostu utwórz jedno pole na Szkołę i trzy dodatkowe pola na wyniki z czytania, matematyki i pisania.

Następnie musisz użyć funkcji WYSZUKAJ.PIONOWO w programie Excel, aby te trzy pola działały. W polu Czytanie utwórz funkcję WYSZUKAJ.PIONOWO w następujący sposób:

1. Wpisz =WYSZUKAJ.PIONOWO(
2. Wybierz pole Szkoła, które w tym przykładzie to I2 . Wpisz przecinek.
3. Zaznacz cały zakres komórek zawierających dane, które chcesz wyszukać. Wpisz przecinek.

Po wybraniu zakresu możesz zacząć od kolumny, której używasz do wyszukiwania (w tym przypadku kolumny nazwy szkoły), a następnie zaznaczyć wszystkie inne kolumny i wiersze zawierające dane.

Uwaga : Funkcja WYSZUKAJ.PIONOWO w programie Excel może przeszukiwać tylko komórki po prawej stronie kolumny wyszukiwania. W tym przykładzie kolumna nazwy szkoły musi znajdować się na lewo od szukanych danych.

4. Następnie, aby pobrać wynik Reading, musisz wybrać trzecią kolumnę z ostatniej wybranej kolumny. Więc wpisz 3 , a następnie wpisz kolejny przecinek.
5. Na koniec wpisz FALSE dla dokładnego dopasowania i zamknij funkcję za pomocą a ) .

Twoja ostatnia funkcja WYSZUKAJ.PIONOWO powinna wyglądać mniej więcej tak:

 =WYSZUKAJ.PIONOWO(I2,B2:G461,3;FAŁSZ)

Gdy po raz pierwszy naciśniesz Enter i zakończysz funkcję, zauważysz, że pole Czytanie będzie zawierać #N/A .

Dzieje się tak, ponieważ pole Szkoła jest puste i funkcja WYSZUKAJ.PIONOWO nie ma nic do znalezienia. Jeśli jednak wpiszesz nazwę jakiejkolwiek szkoły średniej, którą chcesz wyszukać, zobaczysz prawidłowe wyniki z tego wiersza dla wyniku w czytaniu.

Jak radzić sobie z funkcją WYSZUKAJ.PIONOWO, uwzględniającą wielkość liter

Możesz zauważyć, że jeśli nie wpiszesz nazwy szkoły w tym samym przypadku, w którym jest ona wymieniona w zbiorze danych, nie zobaczysz żadnych wyników.

Dzieje się tak, ponieważ funkcja WYSZUKAJ.PIONOWO rozróżnia wielkość liter. Może to być denerwujące, zwłaszcza w przypadku bardzo dużego zbioru danych, w którym kolumna, którą przeszukujesz, jest niezgodna z kapitalizacją.

Aby obejść ten problem, możesz zmusić to, czego szukasz, do zmiany na małe litery przed wyszukaniem wyników. Aby to zrobić, utwórz nową kolumnę obok kolumny, której szukasz. Wpisz funkcję:

 =PRZYC(DOL(B2))

Spowoduje to zmniejszenie litery nazwy szkoły i usunięcie wszelkich zbędnych znaków (spacji), które mogą znajdować się po lewej lub prawej stronie nazwy.

Przytrzymaj klawisz Shift i umieść kursor myszy w prawym dolnym rogu pierwszej komórki, aż zmieni się w dwie poziome linie. Kliknij dwukrotnie myszą, aby automatycznie wypełnić całą kolumnę.

Wreszcie, ponieważ WYSZUKAJ.PIONOWO spróbuje użyć formuły zamiast tekstu w tych komórkach, musisz przekonwertować je wszystkie tylko na wartości. Aby to zrobić, skopiuj całą kolumnę, kliknij prawym przyciskiem myszy pierwszą komórkę i wklej tylko wartości.

Teraz, gdy wszystkie dane są wyczyszczone w tej nowej kolumnie, nieznacznie zmodyfikuj funkcję WYSZUKAJ.PIONOWO w programie Excel, aby używać tej nowej kolumny zamiast poprzedniej, rozpoczynając zakres wyszukiwania od C2 zamiast B2.

 =WYSZUKAJ.PIONOWO(I2,C2:G461,3;FAŁSZ)

Teraz zauważysz, że jeśli zawsze będziesz pisać małymi literami, zawsze uzyskasz dobry wynik wyszukiwania.

Jest to przydatna wskazówka programu Excel, która pozwala przezwyciężyć fakt, że funkcja WYSZUKAJ.PIONOWO rozróżnia wielkość liter.

WYSZUKAJ.PIONOWO Przybliżone dopasowanie

Chociaż przykład dopasowania dokładnego WYSZUKAJ opisany w pierwszej części tego artykułu jest dość prosty, dopasowanie przybliżone jest nieco bardziej złożone.

Dopasowanie przybliżone najlepiej nadaje się do przeszukiwania zakresów liczb. Aby to zrobić poprawnie, zakres wyszukiwania musi być odpowiednio posortowany. Najlepszym tego przykładem jest funkcja WYSZUKAJ.PIONOWO służąca do wyszukiwania klasy literowej odpowiadającej klasie liczbowej.

Jeśli nauczyciel ma długą listę ocen z pracy domowej uczniów z całego roku z końcową uśrednioną kolumną, byłoby miło, gdyby ocena literowa odpowiadająca tej końcowej ocenie pojawiła się automatycznie.

Jest to możliwe dzięki funkcji WYSZUKAJ.PIONOWO. Wszystko, co jest wymagane, to tabela przeglądowa po prawej stronie, która zawiera odpowiednią ocenę literową dla każdego zakresu liczbowego.

Teraz, korzystając z funkcji WYSZUKAJ.PIONOWO i przybliżonego dopasowania, możesz znaleźć odpowiednią ocenę liter odpowiadającą prawidłowemu zakresowi liczbowemu.

W tej funkcji WYSZUKAJ.PIONOWO:

• lookup_value : F2, końcowa uśredniona ocena
• table_array : I2:J8, zakres wyszukiwania klasy liter
• index_column : 2, druga kolumna w tabeli odnośników
• [range_lookup] : TRUE, przybliżone dopasowanie

Po zakończeniu funkcji WYSZUKAJ.PIONOWO w G2 i naciśnięciu klawisza Enter, możesz wypełnić pozostałe komórki, stosując to samo podejście opisane w ostatniej sekcji. Zobaczysz, że wszystkie oceny literowe są poprawnie wypełnione.

Zwróć uwagę, że funkcja WYSZUKAJ.PIONOWO w programie Excel wyszukuje od dolnego końca zakresu ocen z przypisanym wynikiem literowym do górnej części zakresu następnej litery.

Tak więc „C” musi być literą przypisaną do dolnego zakresu (75), a B jest przypisane do dolnego (minimum) własnego zakresu liter. WYSZUKAJ.PIONOWO „znajdzie” wynik dla 60 (D) jako najbliższą przybliżoną wartość dla wartości od 60 do 75.

WYSZUKAJ.PIONOWO w Excelu to bardzo potężna funkcja, która jest dostępna od dawna. Przydaje się również do znajdowania pasujących wartości w dowolnym miejscu skoroszytu programu Excel.

Pamiętaj jednak, że użytkownicy Microsoft, którzy mają miesięczną subskrypcję Office 365, mają teraz dostęp do nowszej funkcji XLOOKUP. Ta funkcja ma więcej parametrów i dodatkową elastyczność. Użytkownicy z półroczną subskrypcją będą musieli poczekać na wprowadzenie aktualizacji w lipcu 2020 r.