Uwaga: inne języki witryny są przetłumaczone przez Google. Powrót do angielskiego
Zaloguj Się  \/ 
x
or
x
Zarejestruj się  \/ 
x

or

WYSZUKAJ.PIONOWO z kilkoma podstawowymi i zaawansowanymi przykładami w programie Excel

W programie Excel funkcja WYSZUKAJ.PIONOWO jest potężną funkcją dla większości użytkowników programu Excel, która służy do wyszukiwania wartości w lewym skrajnym zakresie danych i zwracania pasującej wartości w tym samym wierszu z kolumny określonej na poniższym zrzucie ekranu . W tym samouczku omówiono sposób korzystania z funkcji WYSZUKAJ.PIONOWO z kilkoma podstawowymi i zaawansowanymi przykładami w programie Excel.

Spis treści:

1. Wprowadzenie do funkcji WYSZUKAJ.PIONOWO - składnia i argumenty

2. Podstawowe przykłady WYSZUKAJ.PIONOWO

3. Zaawansowane przykłady WYSZUKAJ.PIONOWO

4. Dopasowane wartości WYSZUKAJ.PIONOWO zachowują formatowanie komórek

5. Pobierz przykładowe pliki WYSZUKAJ.PIONOWO


Wprowadzenie do funkcji WYSZUKAJ.PIONOWO - składnia i argumenty

Składnia funkcji WYSZUKAJ.PIONOWO:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

argumenty:

Lookup_value: Wartość, którą chcesz przeszukać. Musi znajdować się w pierwszej kolumnie zakresu table_array.

Table_array: Zakres danych lub tabela, w której znajduje się kolumna wartości wyszukiwania i kolumna wartości wyniku.

Col_index_num: Liczba kolumn, z których zostanie zwrócona dopasowana wartość. Zaczyna się od 1 od skrajnej lewej kolumny w tablicy tabeli.

Zakres wyszukiwania: Wartość logiczna, która określa, czy funkcja WYSZUKAJ.PIONOWO zwróci dokładne dopasowanie, czy przybliżone dopasowanie.

  • Przybliżone dopasowanie - 1 / PRAWDA: Jeśli dokładne dopasowanie nie zostanie znalezione, formuła wyszukuje najbliższe dopasowanie - największą wartość, która jest mniejsza niż wartość wyszukiwania. W takim przypadku należy posortować kolumnę wyszukiwania w porządku rosnącym.
    = WYSZUKAJ.PIONOWO (wartość_wyszukiwania, tablica_tabeli, indeks_kolumny, PRAWDA)
    = WYSZUKAJ.PIONOWO (wartość_wyszukiwania, tablica_tabeli, indeks_kolumny, 1)
  • 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.
    = WYSZUKAJ.PIONOWO (wartość_wyszukiwania, tablica_tabeli, indeks_kolumny, FAŁSZ)
    = WYSZUKAJ.PIONOWO (wartość_wyszukiwania, tablica_tabeli, indeks_kolumny, 0)

Uwagi:

  • 1. Funkcja Vlookup szuka wartości tylko od lewej do prawej.
  • 2. Jeśli istnieje wiele pasujących wartości opartych na wyszukanej wartości, tylko pierwsza dopasowana zostanie zwrócona za pomocą funkcji Vlookup.
  • 3. Zwróci wartość błędu # N / D, jeśli nie można znaleźć wartości wyszukiwania.

Podstawowe przykłady funkcji WYSZUKAJ.PIONOWO

1. Wykonaj dokładne dopasowanie Vlookup i przybliżone dopasowanie Vlookup

Wykonaj dokładne dopasowanie Vlookup w programie Excel

Zwykle, jeśli szukasz dokładnego dopasowania za pomocą funkcji Vlookup, wystarczy użyć FALSE w ostatnim argumencie.

Na przykład, aby uzyskać odpowiednie wyniki matematyczne na podstawie określonych numerów identyfikacyjnych, wykonaj następujące czynności:

1. Zastosuj poniższą formułę do pustej komórki, w której chcesz uzyskać wynik:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić tę formułą, a otrzymasz wyniki zgodnie z potrzebami. Zobacz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze F2 to wartość, którą chcesz zwrócić jej pasującą wartość, A2: D7 to tablica tabeli, liczba 3 to numer kolumny, z której zwracana jest dopasowana wartość, a rozszerzenie FAŁSZYWY odnosi się do dokładnego dopasowania.
  • 2. Jeśli wartość kryterium nie zostanie znaleziona w zakresie danych, zostanie wyświetlona wartość błędu # N / A.

Wykonaj przybliżone dopasowanie Vlookup w programie Excel

Przybliżone dopasowanie jest przydatne do wyszukiwania wartości w zakresach danych. Jeśli dokładne dopasowanie nie zostanie znalezione, przybliżona funkcja Vlookup zwróci największą wartość, która jest mniejsza niż wartość wyszukiwania.

Na przykład, jeśli masz następujące dane zakresu, określone zamówienia nie znajdują się w kolumnie Zamówienia, jak uzyskać najbliższy rabat w kolumnie B?

1. Wprowadź następującą formułę do komórki, w której chcesz umieścić wynik:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, aby zastosować tę formułę, a otrzymasz przybliżone dopasowania na podstawie podanych wartości, patrz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze D2 to wartość, dla której chcesz zwrócić jej względne informacje, A2: B9 to zakres danych, liczba 2 wskazuje numer kolumny, do której zwracana jest dopasowana wartość, a rozszerzenie TRUE odnosi się do przybliżonego dopasowania.
  • 2. Przybliżone dopasowanie zwróci największą wartość, która jest mniejsza niż określona wartość wyszukiwania.
  • 3. Aby użyć funkcji Vlookup w celu uzyskania przybliżonej wartości dopasowania, należy posortować skrajną lewą kolumnę zakresu danych w porządku rosnącym, w przeciwnym razie zwróci ona nieprawidłowy wynik.

2. Wykonaj przeglądanie wielkości liter w programie Excel

Domyślnie funkcja Vlookup przeprowadza wyszukiwanie bez rozróżniania wielkości liter, co oznacza, że ​​traktuje małe i wielkie litery jako identyczne. Czasami może być konieczne wyszukanie wielkości liter w programie Excel, funkcje indeksu, dopasowania i dokładności lub funkcje wyszukiwania i dokładności mogą wyświadczyć ci przysługę.

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.

Formuła 1: Korzystanie z funkcji EXACT, INDEX, MATCH

1. Wprowadź lub skopiuj poniższą formułę tablicową do pustej komórki, w której chcesz uzyskać wynik:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Następnie naciśnij Ctrl + Shift + Enter klawisze jednocześnie, aby uzyskać pierwszy wynik, a następnie wybierz komórkę z formułą, przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić tę formułą, a następnie uzyskasz poprawne wyniki, których potrzebujesz. Zobacz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze A2: A10 to kolumna zawierająca określone wartości, w których chcesz sprawdzić, F2 jest wartością wyszukiwania, C2: C10 to kolumna, z której zostanie zwrócony wynik.
  • 2. Jeśli zostanie znalezionych wiele dopasowań, ta formuła zawsze zwróci pierwsze dopasowanie.

Formuła 2: Korzystanie z funkcji wyszukiwania i dokładności

1. Zastosuj poniższą formułę do pustej komórki, w której chcesz uzyskać wynik:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, które chcesz skopiować tę formułę, a otrzymasz dopasowane wartości z rozróżnianiem wielkości liter, jak pokazano na poniższym zrzucie ekranu:

Uwagi:

  • 1. W powyższym wzorze A2: A10 to kolumna zawierająca określone wartości, w których chcesz sprawdzić, F2 jest wartością wyszukiwania, C2: C10 to kolumna, z której zostanie zwrócony wynik.
  • 2. Jeśli zostanie znalezionych wiele dopasowań, ta formuła zawsze zwróci ostatnie dopasowanie.

3. Przeglądaj wartości od prawej do lewej w programie Excel

Funkcja Vlookup zawsze wyszukuje wartość w skrajnej lewej kolumnie zakresu danych i zwraca odpowiednią wartość z kolumny po prawej stronie. Jeśli chcesz wykonać odwrotne Vlookup, co oznacza wyszukanie określonej wartości po prawej stronie i zwrócenie jej odpowiedniej wartości w lewej kolumnie, jak pokazano na zrzucie ekranu:

Kliknij, aby poznać szczegółowe informacje krok po kroku o tym zadaniu…


4. Wyszukaj drugą, n-tą lub ostatnią pasującą wartość w programie Excel

Zwykle, jeśli podczas korzystania z funkcji Vlookup zostanie znalezionych wiele pasujących wartości, zostanie zwrócony tylko pierwszy dopasowany rekord. W tej sekcji omówię, jak uzyskać drugą, n-tą lub ostatnią pasującą wartość za pomocą funkcji Vlookup.

Wyszukaj i zwróć drugą lub n-tą pasującą wartość

Załóżmy, że masz listę nazwisk w kolumnie A, kurs szkoleniowy, który kupili w kolumnie B, a teraz szukasz drugiego lub ntego kursu zakupionego przez danego klienta. Zobacz zrzut ekranu:

1. Aby uzyskać drugą lub n-tą pasującą wartość na podstawie podanych kryteriów, zastosuj następującą formułę tablicową do pustej komórki:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie wybierz komórkę formuły, przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić tę formułą, a wszystkie drugie dopasowane wartości na podstawie podanych nazw zostały wyświetlone od razu, patrz zrzut ekranu:

Uwaga:

  • W tym wzorze A2: A14 to zakres zawierający wszystkie wartości do wyszukiwania, B2: B14 to zakres pasujących wartości, z których chcesz powrócić, E2 jest wartością wyszukiwania i ostatnią liczbą 2 wskazuje drugą dopasowaną wartość, którą chcesz uzyskać, jeśli chcesz zwrócić trzecią pasującą wartość, wystarczy zmienić ją na 3 w razie potrzeby.

Wyszukaj 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 i zwróć ostatnią pasującą wartość samouczek może pomóc w uzyskaniu szczegółowych informacji o ostatniej pasującej wartości.


5. Wyszukaj pasujące 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 poniżej, w tym przypadku możesz użyć funkcji WYSZUKAJ i posortowanej tabeli.

Wyszukaj pasujące wartości między dwiema podanymi wartościami lub datami za pomocą formuły

1. Po pierwsze, oryginalna tabela powinna być posortowanym zakresem danych. Następnie skopiuj lub wprowadź następującą formułę do pustej komórki:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Następnie przeciągnij uchwyt wypełniania, aby wypełnić tę formułę do innych potrzebnych komórek, a teraz otrzymasz wszystkie dopasowane rekordy na podstawie podanej wartości, patrz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze A2: A6 to zakres mniejszych wartości i B2: B6 to zakres większych liczb w zakresie danych, rozszerzenie E2 to dana wartość, którą chcesz uzyskać odpowiadającą jej wartość, C2: C6 to dane kolumny, z których chcesz wyodrębnić.
  • 2. 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:

