Jak korzystać z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami w programie Excel

Obejrzyj wideo - jak korzystać z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami

Funkcja WYSZUKAJ.PIONOWO programu Excel w swojej podstawowej formie może szukać jednej wartości wyszukiwania i zwracać odpowiednią wartość z określonego wiersza.

Ale często istnieje potrzeba korzystania z funkcji WYSZUKAJ.PIONOWO Excela z wieloma kryteriami.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami

Załóżmy, że masz dane z nazwiskami uczniów, typem egzaminu i wynikiem matematycznym (jak pokazano poniżej):

Korzystanie z funkcji WYSZUKAJ.PIONOWO w celu uzyskania wyniku matematycznego dla każdego ucznia na poszczególnych poziomach egzaminu może być wyzwaniem.

Można argumentować, że lepszą opcją byłaby restrukturyzacja zestawu danych lub użycie tabeli przestawnej. Jeśli to działa, nic takiego. Ale w wielu przypadkach utkniesz z danymi, które posiadasz, a tabela przestawna może nie być opcją.

W takich przypadkach ten samouczek jest dla Ciebie.

Teraz istnieją dwa sposoby uzyskania wartości wyszukiwania za pomocą funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami.

• Korzystanie z kolumny pomocniczej.
• Korzystanie z funkcji WYBIERZ.

WYSZUKAJ.PIONOWO z wieloma kryteriami - korzystanie z kolumny pomocniczej

Jestem fanem kolumn pomocniczych w Excelu.

Uważam, że stosowanie kolumn pomocniczych w porównaniu z formułami tablicowymi ma dwie istotne zalety:

• Ułatwia zrozumienie, co dzieje się w arkuszu.
• Sprawia, że ​​jest szybszy w porównaniu z funkcjami tablicowymi (zauważalne w dużych zbiorach danych).

Nie zrozum mnie źle. Nie jestem przeciwny formułom tablicowym. Uwielbiam niesamowite rzeczy, które można zrobić za pomocą formuł tablicowych. Tyle, że zostawiam je na specjalne okazje, kiedy wszystkie inne opcje nie pomagają.

Wracając do omawianego pytania, kolumna pomocnicza jest potrzebna do stworzenia unikalnego kwalifikatora. Ten unikalny kwalifikator może być następnie użyty do wyszukania prawidłowej wartości. Na przykład w danych są trzy Matt, ale jest tylko jedna kombinacja Matt i Unit Test lub Matt i Mid-Term.

Oto kroki:

• Wstaw kolumnę pomocniczą między kolumną B i C.
• Użyj następującej formuły w kolumnie pomocniczej:=A2&”|”&B2
  • Stworzyłoby to unikalne kwalifikatory dla każdej instancji, jak pokazano poniżej.
• Użyj następującej formuły w G3 = WYSZUKAJ.PIONOWO($F3&”|”&G$2,$C$2:$D$19,2,0)
• Skopiuj dla wszystkich komórek.

Jak to działa?

Tworzymy unikalne kwalifikatory dla każdego wystąpienia nazwy i egzaminu. W użytej tutaj funkcji WYSZUKAJ.PIONOWO wartość wyszukiwania została zmodyfikowana do $F3&”|”&G$2, aby oba kryteria wyszukiwania zostały połączone i były używane jako pojedyncza wartość wyszukiwania. Na przykład wartość wyszukiwania funkcji WYSZUKAJ.PIONOWO w G2 to Matt|Test jednostkowy. Teraz ta wartość wyszukiwania jest używana do uzyskania wyniku z C2:D19.

Wyjaśnienia:

Jest kilka pytań, które prawdopodobnie przyjdą ci do głowy, więc pomyślałem, że spróbuję odpowiedzieć tutaj:

• Dlaczego użyłem | symbol podczas łączenia dwóch kryteriów? - W niektórych wyjątkowo rzadkich (ale możliwych) warunkach możesz mieć dwa różne kryteria, które w połączeniu dają ten sam wynik. Oto bardzo prosty przykład (wybaczcie mój brak kreatywności tutaj):

