Z nami uczy się już 7382 osób. Czekamy na Ciebie

Strona główna » Blog » Wyszukiwanie po kilku kryteriach

Wyszukiwanie po kilku kryteriach

Chcesz wyszukać wartość po dwóch lub więcej kryteriach w zbiorze danych w Excelu? Jak w większości przypadków w Excelu są na to różne sposoby. Rozważymy tutaj sposób wykorzystujący funkcję WYSZUKAJ.PIONOWO i łączenie tekstu.

Funkcja WYSZUKAJ.PIONOWO, sama nie radzi sobie z kilkoma kryteriami, ale jeśli połączymy dla niej 2 lub więcej kryteriów (kolumn), to będzie te połączone kryteria traktować jako jedno.

Omówmy to na prostym przykładzie. Na rysunku poniżej możesz zobaczyć dane producentów tabletów ich modele, cenę (zmyśloną) oraz liczbę sztuk na magazynie.

Kryteria01

magazynie.

Nasze zadanie polega na tym, by wyszukać cenę znając producenta i model. Żeby to zrobić potrzebujemy sobie dodać kolumnę pomocniczą. W naszym przykładzie będzie to kolumna po kolumnie Model.

Moglibyśmy dodać kolumnę wcześniej, ale nie później, bo musisz zapamiętać, że funkcja WYSZUKAJ.PIONOWO zawsze przeszukuje pierwszą kolumnę wybranego zakresu danych.

Kolumnę najprościej dodasz klikając na nagłówku kolumny (w naszym przykładzie kolumny C) prawym przyciskiem myszy, a następnie wybierzesz opcję wstaw.

Ta część tylko dla członków iExcel.pl
Wystarczy zarejestrować się za darmo by odblokować treść.

Warto sobie jeszcze dołożyć nazwę nowej kolumny „Pomocnicza”, żebyś wiedział, że nie są to faktyczne dane tylko kolumna ułatwiająca obliczenia.

Teraz potrzebujemy jeszcze kryteriów do wyszukiwania. Je również musimy połączyć, żeby funkcji WYSZUKAJ.PIONOWO udało się wyszukać odpowiednią wartość.

Kryteria04

Teraz możemy już wykorzystać funkcję WYSZUKAJ.PIONOWO, ponieważ mamy już 2 połączone kryteria (czyli to czego szukamy), oraz kolumnę z połączonymi wszystkimi wartościami tych kryteriów występujących w danych. Ważne tu było to, że połączenie wystąpiło najpóźniej w 3 kolumnie ponieważ funkcja WYSZUKAJ.PIONOWO będzie wyszukiwać w 1 kolumnie zakresu, który jej podamy, za to wartość może wyciągać z dowolnej kolumny tego zakresu, czyli jeśli kolumna, po której dokonywalibyśmy wyszukiwania była później, to nie moglibyśmy pobrać elementów, które byłyby we wcześniejszych kolumnach.

=WYSZUKAJ.PIONOWO(C2;$C$6:$E$76;2;0)

Kryteria05

 

Tyle wystarczy by uzyskać prawidłowy wynik, oczywiście jeśli zależy Ci, żeby wyszukać pierwsze wystąpienie kryteriów (albo z założenia jedyne).

Ewentualnym problemem może być to, że nie chcesz mieć kolumny pomocniczej. Wtedy musisz włożyć wszystkie łączenia wartości w formule. O ile połączenie dwóch komórek kryteriów nie stanowi dużego wyzwania, to połączenie dwóch kolumn z danymi może przysporzyć problemów, ponieważ wchodzimy wtedy w obszar tzw. formuł tablicowych. Takie formuły musisz zatwierdzać kombinacją klawiszy Ctrl + Shift + Enter, inaczej możesz uzyskać błędny wynik.

Po zatwierdzeniu formuły tablicowej Excel w pasku formuły otoczy ją informacyjnie nawiasami klamrowymi {}.

Kryteria07

Pamiętaj, że są one tylko informacyjne i wstawienie ich do formuły nie sprawi, że będzie ona tablicowa. Tylko zatwierdzenie kombinacją klawiszy Ctrl + Shift + Enter sprawi, że Excel będzie prawidłowo rozpoznawał formułę jako formułę tablicową.

Czyli łączenie dwóch komórek już widziałeś wcześniej, a jak wygląda łączenie dwóch kolumn? Najprościej wstawić zakresy tych kolumn, a pomiędzy nimi znak ampersand (&):

$A$6:$A$76&$B$6:$B$76

Tylko to nastręcza kolejnej trudności – wstawienie połączonych kolumn jako argument tabela_tablica, funkcji WYSZUKAJ.PIONOWO nie da nam odpowiednich wyników, bo nie uda nam się uzyskać (bez kolumny pomocniczej) takiego zakresu z dalszymi kolumnami. Dlatego musimy się przełączyć na funkcję PODAJ.POZYCJĘ, która zwróci nam pozycję/miejsce szukanego elementu na liście, którą stworzymy. Dzięki temu będziemy mogli jej wynik wstawić do funkcji INDEKS, dzięki czemu znajdziemy interesującą nas wartość.

Funkcji INDEKS jako zakres tablicy, można podać albo większy zakres danych, które przeglądamy, albo tylko kolumnę, gdzie znajduje się interesująca nas wartość. Ponieważ chcemy zyskać większą elastyczność to podamy cały zbiór danych i będziemy odpowiednio wybierać numer kolumny (podobnie jak w funkcji WYSZUKAJ.PIONOWO).

=INDEKS($A$6:$E$76;PODAJ.POZYCJĘ(A2&B2;$A$6:$A$76&$B$6:$B$76;0);4)

Kryteria06

Teraz możesz usunąć dane z kolumny pomocniczej i zobaczyć, że druga stworzona formuła wciąż działa, ponieważ odpowiednie połączenia ma wbudowane we wnętrzu argumentów.

Kryteria08

Na koniec możemy jeszcze nasze formuły wstawić do funkcji JEŻELI.BŁĄD, żeby w sytuacji, gdy danego elementu (podwójnego kryterium, lub większej ilości kryteriów) nie będzie w liście danych, zamiast błędu #N/D! pojawiał się bardziej przyjazny komunikat np.:

=JEŻELI.BŁĄD(INDEKS($A$6:$E$76;PODAJ.POZYCJĘ(A2&B2;$A$6:$A$76&$B$6:$B$76;0);4);”Brak wyniku”)


Film i materiały do lekcji mogą oglądać tylko zalogowani użytkownicy. Nie masz konta?
Zarejestruj się za darmo lub dowiedz się więcej


Lekcja-1,99zł
  • Dostęp do wybranej listy

lub dostęp do całego serwisu przez

Rok-49zł
  • Dostęp do ponad 200 lekcji
  • Możliwość zdobycia certyfikatu
  • Udział w konferencjach on-line
  • Liczne pliki, ćwiczenia i testy
  • Co tydzień nowa lekcja
Firmy szkolące się na platformie www.iExcel.pl
CLOSE
CLOSE
Przeczytaj poprzedni wpis:
magazyn
Magazyn

Szablon pozwalający efektywnie zarządzać stanami magazynowymi. Zastosowane funkcje dają możliwość dodawania nowych produktów, wydawania ich na zewnątrz oraz zamawiania nowych....

Zamknij