Wprowadzenie do funkcji WYSZUKAJ.PIONOWO
Funkcja WYSZUKAJ.PIONOWO w programie Excel jest bez wątpienia najczęściej używaną i omawianą funkcją. W każdej rozmowie kwalifikacyjnej wymagającej umiejętności Excela jest to najczęściej zadawane pytanie, jeśli wiesz, jak korzystać z funkcji WYSZUKAJ.PIONOWO.
Funkcja WYSZUKAJ.PIONOWO należy do kategorii wyszukiwania funkcji programu Excel. Litera V w funkcji WYSZUKAJ.PIONOWO oznacza pion. Funkcja służy do wyszukiwania danych o strukturze pionowej. Do wyszukiwania poziomego dostępna jest funkcja o nazwie WYSZUKAJ.POZIOMO.
Funkcja WYSZUKAJ.PIONOWO w Excelu szuka pierwszego dopasowania podanej wartości, zwanej wartością wyszukiwania, w tabeli danych i zwraca wartość z podanego indeksu kolumny. Dopasowanie może być przybliżone lub dokładne.
Chociaż WYSZUKAJ.PIONOWO jest łatwy w użyciu, ma swoje ograniczenia i tła. Omówimy wszystko, w tym mocne i słabe strony funkcji.
Składnia:
= WYSZUKAJ.PIONOWO(wyszukaj_wartość; tablica_tabeli; numer_indeksu_kolumn; [przeszukaj_zakres])
Co to jest wartość wyszukiwania?
Szukana_wartość: Jest to wartość, której chcesz szukać w tablicy tabeli.
Na przykład, jeśli szukasz rolki nr. 110 w tabeli ucznia to jest 110 to wartość wyszukiwania.
Tablica_tabeli: Tabela, z której chcesz szukać wartości wyszukiwania.
Na przykład, jeśli szukasz 110 w tablicy tabeli B5:E17. Następnie B5:E17 to twoja tablica tabeli.
col_index_number: Numer kolumny w tablicy tabeli, z której chcesz pobrać wyniki.
Na przykład jeśli w tablicy tablicy B5:E17 chcesz uzyskać wartość z kolumny D wtedy indeks Twojej kolumny będzie wynosił 3 (licząc od B do D (B,C,D)).
[Zakres wyszukiwania]: FALSE, jeśli chcesz wyszukać dokładną wartość, TRUE, jeśli chcesz uzyskać przybliżone dopasowanie.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO
Gdy masz uporządkowane dane i chcesz wyszukać dane w tych danych, rozwiązaniem jest funkcja WYSZUKAJ.PIONOWO.
Tryby dopasowywania WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO ma dwa tryby dopasowania, dopasowanie przybliżone i dopasowanie dokładne. Domyślnie funkcja WYSZUKAJ.PIONOWO wykonuje przybliżone dopasowanie. Ale jeśli chcesz zmusić funkcję WYSZUKAJ.PIONOWO do dokładnego dopasowania, możesz to zrobić. W większości przypadków ja i ja też mocno wierzymy, że inni starają się wykonać dokładne dopasowanie za pomocą funkcji WYSZUKAJ.PIONOWO. Nie rozumiem, dlaczego programiści Excela uważają, że użytkownicy woleliby używać głównie dopasowania przybliżonego.
1. WYSZUKAJ.PIONOWO Przybliżone dopasowanie
= WYSZUKAJ.PIONOWO(wyszukiwana_wartość; tablica_tabeli; numer_indeksu_kolumn; 1)
W dopasowaniu przybliżonym WYSZUKAJ.PIONOWO, jeśli wartość nie zostanie znaleziona, wówczas dopasowywana jest ostatnia wartość mniejsza niż wartość wyszukiwania i zwracana jest wartość z podanego indeksu kolumny.
WYSZUKAJ.PIONOWO domyślnie wykonuje przybliżone dopasowanie, jeśli pominiemy zmienną wyszukiwania zakresu. Dopasowanie przybliżone jest przydatne, gdy chcesz przeprowadzić dopasowanie przybliżone i kiedy masz tablicę tabeli posortowaną w porządku rosnącym.
Vlookup szuka wartości i jeśli nie znajdzie wartości w tablicy tabeli, to dopasowuje wartość mniejszą niż wartość w tablicy tabeli. Zrozummy to na przykładzie.
Przykład 1
Mamy tutaj listę studentów. Każdy uczeń uzyskał kilka punktów w teście. Teraz chcę oceniać według ich ocen.
Każdy Uczeń, który uzyskał mniej niż 40 punktów, powinien być oznaczony jako F, uczeń, który uzyskał między 40 a 60 punktów, powinien być oznaczony jako C i tak dalej, jak pokazano na poniższym obrazku.
Napisalibyśmy tę formułę WYSZUKAJ.PIONOWO w E2 i przeciągnęli ją w dół.
=WYSZUKAJ.PIONOWO(D2,$H$2:$I$6,2,PRAWDA)
Jak to działa?
W powyższym przykładzie nasze wartości wyszukiwania znajdują się w kolumnie D, zaczynając od D2. Tablica Array to H2:I6 (należy zauważyć, że jest posortowana w porządku rosnącym i bezwzględnie). Kolumna, z której otrzymujemy dane, to 2. A typ dopasowania zdefiniowaliśmy w przybliżeniu, podając TRUE (nic i 1 będą oznaczać to samo).
Przyjrzyjmy się więc pierwszej wartości wyszukiwania D2, która zawiera 40.
- WYSZUKAJ.PIONOWO szuka 40 w pierwszej kolumnie (H) tablicy tabeli H2:I6.
- Znajduje 40 na drugiej pozycji w komórce H3.
- Teraz przechodzi do drugiej kolumny z H3 do I3 i zwraca D.
W tym przypadku vlookup znalazł dokładne dopasowanie. Zobaczmy następny przypadek.
Drugie wyszukiwanie wartości D3, które zawiera 36.
- WYSZUKAJ.PIONOWO szuka 36 w pierwszej kolumnie tablicy tabeli H2:I6.
- WYSZUKAJ.PIONOWO nie mógł znaleźć 36 nigdzie w pierwszej kolumnie.
- Ponieważ VLOOKUP nie mógł znaleźć 36, dopasowuje go do ostatniej znalezionej wartości, która jest mniejsza niż wartość wyszukiwania.
- Pierwsza wartość, która jest mniejsza niż 36, to 0, dlatego zwraca F, które jest powiązane z 0.
Dzieje się tak w każdym przypadku tutaj. Dla 92 ostatnia wartość, która jest mniejsza niż 92, to 90. Stąd A jest zwracane dla przybliżonego dopasowania.
2. WYSZUKAJ.PIONOWO dokładne dopasowanie
To najczęściej używana forma funkcji WYSZUKAJ.PIONOWO i może być również najbardziej przydatna. Kiedy chcesz szukać dokładnej wartości w pierwszej kolumnie tablicy tabeli, używasz dokładnego dopasowania. Na przykład, jeśli wykonujesz wyszukiwanie z identyfikatora, najbardziej chciałbyś uzyskać dokładne dopasowanie zamiast wartości mniejszej niż ta wartość.
Aby wymusić WYSZUKAJ.PIONOWO do dokładnego dopasowania w programie Excel, przekazujemy 0 lub FALSE jako parametr range_lookup.
= WYSZUKAJ.PIONOWO(wyszukaj_wartość; tablica_tabeli; numer_indeksu_kolumn; 0)
Jeśli wartość nie zostanie znaleziona w pierwszej kolumnie tablicy tabeli, formuła zwróci błąd #N/D.
Zobaczmy przykład.
Przykład 2
W tym przykładzie chcę napisać tylko listę uczniów w A2, a w B2 chcę pobrać imię i nazwisko ucznia i w C2 jego Stopień. Prosty. Tablica tabeli to B5:D17. Czemu?
Aby to zrobić, napisz te dwie formuły w komórce odpowiednio w komórce B2 i C2.
=WYSZUKAJ.PIONOWO(A2,$B$5:$E$17,2,0)
=WYSZUKAJ.PIONOWO(A2,$B$5:$E$17,3,0)
Teraz niezależnie od tego, który identyfikator wpiszesz w komórce A2, funkcja WYSZUKAJ.PIONOWO będzie szukać dokładnego dopasowania w kolumnie B i zwróci wartość z podanego indeksu col.
Jak działa funkcja WYSZUKAJ.PIONOWO DOKŁADNIE DOPASOWANEGO?
Dokładne dopasowanie VLOOKUP w programie Excel jest proste. Szuka szukanej wartości w pierwszej kolumnie podanej tablicy tablica_tabeli i zwraca wartość z wartości z podanego numeru indeksu_kolumny. Jeśli wartość nie zostanie znaleziona, funkcja WYSZUKAJ.PIONOWO zwraca błąd #N/D.
Najlepsze zastosowania funkcji WYSZUKAJ.PIONOWO
Vlookup ma wiele zastosowań. Omówię kilka najbardziej użytecznych przypadków użycia.
3. Użyj funkcji WYSZUKAJ.PIONOWO Excela do oceniania zamiast złożonej zagnieżdżonej funkcji JEŻELI
Użyj przybliżonego dopasowania WYSZUKAJ.PIONOWO, aby zastąpić zagnieżdżoną funkcję JEŻELI. W przykładzie 1 widzieliśmy, jak możemy używać funkcji vlookup do oceniania uczniów według ich ocen.
Dopasowanie przybliżone WYSZUKAJ.PIONOWO jest szybsze niż zagnieżdżone, ponieważ używa wewnętrznego wyszukiwania binarnego.
4. Użyj funkcji WYSZUKAJ.PIONOWO, aby pobrać dane
To najczęstsze zadanie, które można łatwo wykonać za pomocą funkcji WYSZUKAJ.PIONOWO w programie Excel. W przykładzie 2 zrobiliśmy to, aby pobrać dane uczniów za pomocą ich numeru rzutu.
5. Sprawdź, czy wartość znajduje się w rekordzie za pomocą funkcji Vlookup.
Funkcja WYSZUKAJ.PIONOWO może służyć do sprawdzania, czy wartość znajduje się na jakiejś liście, czy nie.
W przykładzie 2, gdy piszemy 152, zwraca błąd #N/A. Jeśli WYSZUKAJ.PIONOWO zwraca #N/A, oznacza to, że nie znalazł podanej wartości nigdzie w danych.
Przygotowałem krótki artykuł na ten temat. Możesz go przeczytać tutaj.
Używanie formuły WYSZUKAJ.PIONOWO do sprawdzania, czy istnieje wartość
Automatyczne pobieranie indeksu kolumn
Należę do tej grupy ludzi, którzy nie chcą ciągle powtarzać tego samego zadania. Tak więc ciągłe zmienianie numeru col_index denerwuje mnie i zawsze tego unikam. Właściwie unikam jakiejkolwiek edycji w moich formułach, kiedy to napisałem, chyba że nie ma innej opcji.
Indeks kolumn można obliczyć automatycznie w programie Excel. Jest na to kilka sposobów. Zobaczmy niektóre z nich tutaj.
6. Użyj funkcji WYSZUKAJ.PIONOWO z funkcją DOPASUJ
Jak wiemy, funkcja MATCH zwraca znaleziony indeks o dopasowanej wartości.
Więc jeśli mamy dokładnie takie same nagłówki w lokalizacji wyszukiwania, jak dane źródłowe, możemy użyć do uzyskania col_index. Jak? Pozwól nam zobaczyć…
W poniższych danych chcemy po prostu wpisać numer rolki w G2 i chcemy pobrać imię i nazwisko ucznia, oceny i ocenę w H2, niezależnie od tego, który nagłówek tam napiszemy.
Zapisz tę formułę w komórce H2
=WYSZUKAJ.PIONOWO(G2,B2:E14;PODSTAW(H1;B1:E1,0);0)
Teraz za każdym razem, gdy zmienisz nagłówek w H1, ta wartość w H2 będzie pokazana z tej kolumny.
Możesz użyć listy rozwijanej z walidacją danych, która ma wszystkie nagłówki z tabeli, aby była bardziej przyjazna dla użytkownika.
Tutaj VLOOKUP wykonuje dokładne dopasowanie. Teraz WYSZUKAJ.PIONOWO wykonuje swoje zwykłe czynności pobierania danych. Magii dokonuje funkcja MATCH na pozycji col_index.
Tutaj WYSZUKAJ.PIONOWO widzi dowolną wartość w G2 w pierwszej kolumnie tablicy tabeli B2:E14. Teraz w miejscu col_index mamy PODAJ.POZYCJĘ(H1,B1:E1,0).
Funkcja MATCH szuka dowolnej wartości w H1 w zakresie B1:E1 i zwraca indeks wartości dopasowania.
Na przykład, gdy piszemy student w H1 szuka go w zasięgu B1:E1. Znajduje się w C2. stąd zwraca 2. Jeśli wpiszemy Stopień zwróci ocenę ucznia.
Zauważ, że kiedy wpisujemy Region, zwraca #N/A. Ponieważ jest poza zakresem tabeli. Porozmawiamy o tym w dalszej części artykułu.
Jest to najlepszy sposób, aby WYSZUKAJ.PIONOWO automatycznie. Istnieją inne metody, ale nie są one tak elastyczne niż ta.
7. Użyj funkcji WYSZUKAJ.PIONOWO z funkcją COLUMN
Funkcja kolumny zwraca numer kolumny podanego odwołania. A czego potrzebujemy do pobierania danych za pomocą funkcji WYSZUKAJ.PIONOWO? Col_index. Czyli korzystając z funkcji COLUMN możemy oczywiście uzyskać dane z dowolnego zestawu danych.
Weźmy powyższy przykład. Ale teraz musimy pobrać całe dane. Nie tylko jedna kolumna.
Zapisz ten wzór w H2 i skopiuj w I2 i J2.
=WYSZUKAJ.PIONOWO($G2,$B$2:$E$14,KOLUMNA(B1),0)
To da ci dynamiczny wynik. Jak? Zobaczmy.
WYSZUKAJ.PIONOWO działa jak zwykle. W column_index napisaliśmy KOLUMNA(B1), co przełoży się na 2, ponieważ mamy nazwisko ucznia w kolumnie 2 z kolumny B, zwraca imię ucznia.
Ważny: To działa, ponieważ nasza tabela zaczyna się od kolumny B. Jeśli twój stół znajduje się na środku arkusza, musisz odjąć lub dodać kilka liczb, aby spełnić twoje wymagania.
Na przykład, jeśli powyższa tabela zaczynała się od C1 w tej samej strukturze, wszystkie poniższe formuły działały poprawnie.
=WYSZUKAJ.PIONOWO($G2,$C$2:$F$14,KOLUMNA(B1),0)
=WYSZUKAJ.PIONOWO($G2,$C$2:$F$14;KOLUMNA(C1)-1,0)
=WYSZUKAJ.PIONOWO($G2,$C$2:$F$14;KOLUMNA(A1)+1,0)
8. WYSZUKAJ.PIONOWO, aby scalić dane
W większości baz danych istnieje jedno zapytanie, które łączy się z tabelami, najczęściej nazywane zapytaniem złączenia, ale MS Excel w żadnej bazie danych, stąd nie ma zapytania złączenia. Ale to nie znaczy, że nie możemy połączyć dwóch stołów, jeśli mamy wspólne kolumny w dwóch stołach.
Więc kiedy otrzymujesz dane z dwóch działów tej samej firmy, chciałbyś je połączyć w celu analizy tych danych.
Na przykład, gdy otrzymujesz oceny uczniów od nauczycieli, a następnie rekord frekwencji z działu administracyjnego, chciałbyś zobaczyć, który uczeń otrzymał jaką ocenę i jaki jest jego procent frekwencji w jednym miejscu.
Musimy je połączyć w poniższej tabeli.
Oceny, musimy uzyskać z tabeli ocen, a obecność z danych dotyczących obecności.
Napisz poniżej Formuły w komórce D19, aby uzyskać znaki i przeciągnij w dół.
= WYSZUKAJ.PIONOWO(B19;$B2$:$D$15,3,0)
Napisz poniżej formułę w komórce E19
=WYSZUKAJ.PIONOWO(B19;$G3$:$I15,3,0$)
Połączyliśmy dwa stoły w jeden. Oczywiście możesz dodać więcej niż dwie tabele. Wystarczy pobrać dane ze wszystkich żądanych tabel w jednym miejscu za pomocą funkcji WYSZUKAJ.PIONOWO.
Najlepsze praktyki funkcji WYSZUKAJ.PIONOWO
Tak więc we wszystkich powyższych przykładach właśnie użyliśmy funkcji vlookup z surowymi danymi. We wszystkich powyższych przykładach uważaliśmy na dane, których używaliśmy. Musieliśmy być ostrożni, podając referencje tablic tablicowych. Ale istnieją pewne sposoby, które mogą znacznie zmniejszyć ten wysiłek.
9. Użyj odwołań bezwzględnych za pomocą funkcji WYSZUKAJ.PIONOWO
Być może zauważyłeś, że w niektórych artykułach użyłem zakresów bezwzględnych (znak $). Cóż, wściekłość bezwzględna jest używana, gdy nie chcemy zmieniać zakresu podczas kopiowania komórki formuły do innych komórek.
Na przykład, jeśli mam = WYSZUKAJ.PIONOWO(G2,B2:E14,2,0),0) w komórce H2, a jeśli skopiuję lub przeciągnę w dół w komórce H3, formuła zmieni się na = VLOOKUP (G3,B3:E15,2,0). Wszystkie referencje są stosunkowo zmienione. Tutaj możemy chcieć zmienić referencję wartości wyszukiwania, ale nie tablicę tabeli. Ponieważ B2 wychodzi poza zakres i możemy chcieć mieć go zawsze w naszych tablicach tabeli.
Aby temu zapobiec, używamy odwołań bezwzględnych. Ustawiamy te zakresy jako absolutne, których nie chcemy zmieniać, na przykład tablica_tabeli w H2, =WYSZUKAJ.PIONOWO(G2,$ B 2 $: 14 $ E $,2,0),0). Kiedy skopiujesz H2 w H3, formuła będzie wyglądała następująco =WYSZUKAJ.PIONOWO(G3,$ B 2 $: 14 $ E $,2,0),0). Zauważ, że zmienia się wartość wyszukiwania, ale nie tablica_tabeli.
10. Użyj nazwanych zakresów za pomocą funkcji WYSZUKAJ.PIONOWO
W powyższym przykładzie użyliśmy odwołań bezwzględnych do naprawy tablicy table_array. Jest dość rozmyty i trudny do odczytania. Co powiesz na to, że możesz po prostu pisać 'dane' w miejscu $ B 2 $: 14 $ E $ i odwołuje się do $ B 2 $: 14 $ E $ zawsze. Ułatwi jeszcze czytanie i pisanie formuły.
Po prostu wybierz zakres i nazwij go „dane”.
Aby nazwać zakres, wybierz zakres. Kliknij prawym przyciskiem myszy, kliknij Zdefiniuj nazwę i nazwij żądany zakres. Teraz wystarczy =WYSZUKAJ.PIONOWO(G3,dane,2,0),0) formuła będzie działała dokładnie tak samo jak poprzednio.
Możesz przeczytać więcej o nazwanych zakresach w programie Excel. Wyjaśniłem tutaj każdy aspekt nazwanego zakresu.
11. Symbole wieloznaczne do częściowego dopasowania za pomocą funkcji WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO umożliwia częściowe dopasowanie. Tak, podczas wykonywania dokładnego dopasowania funkcji WYSZUKAJ.PIONOWO możesz użyć operatorów wieloznacznych, aby wykonać częściowe dopasowanie. Na przykład, jeśli chcesz wyszukać wartość, która zaczyna się od „Gil”, możesz użyć operatora wieloznacznego * z „Gil” jako „Gil*”. Zobaczmy przykład.
Tutaj chcę uzyskać oceny ucznia, którego nazwisko zaczyna się na Gil. Aby tak się stało, napiszę poniżej wzór.
=WYSZUKAJ.PIONOWO("*Skrzela";C2:D14,2,0)
W programie Excel dostępne są dwa symbole wieloznaczne, których można używać z funkcją WYSZUKAJ.PIONOWO.
Gwiazdka (*) symbol wieloznaczny. Jest to używane, gdy użytkownik nie zna liczby znaków i zna tylko niektóre znaki dopasowania. Na przykład, jeśli użytkownik zna nazwę zaczynającą się od „Sm”, napisze „Sm*”. Jeśli użytkownik wie, że wartość wyszukiwania, która kończy się na „123”, napisze „*123”. (Wild Card działa tylko z tekstami). A jeśli wie, że „se” jest w tekście, to napisze „*se*”.
Znak zapytania ("?"). Jest to używane, gdy użytkownik zna liczbę brakujących znaków. Na przykład, jeśli chcę wyszukać wartość, która ma 4 znaki, ale nie wiem, czym one są, po prostu napiszę „???” w miejscu wartości wyszukiwania.
Vlookup zwróci odpowiednią wartość pierwszej znalezionej wartości, która ma dokładnie cztery znaki.
Ważny: Dzika karta działa tylko z wartościami tekstowymi. Konwertuj liczby na tekst, jeśli chcesz używać z nimi symboli wieloznacznych.
Słabe strony funkcji WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO jest rzeczywiście potężną i łatwą funkcją do pobierania wartości, ale istnieje wiele obszarów, w których WYSZUKAJ.PIONOWO nie jest tak pomocna. Jeśli pracujesz nad programem Excel, musisz je również znać i jak wykonać zadanie, którego nie może wykonać WYSZUKAJ.PIONOWO.
12. Nie można pobrać wartości z lewej strony tablicy_tabeli
Największą wadą funkcji WYSZUKAJ.PIONOWO jest to, że nie może pobrać wartości z lewej strony tablicy tabeli. WYSZUKAJ.PIONOWO wyszukuje tylko na prawo od wartości wyszukiwania.
Dzieje się tak dlatego, że funkcja WYSZUKAJ.PIONOWO wyszukuje podane wartość wyszukiwania w pierwszej kolumnie podanej tablica tabeli. Nigdy nie zwróci wartości spoza tabeli. A jeśli spróbujesz zachować lewą kolumnę w tablicy tabeli, stanie się ona pierwszą kolumną tablicy tablicy, stąd wartość wyszukiwania będzie przeszukiwana w tym zakresie. Co prawdopodobnie wygeneruje błąd.
Aby wyszukać wartości z lewej strony wartości wyszukiwania, zamiast tego używamy INDEX-MATCH. Funkcja INDEX-MATCH może wyszukiwać wartości z dowolnej kolumny w arkuszu. W rzeczywistości zakres wyszukiwania i zakres, z którego chcesz wyszukiwać wartości, są całkowicie niezależne, co sprawia, że dopasowanie indeksu jest wysoce konfigurowalne.
WYSZUKAJ.PIONOWO zwraca #N/D, nawet jeśli istnieje wartość wyszukiwania.
#N/D Błąd jest zwracany przez funkcję WYSZUKAJ.PIONOWO, gdy wartość wyszukiwania nie zostanie znaleziona. Może to być denerwujące, gdy wartość znajduje się w zakresie, ale WYSZUKAJ.PIONOWO zwraca błąd #N/D.
14. Gdy liczby są sformatowane jako tekst
Dzieje się tak najczęściej, gdy liczby są sformatowane jako tekst. Gdy znajdziesz za pomocą polecenia CTRL + F, program Excel go znajdzie, ale gdy spróbujesz użyć funkcji WYSZUKAJ.PIONOWO, zwraca #N/A. To zadanie jest podawane w większości wywiadów jako pytania podchwytliwe.
Na powyższym obrazku widać, że 101 jest właśnie tam, ale formuła zwraca błąd. Jak sobie z tym radzisz? Cóż, są dwie metody.
1. Konwertuj tekst na liczbę w danych
Możesz zobaczyć ten zielony znacznik w lewym górnym rogu w kolumnie rollno. Wskazują, że coś jest niezwykłego w wartości komórki. Po wybraniu komórki pokaże się, że liczba jest sformatowana jako tekst.
Po kliknięciu na wykrzyknik (!), pojawi się opcja Konwertuj na numer. Zaznacz wszystkie komórki i kliknij tę opcję. Wszystkie wartości zostaną przekonwertowane na tekst.
2. Konwertuj swój numer wyszukiwania na tekst w formule
W powyższym przykładzie zmutowaliśmy oryginalne dane. Możesz nie chcieć niczego zmieniać w oryginalnych danych. Więc chciałbyś to zrobić w formule. Aby zmienić liczbę na tekst w formule WYSZUKAJ.PIONOWO napisz to.
=WYSZUKAJ.PIONOWO(TEKST(G2,"0");B2:D14,2,0)
Tutaj formuła dynamicznie zamienia liczbę na tekst, a następnie dopasowuje ją do podanego zakresu.
15. Tekst mający spacje wiodące i końcowe
Niektóre dane podawane przez operatorów wprowadzania danych oraz dane z Internetu nie są czyste. Mogą mieć końcowe spacje lub niektóre niedrukowalne znaki. Podczas wyszukiwania przy użyciu tych wartości może pojawić się błąd #N/D. Aby tego uniknąć, najpierw wyczyść swoje dane. Aby usunąć spacje wiodące, użyj funkcji TRIM. TRIM usuwa z tekstu dowolną liczbę wiodących lub końcowych spacji.
Sugeruję więc dodanie nowej kolumny i umieszczenie tam oczyszczonych danych. Następnie wartość wklej to. Teraz, jeśli chcesz, możesz pozbyć się oryginalnej kolumny.
=PRZYC(WYCZYŚĆ(tekst))
Obsługa błędów funkcji WYSZUKAJ.PIONOWO
Jak wiesz, funkcja WYSZUKAJ.PIONOWO nie może znaleźć wartości, zwraca błąd #N/D. Cóż, jest w porządku, aby uzyskać błąd #N/A, może czasami masz taki zamiar, na przykład, gdy chcesz sprawdzić, czy wartość już istnieje na liście, czy nie. Tam #N/A oznacza, że wartości nie ma.
Ale #N/A wygląda brzydko. Co powiesz na uzyskanie przyjaznych dla użytkownika danych wyjściowych, takich jak „Nie znaleziono” lub „Nie znaleziono klienta”. Możemy użyć funkcji IFERROR z funkcją WYSZUKAJ.PIONOWO, aby uniknąć #N/A.
Użyj poniższej formuły, aby wyłapać błąd w funkcji WYSZUKAJ.PIONOWO.
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(150;B2:E14,2,0);"Nie znaleziono rolki")
16. WYSZUKAJ.PIONOWO z WIELOMA kryteriami
WYSZUKAJ.PIONOWO nie może działać z wieloma kryteriami.Tak, możesz mieć tylko jedno kryterium wyszukiwania wartości za pomocą formuły WYSZUKAJ.PIONOWO.
Na przykład, jeśli masz imię i nazwisko w dwóch oddzielnych kolumnach.
A teraz, jeśli chcesz wyszukać wartość, której imię to Jan i nazwisko Dave, nie możesz tego zrobić normalnie.
Ale istnieje obejście tego. Możesz utworzyć oddzielną kolumnę, łącząc imię i nazwisko, a następnie sprawdź tę kolumnę imienia i nazwiska.
Tam jest Alternatywa INDEX-MATCH, która może wyszukiwać wiele kryteriów bez żadnej kolumny pomocy.
17. WYSZUKAJ.PIONOWO pobiera tylko pierwszą znalezioną wartość
Wyobraź sobie, że masz jakieś dane w regionie centralnym. Teraz chcesz uzyskać pierwsze 5 rekordów danych regionu centralnego. Funkcja WYSZUKAJ.PIONOWO zwróci tylko pierwszą środkową wartość ze wszystkich pięciu rekordów. To jest główne tło.
Ale to nie znaczy, że nie możemy tego osiągnąć. Możemy użyć złożonej formuły tablicowej, jak poniżej.
=INDEKS($C$2:$C$14,MAŁY(JEŻELI(G2=$A$2:$A$14;WIERSZ($A$2:$A$14))-WIERSZ($A$2)+1),WIERSZ (1: 1)))
Napisz tę formułę i naciśnij CTRL+SHIFT+ENTER.
I gotowe.
Wyjaśniłem to w artykule, jak WYSZUKAJ wiele wartości w programie Excel.
Więc tak, w tym artykule omówiłem wszystkie możliwe aspekty funkcji WYSZUKAJ.PIONOWO zgodnie z moją wiedzą. Jeśli uważasz, że coś przegapiłem, daj mi znać w sekcji komentarzy poniżej.
Vlookup Top 5 wartości ze zduplikowanymi wartościami przy użyciu INDEX-MATCH w programie Excel
WYSZUKAJ.PIONOWO Wiele wartości
WYSZUKAJ.PIONOWO z dynamicznym indeksem Col
Częściowe dopasowanie z funkcją WYSZUKAJ.PIONOWO
Użyj funkcji WYSZUKAJ.PIONOWO z co najmniej dwóch tabel przeglądowych
Przeglądaj według daty w Excelu
Używanie formuły WYSZUKAJ.PIONOWO do sprawdzania, czy istnieje wartość
Jak WYSZUKAJ.PIONOWO z innego arkusza Excel
WYSZUKAJ.PIONOWO z liczbami i tekstem
Funkcja JEŻELI, ISNA i WYSZUKAJ.PIONOWO
ISNA i funkcja WYSZUKAJ.PIONOWO
IFERROR i funkcja WYSZUKAJ.PIONOWO