W tym poście omówimy wyszukiwanie po dwóch lub więcej kryteriach. Ze względu na nową funkcję w Excelu tablicowym przedstawimy 4 różne sposoby rozwiązania takiego problemu.
Problem polega na tym, że cena produktu zależy nie tylko od nazwy produktu, ale również od kraju, z jakiego pochodzi ten produkt. Musimy wyszukać cenę produktu po 2 kryteriach. Pierwsze rozwiązanie wykorzystuje kolumnę pomocniczą (rys. nr 1).
Musimy napisać formułę, w której połączymy nazwę owocu z krajem jego pochodzenia. Użyjemy do tego znaku ampersand (&) i pionowej kreski (|), aby na pewno rozdzielić dwa ciągi tekstowe. Zapis formuły powinien wyglądać następująco:
=B3&"|"&C3
Po zatwierdzeniu formuły i przeciągnięciu jej na komórki poniżej otrzymamy dane przedstawione na rys. nr 2.
Otrzymaliśmy połączone niejako dane z pierwszej i drugiej kolumny rozdzielone pionową kreską. Dla Excela nie jest to potrzebne, ale my mamy lepszą wizualną formę danych. Mamy pomocniczą kolumnę z połączoną informacją i teraz możemy skopiować z niej formułę za pomocą skrótu klawiszowego Ctrl+C i wkleić w kolumnę pomocniczą z Cennikiem. Istotne jest tutaj, że zarówno dane z naszej Tabeli sprzedaży jak i w Cenniku muszą mieć ten znak rozdzielający (pionową kreskę) – rys. nr 3.
Po wklejeniu formuły do komórki I3, zatwierdzamy ją i kopiujemy na komórki poniżej. Otrzymamy dane przedstawione na rys. nr 4.
Teraz musimy wyszukać tylko jedną informację (połączone dwie wartości), dlatego możemy skorzystać ze standardowej funkcji WYSZUKAJ.PIONOWO. Pierwszym argumentem funkcji jest szukana_wartość, czyli dane z kolumny Pomoc z Tabeli sprzedażowej. Drugi argument funkcji to tabela_tablica, czyli kolumny Pomoc i Cena z tabeli Cennik (zablokowane bezwzględnie). Trzeci argument to nr_indeksu_kolumny, czyli u nas wartość 2, bo chcemy wyciągnąć informację z kolumny Cena, która jest druga w kolejności z zakresu tabela_tablica. Ostatni argument (opcjonalny) to przeszukiwany_zakres, w którym musimy zdecydować, czy robimy wyszukiwanie przybliżone, czy dokładne. W większości sytuacji chcemy mieć dopasowanie dokładne, bo nie mamy pewności, czy dane są posortowane, czyli wpisujemy wartość logiczną FAŁSZ lub wartość z nią tożsamą (0). Zapis formuły powinien wyglądać następująco:
=WYSZUKAJ.PIONOWO(D3;$I$3:$J$10;2;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane z dopasowaną ceną według dwóch kryteriów przedstawione na rys. nr 5.
Teraz omówimy drugie rozwiązanie, które również będzie niejako wykorzystywało kolumnę pomocniczą, ale zbudowaną wewnątrz funkcji. Rozwiązanie to omówimy na podstawie przykładowych danych z rys. nr 6.
W klasycznym Excelu mogliśmy użyć funkcji PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, w którym szukamy owocu połączonego z krajem pochodzenia za pomocą znaku ampersand (&). Drugi argument to przeszukiwana_tab, czyli znowu dwie połączone za pomocą znaku & kolumny tym razem z tabeli Cennik zablokowane bezwzględnie. Jeśli na tym etapie podejrzymy wyniki formuły za pomocą klawisza F9, zobaczymy połączone dane z obu kolumn (rys. nr 7).
Wychodzimy z podglądu wyników formuły za pomocą skrótu klawiszowego Ctrl+Z. Pozostaje nam ostatni argument, czyli typ_porównania. U nas będzie to wartość 0 odpowiadająca dopasowaniu dokładnemu. Zapis formuły powinien wyglądać następująco:
=PODAJ.POZYCJĘ(N3&O3;$R$3:$R$10&$S$3:$S$10;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Pokazują nam się wartości w złotówkach, ale to tylko dlatego, że mamy sformatowaną kolumnę walutowo. Tak naprawdę pokazuje nam się pozycja szukanej ceny (rys. nr 8).
Mamy znalezioną pozycję właściwej ceny. Teraz na podstawie tej pozycji musimy wyszukać pasującą cenę. Taki efekt możemy uzyskać za pomocą funkcji INDEKS. Pierwszym argumentem funkcji jest tablica, czyli kolumna, z której chcemy dostać zwróconą cenę zablokowana bezwzględnie. Drugi argument funkcji to nr_wiersza, czyli wynik naszej funkcji PODAJ.POZYCJĘ. Zapis formuły powinien wyglądać następująco:
=INDEKS($T$3:$T$10;PODAJ.POZYCJĘ(N3&O3;$R$3:$R$10&$S$3:$S$10;0))
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy ceny produktów wyszukane według 2 kryteriów przedstawione na rys. nr 9.
Trzecie rozwiązanie wykonamy w Excelu tablicowym. Przykładowe dane do tego zadania zostały przedstawione na rys. nr 10.
Wykorzystamy tutaj funkcję X.WYSZUKAJ. Pierwszym argumentem funkcji jest szukana_wartość, czyli połączone dwie wartości z kolumny Owoc i Kraj za pomocą znaku & z Tabeli sprzedażowej. Drugi argument funkcji to szukana_tablica, czyli połączone kolumny Owoc i Kraj za pomocą znaku &, ale z tabeli Cennik i zablokowane bezwzględnie. Kolejny argument to zwracana_tablica, czyli wartości z kolumny Cena z tabeli Cennik zablokowane bezwzględnie. Funkcja X.WYSZUKAJ działa na zasadzie dopasowania dokładnego, więc nie musimy podawać argumentów opcjonalnych. Zapis formuły powinien wyglądać następująco:
=X.WYSZUKAJ(N19&O19;$R$19:$R$26&$S$19:$S$26;$T$19:$T$26)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy ceny wyszukane na podstawie 2 kryteriów przedstawione na rys. nr 11.
Pozostało nam czwarte rozwiązanie, w którym będziemy bazować na tym, jak się nasze dane układają i jak często się powtarzają. Przykładowe dane zostały przedstawione na rys. nr 12.
Możemy łatwo zauważyć, że każdy wiersz z tabeli Cennik jest unikatowy, czyli występuje tylko 1 raz. Na tej podstawie w tym rozwiązaniu możemy wykorzystać całkiem inną funkcję niż funkcje wyszukujące, których do tej pory używaliśmy. Wykorzystamy funkcję SUMA.WARUNKÓW. Pierwszym argumentem funkcji jest suma_zakres, czyli u nas kolumna z Ceną z tabeli Cennik zablokowana bezwzględnie za pomocą klawisza F4. Drugi argument funkcji to kryteria_zakres1, czyli zakres, na którym budujemy pierwsze kryterium – kolumna Owoc z tabeli Cennik zablokowana bezwzględnie. Kolejny argument to kryteria1, czyli dane z kolumny Owoc z Tabeli sprzedażowej. Kolejny argument to kryteria_zakres2, czyli drugie kryterium – kolumna Kraj z tabeli Cennik zablokowana bezwzględnie. I ostatni argument kryteria2, czyli wartość z kolumny Kraj z Tabeli sprzedażowej. Zapis formuły powinien wyglądać następująco:
=SUMA.WARUNKÓW($AD$3:$AD$10;$AB$3:$AB$10;X3;$AC$3:$AC$10;Y3)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy ceny ustalone według 2 kryteriów przedstawione na rys. nr 13.
Jak widać powyżej możemy to samo zadanie wykonać w Excelu na kilka sposobów, wykorzystując różne funkcje. Każde z przedstawionych rozwiązań zwróciło te same wartości. Do nas należy decyzja i ocena, które rozwiązanie jest najlepsze czy najszybsze.