Zauważ, że chociaż A2 i A3 są różne, a B2 i B3 są różne, kombinacje są takie same. Ale jeśli użyjesz separatora, nawet kombinacja będzie inna (D2 i D3).

• Dlaczego umieściłem kolumnę pomocniczą między kolumną B i C, a nie skrajnie po lewej stronie? - Nie zaszkodzi wstawić kolumnę pomocniczą skrajnie w lewo. W rzeczywistości, jeśli nie chcesz łagodzić oryginalnych danych, to powinna być droga. Zrobiłem to, ponieważ dzięki temu zużywam mniej komórek w funkcji WYSZUKAJ.PIONOWO. Zamiast mieć 4 kolumny w tablicy tabeli, mogłem zarządzać tylko 2 kolumnami. Ale to tylko ja.

Teraz nie ma jednego rozmiaru, który pasuje do wszystkich. Niektóre osoby mogą wolą nie używać żadnej kolumny pomocniczej podczas korzystania z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami.

Oto metoda kolumny bez pomocy.

Pobierz przykładowy plik

WYSZUKAJ.PIONOWO z wieloma kryteriami - korzystanie z funkcji WYBIERZ

Używanie formuł tablicowych zamiast kolumn pomocniczych pozwala zaoszczędzić miejsce w arkuszu, a wydajność może być równie dobra, jeśli jest używana mniej razy w skoroszycie.

Biorąc pod uwagę ten sam zestaw danych, który zastosowano powyżej, oto wzór, który da ci wynik:

=WYSZUKAJ.PIONOWO($E3&”|”&F$2,WYBIERZ({1,2};$A$2:$A$19&”|”&$B$2:$B$19;$C$2:$C$19),2 0)

Ponieważ jest to formuła tablicowa, użyj jej z klawiszami Control + Shift + Enter, a nie tylko Enter.

Jak to działa?

Formuła wykorzystuje również koncepcję kolumny pomocniczej. Różnica polega na tym, że zamiast umieszczać kolumnę pomocnika w arkuszu, traktuj ją jako wirtualne dane pomocnicze, które są częścią formuły.

Pokażę Ci, co mam na myśli przez wirtualne dane pomocnicze.

Na powyższej ilustracji, gdy wybieram część WYBIERZ formuły i naciskam F9, pokazuje wynik, który dałaby formuła WYBIERZ.

Wynik to {“Matt|Test jednostkowy”,91;”Bob|Test jednostkowy”, 52;… }

Jest to tablica, w której przecinek oznacza następną komórkę w tym samym wierszu, a średnik oznacza, że ​​kolejne dane znajdują się w następnej kolumnie. Dlatego ta formuła tworzy 2 kolumny danych - jedna kolumna ma unikalny identyfikator, a druga wynik.

Teraz, gdy używasz funkcji WYSZUKAJ.PIONOWO, po prostu szuka ona wartości w pierwszej kolumnie (z tych wirtualnych danych dwukolumnowych) i zwraca odpowiedni wynik.

Pobierz przykładowy plik

Możesz również użyć innych formuł, aby wykonać wyszukiwanie z wieloma kryteriami (na przykład INDEKS/PODZIELANIE lub SUMAPRODUKT).

Czy znasz inny sposób, aby to zrobić? Jeśli tak, podziel się ze mną w sekcji komentarzy.

Możesz również polubić następujące samouczki WYSZUKAJ:

• WYSZUKAJ.PIONOWO vs. INDEKS/DOPASUJ
• Uzyskaj wiele wartości wyszukiwania bez powtórzeń w jednej komórce.
• Jak rozróżniać wielkość liter w funkcji WYSZUKAJ.PIONOWO.
• Użyj funkcji IFERROR z funkcją WYSZUKAJ.PIONOWO, aby pozbyć się błędów #N/D.