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.

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:

Uwagi dotyczące funkcji:

Podstawowe przykłady funkcji WYSZUKAJ.PIONOWO

W tej sekcji omówimy niektóre często używane formuły Vlookup.

2.1 Dokładne dopasowanie i przybliżone dopasowanie WYSZUKAJ.PIONOWO

 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)

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:

 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:

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:

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.

 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:

 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.

1. Kliknij Kutools > Super WYSZUKAJ > WYSZUKAJ między dwiema wartościami aby włączyć tę funkcję.
2. 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:

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:

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:

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ę.

Zaawansowane przykłady WYSZUKAJ.PIONOWO

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)

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:

 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ęć.

1. Kliknij Kutools > Super WYSZUKAJ > Wyszukiwanie wielowarunkowe aby włączyć tę funkcję.
2. 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.

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:

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:

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:

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:

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:

 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:

 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.

1. Kliknij Kutools > Super WYSZUKAJ > WYSZUKAJ i Suma aby włączyć tę funkcję.
2. 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))

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.

 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:

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

1. 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:
   
2. W otwartym Microsoft Visual Basic for Applications oknie, skopiuj poniższy kod VBA do okna Code.
3. Kod VBA 1: WYSZUKAJ.PIONOWO, aby uzyskać formatowanie komórki wraz z wartością wyszukiwania

4. 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
   

5. 

Krok 2: Skopiuj kod 2 do okna modułu

1. Jeszcze w Microsoft Visual Basic for Applications okno, kliknij wstawka > Moduł, a następnie skopiuj poniższy kod VBA 2 do okna Module.
2. Kod VBA 2: WYSZUKAJ.PIONOWO, aby uzyskać formatowanie komórki wraz z wartością wyszukiwania

3. 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. 

Krok 3: Wybierz opcję dla projektu VBA

1. 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:
2. Następnie kliknij OK aby zamknąć okno dialogowe, a następnie zapisz i zamknij okno kodu.

Krok 4: Wpisz formułę uzyskania wyniku

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

   
   

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:

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

1. Przytrzymaj przycisk 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.

Krok 2: Wpisz formułę, aby uzyskać wynik

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

   
   

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: