Funkcja WYSZUKAJ.PIONOWO - wprowadzenie
Funkcja WYSZUKAJ.PIONOWO jest punktem odniesienia.
Wiesz coś w programie Excel, jeśli wiesz, jak korzystać z funkcji WYSZUKAJ.PIONOWO.
Jeśli nie, lepiej nie wymieniaj Excela jako jednego z twoich mocnych obszarów w swoim CV.
Brałem udział w rozmowach panelowych, gdzie jak tylko kandydat wspomniał o Excelu jako swojej specjalizacji, pierwszą rzeczą, o którą pytano, było – rozumiesz – funkcja WYSZUKAJ.PIONOWO.
Teraz, gdy wiemy, jak ważna jest ta funkcja Excela, warto ją całkowicie odnieść, aby móc z dumą powiedzieć: „W Excelu wiem kilka rzeczy”.
To będzie ogromny samouczek VLOOKUP (według moich standardów).
Omówię wszystko, co trzeba o tym wiedzieć, a następnie pokażę przydatne i praktyczne przykłady WYSZUKAJ.PIONOWO.
Więc zapnij pasy.
Czas na start.
Kiedy używać funkcji WYSZUKAJ.PIONOWO w programie Excel?
Funkcja WYSZUKAJ.PIONOWO najlepiej nadaje się do sytuacji, gdy szukasz pasującego punktu danych w kolumnie, a po znalezieniu pasującego punktu danych przechodzisz w prawo w tym wierszu i pobierasz wartość z komórki, która jest określoną liczbą kolumny po prawej stronie.
Weźmy tutaj prosty przykład, aby zrozumieć, kiedy używać funkcji Vlookup w programie Excel.
Pamiętam, kiedy lista wyników egzaminu była wyrzucona i wklejona na tablicy ogłoszeń i wszyscy szaleli, szukając swoich nazwisk i wyniku (przynajmniej tak się zdarzało, kiedy byłem w szkole).
Oto jak to działało:
- Podchodzisz do tablicy ogłoszeń i zaczynasz szukać swojego nazwiska lub numeru wpisu (przesuwając palcem od góry do dołu na liście).
- Jak tylko zauważysz swoje imię, przesuwasz oczy na prawo od nazwiska/numeru rejestracji, aby zobaczyć swoje wyniki.
I to jest dokładnie to, co robi dla Ciebie funkcja WYSZUKAJ.PIONOWO w programie Excel (możesz użyć tego przykładu w następnym wywiadzie).
Funkcja WYSZUKAJ.PIONOWO szuka określonej wartości w kolumnie (w powyższym przykładzie było to twoje imię) i gdy znajdzie określone dopasowanie, zwraca wartość w tym samym wierszu (uzyskane znaki).
Składnia
= WYSZUKAJ.PIONOWO(wyszukaj_wartość; tablica_tabeli; nr_indeksu_kolumn; [przeszukaj_zakres])
Argumenty wejściowe
- szukana_wartość - jest to wartość wyszukiwania, którą próbujesz znaleźć w skrajnej lewej kolumnie tabeli. Może to być wartość, odwołanie do komórki lub ciąg tekstowy. W przykładzie arkusza wyników będzie to twoje imię.
- tablica_tabeli - jest to tablica tabeli, w której szukasz wartości. Może to być odwołanie do zakresu komórek lub nazwanego zakresu. W przykładzie arkusza wyników byłaby to cała tabela zawierająca wyniki dla wszystkich dla każdego przedmiotu
- col_index - jest to numer indeksu kolumny, z którego chcesz pobrać pasującą wartość. W przykładzie arkusza wyników, jeśli chcesz uzyskać wyniki z matematyki (która jest pierwszą kolumną w tabeli zawierającej wyniki), spójrz w kolumnie 1. Jeśli chcesz uzyskać wyniki z fizyki, spójrz w kolumnie 2.
- [Zakres wyszukiwania] - tutaj określasz, czy chcesz uzyskać dokładne lub przybliżone dopasowanie. Jeśli zostanie pominięty, domyślnie ma wartość TRUE - przybliżone dopasowanie (patrz dodatkowe uwagi poniżej).
Dodatkowe uwagi (nudne, ale ważne, aby wiedzieć)
- Dopasowanie może być dokładne (FAŁSZ lub 0 w range_lookup) lub przybliżone (PRAWDA lub 1).
- Przy wyszukiwaniu przybliżonym upewnij się, że lista jest posortowana w porządku rosnącym (od góry do dołu), w przeciwnym razie wynik może być niedokładny.
- Gdy range_lookup ma wartość TRUE (przybliżone wyszukiwanie), a dane są sortowane w kolejności rosnącej:
- Jeśli funkcja WYSZUKAJ.PIONOWO nie może znaleźć wartości, zwraca największą wartość, która jest mniejsza niż szukana_wartość.
- Zwraca błąd #N/D, jeśli szukana_wartość jest mniejsza niż najmniejsza wartość.
- Jeśli szukana_wartość jest tekstem, można użyć symboli wieloznacznych (patrz przykład poniżej).
Teraz, mając nadzieję, że masz podstawową wiedzę na temat funkcji WYSZUKAJ.PIONOWO, obierzmy tę cebulę i zobaczmy kilka praktycznych przykładów funkcji WYSZUKAJ.PIONOWO.
10 przykładów funkcji WYSZUKAJ.PIONOWO w Excelu (podstawowe i zaawansowane)
Oto 10 przydatnych przykładów korzystania z funkcji Excel Vlookup, które pokażą Ci, jak używać go w codziennej pracy.
Przykład 1 - Znalezienie wyniku matematycznego Brada
W poniższym przykładzie WYSZUKAJ.PIONOWO mam listę z nazwiskami uczniów w skrajnej lewej kolumnie i ocenami z różnych przedmiotów w kolumnach od B do E.
Teraz zabierzmy się do pracy i użyj funkcji WYSZUKAJ.PIONOWO do tego, co robi najlepiej. Z powyższych danych muszę wiedzieć, ile Brad zdobył w matematyce.
Z powyższych danych muszę wiedzieć, ile Brad zdobył w matematyce.
Oto formuła WYSZUKAJ.PIONOWO, która zwróci wynik matematyczny Brada:
=WYSZUKAJ.PIONOWO("Brad",$A$3:$E$10,2,0)
Powyższa formuła ma cztery argumenty:
- "Ćwiek: - to jest wartość wyszukiwania.
- $ 3 $: $ 10 $ - to jest zakres komórek, w których szukamy. Pamiętaj, że Excel szuka wartości wyszukiwania w skrajnej lewej kolumnie. W tym przykładzie szukałoby nazwy Brad w A3:A10 (która jest skrajną lewą kolumną określonej tablicy).
- 2 - Gdy funkcja wykryje nazwisko Brada, przejdzie do drugiej kolumny tablicy i zwróci wartość w tym samym wierszu, co Brad. Wartość 2 tutaj wskazuje, że szukamy wyniku z drugiej kolumny określonej tablicy.
- 0 - nakazuje funkcji WYSZUKAJ.PIONOWO szukanie tylko dokładnych dopasowań.
Oto jak działa formuła WYSZUKAJ.PIONOWO w powyższym przykładzie.
Najpierw szuka wartości Brad w skrajnej lewej kolumnie. Przechodzi od góry do dołu i znajduje wartość w komórce A6.
Gdy tylko znajdzie wartość, przechodzi w prawo w drugiej kolumnie i pobiera z niej wartość.
Możesz użyć tej samej konstrukcji formuły, aby uzyskać czyjeś oceny z dowolnego z przedmiotów.
Na przykład, aby znaleźć oceny Marii z chemii, użyj następującej formuły WYSZUKAJ.PIONOWO:
=WYSZUKAJ.PIONOWO("Maria",$A$3:$E$10,4,0)
W powyższym przykładzie wartość wyszukiwania (nazwisko ucznia) jest wpisana w cudzysłów. Możesz także użyć odwołania do komórki, które zawiera wartość wyszukiwania.
Zaletą korzystania z odwołania do komórki jest to, że sprawia, że formuła jest dynamiczna.
Na przykład, jeśli masz komórkę z nazwiskiem ucznia i pobierasz wynik z matematyki, wynik zostanie automatycznie zaktualizowany po zmianie nazwiska ucznia (jak pokazano poniżej):
Jeśli wprowadzisz wartość wyszukiwania, która nie znajduje się w skrajnej lewej kolumnie, zwróci błąd #N/D.
Przykład 2 - Dwukierunkowe wyszukiwanie
W powyższym przykładzie 1 na stałe zakodowaliśmy wartość kolumny. W związku z tym formuła zawsze zwraca wynik dla matematyki, ponieważ użyliśmy 2 jako numeru indeksu kolumny.
Ale co, jeśli chcesz, aby zarówno wartość WYSZUKAJ.PIONOWO, jak i numer indeksu kolumny były dynamiczne. Na przykład, jak pokazano poniżej, możesz zmienić imię ucznia lub nazwę przedmiotu, a formuła WYSZUKAJ.PIONOWO pobiera poprawny wynik. To jest przykład dwukierunkowej formuły WYSZUKAJ.PIONOWO.
To jest przykład dwukierunkowej funkcji WYSZUKAJ.PIONOWO.
Aby utworzyć tę dwukierunkową formułę wyszukiwania, musisz również uczynić kolumnę dynamiczną. Kiedy więc użytkownik zmieni temat, formuła automatycznie wybiera odpowiednią kolumnę (2 w przypadku matematyki, 3 w przypadku fizyki, i tak dalej…).
Aby to zrobić, musisz użyć funkcji PODAJ.POZYCJĘ jako argumentu kolumny.
Oto formuła WYSZUKAJ.PIONOWO, która to zrobi:
=WYSZUKAJ.PIONOWO(G4,$A$3:$E$10,DOPASUJ(H3,$A$2:$E$2,0),0)
Powyższa formuła używa MATCH (H3, $ A $ 2: $ E $ 2, 0) jako numeru kolumny. Funkcja PODAJ.POZYCJĘ przyjmuje nazwę podmiotu jako wartość wyszukiwania (w H3) i zwraca jego pozycję w A2:E2. W związku z tym, jeśli użyjesz Math, zwróci 2, ponieważ Math znajduje się w B2 (która jest drugą komórką w określonym zakresie tablicy).
Przykład 3 - Używanie list rozwijanych jako wartości wyszukiwania
W powyższym przykładzie musimy ręcznie wprowadzić dane. Może to być czasochłonne i podatne na błędy, zwłaszcza jeśli masz ogromną listę wartości wyszukiwania.
Dobrym pomysłem w takich przypadkach jest utworzenie listy rozwijanej wartości wyszukiwania (w tym przypadku mogą to być nazwiska uczniów i przedmioty), a następnie po prostu wybierz z listy.
Na podstawie wyboru formuła automatycznie zaktualizuje wynik.
Coś, jak pokazano poniżej:
To sprawia, że jest to dobry komponent pulpitu nawigacyjnego, ponieważ możesz mieć ogromny zestaw danych z setkami uczniów na zapleczu, ale użytkownik końcowy (powiedzmy, że nauczyciel) może szybko uzyskać oceny ucznia z danego przedmiotu, po prostu dokonując wyborów z menu rozwijane.
Jak to zrobić:
Formuła WYSZUKAJ.PIONOWO użyta w tym przypadku jest taka sama jak w przykładzie 2.
= WYSZUKAJ.PIONOWO(G4,$A$3:$E$10,DOPASUJ(H3,$A$2:$E$2,0),0)
Wartości wyszukiwania zostały przekonwertowane na listy rozwijane.
Oto kroki, aby utworzyć listę rozwijaną:
- Wybierz komórkę, w której chcesz wyświetlić listę rozwijaną. W tym przykładzie w G4 chcemy nazwiska uczniów.
- Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
- W oknie dialogowym sprawdzania poprawności danych na karcie ustawień wybierz opcję Lista z listy rozwijanej Zezwalaj.
- W źródle wybierz $ A $ 3: $ A $ 10
- Kliknij OK.
Teraz będziesz mieć listę rozwijaną w komórce G4. Podobnie możesz stworzyć jeden w H3 dla tematów.
Przykład 4 - Wyszukiwanie trójstronne
Co to jest wyszukiwanie trójstronne?
W przykładzie 2 użyliśmy jednej tabeli przeglądowej z wynikami uczniów z różnych przedmiotów. Jest to przykład wyszukiwania dwukierunkowego, ponieważ do pobrania wyniku używamy dwóch zmiennych (nazwisko studenta i nazwisko podmiotu).
Załóżmy teraz, że za rok uczeń ma trzy różne poziomy egzaminów, test jednostkowy, egzamin śródsemestralny i egzamin końcowy (to właśnie miałem, gdy byłem studentem).
Wyszukiwanie trójstronne to możliwość uzyskania ocen ucznia z określonego przedmiotu z określonego poziomu egzaminu.
Coś, jak pokazano poniżej:
W powyższym przykładzie funkcja WYSZUKAJ.PIONOWO może wyszukiwać w trzech różnych tabelach (test jednostkowy, egzamin śródokresowy i egzamin końcowy) i zwraca wynik dla określonego ucznia z określonego przedmiotu.
Oto wzór użyty w komórce H4:
=WYSZUKAJ.PIONOWO(G4,WYBIERZ(JEŻELI(H2="Test jednostkowy",1,JEŻELI(H2="Średnia",2,3)),$A$3:$E$7,$A$11:$E$15,$A 19 $: $ E $ 23), MECZ (H3, $ A $ 2: $ E $ 2, 0, 0)
Ta formuła używa funkcji WYBIERZ, aby upewnić się, że odwołuje się do właściwej tabeli. Przeanalizujmy część WYBIERZ formuły:
WYBIERZ(JEŻELI(H2=”Test jednostkowy”,1,JEŻELI(H2=”Średni okres”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23 )
Pierwszym argumentem formuły jest JEŻELI(H2=”Test jednostkowy”,1,JEŻELI(H2=”Midterm”,2,3)), który sprawdza komórkę H2 i widzi, do jakiego poziomu egzaminu się odnosi. Jeśli jest to test jednostkowy, zwraca 3 USD: 7 USD, co ma wyniki testu jednostkowego. Jeśli jest to średnioterminowe, zwraca 11 USD: 15 USD, w przeciwnym razie zwraca 19 USD: 23 USD.
W ten sposób tablica tabeli WYSZUKAJ.PIONOWO jest dynamiczna, a zatem jest przeglądaniem trójstronnym.
Przykład 5 - Pobieranie ostatniej wartości z listy
Możesz utworzyć formułę WYSZUKAJ.PIONOWO, aby uzyskać ostatnią wartość liczbową z listy.
Największa liczba dodatnia, której możesz użyć w programie Excel, to 9.99999999999999E+307. Oznacza to również, że największy numer wyszukiwania w numerze WYSZUKAJ.PIONOWO jest również taki sam.
Nie sądzę, żebyś kiedykolwiek potrzebował obliczeń obejmujących tak dużą liczbę. I to jest dokładnie to, czego możemy użyć, aby uzyskać ostatnią liczbę na liście.
Załóżmy, że masz zbiór danych (w A1:A14) jak pokazano poniżej i chcesz uzyskać ostatni numer na liście.
Oto formuła, której możesz użyć:
= WYSZUKAJ.PIONOWO(9.99999999999999E+307;$A$1:$A$14;PRAWDA)
Zauważ, że powyższa formuła używa przybliżonego dopasowania WYSZUKAJ.PIONOWO (zwróć uwagę na TRUE na końcu formuły, zamiast FALSE lub 0). Pamiętaj też, że lista nie musi być sortowana, aby ta formuła WYSZUKAJ.PIONOWO działała.
Oto jak działa przybliżona funkcja WYSZUKAJ.PIONOWO. Skanuje skrajną lewą kolumnę od góry do dołu.
- Jeśli znajdzie dokładne dopasowanie, zwraca tę wartość.
- Jeśli znajdzie wartość wyższą niż wartość wyszukiwania, zwraca wartość w komórce nad nią.
- Jeśli wartość wyszukiwania jest większa niż wszystkie wartości na liście, zwraca ostatnią wartość.
W powyższym przykładzie działa trzeci scenariusz.
Od 9.99999999999999E+307 to największa liczba, której można użyć w programie Excel, gdy jest używana jako wartość wyszukiwania, zwraca ostatnią liczbę z listy.
W ten sam sposób możesz również użyć go do zwrócenia ostatniego elementu tekstowego z listy. Oto formuła, która może to zrobić:
=WYSZUKAJ.PIONOWO("zzz",$A$1:$A$8,1,1PRAWDA)
Następuje ta sama logika. Excel przegląda wszystkie nazwy, a ponieważ zzz jest uważane za większe niż jakakolwiek nazwa/tekst rozpoczynający się od alfabetu przed zzz, zwróci ostatni element z listy.
Przykład 6 - Częściowe wyszukiwanie za pomocą symboli wieloznacznych i WYSZUKAJ.PIONOWO
Znaki wieloznaczne Excela mogą być bardzo pomocne w wielu sytuacjach.
To ta magiczna mikstura daje supermoce twoim formułom.
Częściowe wyszukiwanie jest potrzebne, gdy musisz wyszukać wartość na liście, a nie ma dokładnego dopasowania.
Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz wyszukać firmę ABC na liście, ale lista zawiera ABC Ltd zamiast ABC.
Nie możesz użyć ABC jako wartości wyszukiwania, ponieważ nie ma dokładnego dopasowania w kolumnie A. Dopasowanie przybliżone również prowadzi do błędnych wyników i wymaga posortowania listy w porządku rosnącym.
Możesz jednak użyć znaku wieloznacznego w funkcji WYSZUKAJ.PIONOWO, aby uzyskać dopasowanie.
Wprowadź następującą formułę w komórce D2 i przeciągnij ją do innych komórek:
=WYSZUKAJ.PIONOWO("*"&C2&"*",$A$2:$A$8,1,FAŁSZ)
Jak działa ta formuła?
W powyższym wzorze zamiast używać wartości wyszukiwania w obecnej postaci, jest ona otoczona po obu stronach gwiazdką (*) - „*”&C2&”*”
Gwiazdka to znak wieloznaczny w programie Excel i może reprezentować dowolną liczbę znaków.
Użycie gwiazdki po obu stronach wartości wyszukiwania informuje program Excel, że musi wyszukać dowolny tekst zawierający słowo w C2. Może mieć dowolną liczbę znaków przed lub po tekście w C2.
Na przykład komórka C2 ma ABC, więc funkcja WYSZUKAJ.PIONOWO przeszukuje nazwy w A2:A8 i wyszukuje ABC. Znajduje dopasowanie w komórce A2, ponieważ zawiera ABC w ABC Ltd. Nie ma znaczenia, czy po lewej lub prawej stronie ABC znajdują się jakieś znaki. Dopóki ciąg tekstowy nie zawiera ABC, będzie uważany za dopasowanie.
Uwaga: Funkcja WYSZUKAJ.PIONOWO zawsze zwraca pierwszą pasującą wartość i przestaje szukać dalej. Więc jeśli masz ABC Sp. z o.o., i ABC Corporation na liście, zwróci pierwszy i zignoruje resztę.
Przykład 7 - WYSZUKAJ.PIONOWO Zwraca błąd pomimo dopasowania wartości wyszukiwania
Może doprowadzić Cię do szału, gdy zobaczysz, że istnieje pasująca wartość wyszukiwania, a funkcja WYSZUKAJ.PIONOWO zwraca błąd.
Na przykład w poniższym przypadku występuje dopasowanie (Matt), ale funkcja WYSZUKAJ.PIONOWO nadal zwraca błąd.
Teraz, podczas gdy widzimy, że jest dopasowanie, gołym okiem nie możemy zobaczyć, że mogą istnieć przestrzenie prowadzące lub końcowe. Jeśli masz te dodatkowe spacje przed, po lub pomiędzy wartościami wyszukiwania, NIE JEST to dokładne dopasowanie.
Dzieje się tak często, gdy importujesz dane z bazy danych lub otrzymujesz je od kogoś innego. Te wiodące/końcowe przestrzenie mają tendencję do wkradania się.
Rozwiązaniem jest tutaj funkcja TRIM. Usuwa wszelkie początkowe lub końcowe spacje lub dodatkowe spacje między słowami.
Oto formuła, która zapewni Ci właściwy wynik.
=WYSZUKAJ.PIONOWO("Mat", PRZYTRZYMAJ($A$2:$A$9);1,0)
Ponieważ jest to formuła tablicowa, użyj klawiszy Control + Shift + Enter zamiast samego Enter.
Innym sposobem może być najpierw potraktowanie tablicy wyszukiwania za pomocą funkcji TRIM, aby upewnić się, że wszystkie dodatkowe spacje zniknęły, a następnie jak zwykle użyj funkcji WYSZUKAJ.PIONOWO.
Przykład 8 - Wykonywanie wyszukiwania z rozróżnianiem wielkości liter
Domyślnie w wartości wyszukiwania w funkcji WYSZUKAJ.PIONOWO nie jest rozróżniana wielkość liter. Na przykład, jeśli wartość wyszukiwania to MATT, mat lub Matt, wszystko jest takie samo dla funkcji WYSZUKAJ.PIONOWO. Zwróci pierwszą pasującą wartość niezależnie od wielkości liter.
Ale jeśli chcesz przeprowadzić wyszukiwanie z uwzględnieniem wielkości liter, musisz użyć funkcji DOKŁADNIE wraz z funkcją WYSZUKAJ.PIONOWO.
Oto przykład:
Jak widać, istnieją trzy komórki o tej samej nazwie (w A2, A4 i A5), ale z inną wielkością liter. Po prawej mamy trzy nazwiska (Matt, MATT i matt) wraz z ich wynikami w matematyce.
Teraz funkcja WYSZUKAJ.PIONOWO nie jest przystosowana do obsługi wartości wyszukiwania z rozróżnianiem wielkości liter. W powyższym przykładzie zawsze zwróciłby 38, co jest wynikiem dla Matta w A2.
Aby rozróżniać wielkość liter, musimy użyć kolumny pomocniczej (jak pokazano poniżej):
Aby uzyskać wartości w kolumnie pomocniczej, użyj funkcji =ROW(). Po prostu otrzyma numer wiersza w komórce.
Gdy masz kolumnę pomocnika, oto formuła, która da wynik wyszukiwania z uwzględnieniem wielkości liter.
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNE(E2,$A$2:$A$9)*(WIERSZ($A$2:$A$9))),$B$2:$C$9,2,0)
Teraz załammy się i zrozummy, co to robi:
- EXACT (E2, $ A $ 2: $ A $ 9) - Ta część porównałaby wartość wyszukiwania w E2 ze wszystkimi wartościami w A2: A9. Zwraca tablicę PRAWDA/FAŁSZ, gdzie PRAWDA jest zwracana w przypadku dokładnego dopasowania. W takim przypadku zwróci następującą tablicę: {PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}.
- EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - Ta część mnoży tablicę TRUE/FALSE przez numer wiersza. Gdziekolwiek jest PRAWDA, podaje numer wiersza , w przeciwnym razie daje 0. W tym przypadku zwróci {2;0;0;0;0;0;0;0}.
- MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) - ta część zwraca maksymalną wartość z tablicy liczb. W takim przypadku zwróci 2 (czyli numer wiersza, w którym występuje dokładne dopasowanie).
- Teraz po prostu używamy tej liczby jako wartości wyszukiwania i używamy tablicy wyszukiwania jako B2:C9
Uwaga: Ponieważ jest to formuła tablicowa, użyj klawiszy Control + Shift + Enter zamiast po prostu wchodzić.
Przykład 9 - Korzystanie z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami
Funkcja WYSZUKAJ.PIONOWO programu Excel w swojej podstawowej formie może szukać jednej wartości wyszukiwania i zwracać odpowiednią wartość z określonego wiersza.
Ale często istnieje potrzeba korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel z wieloma kryteriami.
Załóżmy, że masz dane z nazwiskami uczniów, typem egzaminu i wynikiem matematycznym (jak pokazano poniżej):
Korzystanie z funkcji WYSZUKAJ.PIONOWO w celu uzyskania wyniku matematycznego dla każdego ucznia na poszczególnych poziomach egzaminu może być wyzwaniem.
Na przykład, jeśli spróbujesz użyć WYSZUKAJ.PIONOWO z Mattem jako wartością wyszukiwania, zawsze zwróci 91, co jest wynikiem pierwszego wystąpienia Matta na liście. Aby uzyskać wynik dla Matta dla każdego typu egzaminu (test jednostkowy, semestralny i końcowy), musisz utworzyć unikalną wartość wyszukiwania.
Można to zrobić za pomocą kolumny pomocniczej. Pierwszym krokiem jest wstawienie kolumny pomocniczej po lewej stronie wyników.
Teraz, aby utworzyć unikalny kwalifikator dla każdego wystąpienia nazwy, użyj następującej formuły w C2: =A2&”|”&B2
Skopiuj tę formułę do wszystkich komórek w kolumnie pomocniczej. Spowoduje to utworzenie unikalnych wartości wyszukiwania dla każdego wystąpienia nazwy (jak pokazano poniżej):
Teraz, podczas gdy powtarzały się nazwiska, nie ma powtórzeń, gdy imię jest połączone z poziomem egzaminu.
Ułatwia to, ponieważ teraz możesz użyć wartości kolumny pomocniczej jako wartości wyszukiwania.
Oto wzór, który da ci wynik w G3: I8.
=WYSZUKAJ.PIONOWO($F3&"|"&G$2,$C$2:$D$19,2,0)
Tutaj połączyliśmy nazwisko ucznia i poziom egzaminu, aby uzyskać wartość wyszukiwania, i używamy tej wartości wyszukiwania i sprawdzamy ją w kolumnie pomocniczej, aby uzyskać pasujący rekord.
Uwaga: W powyższym przykładzie użyliśmy | jako separator podczas łączenia tekstu w kolumnie pomocniczej. W niektórych wyjątkowo rzadkich (ale możliwych) warunkach możesz mieć dwa kryteria, które są różne, ale w połączeniu dają ten sam wynik. Oto przykład:
Zauważ, że chociaż A2 i A3 są różne, a B2 i B3 są różne, kombinacje są takie same. Ale jeśli użyjesz separatora, nawet kombinacja będzie inna (D2 i D3).
Oto samouczek dotyczący korzystania z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami bez używania kolumn pomocniczych. Możesz również obejrzeć mój samouczek wideo tutaj.
Przykład 10 - Obsługa błędów podczas korzystania z funkcji WYSZUKAJ.PIONOWO
Funkcja WYSZUKAJ.PIONOWO programu Excel zwraca błąd, gdy nie może znaleźć określonej wartości wyszukiwania. Możesz nie chcieć, aby brzydka wartość błędu zakłócała estetykę twoich danych na wypadek, gdyby funkcja WYSZUKAJ.PIONOWO nie mogła znaleźć wartości.
Możesz łatwo usunąć wartości błędów o dowolnym znaczeniu, pełnym tekście, takim jak „Niedostępne” lub „Nie znaleziono”.
Na przykład w poniższym przykładzie, gdy próbujesz znaleźć wynik Brada na liście, zwraca błąd, ponieważ nazwiska Brada nie ma na liście.
Aby usunąć ten błąd i zastąpić go czymś znaczącym, otocz funkcję WYSZUKAJ.PIONOWO funkcją JEŻELI.BŁĄD.
Oto wzór:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D2,$A$2:$B$7,2,0);"Nie znaleziono")
Funkcja JEŻELI.BŁĄD sprawdza, czy wartość zwrócona przez pierwszy argument (w tym przypadku funkcja WYSZUKAJ.PIONOWO) jest błędem, czy nie. Jeśli nie jest to błąd, zwraca wartość za pomocą funkcji WYSZUKAJ.PIONOWO, w przeciwnym razie zwraca wartość Nie znaleziono.
Funkcja JEŻELI.BŁĄD jest dostępna w programie Excel 2007 i nowszych. Jeśli używasz wcześniejszych wersji, użyj następującej funkcji:
=JEŻELI(CZY.BŁĄD(WYSZUKAJ.PIONOWO(D2,$A$2:$B$7,2,0));"Nie znaleziono";WYSZUKAJ.PIONOWO(D2,$A$2:$B$7,2,0))
Zobacz także: Jak radzić sobie z błędami WYSZUKAJ.PIONOWO w programie Excel.
To wszystko w tym samouczku WYSZUKAJ.PIONOWO.
Próbowałem omówić główne przykłady użycia funkcji Vlookup w programie Excel. Jeśli chcesz zobaczyć więcej przykładów dodanych do tej listy, daj mi znać w sekcji komentarzy.
Uwaga: starałem się jak najlepiej sprawdzić ten samouczek, ale jeśli znajdziesz jakieś błędy lub błędy ortograficzne, daj mi znać 🙂
Za pomocą Funkcja WYSZUKAJ.PIONOWO w programie Excel - wideo
- Funkcja WYSZUKAJ.POZIOMO Excela.
- Funkcja WYSZUKAJ X Excel
- Funkcja INDEKS Excela.
- Excel ADR.Funkcja.
- Funkcja PODAJ.POZYCJĘ Excela.
- Funkcja przesunięcia Excela.
