Ponad 20 przykładów WYSZUKAJ.PIONOWO dla początkujących i zaawansowanych użytkowników programu Excel
Funkcja WYSZUKAJ.PIONOWO jest jedną z najpopularniejszych funkcji w programie Excel. W tym samouczku krok po kroku przedstawiono sposób korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel z dziesiątkami podstawowych i zaawansowanych przykładów.
Spis treści:
1. Wprowadzenie do funkcji WYSZUKAJ.PIONOWO - składnia i argumenty
2. Podstawowe przykłady WYSZUKAJ.PIONOWO
- 2.1 Dokładne dopasowanie i przybliżone dopasowanie WYSZUKAJ.PIONOWO
- 2.2 WYSZUKAJ.PIONOWO rozróżniana jest wielkość liter
- 2.3 WYSZUKAJ.PIONOWO od prawej do lewej
- 2.4 WYSZUKAJ.PIONOWO druga, n-ta lub ostatnia pasująca wartość
- 2.5 WYSZUKAJ.PIONOWO między dwiema podanymi wartościami lub datami
- 2.6 Używanie symboli wieloznacznych dla częściowych dopasowań w funkcji WYSZUKAJ.PIONOWO
- 2.7 WYSZUKAJ.PIONOWO wartości z innego arkusza
- 2.8 Wartości WYSZUKAJ.PIONOWO z innego skoroszytu
- 2.9 WYSZUKAJ.PIONOWO i zwróć pusty lub określony tekst zamiast wartości błędu 0 lub #N/D
3. Zaawansowane przykłady WYSZUKAJ.PIONOWO
- 3.1 Wyszukiwanie dwukierunkowe z funkcją WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO w wierszu i kolumnie)
- 3.2 VLOOKUP dopasowanie wartości na podstawie dwóch lub więcej kryteriów
- 3.3 WYSZUKAJ.PIONOWO, aby zwrócić wiele pasujących wartości z jednym lub większą liczbą warunków
- 3.4 WYSZUKAJ.PIONOWO, aby zwrócić cały lub cały wiersz dopasowanej komórki
- 3.5 Wykonaj wiele funkcji WYSZUKAJ.PIONOWO (zagnieżdżone WYSZUKAJ.PIONOWO) w programie Excel
- 3.6 WYSZUKAJ.PIONOWO, aby sprawdzić, czy wartość istnieje na podstawie danych listy w innej kolumnie
- 3.7 WYSZUKAJ.PIONOWO i zsumuj wszystkie pasujące wartości w wierszach lub kolumnach
- 3.8 WYSZUKAJ.PIONOWO, aby scalić dwie tabele na podstawie jednej lub więcej kluczowych kolumn
- 3.9 WYSZUKAJ.PIONOWO dopasowywanie wartości w wielu arkuszach
4. Dopasowane wartości WYSZUKAJ.PIONOWO zachowują formatowanie komórek
Pobierz przykładowe pliki WYSZUKAJ.PIONOWO
Podstawowe przykłady Vlookup | Zaawansowane przykłady Vlookup | Vlookup zachowuje formatowanie komórek
Wprowadzenie do funkcji WYSZUKAJ.PIONOWO - składnia i argumenty
W programie Excel funkcja WYSZUKAJ.PIONOWO jest potężną funkcją dla większości użytkowników programu Excel, umożliwia wyszukanie wartości w skrajnej lewej części zakresu danych i zwrócenie pasującej wartości w tym samym wierszu z kolumny określonej zgodnie z poniższym zrzutem ekranu .
Składnia funkcji WYSZUKAJ.PIONOWO:
argumenty:
Wyszukaj_wartość (wymagane): wartość, którą chcesz wyszukać. Może to być wartość (liczba, data lub tekst) lub odwołanie do komórki. Musi znajdować się w pierwszej kolumnie zakresu table_array.
Tablica_tabeli (wymagane): zakres danych lub tabela, w której znajduje się kolumna wartości wyszukiwania i kolumna wartości wyniku.
numer_indeksu_kolumny (wymagane): numer kolumny zawierającej zwracane wartości. Zaczyna się od 1 od skrajnej lewej kolumny w tablicy tabeli.
Zakres wyszukiwania (opcjonalnie): wartość logiczna określająca, czy ta funkcja WYSZUKAJ.PIONOWO zwróci dopasowanie dokładne, czy przybliżone.
- Przybliżone dopasowanie – 1 / PRAWDA / pominięte (domyślnie): Jeśli dokładne dopasowanie nie zostanie znalezione, formuła wyszukuje najbliższe dopasowanie — największą wartość, która jest mniejsza niż szukana wartość.
Ogłoszenie: W takim przypadku należy posortować kolumnę odnośnika (skrajnie lewą kolumnę zakresu danych) w porządku rosnącym, w przeciwnym razie zwróci nieprawidłowy wynik lub błąd #N/D. - Dokładne dopasowanie – 0 / FAŁSZ: Służy do wyszukiwania wartości dokładnie równej wartości wyszukiwania. Jeśli dokładne dopasowanie nie zostanie znalezione, zostanie zwrócona wartość błędu # N / A.
Uwagi dotyczące funkcji:
- Funkcja Vlookup szuka wartości tylko od lewej do prawej.
- Funkcja Vlookup wykonuje wyszukiwanie bez rozróżniania wielkości liter.
- Jeśli istnieje wiele pasujących wartości opartych na wyszukiwanej wartości, tylko pierwsza dopasowana wartość zostanie zwrócona za pomocą funkcji Vlookup.
Podstawowe przykłady funkcji WYSZUKAJ.PIONOWO
W tej sekcji omówimy niektóre często używane formuły Vlookup.
2.1.1 Wykonaj dokładne dopasowanie WYSZUKAJ.PIONOWO
Zwykle, jeśli szukasz dokładnego dopasowania za pomocą funkcji WYSZUKAJ.PIONOWO, wystarczy użyć FALSE jako ostatniego argumentu.
Na przykład, aby uzyskać odpowiednie wyniki matematyczne na podstawie określonych numerów identyfikacyjnych, wykonaj następujące czynności:
Skopiuj i wklej poniższą formułę do pustej komórki (tutaj wybieram G2) i naciśnij Wchodzę klucz do uzyskania wyniku:
=VLOOKUP(F2,$A$2:$D$7,3,FALSE)
Uwaga: w powyższym wzorze występują cztery argumenty:
- F2 jest komórką zawierającą wartość C1005, którą chcesz wyszukać;
- A2: D7 to tablica tabeli, w której przeprowadzasz wyszukiwanie;
- 3 to numer kolumny, z której zwracana jest dopasowana wartość; (Gdy funkcja wykryje identyfikator - C1005, przejdzie do trzeciej kolumny tablicy tabeli i zwróci wartości w tym samym wierszu co identyfikator - C1005.)
- FAŁSZYWY odnosi się do dokładnego dopasowania.
Jak działa formuła WYSZUKAJ.PIONOWO?
Najpierw szuka identyfikatora - C1005 w skrajnej lewej kolumnie tabeli. Przechodzi od góry do dołu i znajduje wartość w komórce A6.
Gdy tylko znajdzie wartość, przechodzi w prawo w trzeciej kolumnie i wyodrębnia z niej wartość.
Otrzymasz wynik jak pokazano na poniższym zrzucie ekranu:
Kutools dla Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
2.1.2 Wykonaj przybliżone dopasowanie WYSZUKAJ.PIONOWO
Dopasowanie przybliżone jest przydatne do wyszukiwania wartości między zakresami danych. Jeśli dokładne dopasowanie nie zostanie znalezione, przybliżona funkcja WYSZUKAJ.PIONOWO zwróci największą wartość, która jest mniejsza niż wartość wyszukiwania.
Na przykład, jeśli masz następujący zakres danych, a określonych zamówień nie ma w kolumnie Zamówienia, jak uzyskać najbliższy Rabat w kolumnie B?
Krok 1: Zastosuj formułę WYSZUKAJ.PIONOWO i wypełnij nią inne komórki
Skopiuj i wklej następującą formułę do komórki, w której chcesz umieścić wynik, a następnie przeciągnij uchwyt wypełniania w dół, aby zastosować tę formułę do innych komórek.
=VLOOKUP(D2,$A$2:$B$9,2,TRUE)
Wynik:
Teraz otrzymasz przybliżone dopasowania na podstawie podanych wartości, patrz zrzut ekranu:
Uwagi:
- W powyższym wzorze:
- D2 jest wartością, której chcesz zwrócić informacje względne;
- A2: B9 to zakres danych;
- 2 wskazuje numer kolumny, do której zwracana jest dopasowana wartość;
- TRUE odnosi się do przybliżonego dopasowania.
- Przybliżone dopasowanie zwróci największą wartość, która jest mniejsza niż określona wartość wyszukiwania, jeśli nie zostanie znalezione dokładne dopasowanie.
- Aby użyć funkcji WYSZUKAJ.PIONOWO w celu uzyskania przybliżonej wartości dopasowania, należy posortować rosnącą kolumnę zakresu danych znajdującą się najbardziej na lewo, w przeciwnym razie zwróci nieprawidłowy wynik.
2.2 Wykonaj funkcję WYSZUKAJ.PIONOWO z uwzględnieniem wielkości liter w programie Excel
Domyślnie funkcja WYSZUKAJ.PIONOWO przeprowadza wyszukiwanie bez rozróżniania wielkości liter, co oznacza, że traktuje małe i wielkie litery jako identyczne. Czasami może być konieczne wykonanie wyszukiwania z uwzględnieniem wielkości liter w programie Excel, normalna funkcja WYSZUKAJ.PIONOWO może tego nie rozwiązać. W takim przypadku możesz użyć funkcji alternatywnych, takich jak INDEKS i PODAJ.POZYCJĘ z funkcją DOKŁADNIE lub funkcjami WYSZUKAJ i DOKŁADNIE.
Na przykład mam następujący zakres danych, którego kolumna ID zawiera ciąg tekstowy z dużymi lub małymi literami, teraz chcę zwrócić odpowiedni wynik matematyczny dla podanego numeru identyfikacyjnego.
Krok 1: Zastosuj dowolną formułę i wypełnij nią inne komórki
Skopiuj i wklej dowolną z poniższych formuł do pustej komórki, w której chcesz uzyskać wynik. Następnie wybierz komórkę z formułą, przeciągnij uchwyt wypełniania w dół do komórek, w których chcesz wypełnić tę formułę.
Formuła 1: Po wklejeniu formuły naciśnij Ctrl + Shift + Enter klawiatura.
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))
Formuła 2: Po wklejeniu formuły naciśnij Wchodzę klawisz.
=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)
Wynik:
Wtedy uzyskasz prawidłowe wyniki, których potrzebujesz. Zobacz zrzut ekranu:
Uwagi:
- W powyższym wzorze:
- A2: A10 to kolumna zawierająca określone wartości, w których chcesz wyszukać;
- F2 jest wartością wyszukiwania;
- C2: C10 to kolumna, z której zostanie zwrócony wynik.
- Jeśli zostanie znalezionych wiele dopasowań, ta formuła zawsze zwróci ostatnie dopasowanie.
2.3 Wartości WYSZUKAJ.PIONOWO od prawej do lewej w programie Excel
Funkcja WYSZUKAJ.PIONOWO zawsze wyszukuje wartość w skrajnej lewej kolumnie zakresu danych i zwraca odpowiednią wartość z kolumny po prawej stronie. Jeśli chcesz wykonać odwrotne WYSZUKAJ.PIONOWO, co oznacza wyszukanie określonej wartości w prawej kolumnie i zwrócenie odpowiadającej jej wartości w lewej kolumnie, jak pokazano na poniższym zrzucie ekranu:
Kliknij, aby poznać szczegółowe informacje krok po kroku o tym zadaniu…
2.4 WYSZUKAJ.PIONOWO druga, n-ta lub ostatnia pasująca wartość w programie Excel
Zwykle, jeśli podczas korzystania z funkcji Vlookup zostanie znalezionych wiele pasujących wartości, zwrócony zostanie tylko pierwszy pasujący rekord. W tej sekcji omówię, jak uzyskać drugą, n-tą lub ostatnią pasującą wartość w zakresie danych.
2.4.1 WYSZUKAJ.PIONOWO i zwróć drugą lub n-tą pasującą wartość
Załóżmy, że w kolumnie A masz listę nazwisk, w kolumnie B szkolenie, które kupili. Teraz szukasz drugiego lub n-tego szkolenia kupionego przez danego klienta. Zobacz zrzut ekranu:
W tym przypadku funkcja WYSZUKAJ.PIONOWO może nie rozwiązać tego zadania bezpośrednio. Możesz jednak użyć funkcji INDEKS jako alternatywy.
Krok 1: Zastosuj i wypełnij formułę w innych komórkach
Na przykład, aby uzyskać drugą pasującą wartość na podstawie podanych kryteriów, zastosuj następującą formułę do pustej komórki i naciśnij Ctrl + Shift + Enter klawisze razem, aby uzyskać pierwszy wynik. Następnie wybierz komórkę z formułą, przeciągnij uchwyt wypełniania w dół do komórek, w których chcesz wypełnić tę formułę.
=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))
Wynik:
Teraz wszystkie drugie dopasowane wartości oparte na podanych nazwach zostały wyświetlone jednocześnie.
Uwaga: W powyższym wzorze:
- A2: A14 to zakres ze wszystkimi wartościami do wyszukania;
- B2: B14 to zakres pasujących wartości, z których chcesz zwrócić;
- E2 jest wartością wyszukiwania;
- 2 wskazuje drugą dopasowaną wartość, którą chcesz uzyskać, aby zwrócić trzecią dopasowaną wartość, wystarczy zmienić ją na 3.
2.4.2 WYSZUKAJ.PIONOWO i zwróć ostatnią pasującą wartość
Jeśli chcesz pominąć i zwrócić ostatnią pasującą wartość, jak pokazano na poniższym zrzucie ekranu, to WYSZUKAJ.PIONOWO i zwróć ostatnią pasującą wartość samouczek może pomóc w uzyskaniu ostatniej pasującej wartości w szczegółach.
2.5 WYSZUKAJ.PIONOWO dopasowywanie wartości między dwiema podanymi wartościami lub datami
Czasami możesz chcieć wyszukać wartości między dwiema wartościami lub datami i zwrócić odpowiednie wyniki, jak pokazano na zrzucie ekranu poniżej. W takim przypadku możesz użyć funkcji WYSZUKAJ zamiast funkcji WYSZUKAJ.PIONOWO z posortowaną tabelą.
2.5.1 WYSZUKAJ.PIONOWO dopasowywanie wartości między dwiema podanymi wartościami lub datami za pomocą formuły
Krok 1: Uporządkuj dane i zastosuj poniższą formułę
Oryginalna tabela powinna zawierać posortowany zakres danych. Następnie skopiuj lub wprowadź następującą formułę do pustej komórki. Następnie przeciągnij uchwyt wypełniania, aby wypełnić tę formułę w innych potrzebnych komórkach.
=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)
Wynik:
A teraz otrzymasz wszystkie dopasowane rekordy na podstawie podanej wartości, patrz zrzut ekranu:
Uwagi:
- W powyższym wzorze:
- A2: A6 jest przedziałem mniejszych wartości;
- B2: B6 jest zakresem większych liczb;
- E2 jest wartością wyszukiwania, dla której chcesz uzyskać odpowiadającą jej wartość;
- C2: C6 to kolumna, z której chcesz zwrócić odpowiednią wartość.
- Tej formuły można również użyć do wyodrębnienia dopasowanych wartości między dwiema datami, jak pokazano na poniższym zrzucie ekranu:
2.5.2 WYSZUKAJ.PIONOWO dopasowywanie wartości między dwiema podanymi wartościami lub datami za pomocą przydatnej funkcji
Jeśli masz trudności z zapamiętaniem i zrozumieniem powyższej formuły, tutaj przedstawię proste narzędzie – Kutools dla programu Excel, Z siedzibą w WYSZUKAJ między dwiema wartościami funkcji, możesz z łatwością zwrócić odpowiednią pozycję na podstawie określonej wartości lub daty między dwiema wartościami lub datami.
- Kliknij Kutools > Super WYSZUKAJ > WYSZUKAJ między dwiema wartościami aby włączyć tę funkcję.
- Następnie określ operacje z okna dialogowego na podstawie swoich danych.
2.6 Używanie symboli wieloznacznych dla częściowych dopasowań w funkcji WYSZUKAJ.PIONOWO
W programie Excel symboli wieloznacznych można używać w ramach funkcji WYSZUKAJ.PIONOWO, która umożliwia częściowe dopasowanie szukanej wartości. Na przykład możesz użyć funkcji WYSZUKAJ.PIONOWO, aby zwrócić dopasowaną wartość z tabeli na podstawie części wyszukiwanej wartości.
Przypuśćmy, że mam szereg danych, jak pokazano na zrzucie ekranu, teraz chcę wyodrębnić wynik na podstawie imienia (nie imienia i nazwiska). Jak można rozwiązać to zadanie w programie Excel?
Krok 1: Zastosuj i wypełnij formułę w innych komórkach
Skopiuj lub wprowadź następującą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania, aby wypełnić tę formułę w innych potrzebnych komórkach:
=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)
Wynik:
Wszystkie dopasowane wyniki zostały zwrócone, jak pokazano na poniższym zrzucie ekranu:
Uwaga: W powyższym wzorze:
- E2 & „*” jest kryterium częściowej matematyki. Oznacza to, że szukasz dowolnej wartości, która zaczyna się od wartości w komórce E2. (znak wieloznaczny „*” oznacza dowolny znak lub dowolne znaki)
- A2: C11 to zakres danych, w którym chcesz wyszukać dopasowaną wartość;
- 3 oznacza zwrócenie pasującej wartości z 3. kolumny zakresu danych;
- Fałszywy wskazuje dokładną matematykę. (W przypadku używania symboli wieloznacznych należy ustawić ostatni argument w funkcji na FAŁSZ lub 0, aby włączyć tryb dokładnego dopasowania w funkcji WYSZUKAJ.PIONOWO).
- Aby znaleźć i zwrócić pasujące wartości kończące się określoną wartością, przed wartością należy umieścić symbol wieloznaczny „*”. Proszę zastosować tę formułę:
-
=VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)
- Aby wyszukać i zwrócić dopasowaną wartość na podstawie części ciągu tekstowego, niezależnie od tego, czy określony tekst znajduje się na początku, na końcu czy w środku ciągu tekstowego, wystarczy ująć odwołanie do komórki lub tekst dwoma gwiazdkami (*) po obu stronach. Proszę zrobić z tą formułą
-
=VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)
2.7 WYSZUKAJ.PIONOWO wartości z innego arkusza
Zwykle może być konieczna praca z więcej niż jednym arkuszem, funkcja WYSZUKAJ.PIONOWO może służyć do wyszukiwania danych z innego arkusza, tak samo jak w jednym arkuszu.
Na przykład masz dwa arkusze robocze, jak pokazano poniżej, aby wyszukać i zwrócić odpowiednie dane z określonego arkusza roboczego, wykonaj następujące czynności:
Krok 1: Zastosuj i wypełnij formułę w innych komórkach
Wprowadź lub skopiuj poniższą formułę do pustej komórki, w której chcesz uzyskać pasujące elementy. Następnie przeciągnij uchwyt wypełniania w dół do komórek, w których chcesz zastosować tę formułę.
=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)
Wynik:
Otrzymasz odpowiednie wyniki, jak potrzebujesz, zobacz zrzut ekranu:
Uwaga: W powyższym wzorze:
- A2 reprezentuje wartość wyszukiwania;
- „Karta danych”!A2:C15 wskazuje, aby wyszukać wartości z zakresu A2:C15 w arkuszu o nazwie Arkusz danych; (Jeśli nazwa arkusza zawiera spacje lub znaki interpunkcyjne, należy ująć nazwę arkusza w pojedyncze cudzysłowy, w przeciwnym razie można bezpośrednio użyć nazwy arkusza, np. =WYSZUKAJ.PIONOWO(A2;arkusz danych!$A$2:$C$15,3,0) ).
- 3 to numer kolumny zawierającej dopasowane dane, z których chcesz powrócić;
- 0 oznacza wykonanie dokładnego dopasowania.
2.8 Wartości WYSZUKAJ.PIONOWO z innego skoroszytu
W tej sekcji omówiono wyszukiwanie i zwracanie pasujących wartości z innego skoroszytu za pomocą funkcji WYSZUKAJ.PIONOWO.
Załóżmy na przykład, że masz dwa skoroszyty. Pierwszy skoroszyt zawiera listę produktów i ich koszty. W drugim skoroszycie chcesz wyodrębnić odpowiedni koszt dla każdej pozycji produktu, jak pokazano na poniższym zrzucie ekranu.
Krok 1: Zastosuj i wypełnij formułę
Otwórz oba skoroszyty, których chcesz użyć, a następnie zastosuj następującą formułę do komórki, w której chcesz umieścić wynik w drugim skoroszycie. Następnie przeciągnij i skopiuj tę formułę do innych potrzebnych komórek
=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)
Wynik:
Uwagi:
- W powyższym wzorze:
- B2 reprezentuje wartość wyszukiwania;
- „[Lista produktów.xlsx]Arkusz1”!A2:B6 wskazuje na wyszukiwanie z zakresu A2:B6 na arkuszu o nazwie Arkusz1 ze skoroszytu Lista produktów; (Odniesienie do skoroszytu jest ujęte w nawiasy kwadratowe, a cały skoroszyt + arkusz jest ujęty w pojedyncze cudzysłowy.)
- 2 to numer kolumny zawierającej dopasowane dane, z których chcesz powrócić;
- 0 wskazuje na zwrócenie dokładnego dopasowania.
- Jeśli skoroszyt wyszukiwania jest zamknięty, pełna ścieżka do skoroszytu wyszukiwania zostanie wyświetlona w formule, jak pokazano na poniższym zrzucie ekranu:
2.9 Zwraca pusty lub określony tekst zamiast błędu 0 lub #N/D
Zwykle, gdy używasz funkcji WYSZUKAJ.PIONOWO w celu zwrócenia odpowiedniej wartości, jeśli pasująca komórka jest pusta, zwrócona zostanie wartość 0. A jeśli pasująca wartość nie zostanie znaleziona, otrzymasz wartość błędu #N/D, jak pokazano w zrzut ekranu poniżej. Jeśli chcesz wyświetlić pustą komórkę lub określoną wartość zamiast 0 lub #N/D, to WYSZUKAJ.PIONOWO, aby zwrócić pustą lub określoną wartość zamiast 0 lub nie dotyczy samouczek może wyświadczyć ci przysługę.
3.1 Wyszukiwanie dwukierunkowe (WYSZUKAJ.PIONOWO w wierszu i kolumnie)
Czasami może być konieczne wykonanie wyszukiwania dwuwymiarowego, co oznacza jednoczesne wyszukanie wartości zarówno w wierszu, jak iw kolumnie. Na przykład, jeśli masz następujący zakres danych i może być konieczne uzyskanie wartości dla określonego produktu w określonym kwartale. Ta sekcja przedstawi formułę radzenia sobie z tym zadaniem w Excelu.
W programie Excel można użyć kombinacji funkcji WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ, aby przeprowadzić wyszukiwanie dwukierunkowe.
Proszę zastosować następującą formułę do pustej komórki, a następnie nacisnąć Wchodzę aby uzyskać wynik.
=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)
Uwaga: W powyższym wzorze:
- G2 to wyszukiwana wartość w kolumnie, na podstawie której chcesz uzyskać odpowiednią wartość;
- A2: E7 to tabela danych, z której będziesz patrzeć;
- H1 to wyszukiwana wartość w wierszu, na podstawie którego chcesz uzyskać odpowiednią wartość;
- A2: E2 to komórki nagłówków kolumn;
- FAŁSZYWY wskazuje, aby uzyskać dokładne dopasowanie.
3.2 VLOOKUP dopasowanie wartości na podstawie dwóch lub więcej kryteriów
Łatwo jest wyszukać pasującą wartość na podstawie jednego kryterium, ale jeśli masz dwa lub więcej kryteriów, co możesz zrobić?
3.2.1 VLOOKUP dopasowanie wartości na podstawie dwóch lub więcej kryteriów z formułami
W takim przypadku funkcje WYSZUKAJ lub PODAJ.POZYCJĘ i INDEKS w programie Excel mogą pomóc w szybkim i łatwym rozwiązaniu tego zadania.
Na przykład mam poniższą tabelę danych, aby zwrócić dopasowaną cenę na podstawie konkretnego produktu i rozmiaru, poniższe formuły mogą ci pomóc.
Krok 1: Zastosuj dowolną formułę
Formuła 1: Po wklejeniu formuły naciśnij Wchodzę klawisz.
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))
Formuła 2: Po wklejeniu formuły naciśnij Ctrl + Shift + Enter klawiatura.
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
Wynik:
Uwagi:
- W powyższych wzorach:
- A2: A12 = G1 środki do wyszukiwania kryteriów G1 w zakresie A2:A12;
- B2: B12 = G2 środki do wyszukiwania kryteriów G2 w zakresie B2:B12;
- D2: D12 is zakres, z którego chcesz zwrócić odpowiednią wartość.
- Jeśli masz więcej niż dwa kryteria, wystarczy dołączyć pozostałe kryteria do formuły, na przykład:
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3.2.2 Wartość dopasowywania funkcji WYSZUKAJ.PIONOWO na podstawie dwóch lub więcej kryteriów za pomocą inteligentnej funkcji
Zapamiętanie powyższych skomplikowanych formuł, które trzeba wielokrotnie stosować, może być trudne, co może spowolnić wydajność pracy. Jednakże, Kutools dla programu Excel oferuje Wyszukiwanie wielowarunkowe funkcja, która pozwala zwrócić odpowiedni wynik w oparciu o jeden lub więcej warunków za pomocą zaledwie kilku kliknięć.
- Kliknij Kutools > Super WYSZUKAJ > Wyszukiwanie wielowarunkowe aby włączyć tę funkcję.
- Następnie określ operacje z okna dialogowego na podstawie swoich danych.
3.3 WYSZUKAJ.PIONOWO, aby zwrócić wiele wartości z jednym lub większą liczbą kryteriów
W programie Excel funkcja WYSZUKAJ.PIONOWO wyszukuje wartość i zwraca tylko pierwszą pasującą wartość, jeśli znaleziono wiele odpowiednich wartości. Czasami możesz chcieć zwrócić wszystkie odpowiednie wartości w wierszu, w kolumnie lub w pojedynczej komórce. W tej sekcji omówiono sposób zwracania wielu pasujących wartości z co najmniej jednym warunkiem w skoroszycie.
3.3.1 WYSZUKAJ.PIONOWO wszystkie pasujące wartości na podstawie jednego lub więcej warunków w poziomie
Zakładając, że masz tabelę danych zawierającą kraj, miasto i nazwy z zakresu A1: C14, a teraz chcesz zwrócić wszystkie nazwy w poziomie, które pochodzą z „USA”, jak pokazano na zrzucie ekranu. Proszę o rozwiązanie tego zadania kliknij tutaj, aby uzyskać wynik krok po kroku.
3.3.2 WYSZUKAJ.PIONOWO wszystkie pasujące wartości na podstawie jednego lub więcej warunków w pionie
Jeśli potrzebujesz Vlookup i zwrócić wszystkie pasujące wartości w pionie na podstawie określonych kryteriów, jak pokazano na poniższym zrzucie ekranu, kliknij tutaj, aby uzyskać szczegółowe rozwiązanie.
3.3.3 WYSZUKAJ.PIONOWO wszystkie pasujące wartości na podstawie jednego lub więcej warunków w jednej komórce
Jeśli chcesz Vlookup i zwrócić wiele dopasowanych wartości do pojedynczej komórki z określonym separatorem, nowa funkcja TEXTJOIN może pomóc szybko i łatwo rozwiązać to zadanie.
Uwagi:
- Funkcja TEXTJOIN jest dostępna tylko w programach Excel 2019, Excel 365 i nowszych wersjach.
- Jeśli używasz programu Excel 2016 i wcześniejszych wersji, użyj funkcji zdefiniowanej przez użytkownika w poniższym artykule:
- Vlookup, aby zwrócić wiele wartości w jednej komórce w programie Excel
3.4 WYSZUKAJ.PIONOWO, aby zwrócić cały wiersz dopasowanej komórki
W tej sekcji omówię, jak pobrać cały wiersz dopasowanej wartości za pomocą funkcji WYSZUKAJ.PIONOWO.
Krok 1: Zastosuj i wypełnij poniższą formułę
Skopiuj lub wpisz poniższą formułę do pustej komórki, w której chcesz wyprowadzić wynik, i naciśnij Wchodzę klucz, aby uzyskać pierwszą wartość. Następnie przeciągnij komórkę formuły w prawo, aż zostaną wyświetlone dane całego wiersza.
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
Wynik:
Teraz możesz zobaczyć, że zwracane są dane całego wiersza. Zobacz zrzut ekranu:
Uwaga: w powyższym wzorze:
- F2 jest wartością wyszukiwania, na podstawie której chcesz zwrócić cały wiersz;
- A1: D12 jest zakresem danych, z którego chcesz wyszukać szukaną wartość;
- A1 wskazuje pierwszy numer kolumny w twoim zakresie danych;
- FAŁSZYWY wskazuje dokładne wyszukiwanie.
Porady:
- Jeśli na podstawie dopasowanej wartości zostanie znalezionych wiele wierszy, aby zwrócić wszystkie odpowiednie wiersze, zastosuj poniższą formułę, a następnie naciśnij Ctrl + Shift + Enter klawisze razem, aby uzyskać pierwszy wynik. Następnie przeciągnij uchwyt wypełniania w prawo. Następnie przeciągnij uchwyt wypełniania w dół komórek, aby uzyskać wszystkie pasujące wiersze. Zobacz demo poniżej:
=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
3.5 Zagnieżdżone WYSZUKAJ.PIONOWO w Excelu
Czasami może być konieczne wyszukanie wartości, które są ze sobą powiązane w wielu tabelach. W takim przypadku możesz zagnieżdżać razem wiele funkcji WYSZUKAJ.PIONOWO, aby uzyskać ostateczną wartość.
Na przykład mam arkusz zawierający dwie oddzielne tabele. Pierwsza tabela zawiera listę wszystkich nazw produktów wraz z odpowiadającym im sprzedawcą. Druga tabela zawiera łączną sprzedaż każdego sprzedawcy. Teraz, jeśli chcesz znaleźć sprzedaż każdego produktu, jak pokazano na poniższym zrzucie ekranu, możesz zagnieździć funkcję WYSZUKAJ.PIONOWO, aby wykonać to zadanie.
Ogólna formuła zagnieżdżonej funkcji WYSZUKAJ.PIONOWO to:
Uwaga:
- lookup_value to wartość, której szukasz;
- Tabela_tablica1, Tabela_tablica2 to tabele, w których istnieje wartość wyszukiwania i wartość zwracana;
- numer_indeksu_kolumny1 wskazuje numer kolumny w pierwszej tabeli do wyszukiwania wspólnych danych pośrednich;
- numer_indeksu_kolumny2 wskazuje numer kolumny w drugiej tabeli, do której chcesz zwrócić pasującą wartość;
- 0 służy do dokładnego dopasowania.
Krok 1: Zastosuj i wypełnij poniższą formułę
Zastosuj następującą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania w dół do komórek, w których chcesz zastosować tę formułę.
=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)
Wynik:
Teraz otrzymasz wynik, jak pokazano na poniższym zrzucie ekranu:
Uwaga: w powyższym wzorze:
- G3 zawiera szukaną wartość;
- A3: B7, D3: E7 to zakresy tabeli, w których istnieje wartość wyszukiwania i wartość zwracana;
- 2 to numer kolumny w zakresie, z którego ma zostać zwrócona pasująca wartość.
- 0 wskazuje dokładną matematykę WYSZUKAJ.PIONOWO.
3.6 Sprawdź, czy wartość istnieje na podstawie danych listy w innej kolumnie
Funkcja WYSZUKAJ.PIONOWO może również pomóc w sprawdzeniu, czy istnieją wartości na podstawie listy danych w innej kolumnie. Na przykład, jeśli chcesz wyszukać nazwiska w kolumnie C i po prostu zwróć Tak lub Nie, jeśli nazwisko zostanie znalezione lub nie w kolumnie A, jak pokazano na poniższym zrzucie ekranu.
Krok 1: Zastosuj i wypełnij poniższą formułę
Zastosuj następującą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić tą formułą.
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")
Wynik:
Otrzymasz wynik tak, jak potrzebujesz, patrz zrzut ekranu:
Uwaga: w powyższym wzorze:
- C2 to wartość wyszukiwania, którą chcesz sprawdzić;
- A2: A10 jest listą zakresu, z którego należy sprawdzić, czy wyszukiwane wartości zostaną znalezione, czy nie;
- FAŁSZYWY wskazuje, aby uzyskać dokładne dopasowanie.
3.7 WYSZUKAJ.PIONOWO i zsumuj wszystkie pasujące wartości w wierszach lub kolumnach
Podczas pracy z danymi liczbowymi może być konieczne wyodrębnienie dopasowanych wartości z tabeli i zsumowanie liczb w kilku kolumnach lub wierszach. W tej sekcji przedstawiono niektóre formuły, które mogą pomóc w wykonaniu tego zadania.
3.7.1 WYSZUKAJ.PIONOWO i zsumuj wszystkie dopasowane wartości w wierszu lub wielu wierszach
Załóżmy, że masz listę produktów ze sprzedażą przez kilka miesięcy, jak pokazano na poniższym zrzucie ekranu. Teraz należy zsumować wszystkie zamówienia we wszystkich miesiącach na podstawie podanych produktów.
Krok 1: Zastosuj i wypełnij poniższą formułę
Skopiuj lub wprowadź następującą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klawisze razem, aby uzyskać pierwszy wynik. Następnie przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę do innych potrzebnych komórek.
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
Wynik:
Wszystkie wartości w rzędzie pierwszej pasującej wartości zostały zsumowane, patrz zrzut ekranu:
Uwaga: w powyższym wzorze:
- H2 to komórka zawierająca szukaną wartość;
- A2: F9 to zakres danych (bez nagłówków kolumn), który obejmuje wyszukiwaną wartość i dopasowane wartości;
- 2,3,4,5,6 {} to numery kolumn używane do obliczenia sumy zakresu;
- FAŁSZYWY wskazuje dokładne dopasowanie.
Porady: Jeśli chcesz zsumować wszystkie dopasowania w wielu wierszach, użyj następującej formuły:
-
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
3.7.2 WYSZUKAJ.PIONOWO i zsumuj wszystkie dopasowane wartości w kolumnie lub wielu kolumnach
Jeśli chcesz zsumować całkowitą wartość dla poszczególnych miesięcy, jak pokazano na zrzucie ekranu poniżej. Zwykła funkcja WYSZUKAJ.PIONOWO może ci nie pomóc, tutaj powinieneś zastosować razem funkcje SUMA, INDEKS i PODAJ.POZYCJĘ, aby utworzyć formułę.
Krok 1: Zastosuj następującą formułę
Zastosuj poniższą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę do innych komórek.
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
Wynik:
Teraz pierwsze dopasowane wartości na podstawie określonego miesiąca w kolumnie zostały zsumowane, patrz zrzut ekranu:
Uwaga: w powyższym wzorze:
- H2 to komórka zawierająca szukaną wartość;
- B1: F1 to nagłówki kolumn zawierające wartość wyszukiwania;
- B2: F9 to zakres danych zawierający wartości liczbowe, które mają zostać zsumowane.
Porady: Aby WYSZUKAJ.PIONOWO i zsumować wszystkie pasujące wartości w wielu kolumnach, należy użyć następującej formuły:
-
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
3.7.3 WYSZUKAJ.PIONOWO i sumuj pierwsze dopasowane lub wszystkie dopasowane wartości za pomocą potężnej funkcji
Być może powyższe formuły są dla Ciebie trudne do zapamiętania, w tym przypadku polecę potężną funkcję - Wyszukiwanie i suma of Kutools dla programu Excel, dzięki tej funkcji możesz przeglądać pionowo i sumować pierwsze pasujące lub wszystkie pasujące wartości w wierszach lub kolumnach tak łatwo, jak to możliwe.
- Kliknij Kutools > Super WYSZUKAJ > WYSZUKAJ i Suma aby włączyć tę funkcję.
- Następnie określ operacje w oknie dialogowym w zależności od potrzeb.
3.7.4 WYSZUKAJ.PIONOWO i zsumuj wszystkie pasujące wartości zarówno w wierszach, jak iw kolumnach
Jeśli chcesz zsumować wartości, gdy musisz dopasować zarówno kolumnę, jak i wiersz, na przykład, aby uzyskać całkowitą wartość produktu Sweter w miesiącu mar, jak pokazano na zrzucie ekranu.
Tutaj możesz użyć funkcji SUMPRODCT, aby wykonać to zadanie.
Zastosuj następującą formułę do komórki, a następnie naciśnij Wchodzę klucz, aby uzyskać wynik, patrz zrzut ekranu:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
Uwaga: W powyższym wzorze:
- B2: F9 to zakres danych zawierający wartości liczbowe, które chcesz zsumować;
- B1: F1 to nagłówki kolumn zawierające wyszukiwaną wartość, na podstawie której chcesz zsumować;
- I2 to wartość wyszukiwania w nagłówkach kolumn, których szukasz;
- A2: A9 to nagłówki wierszy zawierające wyszukiwaną wartość, na podstawie której chcesz zsumować;
- H2 to wyszukiwana wartość w nagłówkach wierszy, których szukasz.
3.8 WYSZUKAJ.PIONOWO, aby połączyć dwie tabele na podstawie kluczowych kolumn
W codziennej pracy, analizując dane, może być konieczne zebranie wszystkich niezbędnych informacji w jednej tabeli na podstawie jednej lub kilku kluczowych kolumn. Aby wykonać to zadanie, możesz użyć funkcji INDEKS i PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO.
3.8.1 WYSZUKAJ.PIONOWO, aby scalić dwie tabele na podstawie jednej kluczowej kolumny
Na przykład masz dwie tabele, pierwsza zawiera dane produktów i nazw, a druga zawiera dane produktów i zamówień. Teraz chcesz połączyć te dwie tabele, dopasowując wspólną kolumnę produktów w jedną tabelę.
Krok 1: Zastosuj i wypełnij poniższą formułę
Proszę zastosować następującą formułę do pustej komórki. Następnie przeciągnij uchwyt wypełniania w dół do komórek, w których chcesz zastosować tę formułę
=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))
Wynik:
Teraz otrzymasz scaloną tabelę z kolumną zamówienia łączącą się z pierwszą tabelą na podstawie danych z kolumny klucza.
Uwaga: W powyższym wzorze:
- A2 jest wartością wyszukiwania, której szukasz;
- F2: F8 to zakres danych, dla których chcesz zwrócić pasujące wartości;
- E2: E8 to zakres wyszukiwania zawierający wartość wyszukiwania.
3.8.2 WYSZUKAJ.PIONOWO, aby scalić dwie tabele na podstawie wielu kluczowych kolumn
Jeśli dwie tabele, które chcesz połączyć, mają wiele kolumn kluczowych, aby scalić tabele na podstawie tych wspólnych kolumn, wykonaj poniższe czynności.
Ogólna formuła to:
Uwaga:
- tabela_wyszukiwania czy zakres danych zawiera dane wyszukiwania i pasujące rekordy;
- szukana_wartość1 jest pierwszym kryterium, którego szukasz;
- szukany_zakres1 czy lista danych zawiera pierwsze kryterium;
- szukana_wartość2 to drugie kryterium, którego szukasz;
- szukany_zakres2 czy lista danych zawiera drugie kryterium;
- zwrot_numer_kolumny wskazuje numer kolumny w lookup_table, dla której ma zostać zwrócona pasująca wartość.
Krok 1: Zastosuj następującą formułę
Zastosuj poniższą formułę do pustej komórki, w której chcesz umieścić wynik, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą dopasowaną wartość, patrz zrzut ekranu:
=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)
Krok 2: Wypełnij formułę w innych komórkach
Następnie wybierz pierwszą komórkę formuły i przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek zgodnie z potrzebami:
3.9 WYSZUKAJ.PIONOWO dopasowywanie wartości w wielu arkuszach
Czy kiedykolwiek musiałeś wykonać WYSZUKAJ.PIONOWO w wielu arkuszach kalkulacyjnych w programie Excel? Na przykład, jeśli masz trzy arkusze z zakresami danych i chcesz pobrać określone wartości na podstawie kryteriów z tych arkuszy, możesz wykonać samouczek krok po kroku Wartości WYSZUKAJ.PIONOWO w wielu arkuszach do wykonania tego zadania.
Dopasowane wartości WYSZUKAJ.PIONOWO zachowują formatowanie komórek
Podczas wyszukiwania dopasowanych wartości oryginalne formatowanie komórek, takie jak kolor czcionki, kolor tła, format danych itp., nie zostanie zachowane. Aby zachować formatowanie komórki lub danych, w tej sekcji przedstawiono kilka sztuczek służących do rozwiązywania zadań.
4.1 WYSZUKAJ.PIONOWO dopasuj wartość i zachowaj kolor komórki, formatowanie czcionki
Jak wszyscy wiemy, normalna funkcja WYSZUKAJ.PIONOWO może pobrać pasującą wartość tylko z innego zakresu danych. Mogą jednak wystąpić sytuacje, w których chcesz uzyskać odpowiednią wartość wraz z formatowaniem komórki, na przykład kolorem wypełnienia, kolorem czcionki i stylem czcionki. W tej sekcji omówimy sposób pobierania pasujących wartości przy jednoczesnym zachowaniu formatowania źródłowego w programie Excel.
Wykonaj następujące kroki, aby wyszukać i zwrócić odpowiednią wartość wraz z formatowaniem komórki:
Krok 1: Skopiuj kod 1 do modułu kodu arkusza
- W arkuszu zawierającym dane, które chcesz WYSZUKAJ.PIONOWO, kliknij prawym przyciskiem myszy kartę arkusza i wybierz Wyświetl kod z menu kontekstowego. Zobacz zrzut ekranu:
- W otwartym Microsoft Visual Basic for Applications oknie, skopiuj poniższy kod VBA do okna Code.
- Kod VBA 1: WYSZUKAJ.PIONOWO, aby uzyskać formatowanie komórki wraz z wartością wyszukiwania
-
Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Range(xDic.Keys(I)).Font.FontStyle = _ Range(xDic.Items(I)).Font.FontStyle Range(xDic.Keys(I)).Font.Size = _ Range(xDic.Items(I)).Font.Size Range(xDic.Keys(I)).Font.Color = _ Range(xDic.Items(I)).Font.Color Range(xDic.Keys(I)).Font.Name = _ Range(xDic.Items(I)).Font.Name Range(xDic.Keys(I)).Font.Underline = _ Range(xDic.Items(I)).Font.Underline Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End Sub
Krok 2: Skopiuj kod 2 do okna modułu
- Jeszcze w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł, a następnie skopiuj poniższy kod VBA 2 do okna Module.
- Kod VBA 2: WYSZUKAJ.PIONOWO, aby uzyskać formatowanie komórki wraz z wartością wyszukiwania
-
Public xDic As New Dictionary Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = "" xDic.Add Application.Caller.Address, "" Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function
Krok 3: Wybierz opcję dla projektu VBA
- Po wpisaniu powyższych kodów kliknij Tools > Referencje Microsoft Visual Basic for Applications okno. Następnie sprawdź Środowisko wykonawcze skryptów firmy Microsoft Microsoft pole wyboru w Referencje - VBAProject Okno dialogowe. Zobacz zrzuty ekranu:
- Następnie kliknij OK aby zamknąć okno dialogowe, a następnie zapisz i zamknij okno kodu.
Krok 4: Wpisz formułę uzyskania wyniku
- Teraz wróć do arkusza, zastosuj następującą formułę. Następnie przeciągnij uchwyt wypełniania w dół, aby uzyskać wszystkie wyniki wraz z ich formatowaniem. Zobacz zrzut ekranu:
=LookupKeepFormat(E2,$A$1:$C$10,3)
Uwaga: w powyższym wzorze:
- E2 jest wartością, którą wyszukasz;
- A1: C10 to zakres tabeli;
- 3 to numer kolumny tabeli, z której chcesz pobrać dopasowaną wartość.
4.2 Zachowaj format daty ze zwróconej wartości WYSZUKAJ.PIONOWO
Podczas korzystania z funkcji WYSZUKAJ.PIONOWO do wyszukiwania i zwracania wartości w formacie daty zwrócony wynik może być wyświetlany jako liczba. Aby zachować format daty w zwróconym wyniku, należy umieścić funkcję WYSZUKAJ.PIONOWO w funkcji TEKST.
Krok 1: Zastosuj i wypełnij poniższą formułę
Proszę zastosować poniższą formułę do pustej komórki. Następnie przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek.
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")
Wynik:
Wszystkie dopasowane daty zostały zwrócone, jak pokazano na poniższym zrzucie ekranu:
Uwaga: W powyższym wzorze:
- E2 jest wartością wyszukiwania;
- A2: C9 jest zakresem wyszukiwania;
- 3 jest numerem kolumny, do której ma zostać zwrócona wartość;
- FAŁSZYWY wskazuje, aby uzyskać dokładne dopasowanie;
- mm/dd/yyy to format daty, który chcesz zachować.
4.3 Zwróć komentarz do komórki z WYSZUKAJ.PIONOWO
Czy kiedykolwiek potrzebowałeś pobrać zarówno pasujące dane komórki, jak i powiązany z nimi komentarz za pomocą funkcji WYSZUKAJ.PIONOWO w programie Excel, jak pokazano na poniższym zrzucie ekranu? Jeśli tak, funkcja zdefiniowana przez użytkownika podana poniżej może pomóc w wykonaniu tego zadania.
Krok 1: Skopiuj kod do modułu
- Przytrzymaj przycisk ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
- Kliknij wstawka > Moduł, a następnie skopiuj i wklej następujący kod w oknie modułu.
Kod VBA: Wyszukaj i zwróć pasującą wartość z komentarzem do komórki:Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant 'Updateby Extendoffice Application.Volatile Dim xRet As Variant 'could be an error Dim xCell As Range xRet = Application.Match(LookVal, FTable.Columns(1), FType) If IsError(xRet) Then VlookupComment = "Not Found" Else Set xCell = FTable.Columns(FColumn).Cells(1)(xRet) VlookupComment = xCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text End If End With End If End Function
- Następnie zapisz i zamknij okno kodu.
Krok 2: Wpisz formułę, aby uzyskać wynik
- Teraz wprowadź następującą formułę i przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek. Zwróci jednocześnie dopasowane wartości i komentarze, patrz zrzut ekranu:
=vlookupcomment(D2,$A$2:$B$9,2,FALSE)
Uwaga: W powyższym wzorze:
- D2 jest wartością wyszukiwania, którą chcesz zwrócić, odpowiadającą jej wartością;
- A2: B9 jest tabelą danych, której chcesz użyć;
- 2 jest numerem kolumny zawierającej dopasowaną wartość, którą chcesz zwrócić;
- FAŁSZYWY wskazuje, aby uzyskać dokładne dopasowanie.
4.4 Numery WYSZUKAJ.PIONOWO przechowywane jako tekst
Na przykład mam zakres danych, w których numer identyfikacyjny w oryginalnej tabeli jest w formacie liczbowym, a numer identyfikacyjny w komórkach wyszukiwania jest przechowywany jako tekst, podczas korzystania ze zwykłej funkcji WYSZUKAJ.PIONOWO możesz napotkać błąd #N/D. W takim przypadku, aby pobrać prawidłowe informacje, możesz zawinąć funkcje TEKST i WARTOŚĆ w funkcję WYSZUKAJ.PIONOWO. Poniżej znajduje się formuła, która to umożliwia:
Krok 1: Zastosuj i wypełnij poniższą formułę
Zastosuj następującą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę.
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))
Wynik:
Teraz otrzymasz poprawne wyniki, jak pokazano na poniższym zrzucie ekranu:
Uwagi:
- W powyższym wzorze:
- D2 jest wartością wyszukiwania, którą chcesz zwrócić, odpowiadającą jej wartością;
- A2: B8 jest tabelą danych, której chcesz użyć;
- 2 jest numerem kolumny zawierającej dopasowaną wartość, którą chcesz zwrócić;
- 0 wskazuje, aby uzyskać dokładne dopasowanie.
- Ta formuła działa również dobrze, jeśli nie masz pewności, gdzie masz liczby, a gdzie tekst.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Spis treści
- 1. Wprowadzenie do funkcji WYSZUKAJ.PIONOWO
- 2. Podstawowe przykłady WYSZUKAJ.PIONOWO
- 2.1 Dokładne i przybliżone Vlookup
- Dokładne dopasowanie
- Przybliżone dopasowanie
- 2.2 Funkcja wyszukiwania pionowego uwzględniająca wielkość liter
- 2.3 Vlookup od prawej do lewej
- 2.4 Vlookup drugi, n-ty lub ostatni pasująca wartość
- Druga lub n-ta pasująca wartość
- Ostatnia pasująca wartość
- 2.5 Vlookup między dwiema wartościami
- Używając formuły
- Korzystając z przydatnej funkcji - Kutools
- 2.6 Częściowe dopasowanie Vlookup
- 2.7 Vlookup z innego arkusza
- 2.8 Wyszukiwanie pionowe z innego skoroszytu
- 2.9 Napraw wartość błędu 0 lub #N/D w Vlookup
- 3. Zaawansowane przykłady WYSZUKAJ.PIONOWO
- 3.1 Wyszukiwanie dwukierunkowe
- 3.2 Wyszukiwanie pionowe na podstawie większej liczby kryteriów
- Używając formuł
- Korzystając z inteligentnej funkcji - Kutools
- 3.3 Vlookup wiele pasujących wartości
- Zwracaj wartości poziomo
- Zwracaj wartości w pionie
- Zwróć wartości do jednej komórki
- 3.4 Przeszukaj cały wiersz
- 3.5 Zagnieżdżone wyszukiwanie pionowe
- 3.6 Sprawdź, czy wartość istnieje
- 3.7 Vlookup i suma
- W rzędach
- w kolumnach
- Z potężną funkcją - Kutools
- Zarówno w wierszach, jak i kolumnach
- 3.8 Vlookup, aby scalić dwie tabele
- Według jednej kluczowej kolumny
- Według wielu kluczowych kolumn
- 3.9 Wyszukiwanie pionowe w wielu arkuszach
- 4. WYSZUKAJ.PIONOWO i zachowaj formatowanie komórek
- 4.1 Zachowaj kolor i formatowanie czcionek
- 4.2 Zachowaj format daty
- 4.3 Zachowaj komentarz do komórki
- 4.4 Liczby zapisane jako tekst
- Najlepsze narzędzia biurowe
- Komentarze