Wyszukaj dopasowanie wartości między dwiema podanymi wartościami lub datami z przydatną funkcją

Jeśli boli cię powyższa formuła, tutaj przedstawię proste narzędzie - Kutools dla programu Excel, Z siedzibą w WYSZUKAJ między dwiema wartościami możesz zwrócić odpowiedni element na podstawie określonej wartości lub daty między dwiema wartościami lub datami bez pamiętania żadnej formuły.   Kliknij, aby pobrać teraz Kutools dla programu Excel!


6. Używanie symboli wieloznacznych do częściowych dopasowań w funkcji Vlookup

W programie Excel symbole wieloznaczne mogą być używane w funkcji Vlookup, co powoduje częściowe dopasowanie wartości wyszukiwania. Na przykład możesz użyć Vlookup, aby zwrócić dopasowaną wartość z tabeli na podstawie części wartości wyszukiwania.

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?

1. Normalna funkcja Vlookup nie działa poprawnie, musisz połączyć tekst lub odwołanie do komórki za pomocą symbolu wieloznacznego, skopiuj lub wprowadź następującą formułę do pustej komórki:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Następnie przeciągnij uchwyt wypełniania, aby wypełnić tę formułę do innych potrzebnych komórek, a wszystkie dopasowane wyniki zostały zwrócone, jak pokazano na poniższym zrzucie ekranu:

Uwagi:

  • 1. W powyższym wzorze E2 & „*” jest wartością wyszukiwania, wartością w E2 oraz * symbol wieloznaczny („*” oznacza dowolny znak lub dowolne znaki), A2: C11 to zakres wyszukiwania, liczba 3 kolumna zawierająca wartość do zwrócenia.
  • 2. Vlookup podczas używania symboli wieloznacznych, należy ustawić tryb dopasowania ścisłego z FALSE lub 0 dla ostatniego argumentu funkcji Vlookup.

Porady:

1. Znajdź i zwróć pasujące wartości kończące się określoną wartością, zastosuj następującą formułę: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. 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, za lub w środku ciągu tekstowego, wystarczy połączyć dwa * znaki wokół odwołania do komórki lub tekstu. Proszę zrobić z tą formułą: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Przeglądaj wartości z innego arkusza

Zwykle może zajść potrzeba pracy z więcej niż jednym arkuszem roboczym, funkcja Vlookup może być używana 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:

1. Wprowadź lub skopiuj poniższą formułę do pustej komórki, w której chcesz uzyskać dopasowane elementy:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę, a otrzymasz odpowiednie wyniki zgodnie z potrzebami, patrz zrzut ekranu:

Uwaga: W powyższym wzorze:

  • A2 reprezentuje wartość wyszukiwania;
  • Karta katalogowa to nazwa arkusza roboczego, z którego chcesz wyszukać dane, (jeśli nazwa arkusza zawiera spacje lub znaki interpunkcyjne, powinieneś ująć pojedyncze cudzysłowy wokół nazwy arkusza, w przeciwnym razie możesz bezpośrednio użyć nazwy arkusza, takiej jak = WYSZUKAJ.PIONOWO (A2, Arkusz danych! 2 $ A $: 15,3,0 $ C $));
  • A2: C15 to zakres danych w Karcie Danych, w którym szukamy danych;
  • liczba 3 to numer kolumny zawierającej dopasowane dane, z których chcesz wrócić.

8. Przeglądaj wartości z innego skoroszytu

W tej sekcji omówiono wyszukiwanie i zwracanie pasujących wartości z innego skoroszytu za pomocą funkcji Vlookup.

Na przykład pierwszy skoroszyt zawiera listy produktów i kosztów, teraz chcesz wyodrębnić odpowiedni koszt w drugim skoroszycie na podstawie pozycji produktu, jak pokazano poniżej.

1. Aby pobrać względny koszt z innego skoroszytu, najpierw 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:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Następnie przeciągnij i skopiuj tę formułę do innych potrzebnych komórek, zobacz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze:
    B2 reprezentuje wartość wyszukiwania;
    [Lista produktów.xlsx] Arkusz1 to nazwa skoroszytu i arkusza, z którego chcesz wyszukać dane, (odniesienie do skoroszytu jest zawarte w nawiasach kwadratowych, a cały skoroszyt + arkusz jest ujęty w pojedyncze cudzysłowy);
    A2: B6 to zakres danych w arkuszu innego skoroszytu, w którym szukamy danych;
    liczba 2 to numer kolumny zawierającej dopasowane dane, z których chcesz wrócić.
  • 2. Jeśli skoroszyt wyszukiwania jest zamknięty, pełna ścieżka do skoroszytu wyszukiwania zostanie pokazana w formule, jak pokazano na poniższym zrzucie ekranu:

9. Wyszukaj i zwróć pusty lub określony tekst zamiast wartości błędu 0 lub # N / D

Zwykle po zastosowaniu funkcji vlookup w celu zwrócenia odpowiedniej wartości, jeśli pasująca komórka jest pusta, zwróci ona 0, a jeśli pasująca wartość nie zostanie znaleziona, pojawi się błąd nr N / A, jak pokazano na zrzucie ekranu. Zamiast wyświetlać wartość 0 lub # N / A z pustą komórką lub inną wartością, którą lubisz, to Vlookup, aby zwrócić pustą lub określoną wartość zamiast 0 lub nie dotyczy samouczek może zrobić ci przysługę krok po kroku.


Zaawansowane przykłady WYSZUKAJ.PIONOWO

