Te przykłady pokazują, jak przeglądać wiele kryteriów, zwracać określone wystąpienie lub wszystkie dopasowania, wykonywać dynamiczne wyszukiwanie w wielu arkuszy i nie tylko.
Jest to druga część serii, która pomoże Ci wykorzystać moc funkcji WYSZUKAJ.PIONOWO w programie Excel. Przykłady sugerują, że wiesz, jak działa ta funkcja. Jeśli nie, warto zacząć od podstawowych zastosowań funkcji WYSZUKAJ.PIONOWO w programie Excel.
Zanim przejdę dalej, przypomnę pokrótce składnię:
Teraz, gdy wszyscy są na tej samej stronie, przyjrzyjmy się bliżej przykładom zaawansowanych formuł WYSZUKAJ.PIONOWO:
- Jak pominąć wiele kryteriów w programie Excel
- Vlookup i zwracanie n-tego dopasowania
- Vlookup wiele wartości
- Vlookup na podstawie wartości wierszy i kolumn
- Zagnieżdżona formuła Vlookup
- WYSZUKAJ.PIONOWO i POŚREDNI, aby pobierać dane z wielu arkuszy
Jak przeglądać wiele kryteriów
Funkcja WYSZUKAJ.PIONOWO w programie Excel jest bardzo pomocna przy wyszukiwaniu w bazie danych dla określonej wartości. Brakuje mu jednak ważnej funkcji – jego składnia pozwala na tylko jedną wartość wyszukiwania. Ale co, jeśli chcesz wyszukać kilka warunków? Jest kilka różnych rozwiązań do wyboru.
Wzór 1. WYSZUKAJ.PIONOWO na podstawie tw o wartości
Załóżmy, że masz listę zamówień i chcesz znaleźć ilość na podstawie 2 kryteriów, nazwy klienta i produktu. Czynnikiem komplikującym jest to, że każdy klient zamówił wiele produktów, jak pokazano w poniższej tabeli:
Zwykła formuła WYSZUKAJ.PIONOWO nie zadziała w tym przypadku sytuacji, ponieważ zwraca pierwsze znalezione dopasowanie na podstawie pojedynczej określonej wartości wyszukiwania.
Aby temu zaradzić, możesz dodać kolumnę pomocniczą i połączyć w niej wartości z dwóch kolumn wyszukiwania (Klient i Produkt). Ważne jest, aby kolumna pomocnicza była skrajną lewą kolumną w tablicy tabeli, ponieważ jest to miejsce, w którym Excel WYSZUKAJ.PIONOWO zawsze szuka wartości wyszukiwania.
Dodaj więc kolumnę po lewej stronie tabeli i skopiuj poniższa formuła w tej kolumnie. Spowoduje to wypełnienie kolumny pomocnika wartościami z kolumn B i C (znak spacji jest łączony między nimi dla lepszej czytelności):
=B2&" "&C2
Następnie użyj standardowej formuły WYSZUKAJ.PIONOWO i umieść oba kryteria w argumencie lookup_value, oddzielając je spacją:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Lub wprowadź kryteria w oddzielnych komórkach (w naszym przypadku G1 i G2) i połącz te komórki:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Ponieważ Chcąc zwrócić wartość z kolumny D, która jest czwarta w tablicy tabeli, używamy 4 dla col_index_num. Argument przeszukiwany_zakres jest ustawiony na FALSE, aby sprawdzić dokładne dopasowanie. Poniższy zrzut ekranu przedstawia wynik:
Jeśli tabela przeglądowa znajduje się w innym arkuszu, dołącz nazwę arkusza do formuły WYSZUKAJ.PIONOWO. Na przykład:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternatywnie utwórz nazwany zakres dla tabeli przeglądowej (na przykład Zamówienia), aby ułatwić formułę. do przeczytania:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Aby uzyskać więcej informacji, zobacz Jak przejrzeć inny arkusz w programie Excel.
Formuła 2. WYSZUKAJ.PIONOWO w Excelu z wieloma warunkami
Teoretycznie możesz zastosować powyższe podejście do Vlookup więcej niż dwóch kryteriów. H jest jednak kilka zastrzeżeń. Po pierwsze, wartość wyszukiwania jest ograniczona do 255 znaków, a po drugie, projekt arkusza może nie pozwalać na dodanie kolumny pomocniczej.
Na szczęście Microsoft Excel często udostępnia więcej niż jeden sposób zrobienia tego samego. Aby przejrzeć wiele kryteriów, możesz użyć kombinacji INDEX MATCH lub funkcji XLOOKUP ostatnio wprowadzonej w Office 365.
Na przykład, aby wyszukać na podstawie 3 różnych wartości (Data, Nazwa klienta i Produkt), użyj jednej z następujących formuł:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Gdzie:
Aby uzyskać szczegółowe wyjaśnienie formuł, zobacz:
- XLOOKUP z wieloma kryteriami
- INDEX MATCH formuła z wieloma kryteriami
Jak używać funkcji WYSZUKAJ.PIONOWO do uzyskania drugiego, trzeciego lub n-tego dopasowania
Jak już wiesz, funkcja WYSZUKAJ.PIONOWO w programie Excel może pobrać tylko jedną pasującą wartość, a dokładniej zwraca pierwsze znalezione dopasowanie. Ale co, jeśli w tablicy wyszukiwania jest kilka dopasowań i chcesz uzyskać drugą lub trzecią instancję? Zadanie brzmi dość skomplikowanie, ale rozwiązanie istnieje!
Formuła 1. Vlookup N-ta instancja
Załóżmy, że masz nazwiska klientów w jednej kolumnie, produkty, które kupili w innej, a Ty szukają drugiego lub trzeciego produktu kupionego przez danego klienta.
Najprostszym sposobem jest dodanie kolumny pomocnika po lewej stronie tabeli, tak jak to zrobiliśmy w pierwszym przykładzie. Ale tym razem wypełnimy go nazwami klientów i numerami wystąpień, takimi jak „Jan Kowalski1”, „Jan Kowalski2” itp.
Aby uzyskać wystąpienie, użyj funkcji LICZ.JEŻELI z odwołaniem do zakresu mieszanego ( pierwsze odniesienie jest bezwzględne, a drugie względne, np. $ B $ 2: B2). Ponieważ odniesienie względne zmienia się w zależności od pozycji komórki, do której kopiowana jest formuła, w trzecim wierszu będzie to 2 $ B $: B3, w wierszu 4 – $ B 2 $: B4 i tak dalej.
W połączeniu z nazwą klienta (B2), formuła przyjmuje następującą postać:
=B2&COUNTIF($B$2:B2, B2)
Powyższa formuła idzie do A2 i następnie kopiujesz go do tylu komórek, ile potrzeba.
Następnie wprowadź nazwę docelową i numer wystąpienia w oddzielnych komórkach (F1 i F2) i użyj poniższej formuły, aby przejrzeć określone wystąpienie:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formuła 2. Vlookup drugie wystąpienie
Jeśli szukasz drugiego wystąpienia wartości wyszukiwania, możesz obejść się bez kolumny pomocnika. Zamiast tego utwórz tablicę tablicową dynamicznie, używając funkcji INDIRECT razem z funkcją MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Gdzie:
- E1 to wartość wyszukiwania
- A2: A11 to zakres wyszukiwania
- B11 to ostatnia (na dole po prawej) komórka tabeli przeglądowej
Pamiętaj, że powyższa formuła została napisana dla konkretnego przypadku, w którym komórki danych w tabeli przeglądowej zaczynają się w wierszu 2. Jeśli Twoja tabela znajduje się gdzieś w środek arkusza, użyj tej uniwersalnej formuły, w której A1 jest lewą górną komórką tabeli przeglądowej zawierającej nagłówek kolumny:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Jak działa ta formuła
Oto kluczowa część formuły, która tworzy dynamiczny zakres podglądu:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
Funkcja PODAJ.POZYCJĘ skonfigurowana na dokładne dopasowanie (0 w ostatnim argumencie) porównuje nazwę celu (E1) z listą nazwisk (A2: A11) i zwraca pozycję pierwszego znalezionego dopasowania, czyli 3 ja nie Twoja sprawa. Ta liczba będzie używana jako początkowa współrzędna wiersza dla zakresu podglądu, więc dodajemy do niej 2 (+1, aby wykluczyć pierwsze wystąpienie i +1, aby wykluczyć wiersz 1 z nagłówkami kolumn). Alternatywnie możesz użyć 1 + WIERSZ (A1), aby automatycznie obliczyć niezbędną korektę na podstawie pozycji wiersza nagłówka (w naszym przypadku A1).
W rezultacie otrzymamy następujący ciąg tekstowy, który INDIRECT konwertuje na odniesienie do zakresu:
INDIRECT("A"&5&":B11") -> A5:B11
Ten zakres przechodzi do argumentu table_array funkcji WYSZUKAJ.PIONOWO, zmuszając go do rozpoczęcia wyszukiwania w wierszu 5, pomijając pierwsze wystąpienie wartości wyszukiwania:
VLOOKUP(E1, A5:B11, 2, FALSE)
Jak przeglądać i zwracać wiele wartości w programie Excel
Funkcja WYSZUKAJ.PIONOWO w programie Excel zwraca tylko jedno dopasowanie. Czy istnieje sposób na przeglądanie wielu wystąpień? Tak, jest, choć nie jest to łatwe. Wymaga to połączonego użycia kilku funkcji, takich jak INDEKS, MAŁE i WIERSZ, to formuła tablicowa.
Na przykład poniżej można znaleźć wszystkie wystąpienia wartości wyszukiwania F2 w zakresie wyszukiwania B2: B16 i zwraca wiele dopasowań z kolumny C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Istnieją dwa sposoby wprowadzenia formuły do arkusza:
- Wpisz formułę w pierwszej komórce, naciśnij Ctrl + Shift + Enter, a następnie przeciągnij ją w dół do kilku kolejnych komórek.
- Zaznacz kilka sąsiednich komórek w jednej kolumnie (F1: F11 w zrzut ekranu poniżej), wpisz formułę i naciśnij Ctrl + Shift + Enter, aby ją uzupełnić.
Tak czy inaczej, liczba komórek, w których wpisujesz formułę, powinna być równa lub większa od maksymalna liczba możliwych dopasowań.
Aby uzyskać szczegółowe wyjaśnienie logiki formuły i więcej przykładów, zobacz Jak wykonać WYSZUKAJ.PIONOWO wiele wartości w programie Excel .
Jak przeglądać wiersze i kolumny (wyszukiwanie dwukierunkowe)
Wyszukiwanie dwukierunkowe (zwane również wyszukiwaniem macierzy lub wyszukiwaniem dwuwymiarowym) to fantazyjne słowo do wyszukiwania wartości na przecięciu określonego wiersza i kolumny.Istnieje kilka różnych sposobów wyszukiwania dwuwymiarowego w programie Excel, ale ponieważ w tym samouczku skupiamy się na funkcji WYSZUKAJ.PIONOWO, będziemy jej naturalnie używać.
W tym przykładzie weźmiemy poniżej tabeli ze sprzedażą miesięczną i opracuj formułę WYSZUKAJ.PIONOWO, aby pobrać wielkość sprzedaży dla określonego towaru w danym miesiącu.
Z nazwami pozycji w A2: A9, nazwami miesięcy w B1: F1, element docelowy w I1 i docelowym miesiącu w I2 formuła wygląda następująco:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Jak działa ta formuła
Rdzeniem tej formuły jest standardowa funkcja WYSZUKAJ.PIONOWO, która wyszukuje dokładne dopasowanie do wartości wyszukiwania w I1. Ale ponieważ nie wiemy w której dokładnie kolumnie znajduje się sprzedaż w określonym miesiącu, nie możemy podać numeru kolumny bezpośrednio do argumentu col_index_num. Aby znaleźć tę kolumnę, używamy następującej funkcji MATCH:
MATCH(I2, A1:F1, 0)
W tłumaczeniu na język angielski formuła mówi: spójrz w górę wartość I2 w A1: F1 i zwróć jej względną pozycję w tablicy. Podając 0 do trzeciego argumentu, nakazujesz PODAJ.POZYCJĘ znaleźć wartość dokładnie równą wartości wyszukiwania (to tak, jak użycie FALSE dla argumentu przeszukiwanego zakresu w WYSZUKAJ.PIONOWO).
Ponieważ Mar znajduje się w czwartej kolumnie w tablicy wyszukiwania funkcja PODAJ.POZYCJĘ zwraca 4, co prowadzi bezpośrednio do argumentu col_index_num funkcji WYSZUKAJ.PIONOWO:
VLOOKUP(I1, A2:F9, 4, FALSE)
Proszę zwrócić uwagę że chociaż nazwy miesięcy zaczynają się w kolumnie B, używamy A1: I1 jako tablicy odnośników. Jest to robione po to, aby liczba zwracana przez PODAJ.POZYCJĘ odpowiadała pozycji kolumny w tabeli_tablica funkcji WYSZUKAJ.PIONOWO.
Aby dowiedzieć się więcej na temat wyszukiwania macierzy w programie Excel, zobacz INDEX MATCH MATCH i inne formuły do wyszukiwania dwuwymiarowego.
Jak wykonać wiele przeglądarek w programie Excel (zagnieżdżone Vlookup)
Czasami może się zdarzyć, że główna tabela i tabela przeglądowa nie mają jednej wspólnej kolumny, co uniemożliwia wykonanie podglądu między dwiema tabelami. Istnieje jednak inna tabela, która nie zawiera informacji, których szukasz, ale ma jedną wspólną kolumnę z główną tabelą i inną wspólną kolumnę z tabelą przeglądową.
Poniższy obrazek ilustruje sytuację:
Celem jest skopiowanie cen do głównej tabeli na podstawie identyfikatorów pozycji. Problem polega na tym, że tabela zawierająca ceny nie ma identyfikatorów pozycji, co oznacza, że będziemy musieli wykonać dwa Vlookup w jednej formule.
Dla wygody stwórzmy najpierw kilka nazwanych zakresów :
- Tabela przeglądowa 1 nosi nazwę Produkty (D3: E3).
- Tabela przeglądowa 2 nosi nazwę Ceny (G3: H3).
Tabele mogą znajdować się w tym samym lub różnych arkuszach.
A teraz wykonamy tak zwane podwójne Vlookup, czyli zagnieżdżone Vlookup.
Najpierw wykonaj VLOOKUP wzór do znalezienia nazwy produktu w tabeli przeglądowej 1 (o nazwie Produkty) na podstawie identyfikatora produktu (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Następnie umieść powyższą formułę w argumencie lookup_value innej funkcji WYSZUKAJ.PIONOWO, aby pobrać ceny z tabeli przeglądowej 2 (o nazwie Ceny) na podstawie nazwy produktu zwróconej przez zagnieżdżoną funkcję WYSZUKAJ.PIONOWO:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Poniższy zrzut ekranu przedstawia naszą zagnieżdżoną formułę Vlookup w działaniu:
Jak przeglądać wiele arkusze dynamicznie
Czasami dane w tym samym formacie mogą być podzielone na kilka arkuszy. Twoim celem jest pobranie danych z określonego arkusza w zależności od wartości klucza w danej komórce.
Może to być łatwiejsze do zrozumienia na przykładzie. Powiedzmy, że masz kilka regionalnych raportów sprzedaży w tym samym formacie i chcesz uzyskać dane dotyczące sprzedaży określonego produktu w określonych regionach:
Podobnie jak w poprzednim przykładzie, zaczynamy od zdefiniowania kilku nazw:
- Zakres A2: B5 w arkuszu CA nazywa się CA_Sales.
- Zakres A2 : B5 w arkuszu FL nosi nazwę FL_Sales.
- Zakres A2: B5 w arkuszu KS nosi nazwę KS_Sales.
Jak widać, wszystkie nazwane zakresy mają wspólne część (Sales) i unikalne części (CA, FL, KS). Pamiętaj, aby nazwać zakresy w podobny sposób, jak jest to niezbędne dla formuły, którą zamierzamy zbudować.
Formuła 1. POŚREDNIE WYSZUKAJ.PIONOWO do dynamicznego pobierania danych z różnych arkuszy
Jeśli Twoim zadaniem jest pobieranie danych z wielu arkuszy, najlepszym rozwiązaniem jest formuła WYSZUKAJ.PIONOWO POŚREDNIE – kompaktowe i łatwe do zrozumienia.
W tym przykładzie organizujemy tabelę podsumowującą w następujący sposób:
- Wprowadź interesujące produkty w A2 i A3. To są nasze wartości wyszukiwania.
- Wprowadź unikalne części nazwanych zakresów w B1, C1 i D1.
A teraz konkatenujemy komórkę zawierającą unikalną część (B1) z częścią wspólną („_Sales”) i przekazujemy wynikowy ciąg do POŚREDNIE:
INDIRECT(B$1&"_Sales")
Funkcja INDIRECT przekształca ciąg w nazwę zrozumiałą dla programu Excel i umieszczasz ją w argumencie table_array funkcji WYSZUKAJ.PIONOWO:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Powyższy wzór idzie do B2, a następnie kopiujesz go w dół i w prawo.
Zwróć uwagę, że: w wartości wyszukiwania ($ A2) zablokowaliśmy współrzędną kolumny z bezwzględnym odwołaniem do komórki, tak aby kolumna pozostała niezmieniona, gdy formuła jest kopiowana w prawo. W odwołaniu B $ 1 zablokowaliśmy wiersz, ponieważ chcemy, aby kolumna koordynuj, aby zmienić i podać odpowiednią część nazwy na POŚREDNI, w zależności od kolumny, do której kopiowana jest formuła:
Jeśli Twoja główna tabela jest zorganizowana inaczej, wartości wyszukiwania w wierszu i unikatowe części nazw zakresów w kolumnie mn, to należy zablokować współrzędną wiersza w wartości wyszukiwania (B $ 1) i współrzędną kolumny w częściach nazwy ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formuła 2. WYSZUKAJ.PIONOWO i zagnieżdżone IF, aby wyszukać wiele arkuszy
W sytuacji, gdy masz tylko dwa lub trzy arkusze odnośników, możesz użyć dość prostej formuły WYSZUKAJ.PIONOWO z zagnieżdżonymi funkcjami JEŻELI, aby wybrać właściwy arkusz na podstawie wartości klucza w określonej komórce:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Gdzie $ A2 to wartość wyszukiwania (nazwa pozycji), a B $ 1 to wartość klucza (stan):
W tym przypadku nie musisz koniecznie definiować nazw i możesz użyć odwołań zewnętrznych, aby odnieść się do innego arkusza lub skoroszytu.
Aby uzyskać więcej przykładów formuł, zobacz Jak wykonać WYSZUKAJ.PIONOWO na wielu arkuszach w programie Excel .
Oto jak używać funkcji WYSZUKAJ.PIONOWO w programie Excel. Dziękuję za przeczytanie i mam nadzieję, że zobaczymy się na naszym blogu w przyszłym tygodniu!
Ćwiczenie ćwiczeń do pobrania
Zaawansowane przykłady formuł WYSZUKAJ.PIONOWO (plik .xlsx)
- Samouczek WYSZUKAJ.PIONOWO dla początkujących w programie Excel
- Jak przeglądać wiele dopasowań w programie Excel
- Jak korzystać z funkcji WYSZUKAJ.PIONOWO & SUMA lub SUMA.JEŻELI w Excel
- INDEKS & Funkcje dopasowywania w programie Excel – lepsza alternatywa dla WYSZUKAJ.PIONOWO
- 4 sposoby na przeglądanie z uwzględnieniem wielkości liter w programie Excel
- WYSZUKAJ.PIONOWO Excela nie działa – błędy N / A, NAZWA i WARTOŚĆ