Obejrzyj film – Jak używać funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami

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

Często jednak zachodzi potrzeba użycia funkcji WYSZUKAJ.PIONOWO programu Excel z wieloma kryteriami.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami

Załóżmy, że masz dane z imieniem i nazwiskiem ucznia, typem egzaminu i wynikiem z matematyki (jak pokazano poniżej):

Korzystanie z funkcji WYSZUKAJ.PIONOWO do uzyskania wyniku matematycznego dla każdego ucznia na odpowiednim poziomie egzaminu może być wyzwaniem.

Można się spierać że lepszym rozwiązaniem byłaby zmiana struktury zbioru danych lub użycie tabeli przestawnej. Jeśli to działa dla ciebie, nic takiego. Ale w wielu przypadkach utkniesz z danymi, które posiadasz, a tabela przestawna może nie być rozwiązaniem.

W takich przypadkach ten samouczek jest dla Ciebie.

Teraz jest dwa sposoby na uzyskanie wartości wyszukiwania za pomocą funkcji WYSZUKAJ.PIONOWO z wieloma kryteriami.

• Za pomocą kolumny pomocniczej.
• Za pomocą funkcji WYBIERZ.

WYSZUKAJ.PIONOWO z wieloma kryteriami – używanie kolumny pomocniczej

Jestem fanem kolumn pomocniczych w programie Excel.

Znajduję dwie istotne zalety używania kolumn pomocniczych zamiast formuł tablicowych:

• Ułatwia zrozumienie, co dzieje się w arkuszu.
• Przyspiesza to w porównaniu z funkcjami tablicowymi (zauważalne w dużych zestawach danych).

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

Wracając do omawianego pytania, kolumna pomocnicza jest potrzebna do utworzenia unikalnego kwalifikatora. Tego unikalnego kwalifikatora można następnie użyć do wyszukania poprawnej wartości. Na przykład w danych jest trzech Matów, ale jest tylko jedna kombinacja Matów i testów jednostkowych lub Matów i średniookresów.

Oto kroki:

Jak to działa?

Tworzymy unikalne kwalifikatory dla każdego wystąpienia nazwy i egzaminu. W zastosowanej tutaj funkcji WYSZUKAJ.PIONOWO wartość wyszukiwania została zmieniona na $ F3 & ”|” & G $ 2, tak aby oba kryteria wyszukiwania są łączone i używane jako pojedyncza wartość wyszukiwania. Na przykład wartość wyszukiwania dla 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 (wybacz mi brak kreatywności tutaj):

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

• Dlaczego wstawiłem kolumnę pomocniczą między kolumną B i C, a nie skrajnie po lewej? – Nie ma nic złego w wstawieniu kolumny pomocniczej do skrajnej lewej strony. W rzeczywistości, jeśli nie chcesz złagodzić oryginalnych danych, powinna to być droga. Zrobiłem to, ponieważ sprawia, że używam mniejszej liczby komórek w funkcji WYSZUKAJ.PIONOWO. Zamiast 4 kolumn w tablicy tabeli, mogłem sobie poradzić tylko z 2 kolumnami. Ale to tylko ja.

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

Oto metoda kolumn nie-pomocniczych.

Pobierz przykładowy plik

WYSZUKAJ.PIONOWO z wieloma kryteriami – użycie funkcji WYBIERZ

Używanie formuł tablicowych zamiast pomocnika kolumny oszczędzają miejsce w arkuszu, a wydajność może być równie dobra, jeśli jest używana rzadziej w skoroszycie.

Biorąc pod uwagę ten sam zestaw danych, który został użyty powyżej, oto formuła, która da ci wynik :

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

Ponieważ jest to formuła tablicowa, użyj jej z kombinacją Control + Shift + Enter zamiast tylko Enter.

Jak to działa?

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

Pokażę, co mam na myśli, mówiąc o wirtualnych danych pomocniczych.

Na powyższej ilustracji, gdy zaznaczam część WYBIERZ formuły i naciskam klawisz F9, pokazuje wynik, że WYBIERZ formułę.

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

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

Teraz , kiedy używasz funkcji WYSZUKAJ.PIONOWO, wyszukuje ona po prostu wartość w pierwszej kolumnie (tych wirtualnych 2 kolumn danych) i zwraca odpowiedni wynik.

Pobierz Przykładowy plik

Możesz także użyć innych formuł, aby wyszukać wiele kryteriów (takich jak INDEX / MATCH lub SUMPRODUCT).