1. Dwukierunkowe wyszukiwanie z funkcją Vlookup (Vlookup w wierszu i kolumnie)

Czasami może być konieczne wykonanie wyszukiwania dwuwymiarowego, co oznacza jednoczesne przeglądanie w wierszu i kolumnie. Powiedzmy, że jeśli masz następujący zakres danych, a teraz możesz potrzebować uzyskać wartość dla określonego produktu w określonym kwartale. Ta sekcja przedstawi formułę radzenia sobie z tą pracą w programie Excel.

Formuła 1: Korzystanie z funkcji WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ

W programie Excel możesz użyć kombinacji funkcji WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ, aby przeprowadzić wyszukiwanie dwukierunkowe, zastosuj następującą formułę do pustej komórki, a następnie naciśnij wchodzić aby uzyskać wynik.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Uwaga: W powyższym wzorze:

  • H1: wartość wyszukiwania w kolumnie, na podstawie której chcesz uzyskać odpowiednią wartość;
  • A2: E6: zakres danych, w tym nagłówki wierszy;
  • H2: wartość wyszukiwania w wierszu, na podstawie którego chcesz uzyskać odpowiednią wartość;
  • A1: E1: komórki nagłówków kolumn.

Formuła 2: Korzystanie z funkcji INDEKS i PODAJ.POZYCJĘ

Oto inna formuła, która również może pomóc w przeprowadzeniu wyszukiwania dwuwymiarowego. Zastosuj poniższą formułę, a następnie naciśnij wchodzić aby uzyskać żądany wynik.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Uwaga: W powyższym wzorze:

  • B2: E6: zakres danych, z którego ma zostać zwrócony dopasowany element;
  • H1: wartość wyszukiwania w kolumnie, na podstawie której chcesz uzyskać odpowiednią wartość;
  • A2: A6: nagłówki wierszy zawierają produkt, którego szukasz.
  • H2: wartość wyszukiwania w wierszu, na podstawie którego chcesz uzyskać odpowiednią wartość;
  • B1: E1: nagłówki kolumn zawierają kwartał, którego chcesz szukać.

2. Wyszukaj dopasowaną wartość na podstawie co najmniej dwóch kryteriów

Wyszukiwanie wartości dopasowania na podstawie jednego kryterium jest łatwe, ale co możesz zrobić, jeśli masz dwa lub więcej kryteriów? Funkcje WYSZUKAJ lub PODAJ. 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.

Formuła 1: Korzystanie z funkcji WYSZUKAJ

Zastosuj poniższą formułę do komórki, w której chcesz uzyskać wynik, a następnie naciśnij klawisz Enter, zobacz zrzut ekranu:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Uwagi:

  • 1. W powyższym wzorze:
    A2: A12 = G1: oznacza przeszukiwanie kryteriów G1 w zakresie A2: A12;
    B2: B12 = G2: oznacza wyszukiwanie kryteriów G2 w zakresie B2: B12;
    D2: D12: zakres, dla którego ma zostać zwrócona odpowiednia wartość.
  • 2. Jeśli masz więcej niż dwa kryteria, wystarczy dołączyć pozostałe kryteria do formuły, takie jak: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formuła 2: Korzystanie z funkcji INDEKST i DOPASUJ

Do zwrócenia dopasowanej wartości na podstawie wielu kryteriów można również użyć kombinacji funkcji Index i Match. Skopiuj lub wprowadź następującą formułę:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Następnie naciśnij kombinację klawiszy Ctrl + Shift + Enter, aby uzyskać odpowiednią wartość. Zobacz zrzut ekranu:

Uwagi:

  • 1. W powyższym wzorze:
    A2: A12 = G1: oznacza przeszukiwanie kryteriów G1 w zakresie A2: A12;
    B2: B12 = G2: oznacza wyszukiwanie kryteriów G2 w zakresie B2: B12;
    D2: D12: zakres, dla którego ma zostać zwrócona odpowiednia wartość.
  • 2. Jeśli masz więcej niż dwa kryteria, wystarczy dołączyć nowe kryteria do formuły, takie jak: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup, aby zwrócić wiele pasujących wartości z co najmniej jednym warunkiem

W programie Excel funkcja Vlookup wyszukuje wartość i zwraca pierwszą pasującą wartość tylko wtedy, gdy znaleziono wiele odpowiadających wartości. Czasami możesz chcieć zwrócić wszystkie odpowiadające wartości w wierszu, kolumnie lub w pojedynczej komórce. W tej sekcji omówiono, jak zwrócić wiele pasujących wartości z co najmniej jednym warunkiem w skoroszycie.

Przeglądaj w poziomie wszystkie pasujące wartości w oparciu o jeden lub więcej warunków

Przeglądaj w poziomie wszystkie pasujące wartości na podstawie jednego warunku:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie jednej określonej wartości w poziomie, ogólna formuła to:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Uwagi: m to numer wiersza pierwszej komórki w zakresie zwracanym minus 1.
      n to numer kolumny pierwszej komórki z formułą minus 1.

1. Zastosuj poniższą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą dopasowaną wartość, patrz zrzut ekranu:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. A następnie wybierz pierwszą komórkę formuły i przeciągnij uchwyt wypełniania do odpowiednich komórek, aż zostanie wyświetlona pusta komórka, a wszystkie odpowiednie elementy zostaną wyodrębnione, patrz zrzut ekranu:

Porady:

Jeśli na zwróconej liście znajdują się zduplikowane dopasowane wartości, aby zignorować duplikaty, użyj tych formuł, a następnie naciśnij wchodzić aby otrzymać pierwszy wynik: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Kontynuuj wprowadzanie tej formuły: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") do komórki obok pierwszego wyniku, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać drugi wynik, a następnie przeciągnij tę formułę do odpowiednich komórek, aby uzyskać wszystkie inne dopasowane wartości, aż wyświetli się pusta komórka, patrz zrzut ekranu:


Przeglądaj w poziomie wszystkie pasujące wartości na podstawie co najmniej dwóch warunków:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie bardziej szczegółowych wartości w poziomie, ogólna formuła jest następująca:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Uwagi: m to numer wiersza pierwszej komórki w zakresie zwracanym minus 1.
      n to numer kolumny pierwszej komórki z formułą minus 1.

1. Zastosuj następującą formułę do pustej komórki, w której chcesz wyprowadzić wynik:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Następnie wybierz komórkę formuły i przeciągnij uchwyt wypełniania do właściwych komórek, aż wyświetlą się puste komórki, a wszystkie dopasowane wartości w oparciu o określone kryteria zostaną zwrócone, patrz zrzut ekranu:

Uwagi: Aby uzyskać więcej kryteriów, wystarczy dołączyć lookup_value i lookup_range do formuły, na przykład: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Wyszukaj w pionie wszystkie pasujące wartości w oparciu o jeden lub więcej warunków

Przeglądaj w pionie wszystkie pasujące wartości na podstawie jednego warunku:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie jednej określonej wartości w pionie, ogólna formuła to:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Uwagi: m to numer wiersza pierwszej komórki w zakresie zwracanym minus 1.
      n to numer wiersza pierwszej komórki formuły minus 1.

1. Skopiuj lub wpisz następującą formułę do komórki, w której chcesz uzyskać wynik, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą dopasowaną wartość, patrz zrzut ekranu:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Następnie wybierz pierwszą komórkę formuły i przeciągnij uchwyt wypełniania w dół do innych komórek, aż zostanie wyświetlona pusta komórka, a wszystkie odpowiednie elementy zostaną wymienione w kolumnie, patrz zrzut ekranu:

Porady:

Aby zignorować duplikaty w zwróconych pasujących wartościach, użyj następujących formuł: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszą dopasowaną wartość, a następnie przeciągnij tę komórkę formuły w dół do innych komórek, aż wyświetli się pusta komórka, a otrzymasz wynik, jakiego potrzebujesz:


Wyszukaj w pionie wszystkie pasujące wartości na podstawie co najmniej dwóch warunków:

Aby Vlookup i zwrócić wszystkie pasujące wartości na podstawie bardziej szczegółowych wartości w pionie, ogólna formuła to:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Uwagi: m to numer wiersza pierwszej komórki w zakresie zwracanym minus 1.
      n to numer wiersza pierwszej komórki formuły minus 1.

1. Skopiuj poniższą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy dopasowany element.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Następnie przeciągnij komórkę formuły w dół do innych komórek, aż zostanie wyświetlona pusta komórka, zobacz zrzut ekranu:

Uwagi: Aby uzyskać więcej kryteriów, wystarczy dołączyć lookup_value i lookup_range do formuły, na przykład: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Wyszukaj wszystkie pasujące wartości na podstawie co najmniej dwóch warunków w jednej komórce

Jeśli chcesz Vlookup i zwrócić wiele dopasowanych wartości do jednej komórki z określonym separatorem, nowa funkcja TEXTJOIN może pomóc Ci szybko i łatwo rozwiązać to zadanie.

Wyszukaj wszystkie pasujące wartości na podstawie jednego warunku w jednej komórce:

Zastosuj poniższą prostą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać wynik:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Porady:

Aby zignorować duplikaty w zwróconych pasujących wartościach, użyj następujących formuł: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Wyszukaj wszystkie pasujące wartości na podstawie dwóch lub więcej warunków w jednej komórce:

Aby poradzić sobie z wieloma warunkami podczas zwracania wszystkich pasujących wartości do jednej komórki, zastosuj poniższą formułę, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać wynik:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Uwagi:

1. Funkcja TEXTJOIN jest dostępna tylko w programie Excel 2019 i Office 365.

2. Jeśli korzystasz z programu Excel 2016 i wcześniejszych wersji, użyj funkcji zdefiniowanej przez użytkownika z poniższych artykułów:


4. Vlookup, aby zwrócić cały lub cały wiersz dopasowanej komórki

W tej sekcji omówię, jak pobrać cały wiersz dopasowanej wartości za pomocą funkcji Vlookup.

1. Skopiuj lub wpisz poniższą formułę w pustej komórce, w której chcesz wyprowadzić wynik, i naciśnij wchodzić klucz, aby uzyskać pierwszą wartość.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Następnie przeciągnij komórkę formuły na prawą stronę, aż zostaną wyświetlone dane całego wiersza, patrz zrzut ekranu:

Uwagi: W powyższym wzorze, F2 to wartość wyszukiwania, na podstawie której chcesz zwrócić cały wiersz, A1: D12 to zakres danych, którego chcesz użyć, A1 wskazuje numer pierwszej kolumny w zakresie danych.

Porady:

Jeśli na podstawie dopasowanej wartości znaleziono wiele wierszy, aby zwrócić wszystkie odpowiednie wiersze, zastosuj następującą formułę: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt wypełniania w prawo do komórek, patrz zrzut ekranu:

A następnie przeciągnij uchwyt wypełniania w dół po komórkach, aby uzyskać wszystkie pasujące wiersze, jak pokazano na zrzucie ekranu:


5. Wykonaj wiele funkcji Vlookup (zagnieżdżonych Vlookup) w programie Excel

Czasami możesz chcieć wyszukać wartości w wielu tabelach, jeśli którakolwiek z tabel zawiera daną wartość wyszukiwania, jak pokazano na poniższym zrzucie ekranu, w tym przypadku możesz połączyć jedną lub więcej funkcji Vlookup razem z funkcją IFERROR, aby wykonać wielokrotne wyszukiwanie.

Ogólny wzór zagnieżdżonej funkcji Vlookup to:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Uwaga:

  • lookup_value: wartość, której szukasz;
  • Table1, Table2, Table3, ...: tabele, w których istnieje szukana wartość i zwracana wartość;
  • col: numer kolumny w tabeli, z której ma zostać zwrócona pasująca wartość.
  • 0: Służy do dokładnego dopasowania.

1. Zastosuj następującą formułę do pustej komórki, w której chcesz umieścić wynik:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę, a wszystkie dopasowane wartości zostały zwrócone, jak pokazano na poniższym zrzucie ekranu:

Uwagi:

  • 1. W powyższym wzorze J3 to wartość, której szukasz; A3: B7, D3: E7, G3: H7 to zakresy tabel, w których istnieje szukana wartość i zwracana wartość; Numer 2 to numer kolumny w zakresie, z którego ma zostać zwrócona pasująca wartość.
  • 2. Jeśli nie można znaleźć wartości wyszukiwania, wyświetlana jest wartość błędu. Aby zastąpić błąd czytelnym tekstem, użyj następującego wzoru: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Wyszukaj, aby sprawdzić, czy wartość istnieje na podstawie danych listy w innej kolumnie

Funkcja Vlookup może również pomóc Ci sprawdzić, czy istnieją wartości na podstawie innej listy, na przykład, jeśli chcesz wyszukać nazwy w kolumnie C i po prostu zwrócić Tak lub Nie, jeśli nazwa została znaleziona lub nie w kolumnie A, jak na zrzucie ekranu pokazane.

1. Zastosuj następującą formułę do pustej komórki:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, które chcesz wypełnić tę formułę, a otrzymasz wynik, jakiego potrzebujesz, patrz zrzut ekranu:

Uwagi: W powyższym wzorze, C2 to wartość wyszukiwania, którą chcesz sprawdzić; A2: A10 jest listą zakresu, z którego zostaną znalezione wartości wyszukiwania; numer 1 to numer kolumny, z której chcesz pobrać wartość w swoim zakresie.


7. Wyszukaj i zsumuj wszystkie dopasowane wartości w wierszach lub kolumnach

Jeśli pracujesz z danymi liczbowymi, czasami podczas wyodrębniania dopasowanych wartości z tabeli może być konieczne zsumowanie liczb w kilku kolumnach lub wierszach. W tej sekcji przedstawimy kilka formuł, aby zakończyć tę pracę w programie Excel.

Wyszukaj i zsumuj wszystkie dopasowane wartości w jednym lub wielu wierszach

Załóżmy, że masz listę produktów ze sprzedażą przez kilka miesięcy, jak pokazano na poniższym zrzucie ekranu, teraz musisz zsumować wszystkie zamówienia ze wszystkich miesięcy na podstawie podanych produktów.

Wyszukaj i zsumuj pierwsze dopasowane wartości z rzędu:

1. Skopiuj lub wprowadź następującą formułę do pustej komórki, a następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Następnie przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę do innych potrzebnych komórek, a wszystkie wartości w wierszu pierwszej pasującej wartości zostały zsumowane, patrz zrzut ekranu:

Uwagi: W powyższym wzorze: H2 to komórka zawierająca szukaną wartość; A2: F9 to zakres danych (bez nagłówków kolumn), który zawiera szukaną wartość i dopasowane wartości; Numer 2,3,4,5,6 {} to numery kolumn używane do obliczania całkowitego zakresu.


Wyszukaj i zsumuj wszystkie dopasowane wartości w wielu wierszach:

Powyższa formuła może sumować wartości w wierszu tylko dla pierwszej dopasowanej wartości. Jeśli chcesz zsumować wszystkie dopasowania w wielu wierszach, użyj poniższej formuły, a następnie przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę, a otrzymasz pożądany wynik, patrz zrzut ekranu:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

Uwagi: W powyższym wzorze: H2 jest wartością wyszukiwania, której szukasz; A2: A9 to nagłówki wierszy zawierające wartość wyszukiwania; B2: F9 zakres danych wartości liczbowych, które chcesz zsumować.


Wyszukaj i zsumuj wszystkie dopasowane wartości w jednej lub wielu kolumnach

Wyszukaj i zsumuj pierwsze dopasowane wartości w kolumnie:

Jeśli chcesz zsumować całkowitą wartość dla poszczególnych miesięcy, jak pokazano na zrzucie ekranu poniżej.

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, teraz pierwsze dopasowane wartości na podstawie określonego miesiąca w kolumnie zostały zsumowane, patrz zrzut ekranu:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Uwagi: W powyższym wzorze: H2 jest wartością wyszukiwania, której szukasz; B1: F1 to nagłówki kolumn zawierające wartość wyszukiwania; B2: F9 zakres danych wartości liczbowych, które chcesz zsumować.


Przejrzyj i zsumuj wszystkie dopasowane wartości w wielu kolumnach:

Aby przejrzeć i zsumować wszystkie dopasowane wartości w wielu kolumnach, należy użyć następującej formuły:

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

Uwagi: W powyższym wzorze: H2 jest wartością wyszukiwania, której szukasz; B1: F1 to nagłówki kolumn zawierające wartość wyszukiwania; B2: F9 zakres danych wartości liczbowych, które chcesz zsumować.


Przeglądaj i sumuj pierwsze dopasowane lub wszystkie dopasowane wartości za pomocą potężnej funkcji

Może powyższe formuły są trudne do zapamiętania, w tym przypadku polecę przydatną funkcję - Wyszukiwanie i suma of Kutools dla programu Excel, dzięki tej funkcji możesz uzyskać wynik tak łatwo, jak to tylko możliwe.    Kliknij, aby pobrać teraz Kutools dla programu Excel!


Wyszukaj i zsumuj wszystkie dopasowane wartości zarówno w wierszach, jak i 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.

Zastosuj następującą formułę do komórki, a następnie naciśnij klawisz Enter, aby uzyskać wynik, patrz zrzut ekranu:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Uwagi: W powyższym wzorze: B2: F9 to zakres danych wartości liczbowych, które chcesz zsumować; B1: F1 is nagłówki kolumn zawierają wartość wyszukiwania, na podstawie której chcesz zsumować; I2 to wartość wyszukiwania w nagłówkach kolumn, których szukasz; A2: A9 to nagłówki wierszy zawierają wartość wyszukiwania, na podstawie której chcesz zsumować; H2 to wartość wyszukiwania w nagłówkach wierszy, których szukasz.


8. Vlookup, aby scalić dwie tabele na podstawie jednej lub więcej kolumn kluczowych

W codziennej pracy podczas analizy danych może zaistnieć potrzeba zebrania wszystkich niezbędnych informacji w jednej tabeli opartej na jednej lub kilku kluczowych kolumnach. Aby rozwiązać tę pracę, funkcja Vlookup może również wyświadczyć ci przysługę.

Vlookup, aby scalić dwie tabele na podstawie jednej kolumny klucza

Na przykład masz dwie tabele, pierwsza tabela zawiera dane produktów i nazw, a druga tabela zawiera produkty i zamówienia. Teraz chcesz połączyć te dwie tabele, dopasowując wspólną kolumnę produktów do jednej tabeli.

Formuła 1: Korzystanie z funkcji WYSZUKAJ.PIONOWO

Aby scalić dwie tabele w jedną na podstawie kolumny kluczowej, zastosuj następującą formułę do pustej komórki, w której chcesz uzyskać wynik, a następnie przeciągnij uchwyt wypełniania w dół do komórek, do których chcesz zastosować tę formułę. pobierz scaloną tabelę z kolumną kolejności łączącą dane pierwszej tabeli na podstawie danych z kolumny kluczowej.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Uwagi: W powyższym wzorze, A2 to wartość, której szukasz, E2: F8 to tabela do przeszukania, liczba 2 to numer kolumny w tabeli, z której należy pobrać wartość.

Formuła 2: Korzystanie z funkcji INDEKS i PODAJ.POZYCJĘ

Jeśli twoje wspólne dane po prawej stronie i zwrócone dane w lewej kolumnie w drugiej tabeli, aby scalić kolumnę zamówienia, funkcja Vlookup nie może wykonać pracy. Aby spojrzeć w górę od prawej do lewej, możesz użyć funkcji INDEKS i PODAJ.POZYCJĘ, aby zastąpić funkcję Vlookup.

Skopiuj lub wprowadź poniższą formułę do pustej komórki, a następnie skopiuj formułę w dół kolumny, a kolumna zamówienia została połączona z pierwszą tabelą, patrz zrzut ekranu:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Uwagi: W powyższym wzorze, A2 to wartość wyszukiwania, której szukasz, E2: E8 to zakres danych, które chcesz zwrócić, F2: F8 to zakres wyszukiwania zawierający wartość wyszukiwania.


Vlookup, aby scalić dwie tabele na podstawie wielu kolumn kluczowych

Jeśli dwie tabele, które chcesz połączyć, mają wiele kolumn kluczowych, w celu scalenia tabel na podstawie tych wspólnych kolumn pomocne mogą być funkcje INDEKS i PODAJ.POZYCJĘ.

Ogólna formuła łączenia dwóch tabel na podstawie wielu kolumn kluczowych to:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. 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)

Uwagi: W powyższym wzorze, co reprezentują odwołania do komórek, jak pokazano na zrzucie ekranu:

2Następnie wybierz pierwszą komórkę formuły i przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek, jeśli potrzebujesz:

Wskazówki: W programie Excel 2016 i nowszych wersjach można również użyć Zapytanie o moc funkcja łączenia dwóch lub więcej tabel w jedną na podstawie kolumn kluczowych. Kliknij, aby poznać szczegóły krok po kroku.

9. Wyszukaj pasujące wartości w wielu arkuszach

Czy kiedykolwiek próbowałeś przeglądać wartości w wielu arkuszach? Przypuśćmy, że mam następujące trzy arkusze z zakresem danych, a teraz chcę uzyskać część odpowiednich wartości na podstawie kryteriów z tych trzech arkuszy, aby uzyskać wynik, jak pokazano poniżej. W tym przypadku Przeglądaj wartości w wielu arkuszach roboczych samouczek może zrobić ci przysługę krok po kroku.


Dopasowane wartości WYSZUKAJ.PIONOWO zachowują formatowanie komórek

1. Vlookup, aby uzyskać formatowanie komórki (kolor komórki, kolor czcionki) wraz z wartością wyszukiwania

Jak wszyscy wiemy, zwykła funkcja Vlookup może pomóc nam zwrócić dopasowaną wartość z innego zakresu danych, ale czasami możesz chcieć zwrócić odpowiednią wartość wraz z formatowaniem komórki, takim jak kolor wypełnienia, kolor czcionki, styl czcionki jak pokazano na poniższym zrzucie ekranu. W tej sekcji omówiono, jak uzyskać formatowanie komórki za pomocą zwracanej wartości w programie Excel.

Wykonaj następujące kroki, aby wyszukać i zwrócić odpowiednią wartość wraz z formatowaniem komórki:

1. W arkuszu roboczym zawiera dane, które chcesz przeglądać, kliknij prawym przyciskiem myszy kartę arkusza i wybierz Wyświetl kod z menu kontekstowego. Zobacz zrzut ekranu:

2. W otwartym Microsoft Visual Basic for Applications okno, skopiuj poniższy kod VBA do okna Code.

Kod VBA 1: Vlookup, aby uzyskać formatowanie komórek 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

3. Wciąż 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: Vlookup, aby uzyskać formatowanie komórek 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

4. Po wstawieniu powyższych kodów kliknij Przybory i narzędzia > Referencje w Microsoft Visual Basic for Applications okno. Następnie sprawdź Microsoft Script Runtime pole wyboru w Referencje - VBAProject Okno dialogowe. Zobacz zrzuty ekranu:

5. Następnie kliknij OK aby zamknąć okno dialogowe, a następnie zapisz i zamknij okno kodu, teraz wróć do arkusza, a następnie zastosuj tę formułę: =LookupKeepFormat(E2,$A$1:$C$10,3) do pustej komórki, w której chcesz wyprowadzić wynik, a następnie naciśnij klawisz Enter. Zobacz zrzut ekranu:

Uwagi: W powyższym wzorze, E2 to wartość, na którą spojrzysz, A1: C10 to zakres i numer tabeli 3 to numer kolumny tabeli, do której ma zostać zwrócona dopasowana wartość.

6. Następnie wybierz pierwszą komórkę wynikową i przeciągnij uchwyt wypełniania w dół, aby uzyskać wszystkie wyniki wraz z ich formatowaniem. Zobacz zrzut ekranu.


2. Zachowaj format daty z wartości zwróconej przez Vlookup

Zwykle podczas korzystania z funkcji Vloook do wyszukania i zwrócenia dopasowanej wartości formatu daty, niektóre formaty liczb zostaną wyświetlone, jak pokazano poniżej. Aby zachować format daty z zwracanego wyniku, należy dołączyć funkcję TEKST do funkcji Vlookup.

Zastosuj poniższą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek, a wszystkie dopasowane daty zostały zwrócone, jak pokazano na poniższym zrzucie ekranu:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Uwagi: W powyższym wzorze, E2 to wartość wyglądu, A2: C9 to zakres wyszukiwania, liczba 3 to numer kolumny, do której ma zostać zwrócona wartość, mm/dd/yyy to format daty, który chcesz zachować.


3. Wyszukaj i zwróć pasującą wartość z komentarzem do komórki

Czy kiedykolwiek próbowałeś Vlookup zwrócić nie tylko dopasowane dane komórki, ale także komentarz komórki, a także w programie Excel, jak pokazano na poniższym zrzucie ekranu? Aby rozwiązać to zadanie, poniższa funkcja zdefiniowana przez użytkownika może wyświadczyć ci przysługę.

1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2, 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

3. Następnie zapisz i zamknij okno kodu, wprowadź tę formułę: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) do pustej komórki, aby zlokalizować wynik, a następnie przeciągnij uchwyt wypełniania, aby skopiować tę formułę do innych komórek, teraz dopasowane wartości, a także komentarze są zwracane jednocześnie, patrz zrzut ekranu:

Uwagi: W powyższym wzorze, D2 jest wartością wyszukiwania, którą chcesz zwrócić odpowiadającą jej wartość, A2: B9 to tabela danych, której chcesz użyć, liczba 2 to numer kolumny zawierającej dopasowaną wartość, którą chcesz zwrócić.


4. Zajmij się tekstem i liczbami rzeczywistymi w Vlookup

Na przykład mam zakres danych, numer identyfikacyjny w oryginalnej tabeli to format liczbowy, w komórce wyszukiwania, która jest przechowywana jako tekst, podczas stosowania normalnej funkcji Vlookup, wynik błędu # N / A jest wyświetlany jak poniżej zrzut ekranu pokazane. W takim przypadku, jak można uzyskać prawidłowe informacje, jeśli numer wyszukiwania i pierwotna liczba w tabeli mają inny format danych?

Aby poradzić sobie z tekstem i liczbami rzeczywistymi w funkcji Vlookup, zastosuj następującą formułę do pustej komórki, a następnie przeciągnij uchwyt wypełniania w dół, aby skopiować tę formułę, a otrzymasz prawidłowe wyniki, jak pokazano na zrzucie ekranu:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Uwagi:

  • 1. W powyższym wzorze D2 jest wartością wyszukiwania, którą chcesz zwrócić odpowiadającą jej wartość, A2: B8 to tabela danych, której chcesz użyć, liczba 2 to numer kolumny zawierającej dopasowaną wartość, którą chcesz zwrócić.
  • 2. Ta formuła działa również dobrze, jeśli nie jesteś pewien, gdzie masz liczby i gdzie masz tekst.

Pobierz przykładowe pliki WYSZUKAJ.PIONOWO

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Prevent Duplicate Cells; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
kte tab 201905
  • 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ęć myszą każdego dnia!
officetab dół

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.